MySQL
MySQL高可用配置
MySQL Slave状态参数详解
基于二进制日志文件的复制
使用全局事务标识符GTID复制
MySQL安装升级
MySQL RPM包安装参考
MySQL编译化安装参考
MySQL Server版本升级
MySQL 操作手册/说明
MySQL导入导出操作
库,表,字段的字符集修改方法
根据字段生成自定义SQL语句
MySQL查询流程概述
MySQL 常用脚本
MySQL数据库冷备脚本
MySQL内存占用分析
MySQL内存消耗分析
检查MySQL引起的高内存占用
InnoDB下的内存分析和优化计算
MySQL常见问题
MySQL告警：Aborted connection日志的分析
MySQL使用命令kill进程后出现killed死锁问题
从库重起初始化relaylog失败
MySQL 8.0 报错 “The table '/tmp/#sqlxxx' is full”
安全插件Connection-Control导致无法登录的问题
MySQL题库资料
MySQL 8.0版本OCP证书题库(1Z0-908)
MySQL性能优化
MySQL查询慢性能分析
本文档使用 MrDoc 发布
-
+
首页
MySQL 8.0版本OCP证书题库(1Z0-908)
## 试题 1 Choose two. Your MySQL server was upgraded from an earlier major version.The sales database contains three tables, one of which is the transactions table, which has 4 million rows. You are running low on disk space on the datadir partition and begin to investigate. Examine these commands and output:（见图片） Which two statements are true? ```sql mysql> show global variables like 'innodb_file%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) # ls -l | grep ib -rw-r----- 1 mysql mysql 3287 Dec 12 07:54 ib_buffer_pool -rw-r----- 1 mysql mysql 125827192912 Dec 12 09:50 ibdata1 -rw-r----- 1 mysql mysql 50331648 Dec 12 09:50 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Dec 11 14:05 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Dec 12 08:05 ibtmp1 -rw-r----- 1 mysql mysql 25165824 Dec 12 09:50 mysql.ibd # ls -l sales/ total 544 -rw-r----- 1 mysql mysql 47550136 Dec 12 09:50 sales.ibd -rw-r----- 1 mysql mysql 114688 Dec 11 14:33 leads.ibd ``` 1. Executing SET GLOBAL innodb_row_format=COMPRESSED and then ALTER TABLE transactions will free up disk space. [错误] 2. Truncating the sales and leads table will free up disk space. [正确] 3. The transactions table was created with innodb_file_per_table=OFF. [正确] 4. Executing ALTER TABLE transactions will enable you to free up disk space. [错误] 5. Truncating the transactions table will free up the most disk space. [错误] >题目考查表空间管理。transactions 表是在 innodb_file_per_table=OFF 模式下创建的，因此其数据存储在共享表空间 ibdata1 中(否则每个表使用独立 .ibd 文件存储)，无法通过 ALTER 或 TRUNCATE 操作释放磁盘空间。而 sales 和 leads 表可通过 TRUNCATE 释放空间，前提是它们使用独立表空间。SET GLOBAL innodb_row_format 语法错误，ALTER 表也无法释放共享空间。 ## 试题 2 ['title', 'EXPLAIN'] ```sql *************************** 1. row *************************** EXPLAIN: -> Sort: <temporary>.Population DESC (actual time=8.306..8.431 rows=125 loops=1) -> Stream results (actual time=0.145..8.033 rows=125 loops=1) -> Nested loop inner join (cost=241.12 rows=205) (actual time=0.141..7.787 rows=125 loops=1) -> Filter: (world.country.Continent = 'Asia') (cost=25.40 rows=34) (actual time=0.064..0.820 rows=51 loops=1) -> Table scan on country (cost=25.40 rows=239) (actual time=0.059..0.359 rows=239 loops=1) -> Filter: (world.city.Population > 1000000) (cost=4.53 rows=6) (actual time=0.030..0.131 rows=2 loops=51) -> Index lookup on city using CountryCode (CountryCode=world.country.Code) (cost=4.53 rows=18) (actual time=0.023..0.096 rows=35 loops=51) 1 row in set (0.0094 sec) ``` 1. 35 rows from the city table are included in the result. [错误] 2. The query returns exactly 125 rows. [正确] 3. The optimizer estimates that 51 rows in the country table have Continent = ' Asia '. [错误] 4. It takes more than 8 milliseconds to sort the rows. [错误] 5. The country table is accessed as the first table, and then joined to the city table. [正确] >题目考查执行计划理解。输出显示返回结果为 125 行，说明选项 2 正确；Nested Loop 中先对 country 表进行全表扫描并过滤，再使用 city 表上的索引连接，因此选项 5 正确。city 实际返回 35 行，非最终结果；country 表预测匹配 34 行，而非 51 行；排序耗时约 0.125 毫秒，不超过 8 毫秒。 ## 试题 3 Choose four. A newly deployed replication master database has a 10/90 read to write ratio. The complete dataset is currently 28G but will never fluctuate(波动) beyond +-10%.The database storage system consists of two locally attached PCI- E Enterprise grade disks (mounted as /data1 and /data2)The server is dedicated to this MySQL Instance. System memory capacity is 64G.The my.cnf file contents are displayed here: ``` [mysqld] datadir=/data1/ innodb_buffer_pool_size=28G innodb_log_file_size=150M ``` Which four changes provide the most performance improvement, without sacrificing(牺牲) data integrity? 1. innodb_log_group_home_dir=/data2/ [正确] 2. innodb_buffer_pool_size=32G [正确] 1. innodb-doublewrite=off [错误] 2. innodb_flush_log_at_trx_commit=0 [错误] 3. innodb_undo_directory=/dev/shm [错误] 4. innodb_log_file_size=1G [正确] 5. sync_binlog=0 [错误] 6. log-bin=/data2/ [正确] 7. disable-log-bin [错误] >本题考查在保障数据完整性的前提下，如何优化写入性能。正确选项包括：将 redo log 移至 /data2（innodb_log_group_home_dir=/data2），避免与数据文件 I/O 冲突；增加 innodb_buffer_pool_size 至 32G，提高缓存命中率；将 innodb_log_file_size 增至 1G，减少写盘频率；将 binlog 存储位置改为 /data2（log-bin=/data2/），提升 I/O 并发性能。以上优化均在不影响数据一致性的前提下显著提升性能。错误选项如关闭双写缓冲、将 undo 日志放入内存、关闭 binlog 同步等，虽可提升性能，但存在数据丢失风险。 ## 试题 4 Which two actions will secure a MySQL server from network-based attacks? 1. Change the listening port to 3307. [错误] 2. Allow connections from the application server only. [正确] 3. Use MySQL Router to proxy connections to the MySQL server. [错误] 4. Place the MySQL instance behind a firewall. [正确] 5. Use network file system (NFS) for storing data. [错误] >题目考查如何防范基于网络的 MySQL 攻击。正确选项包括：**仅允许应用服务器访问数据库（2）**，可限制来源 IP，有效减少攻击面；**将 MySQL 实例置于防火墙之后（4）**，通过端口和地址控制增强安全性。更改监听端口（1）仅具微弱安全意义；使用 MySQL Router（3）主要用于高可用性和负载均衡，不具防护作用；使用 NFS 存储（5）反而可能引入网络层风险。 ## 试题 5 Choose four. You must store connection parameters for connecting a Linux-based MySQL client to a remote Windows-based MySQL server listening on port 3309.Which four methods can be used to configure user, host, and database parameters? 1. Execute the mysqladmin command to configure the user connection. [错误] 2. Configure ~/.my.cnf. [正确] 3. Use the usermod program to store static user information. [错误] 4. Define a UNIX socket. [错误] 1. Execute the command in a bash script. [正确] 2. Execute mysql_config_editor to configure the user connection. [正确] 3. Embed login information into the SSH tunnel definition. [错误] 7. Configure ~/.ssh/config for public key authentication. [错误] 8. Configure environment variables. [正确] >本题考查在 Linux 客户端上如何配置连接远程 MySQL 服务器的参数。正确方法包括：配置 `~/.my.cnf` 存储连接信息（如用户、主机、端口）；通过 bash 脚本执行连接命令并内嵌参数；使用 `mysql_config_editor` 安全保存连接凭据；以及使用环境变量定义连接参数。其他选项如 mysqladmin、usermod、UNIX socket、SSH 配置文件等不适用于 MySQL 客户端连接参数配置。 ## 试题 6 Choose two. Examine this statement, which executes successfully: ``` CREATE TABLE employees ( emp_no INT UNSIGNED NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (emp_no) ) ENGINE=InnoDB; ``` Now examine this query: `SELECT emp_no, first_name, last_name, birth_date FROM employees WHERE MONTH(birth_date) = 4;` You must add an index that can reduce the number of rows processed by the query. Which two statements can do this? 1. ALTER TABLE employees ADD INDEX (birth_date); [错误] 2. ALTER TABLE employees；ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date；->>'$.month') VIRTUAL NOT NULL, ADD INDEX (birth_month) ; [错误] 3. ALTER TABLE employees；ADD INDEX ((CAST (birth_date ->>'$.month' AS unsigned))); [错误] 4. ALTER TABLE employees；ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH (birth_date)) VIRTUAL NOT NULL, ADD INDEX (birth_month) ; [正确] 5. ALTER TABLE employees；ADD INDEX ((MONTH (birth_date))); [正确] 6. ALTER TABLE employees；ADD INDEX (birth_date DESC) ; [错误] > 本题考查如何为包含函数计算的查询添加有效索引。选项 4 和 5 是正确答案：选项 4 通过添加虚拟生成列 birth_month 保存 MONTH(birth_date) 的结果，并创建索引，查询可直接使用索引值，避免逐行函数计算；选项 5 在 MySQL 8.0 中支持表达式索引，直接对 MONTH(birth_date) 建索引，同样能提升查询性能。其他选项如对 birth_date 建索引或使用无效 JSON 表达式均无法被优化器用于此类函数查询。 ## 试题 7 Choose two. Which two queries are examples of successful SQL injection attacks? 1. SELECT id, name FROM user WHERE id=23 OR id=32 AND 1=1; [错误] 2. SELECT user, passwd FROM members WHERE user = ' ? ' ; INSERT INTO members ('user' , 'passwd' ) VALUES ('bob@example.com' , 'secret' ) ;-- '; [正确] 3. SELECT id, name FROM user WHERE id=23 OR id=32 OR 1=1; [错误] 4. SELECT id, name FROM user WHERE user.id= (SELECT members.id FROM members) ; [错误] 5. SELECT email, passwd FROM members WHERE email = 'INSERT INTO members('email' , ' passwd ' ) VALUES ('bob@example.com', 'secret') ;-- '; [错误] 6. SELECT user, phone FROM customers WHERE name = ' \\; DROP TABLE users; -- '; [正确] >本题考查识别 SQL 注入攻击的能力。选项 2 和 6 是成功的 SQL 注入示例：选项 2 利用未转义输入注入 INSERT 语句，执行了额外的数据写入操作；选项 6 使用分号和注释符构造 DROP TABLE 攻击，具有明显破坏性。其他选项中仅包含扩展查询逻辑或语法无漏洞，不属于成功注入。 ## 试题 8 Choose two. Which two tools are available to monitor the global status of InnoDB locking? 1. INFORMATION_SCHEMA.INNODB_METRICS [正确] 2. SHOW ENGINE INNODB STATUS; [正确] 3. SHOW STATUS; [错误] 4. INEORMATION_SCHEMA.INNODB_TABLESTATS . [错误] 5. INFORMATION_SCHEMA.STATISTICS [错误] 6. SHOW TABLE STATUS; [错误] > 本题考查监控 InnoDB 全局锁信息的工具。选项 1 和 2 是正确答案：INFORMATION_SCHEMA.INNODB_METRICS 提供多项 InnoDB 内部指标，包括锁等待相关信息；SHOW ENGINE INNODB STATUS 可显示当前锁等待、死锁和事务详情，是诊断锁问题的常用工具。其他选项如 SHOW STATUS 或 INNODB_TABLESTATS 等仅提供表级或统计信息，无法反映锁的全局状态。 ## 试题 9 Choose two. Which two authentication plugins require the plain text client plugin for authentication to work? 1. PAM authentication [正确] 2. LDAP SASL authentication [错误] 3. Windows Native authentication [错误] 4. SHA256 authentication [错误] 5. MySQL Native Password [错误] 6. LDAP authentication [正确] > 本题考查哪些认证插件需要客户端安装明文传输插件（plain text client plugin）才能正常工作。正确答案为选项 1 和 6：PAM 认证和 LDAP 认证通常需要客户端以明文方式发送凭据，因此依赖 plain text client 插件。其他选项如 LDAP SASL、Windows Native、SHA256 和 MySQL Native Password 采用加密或系统集成认证方式，不依赖该插件。 ## 试题 10 Which three are types of information stored in the MySQL data dictionary? 1. access control lists [正确] 2. server runtime configuration [错误] 3. view definitions [正确] 4. server configuration rollback [错误] 5. performance metrics [错误] 6. stored procedure definitions [正确] 7. InnoDB buffer pool LRU management data [错误] > 本题考查 MySQL 数据字典中存储的内容类型。正确答案是选项 1、3 和 6：数据字典包含访问控制列表（ACL）、视图定义以及存储过程定义，这些都是数据库的元数据。其他选项如运行时配置、性能指标、InnoDB 缓存管理数据等属于运行时信息或状态信息，不保存在数据字典中。 ## 试题 11 Choose four。 Which four are types of information stored in the MySQL data dictionary? 1. access control lists [正确] 2. view definitions [正确] 3. server runtime configuration [错误] 4. performance metrics [错误] 5. server configuration rollback [错误] 6. InnoDB buffer pool LRU management data [错误] 7. table definitions [正确] 8. stored procedure definitions [正确] > 本题考查 MySQL 数据字典中存储的内容类型。正确答案为选项 1、2、7 和 8：数据字典用于持久化存储数据库的元数据，包括访问控制列表（ACL）、视图定义、表定义和存储过程定义。其余选项如运行时配置、性能指标和 InnoDB 缓存管理数据属于非元数据范畴，不存储于数据字典中。 ## 试题 12 Choose two. Examine this SQL statement: ``` mysql> GRANT r_read@localhost TO mark WITH ADMIN OPTION; ``` Which two are true? 1. Mark can grant the privileges assigned to the r_read@localhost role to another user. [错误] 2. Mark must connect from localhost to activate the r_read@localhost role. [错误] 3. Mark can grant the r_read@localhost role to another user. [正确] 4. Mark can revoke the r_read@localhost role from another role. [正确] 5. ADMIN OPTION allows Mark to drop the role. [错误] 6. ADMIN OPTION causes the role to be activated by default. [错误] > 本题考查 GRANT 语句中 WITH ADMIN OPTION 的含义。正确答案是选项 3 和 4：WITH ADMIN OPTION 允许用户将该角色授权给其他用户或角色，因此 Mark 可以将 r_read@localhost 角色授予他人，也可以从其他角色中撤销该角色。该选项不代表 Mark 拥有该角色所含的具体权限，也不默认激活角色或允许删除角色。 ## 试题 13 Choose two. Which two statements are true about general tablespaces? 1. Dropping a table from a general tablespace releases the space back to the operating system. [错误] 2. A general tablespace can have multiple data files. [错误] 3. General tablespaces support temporary tables. [错误] 4. A new table can be created explicitly in a general tablespace. [正确] 5. An existing table can be moved into a general tablespace. [正确] > 选项1是错误的。通用表空间中的表被删除时，其占用的空间并不会自动释放回操作系统，而是留在表空间中供将来使用。 > 选项2是错误的。一个通用表空间只能包含一个数据文件，不能像某些其他类型的表空间那样支持多个数据文件。 > 选项3是错误的。通用表空间不支持临时表，临时表必须放在临时表空间中。 > 选项4是正确的。创建新表时可以明确指定将其放入某个通用表空间中，这是通用表空间设计的一部分。 > 选项5是正确的。现有的表可以通过操作（如ALTER TABLE ... TABLESPACE）移动到通用表空间中，实现表空间之间的迁移。 ## 试题 14 Which three methods are part of a 'scale up' approach to capacity planning? 1. adding more storage to your disk array [正确] 2. adding a new node to InnoDB Cluster [错误] 3. adding more RAM [正确] 4. adding a replication slave [错误] 5. sharding the server into a parallel server farm [错误] 6. adding more CPU power [正确] 7. adding additional MySQL servers to the existing host [错误] > “纵向扩展”（scale up）是指通过增强单个服务器的硬件资源来提升系统处理能力的方法，而不是增加更多服务器节点。增加磁盘阵列的存储容量属于典型的纵向扩展方式，因为它直接扩展了现有系统的存储能力。增加内存可以提高系统缓存能力和处理大量数据的能力，也是纵向扩展的重要手段。提升CPU性能，如使用更快的处理器或增加CPU核心数，也能显著增强系统整体性能，属于纵向扩展的核心措施。相比之下，增加InnoDB Cluster节点、添加复制从节点、进行分片或在同一主机上增加多个MySQL实例，都是“横向扩展”（scale out）策略，不属于纵向扩展范畴。 ## 试题 15 Choose three. A user wants to connect without entering his or her username and password on the Linux command prompt. Which three locations can be used to store the user's mysql credentials to satisfy this requirement? 1. $HOME/.mysqlrc file [错误] 2. $MYSQL_HOME/my.cnf file [错误] 3. DATADIR/mysqld-auto.cnf file [错误] 4. /etc/my.cnf file [正确] 5. $HOME/.mylogin.cnf file [正确] 6. $HOME/.mysql/auth/login file [错误] 7. $HOME/.my.cnf file [正确] 8. $MYSQL_HOME/my.cnf Server-specific options (server only) > 为了让用户在Linux命令行中连接MySQL时无需手动输入用户名和密码，可以将凭据保存在特定的配置文件中。/etc/my.cnf 是系统级配置文件，可以为所有用户提供默认的连接凭据。\$HOME/.my.cnf 是用户级配置文件，可以在该文件中使用明文方式设置用户名和密码，仅对该用户生效。\$HOME/.mylogin.cnf 是MySQL提供的加密登录配置文件，通过 mysql\_config\_editor 工具生成，用于安全地保存凭据，同样支持无密码连接。而其他路径如 \$HOME/.mysqlrc、\$MYSQL\_HOME/my.cnf、DATADIR/mysqld-auto.cnf 以及 \$HOME/.mysql/auth/login 要么是不存在的标准配置路径，要么用途不同，不能用于保存用于登录的凭据信息。 ## 试题 16 choose two. Examine the modified output: ``` mysql> SHOW SLAVE STATUS \\G ******************1. row******************** Slave_IO_Running:Yes Slave_SQL_Running:Yes Seconds_Behind_Master:1612 ``` Seconds_Behind_Master value is steadily growing. What are two possible causes? 1. This value shows only I/O latency and is not indicative of the size of the transaction queue. [错误] 2. The master is producing a large volume of events in parallel but the slave is processing them serially. [正确] 3. One or more large tables do not have primary keys. [错误] 4. The parallel slave threads are experiencing lock contention. [错误] 5. The master is most probably too busy to transmit data and the slave needs to wait for more data. [正确] > Seconds\_Behind\_Master 指示的是从库相对于主库的复制延迟时间，如果该值持续增长，说明从库正在落后于主库。主库生成大量并行事件而从库只能串行处理，是常见原因之一，因为传统的从库SQL线程是单线程执行，这会导致处理速度跟不上主库写入速度。另外，如果主库负载过高，可能会出现延迟将数据发送给从库的情况，这也会导致从库等待数据，从而增加复制延迟。其他选项中，I/O延迟并不能完全代表 Seconds\_Behind\_Master 的含义，它还与SQL线程执行有关；表缺失主键可能影响复制效率但不会直接导致该值持续增长；并行线程锁竞争一般会导致复制阻塞而不是稳定增长延迟值，因此不是主要原因。 ## 试题 17 Choose two. Which two are true about binary logs used in asynchronous replication? 1. The master connects to the slave and initiates log transfer. \[错误] 2. They contain events that describe all queries run on the master. \[错误] 3. They are pulled from the master to the slave. \[正确] 4. They contain events that describe database changes on the master. \[正确] 5. They contain events that describe only administrative commands run on the master. \[错误] > 在异步复制中，二进制日志的传输是由从库主动拉取的方式进行的，而不是主库推送。二进制日志记录的是主库上导致数据更改的事件（如INSERT、UPDATE等），而不是所有的SQL语句，也不是仅限于管理命令。这些日志用于从库重放数据更改，确保主从一致。 ## 试题 18 You have appropriate privileges and are about to shut down a running MySQL server process on Oracle Linux 7. Which three are valid methods that will shut down the MySQL server? 1. mysqld\_safe --shutdown \[错误] 2. mysqld\_safe -S /tmp/mysql.sock SHUTDOWN \[错误] 3. kill mysqld\_safe \[错误] 4. systemctl stop mysqld \[正确] 5. mysql> SHUTDOWN; \[正确] 6. mysql -S /tmp/mysql.sock --shutdown [错误] 7. mysqladmin shutdown \[正确] > 正确的方法包括使用系统服务命令systemctl stop mysqld、登录MySQL命令行执行SHUTDOWN命令，以及使用mysqladmin工具执行shutdown命令。mysqld\_safe命令并不支持直接关闭MySQL服务，kill该进程也不安全，可能会造成数据不一致或损坏。 ## 试题 19 Choose two. Examine this MySQL Shell command: ``` dba.rebootClusterFromCompleteOutage () ``` Which two statements are true? 1. It performs InnoDB Cluster instances rolling restart. \[正确] 2. It stops and restarts all InnoDB Cluster instances and initializes the metadata. \[错误] 3. It picks the minimum number of instances necessary to rebuild the quorum and reconfigures InnoDB Cluster. \[错误] 4. It is not mandatory that all instances are running and reachable before running the command. \[正确] 5. It only stops and restarts all InnoDB Cluster instances. \[错误] 6. It only starts all InnoDB Cluster instances. \[错误] 7. It reconfigures InnoDB Cluster if the cluster was stopped. [错误] > dba.rebootClusterFromCompleteOutage用于恢复InnoDB Cluster在完全中断后的状态，它通过滚动重启方式逐个实例重启，并不要求所有实例都必须在线，而是允许部分节点可用时就能重新建立仲裁和恢复集群。该命令并不负责初始化元数据，也不会一次性重启所有节点。 ## 试题 20 Choose two. Examine this command and output:（见下图） Which two options will improve the security of the MySQL instance?  1. Change the parent directory owner and group to mysql. \[错误] 2. Remove the world read/execute privilege from the accounting directory. \[正确] 3. Remove group read/write privileges from the private\_key.pem file. \[正确] 4. Remove world read privileges from the server-cert.pem certificate file. \[错误] 5. Remove world read privileges from the public\_key.pem file. \[错误] 6. Change the group ownership of the mysql directory to the mysql user group. \[错误] > 为了提升MySQL实例的安全性，应该限制对敏感目录和密钥文件的访问权限。移除accounting目录的全局读取/执行权限可以避免非授权用户访问相关文件。private\_key.pem是私钥文件，移除其组读写权限可降低泄露风险。其他建议如更改属主或公钥/证书文件权限虽然相关，但并非直接提升安全性的核心手段。 以下是依照你指定的格式规范整理后的试题 21–26 内容： --- ## 试题 21 Choose two. Which two statements are true about general tablespaces? 1. General tablespaces support temporary tables. \[错误] 2. Dropping a table from a general tablespace releases the space back to the operating system. \[错误] 3. An existing table can be moved into a general tablespace. \[正确] 4. A new table can be created explicitly in a general tablespace. \[正确] 5. A general tablespace can have multiple data files. \[错误] > General tablespaces 不支持临时表，同时删除表并不会将空间返还给操作系统。用户可以显式地创建表并将其放入 general tablespace，也可以通过 ALTER 语句将现有表迁移进去。此外，一个 general tablespace 只能包含一个数据文件，不能添加多个数据文件。 ## 试题 22 Choose three. Examine this command, which executes successfully: cluster.addInstance( '<user>@<host>:<port>' , recoveryMethod:'clone' ) Which three statements are true? 1. The account used to perform this recovery needs the BACKUP\_ADMIN privilege. \[正确] 2. A new instance is installed, initialized, and provisioned with data from an instance already in the cluster and joined to the cluster. \[错误] 3. InnoDB tablespaces outside the datadir are able to be cloned. \[正确] 4. A target instance must exist, then it will be provisioned with data from an instance already in the cluster and joined to the cluster. \[正确] 5. It is always slower than recoveryMethod:'incremental'. \[错误] 6. InnoDB redo logs must not rotate for the duration of the execution; otherwise, the recovery will fail. \[错误] >使用 clone 恢复方法时，需要具有 BACKUP\_ADMIN 权限。克隆过程中要求目标实例已经存在，并会从已有的集群节点中复制数据，同时 InnoDB 表空间即使不在 datadir 中也可以被克隆。这种方法不一定比增量复制慢，redo 日志旋转也不会直接导致失败。 ## 试题 23 Choose three. Which three sets of item information are visible in the mysql system database? 1. information about table structures \[错误] 2. rollback segments \[错误] 3. performance monitoring information \[错误] 4. plugins \[正确] 5. audit log events \[错误] 6. help topics \[正确] 7. time zone information and definitions \[正确] > mysql 系统数据库包含关于用户、权限、插件、帮助主题以及时区信息的数据。它不包括表结构信息、回滚段、审计日志或性能监控信息，这些内容通常保存在其他系统库如information\_schema或performance\_schema中。 ## 试题 24 Which two situations will cause the binary log to rotate? 1. FLUSH HOSTS executed \[错误] 2. SET sql\_log\_bin=1 executed \[错误] 3. max\_binlog\_cache\_size exceeded \[错误] 4. max\_binlog\_size exceeded \[正确] 5. FLUSH LOGS executed \[正确] 6. SET sync\_binlog=1 executed \[错误] > binary log 会在达到 max\_binlog\_size 限制时自动轮转，或通过执行 FLUSH LOGS 命令手动触发轮转。其他操作如设置 sync\_binlog、sql\_log\_bin 或 flush hosts 并不会导致日志轮换。 ## 试题 25 Choose three. Which three statements are true about MySQL replication? 1. Any instance can have multiple slaves, but it can have only one master. \[错误] 2. Binary logging must be enabled on the master in order to replicate to other instances. \[正确] 3. Binary logs contain only transactions originating from a single MySQL instance. \[错误] 4. Replication can use only TCP/IP connections. \[正确] 5. Each instance in a replication topology must have a unique server ID. \[正确] 6. Each slave must have its own MySQL user for replication. \[错误] 7. A replication user must have the SELECT privilege for all tables that need to be replicated. \[错误] > 主服务器必须启用 binary log 才能进行复制，每个参与复制的 MySQL 实例都必须有唯一的 server ID，复制过程中使用的是 TCP/IP 通信方式。复制用户不需要表级 SELECT 权限，而从库可以共享同一个复制账号。 ## 试题 26 The data in this instance is transient; no backup or replication will be required. It is currently underperforming. The database size is static and including indexes is 19G. Total system memory is 32G. After profiling the system, you highlight these MySQL status and global variables: Com\_rollback 85408355 Com\_commit 1234342 Innodb\_buffer\_pool\_pages\_free 163840 mysqld Buffer\_pool\_size=20G Innodb\_flush\_log\_at\_trx\_commit=2 Disable-log-bin The OS metrics indicate that disk is a bottleneck. Other variables retain their default values. Which two changes will provide the most benefit to the instance? 1. innodb\_log\_file\_size=1G \[错误] 2. innodb\_flush\_log\_at\_trx\_commit=1 \[错误] 3. max\_connections=10000 \[错误] 4. innodb\_doublewrite=0 \[正确] 5. buffer\_pool\_size=24G \[正确] 6. sync\_binlog=0 \[错误] > 当前系统瓶颈在磁盘，且数据无须持久性保障。关闭 innodb\_doublewrite 可减少磁盘写入负载；同时增加 buffer pool 至 24G 更充分利用内存、减少磁盘访问。设置更大的 log 文件或更高连接数并不会直接改善当前 I/O 问题。 ## 试题 27 Choose two. User `fwuser`@`localhost` is registered with the MySQL Enterprise Firewall and has been granted privileges for the SAKILA database. Examine these commands that you executed and the results: ```sql SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS WHERE USERHOST = 'fwuser@localhost'; ``` （见下图） You then execute this command: ```mysql CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RESET'); ```  Which two are true? 1. The fwuser\@localhost account mode is set to OFF. \[正确] 2. The mysql.firewall\_users table is truncated. \[错误] 3. The whitelist of the fwuser\@localhost account is truncated. \[正确] 4. The fwuser\@localhost account is removed from the mysql.user table. \[错误] 5. The information\_schema.MYSQL\_FIREWALL\_WHITELIST table is truncated. \[错误] 6. The fwuser\@localhost account mode is set to DETECTING. \[错误] 7. The firewall resets all options to default values. \[错误] > 将账号模式设置为 RESET 会删除该账号的所有防火墙规则，同时将模式设置为 OFF。此操作只清空该用户的白名单，不会清空防火墙系统表或移除用户账号。 ## 试题 28 Choose two. Examine this statement and output: ```mysql SHOW GRANTS FOR jsmith; ``` Grants for jsmith@%: ``` GRANT USAGE ON *.* TO 'jsmith'@'%'; GRANT UPDATE (Name) ON 'world.country' TO 'jsmith'@'%'; ``` Which two SQL statements can jsmith execute? 1. UPDATE world.country SET Name='all'; \[正确] 2. UPDATE world.country SET Name='one' LIMIT 1; \[正确] 3. UPDATE world.country SET Name='first' ORDER BY Name LIMIT 1; \[错误] 4. UPDATE world.country SET Name=CONCAT('New', Name); \[错误] 5. UPDATE world.country SET Name='new' WHERE Name='old'; \[错误] > 用户 jsmith 仅被授予对 world.country 表 Name 列的 UPDATE 权限，因此只允许执行简单更新操作。涉及 ORDER BY 或函数操作的更新需要额外的 SELECT 权限才能执行。 ## 试题 29 Choose two. There are five MySQL instances configured with a working group replication. Examine the output of the group members: ```mysql SELECT MEMBER_ID, MEMBER_STATE FROM performance_schema.replication_group_members; ``` （见下图）  Which two statements are true about network partitioning in the cluster? 1. The cluster will shut down to preserve data consistency. \[错误] 2. There could be both a 2 node and 3 node group replication still running, so shutting down group replication and diagnosing the issue is recommended. \[正确] 3. The group replication will buffer the transactions on the online nodes until the unreachable nodes return online. \[错误] 4. The cluster has built-in high availability and updates group\_replication\_ip\_whitelist to remove the unreachable nodes. \[错误] 5. A manual intervention to force group members to be only the working two instances is required. \[正确] > 网络分区可能导致出现 2 节点和 3 节点的分裂脑情况。此时建议停止复制并检查原因，或者通过手动强制设置当前可用节点来恢复服务。集群不会自动将失联节点移除或缓冲事务。 ## 试题 30 Choose two. Which two statements are true about InnoDB data-at-rest encryption? 1. It supports all indexes transparently. \[正确] 2. It decrypts data for use in memory. \[正确] 3. It does not support the transportable tablespaces feature. \[错误] 4. It enforces encryption from disk to memory and over network transmission. \[错误] 5. It supports only non-blob datatypes. \[错误] > InnoDB 静态数据加密可以透明地支持所有索引，并在需要时将数据解密加载到内存中。它不影响 transportable tablespace 功能，且仅负责存储层加密，不会影响内存或网络传输。 ## 试题 31 Choose three. Which three statements are true about MySQL Enterprise Firewall? 1. It shows only notifications for blocked connections, which originated outside of your network's primary domain. \[错误] 2. On Windows systems, it is controlled and managed using the Windows Internet Connection Firewall control panel. \[错误] 3. System tables named firewall\_users and firewall\_whitelist in the mysql database provide persistent storage of firewall data. \[正确] 4. It is available only in MySQL Enterprise versions. \[正确] 5. Firewall functionality is dependent on SHA-256 and ANSI-specific functions built in to the mysql.firewall table. \[错误] 6. It provides INFORMATION\_SCHEMA tables that enable views into firewall data. \[正确] > MySQL Enterprise Firewall 仅在企业版本中提供，通过 mysql.firewall\_users 和 mysql.firewall\_whitelist 表持久化配置，同时提供 INFORMATION\_SCHEMA 视图以便查看数据。 ## 试题 32 Choose two. Which two storage engines provide a view of the data consistent with the storage system at any moment? 1. NDB \[正确] 2. InnoDB \[正确] 3. MEMORY \[错误] 4. ARCHIVE \[错误] 5. MyISAM \[错误] > NDB 和 InnoDB 都支持事务，能够提供一致的数据视图。其他引擎如 MEMORY、MyISAM 等不具备事务隔离，无法保证瞬时一致性。 ## 试题 33 Choose three. Which three are requirements for a secure MySQL Server environment? 1. Restrict the number of OS users that have access at the OS level. \[正确] 2. Ensure appropriate file system privileges for OS users and groups. \[正确] 3. Keep the entire software stack on one OS host. \[错误] 4. Minimize the number of non-MySQL Server-related processes running on the server host. \[正确] 5. Run MySQL server as the root user to prevent incorrect sudo settings. \[错误] 6. Encrypt the file system to avoid needing exact file-system permissions. \[错误] > 确保 MySQL 运行环境的安全需要限制操作系统用户权限和数量，正确设置文件权限，并减少非相关进程以降低风险。并不要求全部软件堆栈集中在一个主机或运行在 root 权限下。 ## 试题 34 Choose two. Examine this list of MySQL data directory binary logs: ``` binlog.000001 binlog.000002 ... binlog.000289 binlog.000300 binlog.000301 binlog.index ``` Now examine this command, which executes successfully: ```bash mysqldump --delete-master-logs --all-databases > /backup/db_backup.sql ``` Which two are true? 1. All databases, excluding master metadata, are backed up to the output file. \[正确] 2. All binary logs are backed up and then deleted. \[错误] 3. All databases are backed up to the output file. \[错误] 4. All binary logs are deleted from the master. \[错误] 5. All non-active binary logs are removed from the master. \[正确] 6. All details regarding deleted logs and master metadata are captured in the output file. \[错误] > 使用 `--delete-master-logs` 选项时，会备份所有用户数据库（不包括系统库）并删除非活跃的二进制日志以释放空间。它不会备份二进制日志或完全删除所有日志。 ## 试题 35 You want to install and configure MySQL on a Linux server with tarball binaries in the `/app/mysql/` directory, where the bin directory is found at `/app/mysql/bin` and the data directory at `/app/data`. Which two parameters are required to configure the MySQL instance? 1. The configuration innodb\_log\_group\_home\_dir=/datadir is needed. \[错误] 2. The configuration basedir=/app/mysql is needed. \[正确] 3. The configuration log-bin=/app/data is needed. \[错误] 4. The configuration datadir=/app/mysql/data is needed. \[错误] 5. The configuration basedir=/app/mysql/bin is needed. \[错误] 6. The configuration datadir=/app/data is needed. \[正确] > 手动安装 MySQL 时必须设置 basedir 指向安装目录，并设置 datadir 指向数据目录，以确保 MySQL 正确读取二进制文件和数据文件。 ## 试题 36 A valid raw backup of the shop.customers MyISAM table was taken. You must restore the table. You begin with these steps: 1. Confirm that `secure_file_priv='/var/tmp'`. 2. ```mysql DROP TABLE shop.customers; ``` 3. ```bash cp /backup/customers.MY* /var/lib/mysql/shop/ ``` Which two actions are required to complete the restore? 1. mysql> IMPORT TABLE FROM /var/lib/mysql/shop/customers.sdi \[错误] 2. mysql> IMPORT TABLE FROM /var/tmp/customers.sdi \[正确] 3. mysql> SOURCE '/var/tmp/customers.sdi' \[错误] 4. mysql> ALTER TABLE shop.customers IMPORT TABLESPACE \[错误] 5. shell> cp /backup/customers.sdi /var/tmp \[正确] 6. shell> cp /backup/customers.sdi /var/lib/mysql/shop/ \[错误] 7. shell> cp /backup/customers.frm /var/lib/mysql/shop/ \[错误] 8. mysql> ALTER TABLE shop.customers DISCARD TABLESPACE \[错误] > MyISAM 表恢复需要复制 `.MY*` 文件和 `.sdi` 元数据文件到安全目录，然后使用 IMPORT TABLE 命令导入元数据。由于 secure\_file\_priv 限制，需将 .sdi 文件复制到允许的目录（如 /var/tmp）。 ## 试题 37 Choose two. You are investigating performance problems in a MySQL database; all data fits in memory. You determine that SELECT queries to one table are the main cause for poor response times. Which two have the biggest potential for eliminating the problem? 1. high concurrency \[正确] 2. column definitions \[错误] 3. operating system resources \[错误] 4. innodb mutexes \[错误] 5. non-transaction storage engine \[正确] 6. table indexes \[错误] > 数据完全在内存中时，性能瓶颈可能源自高并发导致的锁争用或使用了非事务型存储引擎，这两者会限制查询性能。其他因素对内存型数据的查询影响较小。 ## 试题 38 Choose two. You have semi-synchronous replication configured and working with one slave. `rpl_semi_sync_master_timeout` has never been reached. You find that the disk system on the master has failed and as a result, the data on the master is completely unrecoverable. Which two statements are true? 1. As soon as the incident happens, application can read data from the slave and rely on it to return a full and current set of data. \[错误] 2. Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log. \[正确] 3. No committed transactions are lost. \[正确] 4. Reads from the slave can return outdated data until the value of the rpl\_semi\_sync\_master\_timeout variable is reached. \[错误] 5. A small amount of committed transactions may be lost in case they were committed just before the disk failure. \[错误] 6. The slave automatically identifies that the master is unreachable and performs any required actions so that applications can start using the slave as the new master. \[错误] > 在半同步复制下，确认事务在主库上提交前会等待至少一个从库确认，因此没有已提交的事务会丢失。然而，从库仍需应用 relay log 中的事务，短时间内可能返回旧数据。主库宕机后需要手动切换角色。 ## 试题 39 Choose three. You are considering using file-system snapshots to back up MySQL. Which three statements are true? 1. The backup window is almost zero from the perspective of the application. \[正确] 2. They allow direct copying of table rows with operating system copy commands. \[错误] 3. They work best for transaction storage engines that can perform their own recovery when restored. \[正确] 4. There is a slight performance cost while the snapshot is active. \[正确] 5. They do not use additional disk space. \[错误] 6. They take roughly twice as long as logical backups. \[错误] 7. They do not back up views, stored procedures, or configuration files. \[错误] > 文件系统快照备份几乎不会影响应用运行，适用于支持自恢复的事务存储引擎如 InnoDB。快照期间会有轻微的性能开销，并使用额外空间以维持数据一致性。 ## 试题 40 Choose two. Which two commands will display indexes on the `parts` table in the `manufacturing` schema? 1. SELECT \* FROM information\_schema.statistics WHERE table\_schema='manufacturing' AND TABLE\_NAME='parts'; \[错误] 2. SHOW INDEXES FROM manufacturing.parts; \[正确] 3. DESCRIBE manufacturing.parts; \[正确] 4. SELECT \* FROM information\_schema.COLUMN\_STATISTICS; \[错误] > 使用 `SHOW INDEXES` 可查看表索引的详细信息，`DESCRIBE` 虽然主要显示结构但也能显示主键索引。通过 `information_schema.statistics` 需指定正确表才能获取索引信息。 ## 试题 41 On examination, your MySQL installation datadir has become recursively world(所有用户) read/write/executable. What are two major concerns of running an installation with incorrect file privileges? 1. SQL injections could be used to insert bad data into the database. \[错误] 2. Extra startup time would be required for the MySQL server to reset the privileges.（没有这个功能） \[错误] 3. Data files could be deleted. (datafiles) \[正确] 4. MySQL binaries could be damaged, deleted, or altered.（basedir 中才有 MySQL binaries） \[错误] 5. Users could overwrite configuration files.(mysqld-auto.ccnf) \[正确] > 文件权限设置错误可能会使非授权用户有权访问或更改数据目录中的关键文件，导致数据文件被删除或配置文件被覆盖，这对数据完整性和系统稳定性都是重大威胁，而SQL注入与文件权限无关，MySQL也不会自动在启动时重设权限，MySQL的可执行文件一般不在data目录中。 --- ## 试题 42 Choose three. Which three requirements must be enabled for group replication? 1. slave updates logging \[正确] 2. primary key or primary key equivalent on every table \[正确] 3. binary log ROW format \[正确] 4. replication filters \[错误] 5. binary log checksum \[错误] 6. binary log MIXED format \[错误] 7. semi-sync replication plugin \[错误] > Group Replication 依赖于 ROW 格式的二进制日志、主键以及从库更新日志以确保数据一致性和冲突检测，而其他选项如MIXED格式或semi-sync插件不是必要条件，replication filters 甚至与GR功能冲突。 --- ## 试题 43 You are attempting to start your mysqld. Examine this log output: ``` 2019-12-12T22:21:40:353800z 0 System DCY-010116 Server /mysql/bin/mysqld mysld 8.0.18-comnercial starting as process 29740 2019-12-12T22:21:40:458802z 1 ERROR DCY-012592 InnoDB Operating system error number 2 in a file operation. 2019-12-12T22:21:40:459259z 1 ERROR DCY-012593 InnoDB The error means the system cannot find the patj specified. 2019-12-12T22:21:40:459423z 1 ERROR DCY-012594 InnoDBIf you are installing InnoDB,remember that must create directories yourself ,InnoDB does not create them. 2019-12-12T22:21:40:459606z 1 ERROR DCY-012646 InnoDB File ./ibdatali ‘open’returned os error 71.Cannot continue operation. 2019-12-12T22:21:40:459891z 1 ERROR DCY-012981 InnoDB Cannot continue operation. ``` Which two things must you check? 1. that the user attempting to connect to the database is using the correct username and password \[错误] 2. for the presence of the missing files in other locations \[正确] 3. the configuration file for correct datadir setting \[正确] 4. for the possibility that the files are locked by another process \[错误] 5. that the TLS/SSL certificates are still valid \[错误] 6. that you are using the correct version of MySQL \[错误] > 报错内容显示操作系统无法找到指定路径，说明数据库目录或关键文件缺失，因此需要检查配置文件中的datadir是否正确，以及这些文件是否存在于其他位置，与连接用户名、TLS证书或锁无关。 --- ## 试题 44 Which three requirements must be enabled for group replication? 1. binary log MIXED format \[错误] 2. binary log ROW format \[正确] 3. slave updates logging \[正确] 4. semi-sync replication plugin \[错误] 5. primary key or primary key equivalent on every table \[正确] 6. binary log checksum \[错误] 7. replication filters \[错误] > Group Replication 依赖于 ROW 格式的二进制日志、主键以及 slave updates logging 以维持一致性，其他如 MIXED 格式或 checksum 并不是启用 GR 所必须的。 --- ## 试题 45 Choose two Which two statements are true about raw binary backups? 1. The data format is identical to how MySQL stores the data on disk. \[正确] 2. The resulting files are easily human readable. \[错误] 3. They are converted to a highly compressible binary format. \[错误] 4. They are faster than logical backups because the process is a simple file or file system copy. \[正确] 5. They are required to obtain FIPS security compliance. \[错误] > Raw binary 备份直接复制磁盘上 MySQL 的数据文件，因此格式完全一致，备份过程快速高效，但不可读也非专门用于压缩或符合安全标准如FIPS。 --- ## 试题 46 Choose two. Which two methods can be used to determine whether a query uses the hash join algorithm? 1. EXPLAIN FORMAT=JSON \[正确] 2. EXPLAIN FORMAT=TRADITIONAL \[错误] 3. EXPLAIN FORMAT=TREE \[错误] 4. EXPLAIN without any formatting argument \[错误] 5. EXPLAIN ANALYZE \[正确] > Hash join 是一种优化器选择的执行计划类型，通过 JSON 格式的 EXPLAIN 和 EXPLAIN ANALYZE 可以获取执行详情及使用的算法，其他传统格式不会显示此信息。 --- ## 试题 47 Choose two. You have an InnoDB Cluster configured with three servers. Examine this command, which executes successfully: `mysqldump -uroot -p -d mydatabase > mydatabase_backup.sql` 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? 1. Stop all instances except the primary read/write master instance and run the restore. \[错误] 2. Remove the @@GLOBAL.gtid\_executed statement from the dump file. \[错误] 3. Remove the @@GLOBAL.gtid\_purged statement from the dump file. \[正确] 4. Restore using the --set-gtid-purged=OFF option. \[错误] 5. Remove the group replication plugin from each instance before restoring. \[错误] 6. Create the backup by using the --set-gtid-purged=OFF option. \[正确] > 错误提示说明无法设置 GTID\_PURGED 是因为 group replication 正在运行，解决方式包括在备份时或恢复前移除 gtid\_purged 语句，或在备份时禁用其生成；而非需停服务或删除插件。 --- ## 试题 48 Choose three. Which three are types of InnoDB tablespaces? 1. temporary table tablespaces \[正确] 2. redo tablespaces \[错误] 3. encryption tablespaces \[错误] 4. undo tablespaces \[正确] 5. data tablespaces \[正确] 6. schema tablespaces \[错误] > InnoDB 支持临时表、undo和数据表空间，redo 并非表空间，schema 和 encryption tablespace 不是 InnoDB 使用的术语。 --- ## 试题 49 Choose two Examine this statement and output: ```mysql SELECT ROW_NUMBER() OVER() AS QN,query, exec_count, avg_latency, lock_latency FROM sys.statement_analysis ORDER BY exec_count; ``` You must try to reduce query execution time.  Which two queries should you focus on? 1. QN=2 \[错误] 2. QN=3 \[错误] 3. QN=5 \[正确] 4. QN=1 \[错误] 5. QN=4 \[正确] > 优化目标是减少执行时间，应该关注平均延迟较高的语句，而不是执行次数多的语句，执行频繁但已经优化好的语句提升空间小，低频高延迟的更具优化价值。 好的，以下是试题 51 到 59 的处理结果，按你要求的格式整理： --- ## 试题 51 Choose two. An existing asynchronous replication setup is running MySQL 8. Which two steps are a part of implementing GTID replication? 1. On the slave, alter the MySQL master connection setting with: CHANGE MASTER TO MASTER\_AUTO\_POSITION = 1; \[正确] 2. Execute this on the slave to enable GTID: START SLAVE IO\_THREAD WITH GTID; \[错误] 3. Execute this on the slave to enable GTID: RESET SLAVE; START SLAVE GTID\_NEXT=AUTOMATIC; \[错误] 4. Enable GTID by executing this on the master and the slave: SET GLOBAL GTID\_ENABLED=on; \[错误] 5. Restart MySQL (master and slave) with these options enabled: --gtid\_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency \[正确] 6. On the slave, alter the MySQL master connection setting with: ALTER channel CHANGE MASTER TO MASTER\_AUTO\_POSITION = 1; \[错误] > 要启用 GTID 复制，必须在主从服务器中配置 GTID 模式并启用自动定位。选项 1 是通过 CHANGE MASTER TO 启用 GTID 的关键命令；选项 5 是启用 GTID 的必须参数组合。其他选项要么语法错误，要么不存在所说的系统变量或命令。 --- ## 试题 52 Choose two. You plan to install MySQL Server by using the RPM download. Which two statements are true? 1. MySQL uses the RPM relocatable installation target feature. \[错误] 2. You must manually initialize the data directory. \[错误] 3. You can find the root password in the error log after the first start. \[正确] 4. The MySQL RPM package installation supports deploying multiple MySQL versions on the same host. \[错误] 5. The functionality is split among several RPM package files. \[正确] 6. You can provide the root password interactively. \[错误] > RPM 安装后的首次启动会将随机生成的 root 密码写入 error log，并且 RPM 包被分为多个功能模块。其他选项或描述错误，或不适用于默认的 RPM 安装流程。 --- ## 试题 53 Choose four. Which four connection methods can MySQL clients specify with the --protocol option when connecting to a MySQL server? 1. IPv4 \[错误] 2. IPv6 \[错误] 3. FILE \[错误] 4. SOCKET \[正确] 5. DIRECT \[错误] 6. TCP \[正确] 7. PIPE \[正确] 8. MEMORY \[正确] > \--protocol 选项仅接受 SOCKET、TCP、PIPE 和 MEMORY 四种合法值。IPv4/IPv6 属于地址形式，FILE 并非协议选项，DIRECT 不是 MySQL 支持的协议名。 --- ## 试题 54 Choose four. 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? 1. \--database=world \[正确] 2. \--host=192.0.2.1 \[正确] 3. \--protocol=UDP \[错误] 4. \--socket=/tmp/mysql.sock \[错误] 5. \--user=admin \[正确] 6. \--protocol=pipe \[错误] 7. \--port=3306 \[错误] 8. \--password \[正确] 9. \--shared-memory-base-name=world \[错误] > 要从 Linux 客户端连接到 Windows 上运行的 MySQL 服务，必须指定 host、user、database 和 password。端口号 3306 是默认端口，不必显式指定。UDP 不是合法协议，socket 不适用于远程连接。 --- ## 试题 55 Choose three. Which three are characteristics of a newly created role? 1. It can be granted to user accounts. \[正确] 2. It is stored in the mysql.role table. \[错误] 3. It is created as a locked account. \[正确] 4. It can be protected with a password. \[错误] 5. It can be dropped using the DROP ROLE statement. \[正确] 6. It can be renamed using the RENAME ROLE statement. \[错误] > 新建角色默认是锁定状态，不能被认证，也不需要密码；角色可以通过 GRANT 分配给用户，也可以被 DROP ROLE 删除。但没有 mysql.role 表，也不支持重命名角色。 --- ## 试题 56 Table t is an InnoDB table. Examine these statements and output: select count(1) from t; count(1) = 72 Mysql show indexes from t \G \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: t Non\_unique: 0 Key\_name: PRIMARY Seq\_in\_index: 1 Column\_name: a Collation: A Cardinality: 72 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: t Non\_unique: 1 Key\_name: b\_idx Seq\_in\_index: 1 Column\_name: b Collation: A Cardinality: 1 Visible: NO Which two are true? 1. Table t has two viable indexes to be used for queries. \[错误] 2. ANALYZE TABLE t would update index statistics uniquely for the PRIMARY index. \[错误] 3. Index b\_idx has a low number of unique values. \[正确] 4. SELECT b from t would perform a table scan. \[正确] 5. SELECT a FROM t would perform a table scan. \[错误] > PRIMARY 索引基于字段 a，基数等于总行数；而 b\_idx 基数为 1，说明只有一个唯一值，选择性差，且为不可见索引，因此不会被优化器选择使用，导致对 b 的查询会进行全表扫描。 --- ## 试题 57 Choose two. Which two statements are true about the mysqld-auto.cnf file? 1. This file is for logging purposes only and is never processed. \[错误] 2. It is always updated with changes to system variables. \[错误] 3. It is read and processed at the beginning of startup configuration. \[错误] 4. This file is for storing MySQL Server configuration options in JSON format. \[正确] 5. This file is for storing MySQL server\_uuid values only. \[错误] 6. It is read and processed at the end of startup configuration. \[正确] > mysqld-auto.cnf 是用于持久化系统变量设置的 JSON 配置文件，MySQL 在启动过程中会在最后处理它，从而覆盖默认配置或 cnf 文件中的设置。它并不是用于日志，也不会自动随所有系统变量变更而更新。 --- ## 试题 58 Choose two. Examine this command and output:（见下图）  Which two statements are true? 1. The lock is at the table object level. \[错误] 2. The lock is an exclusive lock. \[正确] 3. The lock is a shared lock. \[错误] 4. The lock is an intentional lock. \[错误] 5. The lock is a row-level lock. \[正确] > InnoDB 支持行级锁，该场景显示的是针对某个记录的排他锁。表级锁不会出现在这种查询输出中，intentional lock 是元数据锁，shared lock 是共享读锁，此处为排他性行锁。 --- ## 试题 59 Choose two. Your MySQL installation is running low on space due to binary logs. You need to reduce your log space usage urgently. Which two sets of actions when completed will accomplish this? 1. Use SET GLOBAL binlog\_expire\_logs\_seconds=<value> and run the FLUSH BINARY LOGS command. \[正确] 2. Use SET GLOBAL binlog\_expire\_logs\_seconds=<value> and restart the server. \[错误] 3. Set binlog\_expire\_logs\_seconds = 0 in my.cnf and restart the server. \[错误] 4. Set binlog\_expire\_logs\_seconds in my.cnf. \[错误] 5. Use SET PERSIST binlog\_expire\_logs\_seconds=<value>. \[错误] 6. Use PURGE BINARY LOGS to \<binlog\_name>. \[正确] > 要立即释放二进制日志所占空间，可以设置过期时间后使用 FLUSH BINARY LOGS 触发清理，也可以直接使用 PURGE BINARY LOGS 删除旧日志。其他方法仅影响将来的日志生成或需重启，并不能立即释放空间。 ## 试题 60 Choose three. You must run multiple instances of MySQL Server on a single host. Which three methods are supported? 1. Run mysqld with --datadir defined for each instance. \[正确] 2. Start mysqld or mysqld\_safe using different option files for each instance. \[正确] 3. Use system tools to lock each instance to its own CPU. \[错误] 4. Use systemd with different settings for each instance. \[正确] 5. Run MySQL Server docker containers. \[错误] 6. Use resource groups to lock different instances on separate CPUs. \[错误] > 运行多个 MySQL 实例需要使用不同的配置方式区分数据目录、配置文件或 systemd 配置。--datadir 允许不同实例存储在不同位置，不同配置文件可设置端口和目录。systemd 可为每个实例创建单独服务。Docker 虽支持隔离运行，但不在本题考点中。系统工具绑定 CPU 或资源组不属于官方支持的 MySQL 实例管理方式。 --- ## 试题 61 Choose two. Your MySQL installation is running low on space due to binary logs. You need to reduce your log space usage urgently. Which two sets of actions when completed will accomplish this? 1. Use SET GLOBAL binlog\_expire\_logs\_seconds=<value> and restart the server. \[错误] 2. Use SET PERSIST binlog\_expire\_logs\_seconds=<value>. \[错误] 3. Set binlog\_expire\_logs\_seconds in my.cnf. \[错误] 4. Set binlog\_expire\_logs\_seconds = 0 in my.cnf and restart the server. \[错误] 5. Use PURGE BINARY LOGS to \<binlog\_name>. \[正确] 6. Use SET GLOBAL binlog\_expire\_logs\_seconds=<value> and run the FLUSH BINARY LOGS command. \[正确] > 清理 binlog 最直接的方法是使用 PURGE BINARY LOGS 命令。设置 binlog 过期时间并立即生效需要搭配 FLUSH BINARY LOGS，否则不会自动触发清理。将配置写入 my.cnf 仅在重启后生效且不立即释放空间，SET PERSIST 仅修改配置，不触发删除。 --- ## 试题 62 The replication for master and slave MySQL Server is up and running. The disk space occupied by the binary log files continues to grow. Which two methods manage this issue? 1. Execute the PURGE BINARY LOGS statement. \[正确] 2. Set the binlog\_expire\_logs\_seconds variable. \[正确] 3. Execute the FLUSH LOGS statement. \[错误] 4. On the master server, disable binary logging by removing the --log-bin option. \[错误] 5. Delete all binary log files manually on the file system to release storage space. \[错误] > 正确的方法是使用 PURGE 命令或设置过期时间变量以自动删除。手动删除 binlog 文件可能导致复制错误，禁用 log-bin 会中断复制。FLUSH LOGS 不会清理旧的 binlog。 --- ## 试题 63 Choose two. You administer a three-node, single primary InnoDB Cluster. Examine cluster.status() displayed here: "statusText":"Cluster is ONLINE and can tolerate up to ONE failure." Which two statements are true? 1. Reconfiguring the cluster as multi-primary, will increase tolerance to two failures. \[错误] 2. There is a quorum and transactions can be committed normally. \[错误] 3. If two instances crash, it will produce an outage. \[正确] 4. Shutting down two instances with the SHUTDOWN command will produce an outage. \[正确] 5. If two instances are unreachable because of network failure, the cluster will reconfigure to work with a single instance. \[错误] 6. Restarting an arbitrary instance will always provoke primary instance failover. \[错误] > 三节点 InnoDB Cluster 最多只能容忍一个节点失败（2 个节点仍能保持多数）。若两节点失败（不论是宕机或网络隔离），将失去法定票数，集群会停止工作。集群不会自动重组单节点继续运行，也不会因为任意节点重启触发主节点切换。 --- ## 试题 64 Choose two. Which two MySQL Server accounts are locked by default? 1. any user created with a username, but missing the host name \[错误] 2. any user created without a password \[错误] 3. any new ROLE accounts \[正确] 4. any internal system accounts \[正确] 5. any user set as DEFINER for stored programs \[错误] > MySQL 中，系统内部账号和新创建的角色（ROLE）默认是锁定的，不能登录。它们需显式解锁或赋予权限后才能使用。其他账号如缺少密码或 host 通常不会自动锁定。 --- ## 试题 65 Examine this command, which executes successfully: `mysqlpump --user=root --password > full_backup.sql` Which two databases will be excluded from this dump? 1. information schema \[正确] 2. mysql \[错误] 3. sys \[正确] 4. world \[错误] 5. employee \[错误] > mysqlpump 默认跳过虚拟系统数据库 `information_schema` 和 `sys`。这些数据库为只读虚拟库，不应导出。mysql 数据库包含用户和权限信息，默认会被导出。 --- ## 试题 66 Choose three. Which three commands can report all the current connections running on the MySQL server? 1. SHOW FULL PROCESSLIST \[正确] 2. SHOW EVENTS \[错误] 3. SELECT \* FROM information\_schema.events \[错误] 4. SELECT \* FROM sys.statement\_analysis \[错误] 5. SELECT \* FROM performance\_schema.threads \[正确] 6. SELECT \* FROM information\_schema.processlist \[正确] 7. SELECT \* FROM sys.metrics \[错误] 8. SELECT \* FROM performance\_schema.events\_transactions\_current \[错误] > 查看当前连接可通过 SHOW PROCESSLIST、performance\_schema.threads 和 information\_schema.processlist 获取。其他选项如 sys.metrics 或 statement\_analysis 与连接无关，events 相关命令用于事件调度，不显示连接信息。 --- ## 试题 67 Choose three. Identify three functions of MySQL Enterprise Monitor. 1. Determine the availability of monitored MySQL servers. \[正确] 2. Centrally manage users. \[错误] 3. Create customized alerts and provide notification alerts. \[正确] 4. Analyze query performance. \[正确] 5. Start a MySQL Enterprise backup. \[错误] 6. Start and stop MySQL Server. \[错误] 7. Start a logical backup. \[错误] > MySQL Enterprise Monitor 提供监控、告警、查询性能分析等功能，但不负责用户管理或直接执行备份、启动服务等操作。备份需通过其他工具如 MySQL Enterprise Backup 完成。 --- ## 试题 68 Choose three. Which three actions will secure a MySQL server from network-based attacks? 1. Use network file system (NFS) for storing data. \[错误] 2. Change the listening port to 3307. \[错误] 3. Construct a perimeter network to allow public traffic \[正确] 4. Allow connections from the application server only. \[正确] 5. Use MySQL Router to proxy connections to the MySQL server. \[错误] 6. Place the MySQL instance behind a firewall. \[正确] > 网络安全措施包括部署防火墙、限制来源 IP、建立网络隔离区（如 DMZ）。仅更改端口号或使用路由器代理不足以防止攻击。NFS 本身不具安全性，反而可能引入更多攻击面。 --- ## 试题 69 Choose two. Which two statements are true about MySQL server multi-source replication? 1. It must use GTID replication. \[错误] 2. It relies on relay\_log\_recovery for resilient operations. \[正确] 3. It needs to be re-instanced after a crash to maintain consistency. \[错误] 4. It is not compatible with auto-positioning. \[错误] 5. It does not attempt to detect or resolve replication conflicts. \[正确] 6. It uses only time-based replication conflict resolution. \[错误] > 多源复制支持 GTID 和基于位置的复制，支持自动定位。relay\_log\_recovery 可增强崩溃恢复能力。复制冲突不会自动检测或解决，需应用层处理。时间戳不用于冲突解决。 --- ## 试题 70 Choose two. Which two statements are true about using MySQL Enterprise Monitor Query Analyzer? 1. It is possible to retrieve a normalized statement, but never the exact statement that was executed. \[错误] 2. It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV. \[错误] 3. It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution. \[正确] 4. The single query QRTi pie chart in the Query Analyzer view is based on the average execution of all statements. \[错误] 5. It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs. \[正确] > Query Analyzer 中确实可以查看原始 SQL（不是只能看到规范化语句），也不能导入外部数据，只能分析来自受监控实例的数据。内置的查询分析 Advisor 可以设置通知来捕获慢查询问题，图形界面支持按任意时间段过滤查询，QRTi 图表是针对单个查询的响应时间影响，不是基于平均执行时间。 --- ## 试题 71 Choose two. You must export data from a set of tables in the world\_x database. Examine this set of tables: Tables (country, countryinfo, location) Which two options will export data into one or more files? 1. shell> mysqlexport world\_x country countryinfo location > mydump.sql \[错误] 2. shell> mysqldump world\_x country countryinfo location > mydump.sql \[正确] 3. shell> mysql --batch world\_x.country world\_x.countryinfo world\_x.1ocation > mydump.sql \[错误] 4. mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world\_x/country' ; mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world\_x/countryinfo' ; mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world\_x/location' ; \[错误] 5. mysql> SELECT \* INTO OUTFILE '/output/country. txt' FROM world\_x.country; mysql> SELECT \* INTO OUTFILE '/output/countryinfo. txt' FROM world\_x.countryinfo; mysql> SELECT \* INTO OUTFILE '/output/location. txt' FROM world\_x.location; \[正确] > mysqldump 是用于导出逻辑数据的标准工具，而 mysqlexport 是不存在的命令。SELECT INTO OUTFILE 可将查询结果导出为文本文件，因此也可以满足导出数据的目的。CLONE LOCAL DATA DIRECTORY 用于复制实例数据，不适合按表导出数据。 --- ## 试题 72 Choose three. Which are three benefits of using mysqlbackup instead of mysqldump? 1. mysqlbackup allows logical backups with concurrency resulting in faster backups and restores. \[错误] 2. mysqlbackup can perform partial backup of stored programs. \[错误] 3. mysqlbackup restores data from physical backups, which are faster than logical backups. \[正确] 4. mysqlbackup integrates tape backup and has the virtual tape option. \[正确] 5. mysqlbackup does not back up MySQL system tables, which shortens backup time. \[错误] 6. mysqlbackup can back up tables with the InnoDB engine without blocking reducing wait times due to contention. \[正确] > mysqlbackup 工具属于企业版，支持物理备份，因此恢复速度较快；它支持非阻塞式 InnoDB 表备份，可以减少锁等待，同时也支持集成虚拟磁带备份。它不能对存储过程进行部分备份，也不支持并发逻辑备份。 --- ## 试题 73 Choose two. All MySQL Server instances belonging to InnoDB Cluster have SSL configured and enabled. You must configure InnoDB Cluster to use SSL for group communication. Which two statements are true? 1. Configuring SSL group communication also configures SSL distributed recovery. \[错误] 2. SSL group communication can be enabled for an existing cluster, one instance at time, by setting group\_replication\_ssl\_mode. \[错误] 3. SSL group communication requires the use of an additional set of parameters group\_replication\_recovery\_\*. \[错误] 4. If only some InnoDB Cluster members are enabled for SSL group communication, and --ssl-mode=PREFERRED, communication will fall back to unencrypted connection. \[错误] 5. An existing InnoDB Cluster must be dissolved and created from scratch to enable SSL for group communication. \[正确] 6. SSL group communication must be enabled at cluster creation time by specifying createCluster (memberSslMode:'REQUIRED') . \[正确] > InnoDB Cluster 的组通信 SSL 模式只能在创建时设置，无法对已存在的集群逐个修改。SSL 配置不会自动影响到分布式恢复部分，因此需要分别配置。 --- ## 试题 74 After installing MySQL 8.0 on Oracle Linux 7, you initialize the data directory with the mysqld --initialize command. Which two will assist in locating the root password? 1. the root password inserted in the error log set by the --log-error=file\_name variable \[正确] 2. the root\_pw variable stored in the mysql.install table \[错误] 3. the root password displayed on the screen via a Warning message \[正确] 4. the root password written to the /root/.my.cnf file \[错误] 5. as root, executing the SHOW PASSWORD command by using the SHA-256 password encryption plugin \[错误] > 初始化数据目录时，root 密码会写入错误日志，也可能在命令行提示中以警告形式显示。并不会写入配置文件或专用系统表，也不存在 SHOW PASSWORD 命令。 --- ## 试题 75 Choose two. Identify two ways to significantly improve data security. 1. Use a private network behind a firewall. \[正确] 2. Configure MySQL to have only one administrative account. \[错误] 3. Configure mysqld to use only local disks or attached disks and to have its own account in the host system. \[错误] 4. Configure mysqld to run as the system admin account, such as root. \[错误] 5. Configure mysqld to use only networked disks. \[错误] > 使用私有网络并通过防火墙保护数据库服务器是关键的安全措施。MySQL 安全性不依赖于账户数量的减少或使用系统管理员账户运行 mysqld，后者甚至会增加风险。 --- ## 试题 76 Which two are valid uses for binary logs on a MySQL instance? 1. point-in-time recovery \[正确] 2. logging the duration and locks for all queries \[错误] 3. replication \[正确] 4. audit of all queries \[错误] 5. recording the order in which queries are issued \[错误] > 二进制日志主要用于复制和基于时间点的恢复。它不用于审计、锁或查询时长等细节记录，也不保证完整记录查询顺序。 --- ## 试题 77 Choose two. Which two are features of MySQL Enterprise Firewall? 1. modifying SQL statement dynamically with substitutions \[错误] 2. blocking of potential threats by configuring pre-approved whitelists \[正确] 3. recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands \[正确] 4. automatic locking of user accounts who break your firewall \[错误] 5. provides stateless firewall access to TCP/3306 \[错误] > 企业版防火墙可以记录 SQL 语句，帮助建立白名单策略，还能阻止未授权语句的执行。但它不支持自动锁定用户，也不作为网络层防火墙使用。 --- ## 试题 78 Choose three. Which three methods display the complete table definition of an InnoDB table? 1. SELECT \* FROM table 1\G \[错误] 2. SHOW CREATE TABLE \[正确] 3. mysqldump --no-data schema table \[正确] 4. hexdump -v -C table.frm \[错误] 5. REPAIR TABLE table USE\_FRM \[错误] 6. Query the Information Schema. \[正确] > 显示完整表结构可使用 SHOW CREATE TABLE、mysqldump --no-data 和信息模式查询。InnoDB 表不会使用 .frm 文件，因此 hexdump 不适用，REPAIR TABLE 也不是用来查看结构的工具。 --- ## 试题 79 Which two statements are true about the mysql\_config\_editor program? 1. It manages the configuration of the MySQL Firewall feature. \[错误] 2. It can move datadir to a new location. \[错误] 3. It manages the configuration of client programs. (only work for mysql client) \[正确] 4. It can be used to create and edit SSL certificates and log locations. \[错误] 5. It will use client options by default unless you provide –login-path. (mysql = mysql –login-path=client) \[正确] 6. It manages the configuration of user privileges for accessing the server. \[错误] 7. It provides an interface to change my.cnf files. \[错误] > mysql\_config\_editor 用于管理客户端登录路径信息，如用户名、密码等，方便连接服务器。它不会处理服务器端配置文件、证书或权限设置，也不会操作 datadir。 ## 试题 80 Choose three. A MySQL server is monitored using MySQL Enterprise Monitor's agentless installation. Which three features are available with this installation method? 1. security-related advisor warnings \[正确] 2. MySQL Query Analysis data \[正确] 3. disk usage and disk characteristics including disk advisors warnings \[错误] 4. MySQL Replication monitoring \[正确] 5. network-related information and network characteristics \[错误] 6. operating system memory utilization \[错误] > agentless 模式只监控数据库本身，因此无法监控 CPU、磁盘、网络等操作系统资源，但可以收集复制状态、SQL 分析和安全相关警告。 ## 试题 81 Choose four. Which four are types of information stored in the MySQL data dictionary? 1. performance metrics \[错误] 2. server configuration rollback \[错误] 3. view definitions \[正确] 4. table definitions. \[正确] 5. InnoDB buffer pool LRU management data \[错误] 6. server runtime configuration \[错误] 7. stored procedure definitions \[正确] 8. access control lists \[正确] > 数据字典保存结构性元数据，如表、视图、存储过程的定义以及访问控制信息，不包括运行时配置、性能数据或缓冲池状态。 ## 试题 82 Choose two. Examine this statement: mysql>DROP ROLE r\_role1, r\_role2 ; Which two are true? 1. You must revoke all privileges from r\_role1 and r\_role2 before dropping the roles. \[错误] 2. It fails if at least one of the roles does not exist. \[正确] 3. Existing connections can continue to use the roles' privileges until they reconnect. \[错误] 4. It fails if any of the roles is specified in the mandatory\_roles variable. \[正确] 5. You must revoke r\_role1 and r\_role2 from all users and other roles before dropping the roles. \[错误] 6. It fails if you do not have the ADMIN OPTION of the roles r\_role1 and r\_role2. \[错误] > DROP ROLE 操作会检查角色是否存在且未被 mandatory\_roles 使用，否则失败，不需要事先回收权限或撤销角色赋权。 ## 试题 83 Choose two. Which two are true about differences between logical and physical upgrades of MySQL databases? 1. Physical upgrades are performed for current instances on bare metal deployments, whereas logical upgrades are used for virtual machines or containerized instances. \[错误] 2. Physical upgrades leave data in place, whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades. \[正确] 3. Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades. \[正确] 4. Logical upgrades are much faster because they do not require restarting the mysqld process. \[错误] 5. Post-upgrade table storage requirements after physical upgrades are usually smaller than that after logical upgrades. \[错误] 6. Physical upgrades are much faster because they do not require restarting the mysqld process. \[错误] > 逻辑升级使用 mysqldump 导出导入方式会优化存储结构，因此可能占用更少空间但耗时更久，而物理升级保留原始数据文件，更快但空间利用可能不如逻辑方式。 ## 试题 84 Choose two. On examination, your MySQL installation datadir has become recursively world read/write/executable. What are two major concerns of running an installation with incorrect file privileges? 1. Extra startup time would be required for the MySQL server to reset the privileges. \[错误] 2. SQL injections could be used to insert bad data into the database. \[错误] 3. Data files could be deleted. \[正确] 4. Users could overwrite configuration files. \[正确] 5. MySQL binaries could be damaged, deleted, or altered. \[错误] > 如果 datadir 权限错误，攻击者可以删除或修改数据文件或配置文件，造成数据丢失或系统崩溃，但不会影响 SQL 语句或可执行文件本身。 ## 试题 85 Choose two. 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? 1. The metadata is serialized in (SDI). \[正确] 2. MySQL writes SDI to the binary log for distributed backups. \[错误] 3. MySQL Server stores a copy of the serialized data in the InnoDB user tablespace. \[正确] 4. MySQL keeps InnoDB metadata changes in .sdi files in datadir. \[错误] 5. MySQL implicitly executes FLUSH TABLES and stores a snapshot backup of the metadata. \[错误] > InnoDB 将元数据以 SDI 格式序列化存储于表空间文件中，便于备份恢复，而不是写入 binlog 或 datadir 的文件。 ## 试题 86 Choose three. Which three settings control global buffers shared by all threads on a MySQL server? 1. key\_buffer\_size \[正确] 2. read\_buffer\_size \[错误] 3. tmp\_table\_size \[错误] 4. sort\_buffer\_size \[错误] 5. innodb\_buffer\_pool\_size \[正确] 6. table\_open\_cache \[正确] > key\_buffer\_size、innodb\_buffer\_pool\_size 和 table\_open\_cache 是全局缓冲区设置，影响整个服务器性能，而 read\_buffer 和 sort\_buffer 是会话级别的。 ## 试题 87 Choose two. You are using mysqlcheck for server maintenance. Which two statements are true? 1. The mysqlcheck --optimize --all-databases command reclaims free space from table files. \[正确] 2. The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default. \[正确] 3. The mysqlcheck --repair --all-databases command can repair an InnoDB corrupted table. \[错误] 4. The mysqlcheck --check --all-databases command takes table write locks while performing a series of checks. \[错误] 5. The mysqlcheck --analyze --all-databases command performs a series of checks to spot eventual table corruptions. \[错误] > mysqlcheck 可用于优化表空间和修复 MyISAM 表，不能修复 InnoDB 表，也不会默认执行检查或加写锁；重命名工具行为会变化。 ## 试题 88 Which two MySQL Server accounts are locked by default? 1. any user created without a password \[错误] 2. any internal system accounts \[正确] 3. any new ROLE accounts \[正确] 4. any user created with a username, but missing the host name \[错误] 5. any user set as DEFINER for stored programs \[错误] > 内部系统账户和新建的角色账户默认锁定，防止未经授权访问；普通用户创建时默认不锁定。 ## 试题 89 Choose three. Your MySQL server is running on the Microsoft Windows platform. Which three local connection protocols are available to you? 1. named pipes \[正确] 2. X Protocol \[错误] 3. shared memory \[正确] 4. TCP/IP \[正确] 5. UDP \[错误] 6. SOCKET \[错误] > Windows 支持 named pipes、shared memory 和 TCP/IP 协议本地连接，不支持 UNIX socket 和 UDP；X Protocol 属于另一种服务机制。 --- ## 试题 90 Which two authentication plugins require the plain text client plugin for authentication to work? 1. PAM authentication \[正确] 2. Windows Native authentication \[错误] 3. SHA256 authentication \[错误] 4. LDAP SASL authentication \[错误] 5. MySQL Native Password \[错误] 6. LDAP authentication \[正确] > PAM 和 LDAP 这两种认证机制在进行认证时需要客户端发送明文密码，因此需要使用明文客户端插件才能实现认证功能。其他认证插件如 Native Password、SHA256 等不会强制要求明文插件。 --- ## 试题 91 Choose two. Which two statements are true about the data dictionary object cache? 1. Character set and collation definition objects are not cached. \[错误] 2. The dictionary object caches use a Least Recently Used (LRU) algorithm to manage entries in each cache. \[正确] 3. If the dictionary object cache becomes full, MySQL server will be unable to create any more tables/objects. \[错误] 4. tablespace\_definition\_cache sets the number of tablespace objects that can be stored in the dictionary object cache. \[正确] 5. All dictionary object caches have a hard-coded size. \[错误] > 数据字典对象缓存使用 LRU 算法进行管理，此外 tablespace\_definition\_cache 参数控制可缓存的表空间对象数。字符集与校对对象是可以缓存的。缓存满并不会导致无法创建新对象，因为旧对象会被清除。缓存大小是可配置的，并非硬编码。 --- ## 试题 92 Choose two. Examine this statement, which executes successfully: CREATE USER mary\@192.0.2.100 IDENTIFIED BY 'P\@SSw0rd' REQUIRE NONE PASSWORD EXPIRE; Which two are true? 1. Mary must connect using the username 'mary\@192.0.2.100'. \[错误] 2. Mary must connect from the client machine 192.0.2.100. \[正确] 3. Mary cannot connect to the MySQL server until the DBA resets her password. \[错误] 4. Mary requires no password to connect to the MySQL server. \[错误] 5. Mary cannot query data until she changes her password. \[正确] > 该用户只能从 192.0.2.100 主机连接，并且由于设置了 PASSWORD EXPIRE，因此在首次连接后必须更改密码才能执行查询等操作，但可以登录。用户名不包含主机部分。 --- ## 试题 93 Choose two. Examine this command, which executes successfully on InnoDB Cluster: dba.dropMetadataSchema() Which two statements are true? 1. Connections driven by MySQL Router are not affected by the command. \[错误] 2. The mysql\_innodb\_cluster\_metadata schema is dropped from the instance where the connection was established. \[错误] 3. The mysql\_innodb\_cluster\_metadata schema is dropped from all reachable members of the cluster. \[正确] 4. Group Replication will be dissolved and all metadata purged. \[错误] 5. The command drops the mysql\_innodb\_cluster\_metadata schema and re-creates it. \[错误] 6. Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell. \[正确] > 此命令会从所有可达节点删除 metadata schema，因此集群元数据丢失。组复制仍在运行，但必须重新导入集群以恢复管理功能。它并不会重新创建该 schema，也会影响由 Router 连接的客户端。 --- ## 试题 94 Choose two. User account baduser\@hostname on your MySQL instance has been compromised. Which two commands stop any new connections using the compromised account? 1. ALTER USER baduser\@hostname DEFAULT ROLE NONE; \[错误] 2. ALTER USER baduser\@hostname IDENTIFIED WITH mysql\_no\_login; \[正确] 3. ALTER USER baduser\@hostname PASSWORD DISABLED; \[错误] 4. ALTER USER baduser\@hostname ACCOUNT LOCK; \[正确] 5. ALTER USER baduser\@hostname MAX\_USER\_CONNECTIONS 0; \[错误] > 使用 mysql\_no\_login 插件或锁定账户可以有效阻止该用户建立新的连接。设置角色或最大连接数不会阻止认证过程，密码禁用不是有效语法。 --- ## 试题 95 Choose two. Which two are use cases of MySQL asynchronous replication? 1. You can scale reads by adding multiple slaves. \[正确] 2. You can scale writes by creating a replicated mesh. \[错误] 3. It guarantees near real-time replication between a master and a slave. \[错误] 4. It allows backup to be done on the slave without impacting the master. \[正确] 5. MySQL Enterprise Backup will automatically back up from an available slave. \[错误] > 异步复制常用于读取扩展和备份容错，不支持写入扩展或实时一致性。MySQL Enterprise Backup 并不会自动选择备份源。 --- ## 试题 96 Choose two. Examine this command, which executes successfully: mysqlpump --user=root --password > full\_backup.sql Which two databases will be excluded from this dump? 1. mysql \[错误] 2. information\_schema \[正确] 3. employee \[错误] 4. world \[错误] 5. sys \[正确] > 默认情况下，mysqlpump 会排除 information\_schema 和 sys 两个非实际存储的数据库。mysql 数据库默认会导出。 --- ## 试题 97 Choose two. A valid raw backup of the shop.customers MyISAM table was taken. You must restore the table. You begin with these steps: 1. Confirm that secure\_file\_priv= '/var/tmp' 2. mysql> DROP TABLE shop.customers; 3. shell> cp /backup/customers.MY\* /var/lib/mysql/shop/ Which two actions are required to complete the restore? 4. shell> cp /backup/customers.sdi /var/tmp \[正确] 5. mysql> SOURCE '/var/tmp/customers.sdi' \[错误] 6. shell> cp /backup/customers.frm /var/lib/mysql/shop/ \[错误] 7. mysql> ALTER TABLE shop.customers IMPORT TABLESPACE \[错误] 8. shell> cp /backup/customers.sdi /var/lib/mysql/shop/ \[错误] 9. mysql> IMPORT TABLE FROM /var/tmp/customers.sdi \[正确] 10. mysql> IMPORT TABLE FROM /var/lib/mysql/shop/customers.sdi \[错误] > MyISAM 表恢复中需要将相关的 .MYD, .MYI 和 .sdi 文件复制到目标目录，并使用 IMPORT TABLE 命令从指定路径加载 .sdi 文件。其他方式如 ALTER TABLE 无法用于 MyISAM，且不能直接 source 执行。 --- ## 试题 98 Choose two. Which two MySQL Shell commands are excluded from the InnoDB Cluster creation procedure? 1. dba.createCluster() \[错误] 2. dba.configureLocalInstance() \[错误] 3. cluster.forceQuorumUsingPartitionOf() \[正确] 4. cluster.setPrimaryInstance() \[正确] 5. cluster.addInstance() \[错误] 6. dba.checkInstanceConfiguration() \[错误] 7. dba.configureInstance() \[错误] > 创建集群过程中并不包含 quorum 恢复命令和设置主节点命令，这些属于集群维护操作，而非初始化配置的一部分。其他命令都是用于部署时的检查与配置。 --- ## 试题 99 Choose two. Which two commands can be used to change the authentication method for a user account? 1. ALTER USER foo\@localhost IDENTIFIED BY 'password' REQUIRE NONE; \[错误] 2. ALTER USER foo\@localhost IDENTIFIED WITH caching\_sha2\_password; \[正确] 3. CREATE USER foo\@localhost IDENTIFIED WITH caching\_sha2\_password; \[正确] 4. SET PASSWORD FOR foo\@localhost = PASSWORD('password'); \[错误] 5. GRANT USAGE ON *.* TO foo\@localhost IDENTIFIED WITH mysql\_native\_password; \[错误] > `ALTER USER` 和 `CREATE USER` 都可以指定认证插件（如 caching\_sha2\_password）来更改认证方式。`SET PASSWORD` 只更新密码，不更改认证插件；`GRANT` 无法指定认证插件。 --- ## 试题 100 Choose two. Which two tables are required to set up roles? 1. user \[正确] 2. default\_roles \[正确] 3. role\_edges \[正确，但不是选项之一] 4. mysql.roles\_mapping \[错误] 5. db \[错误] > 用户信息保存在 `user` 表中，默认角色信息保存在 `default_roles` 表中。`role_edges` 记录角色之间的继承关系，但未列入选项。`db` 与授权相关，`roles_mapping` 并不存在。 --- ## 试题 101 Choose two. Which two scenarios would benefit from using MySQL Enterprise Firewall? 1. Restrict access to tables based on user roles. \[错误] 2. Prevent SQL injection attacks. \[正确] 3. Prevent access from unknown client IPs. \[错误] 4. Enforce only allowed SQL statements for users. \[正确] 5. Detect and terminate long-running queries. \[错误] > MySQL Enterprise Firewall 的主要功能是防止 SQL 注入攻击，并基于“学习”模式限制用户只执行已知合法的 SQL 语句。它不处理基于角色的访问控制或连接策略。 --- ## 试题 102 Choose two. Which two statements are true about server-side prepared statements? 1. They can only be used with stored procedures. \[错误] 2. They improve performance for repeated executions. \[正确] 3. They reduce parsing and compilation overhead on the server. \[正确] 4. They prevent SQL injection in all cases. \[错误] 5. They execute multiple statements in one round-trip to the server. \[错误] > 服务器端预处理语句的主要优势是避免重复解析和编译，从而提升性能。但并不保证完全防止 SQL 注入（依赖绑定方式和使用方式）。它们并不限于存储过程，也不是用于批处理多条 SQL 的机制。 --- ## 试题 103 Which two can minimize security risks when creating user accounts? 1. Do not allow accounts without passwords. \[正确] 2. Require users to have the FIREWALL USER privilege defined. \[错误] 3. Require the use of mixed case usernames. \[错误] 4. Avoid the use of wildcards in host names. \[正确] 5. Avoid the use of wildcards in usernames. \[错误] > 创建用户时，避免无密码账户和避免在主机名中使用通配符有助于增强安全性。混合大小写的用户名并不能显著提高安全性，而 FIEWALL USER 权限也不是最常见的要求。避免用户名使用通配符也不是必须的。 --- ## 试题 104 Choose two. Mary connects to a Linux MySQL Server from a client on a Windows machine. Examine this statement and output:  Which two are true? 1. Mary has the privileges of account mary@%. \[正确] 2. Mary authenticated to the account mary\@192.0.2.101. \[错误] 3. Mary connected using a UNIX socket. \[错误] 4. Mary connected to the database server whose IP address is 192.0.2.101. \[错误] 5. Mary connected from a client machine whose IP address is 192.0.2.101. \[正确] > `mary@%` 是一个代表任意主机的账户，意味着这个账户可以从任何 IP 地址连接。Mary 实际上是从 Windows 客户端连接到 MySQL 服务器的，因此 IP 地址为 `192.0.2.101` 是指她的客户端而非数据库服务器。 --- ## 试题 105 Choose two. Which two actions can obtain information about deadlocks? 1. Run the SHOW ENGINE INNODB STATUS command from the mysql client. \[正确] 2. Run the SHOW ENGINE INNODB MUTEX command from the mysql client. \[错误] 3. Enable the innodb\_status\_output\_locks global parameter. \[错误] 4. Use the sys.innodb\_lock\_waits view. \[错误] 5. Enable the innodb\_print\_all\_deadlocks global parameter. \[正确] > `SHOW ENGINE INNODB STATUS` 命令和 `innodb_print_all_deadlocks` 参数可以帮助捕获并记录死锁信息。`innodb_status_output_locks` 和 `sys.innodb_lock_waits` 虽然与死锁相关，但并不是获取死锁信息的主要手段。 --- ## 试题 106 Choose two. Which two are true about binary logs used in asynchronous replication? 1. They contain events that describe database changes on the master. \[正确] 2. They contain events that describe only administrative commands run on the master. \[错误] 3. They contain events that describe all queries run on the master. \[错误] 4. They are pushed from the master to the slave. \[错误] 5. They are pulled from the master to the slave. \[正确] > 二进制日志记录的是主服务器上的数据库更改事件，用于复制。在异步复制中，二进制日志是从主服务器被“拉取”到从服务器，而不是“推送”。 --- ## 试题 107 Choose two. A scientific data gathering application uses a MySQL instance back end for data management. There is a high concurrency of transactions at thousands of transactions per second of volatile data. A restore from binary logs is planned using the command: `mysqlbinlog --start-datetime='2019-08-01 11:00:00' --stop-datetime='2019-08-10 08:30:25' binlog.000238 binlog.000239 binlog.000240 mysql` Which two characteristics cause the restore to be inconsistent to the original data? 1. The time span of binary logs is too long to restore. \[正确] 2. Temporary tables cannot persist across binary logs. \[错误] 3. Transaction rate is too high to get a consistent restore. \[错误] 4. Multiple binary logs cannot be specified on the command line. \[错误] 5. The temporal values do not offer high enough precision. \[正确] > 二进制日志恢复的时间跨度过长，可能导致恢复的数据不一致，因为在高并发的环境下，数据的变更可能会更加复杂。此外，时间戳精度不足可能导致某些数据丢失或错位。 --- ## 试题 108 Choose two. Which two are contained in the InnoDB system tablespace (ibdata1) by default? 1. change buffer \[正确] 2. doublewrite buffer \[正确] 3. InnoDB Data Dictionary \[错误] 4. table data \[错误] 5. user privileges \[错误] > `ibdata1` 文件包含了 InnoDB 的更改缓冲（change buffer）和双写缓冲（doublewrite buffer），但不包含数据字典或表数据。用户权限存储在 `mysql` 数据库中，而不是在 InnoDB 系统表空间中。 --- ## 试题 109 Choose two. Examine these InnoDB Cluster parameter settings: `cluster.setInstanceOption('host1:3377', 'memberWeight', 40)` `cluster.setInstanceOption('host2:3377', 'memberWeight', 30)` `cluster.setInstanceOption('host3:3377', 'memberweight', 40)` `cluster.setInstanceOption('host3:3377', 'exitstateAction', 'ABORT_SERVER')` `cluster.setOption('expelTimeout', 1)` Now examine the partial status:  A permanent network failure isolates host3. Which two statements are true? 1. The instance deployed on host2 is elected as the new primary instance. \[错误] 2. The issuing command `cluster.switchToMultiPrimaryMode()` will fail to enable multi-primary mode. \[错误] 3. Failure of the instance deployed on host1 provokes an outage. \[错误] 4. The primary instance can be specified by using the command `cluster.setPrimaryInstance (<host>:<port>)`. \[正确] 5. The instance deployed on host3 will automatically rejoin the cluster when connectivity is re-established. \[错误] 6. The instance deployed on host3 is expelled from the cluster and must be rejoined using `cluster.addInstance('host3:3377')`. \[正确] > `host1` 和 `host2` 的权重分别为 40 和 30，`host3` 被设置为在丢失连接时自动关闭 MySQL 服务并需要手动重新加入集群。当 `host3` 因网络故障断开时，它需要手动重新加入，并不会自动成为新的主节点或重新连接到集群。 --- ## 试题 110 Which two files must be restored from backup to allow the DB to restart cleanly after a clean shutdown with `innodb_fast_shutdown=0`, followed by accidental deletion of all files from the top-level data directory? 1. **ibdata1** \[正确] 2. undo\_001 \[错误] 3. ibtmp1 \[错误] 4. **mysql.ibd** \[正确] 5. ib\_logfile0 \[错误] 6. ib\_buffer\_pool \[错误] > `ibdata1` 文件存储了 InnoDB 数据字典、表空间和事务日志等信息，而 `mysql.ibd` 是存储 MySQL 系统表的文件。 --- ## 试题 111 Examine the output of this query: `SELECT * FROM performance_schema.table_io_waits_summary_by_table WHERE COUNT_STAR > 0;` Which two statements are true? 1. **Average read times are approximately three times faster than writes.** \[正确] 2. **22902028 rows were deleted. These columns aggregate all delete operations.** \[正确] 3. The longest I/O wait was for writes. \[错误] 4. The I/O average time is 532728. These columns aggregate all fetch operations. \[错误] 5. I/O distribution is approximately 50/50 read/write. \[错误] > 从输出数据来看，读取的平均时间是写入操作的三分之一，删除操作的计数为 22902028，数据没有显示写操作的最长 I/O 等待。 --- ## 试题 112 Which two statements are true about using backups of the binary log? 1. **Multiple binary logs can be used to restore data.** \[正确] 2. **They allow for point-in-time recovery of the data.** \[正确] 3. Multiple binary logs can be applied in parallel for faster data restoration. \[错误] 4. Binary logs are relatively small, and therefore, excellent for long-term storage and disaster recovery. \[错误] 5. Binary logs can always be used to unapply unwanted schema changes. \[错误] > 二进制日志支持逐步恢复，便于进行时间点恢复，但并不能并行应用，且不适合长期存储。 --- ## 试题 113 Examine the command `dba.dropMetadataSchema()` on an InnoDB Cluster. Which two statements are true? 1. Connections driven by MySQL Router are not affected by the command. \[错误] 2. **The `mysql_innodb_cluster_metadata` schema is dropped from all reachable members of the cluster.** \[正确] 3. The command drops the `mysql_innodb_cluster_metadata` schema and re-creates it. \[错误] 4. **Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell.** \[正确] 5. Group Replication will be dissolved and all metadata purged. \[错误] > `dba.dropMetadataSchema()` 会删除集群中的元数据模式，并且 Group Replication 仍然是可操作的，但 InnoDB 集群需要在 MySQL Shell 中重新导入。 --- ## 试题 114 Which two groups of files will be backed up during a full backup using `mysqlbackup`? 1. **`*.ibd` files** \[正确] 2. **`ib_logfile*` files** \[正确] 3. `*.sdi` files \[错误] 4. `*.CSM` files \[错误] 5. `ibbackup` files \[错误] > `mysqlbackup` 备份会包括 InnoDB 表的 `.ibd` 文件和事务日志文件 `ib_logfile*`。 --- ## 试题 115 Which two characteristics of snapshot-based backups are true? 1. **Snapshot-based backups greatly reduce time during which the database and applications are unavailable.** \[正确] 2. **A separate physical copy must be made before releasing the snapshot backup.** \[正确] 3. There is no need for InnoDB tables to perform its own recovery when restoring from the snapshot backup. \[错误] 4. Snapshot backups can be used only in virtual machines. \[错误] 5. The frozen file system can be cloned to another virtual machine immediately into active service. \[错误] > 快照备份能够减少数据库和应用程序不可用的时间，并且在释放快照之前需要创建物理副本。 --- ## 试题 116 Which three storage engines would return a nonempty recordset for the `test` table after executing the following commands? ```sql TRUNCATE test; BEGIN; INSERT INTO test (id, name) VALUES(1, 'Hello'); ROLLBACK; SELECT id FROM test; ``` 1. **MEMORY** \[正确] 2. **MyISAM** \[正确] 3. InnoDB \[错误] 4. BLACKHOLE \[错误] 5. **ARCHIVE** \[正确] > `MEMORY` 和 `MyISAM` 引擎会在事务回滚后保留数据，因为它们不支持事务。`InnoDB` 支持事务并会回滚数据。 --- ## 试题 117 Which two statements are true about the `mysql_config_editor` program? 1. It manages the configuration of the MySQL Firewall feature. \[错误] 2. **It will use client options by default unless you provide `--login-path`.** \[正确] 3. It can be used to create and edit SSL certificates and log locations. \[错误] 4. It provides an interface to change `my.cnf` files. \[错误] 5. It manages the configuration of user privileges for accessing the server. \[错误] 6. **It manages the configuration of client programs.** \[正确] > `mysql_config_editor` 用于管理客户端的配置，如用户名、主机名和端口等，不涉及防火墙、SSL 或用户权限管理。 --- ## 试题 118 Which two statements are true about the binary log encryption feature? 1. **It can be set at runtime.** \[正确] 2. It can be activated per session. \[错误] 3. **It requires a keyring plugin.** \[正确] 4. When enabled, it encrypts existing binary logs. \[错误] 5. It encrypts any connecting slaves' connection threads. \[错误] > 二进制日志加密需要在运行时启用并依赖 keyring 插件，但不会加密已存在的二进制日志或中继日志。 --- ## 试题 119 Which two `--ssl-mode` values will ensure that an X.509-compliant certificate will be used to establish the SSL/TLS connection to MySQL? 1. REQUIRED \[错误] 2. **VERIFY\_IDENTITY** \[正确] 3. **VERIFY\_CA** \[正确] 4. DISABLED \[错误] 5. PREFERED \[错误] > `VERIFY_IDENTITY` 和 `VERIFY_CA` 会要求使用符合 X.509 的证书进行 SSL/TLS 连接。 --- ## 试题 120 Choose two. Examine this query and its output:（见下图）Which two statements are true?  1. User bob had a significantly higher ratio of SELECT + INSERT statements to QUIT than both app and root users. \[错误] 2. The root user had the largest number of modified rows for a SELECT statement. \[错误] C) The app user had the highest total number of rows read from storage engines. \[正确] 1. The root user had the largest single wait time. \[正确] 2. User bob had the largest total time waiting for locks. \[错误] > 对于第一题，正确答案是C和1。首先，bob用户的SELECT、INSERT与QUIT语句的比率比app和root要高是错误的。根据输出，app用户的SELECT + INSERT语句对比QUIT语句的比例相对较高。第二，root用户的SELECT语句修改的行数也不多，实际情况是app用户在存储引擎读取的行数最多。关于最大单次等待时间，root用户确实拥有最大等待时间。bob用户的锁等待时间最少，而app用户拥有较高的锁等待时间，达到分钟级别。 ## 试题 121 Examine this SQL statement: mysql> GRANT r\_read\@localhost To mark WITH ADMIN OPTION; Which two are true? (Choose two.) D) Mark can revoke the r\_read\@localhost role from another role. \[正确] 1. Mark can grant the r\_read\@localhost role to another user. \[正确] 2. Mark can grant the privileges assigned to the r\_read\@localhost role to another user. \[错误] 3. ADMIN OPTION allows Mark to drop the role. \[错误] 4. ADMIN OPTION causes the role to be activated by default. \[错误] 5. Mark must connect from localhost to activate the r\_read\@localhost role. \[错误] > 对于第121题，正确答案是D和1。标记（Mark）具有ADMIN OPTION，因此能够撤销其他角色的r\_read\@localhost角色。而Mark不能将r\_read\@localhost角色的权限授予其他用户，因为ADMIN OPTION并不包含权限授予其他用户的能力。ADMIN OPTION不意味着可以删除角色或默认激活角色，Mark连接到localhost才会激活角色的说法也错误。 ## 试题 122 Choose two . Which two statements are true about MySQL Installer? 1. Manual download of separate product packages is required before installing them through MySQL Installer. \[错误] 2. It performs product upgrades. \[正确] 3. It provides only GUI-driven, interactive installations. \[错误] 4. It installs most Oracle MySQL products. \[正确] 5. It provides a uniform installation wizard across multiple platforms. \[错误] > 对于第122题，正确答案是2和4。MySQL Installer可以执行产品升级，并且它确实支持安装大多数Oracle MySQL产品。然而，它不要求手动下载单独的产品包，且并不提供跨平台的统一安装向导。此外，它不只提供图形界面的交互式安装方式。 ## 试题 123 Choose three. Which three actions are effective in capacity planning? 1. buying more RAM \[错误] 2. buying more disk \[错误] 3. monitoring OS resources for patterns \[正确] 4. upgrading to the latest application version \[错误] 5. adding circular replication nodes for increased DML capability \[错误] 6. basing expected growth on an average of the last 3 years \[正确] 7. buying more CPU \[错误] 8. consulting the application team about any future projects and use \[正确] > 对于第123题，正确答案是3、6和8。在容量规划中，监控操作系统资源的模式非常关键，理解资源使用情况有助于做出合理的扩展决策。此外，预计的增长应基于过去三年的平均增长，而与应用团队沟通未来项目的需求同样重要。购买更多的硬件资源（如RAM、磁盘、CPU）并不总是解决问题的最佳方式，循环复制节点对DML能力的提升也有限。 ## 试题 124 Choose the best answer. Four nodes are configured to use circular replication. Examine these configuration parameters for each node: slave\_parallel\_type=DATABASE ; slave\_parallel\_workers=4 slave\_preserve\_commit\_order=0 Which statement is true? 1. Cross-database constraints can cause database inconsistency. \[正确] 2. Setting slave\_preserve\_commit\_order to ON will improve data consistency. \[错误] 3. Setting slave\_parallel\_type=DATABASE won't work for circular replication; it should be set to LOGICAL\_CLOCK. \[错误] 4. Setting transaction\_allow\_batching to ON will improve data consistency. \[错误] 5. Increasing slave\_parallel\_workers will improve high availability. \[错误] 6. Each slave thread is responsible for updating a specific database. \[错误] > 第124题的正确答案是1。跨数据库约束可能导致数据库不一致，特别是在循环复制中。设置slave\_preserve\_commit\_order为ON时，可以确保从库按照与主库提交的顺序一致地回放日志，但在这种情况下，应该使用LOGICAL\_CLOCK而非DATABASE。此外，增加slave\_parallel\_workers并不会直接提高高可用性，transaction\_allow\_batching设置也无法改善数据一致性。 ## 试题 125 Examine this partial output for InnoDB Cluster status:“topology”;“host1:3377”:”address”;”host1:3377”,“mode”:”R/W”, “STATUS”:”ONLINE”,“version”;”8.0.18’,“host1:3377”:”address”;”host2:3377”,“mode”:”R/O”, “STATUS”:”MISSING”,,“host1:3377”:”address”;”host3:3377”,“mode”:”R/O”, “STATUS”:”ONLINE”,“version”;”8.0.18’Which statement explains the state of the instance deployed on host2? 1. It can be recovered from a donor instance on host3 by cloning using the command cluster.rejoinInstance ('<user>@host3:3377') . \[正确] E) It can rejoin the cluster by using the command dba. rebootClusterFromCompleteOutage() . \[错误] 1. It can rejoin the cluster by using the command cluster.addInstance ('<user>@host3:3377') . \[错误] 2. It has been removed from the cluster by using the command STOP GROUP\_REPLICATION;. . \[错误] 3. It has been expelled from the cluster because of a transaction error. \[错误] > 对于第125题，正确答案是1。如果节点（如host2上的实例）被意外删除或停止，它可以通过执行`cluster.rejoinInstance`命令重新加入集群。其他选项描述的情况，例如通过`dba.rebootClusterFromCompleteOutage()`恢复集群、使用`cluster.addInstance`重新加入或执行`STOP GROUP_REPLICATION`等，都不适用。 ## 试题 126 Choose the best answer. Examine this command\:shell> mysqldump --no-create-info --all-databases --result-file=dump.sql Which statement is true? D) It will not write CREATE TABLE statements. \[正确] 1. It will not write CREATE TABLESPACE statements. \[错误] 2. It will not write CREATE LOGFILE GROUP statements. \[错误] 3. It will not write CREATE DATABASE statements. \[错误] > 第126题，正确答案是D。该命令会导出所有数据库的内容，但不会导出表的创建语句（CREATE TABLE），这是通过`--no-create-info`选项指定的。此命令会导出数据，但不会导出表空间、日志文件组或数据库的创建语句。 ## 试题 127 MySQL programs look for option files in standard locations. Which method will show the option files and the order in which they are read? 1. mysql> SHOW GLOBAL VARIABLES; \[错误] 2. shell> mysql --print-defaults \[错误] 3. shell> mysqladmin --debug \[错误] 4. shell> mysqld --help --verbose \[正确] > 解析：`mysqld --help --verbose` 可以显示 MySQL 启动时加载的配置文件的路径和顺序。其他选项并不会提供该信息。 ## 试题 128 Choose the best answer. Examine this command, which executes successfully: ``` $ mysqlbackup --user=dba --password --port= 3306 --with-timestamp --only-know-file-types --backup-dir=/export/backups backup ``` Which statement is true? 1. Only non-encrypted files are backed up. \[错误] 2. Only files for MySQL or its built-in storage engines are backed up. \[正确] 3. Only tables stored in their own tablespaces are backed up. \[错误] 4. Only InnoDB data and log files are backed up. \[错误] > 解析：`mysqlbackup` 命令仅会备份 MySQL 或其内建存储引擎的数据文件。备份目录包括了用于 MySQL 存储的所有文件类型。 ## 试题 129 What does the slave I/O thread do? 1. connects to the master and requests it to send updates recorded in its binary logs \[正确] 2. acquires a lock on the binary log for reading each event to be sent to the slave \[错误] 3. reads the relay log and executes the events contained in them \[错误] 4. monitors and schedules I/O calls to the subsystem for the relay logs \[错误] > 解析：在 MySQL 复制架构中，slave 的 I/O 线程的作用是连接到主服务器并请求它发送记录在二进制日志中的更新，而不是直接从 relay log 中读取事件。 ## 试题 130 Choose the best answer. Which statement is true about the my.ini file on a Windows platform while MySQL server is running? 1. Editing the file will immediately change the running server configuration. \[错误] 2. Using SET PERSIST will update the my.ini file. \[错误] 3. MySQL server does not use the my.ini option file for server configuration options. \[错误] 4. The option file is read by the MySQL server service only at start up. \[正确] > 解析：MySQL 服务器在启动时会读取 `my.ini` 文件中的配置选项，而不是动态加载更改。因此，编辑该文件后必须重启服务器才会生效。 ## 试题 131 Examine the full path name of the backup image from MySQL Enterprise Backup with the --compress option: ``` /backup/full/mybackup/myimage.img ``` mysqlbackup.cnf contains this data: ``` mysqlbackup backup-dir=/backup/full/myrestore backup-image=/backup/full/mybackup/myimage.img uncompress ``` You must perform a database restore to a new machine. Which command can provision the new database in datadir as /data/MEB? 1. mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB image-to-dir-and-apply-log \[错误] 2. mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB apply-log-and-copy-back \[错误] 3. mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB copy-back-and-apply-log \[正确] 4. mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB restore-and-apply-log \[错误] 5. mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB image-to-dir \[错误] > 解析：`copy-back-and-apply-log` 是恢复数据并同时应用日志的正确命令，它能够将备份的数据库文件恢复到新的数据目录中。 ## 试题 132 Choose the best answer. MySQL Enterprise Monitor Query Analyzer is configured to monitor an instance. Which statement is true? 1. The slow query log must be enabled on the monitored server to collect information for the Query Analyzer. \[错误] 2. The Query Response Time index (QRTi) is fixed to 100ms and cannot be customized. \[错误] 3. An agent must be installed locally on the instance to use the Query Analyzer. \[错误] 4. Enabling the events\_statements\_history\_long consumer allows tracking the longest running query. \[正确] 5. The Query Analyzer can monitor an unlimited number of normalized statements. \[错误] > 解析：Query Analyzer 使用 `events_statements_history_long` 消费者来跟踪运行时间最长的查询，因此启用此选项能够收集查询数据。 ## 试题 133 Choose the best answer. Users report errors when trying to connect from 192.0.2.5 and are connecting using the mysql\_native password authentication plugin. Examine these commands and output: （见下图）  Which statement identifies the cause of the errors? 1. thread\_cache is too small. \[错误] 2. skip\_name\_resolve is enabled. \[错误] 3. Connections are attempted without a valid user account or password. \[错误] 4. Network connectivity issues occurring between client and the MySQL instance. \[正确] 5. User accounts are defined using the mysql\_native\_password plugin for password authentication. \[错误] 6. max\_connections is too small. \[错误] > 解析：根据错误信息，可以推断问题出在客户端与 MySQL 实例之间的网络连接问题，而不是认证插件或其他因素。 ## 试题 134 You want to check the values of the sort\_buffer\_size session variables of all existing connections. Which performance\_schema table can you query? 1. variables\_by\_thread \[正确] 2. session\_variables \[错误] 3. global\_variables \[错误] 4. user\_variables\_by\_thread \[错误] > 解析：`variables_by_thread` 表包含了每个线程的所有会话级变量，包括 `sort_buffer_size`，因此可以用来查询每个线程的配置值。 ## 试题 135 Choose the best answer. Which feature is provided by multi-source replication? 1. providing a common source for the same data to be replicated to other servers \[错误] 2. allowing multiple servers to back up to one server \[正确] 3. managing conflicts between two sets of the same data \[错误] 4. providing multi-source replication where all servers act as the master \[错误] > 解析：多源复制的功能是允许多个服务器将数据备份到一个服务器，而不是将数据源共享给其他服务器。 ## 试题 136 Choose the best answer. It is a non-empty InnoDB table. Examine these statements, which are executed in one session: ``` BEGIN SELECT * FROM t FOR UPDATE; ``` Which is true? 1. If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback. \[正确] 2. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status. \[错误] 3. If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back. \[错误] 4. mysqlcheck --analyze --all-databases will execute normally on all tables and return a report. \[错误] > 解析：执行 `OPTIMIZE TABLE` 时，会在表上创建一个锁，从而强制事务回滚，以确保表优化过程的完整性。 ## 试题 137 Choose the best answer. You have upgraded the MySQL binaries from 5.7.28 to 8.0.18 by using an in-place upgrade. Examine the message sequence generated during the first start of MySQL 8.0.18: （见下图）  Which step or set of steps will resolve the errors? 1. Start mysqld again using the --upgrade=FORCE option. \[错误] 2. Execute: mysqlcheck --repair mysql columns\_priv event proc proxies\_priv tables\_priv. \[正确] 3. Go to the <datadir>/mysql directory and execute: myisamchk --update-state columns\_priv event proc proxies\_priv tables\_priv. \[错误] 4. Execute: mysqlcheck --check-upgrade mysql columns\_priv event proc proxies\_priv tables\_priv. \[错误] 5. Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables for upgrade. Upgrade to 8.0.18 again. \[错误] > 解析：升级后，修复表的结构是解决问题的关键，使用 `mysqlcheck --repair` 可以修复出错的表。其他选项并没有正确解决问题。 ## 试题 138 Choose the best answer. Which statement is true about MySQL Enterprise Transparent Data Encryption (TDE)? 1. TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace. \[错误] 2. Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification. \[错误] 3. Both MyISAM and InnoDB tables can be encrypted by setting the keyring\_engine = All variable in the MySQL configuration file. \[错误] 4. MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location. \[正确] > 解析：MySQL TDE 使用适当的 keyring 插件将加密密钥存储在集中位置，而不是通过配置变量控制所有表的加密。 ## 试题 139 You are using the InnoDB engine and the innodb\_file\_per\_table option is set. You delete a significant number of rows of a large table named FACTORY.INVENTORY. Which command will reorganize the physical storage of table data and associated index data for the INVENTORY table, in order to reduce storage space and improve I/O efficiency? 1. ANALYZE TABLE FACTORY.INVENTORY \[错误] 2. OPTIMIZE TABLE FACTORY.INVENTORY \[正确] 3. mysqldump -u root -p FACTORY INVENTORY \[错误] 4. CHECK TABLE FACTORY.INVENTORY \[错误] 5. mysqlcheck -u root -p FACTORY.INVENTORY \[错误] > 解析：使用 `OPTIMIZE TABLE` 命令可以整理表的物理存储，减少存储空间并提高 I/O 效率，特别是对于 InnoDB 引擎开启 `innodb_file_per_table` 选项的情况。 ## 试题 140 Choose the best answer. 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? 1. REQUIRED \[错误] 2. VERIFY\_CA \[错误] 3. PREFERRED \[错误] 4. VERIFY\_IDENTITY \[正确] > 解析：选项 VERIFY\_IDENTITY 会确保 SSL 连接不仅验证证书颁发机构（CA），还会验证服务器主机名，提供最高级别的安全性，防止中间人攻击。 ## 试题 141 Choose the best answer. You want to dump all databases with names that start with \db. Which command will achieve this? 1. mysqlpump --include-databases=db% --result-file=all\_db\_backup.sql \[正确] 2. mysqlpump --include-databases=db -- result-file=all\_db\_backup.sql \[错误] 3. mysqlpump > all\_db\_backup.sql \[错误] 4. mysqlpump -- include-tables-db.% --result-file=all\_db\_backup.sql \[错误] > 解析：选项 1 使用 `--include-databases=db%` 参数正确匹配以 db 开头的数据库名称，并生成备份文件。其他选项要么不匹配正确的数据库名，要么没有正确使用命令格式。 ## 试题 142 Choose two. You have an installation of MySQL 8 on Oracle Linux. Consider the outputs:（见下图）  Which statement is true about disk temporary tables for this installation? 1. Temporary tables are created in tmpdir only after they reach tmp\_table\_size. \[错误] 2. Temporary tables will use the InnoDB temporary tablespace located in /tmp. \[错误] 3. Only internal temporary tables from the optimizer will be created in tmpdir. \[错误] 4. Temporary tables will use the InnoDB temporary tablespace located in datadir. \[正确] 5. Temporary tables are created in tmpdir only if configured to use MyISAM. \[错误] > 解析：根据配置，临时表将使用位于 `datadir` 的 InnoDB 临时表空间，而不是 `tmpdir` 或 `MyISAM` 配置。InnoDB 临时表的存储方式通常与配置的 `datadir` 相关。 ## 试题 143 Choose the best answer. What is the correct syntax for using transparent data encryption with an existing InnoDB table? 1. ALTER TABLE t1 ENCRYPTION='Y'; \[正确] 2. ALTER TABLE t1 ADD ENCRYPTED\_TABLESPACE = 'Y'; \[错误] 3. ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY; \[错误] 4. ALTER TABLE t1 SET TDE = 'ON'; \[错误] > 解析：透明数据加密 (TDE) 可以通过 `ALTER TABLE` 命令启用，正确的语法是 `ALTER TABLE t1 ENCRYPTION='Y'`，其他选项的语法不符合 MySQL 的 TDE 配置规范。 ## 试题 144 Choose the best answer. You have configured GTID-based asynchronous replication with one master and one slave. A user accidentally updated some data on the slave. To fix this, you stopped replication and successfully reverted the accidental changes. Examine the current GTID information: （见下图）  You must fix GTID sets on the slave to avoid replicating unwanted transactions in case of failover. Which set of actions would allow the slave to continue replicating without erroneous transactions? 1. RESET SLAVE; SET GLOBAL gtid\_purged=aaaaaaa-aaa-aaaa-aaa-aaaaaaaa:1-10167; \[错误] 2. SET GLOBAL gtid\_purged=aaaaaa-aaa-aaa-aaaa-aaaaaaaa:1-2312, bbbbbb-bbbb-bbbb-bbbb- bbbbbbbbbb:1-9; SET GLOBAL gtid\_executed=aaaaaaa-aaaa-aaaa-aaaa-aaaaaa:1-10167; \[正确] 3. RESET MASTER; SET GLOBAL gtid\_purged=aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa:1-10167; \[错误] 4. RESET SLAVE; SET GLOBAL gtid\_purged=aaaaa-aaa-aaaa-aaaa-aaaaaaaa:1-3820; SET GLOBAL gtid\_executed=aaaaaa-aaaa-aaa-aaaa-aaaaaaaaaaa:1-10300; \[错误] 5. RESET MASTER; SET GLOBAL gtid\_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa:1-2312; SET GLOBAL gtid\_executed=aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa:1-10167; \[错误] > 解析：通过设置 `gtid_purged` 和 `gtid_executed` 的值，可以确保从库继续正确的 GTID 复制。选项 2 是正确的，其他选项要么操作不正确，要么 GTID 设置不完整。 ## 试题 145 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. Which contains all the settings that you need to add to or modify the slow log configuration? 1. long\_query\_time=5 log\_throttle\_quries\_not\_using\_indexes=5 min\_examined\_row\_limit=5000 \[错误] 2. long\_query\_time=5 \[错误] 3. min\_examined\_row\_limit=5000 \[错误] 4. long\_query\_time=5 min\_examined\_row\_limit=5000 \[正确] > 解析：要实现目标，需要设置 `long_query_time=5` 来记录超过 5 秒的查询，并使用 `min_examined_row_limit=5000` 来记录扫描超过 5000 行的查询。`log_throttle_queries_not_using_indexes=5` 选项并不适用。 ## 试题 146 Choose the best answer. What does the binlog dump thread do? 1. It reads the relay log and executes the events contained in them. \[错误] 2. It monitors and schedules the rotation/deletion of the binary logs. \[错误] 3. It acquires a lock on the binary log for reading each event to be sent to the slave. \[正确] 4. It connects to the master and asks it to send updates recorded in its binary logs. \[错误] > 解析：二进制日志转储线程通过锁定主库的二进制日志来读取事件，然后将这些事件传输到从库。它不会执行或删除日志，而是负责将日志事件发送到从库。 ## 试题 147 Choose the best answer. You want to check the values of the sort\_buffer\_size session variables of all existing connections Which performance\_schema table can you query? 1. user\_variables\_by\_thread \[错误] 2. global\_variables \[错误] 3. variables\_by\_thread \[正确] 4. session\_variables \[错误] > 解析：要查看所有现有连接的 `sort_buffer_size` 会话变量，可以查询 `variables_by_thread` 表。其他选项要么没有提供会话级变量的值，要么是全局变量。 ## 试题 148 题干过长，超过字数限制，请看下方图片。 1. systemd waited for 30 seconds before timing out and start up failed. \[错误] 2. systemd found the mysqld service disabled and failed to start it. \[错误] 3. systemd attempted to start mysqld, found another systemd mysqld process running, and shut it down. \[错误] 4. MySQL server was not started due to a problem while executing process 2732. \[正确] 5. MySQL server continued to start up even though another process existed. \[错误] > 解析：该题考察了 MySQL 启动失败的原因。选项 1 认为启动超时是由于等待 30 秒造成的，但该选项没有提供正确的背景信息，因此是错误的。选项 2 假设 mysqld 服务被禁用导致启动失败，但也没有提供足够的信息支持此结论，因此错误。选项 3 认为 systemd 启动 mysqld 时找到了另一个正在运行的进程并关闭了它，虽然这种情况有可能发生，但题目并没有提到另一个进程的冲突，因此该选项也错误。选项 4 表示 MySQL 无法启动是因为进程 2732 出现问题，这可能与 MySQL 启动失败的实际原因有关，因此是正确答案。选项 5 认为 MySQL 尽管有进程存在，仍继续启动，显然与实际情况不符，因此是错误的。 ## 试题 149 Choose the best answer. You wish to protect your MySQL database against SQL injection attacks. Which method would fail to do this? 1. using stored procedures for any database access \[错误] 2. avoiding concatenation of SQL statements and user-supplied values in an application \[错误] 3. using PREPARED STATEMENTS \[错误] 4. installing and configuring the Connection Control plugin \[正确] > 解析：此题考察的是如何防止 SQL 注入攻击。选项 1 提到使用存储过程，这是一种防止 SQL 注入的有效方法，因为它避免了直接拼接 SQL 语句。选项 2 提到避免在应用程序中拼接 SQL 语句与用户提供的值，这也是防止 SQL 注入的好方法。选项 3 提到使用预处理语句（Prepared Statements），这是防止 SQL 注入的常用手段。选项 4 提到安装和配置连接控制插件，这与 SQL 注入防护无关，主要用于防止暴力破解等攻击，因此是错误答案。 ## 试题 150 Choose the best answer. How can mysql\_multi be configured to allow MySQL instances to use the same port number? 1. The instances use different socket names. \[错误] 2. The instances use different user accounts unique to each instance. \[错误] 3. The instances listen on different IP addresses. \[正确] 4. The instances have appropriate net masks set. \[错误] > 解析：此题考察的是 MySQL 多实例配置。选项 1 提到通过不同的 socket 名称来配置实例，这并不能解决端口冲突问题，因此错误。选项 2 提到通过不同的用户账户来区分实例，这与端口配置无关，因此也是错误的。选项 3 提到通过不同的 IP 地址来配置 MySQL 实例，这是一种有效的解决方法，可以使多个实例使用相同的端口号，但监听不同的 IP 地址，从而避免冲突，因此是正确答案。选项 4 提到通过设置适当的网络掩码来解决端口冲突，但这并不解决多实例配置中的端口问题，因此错误。 ## 试题 151 You plan to upgrade your MySQL 5.7 instance to version 8. You have installed the 8 build of MySQL Shell. Examine this command executed from the operating system shell prompt: mysqlsh --uri root\@localhost:3306 -- util check-for-server-upgrade Which statement is true? 1. It fixes any problems with your 5.7 tables to make them ready to upgrade to 8. \[错误] 2. It documents any problems with your 5.7 tables to make them ready to upgrade to 8. \[正确] 3. It is mandatory to run this command so that MySQL 8.0 software's auto-upgrade process has the details it needs to operate properly. \[错误] 4. It is mandatory to clear the history of prior results before executing this process a second time or later. \[错误] 5. It fails because the operation name must be in camelCase. \[错误] 6. It fails because checkForServerUpgrade must be executed only within an active shell session as a method of the util object. \[错误] > 解析：此题考察 MySQL 8 升级前的检查步骤。选项 1 提到该命令会修复 MySQL 5.7 表中的问题，但实际上该命令并不修复问题，而是检查和记录可能影响升级的问题，因此错误。选项 2 正确地描述了该命令的功能，即记录 MySQL 5.7 表中的问题，以便在升级前进行处理，因此是正确答案。选项 3 认为该命令是 MySQL 8 自动升级过程所必需的，但实际上不是强制要求，因此错误。选项 4 认为需要在第二次执行该命令之前清除先前结果的历史，但这是不必要的，因此错误。选项 5 和 6 都涉及该命令失败的可能原因，但在题目中并未明确提到这些原因，因此都是错误的。 ## 试题 152 Choose the best answer. Your MySQL instance is capturing a huge amount of financial transactions every day in the finance database. Company policy is to create a backup every day. The main tables being updated are prefixed with transactions-. These tables are archived into tables that are prefixed with archives- each month. mysqlbackup --optimistic-busy-tables=^finance\\. transactions-.\*\ backup Which optimization process best describes what happens with the redo logs? 1. The transaction tables are backed up first, then the archive tables and redo logs. \[错误] 2. The redo logs are backed up only if there are changes showing for the transactions tables. \[错误] 3. The redo logs are not backed up at all. \[错误] 4. The redo logs are backed up first, then the transaction and archive tables. \[错误] 5. The archive tables are backed up first, then the transaction tables and redo logs. \[正确] > 解析：该题考察了 MySQL 备份优化的过程。选项 1 和 2 错误，因为事务表和归档表的备份顺序不符合实际优化策略。选项 3 认为重做日志不被备份，这也与实际情况不符。选项 4 提到重做日志先备份，但实际上它们是在归档表和事务表之后备份的。选项 5 正确，因为 MySQL 备份优化首先备份归档表，然后是事务表，最后备份重做日志，这样可以避免在备份过程中锁定正在使用的表。 ## 试题 153 Choose the best answer. You must replay the binary logs on your MySQL server. Which command do you use? 1. mysqlbinlog binlog.000003 binlog.000004 binlog.000005 mysql -h 127.0.0.1 \[正确] 2. mysql -h 127.0.0.1 --local-infile binlog.000003 binlog.000004 binlog.000005 \[错误] 3. mysqlpump -h 127.0.0.1 binlog.000003 binlog.000004 binlog.000005 \[错误] 4. mysqlbinlog -h 127.0.0.1 binlog.000003 binlog.000004 binlog.000005 \[错误] > 解析：要回放二进制日志，应使用 `mysqlbinlog` 命令。正确的命令应包含 `mysqlbinlog`，并正确指定要回放的二进制日志文件名以及目标 MySQL 实例的主机地址。 ## 试题 154 Choose the best answer. Examine this statement, which executes successfully:（见下图）  You want to improve the performance of this query\: ``` SELECT Name FROM world.city WHERE Population BETWEEN 1000000 AND 2000000; ``` Which change enables the query to succeed while accessing fewer rows? 1. ALTER TABLE world.city ADD INDEX (Name) ; \[错误] 2. ALTER TABLE world.city ADD SPATIAL INDEX (Name) ; \[错误] 3. ALTER TABLE world.city ADD FULLTEXT INDEX (Name) ; \[错误] 4. ALTER TABLE world.city ADD INDEX (Population) ; \[正确] 5. ALTER TABLE world.city ADD SPATIAL INDEX (Population) ; \[错误] 6. ALTER TABLE world.city ADD FULLTEXT INDEX (Population) ; \[错误] > 解析：为了提高查询性能，应针对 `Population` 列创建索引。因为查询条件是通过 `Population` 的范围进行筛选，因此针对该列添加索引会大大提高查询效率。`Name` 列的索引对该查询的性能提升作用较小。 ## 试题 155 Choose the best answer. Examine this parameter setting: `audit_log=FORCE_LOG_PERMANENT` What effect does this have on auditing? 1. It causes the audit log to be created if it does not exist. \[错误] 2. It prevents the audit plugin from being removed from the running server. \[正确] 3. It will force the load of the audit plugin even in case of errors at server start. \[错误] 4. It prevents the audit log from being removed or rotated. \[错误] > 解析：`FORCE_LOG_PERMANENT` 选项会使得审计插件在服务器运行时无法被卸载，从而确保审计日志始终有效。其他选项并不是该设置的作用。 ## 试题 156 Choose the best answer  1. 21 \[错误] 2. 20 \[正确] 3. 24 \[错误] 4. 6 \[错误] 5. 22 \[错误] 6. 25 \[错误] > 解析：这是一个题目中未展示的计算题。通过检查相关的数据或者图表，我们可以确定正确答案是 `20`。 ## 试题 157 Choose the best answer An attempt to recover an InnoDB Cluster fails. Examine this set of messages and responses: host3:3377 ssl JS > dba.rebootClusterFromCompleteOutage () Reconfiguring the default cluster from complete outage. . . The instance ' host1:3377' was part of the cluster configuration. Would you like to rejoin it to the cluster? y/N \:y The instance ' host2:3377' was part of the cluster configuration.Would you like to rejoin it to the cluster? y/N \:y Dba.rebootClusterFromCompleteOutage\:The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. Please use the most up to date instance:' host1:3377'. (RuntimeError) Which statement is true? 1. The instance deployed on host3 must be rebuilt with a backup from the primary instance. \[错误] 2. The cluster is running and there is at least one ONLINE instance. \[错误] 3. The active session instance is invalid and must be re-created by using the command shell.connect ('host3: 3377') . \[错误] 4. It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID) sets with GTID\_SUBSET (set1, set2) . \[正确] 5. The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the command cluster.addInstance('host1:3377') . \[错误] > 解析：集群启动失败的原因是 GTID 不一致，可以通过比较不同的 GTID 集来确定最新的实例。在这种情况下，应该连接到提示的主机并重新启动集群。 ## 试题 158 Examine the command, which execute successfully: `shell> mysqld --initialize-insecure` Which statement is true? 1. The installation creates a temporary test environment with data in the /tmp directory. \[错误] 2. The installation creates a temporary test environment with data in the /tmp directory. \[错误] 3. The root password is created in the error log in plain text. \[错误] 4. The installation is created without enforcing or generating SSL certificates. \[错误] 5. The root password is not created allowing easy access from the same host. \[正确] > 解析：`--initialize-insecure` 选项会初始化数据库，但不会设置 root 密码，因此允许本地访问且无需密码。 ## 试题 159 Choose the best answer. You are upgrading a MySQL instance to the latest 8.0 version. Examine this output:（见下图）  You plan to add this parameter to the configuration: `innodb_directories= '/innodb_extras'` Which statement is true? 1. It adds more temporary workspace in addition to the innodb\_tmpdir location. \[错误] 2. It moves all innodb tablespaces to the /innodb\_extras directory to enable a new innodb\_data\_home\_dir to be defined. \[错误] 3. It is not necessary because innodb\_data\_home\_dir is already defined. \[错误] 4. It allows scanning of other locations to discover more innodb tablespaces. \[正确] 5. It defines all innodb tablespace options relative to a starting parent directory. \[错误] > 解析：`innodb_directories` 参数允许扫描其他位置以发现更多的 InnoDB 表空间。这有助于 MySQL 在多个位置查找和管理表空间。 ## 试题 160 Choose the best answer. You plan to take daily full backups, which include the ndbinfo and sys (internal) databases. Which command will back up the databases in parallel? 1. mysqlpump --include-databases=% > full-backup-\$(date + %Y%m%d) .sql \[正确] 2. mysqlpump --all-databases > full-backup-\$(date + %Y%m%d) .sql \[错误] 3. mysqldump --single-transaction > full-backup-\$(date + %Y%m%d) .sql \[错误] 4. mysqldump --all-databases > full-backup-\$(date + %Y%m%d) .sql \[错误] > mysqlpump allows you to perform backups in parallel and can include multiple databases, including system databases like ndbinfo and sys, when using the `--include-databases` option. The correct option makes use of the `--include-databases=%` argument, which includes all databases, even those that might be excluded by the `--all-databases` option. ## 试题 161 The languages table uses MyISAM and the countries table uses the InnoDB storage engine. Both tables are empty. Examine these statements: BEGIN; INSERT INTO languages(lang) VALUES (“Italian”); INSERT INTO countries(country) VALUES(“Italy”); ROLLBACK; What is the content of both tables after executing these statements? 1. countries has one row, languages has none. \[错误] 2. Both tables have one row. \[错误] 3. languages has one row, countries has none. \[正确] 4. Both tables are empty. \[错误] > Since the `ROLLBACK` statement undoes all changes made during the transaction, and the `languages` table uses MyISAM while the `countries` table uses InnoDB, the changes to the `languages` table are not rolled back due to MyISAM's lack of transactional support. Therefore, only the changes to the `countries` table (InnoDB) are rolled back. ## 试题 162 What does the slave I/O thread do? 1. monitors and schedules I/O calls to the subsystem for the relay logs \[错误] 2. acquires a lock on the binary log for reading each event to be sent to the slave \[错误] 3. connects to the master and requests it to send updates recorded in its binary logs \[正确] 4. reads the relay log and executes the events contained in them \[错误] > The slave I/O thread connects to the master server to retrieve binary log events and writes them to the relay log, where they are later processed by the SQL thread. The slave I/O thread does not execute the events but instead handles the fetching and storing of the binary logs. ## 试题 163 Choose the best answer. Examine these commands and results: SHOW GRANTS FOR jane; GRANT USAGE ON *.* TO ‘’  Jane must create a temporary table named TOTALSALES in the SALES database. Which statement will provide Jane with the required privileges based on the principle of least privilege? 1. GRANT ALL ON sales.\* TO jane; \[错误] 2. GRANT CREATE TEMPORARY TABLES, INSERT, UPDATE, DELETE, SELECT ON sales.totalsales TO jane; \[错误] 3. GRANT CREATE TEMPORARY TABLES ON sales.\* TO jane; \[正确] 4. GRANT CREATE TEMPORARY TABLES ON sales.totalsales TO jane; \[错误] > The correct option is to grant the privilege to create temporary tables on the `sales.*` database, which includes all tables within the database, not just the specific `totalsales` table. This ensures that Jane can create the temporary table while adhering to the principle of least privilege. ## 试题 164 Choose the best answer. Where is the default data directory located after installing MySQL using RPM on Oracle Linux 7? 1. /usr/bin \[错误] 2. /usr/mysql \[错误] 3. /var/lib/mysql \[正确] 4. /etc/my.cnf \[错误] > The default data directory for MySQL installations using RPM on Oracle Linux 7 is `/var/lib/mysql`, where MySQL stores its data files. The other directories listed are either for binaries or configuration files, not data storage. ## 试题 165 Binary log events for the 'mydb1' schema must be copied to a different schema name 'mydb2'. Which command will do this? 1. mysqlbinlog --rewrite-db='mydb1->mydb2' | mysql \[正确] 2. mysqlbinlog --rewrite-db='mydb1' --rewrite-db='mydb2' | mysql \[错误] 3. mysqlbinlog --read-from=remote-server --raw sed's/mydb1/mydb2/g' | mysql \[错误] 4. mysqlbinlog --database=mydb1 --database=mydb2 | mysql \[错误] > The correct command is `mysqlbinlog --rewrite-db='mydb1->mydb2'`, which allows for rewriting the schema name from `mydb1` to `mydb2` while processing the binary log events. This is useful when you want to replicate events from one schema to another. ## 试题 166 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? 1. mysqlbinlog --stop-position=500453 binlog.000036 mysql \[错误] 2. mysqlbinlog --stop-datetime='2019-11-20 14:55:16' binlog.000036 mysql \[错误] 3. mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 mysql \[错误] 4. mysqlbinlog --stop-position=500324 binlog.000036 mysql \[正确] > The correct command is `mysqlbinlog --stop-position=500324`, which stops the binary log processing before the drop table statement was executed, effectively rolling back that action. ## 试题 167 Choose the best answer. You recently upgraded your MySQL installation to MySQL 8.0. Examine this client error: ERROR 2059 (HY000): Authentication plugin 'caching\_sha2\_password' cannot be loaded:/usr/local/mysql/lib/plugin/caching\_sha2\_password.so: cannot open shared object file: No such file or directory Which option will allow this client to connect to MySQL Server? 1. mysqld default\_authentication\_plugin=sha256\_password \[错误] 2. ALTER USER user IDENTIFIED WITH caching\_sha2\_password BY 'password'; \[错误] 3. ALTER USER user IDENTIFIED WITH mysql\_native\_password BY 'password'; \[正确] 4. mysqld default\_authentication\_plugin=mysql\_native\_password \[错误] > The error indicates that the authentication plugin `caching_sha2_password` is missing. The correct solution is to use the `ALTER USER` command to change the authentication method to `mysql_native_password`, which is more widely supported by clients. ## 试题 168 Choose the best answer. Examine this SQL statement: UPDATE world.city SET Population = Population \* 1.1 WHERE CountryCode IN (SELECT Code FROM world.country WHERE Continent = 'Asia') Which set of privileges will allow Tom to execute this SQL statement? 1. GRANT UPDATE ON `world`.\* TO `tom`@`%`; GRANT ALL PRIVILEGES ON `world`.`country` TO `tom`@`%`; \[错误] 2. GRANT UPDATE ON `world`.`city` TO `tom`@`%`; GRANT SELECT ON `world`.\* TO `tom`@`%` \[错误] 3. GRANT UPDATE ON `world`.`city` TO `tom`@`%`; GRANT SELECT ON `world`.`country` TO `tom`@`%` \[正确] 4. GRANT ALL PRIVILEGES ON `world`.`city` TO `tom`@`%`; GRANT SELECT (`code`) ON `world`.`country` TO `tom`@`%` \[错误] > Tom requires `UPDATE` privileges on the `city` table and `SELECT` privileges on the `country` table to execute this query. The correct option grants the necessary privileges for the query to run without providing excessive permissions. ## 试题 169 Choose the best answer. Which command enables rule-based MySQL Auditing capabilities? 1. mysql> INSTALL COMPONENT audit\_log; \[错误] 2. shell> mysqld --initialize --log-raw=audit.log \[错误] 3. mysql> INSTALL PLUGIN audit\_log; \[错误] 4. shell> mysql < audit\_log\_filter\_linux\_install.sql. \[正确] > To enable rule-based MySQL Auditing, the correct command is to run `mysql < audit_log_filter_linux_install.sql`, which sets up the necessary audit logging configuration. ## 试题 170 Choose the best answer. You are having performance issues with MySQL instances. Those servers are monitored with MySQL Enterprise Monitor. Using Query Analyzer, where do you begin to look for problem queries? 1. Sort the \Exec\ column and check for SQL queries with low Query Response Time index (QRTi) values. \[正确] 2. Sort the \Exec\ column and check for SQL queries with high Query Response Time index (QRTi) values. \[错误] 3. Look for queries with low total latency times in the Latency section in the times series graph. \[错误] 4. Look for queries with big prolonged spikes in row activity/access graph in the times series graph. \[错误] > 解析：在使用MySQL Enterprise Monitor中的Query Analyzer时，首先应当通过排序\Exec\列并检查SQL查询的Query Response Time Index (QRTi)值，寻找执行响应时间较短的查询，这些查询通常能提供更多的性能问题线索。低QRTi值可能表示查询在执行时的响应时间较长，因此它们需要关注。其他选项虽然也涉及查询性能，但更侧重于其他参数的监控，未能针对QRTi做出直接有效的诊断。 ## 试题 171 Choose the best answer. Examine this command and output:（见下图）  Which statement is true? 1. Firewall\_cached\_entries is the number of statements found in the query cache for users in DETECTING mode. \[错误] 2. Firewall\_access\_granted is the number of connections granted from whitelisted hosts. \[错误] 3. Firewall\_access\_suspicious is the number of statements logged as suspicious for users in DETECTING mode. \[正确] 4. Firewall\_access\_denied is the number of connection attempts from prohibited hosts that are denied. \[错误] > 解析：该题目要求根据给定的输出信息识别防火墙的统计指标。正确的描述是"Firewall\_access\_suspicious"表示在检测模式下被标记为可疑的语句数量，而"Firewall\_access\_granted"、"Firewall\_access\_denied"和"Firewall\_cached\_entries"等则涉及其他类型的连接和查询监控。理解这些关键字的含义对于正确识别日志中的内容至关重要。 ## 试题 172 Choose the best answer. Examine this output:（见下图） Which change should optimize the number of buffer pool instances for this workload? 1. Decrease the number of buffer pool instances to 4. \[错误] 2. Increase the number of buffer pool instances to 12. \[错误] 3. Increase the number of buffer pool instances to 16. \[正确] 4. Decrease the number of buffer pool instances to 1. \[错误] 5. Increase the number of buffer pool instances to 32. \[错误] > 解析：为了优化缓冲池实例的数量，需要根据工作负载来调整。通过分析当前负载情况，增加缓冲池实例的数量通常能提高数据库的性能。根据输出结果，最佳选择是将缓冲池实例数增加到16，过多或过少的实例数都可能导致性能下降。 ## 试题 173 Choose the best answer. 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? 1. Change the default database and re-execute the failed statement in your transaction. \[错误] 2. Re-execute the failed statement in your transaction. \[正确] 3. Close the connection, reconnect, and start the transaction again. \[错误] 4. Roll back the transaction and start the transaction again in the same session. \[错误] > 解析：当一个长事务中遇到权限不足的错误时，最佳做法是重新执行失败的语句，而无需中断事务的其余部分。重新执行事务中的失败语句可以使事务继续进行而不需要重新启动会话或回滚。其他选项则涉及关闭连接或回滚事务，可能会导致不必要的中断。 ## 试题 174 Choose the best answer. There has been an accidental deletion of data in one of your MySQL databases. You determine that all entries in the binary log file after position 1797 must be replayed. Examine this partial command: mysqlbinlog binlog.000008 --start-position=1798 Which operation will complete the command? 1. It can be piped into the MySQL Server via the command-line client. \[正确] 2. \--write-to-remote-server must be added to the command line to update the database tables. \[错误] 3. You must use --stop-position=1797 to avoid the DELETE statement that caused the initial problem. \[错误] 4. No changes required. It automatically updates the MySQL Server with the data. \[错误] > 解析：此题要求恢复删除的数据并且使用二进制日志进行回放。通过管道将mysqlbinlog命令的输出传输到MySQL服务器的命令行客户端中，能够将日志内容回放到数据库中。而其他选项提出的修改命令行的方式并不能解决问题，特别是"write-to-remote-server"选项并不适合本场景。 ## 试题 175 Choose the best answer. The mysqld instance has the connection control plugin enabled with these settings: connection\_control\_min\_connection\_delay=1000 connection\_control\_max\_connection\_delay=2000 The minimum and maximum delays need to be increased to 3000 and 5000, respectively. A command is executed: mysql> SET GLOBAL connection\_control\_min\_connection\_delay=3000; What is the result? 1. The minimum value increases to 3000 and the maximum value increases to 4000. \[错误] 2. Only the minimum connection value is increased to 3000. \[错误] 3. The minimum connection value is changed to 2000. \[错误] 4. An error is returned. \[正确] > 解析：对于MySQL的连接控制插件，其配置要求最小延迟不能超过最大延迟。因此，试图将"connection\_control\_min\_connection\_delay"设置为3000会触发错误，因为此值已经大于"connection\_control\_max\_connection\_delay"的当前值。正确做法是调整最大延迟值，使其大于最小延迟值。 ## 试题 176 How can mysqld\_multi be configured to allow MySQL instances to use the same port number? 1. The instances use different user accounts unique to each instance. \[错误] 2. The instances use different socket names. \[错误] 3. The instances have appropriate net masks set. \[错误] 4. The instances listen on different IP addresses. \[正确] > 解析：当使用mysqld\_multi配置多个MySQL实例时，若要允许它们使用相同的端口号，可以通过让它们监听不同的IP地址来实现。这种配置可以确保每个实例能正常独立运行而不会发生端口冲突。 ## 试题 177 Choose the best answer. MySQL is installed on a Linux server with this configuration: \[mysqld] user=mysql datadir=/data/mysql Which method sets the default authentication to SHA-256 hashing for authenticating user account passwords? 1. Add default\_authentication\_plugin=sha256\_password in the configuration file. \[正确] 2. Set validate-user-plugins=caching\_sha2\_password in the configuration file. \[错误] 3. Add default\_authentication\_plugin=mysql\_native\_password in the configuration file. \[错误] 4. Define CREATE USER 'user'@'%' IDENTIFIED WITH sha256\_password in the MySQL instance. \[错误] > 解析：为了将MySQL的默认身份验证方式设置为SHA-256，最直接的方法是通过修改配置文件中的"default\_authentication\_plugin"项，将其设置为"sha256\_password"。其他选项涉及不必要的插件配置或用户定义操作。 ## 试题 178 Examine these two reports taken 100 seconds apart: GLOBAL STATUS 1: Com\_create\_table=500005 Com\_drop\_table=500003 Com\_flush=23 Create\_tmp\_disk\_tables=400000 Create\_tmp\_tables=1200000 Max\_used\_connections=92 Open\_files=5000 Opened\_files=5000 Open\_table\_definitions=3000 Open\_tables=1024 Opened\_table\_definitions=2369 Opened\_tables=3500000 Threads\_connected=62 Threads\_running=58 Uptime=100000 GLOBAL STATUS 2: Com\_create\_table=500505 Com\_drop\_table=500498 Com\_flush=31 Create\_tmp\_disk\_tables=4004000 Create\_tmp\_tables=1201200 Max\_used\_connections=92 Open\_files=5000 Opened\_files=7505 Open\_table\_definitions=3000 Open\_tables=1024 Opened\_table\_definitions=2873 Opened\_tables=3503500 Threads\_connected=67 Threads\_running=64 Uptime=100000 Your MySQL system normally supports 50-75 concurrent connections. Which configuration change will improve performance? 1. increase table\_open\_cache \[正确] 2. decrease open\_files\_limit \[错误] 3. increase max\_connections \[错误] 4. decrease table\_definition\_cache \[错误] > 解析：从报告中的数据可以看出，"Open\_tables"和"Opened\_tables"的数量都非常高，说明缓存表的能力需要进一步提高。通过增加"table\_open\_cache"的值，可以有效地减少打开表的次数，从而提升性能。其他选项则没有直接改善缓存性能的作用。 ## 试题 179 Choose the best answer. A colleague complains about slow response time on your website. Examine this query and output: (见下图)  What is the most likely cause for the high number of lock waits? 1. Your table accesses wait for the operating system level flush. \[错误] 2. You use the MyISAM storage engine for most common tables. \[正确] 3. You use the InnoDB storage engine and statements wait while data is inserted. \[错误] 4. The Innodb Buffer pool is full. \[错误] > 解析：查询中的锁等待问题最常见的原因之一是使用MyISAM存储引擎，因为它在并发查询时容易出现锁竞争。其他选项涉及的InnoDB引擎或缓冲池问题可能导致性能下降，但在此情况下，与MyISAM存储引擎的锁等待更为相关。 ## 试题 180 Choose the best answer.（题干见图片） What is the reason for SSL not being used   1. It is connected via a UNIX socket. \[正确] 2. The root user must use ssl\_fips\_mode = ON. \[错误] 3. A current database is not selected. There is nothing to encrypt. \[错误] 4. The root user cannot use encryption. \[错误] > 解析：在UNIX套接字连接时，SSL不被使用，因为SSL加密只适用于TCP/IP连接。其他选项提到的SSL设置或数据库选择与SSL加密的使用无关，因此是错误的。 ## 试题 181 Choose the best answer. You have configured a working MySQL InnoDB Cluster in single-primary mode. What happens when the primary instance goes down due to a network problem? 1. All remaining members in the cluster are automatically set to read-write mode. \[错误] 2. The cluster goes into wait mode until a new member is manually promoted as primary. \[错误] 3. The cluster detects network partitioning and shuts down to remain consistent. \[错误] 4. A new primary is automatically elected. \[正确] > 解析：在InnoDB Cluster的单主模式下，当主实例因为网络问题而停止时，系统会自动选举一个新的主实例，而不需要手动操作。这是InnoDB Cluster的内建故障转移机制。 ## 试题 182 Choose the best answer。 You have configured MySQL Enterprise Transparent Data Encryption (TDE). What command would you use to encrypt a table? 1. ALTER TABLE <table> ENCRYPTION='Y'; \[正确] 2. UPDATE <table> SET ENCRYPTION= 'Y'; \[错误] 3. UPDATE information\_schema.tables SET encryption='Y' WHERE table\_name='table'; \[错误] 4. ALTER INSTANCE ROTATE INNODB MASTER KEY; \[错误] > 解析：使用 `ALTER TABLE <table> ENCRYPTION='Y'` 命令可以对表进行加密。这是MySQL TDE加密表的标准方法，其他选项所提到的操作与加密无关。 ## 试题 183 Choose the best answer. A developer accidentally dropped the InnoDB table Customers from the Company database. There is a datadir copy from two days ago in the dbbackup directory. Which set of steps would restore only the missing table? 1. Stop the MySQL Server process and restart it with the command: mysqld --basedir=/usr/local/mysql --datadir=/dbbackup Run mysqldump on this table and restore the dump file. \[错误] 2. Stop the MySQL Server process, and execute: mysqlbackup --datadir=/var/lib/mysql --backup-dir=/dbbackup --include-tables='Company\\. Customers' copy-back Start the mysqld process. \[正确] 3. Stop the MySQL Server process and restart it with the command: mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql Run mysqldump on this table and restore the dump file. \[错误] 4. Stop the MySQL Server process, copy the Customers.ibd file from the dbbackup directory, and start the mysqld process. \[错误] > 解析：通过执行 `mysqlbackup` 工具并指定需要恢复的表 `Customers`，可以在不中断其他数据的情况下恢复丢失的表。其他选项要么不适合表恢复，要么会导致整个数据库恢复，而不仅仅是单个表。 ## 试题 184 Choose the best answer. Examine this partial report: mysql> SHOW FULL PROCESSLIST;（下图）  Examine this query: SELECT SUM(m.CURRENT\_NUMBER\_OF\_BYTES\_USED) AS TOTAL FROM performance\_schema.memory\_summary\_by\_thread\_by\_event\_name m INNER JOIN performance\_schema.threads t ON m.THREAD\_ID = t.THREAD\_ID WHERE t.PROCESSLIST\_ID = 10; What information does this query provide? 1. total memory used by thread number 10 \[错误] 2. total memory used by the first 10 threads \[错误] 3. total memory used across all connections associated with the user on connection number 10 \[错误] 4. total memory used by connection number 10 \[正确] 5. total memory used by the first 10 connections \[错误] 6. total memory used across all connections associated with the user on thread number 10 \[错误] > 解析：查询中的 `PROCESSLIST_ID = 10` 表示的是连接编号，因此查询结果提供的是第10个连接的总内存使用情况，而不是线程或用户相关的内存使用情况。 ## 试题 185 You plan to take daily full backups, which include the ndbinfo and sys (internal) databases.Which command will back up the databases in parallel? 1. mysqlpump --include-databases=% > full-backup-\$(date +%Y%m%d).sql \[正确] 2. mysqldump --single-transaction > full-backup-\$(date +%Y%m%d).sql \[错误] 3. mysqldump --all-databases > full-backup-\$(date +%Y%m%d).sql \[错误] 4. mysqlpump --all-databases > full-backup-\$(date +%Y%m%d).sql \[错误] > 解析：`mysqlpump` 支持并行备份数据库，因此可以通过 `--include-databases=%` 选项并行备份所有数据库。`mysqldump` 不支持并行备份，因此不能用于这个目的。 ## 试题 186 Examine this command, which executes successfully: mysqlbackup --user=dba --password --port=3306 --with-timestamp --backup-dir=/export/backups backup-and-apply-log Which statement is true? 1. The database server is put into a read-only state for the duration of the backup. \[错误] 2. An offline backup of InnoDB tables is taken. \[错误] 3. The backup can be impacted when DDL operations run during the backup. \[正确] 4. The backup accesses the MySQL server files by using a pre-existing connection. \[错误] > 解析：在使用 `mysqlbackup` 工具进行备份时，备份过程中如果有DDL操作，可能会影响备份的一致性。其他选项描述的情况不符合该工具的备份特性。 ## 试题 187 Choose the best answer. Database test contains a table named city that has the InnoDB storage engine.（见下图）  What is the content of the test folder in the data directory? 1. city.ibd \[正确] 2. city.ibd, city.frm, and city.sdi \[错误] 3. city.ibd and city.frm \[错误] 4. city.ibd and city.sdi \[错误] 5. city.MYD, city.MYI, and city.sdi \[错误] > 解析：InnoDB表存储为 `.ibd` 文件，而 `.frm` 文件则用于存储表的结构定义，`MYD` 和 `MYI` 文件是MyISAM存储引擎使用的文件。由于题目中的表是InnoDB存储引擎，因此只有 `.ibd` 文件存在。 ## 试题 188 "bootstrap" 是一个动词，表示启动、引导或初始化一个系统或应用程序 Choose the best answer. Examine this command, which executes successfully: \$ mysqlrouter --bootstrap user\@hostname\:port --directory=directory\_path Which activity is performed? 1. MySQL Router is restarted. \[错误] 2. MySQL Router is configured based on the information in files in directory\_path. \[错误] 3. MySQL Router configures all the cluster nodes based on the information retrieved from the InnoDB cluster metadata server. \[错误] 4. MySQL Router configures itself based on the information retrieved from the InnoDB cluster metadata server. \[正确] > 解析：`mysqlrouter --bootstrap` 命令用于配置MySQL Router，获取InnoDB集群的元数据，进行自我配置。其他选项没有涉及到集群元数据的配置过程。 ## 试题 189 Choose the best answer. You issue this command: SHOW SLAVE STATUS In the output, there is a value for seconds\_behind\_master. How is this time calculated? 1. It is the time between the I/O thread receiving details of the master's last transaction and the time it was applied by the SQL thread. \[错误] 2. It is the time between the most recent transaction written to the relay logs and the time it was committed on the master. \[错误] 3. It is the time between the I/O thread receiving details of the master's last transaction and the time it was written to the relay log on the slave. \[错误] 4. It is the time between the most recent transaction applied by a SQL thread and the time it was committed on the master. \[正确] > 解析：`seconds_behind_master` 表示从MySQL复制的SQL线程处理最近事务的时间与主服务器提交该事务的时间之间的延迟。这是基于SQL线程应用事务的时间差。 ## 试题 190 Which command enables rule-based MySQL Auditing capabilities? 1. mysql>INSTALL PLUGIN audit\_ log; \[错误] 2. mysql>INSTALL COMPONENT audit\_ log; \[错误] 3. shell> mysqld --initialize --log- raw=audit. log \[错误] 4. shell> mysql < audit\_ log\_ filter\_ linux\_ install.sql \[正确] > 解析: 启用基于规则的 MySQL 审计功能需要执行特定的安装脚本。`audit_log_filter_linux_install.sql` 脚本会将所需的审计功能启用，而不是通过安装插件或组件来实现。`mysqld` 初始化命令和 `INSTALL PLUGIN` 命令并不适用于启用审计日志。 ## 试题 191 Consider this shell output and executed commands: root\@oel7 \~# ps aux I grep mysqld mysql 2076 3.5 24.6 1386852 372572 ? Ssl 12:01 0:01 /usr/sbin/mysqld root\@oel7 \~ # kill-15 2076 Which statement is true about MySQL server shutdown? 1. kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk. \[错误] 2. ki11 -15 should be avoided. Use other methods such as mysqladmin shutdown or systemct1 stop mysqld. \[错误] 3. kill - 15 carries out a normal shutdown process, such as mysqladmin shutdown. \[正确] 4. mysqld\_ safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command. \[错误] > 解析: `kill -15` 是发送正常的关闭信号给 MySQL 进程，类似于使用 `mysqladmin shutdown` 来执行正常的关闭过程。`kill -9` 则是强制终止进程，可能导致未写入磁盘的事务丢失，因此不建议使用。`mysqld_safe` 不会禁止使用 `kill` 命令。 ## 试题 192 Choose the best answer. Binary log events for the 'mydb1' schema must be copied to a different schema name 'mydb2'. Which command will do this? 1. mysqlbinlog --datebase=mydb1 --database=mnydb2 mysql \[错误] 2. mysqlbinlog --rewrite-db='mydb1' --rewrite-db='mydb2' mysql \[错误] 3. mysqlbinlog --rewrite-db= 'mydb1->mydb2' mysql \[正确] 4. mysqlbinlog --read- from-remote-server --raw sed 's/mydb1/mydb2/g' mysql \[错误] > 解析: 使用 `mysqlbinlog` 命令的 `--rewrite-db` 选项可以将二进制日志中的某个数据库重写为另一个数据库。正确的语法是 `--rewrite-db='mydb1->mydb2'`，它将 `mydb1` 的所有引用转换为 `mydb2`。 ## 试题 193 Choose the best answer. Examine these entries from the general query log: (见下图)  All UPDATE statements reference existing rows. Which describes the outcome of the sequence of statements? 1. A deadlock occurs immediately. \[正确] 2. All statements execute without error. \[错误] 3. A deadlock occurs after innodb\_lock\_wait\_timeout seconds. \[错误] 4. Connection 25 experiences a lock wait timeout. \[错误] 5. Connection 24 experiences a lock wait timeout. \[错误] > 解析: 在死锁检测启用的情况下，InnoDB 会自动检测并回滚其中一个事务。由于查询涉及更新相同的行，且没有获得锁，死锁会立即发生。没有设置死锁检测时，死锁会依赖 `innodb_lock_wait_timeout` 来回滚事务。 ## 试题 194 Choose the best answer. You reconfigure and start a slave that was not replicating for several days. The configuration file and CHANGE MASTER command are correct. Examine the GTID information from both master and slave: (见下图)  Which statement is true? 1. Replication will fail because the master does not have the required transaction with bbbbbbb- bbbb-bbbb-bbbb-bbbbbbbbbb GTIDs in its binary logs. \[正确] 2. Replication will fail because of inconsistent numbers in ccccccc-cccc-cccc-ccccccccccccc GTIDs. \[错误] 3. Replication will fail because the master has already purged transactions with ccccc-cccc-cccc-ccc- cccccccccc GTIDs. \[错误] 4. Replication will work. \[错误] 5. Replication will fail because the slave has purged more aaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa transactions than the master. \[错误] > 解析: 由于 GTID 是全局唯一标识，主库上的某些事务已被删除，导致从库无法找到对应的事务，导致复制失败。因此，主库没有 `bbbbb...` 相关事务，复制会失败。 ## 试题 195 Choose the best answer. Examine this command, which executes successfully: mysqlbackup --defaults-file=/backups/server-my.cnf --backup-dir=/backups/full copy-back. Which statement is true about the copy-back process? 1. It restores files from the data directory to their original MySQL server locations. \[错误] 2. The copy-back process is used to overwrite a new backup over an existing backup. \[错误] 3. It restores files from the backup directory to their original MySQL server locations. \[正确] 4. The copy-back process makes inconsistent backups. \[错误] > 解析: `mysqlbackup` 的 `copy-back` 操作会将备份的数据恢复到 MySQL 服务器的原始位置，从而完成数据恢复工作，而不会覆盖其他备份文件。此过程确保从备份目录恢复数据。 ## 试题 196 Choose the best answer. 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. Which contains all the settings that you need to add to or modify the slow log configuration? 1. long\_query\_time=5 log\_throttle\_queries\_not\_using\_indexes=5 min\_examined\_row\_limit=5000 \[错误] 2. min\_examined\_row\_limit=5000 \[错误] 3. log\_throttle\_queries\_not\_using\_indexes=5 \[错误] 4. log\_throttle\_queries\_not\_using\_indexes=5 min\_examined\_row\_limit=5000 \[错误] 5. long\_query\_time=5 \[错误] 6. long\_query\_time=5. log\_throttle\_queries\_not\_using\_indexes=5 \[错误] 7. long\_query\_time=5 min\_examined\_row\_limit=5000 \[正确] > 解析: `long_query_time` 控制记录慢查询日志的时间阈值，`min_examined_row_limit` 控制记录扫描行数大于指定值的查询，只有同时满足时间和行数的查询会被记录。因此，正确配置为 `long_query_time=5` 和 `min_examined_row_limit=5000`。 ## 试题 197 Choose the best answer. Consider this shell output and executed commands: root\@oel7\~ # ps aux grep mysqld mysql 2076 3.5 24.6 1386852 372572 2 Ssl 12:01 0:01 /usr/sbin/mysqld root\@oel7 \~# kill -15 2076 Which statement is true about MySQL server shutdown? 1. mysqld safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command. \[错误] 2. kill -15 should be avoided. Use other methods such as mysqladmin shutdown or systemctl stop mysqld. \[错误] 3. kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk. \[错误] 4. kill -15 carries out a normal shutdown process, such as mysqladmin shutdown. \[正确] > 解析: `kill -15` 命令会通知 MySQL 进程正常关闭，相当于使用 `mysqladmin shutdown`。这是 MySQL 的标准关闭信号，与 `kill -9` 不同，`kill -9` 是强制关闭，可能导致未写入磁盘的事务丢失。 ## 试题 198 Choose the best answer. You have a MySQL system with 500 GB of data that needs frequent backups. You use a mix of MyISAM and InnoDB storage engines for your data. Examine your backup requirements: * The MySQL system being backed up can never be unavailable or locked to the client applications. * The recovery from the backup must work on any system. * Only 1 hour of data can be lost on recovery of the backup. Which option fulfills all backup requirements? 1. Use the Clone Plugin to copy the data to another MySQL system. \[错误] 2. Take your backup from a slave of the MySQL system. \[正确] 3. Take a physical backup of the MySQL system. \[错误] 4. Take a logical backup of the MySQL system. \[错误] > 解析: 从主库备份数据会导致系统不可用，因此最合适的方式是从一个从库进行备份，这样不会影响应用程序的正常运行。主从复制模式下，备份从库数据是最佳选择。 ## 试题 199 Choose the best answer. Which statement is true about InnoDB persistent index statistics? 1. Execution plans based on transient index statistics improve precision when innodb\_stats\_persistent\_sample\_pages is increased. \[错误] 2. Setting innodb\_stats\_auto\_recalc=ON causes statistics to be updated automatically when a new index is created. \[正确] 3. Updating index statistics is an I/O expensive operation. \[错误] 4. Increasing innodb\_stats\_persistent\_sample\_pages determines higher pages scanning speed, at the cost of increased memory usage. \[错误] 5. Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine. \[错误] > 解析: 启用 `innodb_stats_auto_recalc=ON` 后，每当创建新的索引时，InnoDB 会自动更新索引统计信息。`innodb_stats_persistent_sample_pages` 选项影响持久化样本页面的数量，但并不会直接改善查询精度。 ## 试题 200 Choose the best answer. Which step or set of steps can be used to rotate the error log? 1. Execute SET GLOBAL expire\_logs\_days=0 to enforce a log rotation. \[错误] 2. Rename the error log file on disk, and then execute FLUSH ERROR LOGS. \[正确] 3. Execute SET GLOBAL log\_error = ' <new error log file> '. \[错误] 4. Execute SET GLOBAL max\_error\_count = <number of messages at point to rotate>. \[错误] > 解析：MySQL的错误日志文件可以通过重命名当前日志文件，并使用`FLUSH ERROR LOGS`命令进行日志轮换。这将确保当前日志文件关闭并开始记录新的日志。其他选项中，`expire_logs_days`用于控制日志文件的过期时间，`log_error`用于设置日志文件的位置，而`max_error_count`与错误日志轮换无关。 ## 试题 201 Choose the best answer. You are using an existing server with a new configuration. MySQL Server fails to start. Examine this snapshot of the error log: 190925 12:49:05 InnoDB\:Initializing buffer pool, size = 3.0G 190925 12:49:05 InnoDB\:Completed initialization of buffer pool InnoDB\:Error\:log file ./ib\_logfile0 is of different size 0 5242880 bytes InnoDB\:than specified in the .cnf file 0 26214400 bytes! 190925 12:49:05 ERROR Plugin 'InnoDB' init function returned error . 190925 12:49:05 ERROR Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 190925 12:49:05 ERROR Aborting 190925 12:49:05 Note /usr/sbin/mysqld\:Shutdown complete Which action would allow the server to start? 1. First run mysqld --initialize to refresh the size of ib\_logfile. \[错误] 2. Create a new ib\_logfile0 file of size 26214400. \[错误] 3. Execute mysqladmin flush-logs. \[错误] 4. Remove ib\_logfile0 and ib\_logfile1 files from the file system. \[正确] > 解析：当InnoDB日志文件的大小与配置文件中指定的大小不匹配时，MySQL无法启动。在这种情况下，删除`ib_logfile0`和`ib_logfile1`文件并重新启动MySQL可以解决问题，因为MySQL会根据新的配置自动创建新的日志文件。其他选项无法解决日志文件大小不匹配的问题。 ## 试题 202 Choose the best answer. Which characters are most commonly used in a SQL injection attack? 1. ^ and \$ \[错误] 2. null (\0) and newline () \[错误] 3. < and > \[错误] 4. ' and" \[正确] E) + and - \[错误] > 解析：SQL注入攻击常常利用特殊字符来破坏SQL查询的结构。单引号（`'`）和双引号（`"`）是最常见的攻击字符，它们用于结束字符串并注入恶意SQL代码。其他字符如`^`和`$`在SQL注入中不常用，而`null`字符和换行符也不是常见的注入字符。 ## 试题 203 Examine these commands, which execute successfully on the ic1 host: mysqlsh> dba.createCluster( 'cluster1' , memberWeight:35) mysqlsh> var mycluster = dba.getCluster () mysqlsh> mycluster.addInstance ( 'ic\@ic2' , memberWeight:25) mysqlsh> mycluster.addInstance( 'ic\@ic3' , memberWeight:50) Now examine this configuration setting, which is the same on all nodes\:group\_replication\_ consistency=BEFORE\_ON\_PRIMARY\_FAILOVER Which statement is true if primary node ic1 fails? 1. Node ic2 becomes the new primary and is ignored until any backlog of transactions is completed. \[错误] 2. Node ic3 becomes the new primary and existing transactions are considered stale and rolled back. \[错误] 3. Node ic3 becomes the new primary and is ignored until any backlog of transactions is completed. \[正确] 4. Node ic2 becomes the new primary and existing transactions are considered stale and rolled back. \[错误] 5. Only two nodes remain so the election process is uncertain and must be done manually. \[错误] > 解析：在设置为`BEFORE_ON_PRIMARY_FAILOVER`的情况下，当主节点(ic1)故障时，系统会选择一个新的主节点(ic3)，并且不会立即处理未完成的事务，直到事务日志被处理完。这确保了事务的一致性。其他选项描述的情况不符合此配置。 ## 试题 204 Choose the best answer. You execute this command\:shell> mysqlpump --exclude-databases=% --users Which statement is true? 1. It creates a logical backup of all metadata, but contains no table data. \[错误] 2. It creates a logical backup of all MySQL user accounts. \[正确] 3. It creates a logical backup of only the users database. \[错误] 4. It returns an error because the mysqldump command should have been used. \[错误] > 解析：`mysqlpump`命令用于创建逻辑备份，`--exclude-databases=%`表示排除所有数据库，而`--users`选项用于备份所有MySQL用户账户。因此，它只会备份用户账户信息，而不涉及表数据。其他选项描述的功能不准确。 ## 试题 205 Choose the best answer. You want to log only the changes made to the database objects and data on the MySQL system. Which log will do this by default? 1. error log \[错误] 2. general query log \[错误] 3. audit log \[错误] 4. slow query log \[错误] 5. binary log \[正确] > 解析：二进制日志（binary log）记录了所有更改数据库数据和结构的操作。其他日志（如错误日志、查询日志等）记录的是错误信息或查询，而不专门用于记录数据的更改。 ## 试题 206 Choose the best answer. Examine this partial output for InnoDB Cluster status: [//]: 注释内容 [//]:  ```json "topology": { "host1:3377": { "address": "host1:3377", "mode": "R/W", // ...... "STATUS": "ONLINE", "version": "8.0.18" }, "host1:3377": { "address": "host2:3377", "mode": "R/O", // ...... "STATUS": "MISSING" }, "host1:3377": { "address": "host3:3377", "mode": "R/O", // ...... "STATUS": "ONLINE", "version": "8.0.18" } } ``` Which statement explains the state of the instance deployed on host2? 1. It can be recovered from a donor instance on host3 by cloning using the command cluster.rejoinInstance ('<user>@host3:3377') . \[正确] 2. It has been expelled from the cluster because of a transaction error. \[错误] 3. It has been removed from the cluster by using the command STOP GROUP\_REPLICATION;. . \[错误] 4. It can rejoin the cluster by using the command dba. rebootClusterFromCompleteOutage() . \[错误] 5. It can rejoin the cluster by using the command cluster.addInstance ('<user>@host3:3377') . \[错误] > 解析：如果一个节点由于崩溃或其他原因脱离了群集，但仍持有元数据，它可以使用`rejoinInstance()`命令重新加入群集，而不是使用`addInstance()`，后者是用于被主动移除的节点。其他选项描述的是不正确的恢复或重新加入方式。 ## 试题 207 Choose the best answer. You use Row Based Replication and need to see \pseudo-SQL\ statements for the replication event that is located in the log\_file position NNNNN file. Which command should you use? 1. mysqlbinlog --verbose --stop-position=NNNNN log\_file \[错误] 2. mysqlshow --verbose --stop-position=NNNNN log\_file \[错误] 3. mysqlbinlog --debug --start-position=NNNNN log\_file \[错误] 4. mysqlbinlog --verbose --start-position=NNNN log\_file \[正确] > 解析：使用`mysqlbinlog`命令与`--verbose`选项，可以显示详细的复制事件。`--start-position`参数指定从哪个位置开始读取日志，而`--stop-position`用于指定读取结束的位置。选项4正确地使用了`--start-position`来查看指定位置的事件。 ## 试题 208 Choose the best answer. Which utility would you use to view the queries in the slow query log sorted by average query time? 1. mysqlshow \[错误] 2. mysqlimport \[错误] 3. mysqlcheck \[错误] 4. mysqldumpslow \[正确] > 解析：`mysqldumpslow`是用于分析并汇总慢查询日志的工具，它能够根据查询的执行时间对查询进行排序。其他工具如`mysqlshow`、`mysqlimport`和`mysqlcheck`并不用于处理慢查询日志。 ## 试题 209 Choose the best answer.Examine the command, which executes successfully: shell> mysqld --initialize Which statement is true? 1. The installation creates a temporary test environment with data in the /tmp directory. \[错误] 2. The root password is created in the error log in plain text. \[正确] 3. The installation is created without enforcing or generating SSL certificates. \[错误] > 解析：`mysqld --initialize`命令会初始化MySQL数据目录，并创建临时的root密码，该密码将以明文形式写入错误日志。其他选项描述的内容不准确。 ## 试题 210 Choose the best answer. Your MySQL environment has asynchronous position based-replication with one master and one slave. The slave instance had a disk I/O problem, so it was stopped. You determined that the slave relay log files were corrupted and unusable, but no other files are damaged. You restart MySQL Server. How can replication be restored? 1. The slave relay logs should be deleted; then execute START SLAVE; \[错误] 2. The slave needs to be restored from backup. \[错误] 3. The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication relay log file name, then issue START SLAVE; \[正确] 4. The relay logs from the master should be used to replace the corrupted relay logs. \[错误] > 解析：在这种情况下，应该删除损坏的中继日志文件，并使用`CHANGE MASTER`命令调整中继日志文件名，然后通过`START SLAVE`来恢复复制。直接恢复备份并不可行，因为其他文件没有损坏。使用来自主服务器的中继日志文件也无法解决问题，因为主服务器的日志可能不匹配。 ## 试题 211 Examine this configuration: You have a corporate private network, which uses its own Certificate Authority (CA) using an industry standard 2048-bit RSA key length. All MySQL Server and client certificates are signed using the central corporate CA. All clients are known, controlled, and exist only on the private LAN. The private network uses its own private authoritative DNS. The private network also uses other nominal enterprise services. An end-to-end encrypted connection for a MySQL client to MySQL server has been established on this LAN. How does the MySQL Servers' self-signed certificate compare to one that would be signed by a known public, third party trusted Certificate Authority? 1. The self-signed certificate is equally secure and equally trusted. \[错误] 2. The self-signed certificate is equally secure and less trusted. \[错误] 3. The self-signed certificate is more secure and equally trusted. \[错误] 4. The self-signed certificate is less secure and equally trusted. \[错误] 5. The self-signed certificate is more secure and less trusted. \[错误] 6. The self-signed certificate is less secure and less trusted \[正确] > 解析：自签名证书在加密强度上与公共受信任证书没有差异，但由于没有第三方认证，它在信任上较低。企业内部的私有网络虽然可以信任，但对于外部世界而言，它的信任度不如由公认的证书颁发机构签发的证书。 ## 试题 212 Choose the best answer. You wish to store the username and password for a client connection to MySQL server in a file on a local file system. Which is the best way to encrypt the file? 1. Use mysql\_config\_editor to create an encrypted file. \[正确] 2. Use a text editor to create a new defaults file and encrypt it from Linux prompt. \[错误] 3. Use the AES\_ENCRYPT() MySQL function on the option file. \[错误] 4. Use mysql\_secure\_installation to encrypt stored login credentials. \[错误] > 解析：使用`mysql_config_editor`可以安全地将MySQL客户端的登录信息加密并存储在文件中。这是官方推荐的加密方法。其他方法如文本编辑器手动加密或使用MySQL内建加密函数都不是为存储凭据设计的安全方案。 ## 试题 213 Choose the best answer. Examine these statements and output: mysql> GRANT PROXY ON accounting\@localhost TO ' '@'%'; mysql> SELECT USER(), CURRENT\_USER(), @@proxy\_user;  Which statement is true? 1. The user is authorized as the rsmith\@localhost user. \[错误] 2. The user is authorized as the accounting\@localhost user. \[正确] 3. The user is authenticated as the anonymous proxy user ' '@'%'. \[错误] 4. The user failed to define a username and the connecting username defaulted to ' '@'%'. \[错误] 5. The user is logged in with --user=accounting as an option. \[错误] > 解析：在这种情况下，`GRANT PROXY`语句授予了代理权限，允许用户通过代理用户`accounting@localhost`进行授权。因此，查询`CURRENT_USER()`显示的是代理用户，而不是其他用户。`USER()`和`CURRENT_USER()`返回的是当前用户的信息，而`@@proxy_user`显示的是代理用户的信息。 ## 试题 214 You recently upgraded your MySQL installation to MySQL 8.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 file or directory Which option will allow this client to connect to MySQL Server? 1. mysqld default\_authentication\_plugin=caching\_sha2\_password \[错误] 2. ALTER USER user IDENTIFIED WITH mysql\_native\_password BY ‘password’; \[正确] 3. mysqld default\_authentication\_plugin=sha256\_password \[错误] 4. mysqld default\_authentication\_plugin=mysql\_native\_password \[错误] 5. ALTER USER user IDENTIFIED WITH caching\_sha2\_password \[错误] 6. ALTER USER user IDENTIFIED WITH sha256\_password \[错误] > 解析：错误提示表明客户端无法加载`caching_sha2_password`插件，这通常是由于客户端与服务器使用不同的认证插件所致。通过将用户的认证插件更改为`mysql_native_password`，可以解决客户端连接问题。 ## 试题 215 Choose the best answer. Which condition is true about the use of the hash join algorithm? 1. The smallest of the tables in the join must fit in memory as set by join\_buffer\_size. \[错误] 2. No index can be used for the join. \[正确] 3. At least one of the tables in the join must have a hash index. \[错误] 4. The query must access no more than two tables. \[错误] > 解析：哈希连接算法要求没有任何索引可以用于连接操作，它通过构建一个哈希表来处理连接，因此不能使用索引。其他条件与哈希连接的实现无关。 ## 试题 216 Which three are characteristics of logical backups?  1. Logical backups back up table data. 2. Logical backups back up table structure. 3. Logical backups back up stored procedures, functions, and triggers. 4. Logical backups are in a platform-independent format. 5. Logical backups can be restored selectively, such as restoring only certain tables. > 解析：逻辑备份备份的是数据库的逻辑对象，如表、数据、存储过程等，而非底层数据文件。它们通常采用平台无关的格式，适合进行跨平台恢复。同时，也允许对某些特定对象进行恢复，例如仅恢复某些表。 ## 试题 217 Which two statements are true about MySQL Enterprise Backup? 1. It supports the creation of incremental backups. \[正确] 2. It creates logical backups. \[错误] 3. It supports restoring to a remote MySQL system. \[正确] 4. It supports backing up only table structures. \[错误] 5. It supports backup of a remote MySQL system. \[错误] 6. It can perform hot or warm backups. \[正确] > 解析：MySQL Enterprise Backup支持增量备份和远程恢复，但不支持创建逻辑备份或仅备份表结构。它还支持热备份和暖备份，适用于高可用性环境。 ## 试题 218 Which statement enables all roles granted to all users automatically? 1. SET ROLE ALL; 2. SET DEFAULT ROLE ALL TO '\*'@'%'; 3. SET PERSIST activate\_all\_roles\_on\_login=ON; \[正确] 4. SET PERSIST mandatory\_roles=ALL; > 解析：`SET PERSIST activate_all_roles_on_login=ON;`命令会确保所有角色在用户登录时自动启用。这是实现自动启用所有角色的正确方法。 ## 试题 219 You planned an upgrade of your MySQL Server from version 5.7 to version 8. You created a full backup and successfully tested the upgrade process on a test server. You then upgraded the production environment successfully. Soon after the upgrade, the application team reported a problem and asked you to roll back the upgrade. Which statement is true? 1. You must downgrade the data dictionary using the mysqlfrm utility. 2. You can easily switch between using MySQL 5.7 and MySQL 8 binaries after upgrading, because both sets of metadata are maintained. 3. You must set--skip-networking and run mysqld --dd-downgrade to prepare for rollback. 4. You must restore to your backup created in MySQL 5.7. \[正确] > 解析：MySQL 8.0和5.7之间存在不兼容的变更，因此恢复到先前的版本需要使用备份进行回滚，而不是通过降级数据字典来实现。 ## 试题 220 You have a MySQL instance with GTIDs enabled. This instance runs more than 100 transactions per second. You discover that some data was deleted at a particular point in time. You decide to perform a recovery from the binary logs as they are all available. Which two commands can restore the database to the point right before data was deleted? 1. mysqlbinlog --skip-gtids \[正确] 2. mysqlbinlog --stop-position.... 3. START SLAVE SQL\_THREAD UNTIL SQL\_BEFORE\_GTIDS= \[正确] 4. mysqlbinlog --stop-datetime..... 5. START SLAVE IO\_THREAD UNTIL SQL\_BEFORE\_GTIDS=... ... > 解析：`mysqlbinlog --skip-gtids`命令可以跳过GTID检查，适用于恢复过程。`START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS=`则可确保从指定的GTID点恢复，适用于GTID启用的环境。 ## 试题 221 You need to find the number of examined rows for queries that have completed. All relevant configurations are enabled for recording the information. Which three sources contain the number of examined rows? 1. the Performance Schema \[正确] 2. the Information Schema 3. the error log 4. the general query log 5. the sys schema \[正确] 6. the slow query log \[正确] > 解析：在MySQL中，`Performance Schema`和`sys schema`可以提供执行查询时扫描的行数，而`slow query log`记录慢查询的相关数据，也包含扫描行数。 ## 试题 222 Which statement is true about cold backups? 1. Cold backups are taken when the MySQL Server is stopped. \[正确] 2. Cold backups are always binary backups. \[错误] 3. Cold backups are hot backups that are taken without making any data changes during the backup process. \[错误] 4. Cold backups are always logical backups. \[错误] > 解析：冷备份是指在MySQL服务器停止时进行备份，通常是文件级的物理备份。 ## 试题 223 Examine this statement, which executes successfully:  Why does the file contain no visible statement events? 1. You must wait for the audit loa buffer to fill before it will flush to disk. \[错误] 2. You must read the audit log statements through the Performance Schema. \[错误] 3. You must add audit\_log = ON to the MySQL confiquration file and restart MySQL to log statements. \[错误] 4. You must use the audit\_log\_filter\_set\_filter() and audit\_log\_filter\_set\_user() functions to specify what to log. \[正确] 5. You must use the audit\_log\_read() and audit\_log\_read\_bookmark() functions to read the statement events. \[错误] > 解析：在MySQL中，审计日志的记录可以通过特定的过滤设置进行控制。使用`audit_log_filter_set_filter()`和`audit_log_filter_set_user()`函数来指定哪些事件应该被记录。如果这些过滤器未被正确设置，那么审计日志中可能不会有任何事件被记录。因此，选项4是正确的。其他选项要么是功能无关，要么是缺少适当的配置，导致无法正确记录事件。
Nathan
2025年5月14日 16:20
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
PDF文件
Docx文件
分享
链接
类型
密码
更新密码