MySQL 8.0 OCP 1Z0-908 51-60题

Q51.Examine this parameter setting:
audit_log=FORCE_LOG_PERMAENT
What effect does this have on auditing?
A)It will force the load of the audit plugin even in case of errors at server start.//插件在之前就必须加载完成，只有当插件之前通过 INSTALL PLUGIN 注册过，或者通过 --plugin-load 或 --plugin-load-add 加载时，这个选项才可用
B)It causes the audit log to be created if it does not exist.
C)It prevents the audit plugin from being removed from the running server.
D)It prevents the audit log from being removed or rotated.
Answer:C

参考：MySQL :: MySQL 8.0 Reference Manual :: 8.4.5.11 Audit Log Reference
audit_log有效值：
ON：启用审计日志插件。
OFF：禁用审计日志插件。
FORCE：强制加载审计日志插件，即使加载失败也不会影响服务器启动。
FORCE_PLUS_PERMANENT：强制加载审计日志插件，并且在服务器运行期间不允许卸载该插件。

Q52.You recently upgraded your MYSQL installation to MYSQL8.0 Examine this client error:
Error 2059 (HY000):authentication plugin ‘caching_sha2_password’ cannot be
Loaded: /usr/local/mysql/libplugin/caching_sha2_password.so: cannot open shared object file: No such or directory
Which option will allow this client to connect to MYSQL Server?
A)[mysqld] default_authentication_plugin=sha256_password
B)[mysqld] default_authentication_plugin=caching_sha2_password
C)ALTER USER user IDENTIFIED WITH mysql_native_password BY ‘password’; //修改改密码重新选择密码验证插件
D)ALTER USER user IDENTIFIED WITH caching_sha2_password
E)ALTER USER user IDENTIFIED WITH sha256_password
F)[mysqld] default_authentication_plugin=mysql_native_password
Answer:C

Q53.You made some table definition changes to a schema in your MySQL Server.
Which two statements reflect how MySQL Server handles the table definition changes?
A)MySQL keeps InnoDB metadata changes in .sdi files in datadir. 
B)MySQL Server stores a copy of the serialized data in the InnoDB user tablespace.
C)MySQL writes SDI to the binary log for distributed backups.
D)MySQL implicitly executes FLUSH TABLES and stores a snapshot backup of the metadata.
E)The metadata is serialized in JSON format in Serialized Dictionary Information (SDI).
Answer:BE

说明：InnoDB stores SDI data within its tablespace files.
NDBCLUSTER stores SDI data in the NDB dictionary.
Other storage engines store SDI data in .sdi files that are created for a given table in the table's database directory.
SDI data is generated in a compact JSON format.
InnoDB 将 SDI 数据存储在其表空间文件内。
NDBCLUSTER 将 SDI 数据存储在 NDB 字典中。
其他存储引擎则在给定表的数据库目录中创建 .sdi 文件来存储 SDI 数据。
SDI 数据以紧凑的 JSON 格式生成。

"ibd2sdi"是一个工具，它的作用是从InnoDB表空间文件中提取序列化的字典信息（即SDI）。而所谓的SDI数据，是存在于所有持久性的InnoDB表空间文件之中的。

Q54.Which two statements are true about MySQL Installer?
A)It provides a uniform installation wizard across multiple platforms. //MySQL Installer 提供了一个统一的安装向导，但主要适用于 Windows 平台，而不是多个平台。
B)Manual download of separate product packages is required before installing them through MySQL Installer.

参考：

MySQL :: MySQL 8.0 Reference Manual :: 2.3.3.3 Installation Workflows with MySQL Installer

• 全量安装包情况：如果你安装的是完整的（非网络版）MySQL Installer包，在初始设置期间，所有的.msi文件已经被加载到“产品缓存（Product Cache）”文件夹中，因此不会再次下载。这可以节省时间和网络带宽，因为所有必要的安装文件都已经预先准备好。

C)It provides only GUI-driven, interactive installations. //MySQL Installer 提供了图形界面（GUI）安装，同时也支持命令行（CLI）安装
D)It performs product upgrades. 可以执行升级，也是正确的
E)It installs most Oracle MySQL products. 可以安装大多数MySQL产品，所以正确的
Answer:DE

Q55.Examine this snippet from the binary log file named binlog.000036:
# at 5000324
#191120 14155116 server id 1 end_log_pos 500453 crc32 0x98159515 Query thread_id=9 exec_time=2
error_code=0xid=1106
SET TIMESTAMP=1574222116/*!*/;
DROP TABLE ‘rental’/* generated by server*/
/*!*/;
The rental table was accidentally dropped, and you must recover the table.
You have restored the last backup, which corresponds to the start of the binlog.000036 binary log.
Which command will complete the recovery?
A)mysqlbinlog --stop-position=500324 binlog.000036 | mysql
B)mysqlbinlog --stop-datetime='2019-11-20 14:55:16' binlog.000036 | mysql
C)mysqlbinlog --stop-position=500453 binlog.000036 | mysql
D)mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 | mysql
Answer:A
上面输出说明是在5000324这个pos中删除的表，所以恢复的话，需要恢复这个pos之前的，所以stop_position需要等于5000324

Q56.Examine this query and output:
Mysql> EXPLAIN ANALYZE
 SELECR city.CountryCode,contry,Name AS Country_Nae,
 FROM world.city
 INNER JOIN world.country ON country.Code =city.CountryCode
 WHERE country.Continent=’Asia’
 AND city.Population >100000
 ORDER BY city,Population DESC\G
***********************************1.row*****************************
EXPLATN:
->Sort <temporary>.Poppulation DESC(acctual time =8.306..8.431 row =125 loops=1)
 ->Strem resule(acctual time =0.145..8.033 row =125rows=125 loops=1)
->Nested loop inner join (cost=241.12 rows=205) (acctual time =0.141.7.787 row =155 loops=1)
 ->Filter (world.country,Continent =’Asia’)(cost=25.40 rows=34)(acctual time =0.064..0.820 row =51
loops=51)
->index lookup on city using CountryCode(Countrycode=world.country.code)(acctual time =4.53..row =10 )
1 row in set (0.0094 sec)

Which two statements are true?
A)The query returns exactly 125 rows. //最终结果，看第一行，为125行
B)It takes more than 8 milliseconds to sort the rows. //第一行，读取到第一行数据的时间为8.306，读取所有数据时间为8.531，所以都超过了8ms
C)The country table is accessed as the first table, and then joined to the city table.//先对city表进行访问，然后在访问country表，在进行nested loop inner join，最后在进行排序
D)35 rows from the city table are included in the result.（rows=2）
E)The optimizer estimates(估计行数) that 51 rows in the country table have continent = 'Asia'.(estimates rows=34)
Answer:AB
这个题库有问题，执行计划中可以看到对city表还做了filter操作，过滤完成后的行数是2，所以D错误，完整的执行计划如下图：

Q57.Examine these InnoDB Cluster parameter settings:
Cluster.setInstanceOption()’host1:3377’,’MenmberWeight’,40)
Cluster.setInstanceOption()’host2:3377’,’MenmberWeight’,30)
Cluster.setInstanceOption()’host3:3377’,’MenmberWeight’,40)
Cluster.setInstanceOption()’host3:3377’,’exitStateAction’,”ABORT_SERVER”)
Now examine the partial staus:
“topology”;{
”host1:3377”:
“address”:”host1:3377”,
“mode”:”R/O”,
[...]
“statue”:”ONLINE”,
“version”:”8.0.18”
},
“host2:3377”:{
“address”:”host2:3377”,
“mode”:”R/O”,
[...]
“statue”:”ONLINE”,
“version”:”8.0.18”
},
“host3:3377”:{
“address”:”host3:3377”,
“mode”:”R/O”,
[...]
“statue”:”ONLINE”,
“version”:”8.0.18”
}
}
A permanent network failure isolates host3.
Which two statements are true?
A)The instance deployed on host2 is elected as the new primary instance.（host1:weight=40权重越高，优先选为主库，所以1可能会被选主）
B)The issuing command cluster.switchToMultiPrimaryMode() will fail to enable multi-primary mode.还有两个节点正常，所以可以设置多主模式
C)The instance deployed on host3 is expelled from the cluster and must be rejoined using
cluster.addInstance('host3:3377'). //exitStateAction 参数设置为 ABORT_SERVER 表示当 host3 检测到其状态异常时，将自动关闭服务器，所以是正确的
D)Failure of the instance deployed on host1 provokes an outage. //1不会受影响，更不会引发故障，所以这个是错误的
E)The primary instance can be specified by using the command cluster.setPrimaryInstance(<host>:<port>). 通过该命令可以设置主节点，正确
F)The instance deployed on host3 will automatically rejoin the cluster when connectivity is re-established.//参考C选项
Answer:CE

Q58.You are asked to review possible options for a new MySQL instance. It will be a large, busy reporting data
warehousing instance.
[mysqld]
innodb_data_file_path=
Which two configurations would satisfy long-term storage demands?
A)ibdata1:12M;ibdata2:12M:autoextend //自动扩展
B)ibdata1:12M:autoextend //自动扩展
C)ibdata1:12M;/tmp/ibdata2:12M:autoextend //有自动扩展，但是在/tmp下，如果重启，该路径下数据将会被清空，所以不满足长期存储需求
D)ibdata1:12M;ibdata2:12M;ibdata3:12M //没有自动扩展
E)ibdata1:12M:autoextend;ibdata2:12M:autoextend //该配置方式是错误的
F)ibdata1:12M //没有自动扩展
Answer:AB

Q59.Examine this command, which executes successfully:
shell> mysqldump --master-data=2 --single-transaction --result-file=dump.sql mydb
Which two statements are true?
A)It is a cold backup. (热备)
B)It executes flush tables with read lock.
C)It enforces consistent backups for all storage engines.（一致性备份仅适用于innodb）
D)This option uses the READ COMMITTED transaction isolation mode. (REPEATABLE READ)
E)The backup created is a consistent data dump.
Answer:BE

参考：

Mysql备份原理与工具mysqldump之--single-transaction，--master-data_mysqldump --single-transaction-CSDN博客文章浏览阅读1.2w次，点赞9次，收藏58次。MySQL备份原理数据备份是数据安全的底线，在任何场景下面，无论是上线后的数据回滚，数据丢失都能够有效的止损，将损失降到最低程度。衡量数据备份与恢复的两个重要指标：对于_mysqldump --single-transactionhttps://blog.csdn.net/qq_34556414/article/details/106781973

--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
 
--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；
 
single-transaction 选项和 lock-all-tables 选项是二选一的，
single-transaction：是在导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上unlock tables，然后执行导出,导出过程不影响其它事务或业务连接，但只支持类似innodb多版本特性的引擎，因为必须保证即使导出期间其它操作(事务点t2)改变了数据，而导出时仍能取出导出开始的事务点t1时的数据。
lock-all-tables：则一开始就 FLUSH TABLES WITH READ LOCK; 加全局读锁，直到dump完毕。

mysqldump备份流程：
1.调用FWRL（flush tables with read lock）,全局禁止读写
2.开启快照读，获取此期间的快照（仅仅对innodb起作用）
3.备份非innodb表数据（*.frm,*.myi,*.myd等）
4.非innodb表备份完毕之后，释放FTWRL
5.逐一备份innodb表数据
6.备份完成

 Q60.Examine this command and output:
+-------------------------------+------------+
| Varibe_name | Value |
+-------------------------------+-------------+
| Firewall_access_denied | 7 |
| Firewall_access_granted | 4 |
| Firewall_access_suspicious | 3 |
| Firewall_access_cached_entries | 11 |
+-------------------------------+-------------+
Which statement is true?
A)Firewall_access_denied is the number of connection attempts from prohibited(禁止的) hosts that are denied.
B)Firewall_cached_entries is the number of statements found in the query cache for users in DETECTING mode.
C)Firewall_access_granted is the number of connections granted from whitelisted hosts.//符合防火墙规则，而不是白名单
D)Firewall_access_suspicious is the number of statements logged as suspicious for users in DETECTING mode.
Answer:D
Firewall_access_denied：被拒绝的
Firewall_access_granted：接受的
Firewall_access_suspicious：可疑的
Firewall_access_cached_entries：重复的

MySQL Enterprise Firewall状态变量概述

• 支持的状态变量：MySQL Enterprise Firewall提供了以下状态变量，这些变量用于获取防火墙运行状态的相关信息。
• 可用性前提：这些状态变量只有在防火墙已安装的情况下才可用（具体安装或卸载防火墙的方法可参考第8.4.7.2节“安装或卸载MySQL Enterprise Firewall”）。
• 初始化设置：每当安装MYSQL_FIREWALL插件或启动服务器时，防火墙状态变量都会被设置为0。
• 重置机制：其中许多状态变量可以通过mysql_firewall_flush_status()函数重置为零（关于MySQL Enterprise Firewall的管理函数可参考相关说明）。

具体状态变量及其含义

• Firewall_access_denied
  • 含义：表示被MySQL Enterprise Firewall拒绝的语句数量。即那些不符合防火墙规则或被认为是潜在威胁的SQL语句，被防火墙阻止执行的次数。
• Firewall_access_granted
  • 含义：表示被MySQL Enterprise Firewall接受的语句数量。即符合防火墙规则，被允许正常执行的SQL语句的次数。
• Firewall_access_suspicious
  • 含义：表示被MySQL Enterprise Firewall记录为可疑的语句数量。这些语句是针对处于DETECTING模式的用户记录的。DETECTING模式可能是防火墙的一种监控模式，在该模式下，防火墙会检测并记录那些可能存在问题或行为异常的SQL语句，但不一定立即阻止它们，而是将其标记为可疑以便进一步分析。
• Firewall_cached_entries
  • 含义：表示被MySQL Enterprise Firewall记录的语句数量，包括重复的语句。防火墙会将检测到的SQL语句进行记录，无论它们是否重复出现，都会被计入此变量。这有助于了解防火墙所处理的语句总量以及重复语句的情况。