13.4 MySQL用户管理
创建用户并授权
指定登录IP
[root@1 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';#创建user1用户并授予其所有权限“*.*”（通配符）#第一个*表示db_name；第二个*表示tb_name#同时指定其来源IP127.0.0.1（即，只可通过此IP登录）#此处可以使用通配符%，代表所有IP（一般不使用）#设定密码：identified bymysql> quitBye
指定登录socket
[root@1 ~]# mysql -uroot -pEnter 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> quitBye
用户登录
使用IP登录
[root@1 ~]# mysql -uuser1 -p123456 -h127.0.0.1Welcome to the MySQL monitor.mysql> quitBye
使用socket登录
[root@1 ~]# mysql -uuser2 -p'123456'Welcome to the MySQL monitor. mysql> exitBye
说明： 因为指定登录主机为localhost，所以该用户默认使用（监听）本地mysql.socket文件，不需要指定IP即可登录。
对具体权限进行授权
[root@1 ~]# 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'@'192.168.8.132' identified by '123456';#创建user2用户，并授予其针对db1库SELECT,UPDATE,INSERT权限mysql> grant all on db1.* to 'user'@'%' identified by '123456';#创建user3，并针对所有IP授予其db1库所有权限
权限相关命令
[root@1 ~]# mysql -uroot -p'123456'Welcome to the MySQL monitor.mysql> show grants;#查看当前用户的权限mysql> show grants for user2@192.168.8.132;#查看指定用户的权限
更改权限
[root@1 ~]# 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> quitBye
注： 更改用户权限时，权限行的内容都要更改。
13.5 常用sql语句
[root@1 ~]# mysql -uroot -p'123456';Welcome to the MySQL monitor.mysql> use db1;Database changed#选择库mysql> select count(*) from mysql.user;+----------+| count(*) |+----------+| 12 |+----------+1 row in set (0.04 sec)#查看指定库的内容的行数mysql> select * from mysql.db\G;#查看库的所有内容mysql> select db,user from mysql.db;#查看库指定内容mysql> select * from mysql.db where host like '192.168.%'\G;#查看某些IP对应的库内容，like表示匹配mysql> create table t1(`id` int(4),`name` char(40));Query OK, 0 rows affected (0.39 sec)#在db1库下创建表t1mysql> select * from db1.t1;Empty set (0.03 sec)#查看表中信息：空表mysql> insert into db1.t1 values(1,'abc');Query OK, 1 row affected (0.09 sec)#向表中插入内容mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 1 | abc |+------+------+1 row in set (0.00 sec)mysql> update db1.t1 set name='aaa' where id=1;Query OK, 1 row affected (0.08 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 1 | aaa |+------+------+1 row in set (0.00 sec)#更改表中指定内容mysql> delete from db1.t1 where id=1;Query OK, 2 rows affected (0.10 sec)#删除表中指定内容mysql> select * from db1.t1;Empty set (0.00 sec)mysql> truncate db1.t1;Query OK, 0 rows affected (0.09 sec)#清空一个表中内容mysql> drop table t1;Query OK, 0 rows affected (0.04 sec)#删除表mysql> drop database db1;Query OK, 0 rows affected (0.13 sec)#删除库mysql> use mysql；mysql> delete from user where User='user1' and Host='127.0.0.1';Query OK, 1 row affected (0.06 sec)#删除用户，在删除用户前需要先指定表
13.6 MySQL数据库备份恢复
备份库
备份指定库：[root@1 ~]# mysqldump -uroot -p123456 mysql > /tmp/mysqlbak.sql备份所有库：[root@1 ~]# mysqldump -uroot -p123456 -A > /tmp/mysql_all.sql
恢复库
[root@1 ~]# mysql -uroot -p123456 < /tmp/mysqlbak.sql
备份表
备份指定表：[root@1 ~]# mysql -uroot -p123456 mysql user > /tmp/user.sql只备份表结构：[root@1 ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql_tb.sql
恢复表
[root@1 ~]# mysql -uroot -p123456 mysql user < /tmp/user.sql