乐趣区

关于mysql:MySQL数据迁移那些事儿

jiezi

前言：

在平时工作中，常常会遇到数据迁徙的需要，比方要迁徙某个表、某个库或某个实例。依据不同的需要可能要采取不同的迁徙计划，数据迁徙过程中也可能会遇到各种大小问题。本篇文章，咱们一起来看下 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

对于迁徙局部库和局部表，也能够采纳上方相似计划，只是备份时要按需备份，上面列举出几种常见的 mysqldump 备份场景：

# 备份单个库
mysqldump -uroot -pxxxxx -R -E --single-transactio --databases db1 > db1.sql

# 备份局部表
mysqldump -uroot -pxxxxx --single-transaction db1 tb1 tb2 > tb12.sql

# 排查某些表
mysqldump -uroot -pxxxxx db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > db1.sql

# 只备份构造或数据
mysqldump -uroot -pxxxxx testdb --no-data > testdb_jiegou.sql
mysqldump -uroot -pxxxxx testdb --no-create-info > testdb_data.sql

总得来说，应用 mysqldump 计划更加灵便，为了疾速迁徙，咱们应该尽量减少备份内容，比如说排除无用的日志表。对于一些大表，咱们也能够采纳独自迁徙的计划，比方说，db1 中的 tb1 表特地大，咱们能够在备份时先排除 tb1，对于大表 tb1，能够应用 LOAD DATA 形式或舍弃再导入表空间的形式来迁徙。

数据迁徙过程中，可能会遇到各种谬误，一步步去解决即可。倡议在新库创立好用户并授予好权限后再迁徙，这样能够避免出现视图及函数导入谬误，因为视图及函数有个定义者的概念。新环境执行导入时，最好应用 root 等具备 SUPER 权限的管理员用户，也能够防止一些因权限产生的问题。

迁徙实现后，对于新环境，咱们还应该再次进行查看，比方表的个数是否雷同，随机抽查几张表，数据是否雷同、是否有乱码等。只有确定无误才功败垂成。

总结：

本篇文章介绍了 MySQL 数据库数据迁徙相干计划及留神点，总结如下思维导图：

退出移动版