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

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

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

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 此命令参数开启代表着同时激活了quickadd-drop-tableadd-locksextended-insertlock-tables 参数。
    • quick 忽略缓存,直接将数据导出到 *.sql 文件中
    • add-drop-table 在每个CREATE TABEL命令之前增加DROP-TABLE IF EXISTS语句,防止数据表重名
    • add-locks 在备份数据表前后添加表锁定与解锁 SQL 语句
    • extended-insert 此参数表示可以多行插入,提高导入效率,开启与否导入效率相差数倍,推荐开启。

01-mysqldump.png

执行 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 默认启用,我们可以通过此命令禁用它。

02-mysqldump-export.png

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

03-desktop.jpg

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

当然,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/

04-sql.png

上图为将本地 *.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 查看并检查数据库中的数据。

05-mysql.png

登录 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 数据库端口

06-tencent.png

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

07-tencent-database.png

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

总结

在本教程中,我们讲解了几种数据迁移的方法和场景。更多数据库相关教程可访问 卡拉云 查看。

如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9