mysql 备份 授权_mysql备份、恢复及用户授权

备份数据库

[root@localhost ~]# mysqldump -u root -p auth > mysql_auth.sql

[root@localhost ~]# ll mysql_auth.sql

-rw-r--r--. 1 root root 2018  1月 31 13:34 mysql_auth.sql

[root@localhost ~]# mysqldump -u root -p mysql host user > mysql.host-user.sql

Enter password:

[root@localhost ~]# ll mysql.host-user.sql

-rw-r--r-- 1 root root 7245  1月 31 13:45 mysql.host-user.sql

[root@localhost ~]# mysqldump -u root -p --all-databases > mysql-all.sql

Enter password:

[root@localhost ~]# ll mysql-all.sql

-rw-r--r-- 1 root root 399798  1月 31 13:45 mysql-all.sql

恢复数据库

[root@localhost ~]# mysql -u root -p < mysql-all.sql

Enter password:

[root@localhost ~]# mysql -u root -p < mysql_auth.sql

Enter password:

ERROR 1046 (3D000) at line 26: No database selected

[root@localhost ~]# mysql -u root -p auth < mysql_auth.sql

Enter password:

[root@localhost ~]# mysql -u root -p mysql < mysql.host-user.sql

Enter password:

用户及权限设置(授予权限、查看权限、撤销权限)

授予权限

mysql> GRANT SELECT ON mysql.user  TO xiaoqing@'localhost' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.01 sec)

[root@localhost ~]# mysql -u xiaoqing -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.0.56-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM mysql.db;

ERROR 1142 (42000): SELECT command denied to user 'xiaoqing'@'localhost' for table 'db'

mysql> GRANT ALL ON auth.* TO admin1@'localhost' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.01 sec)

mysql>  GRANT ALL ON auth.* TO admin2@'1.1.1.0/24' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT SELECT,INSERT  ON auth.* TO admin3@'%.9hcom.com' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

查看权限

mysql> SHOW GRANTS FOR root@'localhost';

+----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost                                                                                                              |

+----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |

+----------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

mysql> SHOW GRANTS FOR admin3@'%.9hcom.com';

+-----------------------------------------------------------------------------------------------------------------+

| Grants for admin3@%.9hcom.com                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'admin3'@'%.9hcom.com' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| GRANT SELECT, INSERT ON `auth`.* TO 'admin3'@'%.9hcom.com'                                                      |

+-----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

撤销权限

mysql> REVOKE ALL ON auth.* FROM admin3@'%.9hcom.com';

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR admin3@'%.9hcom.com';

+-----------------------------------------------------------------------------------------------------------------+

| Grants for admin3@%.9hcom.com                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'admin3'@'%.9hcom.com' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>