MySQL数据迁移

港控/mmm° 2024-03-30 10:57 68阅读 0赞

迁移方案及注意点

物理迁移适用于大数据量下的整体迁移。物理迁移包括拷贝数据文件和使用 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 权限的管理员用户，也可以避免一些因权限产生的问题。

迁移完成后，对于新环境，我们还应该再次进行检查，比如表的个数是否相同，随机抽查几张表，数据是否相同、是否有乱码等。只有确定无误才大功告成。

总结：

![be36a60ab5485fa234721155e57c679f.png][]

[be36a60ab5485fa234721155e57c679f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/409e9db2ce9c4a7e83742a94eb29faa3.png