2026年数据库管理维护实操试题及答案

2026年数据库管理维护实操试题及答案1.实操试题：现有生产环境MySQL8.0数据库实例，数据存储目录为/data/mysql，实例端口3306，核心业务库为order_db，其中order_tb为每日交易流水表。北京时间2026年3月15日01:00运维按规范执行了全量备份，备份文件路径为/backup/mysql/full_20260315.sql.gz，开启二进制日志，01:00至当日10:15的二进制日志范围为/binlog/mysql-bin.000287至mysql-bin.000290，当日10:15开发人员误执行DROPDATABASEorder_db语句删除整个业务库，要求完成以下操作：①恢复误操作前所有业务数据，保证数据无丢失，恢复后业务可正常访问；②跳过误操作语句，保留误操作前所有增量数据；③完成恢复后验证数据完整性。参考答案操作步骤：第一步：前期环境准备，首先通知业务侧停止应用服务，避免恢复过程中产生新的写入数据造成二次损坏，执行命令systemctlstoporder-api停止前端业务接入，登录数据库服务器后，先验证备份文件和二进制日志完整性，执行gzip-t/backup/mysql/full_20260315.sql.gz，无输出则说明压缩备份包未损坏，再执行ls/binlog/mysql-bin.00028[7-0]确认所有增量二进制日志文件存在未丢失。第二步：启动单用户维护模式，执行命令mysqld_safe--skip-networking--user=mysql&，该模式仅允许本地root登录操作，屏蔽外部网络连接，避免恢复过程中数据被篡改。第三步：恢复全量备份数据，执行解压恢复命令gzip-cd/backup/mysql/full_20260315.sql.gz|mysql-uroot-p-S/tmp/mysql.sock，输入数据库root密码后等待执行完成，此时全量数据恢复至2026年3月15日01:00备份时间点。第四步：定位误操作的二进制日志位置，执行mysqlbinlog/binlog/mysql-bin.000287/binlog/mysql-bin.000288/binlog/mysql-bin.000289/binlog/mysql-bin.000290|grep-n"DROPDATABASEorder_db"，从输出结果中得到误操作事件的终止position为1879245，对应执行时间为2026-03-1510:15:22，使用position定位比时间定位精度更高，避免漏掉或者多跳过正常操作语句。第五步：执行增量数据恢复，执行恢复命令mysqlbinlog--stop-position=1879245/binlog/mysql-bin.000287/binlog/mysql-bin.000288/binlog/mysql-bin.000289/binlog/mysql-bin.000290|mysql-uroot-p-S/tmp/mysql.sock，如果对时间精度有把握也可以替换参数为--stop-datetime="2026-03-1510:15:22"，执行完成后，01:00到10:15分之前的所有正常数据都已经恢复完成。第六步：验证数据与恢复业务访问，登录MySQL执行useorder_db;showtables;selectcount()fromorder_tb;核对count结果与业务侧提供的预期记录数一致，索引结构完整，执行mysqladmin-uroot-pshutdown关闭单用户实例，执行systemctlstartmysqld正常启动数据库，再执行systemctlstartorder-api启动业务应用，测试业务读写正常，恢复完成。第六步：验证数据与恢复业务访问，登录MySQL执行useorder_db;showtables;selectcount()fromorder_tb;核对count结果与业务侧提供的预期记录数一致，索引结构完整，执行mysqladmin-uroot-pshutdown关闭单用户实例，执行systemctlstartmysqld正常启动数据库，再执行systemctlstartorder-api启动业务应用，测试业务读写正常，恢复完成。2.实操试题：现有生产环境MySQL8.0，InnoDB引擎的order库下user_info表共120万条用户记录，表结构为CREATETABLE`user_info`(`user_id`intNOTNULLAUTO_INCREMENT,`username`varchar(50)NOTNULL,`phone`varchar(11)DEFAULTNULL,`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMP,`email`varchar(100)DEFAULTNULL,PRIMARYKEY(`user_id`),KEY`idx_create`(`create_time`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;业务侧核心查询语句为SELECTuser_id,username,phoneFROMuser_infoWHEREphone=?;最近该查询响应时间从原来的100ms上升到8s，同时由于该表每周有30%的数据新增更新删除操作，表空间碎片率达到40%，要求完成以下操作：①排查慢查询原因，完成查询优化，将查询响应时间降到50ms以内；②在线整理表碎片，整理过程中不影响业务正常读写；③验证优化结果符合要求。参考答案操作步骤：第一步：排查慢查询原因，登录MySQL执行explainextendedSELECTuser_id,username,phoneFROMuser_infoWHEREphone=\G，从执行计划可以看到type列为ALL，possible_keys与key列均为NULL，说明查询条件phone字段没有建立索引，数据库每次执行查询都需要全表扫描，同时表碎片率过高导致磁盘随机IO性能下降，双重原因导致查询变慢。第二步：创建覆盖索引优化查询，由于查询仅需要返回user_id、username、phone三个字段，查询条件为phone，因此创建组合覆盖索引可以避免回表查询，直接从索引树获取所有需要的数据，大幅降低IO消耗。为了避免高峰期DDL锁表，使用MySQL8.0原生在线DDL，指定算法和锁策略，执行命令：ALTERTABLEuser_infoALGORITHM=INSTANTLOCK=NONEADDINDEXidx_phone_cover(phone,user_id,username);该操作仅修改数据字典，不需要重建整张表，秒级完成，全程不会阻塞业务的读写操作。第三步：在线整理表碎片，对于碎片化的InnoDB表，直接使用ALTERTABLE重建会锁表影响业务，因此使用pt-online-schema-change工具完成在线碎片整理，执行命令：pt-online-schema-change--alter="ENGINE=InnoDB"D=order,t=user_info--user=root--password=DBroot1234--execute，该工具会新建一张和原表结构一致的空表，逐行复制原表数据到新表，全程原表保持可读写，复制完成后自动原子替换原表，删除旧表，完成碎片整理，整个过程对业务无感知。如果使用MySQL8.0.30及以上版本，也可以执行ALTERTABLEuser_infoALGORITHM=INPLACELOCK=NONEFORCE完成在线碎片整理，效果一致。第四步：验证优化结果，重新执行EXPLAIN查看执行计划，结果显示type为ref，使用的索引为idx_phone_cover，Extra列显示Usingindex，说明完全走覆盖索引，不需要回表。实际执行查询，响应时间从8s降到8ms，符合要求。执行SELECTTABLE_NAME,DATA_FREE,(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH))ASfragment_ratioFROMinformation_schema.TABLESWHERETABLE_SCHEMA='order'ANDTABLE_NAME='user_info';输出结果显示fragment_ratio小于0.05，说明碎片率从40%降到5%以下，符合整理要求，优化完成。3.实操试题：现有一主一从MySQL8.0主从复制架构，GTID模式开启，主库IP为192.168.1.10，从库IP为192.168.1.11，昨日主库数据盘打满导致事务写入失败，运维清理磁盘后重启了主库，重启后从库IO线程正常运行，SQL线程中断，错误信息显示：Last_Error:CouldnotexecuteWrite_rowseventontabletest.user_log;Duplicateentry'12580'forkey'user_log.PRIMARY',Error_code:1062，经排查主从数据存在120条记录差异，要求完成以下操作：①修复主从复制故障，保证主从数据最终一致；②修复过程不改变原有主从架构，修复后可以正常同步；③验证同步状态符合要求。参考答案操作步骤：第一步：故障原因分析，主库磁盘打满时，部分已经提交到从库的事务没有成功写入主库二进制日志，主库重启后，二进制日志重新生成了这些事务，从库重新接收执行时，发现该主键已经存在，因此触发主键冲突错误，导致SQL线程停止。第二步：跳过冲突事务，登录从库MySQL执行SHOWSLAVESTATUS\G，提取当前冲突事务的GTID，例如得到冲突GTID为3ccc123-1234-11eb-8dcd-000c29800000:12580，依次执行以下命令：STOPSLAVESQL_THREAD;SET@@GLOBAL.GTID_NEXT='3ccc123-1234-11eb-8dcd-000c29800000:12580';BEGIN;COMMIT;SET@@GLOBAL.GTID_NEXT='AUTOMATIC';STARTSLAVESQL_THREAD;如果是传统文件位点复制，执行STOPSLAVE;SETGLOBALSQL_SLAVE_SKIP_COUNTER=1;STARTSLAVE即可。第三步：校验修复主从数据差异，跳过冲突后SQL线程正常运行，接下来使用pt-table-checksum工具校验主从全库数据一致性，执行命令：pt-table-checksumh=192.168.1.10,u=repl,p=repl123--databasestest--no-check-replication-filters，工具会计算每个表的校验和，标记出存在差异的表和数据行，之后使用pt-table-sync工具同步差异数据，执行命令：pt-table-sync--replicatepercona.checksumsh=192.168.1.10,u=repl,p=repl123h=192.168.1.11,u=repl,p=repl123--execute，工具会自动生成修复语句，仅同步差异数据，不会影响主库业务正常运行。第四步：验证修复结果，登录从库执行SHOWSLAVESTATUS\G，确认输出中Slave_IO_Running=Yes，Slave_SQL_Running=Yes，Seconds_Behind_Master为0，Last_Error列为空，说明复制线程正常运行。在主库插入测试数据：INSERTINTOtest.user_log(id,content,create_time)VALUES(12581,'test_sync','2026-03-1514:30:00');等待3秒后在从库查询SELECTFROMtest.user_logWHEREid=12581;可以查到对应数据，说明主从同步正常，数据一致，故障修复完成。第四步：验证修复结果，登录从库执行SHOWSLAVESTATUS\G，确认输出中Slav