MySQL OCP888题解044-从服务器上导入mysql模式数据后的权限问题

1、原题

1.1、英文原题

You have just created a replication slave from a backup of the master made with mysqldump:
mysqldump -u backup -p --all-databases > /backups/mysql.sql
You try to log in to the slave with the application user, but fail as follows:
Mysql -u application -p
ERROR 1045 (28000): Access denied for user ‘application’@‘localhost’ (using password: YES)
The login works on the master.
Which two changes to the process can fix the issue?
A、After the restore, log in to the database and execute FLUSH PRIVILEGES.
B、Add a second dump for the “mysql” database; --all-databases does not include it.
C、Use the --grants option to include GRANT statements in the dump.
D、Use the --flush-privileges with mysqldump.

1.2、中文翻译

您刚刚从使用mysqldump创建的主服务器备份创建了一个复制从属服务器：
mysqldump -u backup -p --all-databases > /backups/mysql.sql
您尝试使用应用程序用户登录从属服务器，但失败如下：
Mysql -u application -p
ERROR 1045 (28000): Access denied for user ‘application’@‘localhost’ (using password: YES)
对进程的哪两项更改可以解决问题？
A、 还原后，登录数据库并执行FLUSH PRIVILEGES。
B、 Add a second dump for the “mysql” database; --all-databases does not include it.
C、 使用–grants选项在转储中包含GRANT语句。
D、 对mysqldump使用–flush-privileges选项

1.3、答案

A、D

2、题目解析

本题考察mysqldump进行转储备份的细节。

2.1、题干解析

2.2、选项解析

1. 因为mysql的user表被导入，但是user表通过update/insert的权限无法立刻生效，需要FLUSH PRIVILEGES。所以选项A正确。
2. 当导出"mysql"数据库时，可以加上–flush-privileges选项，这样恢复数据时可以让数据生效。所以选项D正确。

3、知识点

3.1、知识点1：mysqldump的–flush-privileges选项

在转储"mysql"数据库（包含权限表）时，要添加一个FLUSH PRIVILEGES语句，恢复时才能正确恢复，而且恢复时也需要足够的权限来执行该语句。

官方参考文档

3.2、知识点2：mysqldump的–all-databases选项

mysqldump默认情况下指定哪个数据库就只导出哪个数据库，如下所示：

mysqldump world > world.sql

当使用了–all-databases选项，mysqlpump会转储所有数据库（除了mysqlpump限制中指出的某些例外），包括"mysql"数据库。

mysqlpump --all-databases

3.3、知识点3：mysqldump默认不转储的内容

• mysqlpump默认不转储INFORMATION_SCHEMA、Performance_schema和sys模式。要转储其中任何一个，请在命令行中明确地命名它们。你也可以用 --databases 或 --include-databases 选项命名它们。
• mysqlpump不转储InnoDB CREATE TABLESPACE语句。

4、实验

4.1、实验1

4.1.1、实验目的

1、mysqldump --all-databases会导出哪些数据库，会导出mysql模式的哪些表。

4.1.2、实验前准备

已安装并正常运行的MySQL5.7

4.1.3、实验步骤

1. 使用root用户登录，创建一个用户名很特殊的用户，方便后面的搜索，用户名为ixidixixhspxzvwy：

mysql> CREATE USER ixidixixhspxzvwy IDENTIFIED BY '000000';

2. 创建一个名字很特殊的数据库，方便后面的搜索，数据库名为nsfduapthkiu，库内创建一张名字特殊的表，表名为eahmhoioynvt，插入几条测试数据。

mysql> create database nsfduapthkiu;
mysql> use nsfduapthkiu;
mysql> create table eahmhoioynvt(id INT NOT NULL PRIMARY KEY, name VARCHAR(10));
mysql> INSERT INTO eahmhoioynvt VALUES(1,"1"),(2,"2"),(3,"c");

3. 将新创建的库授权给用户ixidixixhspxzvwy

GRANT ALL PRIVILEGES ON nsfduapthkiu.* TO ixidixixhspxzvwy; 

4. 查看当前库有哪些模式，发现除了自建的nsfduapthkiu模式，还有information_schema、mysql、performance_schema和sys这几个模式。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nsfduapthkiu       |
| performance_schema |
| sys                |

5. 查看mysql模式下有哪些表，发现共31张表

mysql> USE mysql
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |

6. 退出mysql会话，回到Linux界面，使用mysqldump --all-databases做备份：

$ mysqldump --all-databases > all_databases.sql

7. 对all_databases.sql进行搜索"CREATE DATABASE /!32312 IF NOT EXISTS/"，发现确实会导出两个模式，即mysql模式和自建的模式nsfduapthkiu：

......
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;
......
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nsfduapthkiu` /*!40100 DEFAULT CHARACTER SET utf8 */;
......

8. 对all_databases.sql进行搜索用户名ixidixixhspxzvwy，发现确实会导出user表：

$ cat all_databases.sql | grep ixidixixhspxzvwy
INSERT INTO `user` VALUES
......
('%','ixidixixhspxzvwy','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*032197AE5731D4664921A6CCAC7CFCE6A0698693','N','2022-12-09 02:35:38',NULL,'N');

9. 再仔细查看all_databases.sql，会发现mysql模式的31张表都被导出了。

4.1.4、实验结论

1. mysqldump --all-databases会导出除了information_schema、performance_schema和sys这三个模式以外的所有模式的所有表，包括mysql模式的所有表。

5、总结

1. mysqldump --all-databases会导出除了information_schema、performance_schema和sys这三个模式以外的所有模式的所有表，包括mysql模式的所有表。
2. 如果想让mysql授权信息在导入后立即生效，则导出时要加上–flush-privileges选项，或者在导入后登录再执行FLUSH PRIVILEGES。