MySQL 8.0 OCP 1Z0-908 21-30题

Q21.You encountered an insufficient privilege error in the middle of a long transaction.
The database administrator is informed and immediately grants the required privilege:
GRANT UPDATE ON world.city TO 'user1';
How can you proceed with your transaction with the least interruption?
A)Roll back the transaction and start the transaction again in the same session.
B)Change the default database and re-execute the failed statement in your transaction.
C)Re-execute the failed statement in your transaction.
D)Close the connection, reconnect, and start the transaction again.
Answer:C
在长事务中，遇到权限不足的时候，赋予权限后，重新执行这个失败的语句就好了

//正在运行 MySQL 8.0 的现有异步复制设置中，哪两个步骤是实施基于组提交 ID（GTID）复制的一部分
Q22.An existing asynchronous replication setup is running MySQL 8.0 Which two steps are a part of implementing GTID replication?
A)On the slave, alter the MySQL master connection setting with:
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
//如果进行change master to时使用MASTER_AUTO_POSITION = 1，slave连接master将使用基于GTID的复制协议。
使用基于GTID协议的复制，slave会告诉master它已经接收到或执行了哪些事务。计算这个集，slave需要读取全局参数gtid_executed以及通过show slave status获取的参数Retrieved_gtid_set，此处引用了：https://www.cnblogs.com/bdicaprio/articles/9877525.html
B)Execute this on the slave to enable GTID:
RESET SLAVE; START SLAVE GTID_NEXT = AUTOMATIC; //
C)Enable GTID by executing this on the master and the slave:
SET GLOBAL GTID_ENABLED = on;
D)Restart MySQL (master and slave) with these options enabled: //打开GTID相关参数
--gtid_mode=ON
--log-bin
--log-slave-updates
--enforce-gtid-consistency
E)On the slave, alter the MySQL master connection setting with:
ALTER channel CHANGE MASTER TO MASTER_AUTO_POSITION = 1;该命令是适用于多通道的，个人理解这一条是新的复制通道，新的复制通道打开了GTID，而原有的复制通道没有改变
F)Execute this on the slave to enable GTID: START SLAVE IO_THREAD WITH GTID;这个是修改的IO线程
Answer:AD

Q23.You are using mysqlcheck for server maintenance.
Which two statements are true?
A)The mysqlcheck --optimize --all-databases command reclaims free space from table files. optimize会整理碎片，回收空闲空间
B)The mysqlcheck --analyze --all-databases command performs a series of checks to spot eventual table
corruptions. //只会Analyze给出的表，或者给出的数据库的表，也就是更新统计信息，如果需要检查表需要加--check选项
C)The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default.
与其他客户端程序相比，mysqlcheck有一个特殊功能。通过重命名二进制文件，可以更改其默认的查表行为（--check）。如果想要一个默认修复表的工具，只需将mysqlcheck复制并命名为mysqlrepair，或创建一个指向mysqlcheck且名为mysqlrepair的符号链接。若调用mysqlrepair，它就会修复表。参考：https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html
D)The mysqlcheck --repair --all-databases command can repair an InnoDB corrupted table.
//对于innodb表，只能检查不能修复
E)The mysqlcheck --check -all-databases command takes table write locks while performing a series of checks.//--check为检查表，检查表期间只会加上读锁，参考：https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html#option_mysqlcheck_check
Answer:AC

Q24.Examine this statement:

此处主要参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/drop-role.html
mysql> DROP ROLE r_role1, r_role2;
Which two are true?
A)It fails if at least one of the roles does not exist.

官方文档：
DROP ROLE either succeeds for all named roles or rolls back and has no effect if any error occurs. By default, an error occurs if you try to drop a role that does not exist. If the IF EXISTS clause is given, the statement produces a warning for each named role that does not exist, rather than an error.

DROP ROLE语句的执行行为

• 成功或回滚：DROP ROLE语句要么成功删除所有指定的角色，要么在发生任何错误时回滚，没有任何效果。
• 默认行为：默认情况下，如果尝试删除不存在的角色，会报错。
• IF EXISTS子句：如果提供了IF EXISTS子句，对于每个不存在的角色，语句会生成一个警告，而不是报错。

B)You must revoke all privileges from r_role1 and r_role2 before dropping the roles.

官方文档：

A dropped role is automatically revoked from any user account (or role) to which the role was granted.

删除的角色会自动从任何被授予该角色的用户账户（或角色）中撤销。

C)Existing connections can continue to use the roles' privileges until they reconnect.

Within any current session for such an account, its adjusted privileges apply beginning with the next statement executed.

对于此类账户的任何当前会话，其调整后的权限将从执行的下一个语句开始生效。

D)You must revoke r_role1 and r_role2 from all users and other roles before dropping the roles.//参考B选项解释

E)It fails if you do not have the ADMIN OPTION of the roles r_role1 and r_role2.

使用DROP ROLE语句的权限要求

• 基本权限：要使用DROP ROLE语句，必须具有全局的DROP ROLE权限或CREATE USER权限。
• 额外权限：当read_only系统变量被启用时，DROP ROLE语句还需要CONNECTION_ADMIN权限（或者已废弃的SUPER权限）。

不同权限用户的使用限制

• CREATE USER权限用户：从MySQL 8.0.16开始，具有CREATE USER权限的用户可以使用DROP ROLE语句删除锁定或未锁定的账户。
• DROP ROLE权限用户：具有DROP ROLE权限的用户只能使用DROP ROLE语句删除锁定的账户（未锁定的账户通常用于登录服务器，不仅仅是作为角色）。

F)It fails if any of the roles is specified in the mandatory_roles variable.

Roles named in the mandatory_roles system variable value cannot be dropped.

• mandatory_roles系统变量指定的角色：在mandatory_roles系统变量中列出的角色不能被删除。

Answer:AF

Q25.You have a MySQL client installed on your Linux workstation with a default installation. You have your admin
login credentials to connect to a MySQL server running Microsoft Windows on remote host 192.0.2.1:3306 to
connect to the world database.
Which four options need to be specified to complete this task with a single command?
数据库远程连接：要指定--user,--password,--host --database 其余的可以有默认值，必须明确指定的就是这几个选项
A)--port=3306
B)--protocol=UDP
C)--database=world
D)--user=admin
E)--password
F)--protocol=pipe
G)--host=192.0.2.1
H)--socket=/tmp/mysql.sock
I)--shared-memory-base-name=world
Answer:CDEG

Q26.You want to check the values of the sort_buffer_size session variables of all existing connections. Which
performance_schema table can you query?
要查看所有会话中sort_buffer_size的值
A)global_variables //global_variables 表存储的是全局系统变量。这些变量对整个数据库系统有效，适用于所有会话
B)session_variables //session_variables 表存储的是当前会话的系统变量。这些变量对当前会话有效，可能会覆盖全局变量的值。
C)variables_by_thread //variables_by_thread 表存储的是每个活跃会话的会话系统变量。这些变量对特定的会话有效，通过线程ID（thread ID）来标识。
D)user_variables_by_thread //该表可以展示用户自定义变量。这些变量是在特定会话中定义的，并且其名称前面有一个"@"字符。
Answer:C

Q27.Your my.cnf file contains these settings:
[mysqld]
log_output=FILE
slow_query_log
long_query_time=2.01 //设置记录慢查询的标准，多长时间的查询会记录
log_queries_not_using_indexes //该参数启用会记录未使用索引的慢日志记录
You want to log queries that looked at a minimum of 5000 records and either took longer than 5 seconds to run or
did not use indexes. //题目：您希望记录至少查看了5000条记录，并且运行时间超过5秒或者没有使用索引的查询。所以min_examined_row_limit=5000必须要有，然后就是慢查询时间改为5
Which contains all the settings that you need to add to or modify the slow log configuration?
A)log_throttle_queries_not_using_indexes=5
B)long_query_time=5 log_throttle_queries_not_using_indexes=5
C)long_query_time=5
D)long_query_time=5 log_throttle_quries_not_using_indexes=5 min_examined_row_limit=5000
E)long_query_time=5 min_examined_row_limit=5000 //min_examined_row_limit对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中；
F)min_examined_row_limit=5000
G)log_throttle_quries_not_using_indexes=5 min_examined_row_limit=5000 //log_throttle_quries_not_using_indexes用于记录在一定的时间内，执行没有使用索引的查询的次数，
Answer:E

Q28.Examine this command and output:
mysql>SELECT * FROM data_locks LIMIT 1\G
**************************************************************************
 ENGINE: INNODB
 ENDINE_LOCJK_I: 1200:146
ENGINE_TRANSACTION_ID: 1200
 THREAD_ID:45
ECENT_ID:11
OBJECT_SCHEMA: mydb
OBJECT_NAME: mytable1
PARTITION_NAME: NULL
SUSPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN:118793337250203
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1922,192
Which two statements are true?
A)The lock is an intentional lock.
B)The lock is a shared lock.
C)The lock is at the metadata object level.
D)The lock is an exclusive lock.
E)The lock is a row-level lock.
F)The lock is at the table object level.
Answer:DF
个人理解LOCK_TYPE=RECORD所以是行级锁，F是错误答案，应该选择DE

Q29.A clean shutdown was performed with innodb_fast_shutdown=0.
While you were manipulating files, all files were accidentally deleted from the top-level data directory. Which two
files must be restored from backup to allow the DB to restart cleanly?
A)ibdata1 //ibdata1为系统表空间文件，存放一些关于数据库的元数据等信息
B)mysql.ibd //MySQL的系统表和数据字典表存放在该文件
C)ib_logfile0
D)ibtmp1
E)ib_buffer_pool
F)undo_001
Answer:AB
 

Q30.Which two statements are true about MySQL Enterprise Backup?
A)It supports the creation of incremental backups.
B)It creates logical backups.  //物理备份工具MEB是物理备份工具
C)It supports restoring to a remote MySQL system.
D)It supports backing up only table structures. //物理备份工具
E)It supports backup of a remote MySQL system.
F)It can perform hot or warm backups.
Answer:AF