Shulou(Shulou.com)06/01 Report--

I will not cover some knowledge such as mysql database installation, but I will summarize some of the most commonly used mysql statements I have learned and used in mysql into this article as my own learning notes and notes. The basic environment is CentOS 6.5.The database is mysql5.6.30.

1. Start and stop Mysql services

1. / etc/init.d/mysql restar # restart 2, / etc/init.d/mysql stop # stop 3, / etc/init.d/mysql start # start 4, / etc/init.d/mysql reload # smooth restart 5, service mysql reload # smooth restart 6, service mysql stop # stop 7, service mysql start # start

2. Join the boot self-startup

Chkconfig mysql on # join self-boot chkconfig-- list | Boot self-boot set by grep mysql check

3. Connect to the database

Mysql-h-P-u root-p-e parameter:-h the host to connect to-the port of the P database is 3306 by default. If there is no change, you do not have to write-u the user name-p password to connect to the database can be directly added to the-p parameter without spaces, but in this way, when others view your history command Will get your password is not × × complete, usually when the connection, enter the password. -e you can enter the mysql statement but will not enter the client.

4. View basic information

Select user (), version (), database (), now (); # View current user, version, current database, current time and other information mysql > select user (), version (), database (), now () +-+ | user () | version () | database () | now () | +-+- +-+ | root@localhost | 5.6.30 | NULL | 2016-06-16 10:08:01 | +- -+ 1 row in set (0.11 sec)

5. Set password and password for root

The mysql database does not set a password for root by default. The experimental database rpm package is installed with the initial password. Mariadb prompts us to enter the password during initialization.

Cat / root/.mysql_secret # The random password set for the root user at Sun Jun 12 22:02:31 2016 (local time): nR7PKQyH5DU2zjKM this part is the initial password, mysqladmin-u root password'*'# set password change password select host,user,password from mysql.user +-- +-+-- + | host | user | password | +- -+-+-- + | localhost | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | localhost.localdomain | root | * 47F6BC7F709C7CCFCB1EAF451FFE6D89F1377D84 | | 127.0.0.1 | root | | * 47F6BC7F709C7CCFCB1EAF451FFE6D89F1377D84 |:: 1 | root | * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | 192.168.1.% | tomcat | * 6FDD34EE54803B8CC847CC5D7158702BCC21FCF6 | |% | winnerlook | * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +-- +-- -+ (1) mysqladmin-u username-p Old password password "*" for example: mysqladmin-u winner password "123"-p [root@localhost ~] # mysqladmin-u winner password" 123"-p Enter password: Warning: Using a password on the command line interface can be insecure. (2) after logging in to the database, use the set password command format: SET password for user@host = password ("") Note that encryption functions such as set password for root@'::1' = password; Query OK, 0 rows affected (0.05 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > set password for tomcat@'192.168.1.%' = password ("123123"); Query OK, 0 rows affected (0.00 sec) mysql > flush privileges Query OK, 0 rows affected (0.00 sec) (3) after login, use update to directly manipulate the user table Note: here to use encryption functions and restrictions, do not pay attention to the restrictions may change all user passwords. Such as the following content directly change all the content and plaintext password. Update user set password= ("123123"); Query OK, 6 rows affected (0.03 sec), Rows matched: 6 Changed: 6 Warnings: 0mysql > select host,user,password from mysql.user +-- + | host | user | password | +-- + | localhost | | root | 123123 | | localhost.localdomain | root | 123123 | | 127.0.0.1 | root | 123123 | |:: 1 | root | 123123 | | 192.168.1.% | tomcat | 123123 | |% | winnerlook | 123123 | +-| -+ correct change method: update user set password=password ("123123") Query OK, 6 rows affected (0.02 sec) Rows matched: 6 Changed: 6 Warnings: 0mysql > select host,user,password from mysql.user +-- +-+-- + | host | user | password | +- -+-+-- + | localhost | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | localhost.localdomain | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | 127.0.0.1 | root | | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |:: 1 | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | 192.168.1.% | tomcat | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | |% | winnerlook | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | +-- +-- -+ 6 rows in set (0.00 sec) use the where sentence to add constraints mysql > update user set password=password ("123") where user='tomcat' Query OK, 1 row affected (0.05sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select host,user,password from mysql.user +-- +-+-- + | host | user | password | +- -+-+-- + | localhost | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | localhost.localdomain | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | 127.0.0.1 | root | | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |:: 1 | root | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | 192.168.1.% | tomcat | * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |% | winnerlook | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | +-- +-- -+ 6 rows in set (0.00 sec)

6. Refresh permissions

Mysql > flush privileges;Query OK, 0 rows affected (0.14 sec)

7. Mysql client skills

Echo "select * from tb_emp8;" | mysql-u root-p test_db > > test.txt [root@localhost ~] # echo "select * from tb_emp8" | | mysql-u root-p test_db > test.txtEnter password: [root@localhost ~] # cat test.txt id names deptId salary1 Lucy NULL 10002 Lura NULL 12003 Kevin NULL 15004 Lucy NULL 10005 Lura NULL 12006 Kevin NULL 15007 Lucy NULL 10008 Lura NULL 12009 Kevin NULL 150010 Lucy NULL 100011 Lura NULL " 120012 Kevin NULL 150013 Lucy NULL 100014 Lura NULL 1200 method 2mysql-u root-p-e "select * from test_db.tb_emp8" "> test2.txtEnter password: [root@localhost ~] # cat test2.txt id names deptId salary1 Lucy NULL 10002 Lura NULL 12003 Kevin NULL 15004 Lucy NULL 10005 Lura NULL 1200

The method of executing sql files

(1) mysql-u root-p test_db

< /root/test.sql（2）cat /root/test.sql |mysql -u root -p test_db（3）登录数据库后source 加载 8、创建交互文件日志 可以用来评估和考量操作过程中出现哪些操作，可以用tee mysql --tee=test.log -u root -p # 创建一个test.log日志文件Logging to file 'test.log'Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 34Server version: 5.6.30 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

\ t test.log # starts recording log files Logging to file 'test.log'mysql > select user (), version (), now () +-+ | user () | version () | now () | +- -+ | root@localhost | 5.6.30 | 2016-10-07 17:14:25 | +-+ 1 row in set (0.11 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | booksdb | | company | | mysql | | performance_schema | | sakila | | team | | test | | test_db | | winner | | world | +-+ 11 rows in set (0.00 sec) mysql >\ t # end recording check log file: [root@localhost ~] # cat test.log Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 34Server version: 5.6.30 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql >\ T test.logmysql > select user (), version (), now () +-+ | user () | version () | now () | +- -+ | root@localhost | 5.6.30 | 2016-10-07 17:14:25 | +-+ 1 row in set (0.11 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | booksdb | | company | | mysql | | performance_schema | | sakila | | team | | test | | test_db | | winner | | world | +-+ 11 rows in set (0.00 sec) mysql > use world | Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables;+-+ | Tables_in_world | +-+ | city | | country | | countrylanguage | +-+ 3 rows in set (0.00 sec) mysql > desc city +-+ | Field | Type | Null | Key | Default | Extra | +- -+ | ID | int (11) | NO | PRI | NULL | auto_increment | | Name | char (35) | NO | | CountryCode | char (3) | NO | | District | char (20) | NO | | Population | int (11) | NO | | 0 | | +-+-+ 5 rows in set (0.51sec) mysql > select count (*) from city +-+ | count (*) | +-+ | 4079 | +-+ 1 row in set (sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.