如何加速MySQL存储引擎变更、库导出/导入及版本升级流程

针对250GB数据库、12GB内存的Linux环境，以下是无数据丢失前提下的提速方法：

一、优化MyISAM转InnoDB的过程 #

直接执行ALTER TABLE会锁表并全量转换，耗时极长。利用分区特性做分区级并行转换，大幅缩短锁表时间：

1. 创建与原表结构一致的临时InnoDB表：

   CREATE TABLE devstats_temp LIKE devstats;
   ALTER TABLE devstats_temp ENGINE=InnoDB;
   

2. 逐个分区进行数据交换（避免全表锁）：

   -- 以分区p20230522为例，将原分区数据交换到临时表
   ALTER TABLE devstats EXCHANGE PARTITION p20230522 WITH TABLE devstats_temp;
   -- 验证临时表数据无误后，修改原分区的存储引擎
   ALTER TABLE devstats PARTITION p20230522 ENGINE=InnoDB;
   -- 清空临时表，处理下一个分区
   TRUNCATE TABLE devstats_temp;
   

3. 所有分区转换完成后，修改主表的默认引擎：

   ALTER TABLE devstats ENGINE=InnoDB;
   

注：操作尽量选业务低峰期，MyISAM分区在交换过程中会短暂锁分区，不影响其他分区的读操作。

二、替换mysqldump为物理备份工具 #

逻辑备份（mysqldump）对大数据库效率极低，改用Percona XtraBackup（免费开源）做热备份：

• 备份命令示例：

  xtrabackup --user=root --password=xxx --backup --target-dir=/path/to/backup
  

• 该工具支持InnoDB热备份，无需锁表；对MyISAM表仅需短暂锁表（若已完成引擎转换则无锁表），备份速度比mysqldump快5-10倍。

三、采用原地升级替代“备份→导入”流程 #

无需全量导出导入数据，直接原地升级：

1. 完成所有MyISAM表转InnoDB操作，确保5.7版本数据库运行稳定，无报错。
2. 关闭5.7服务：

   systemctl stop mysql
   

3. 备份my.cnf配置文件，替换为MySQL 8.0的二进制包，调整配置适配8.0（如保留innodb_buffer_pool_size=8GB，新增default-authentication-plugin=caching_sha2_password）。
4. 启动8.0服务，执行升级工具：

   mysql_upgrade -u root -p
   

5. 验证系统表升级完成，检查业务数据一致性。
   注：原地升级前务必做全量物理备份，防止升级失败回滚。

四、若必须使用逻辑备份/导入的优化参数 #

如果因场景限制需保留逻辑备份流程，调整以下参数提速：

备份阶段（mysqldump） #

mysqldump -u root -p --single-transaction --quick --compress --skip-lock-tables --databases your_db > backup.sql

• --single-transaction：InnoDB表无需锁表，保证备份一致性
• --quick：逐行读取数据，避免占用过多内存
• --compress：压缩备份文件，减少磁盘IO和存储占用

导入阶段 #

1. 临时调整InnoDB参数（导入完成后恢复）：

   SET GLOBAL innodb_flush_log_at_trx_commit=2;
   SET GLOBAL sync_binlog=0;
   SET GLOBAL innodb_doublewrite=0;
   

2. 并行导入：拆分大表备份为多个文件，用多个mysql进程并行导入；或使用mysqlimport工具批量导入。
3. 关闭二进制日志（导入前执行SET SQL_LOG_BIN=0;），减少写入开销。

五、系统层面性能调优 #

• 磁盘IO：将备份目录、数据库数据目录挂载到SSD磁盘，大幅提升读写速度。
• 内存配置：除innodb_buffer_pool_size=8GB，设置innodb_log_file_size=2GB（最大不超过4GB），减少日志切换频率。
• 系统资源：关闭非必要服务（如防火墙、监控代理），避免CPU、内存抢占。

六、数据一致性保障 #

• 操作前做全量物理备份，作为兜底方案。
• 分区转换后，用CHECKSUM TABLE devstats PARTITION(p20230522);验证数据完整性。
• 升级完成后，执行SELECT COUNT(*) FROM devstats;对比原库数据量；抽查关键业务数据的准确性。

内容的提问来源于stack exchange，提问作者Dams