MySQL user management, common SQL statements, MySQL database backup recovery

MySQL User management

1, add user user1, and set the password to 123456

Mysql> Grant All on * * to ' user1 ' @ ' 127.0.0.1 ' identified by ' 123456 ';

#创建user1用户并授予其所有权限 "*. *" (wildcard character)

#第一个 *: Indicates all databases

#第二个 *: Indicates all tables

#127.0.0.1 represents the source IP, which means that only this IP can be connected; '% ': represents all IP

#identified by setting a password

2. Authorization Management for User1 users

Mysql> Grant Select,update,insert on test.* to ' user2 ' @ ' 192.168.3.74 ' identified by ' 123456 ';

Create a User2 user and specify the database for test, permissions: Select, UPDATE, insert

3, new User3, all the IP, all the rights

Mysql> Grant all on test.* to ' User3 ' @ ' percent ' identified by ' 123456 ';

4. Query User Rights

Mysql> Show grants for [email protected] ' 192.168.3.74 ';

+-------------------------------------------------------------------------------------------------------------- ---+

| Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- ---+

|  GRANT USAGE on *. user2 ' @ ' 192.168.3.74 ' identified by PASSWORD ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' | #这一行仅仅创建用户

|                                             GRANT SELECT, INSERT, update on ' Test '. * to ' user2 ' @ ' 192.168.3.74 ' #这一行是授权user2用户的select, INSERT, update |

+-------------------------------------------------------------------------------------------------------------- ---+

5. Change Permissions

Mysql> GRANT SELECT, INSERT, UPDATE on ' test '. * to ' user2 ' @ ' 192.168.3.74 ';

Common SQL statements

Additions and deletions to check:

1. View the specified number of database table rows

Select COUNT (*) from Mysql.user;

2, query 192.168.% information

Mysql> SELECT * from mysql.db where host like ' 192.168.% ' \g;

3. CREATE TABLE T1 (' id ' int (4), ' name ' char (40));

4. Insert into DB1.T1 values (1, ' abc ');

5. Clear the table

Truncate DB1.T1;

6. Delete users

Delete from user where user= ' user1 ' and host= ' 127.0.0.1 ';

7. Delete Data

Delete from db1.t1 where id=1;

8. Change data

Update db1.t1 set name= ' AAA ' where id=1;

MySQL Database backup recovery

To back up the specified library:

[Email protected] ~]# mysqldump-uroot-p123456 Test >/tmp/testbak.sql

Back Up all libraries:

[Email protected] ~]# mysqldump-uroot-p123456-a >/tmp/mysql_all.sql

To restore the specified library:

[Email protected] ~]# mysql-uroot-p123456 MySQL </tmp/mysqlbak.sql

Restore all libraries:

[Email protected] ~]# mysql-uroot-p123456 </tmp/testbak.sql

Backup table:

[[email protected] ~]# mysql-uroot-p123456 mysql user >/tmp/user.sql

To back up the table structure only:

mysqldump-uroot-p123456-d mysql >/tmp/mysql_tb.sql

Recovery table:

mysql-uroot-p123456 MySQL User </tmp/user.sql

This article is from the "Discover new things" blog, make sure to keep this source http://jacksoner.blog.51cto.com/5802843/1982777

MySQL user management, common SQL statements, MySQL database backup recovery