MySQL数据迁移那些事儿

前言：

在平时工作中，经常会遇到数据迁移的需求，比如要迁移某个表、某个库或某个实例。根据不同的需求可能要采取不同的迁移方案，数据迁移过程中也可能会遇到各种大小问题。本篇文章，我们一起来看下 MySQL 数据迁移那些事儿，希望能帮助到各位。

1.关于数据迁移

首先引用下维基百科中对数据迁移的解释：

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。
数据迁移是任何系统实现、升级或集成的关键考虑因素，通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。
数据迁移有多种原因，包括服务器或存储设备更换、维护或升级、应用程序迁移、网站集成、灾难恢复和数据中心迁移。

对于 MySQL 数据库，也会经常碰到数据迁移的需求，比如说从测试库迁到开发库，或者从一台机器迁移到另一台机器，可能只是迁移一张表，也可能需要迁移整个数据库实例。对于不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为物理迁移和逻辑迁移两类。

2.迁移方案及注意点

物理迁移适用于大数据量下的整体迁移。物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

相比来说，逻辑迁移适用范围更广，无论是部分迁移还是全量迁移，都可以使用逻辑迁移。逻辑迁移中使用多的就是通过 mysqldump 等备份工具导出再导入了。

mysqldump 同样适用于不同版本不同配置之间的迁移，不过全量迁移时，笔者不建议使用 -A 参数备份全部数据库，特别是对于不同版本之间迁移，可能某些系统库稍有不同，迁移后容易出现未知问题。若使用 mysqldump 进行全量迁移，可以按照下面步骤操作：

# 原库执行 得到的创建数据库语句在新库执行 (排除系统库)
SELECT
  CONCAT( 'CREATE DATABASE IF NOT EXISTS ', '`', SCHEMA_NAME, '`', ' DEFAULT CHARACTER SET ', DEFAULT_CHARACTER_SET_NAME, ';' ) AS CreateDatabaseQuery 
FROM
  information_schema.SCHEMATA 
WHERE
  SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );

 # 原库执行 得到的创建用户语句在新库执行 (排除系统用户)
 SELECT
  CONCAT( 'create user \'', USER, '\'@\'', HOST, '\'' ' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQuery 
FROM
  mysql.`user` 
WHERE
  `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );

# 原库执行 show grants 得到用户权限 然后逐一在新库执行 （也可以复制那几张权限表到新库）
show grants for 'testuser'@'%';

# 至此 新环境已经存在要迁移的库和用户 只是没有数据
# 原环境使用mysqldump备份除系统库外的所有库 
mysqldump  -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql

# 然后导入新环境
mysql -uroot -pxxxx < db.sql