13.4 MySQL用户管理

创建用户并授权

指定登录IP

[root@centos-01linux ~]# mysql -uroot -p

Enter 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 by

mysql> quit

Bye

指定登录socket

[root@centos-01linux ~]# 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

用户登录

使用IP登录

[root@centos-01linux ~]# mysql -uuser1 -p123456 -h127.0.0.1

Welcome to the MySQL monitor.

mysql> quit

Bye

使用socket登录

[root@centos-01linux ~]# mysql -uuser2 -p'123456'

Welcome to the MySQL monitor.

mysql> exit

Bye

说明： 因为指定登录主机为localhost，所以该用户默认使用（监听）本地mysql.socket文件，不需要指定IP即可登录。

对具体权限进行授权

[root@centos-01linux ~]# 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@centos-01linux ~]# mysql -uroot -p'123456'

Welcome to the MySQL monitor.

mysql> show grants;

#查看当前用户的权限

mysql> show grants for user2@192.168.8.132;

#查看指定用户的权限

更改权限

[root@centos-01linux ~]# 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

注： 更改用户权限时，权限行的内容都要更改。

13.5 常用sql语句

[root@centos-01linux ~]# 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库下创建表t1

mysql> 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: 0

mysql> 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@centos-01linux ~]# mysqldump -uroot -p123456 mysql > /tmp/mysqlbak.sql

备份所有库：

[root@centos-01linux ~]# mysqldump -uroot -p123456 -A > /tmp/mysql_all.sql

恢复库

[root@centos-01linux ~]# mysql -uroot -p123456 < /tmp/mysqlbak.sql

备份表

备份指定表：

[root@centos-01linux ~]# mysql -uroot -p123456 mysql user > /tmp/user.sql

只备份表结构：

[root@centos-01linux ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql_tb.sql

恢复表

[root@centos-01linux ~]# mysql -uroot -p123456 mysql user < /tmp/user.sql