如何在两台服务器之间迁移 MySQL / MariaDB 数据库 阿里云腾讯云迁移案例

MySQL 数据库迁移的需求非常常见，测试数据库迁移至生产数据库，从一台服务器，迁移至另一台服务器，又或者是从一个云服务商迁移至另一个云服务商。不同的需求使用不同的迁移方法，总体来说，MySQL 数据库迁移一般可分为物理迁移和逻辑迁移这两类迁移方法。

物理迁移适用于海量数据整体迁移，可以直接复制数据文件或使用 XtraBackup 来进行备份迁移。不同的服务器之间采用物理迁移需要将两台服务器中的 MySQL server 保持完全一样的版本，配置，权限。这种物理迁移有点是速度快，缺点是要求新服务器与原服务器配置完全一致，即便如此也有可能引起一些未知错误。

如果规模较小，业务也并不繁忙，推荐使用 mysqldump 即逻辑迁移法，它相对来说更轻巧、稳健一些。mysqldump 的原理是在导入到新数据库时，先将原数据库表结构使用 CREATE TABLE 'table' 语句创建，然后在使用 INSERT 将原数据导入至新表中。可以理解为一个批量导入脚本。这样按照规范命令导入数据，大幅减少奇怪的未知错误出现。

MySQL 数据库迁移实例 – 从阿里云迁移数据至腾讯云

本文以市面上最常用的两大云服务商作为迁移案例，讲解如何将阿里云 ECS 云主机中的 MySQL 数据库迁移至腾讯云的 MySQL 数据库中。

第一步：使用 mysqldump 导出数据

情况一：登录远程服务器，在服务器中导出数据库

首先登录阿里云 ECS 服务器，先将需要迁移的原数据库数据导出，生成 *.sql 文件。

mysqldump -u root -p --opt kalacloud_database > /tmp/kalacloud-data-export.sql

• kalacloud_database 为本次准备迁移的数据库
• /tmp/kalacloud-data-export.sql 为导出时生成的数据文件存放的位置与文件名
• --opt 此命令参数开启代表着同时激活了quick，add-drop-table，add-locks，extended-insert，lock-tables 参数。
  • quick 忽略缓存，直接将数据导出到 *.sql 文件中
  • add-drop-table 在每个CREATE TABEL命令之前增加DROP-TABLE IF EXISTS语句，防止数据表重名
  • add-locks 在备份数据表前后添加表锁定与解锁 SQL 语句
  • extended-insert 此参数表示可以多行插入，提高导入效率，开启与否导入效率相差数倍，推荐开启。

执行 mysqldump 命令后，如果没有错误，shell 不会有任何显示。我们可以切到导出目录查看导出结果。如上图所示，/tmp 目录下 kalacloud-data-export.sql 文件已经生成。

情况二：无服务器，独立 MySQL 数据库远程导出至本地计算机

如果你在阿里云购买的不是一台服务器，而是一个独立 MySQL 数据库的话，也可以将数据库导出至本地计算机。

$ mysqldump -h 123.57.56.228  -u kalacloud-remote -p -P3306 --default-character-set=utf8 --set-gtid-purged=OFF --column-statistics=0 kalacloud_database > /Users/kalacloud/Desktop/kalacloud-data-export.sql

• 123.57.56.228： 为远程数据库 ip 地址。
• kalacloud-remote：为拥有远程访问权限的数据库账号。
• -P 3306：数据库访问端口，可根据自己情况修改。
• default-character-set=utf8 ：导出时指定字符集
• set-gtid-purged=OFF ：全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。开启这个功能导入导出时，可能会出错，故关闭。
• /Users/kalacloud/Desktop/kalacloud-data-export.sql ：本地计算机保存路径及保存文件名

提示：mysqldump常见报错：mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”‘)

可在命令中添加 column-statistics=0 参数。因 MySQL 数据库早期版本 information_schema 数据库中没有名为 COLUMN_STATISTICS 的数据表，新版 mysqldump 默认启用，我们可以通过此命令禁用它。

执行命令后，如果没有报错，命令行不会有任何信息输出，但我们已经可以在桌面上看到导出后生成的文件了。

已经导出到本地桌面的远程端数据库

当然，mysqldump 也可以分表备份，比较常见的场景有

# 备份单个库
mysqldump -uroot -p -R -E --single-transactio --databases [database_one] > database_one.sql

# 备份部分表
mysqldump -uroot -p --single-transaction [database_one] [table_one] [table_two] > database_table12.sql

# 排除某些表
mysqldump -uroot -p [database_one] --ignore-table=[database_one.table_one] --ignore-table=[database_one.table_two] > database_one.sql

# 只备份结构
mysqldump -uroot -p [database_one] --no-data > [database_one.defs].sql

# 只备份数据
mysqldump -uroot -p [database_one] --no-create-info > [database_one.data].sql

第二步：复制 *.sql 至新数据库 / 新服务器

使用 SCP 将导出的 *.sql 文件传送到新服务器中。

scp [database name].sql [username]@[servername]:path/to/database/

上图为将本地 *.sql MySQL 数据库传送至 腾讯云服务器中。

如果你在腾讯云购买的是独立 MySQL 数据库的话，你也可以直接在阿里云服务器中直接向腾讯云 MySQL 数据库导入数据。第三步里，我会讲到这种方法。

第三步：导入数据库

方法一：本地数据库 *.sql 文件导入

当数据传输到新服务器后，你可以新建一个数据库，然后使用mysql 命令直接导入数据库

接着我们讲解如何将 *.sql 导入到数据库中。我们先建一个新数据库用作演示。

我们以 root 或有足够权限的账号登录 MySQL：

$ mysql -u root -p

输入登录密码后，进入 MySQL shell 状态。接着我们创建一个新数据库，在这个例子中，我们用 kalacloud_new_database 作为新数据库名称。

mysql> CREATE DATABASE kalacloud_new_database；

执行命令后返回内容

Query OK, 1 row affected (0.00 sec)

然后使用 CTRL+D 退出 MySQL shell ，在命令行中我们导入上文导出到 /tmp/kalacloud-data-export.sql

$ mysql -u root -p kalacloud_new_database < /tmp/kalacloud-data-export.sql

• root 是你可以登录数据库的用户名。
• kalacloud_new_database 是刚刚新建的空数据库，这条命令会把数据导入到这其中。
• /tmp/kalacloud-data-export.sql ：是上文我们从数据库导出的 sql 文件，这里我们把它再导入到新数据库中。

如果运行成功，命令行不会有任何提示。如果运行失败，命令行会提示失败原因。要检测是否导入成功，我们可以登录到 MySQL 查看并检查数据库中的数据。

登录 MySQL server ，使用 USE kalacloud_backup; 选择刚刚我们导入数据的新建数据库，然后使用SHOW TABLES; 查看数据库中包含的表，最后用SELECT * FROM users;打开表查看内容

方法二：在阿里云服务器中，直接远程将数据导入至腾讯云

如果购买的是腾讯云的独立数据库的话，那么我们就没办法先把 *.sql 存过去，再导入。所以我们可以直接在阿里云服务器上发起导入操作，直接导入至腾讯云。

$ mysql -hcdb-5nvdsixw.bj.tencentcdb.com -u root -p -P10010 kalacloud_new_database < /tmp/kalacloud-data-export.sql

• cdb-5nvdsixw.bj.tencentcdb.com 此为腾讯云 MySQL 数据库地址
• -P 数据库端口

数据导入至腾讯云 MySQL ，命令执行成功后，并不会有任何提示。我们可以登录腾讯云数据库看到迁移结果。

远程登录 腾讯云 MySQL 查看导入情况。完成云服务商之间的数据迁移。