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

One: MySQL user management

Grant all on . to ' user1 ' identified by ' passwd ';
Grant Select,update,insert on DB1. To ' user2 ' @ ' 192.168.133.1 ' identified by ' passwd ';
Grant all on DB1. To ' user3 ' @ '% ' of ' identified by ' passwd ';
Show grants;
Show grants for [email protected];

Two: Common SQL statements

Select COUNT () from Mysql.user;
Select from Mysql.db;
Select db from Mysql.db;
Select Db,user from Mysql.db;
SELECT * from mysql.db where host like ' 192.168.% ';
INSERT into DB1.T1 values (1, ' abc ');
Update db1.t1 set name= ' AAA ' where id=1;
TRUNCATE TABLE db1.t1;
drop table db1.t1;
Drop database db1;

Three: MySQL database backup recovery

Backup library mysqldump-uroot-p123456 mysql >/tmp/mysql.sql
Recovery library mysql-uroot-p123456 MySQL </tmp/mysql.sql
Backup table mysqldump-uroot-p123456 mysql user >/tmp/user.sql
Recovery table mysql-uroot-p123456 MySQL </tmp/user.sql
Back up all libraries mysqldump-uroot-p-A >/tmp/123.sql
Back up table structure only mysqldump-uroot-p123456-d mysql >/tmp/mysql.sql
Specifying a character set when backing up
Mysqldump-uroot-p--default-character-set=utf8 DB >1.sql
Restore also specifies a character set
Mysql-uroot-p--default-character-set=utf8 DB < 1.sql
Description: The purpose of the specified character set is to avoid having to specify a character set in the SQL table and directly using the
MySQL default character set situation, this will cause garbled.

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