53.mysql user Management, common SQL statements, MySQL database backup recovery

One,. mysql User management

grant all on *.* to ‘user1‘@‘127.0.0.1‘ identified by ‘passwd‘;    //创建以127.0.0.1访问的用户user1，密码为passwd，对所有库的所有表拥有所有权限

 grant SELECT,UPDATE,INSERT on db1.* to ‘user2‘@‘192.168.127.1‘ identified by ‘passwd‘;       //创建以192.168.133.1访问的user2用户，密码为passwd，对db1库的所有表拥有查询，插入权限

 grant all on db1.* to ‘user3‘@‘%‘ identified by ‘passwd‘;  //创建所有ip都可以访问的用户user3.对db1库下所有表拥有所有权限。

 show grants; //查看当前用户的授权 show grants for [email protected];   //查看[email protected] 的授权，多用于增加ip拥有相同授权时使用

Two. Common SQL statements

  Select COUNT (*) from Mysql.user;           Query the user table of the MySQL library with how many rows of data select * from Mysql.db;           Query the DB table of the MySQL library for all data (for large watches with caution) Select DB from Mysql.db;     The DB field of the DB table for the query MySQL library select Db,user from mysql.db;  Query the DB field of the DB table of the MySQL library, the user field select * from Mysql.db where the host like ' 192.168.% ';     Query the DB table in the MySQL library for data in the host field starting with 192.168. Insert into DB1.T1 values (1, ' abc ');  Insert DB1 Library T1 table, 1,ABC update db1.t1 set name= ' AAA ' where id=1;     Modify the Name field value for the ID field 1 in the DB1 Library T1 table to AAA truncate TABLE db1.t1;         Delete table data, keep table structure drop table db1.t1;         Delete table data, do not keep table structure drop database db1; Delete Library  

• The delete operation should be confirmed several times before it is executed, in case of accidental deletion
  
  
  
  Third, backup and recovery of MySQL database

  备份库  mysqldump -uroot -p123456 mysql > /tmp/mysql.sql恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql备份所有库 mysqldump -uroot -p -A >/tmp/123.sql只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

  
  

53.mysql user Management, common SQL statements, MySQL database backup recovery