mysql数据导入导出本质是备份与恢复，小数据可用mysqldump和source，但大数据需进阶技巧；2. 完全备份用mysqldump加--all-databases和--lock-all-tables，适合数据量小或需完整备份的场景；3. 增量备份需开启binlog，通过记录show master status的file和position值并复制binlog文件实现；4. 逻辑备份（如mysqldump）备份sql语句，物理备份（如xtrabackup）复制数据文件，后者速度更快但可移植性差；5. 恢复时小文件用source命令，中等文件用mysql -e命令，大文件可分割后并行导入，提升效率；6. 超大数据量迁移推荐xtrabackup物理备份，先--prepare再--copy-back，或使用mysql enterprise backup；7. 分库分表可并行迁移，也可用canal等工具实现实时同步；8. 避免错误需备份前检查、备份后验证、关闭外键约束（set foreign_key_checks=0）、调整mysql配置参数、监控资源使用并制定回滚方案；9. 数据迁移需耐心细心，技术与流程并重，确保数据完整与系统稳定。
MySQL的数据导入导出，说白了就是备份和恢复。但实际操作起来，会发现远不止简单的
mysqldump和
source命令那么简单。数据量小的时候还好，一旦数据量大了，各种问题就来了，比如连接超时、内存溢出等等。所以，掌握一些进阶的技巧，能让你在数据迁移的时候少走很多弯路。
MySQL数据导入导出，核心在于备份与恢复。下面我们详细拆解，步步为营。
备份方案：mysqldump完全备份与增量备份实战
最常见的备份工具就是
mysqldump。但
mysqldump的用法有很多，直接
mysqldump -u root -p database_name > backup.sql肯定是最简单的，但也是最不靠谱的。
-
完全备份: 适合数据量不大，或者需要完整备份的场景。
mysqldump -u root -p --all-databases --lock-all-tables > full_backup.sql
--all-databases
备份所有数据库。--lock-all-tables
在备份期间锁定所有表，保证数据一致性。但是，这个选项会阻塞写操作，所以要在业务低峰期进行。 -
增量备份: 适合数据量大，需要频繁备份的场景。增量备份需要开启MySQL的binlog功能。
-
开启binlog: 在
my.cnf
文件中配置。[mysqld] log-bin=mysql-bin binlog_format=ROW server-id=1
重启MySQL服务。
执行完全备份: 先进行一次完全备份。
-
执行增量备份: 记录binlog的位置，然后将binlog文件复制出来。
SHOW MASTER STATUS;
记录File和Position的值。 复制binlog文件：
cp mysql-bin.000001 /path/to/backup/
-
逻辑备份 vs 物理备份:
mysqldump
是逻辑备份，备份的是SQL语句。物理备份则是直接复制数据文件，比如使用xtrabackup
。物理备份速度更快，恢复也更快，但是可移植性不如逻辑备份。
恢复方案：source命令、mysql命令与并行导入技巧
备份完成，接下来就是恢复。
-
source
命令: 适合小文件。mysql -u root -p database_name < backup.sql
或者在MySQL客户端中使用
source backup.sql
。但是，如果
backup.sql
文件很大，source
命令可能会很慢，甚至导致连接超时。 -
mysql
命令: 适合中等大小的文件。mysql -u root -p -e "source backup.sql" database_name
-e
选项可以直接执行SQL语句。 -
并行导入: 适合大文件。可以将
backup.sql
分割成多个小文件，然后并行导入。-
分割SQL文件: 使用
split
命令。split -l 10000 backup.sql backup_part_
-l 10000
表示每个文件包含10000行。backup_part_
是文件名前缀。 -
并行导入: 使用
xargs
命令。ls backup_part_* | xargs -n1 -P4 mysql -u root -p -e "source {}" database_name-n1
表示每次传递一个参数。-P4
表示并行执行4个进程。
-
-
使用
mysqlpump
:mysqlpump
是MySQL 5.7.21引入的并行备份工具，也可以用于恢复。mysqlpump -u root -p --databases database_name --result-file=backup.sql --default-parallel=4
--default-parallel=4
表示并行执行4个线程。
如何处理超大数据量MySQL数据库的迁移？
超大数据量意味着什么？意味着简单的
mysqldump和
source已经无法满足需求。我们需要考虑更高效的方案。
-
物理备份与恢复: 使用
xtrabackup
进行物理备份，速度比mysqldump
快很多。xtrabackup --backup --target-dir=/path/to/backup/ --user=root --password=your_password
恢复时，先prepare，再copy back。
xtrabackup --prepare --target-dir=/path/to/backup/ xtrabackup --copy-back --target-dir=/path/to/backup/
然后修改MySQL的配置文件，指向新的数据目录，重启MySQL服务。
使用MySQL Enterprise Backup: 这是MySQL官方提供的商业备份工具，功能更强大，性能更好。
分库分表迁移: 如果数据库已经分库分表，可以并行迁移每个分库分表。
使用数据同步工具: 比如
canal
，可以将数据实时同步到新的数据库。
如何避免数据迁移过程中的常见错误？
数据迁移是一个高风险的操作，稍有不慎就可能导致数据丢失或损坏。
备份前检查: 确保备份的数据是完整的、正确的。
备份后验证: 恢复到测试环境，验证数据是否正确。
-
关闭外键约束: 在导入数据之前，关闭外键约束，可以提高导入速度。导入完成后，再开启外键约束。
SET FOREIGN_KEY_CHECKS = 0; -- 导入数据 SET FOREIGN_KEY_CHECKS = 1;
调整MySQL配置: 根据数据量的大小，调整MySQL的配置参数，比如
innodb_buffer_pool_size
、max_allowed_packet
等。监控迁移过程: 监控CPU、内存、磁盘IO等指标，及时发现问题。
做好回滚方案: 如果迁移失败，要有回滚方案，保证数据能够恢复到原来的状态。
数据迁移不仅仅是技术活，也是体力活。需要耐心、细心、责任心。希望这些技巧能帮助你顺利完成数据迁移。
