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

MySQL User management Create user

grant all on *.* to ‘user1‘@‘localhost‘ identified by ‘123456‘;grant all on db1.* to ‘user2‘@‘%‘ identified by ‘123456‘;        //创建user2用户，所有ip都能登录，指定权限为db1库下的所有表；

flush privileges;  刷新授权

. : represents all libraries and tables;
User1: User name;
localhost: login ip, default localhost is the native login IP, you can also use% to represent all;

View Current User Permissions

show grants;show grants for ‘user1‘@‘%‘;    //查询用户user1，限制ip为所有的权限；

Common SQL statements

Select: View;
Insert: inserting;
Update: Change;
Drop: Delete;

View: Counts the number of rows in the specified table;

select count(*) from mysql.user;        //count（）表示统计行数；*代表所有；

+----------+| count(*) |+----------+|        9 |+----------+1 row in set (0.01 sec)

View the contents of a specified table

select * from mysql.db;        //查看mysql库下db表的所有内容；

View the contents of a field below a specified table

select db from mysql.db;        //查找mysql库下db表里面db列的内容；select db，user from mysql.db;    //查找mysql库下db表中db列与user列的内容；

Fuzzy query

select * from mysql.db where host like ‘192.168.%‘;            //查找mysql.db下带有192.168.字样的列出来

Inserting data

insert into db1.t1 values (1,‘abc‘); //在db1.t1中，插入两条数据，第一条为数字1，第二条为字符串abc；

mysql>select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)

Change data

update db1.t1 set name=‘aaa‘ where id=1; //将db1.t1的id为1的 name内容更改为字符串aaa

mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | aaa  ||    2 | 123  |+------+------+2 rows in set (0.00 sec)

Empty the contents of a table

TRUNCATE TABLE db1.t1;

truncate table db1.t1;            //将db1.t1表的内容全部清空掉；select * from db1.t1;                //查询db1.t1的内容Empty set (0.00 sec)

Delete

drop table db1.t1;    //删除t1表drop database db1;    //删除数据库db1

Backup and restore of MySQL database backup specified database

mysqldump -uroot -p‘123456‘ mysql > /tmp/backup/mysqlbak.sql            //将名为mysql的数据库备份到指定目录下

Recovering a Database

mysql -uroot -p‘123456‘ mysql < /tmp/backup/mysqlbak.sql                //将指定目录下的备份文件恢复为mysql数据库

Backup table

mysqldump -uroot -p‘123456‘ mysql user >/tmp/backup/user.sql        //将mysql下的user表备份到指定目录

Recovery table

mysql -uroot -p‘123456‘ mysql < /tmp/backup/user.sql            //将指定目录下的表恢复到mysql库下

Back up all databases

mysqldump -uroot -p‘123456‘ -A > /tmp/backup/123.sql        //将所有数据库备份到指定目录

Back up table structure only

mysqldump -uroot -p‘123456‘ -d mysql > /tmp/backup/mysql-biaojg.sql

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