1. mysql user management

In daily use, it is necessary to limit the permissions of personnel, so it is necessary to manage users. ##Create users and authorize

• Specify login IP

[root@host ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';
#Create user1 user and grant all rights "*. *" (wildcard)
#The first * represents db_name; the second * represents tb_name
#At the same time, specify its source IP127.0.0.1 (that is, only login through this IP)
#The wildcard% can be used here to represent all IP S (generally not used)
#Set password: identified by
mysql> quit
Bye

• Specify login socket

[root@host ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.
mysql> grant all on *.* to 'user2'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

1.1 user login

• Specified IP user login

[root@host ~]# mysql -uuser1 -p123456 -h127.0.0.1
Welcome to the MySQL monitor.
mysql> quit
Bye

• Specified socket login

[root@host ~]# mysql -uuser2 -p'123456'
Welcome to the MySQL monitor. 
mysql> exit
Bye

Because the specified Login Host is localhost, the user uses (listens to) the local mysql.socket file by default, and does not need to specify IP to log in.

1.2 authorization for specific authority

[root@host ~]# mysql -uroot -p'123456'
Welcome to the MySQL monitor.
mysql> create database db1;
Query OK, 1 row affected (0.04 sec)
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'113.83.64.114' identified by '123456';
#Create user2 user and grant it SELECT,UPDATE,INSERT permission for db1 Library

mysql> grant all on db1.* to 'user'@'%' identified by '123456';
#Create user3 and grant all permissions to its db1 library for all IP S

1.3 view authorization

[root@host ~]# mysql -uroot -p'123456'
Welcome to the MySQL monitor.
mysql> show grants;
#View the permissions of the current user

mysql> show grants for user2@113.83.64.114;
#View permissions for the specified user

1.4 change authority

[root@host ~]# mysql -uroot -p'123456'
Welcome to the MySQL monitor.
mysql> GRANT USAGE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB743291105EE4568DDA7DC67ED2CA2AD9';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user2@127.0.0.1;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@127.0.0.1                                                                                  |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1'                                              |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye

2. Common sql statements

select count(*) from mysql.user;    //Number of rows to view the contents of the specified library
select * from mysql.db\G;                  //View everything in the library
select db from mysql.db;               //View library assignments
select db,user from mysql.db;       //
select * from mysql.db where host like '192.168.%'\G;    //View the library contents corresponding to some IP S, like means matching (fuzzy query)
insert into db1.t1 values (1, 'abc');      //Insert content (1,abc) in table t1 of db1 Library
update db1.t1 set name='aaa' where id=1;      //Update all tables with id 1 in the table, and change the name content to aaa
truncate table db1.t1;    //Empty a watch and leave a grid
drop table db1.t1;         //Delete a table
drop database db1;      //Delete a library

3. MySQL database backup and recovery

Backup library mysqldump - uroot - p123456 db1 > / TMP / db1.sql
 Mysql-uroot-p123456 db1 < / TMP / db1.sql
 Backup table mysqldump - uroot - p123456 db1 T1 > / TMP / user.sql
 Restore table MySQL - uroot - p123456 db1 < / TMP / t1.sql
 Back up all databases mysqldump - uroot - P - a > / TMP / 123.sql
 Only back up the table structure mysqldump - uroot - p123456 - D MySQL > / TMP / mysql.sql