本地CentOS7 MySQL5.7迁移至Azure Paas 5.7 (Azure Database for MySQL flexible server) - 使用mydumper/myloader

本次采用mydumper 说明 【实践数据仅代表本人的环境实验结果】

• 1. 数据库大约 90G 数据库稍大
• 2. 8核的CPU 用 mydumper 备份 比 mysqldump 速度快 10倍 左右
• 3. myloader 比 mysql -u用户名 -p密码 < /home/你的库.sql 还原 速度大约快5倍左右

用 mysqldump 停机迁移 大约需要十几个小时 ，使用 mydumper 大约 1.5小时左右

一、先决条件

序号	环境	用途
1	CentOS7 MySQL5.7	源MySQL（待迁移到Azure云）
2	Azure Database for MySQL flexible server	Azure云上的目标MySQL
3	Ubuntu24.04 VM	操作备份和还原，网络可以连接到 源 和 目标

二、Ubuntu24.04 离线安装mydumper

1.查看Ubuntu24.04的发行版本

lsb_release -cs

结果如下,可以看到发型版本是noble：

2. 查看CPU 核数

nproc

可以看到 CPU 是 8 核

2. 下载mydumper - 与发行版本要匹配

下载地址：https://github.com/mydumper/mydumper/releases

3.上传到 Ubuntu24.04 这台机器

例如上传到 /home/mysqldumper 目录

4.切换到 /home/mysqldumper 目录

cd /home/mysqldumper

5.安装依赖项

# 更新运行 Linux 的 Azure VM 上的包索引
sudo apt update
# 安装 MySQL 客户端
sudo apt install -y mysql-client
# 安装 libatomicl
sudo apt install -y libatomic1
# 安装 libpcre3
sudo apt install -y libpcre3

6. 安装 mydumper 的 .deb 包

# 安装 mydumper 的 .deb 包
sudo dpkg -i mydumper_0.19.1-3.noble_amd64.deb

7.安装完毕，运行以下命令以验证 mydumper/myloader 是否已成功安装

mydumper --version

三、mydumper 备份

# 本人采用的是无压缩的mydumper方式，后边要修改sql中的内容
mydumper --host=你的源Host --user=你的源MySQL账号 --password='你的源MySQL密码' --outputdir=./mybackup --rows=100000 --build-empty-files --threads=16 --compress-protocol --trx-tables --events --routines --triggers  --ssl --regex '^(你的源数据库\.)' -L mydumper-logs.txt

•	--host：目标主机
•	--user：用户名
•	--password：密码
•	--rows：尝试将表拆分为包含这么多行的区块
•	--directory：备份目录
•	--queries-per-transaction：每个事务的语句数
•	--threads：并发要使用的线程数，默认值为 4。 建议使用等于计算机 vCore 数 2 倍的值
•	--compress-protocol：启用压缩协议
•	--ssl：启用 SSL
•	--verbose：日志详细级别
•	--events   ：转储事件。缺省情况下，不转储事件
•	--routines ：转储存储过程和函数。默认情况下，它不转储存储过程或函数
•	--triggers ：导出触发器。默认情况下，不会转储触发器

dbname-schema-create.sql：建库语句。
dbname-schema-post.sql：包含事件、存储过程及函数创建语句（若存在则有该文件）。
dbname.tbname.metadata：记录这个表的行数。
dbname.tbname-schema.sql：此表的创建语句。
dbname.tbname-schema-triggers.sql：创建触发器语句（若该表存在触发器 则有此文件）。
dbname.tbname.sql：该表的插入数据语句（若该表为空 则不存在此文件）。
dbname.viewname-schema.sql：创建视图语句（只列举出视图字段）。
dbname.viewname-schema-view.sql：创建视图的真正语句。
metadata：记录开始及结束备份的时间以及二进制日志位置。

四、myloader 还原

myloader --host=你的目标MySQLHost --user=你的目标MySQL用户名 --password='你的目标MySQL密码' --directory=./mybackup --queries-per-transaction=500 --threads=16 --ssl --verbose=3 2>myloader-logs.txt

•	--host：目标主机
•	--user：用户名
•	--password：密码
•	--directory：备份目录
•	--queries-per-transaction：每个事务的语句数，建议设置为不大于 500 的值
•	--threads：并发线程数
•	--compress-protocol：启用压缩协议
•	--ssl：启用 SSL
•	--verbose：日志详细级别

五、附录 - 经验记录

Q1 - mydumper遇到：

报错：--lock-all-tables is deprecated use --trx-tables instead
原因：我安装的 mydumper 0.19 版本，不支持 --trx-consistency-only命令
      不同的mydumper版本可能会有不同的参数，使用时请查看帮助文档
解决方案：--trx-consistency-only 变更为0.19支持的 --trx-tables

Q2 - myloader遇到:

报错：ERROR 3161: Storage engine MyISAM is disabled (Table creation is disallowed).
原因：在 Azure Database for MySQL（包括大多数云 MySQL 服务）上，ROW_FORMAT=FIXED 主要用于 MyISAM       引擎，而 InnoDB 并不支持 FIXED 这个选项。
解决方案：
SELECT Table_SCHEMA,TABLE_Name,Engine,CONCAT('ALTER TABLE ','`',TABLE_NAME,'`',' ENGINE=InnoDB;') UpdateSQL FROM information_schema.TABLES  WHERE Table_SCHEMA = '你的数据库名称' and ENGINE = 'MyISAM';

利用UpdateSQL，把MyISAM引擎统一换成InnoDB 解决
语法示例：ALTER TABLE `表名称` ENGINE=InnoDB;

Q3 - myloader遇到:

报错：ERROR 1031: Table storage engine for 'MySQL的一个表' doesn't have this option
原因：Q2 改了 ENGINE 的 ROW_FORMAT,但是还有一个字段 CREATE_OPTIONS 未改掉
在 MySQL 中，CREATE_OPTIONS 是表的元数据，不能直接修改。要调整 CREATE_OPTIONS，需要通过修改表的属性（如 ROW_FORMAT 或存储引擎）来间接实现。
解决方案SQL如下：

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT,CREATE_OPTIONS,
CONCAT('ALTER TABLE ','`',TABLE_NAME,'`',' ROW_FORMAT=DYNAMIC;')
FROM information_schema.tables
WHERE TABLE_SCHEMA = '你的数据库名称' AND CREATE_OPTIONS LIKE '%FIXED%';

查出来对应的表，用如下SQL来修改：
ALTER TABLE 你的表名称 ROW_FORMAT=DYNAMIC;

Q4 - myloader遇到:

报错：ERROR 1118: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
原因：MySQL InnoDB 表的单行数据（不包括 BLOB/TEXT 字段）最大不能超过 8126 字节。如果表中有太多 VARCHAR、CHAR 或其它定长/变长字段，且总长度超限，就会报此错。
解决方案（Azure要用管理员在Portal修改这个参数为Off）：SET GLOBAL innodb_strict_mode = OFF;

innodb_strict_mode 是 MySQL InnoDB 存储引擎中的一个配置选项，它决定了在某些情况下数据库的行为模式。具体来说，这个选项控制了当遇到一些可能有问题的表定义或操作时，InnoDB 是否应该以严格模式运行。
当 innodb_strict_mode=ON（启用）：在这种模式下，如果尝试创建或修改表时发生任何不符合 InnoDB 要求的情况（例如，行大小超出限制），InnoDB 会直接返回错误，阻止表的创建或修改。这种行为有助于早期发现问题，并强制用户解决这些问题，而不是让潜在的问题悄无声息地存在。
当 innodb_strict_mode=OFF（禁用）：在这种情况下，如果发生类似的问题，InnoDB 不会直接返回错误，而是可能会调整操作或忽略某些设置（根据情况而定），并尽可能完成请求的操作。这可能导致一些非预期的行为或限制。

Q5 - myloader遇到:

报错：ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
原因：当前用户没有 SUPER 权限，无法以 DEFINER='root'@'%' 的身份创建视图。
     在 Azure Database for MySQL 这类云数据库，通常不允许普通用户拥有 SUPER 权限，也无法直接以 root 身份操作。
解决方案：
定位到文件目录：
cd /home/mybackup 

批量 去除 •	去除 DEFINER（如果表特别多1000+，执行起来比较慢）
sed -i 's/DEFINER=`[^`]*`@`[^`]*` *//g' *.sql

也可以分别执行如下3个语句：

批量 去除 事件、存储过程、函数的 DEFINER
sed -i 's/DEFINER=`[^`]*`@`[^`]*` *//g' *-schema-post.sql 
批量 去除 触发器的 DEFINER
sed -i 's/DEFINER=`[^`]*`@`[^`]*` *//g' *-schema-triggers.sql 
批量 去除 视图 DEFINER
sed -i 's/DEFINER=`[^`]*`@`[^`]*` *//g' *view.sql 

六、备忘信息

mydumper备忘信息：
  单个数据库 regex 的写法：--regex '^(你的源数据库\.)'
  多个数据库 regex 的写法：--regex '^(你的数据库1\.|你的数据库2\.)'
  如果是要采用压缩方式则 加上 compress，示例如下：
  mydumper --host=你的源Host --user=你的源MySQL账号 --password='你的源MySQL密码' --outputdir=./mybackup --rows=100000 --compress --build-empty-files --threads=16 --compress-protocol --trx-tables --ssl --regex '^(你的源数据库\.)' -L mydumper-logs.txt