Q181.Which two statements are true about InnoDB data-at-rest encryption?
A.It does not support the transportable tablespaces feature.
B.It supports only non-blob datatypes.
C.It enforces encryption from disk to memory and over network transmission.
D.It decrypts data for use in memory
E.It supports all indexes transparently.
Answer:DE
选项A：InnoDB 数据静态加密支持可运输表空间功能，只要表空间是加密的，并且目标服务器上有可用的加密密钥
选项B：InnoDB 数据静态加密支持所有数据类型，包括 BLOB。
选项C：InnoDB 数据静态加密在数据写入磁盘前加密，读取时解密。数据在内存中是解密的，网络传输中的数据默认是明文的，除非另外启用了 SSL/TLS 加密。
选项D：它在内存中解密数据供使用。正确。InnoDB 数据静态加密在数据读取到内存时会解密，以便应用程序可以正常使用数据。
选项E：它透明地支持所有索引。正确。InnoDB 数据静态加密透明地支持所有类型的索引，包括主键、唯一索引、全文索引和空间索引
Q182.Examine this statement, which executes successfully:
CREATE TABLE rental(
rental_id int unsigned NOT NULL AUTO_INCREMENT,
rental_date datetime NOT NULL,
inventory_id int unsigned NOT NULL,
customer_id int unsigned NOT NULL,
return_date datetime DEFAULT NULL,
staff_id int unsigned NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (rental_id)
) ENGINE=Innodb
Now examin this query:
SELECT rental_id,customer_id
FROM rental
WHERE rental_date BETWEEN NOW() - INTERVAL 1 MONTH AND NOW()
AND inventory_id=42
AND staff_id=1024;
You want to add one or more indexes that minimize the work done by the query.
Which statement accomplishes this?
A.ALTER TABLE rental ADD INDEX (inventory_id, staff_id, customer_id, rental_id, rental_date);
B.ALTER TABLE rental ADD INDEX (inventory_id) ADD INDEX (staff_id),ADD INDEX (rental_date),ADD INDEX (customer_id);
C.ALTER TABLE rental ADD INDEX (inventory_id),ADD INDEX (staff_id),ADD INDEX (rental_date);
D.ALTER TABLE rental ADD INDEX (rental_date, inventory_id, staff_id, customer_id);
E.ALTER TABLE rental ADD INDEX (inventory_id, staff_id, rental_date, customer_id);
F.ALTER TABLE rental ADD INDEX (inventory_id, staff_id, rental_date);
Answer:C
where 条件中的字段不包含所有的select中的字段，所以无法无论创建什么索引，都需要回表，所以给每个字段加索引和三个字段加联合索引的可能更好一些，但是需要注意联合索引的顺序。个人理解应该选F，
Q183.Examine this statement, which executes successfully:
CREATE USER mary@192.0.2.100 IDENTIFIED BY 'P@SSword' REQUIRE NONE PASSWORD EXPIRE;
创建用户，设置密码，并设置过期
Which two are true?
A. Mary cannot query data until she changes her password.
B. Mary requires no password to connect to the MySOL server.
C. Mary cannot connect to the MysQL server until the DBA resets her password.
D. Mary must connect from the client machine 192.0.2.100.
E. Mary must connect using the username ‘mary@192.0.2.100’
Answer:CD
该用户只能从192.0.2.100上连接
该用户创建时指定立即过期，所以需要修改密码之后才能连接
REQUIRE NONE:https://dev.mysql.com/doc/refman/8.0/en/create-user.html
Q184.t is a non-empty InnoDB table.
Examine these statements, which are executed in one session:
BEGIN;
SELECT * FROM t FOR UPDATE;
Which is true?
A. If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed
or rolled back.
B. If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback
C. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.
D. mysqlcheck --analyze --all-databases will execute normally on all tables and return a Report
Answer:C
SELECT * FROM t FOR UPDATE;会产生一个表级排他锁
选项A：同一个会话不受影响，测试结果如图
optimiz和analyze都会去获取表锁，但select * from t for update;已经在表上添加了一个排他的表锁，所以其他的事务会等待
选项B：他不会强制让其他食物回滚
选项C：经过测试，会被hang住
选项D：mysqlcheck --analyze会做实际的analyze操作，所以也会被卡住
OPTIMIZE TABLE 选项：
NO_WRITE_TO_BINLOG 或LOCAL：不记录二进制日志
这几个选项都有点问题，不知道选哪个
Q185.What does the binlog dump thread do?
A) It monitors and schedules the rotation/deletion of the binary logs.
B) It reads the relay log and executes the events contained in them.
C) It acquires a lock on the binary log for reading each event to be sent to the slave.
D) It connects to the master and asks it to send updates recorded in its binary logs.
Answer:C
二进制日志转储线程( Binlog dump thread )是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件(Event )的时候，会在 Binlog 上加锁，读取完成之
后，再将锁释放掉。
Q186.You must replay the binary logs on your MysQL server.
Which command do you use?
A) mysqlbinlog binlog.000003 binlog.000004 binlog.000005 | mysql -h 127.0.0.1
B) mysqlbinlog -h 127.0.0.1 binlog.000003 binloq.000004 binloq.000005
C) mysqlpump -h 127.0.0.1 binlog. 000003 binlog.000004 binlog.000005
D) cat binlog. 000003 binlog. 000004 binlog.000005 | mysql -h 127.0.0.1
E) mysql -h 127.0.0.1 --local-infile binlog.000003 binlog.000004 binloq.000005
Answer:B
选项A：mysqlbinlog解析后通过管道 使用mysql -h连接后执行，是正确的
选项B：mysqlbinlog -h 是从-h指定的服务端获取binlog进行解析，所以该选项是错误的
选项C：mysqlpump是逻辑备份
选项D：binlog是二进制文件，不能直接cat
选项E：mysql没有解析binlog的功能 --local-infile是启用是否使用load data local infile加载文件的参数
Q187.Examine these commands and output:
Which connection ID is holding the metadata lock?
A.6
B.22
C.20
D.21
E.24
F.25
Answer:A
从第二查询可以看到thread_id=60的线程持有锁，thread_id=62的线程在等待
再结合第三个查询thread_id=60对应的processlist_id是20。所以正确答案应该是C
Q188.Which statement is true about displaying and retrieving data with MySQL Enterprise Monitor Query
Analyzer?
A.It is possible to export statements included in a graph selection in CSV format.
B.The Query Analyzer graph view range selector can extend to cover the same hour over multiple
days
C.It is possible to filter a Query Analyzer view graph by database and by table.
D.The Query Analyzer can plot a CPU utilization graph for remote hosts with a MySQL Enterprise
Service Manager’s built-in Agent installation.
Answer:D
选项A：可以将图形选择中包含的语句导出为 CSV 格式。Query Analyzer 的图形视图选择器允许用户导出所选时间范围内的查询数据，并支持将这些数据导出为 CSV 格式。
选项B:Query Analyzer 的时间范围选择器允许选择从一小时到两天的时间范围，但没有提到可以覆盖多天的同一小时范围。
选项C：Query Analyzer 支持按数据库过滤查询，但不能按表过滤的功能。
选项D：Query Analyzer 主要关注查询分析，不涉及远程主机的 CPU 使用率图表
所以正确答案应该是A
Q189.Which three statements are true about data dictionary in MySQL Server?
A. It auto detects when a new schema is moved into datadir.
B. It facilitates fast DDL by using transactional storage.
C. It stores frequently used query plans.
D. It uses the transactional storage engine
E. It is based on SQL standards
F. It provides data to INFORMATION_SCHEMA.
Answer:BDF
选项A：：数据字典本身不负责检测新的schema等文件是否被移动到 datadir
选项B：数据字典使用事务性存储引擎（如 InnoDB）来存储其元数据，这使得 DDL 操作可以快速执行且保持一致性
选项C：数据字典主要用于存储数据库对象的元数据，而不是查询计划。查询计划通常由查询优化器生成并缓存
选项D：数据字典在 MySQL 8.0 及更高版本中使用 InnoDB 作为其存储引擎，InnoDB 是事务性的。
选项E：数据字典的设计和实现可能不完全遵循所有 SQL 标准，尽管它提供了一些标准兼容的功能
选项F：INFORMATION_SCHEMA 是 MySQL 的信息模式数据库，它提供了数据库元数据的访问接口，而这些元数据存储在数据字典中
Q190.Which two tasks are performed by the mysql_secure_installation programe?
A. It downloads the latest MySQL software over a secure connection and installs it
B. It checks whether the hash value on downloaded software from MySQL repositories matches the
official count.
C. It requires setting a password for the root account.
D. It removes anonymous accounts.
E. It checks whether all account passwords match the server’s established security level.
F. It properly sets the file permissions and file ownership for MysQL server files.
Answer:CD
mysql_secure_installation 程序可以增强 MySQL 安装的安全性，主要通过以下方式实现：
设置 root 账户密码：可以为 root 账户设置密码，增强账户安全性。
移除远程可访问的 root 账户：删除那些可以从本地主机之外的地方访问的 root 账户，防止外部恶意访问。
移除匿名用户账户：删除匿名用户账户，避免匿名用户可能带来的安全风险。
移除测试数据库及相关权限：默认情况下，测试数据库可以被所有用户（包括匿名用户）访问，该程序可以移除这个数据库以及允许任何人访问以 "test_"开头的数据库的权限。
参考：https://dev.mysql.com/doc/refman/8.0/en/mysql-secure-installation.html