MySQL OCP试题解析（7）

Q1： 备份参数解析

.Examine this command, which executes successfully: 
$mysqlbackup --user=dba --password --port=3306 --with-timestamp --only-know-file-types --backup-dir=/exprot/backups backup 
Which statement is true? 
A)Only tables stored in their own tablespaces are backed up. 
B)Only non-encrypted files are backed up. 
C)The backup includes only data files and their metadata. 
D)Only InnoDB data and log files are backed up. 
E)Only files for MySQL or its built-in storage engines are backed up.  

1. 选项解析

A）仅备份独立表空间的表

错误。--only-known-file-types 不限制表空间的存储方式，而是限制文件类型。

B）仅备份非加密文件

错误。是否加密由备份工具和配置决定，与 --only-known-file-types 无关。

C）仅包含数据文件及其元数据

错误。物理备份会包含数据文件、日志文件（如 redo log、binlog）及元数据文件（如 .frm），选项描述不完整。

D）仅备份 InnoDB 数据和日志文件

错误。MEB 默认备份所有 MySQL 引擎文件，包括 InnoDB（热备份）和非 InnoDB（如 MyISAM，温备份）。

E）仅备份 MySQL 或其内置引擎的文件

正确。--only-known-file-types 会过滤掉非 MySQL 引擎或未知文件，符合描述。

2. 相关知识总结

2.1. MySQL Enterprise Backup (MEB) 核心特性

• 物理备份：直接复制数据文件、日志文件及元数据，速度快，适合大型数据库。
• 热备份与温备份
• InnoDB：支持在线热备份（不阻塞 DML 操作）。
• 非 InnoDB（如 MyISAM）：执行温备份（备份期间表不可修改）。
• 增量与差异备份：通过 --incremental 选项减少备份数据量。

高级功能：压缩、加密、云存储支持、Page Tracking（优化增量备份效率）等。

2.2. 重要参数解析

• --with-timestamp：自动在备份目录中创建时间戳子目录，避免覆盖历史备份。
• --backup-dir：指定备份文件的存储路径。
• --only-known-file-types：仅备份 MySQL 引擎相关的文件。

2.3. 备份策略建议

• 全量备份：定期全量备份（如每天或每周一次），使用 backup 命令。
• 增量备份：结合 --incremental 和 --incremental-base，减少备份时间。
• 验证与恢复测试：定期测试备份文件可用性。

2.4. 与 Xtrabackup 的对比

• MEB 优势：支持 MySQL 8.0 新特性（如备份锁、Redo Log Archiving），企业级功能更完善。
• Xtrabackup：开源替代品，但部分功能（如 Page Tracking）尚未支持。

Q2： 半同步复制的原理和故障恢复机制

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? 
A)Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log. 
B)A small amount of committed transactions may be lost in case they were committed just before the disk failure. 
C)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. 
D)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. 
E)Reads from the slave can return outdated data until the value of the rpl_semi_sync_master_timeout variable is 
reached. 

1. 选项分析

1.1 正确选项

• 选项A（正确）

原理：半同步复制仅保证从库的IO线程接收到主库的binlog并写入中继日志（relay log），但SQL线程异步执行这些日志。因此，在主库故障时，从库可能尚未执行完所有中继日志中的事务，导致查询返回过时数据。

验证：若主库崩溃时从库的中继日志中存在未执行的事务，应用程序切换到从库后，需等待SQL线程完成所有事务应用才能读到最新数据。

• 选项B（正确）

原理：在默认的AFTER_SYNC模式下，主库在提交事务前需等待从库确认接收binlog。然而，若主库在提交事务后、但未将数据持久化到磁盘时发生故障（例如磁盘损坏），此时主库数据不可恢复，但从库可能尚未应用该事务（尽管已接收binlog）。此时，已提交的事务可能因主库数据丢失而无法恢复，导致数据丢失。

验证：AFTER_SYNC模式虽降低了数据丢失风险，但主库的提交操作仍需将事务持久化到磁盘。若主库磁盘故障导致提交后的数据未持久化，即使从库已接收binlog，主库恢复时可能无法重建该事务，导致数据不一致。

1.2 错误选项分析

C) 错误。从库需执行完中继日志后才能提供完整数据，故障发生时可能仍有未执行的事务，无法立即返回最新数据。

D) 错误。MySQL半同步复制不包含自动故障转移机制，需手动或通过工具（如MHA）提升从库为主库。

E) 错误。rpl_semi_sync_master_timeout控制主库等待从库ACK的超时时间，但主库磁盘故障与此参数无关，超时机制在此场景下不适用

2. 相关内容小结

2.1 半同步复制模式的核心机制

• AFTER_SYNC（默认模式）

主库先发送binlog到从库并等待ACK，再提交事务到存储引擎。

优势：减少数据丢失风险（从库已接收binlog），但仍存在主库提交后磁盘故障导致数据无法恢复的可能。

• AFTER_COMMIT（旧版本模式）

主库先提交事务，再等待从库ACK。若主库提交后崩溃，可能导致数据丢失（从库可能未收到binlog）。

2.2 建议

• 监控中继日志应用延迟：通过SHOW SLAVE STATUS检查Seconds_Behind_Master，确保从库SQL线程及时执行事务。
• 配置数据持久化：主库设置sync_binlog=1和innodb_flush_log_at_trx_commit=1，确保事务提交后binlog和存储引擎日志均刷盘。

Q3： 异步复制中二进制日志相关问题

Which two are true about binary logs used in asynchronous replication? 
A)They are pulled from the master to the slave. 
B)They are pushed from the master to the slave. 
C)They contain events that describe all queries run on the master. 
D)They contain events that describe only administrative commands run on the master. 
E)They contain events that describe database changes on the master.  

1. 选项解析

• 选项A（正确）

在异步复制中，从库主动通过I/O线程向主库请求（Pull）二进制日志（Binlog），主库不会主动推送（Push）日志到从库。这一过程的关键点包括：

• 主库的Binlog Dump线程负责响应从库的请求，发送Binlog内容。
• 从库的I/O线程持续监控主库的Binlog变化，并基于记录的master.info文件中的位置信息，定期向主库发起请求。
• 选项B（错误）

主库不会主动推送（Push）Binlog到从库，这一行为仅在半同步复制或全同步复制中可能发生（需等待从库确认），但异步复制的本质是主库不主动干预日志传输。

• 选项C（错误）

Binlog不会记录所有查询操作（如SELECT、SHOW等只读操作），仅记录修改数据的操作（如INSERT、UPDATE、DELETE）和DDL语句（如CREATE TABLE、ALTER）。

• 选项D（错误）

Binlog不仅包含管理命令（如CREATE USER、GRANT），还包含所有数据修改事件（DML）和表结构变更事件（DDL）。因此，“仅包含管理命令”的描述明显错误。

• 选项E（正确）

Binlog的核心功能是记录主库上的所有数据库变更，包括：

• 数据修改事件（DML）：如INSERT、UPDATE、DELETE。
• 结构变更事件（DDL）：如CREATE TABLE、ALTER等。
• 事务信息：如事务ID、提交时间等。

2. 异步复制的Binlog机制特点

• 拉取模式（Pull）：从库主动请求主库的Binlog。
• 变更记录：仅记录数据库变更（DML/DDL），不记录只读操作。
• 不保证实时性：主库提交事务后立即返回，不等待从库同步，存在数据丢失风险

Q4： MySQL死锁和锁等待超时

Examine these entries from the general query log: 

1.选项解析

1.1 事务启动与加锁顺序

• 连接24：

START TRANSACTION → UPDATE t1 SET val=1 WHERE ID=130（持有 t1.ID=130 的行锁）。

• 连接25：

START TRANSACTION → UPDATE t2 SET val=5 WHERE ID=3805（持有 t2.ID=3805 的行锁）。

1.2 循环等待形成死锁

• 连接25 尝试执行 UPDATE t1 SET val=10 WHERE ID=130，需等待 连接24 释放 t1.ID=130 的锁。
• 连接24 随后执行 UPDATE t2 SET val=42 WHERE ID=3805，需等待 连接25 释放 t2.ID=3805 的锁。

此时形成循环等待：

• 连接24 持有 t1 的锁，等待 t2 的锁；
• 连接25 持有 t2 的锁，等待 t1 的锁。
• InnoDB 默认开启死锁检测（innodb_deadlock_detect=ON），会立即识别到死锁并回滚其中一个事务。

1.3 选项分析

• A（超时后死锁）：错误。死锁检测是主动的，无需等待 innodb_lock_wait_timeout（默认50秒），死锁会立即触发。
• B/C（连接24或25超时）：错误。超时仅在长时间未获取锁时发生，而此处死锁检测会优先介入。
• D（无错误）：错误。至少一个事务会被回滚，导致错误。
• E（立即死锁）：正确。循环等待触发死锁检测，InnoDB 选择回滚代价较小的事务（如修改行数较少的事务）以解除死锁。

2. 死锁检测机制

• InnoDB 使用 深度优先算法 检测锁等待图中的环。若检测到环，立即回滚一个事务，并记录死锁日志（可通过 SHOW ENGINE INNODB STATUS 查看）。
• 日志中的关键信息：若开启 innodb_print_all_deadlocks=ON，错误日志会记录死锁详情，包括事务ID、等待的锁及涉及的SQL语句

Q5： InnoDB表空间

Which three are types of InnoDB tablespaces? 
A)schema tablespaces 
B)temporary table tablespaces 
C)encryption tables 
D)data tablespaces 
E)redo tablespaces 
F)undo tablespaces

1. 选项解析

1.1 正确选项解析

• 选项B（正确）

临时表空间（Temporary Tablespaces） 用于存储用户创建的临时表和优化器内部生成的临时表。默认情况下，全局临时表空间（如 ibtmp1）存储临时表的回滚段，并在服务器重启时自动重建。临时表空间支持动态扩展，可通过参数 innodb_temp_data_file_path 配置其大小和扩展规则。

• 选项F（正确）

撤销表空间（Undo Tablespaces） 专门存储事务回滚所需的 Undo 日志。从 MySQL 5.7 开始，Undo 日志从系统表空间中分离，支持通过 SQL 动态管理（如 CREATE UNDO TABLESPACE），并可配置自动截断（innodb_undo_log_truncate）以回收空间。每个 MySQL 实例最多支持 127 个 Undo 表空间。

• 选项D（正确）

数据表空间（Data Tablespaces） 是一个广义分类，包含以下具体类型：

系统表空间（System Tablespace）：存储数据字典、双写缓冲区（Doublewrite Buffer）、Change Buffer 和 Undo 日志（若未分离）。默认文件为 ibdata1，可通过 innodb_data_file_path 配置。

独立表空间（File-Per-Table Tablespaces）：每个表单独存储为 .ibd 文件，支持动态行格式和压缩，空间可回收（如 ALTER TABLE ... ENGINE=InnoDB）。

通用表空间（General Tablespaces）：通过 CREATE TABLESPACE 创建，可跨数据库存储多个表，支持所有行格式。

1.2 错误选项解析

• A) Schema tablespaces

InnoDB 中无此类型。表空间与“模式（Schema）”无直接关联，而是按功能和存储对象分类。

• C) Encryption tables

加密是表空间的属性（如 ENCRYPTION=Y），而非独立类型。加密功能可应用于任何表空间类型。

• E) Redo tablespaces

重做日志（Redo Log）是独立结构，用于崩溃恢复，其文件为 ib_logfile0 和 ib_logfile1，不属于表空间。

2. 小结

2.1 InnoDB 表空间核心分类

• 系统表空间：核心元数据和共享存储区。
• 独立表空间：单表存储，支持高效空间管理。
• 通用表空间：多表共享，支持自定义路径。
• 撤销表空间：事务回滚和 MVCC 支持。
• 临时表空间：临时表及内部操作存储。

2.2 建议

• 独立表空间：推荐启用 innodb_file_per_table=ON，便于空间回收和迁移。
• Undo 表空间：配置自动截断（innodb_undo_log_truncate=ON）和独立文件，避免 ibdata1 膨胀。
• 临时表空间：监控 ibtmp1 大小，防止因大事务导致文件膨胀，定期重启或调整 innodb_temp_data_file_path。