MySQL 8.0 OCP 1Z0-908 141-150题

Q141.Which two are features of MySQL Enterprise Firewall? (Choose two.)
A)recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands
B)blocking of potential threats by configuring pre-approved whitelists
C)modifying SQL statement dynamically with substitutions
D)automatic locking of user accounts who break your firewall
E)provides stateless firewall access to TCP/3306
Answer:AB

选项A：翻译：记录传入的 SQL 语句，以便创建允许命令的白名单。
MySQL企业防火墙是记录用户名以及主机名或IP信息，这些SQL的规则可以根据防火墙profile的模式配置去选择是否拒绝某一类SQL的是否为注入SQL，正确
选项B：通过配置白名单批准的SQL组织潜在的威胁，正确
选项C：通过替换来实现动态的修改SQL语句(防火墙不会修改SQL)
选项D：动态的锁定破坏防火墙的账号，防火墙不会锁定用户账号
选项E：无状态防火墙访问TCP端口3306。这句话的意思是，防火墙允许对TCP端口3306的访问，而无状态防火墙指的是防火墙不对连接的状态进行跟踪，每个数据包都是独立处理的。(该选项我使用的deepseek进行翻译)

Q142.Examine the modified output:
mysql> SHOW SLAVE STATUSIG
*****1. row
Slave_I0_Running: Yes
Slave_SQL_Running: Yes
seconds_Behind_Master: 1612
Seconds_Behind_Master value is steadily growing.
What are two possible causes? (Choose two.)
A)The master is most probably too busy to transmit data and the slave needs to wait for more data.
B)One or more large tables do not have primary keys.
C)This value shows only I/0 latency and is not indicative of the size of the transaction queue.
D)The master is producing a large volume of events in parallel but the slave is processing them serially.
E)The parallel slave threads are experiencing lock contention.
Answer:CD

Seconds_Behind_Master = 当前时间 - SQL线程正在执行的事件的时间戳
选项A：有延迟，所以应该是从节点回放较慢，而不是从节点等待数据，从服务器的 I/O 线程（Slave_IO_Running）正常运行，说明数据传输没有问题
选项B：一个或者更多的表没有主键，个人认为没有主键的表也是有可能导致复制延迟的
选项C：这个值只是I/O延迟，不是事务队列大小。正确
选项D：主节点并行处理，从节点顺序处理，导致的延迟也是有可能的
选项E：并行的slave线程有锁竞争，也有可能导致复制延迟

个人理解应该选择BCDE

Q143.You must configure the MySQL command-line client to provide the highest level of trust and security when
connecting to a remote MySQL Server.
Which value of --ssl-mode will do this?
A)PREFERRED ,
B)VERIFY_CA
C)REQUIRED
D)VERIFY_IDENTITY
Answer:D

如果没有指定--ssl-mode选项，客户端会尝试使用加密方式建立连接，如果无法建立加密连接，则会退回到未加密的连接。这与显式指定--ssl-mode=PREFERRED选项时的行为相同。
当指定--ssl-mode=REQUIRED时，客户端要求建立加密连接，如果无法建立加密连接，则会失败。
当指定--ssl-mode=DISABLED时，客户端将使用未加密的连接。
当指定--ssl-mode=VERIFY_CA或--ssl-mode=VERIFY_IDENTITY时，客户端要求建立加密连接，并且还会对服务器的CA证书进行验证，如果使用--ssl-mode=VERIFY_IDENTITY，还会对服务器证书中的主机名进行验证。

Q144.Consider this shell output and executed commands:
[ root@oel7 ~]# ps aux | grep mysqld
mysql 2076 3.5 24.6 1386852 372572 ? Ssl 12:01 0:01 /usr/sbin/mysqid
[root@oel7 ~] #kill -15 2076
Which statement is true about MySQL server shutdown?
A)ki11 -15 should be avoided. Use other methods such as mysqladmin shutdown or systemct1 stop mysqld.
B)kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to
disk.
C)kill - 15 carries out a normal shutdown process, such as mysqladmin shutdown.
D)mysqld_safe prohibits commands that would harm the operation of the server. An error would be returned
by the kill command.
Answer:C

kill -15 系统会发送一个SIGTERM信号给对应的程序。当程序接收到该signal信号后，将会发生以下事情：
程序立刻停止
当程序释放相应资源后再停止
程序可能仍然继续运行
大部分程序接收到SIGTERM信号后，会先释放自己的资源，然后再停止。但是也有程序可能接收信号后，做一些其他的事情（如果程序正在等待IO，可能就不会立马做出响应，我在使用wkhtmltopdf转pdf的项目中遇到这现象），也就是说，SIGTERM多半是会被阻塞的。
所以相当于normal shutdown。
参考：https://www.cnblogs.com/jelly12345/p/18780693

Q145.Binary log events for the ‘mydb1’ schema must be copied to a different schema name ‘mydb2’ .
Which command will do this?
A)mysqlbinlog – rewrite-db=’ mydb1- >mydb2’ I mysql
B)mysqlbinlog --datebase=mydb1 --database=mydb2 lmysql
C)mysqlbinlog – rewrite-db=’ mydb1’ – rewrite-db=’ mydb2’ I mysql
D)mysqlbinlog --read-from= remote-server --rawl | sed’s/mydb1 /mydb2/g’ I mysql
Answer:A

--rewrite-db=name   Rewrite the row event to point so that it can be applied to a new database
这个选项会读取 binlog.000001 文件，将其中所有对 test_db 的操作重写为对 prod_db 的操作，并将结果通过管道传递给 mysql 客户端执行。
mysqlbinlog --rewrite-db='test_db->prod_db' binlog.000001 | mysql -u root -p

Q146.Which command enables rule-based MySQL Auditing capabilities?
A)shell> mysql < audit_log_filter_linux_install.sql
B)shell> mysqld --initialize --log- raw=audit. log
C)mysql>INSTALL PLUGIN audit_log;
D)mysql>INSTALL COMPONENT audit_log;
Answer:A

安装审计插件：
audit_log_filter_win_install.sql	windows
audit_log_filter_linux_install.sql  Linux

Q147.Examine this SQL statement:
mysql> GRANT r_read@localhost To mark WITH ADMIN OPTION;
Which two are true? (Choose two.)
A)Mark can grant the privileges assigned to the r_read@ localhost role to another user.
B)ADMIN OPTION causes the role to be activated by default.
C)Mark can grant the r_read@ localhost role to another user.
D)Mark can revoke the r_read@localhost role from another role.
E)ADMIN OPTION allows Mark to drop the role.
F)Mark must connect from localhost to activate the r_read@localhost role.
Answer:CD

选项A：WITH ADMIN OPTION 允许 Mark 将 r_read@localhost 角色授予其他用户。(角色就是一组权限，而不是将角色具有的权限付给其他用户,只能从这方面去理解，不然感觉A也是正确的，但是答案让选择两个，这个答案我不确定)
选项B：WITH ADMIN OPTION 与角色的默认激活无关。默认激活角色需要其他配置，例如 ACTIVATE ROLE 语句或设置 DEFAULT ROLE
选项C：正确，WITH ADMIN OPTION 允许 Mark 将 r_read@localhost 角色直接授予其他用户
选项D：WITH ADMIN OPTION 允许授予角色，但撤销角色通常需要 REVOKE 权限，这不在 ADMIN OPTION 的范围内。
选项E：删除角色需要 DROP ROLE 权限，WITH ADMIN OPTION 不提供此功能。
选项F：角色的激活与用户连接的主机无关。用户可以从任何地方连接，只要他们有权限激活角色。

Q148.Which four are types of information stored in the MySQL data dictionary? (Choose four.)
A)performance metrics
B)table definitions
C)access control lists
D)view definitions
E)server runtime configuration
F)server configuration rollback
G)stored procedure definitions
H)InnoDB buffer pool LRU management data
Answer:BCDG

MySQL数据字典存储一些数据库对象的定义信息等，而不存储监控指标，服务器的配置，以及innodb元数据等信息。所以BCDG是正确的

Q149.You have an InnoDB Cluster configured with three servers.
Examine this command, which executes successfully.
mysqldump -uroot -P -d mydatabase > mydatabase_backup .sq1
Due to data loss, the cluster is initialized and a restore is attempted resulting in this error:
ERROR 13176(HY000) at line 23: Cannot update GTID_PURGED with the Group Replication plugin running
Which two actions, either one of which, can fix this error and allow a successful restore of the cluster?(Choose two.)
A)Stop all instances except the primary read/write master instance and run the restore.
B)Remove the GLOBAL…gtid_purged statement from the dump file.
C)Create the backup by using the --set-gtid-purged=OFF option.
D)Remove the group replication plugin from each instance before restoring.
E)Remove the GLOBAL.gtid_executed statement from the dump file.
F)Restore using the --set-gtid-purged=OFF option.
Answer:BC

该报错就是在恢复的时候，执行备份文件中的set @@global_purged报错，所以需要将该段注释掉
那么就需要在备份的时候添加--set-gtid-purged=OFF 参数，或者直接从备份文件删掉这段

Q150.Which statement is true about MySQL Enterprise Transparent Data Encryption (TDE)?
A)MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location.
B)Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = ALL variable in the MySQL
configuration file.
C)Lost tablespace encryption keys can be regenerated only if the master database key is known or present in
the Key Vault specification.
D)TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace.
Answer:A