mysql数据库全部迁移 mysql数据库迁移备份工具

摘要：

        MySQL跨平台迁移，字符集转换重导库，大版本升级，误操作大表误删除恢复...... 妙用无穷！

1、工具介绍

C++开发的多线程的MySQL一致性逻辑备份工具。

github地址：https://github.com/maxbube/mydumper

官方文档：https://centminmod.com/mydumper.html

优点：好多好多，逻辑性的分库分表备份可操作性强，多线程加速成倍减少迁移时间(物理备份是有局限性的)。

缺点：非要挑刺就是不支持windows，不过可以用linux跳板机远程操作，还有就是非官方出品，不过同样是C++编写业内也使用多年了。

工具分两个命令：

    mydumper备份导出

    myloader恢复导入

2、安装

#依赖包

yum install glib2-devel mysql-devel zlib-devel pcre-devel -y

#rpm安装

rpm -ivh /opt/mydumper-0.9.5-2.el7.x86_64.rpm

3、mydumper重要备份参数说明

-B, --database	指定需要备份的DB，默认全部
-T, --tables-list	指定需要备份的表列表逗号分开(非正则)
-O, --omit-from-file	包含数据库列表的文件。要跳过的表条目，每行一个(在应用regex选项之前跳过)
-o, --outputdir	备份文件输出的目录
-s, --statement-size	备份文件中INSERT语句的大小(以字节为单位), 默认 1000000
-r, --rows	备份时对大表进行行数切割大小，与--chunk-filesize只能使用其一
-F, --chunk-filesize	备份时对大表进行存储切割大小，默认MB
-c, --compress	压缩输出文件
-e, --build-empty-files	只备份表结构
-x, --regex	使用正则匹配需要备份的表
-i, --ignore-engines	配置忽略的引擎列表逗号隔开
-N, --insert-ignore	备份数据生成INSERT IGNORE的语法
-m, --no-schemas	备份中不生成创建schema 语句
-d, --no-data	不备份数据
-G, --triggers	备份 triggers
-E, --events	备份 events
-R, --routines	备份 procedures 和functions
-W, --no-views	不备份 VIEWs
-k, --no-locks	不建议生产使用，可能导致非一致性备份
-l, --long-query-guard	设置长SQL运行等待时间(秒)，默认60s，超时退出备份
-K, --kill-long-queries	设置长SQL运行等待时间(秒)，默认60s，超时kill掉长SQL继续备份
-D, --daemon	启用守护进程模式，备份完成后进程不会退出需要注意，结合--snapshot-interval可以设置成定时任务
-I, --snapshot-interval	每次备份相隔的时间以分钟为单位，默认60，必须在--daemon的模式下
-L, --logfile	备份日志文件路径，默认不使用而是用stout标准输出
--tz-utc	设置时区，默认使用 --skip-tz-utc 关闭时区设置
--lock-all-tables	使用 LOCK TABLE 替代 FTWRL
-U, --updated-since	指定备份U天前更新过的表
--trx-consistency-only	如果只包含innodb建议加上，可以减少锁表时间，含有myisam重要数据表则建议不加，可能造成其数据不一致,binlog位置不准
--complete-insert	备份文件中使用包含列名的完整插入语句，建议添加，兼容新增字段的情况
-h, --host
-u, --user
-p, --password
-a, --ask-password
-P, --port
-S, --socket
-t, --threads	并行备份的线程数，默认4
-C, --compress-protocol	在MySQL连接上使用压缩
-v, --verbose	输出信息的级别, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

4、全量备份测试

4.1、使用参数说明

#--trx-consistency-only 因为只含有innodb表，减少lock time

#-G -E -R 备份triggers，events，procedures ，functions 不加-W 默认备份view

#-t 4   根据CPU和生产高低峰情况合理分配

#-L 日志推荐输出到文件，方便后续验证结果和排错

#-o 备份文件目录

#-v 方便查看备份进度，建议输出到log file中

mydumper -u root -p root123 -P 3306 -S /tmp/mysql.sock  --trx-consistency-only  --complete-insert -t 4 -v 3 -c -G -E -R -L /data/backup/mydumper.log -o /data/backup/

[root@localhost backup]#mydumper -u root -p root123 -P 3306 -S /tmp/mysql.sock  --trx-consistency-only  --complete-insert -t 4 -v 3 -c -G -E -R -L /data/backup/mydumper.log -o /data/backup/[root@localhost backup]#[root@localhost backup]# ps -ef | grep mydumperroot     25087     1 99 11:27 ?        00:00:25 mydumper -u root -p root123 -P 3306 -S /tmp/mysql.sock --trx-consistency-only --complete-insert -t 4 -v 3 -c -G -E -R -L /data/backup/mydumper.log -o /data/backup/root     25109 18917  0 11:27 pts/0    00:00:00 grep --color=auto mydumper

4.2、备份完成信息

tail -f /data/backup/mydumper.log
2020-08-08 11:39:05 [INFO] - Thread 3 shutting down
2020-08-08 11:39:05 [INFO] - Thread 4 shutting down
2020-08-08 11:39:18 [INFO] - Thread 2 shutting down
2020-08-08 11:41:59 [INFO] - Thread 1 shutting down
2020-08-08 11:41:59 [INFO] - Finished dump at: 2020-08-08 11:41:59

4.3、一致性metadata信息

cat /data/backup/last_dump/metadata
Started dump at: 2020-08-08 14:35:39
SHOW MASTER STATUS:
    Log: mysql-bin.000303
    Pos: 235
    GTID:45fc66c4-95c5-11ea-b29a-0050569f78b2:1-12907,
dba0c3df-2dc1-11e9-8a0c-0050569f2390:1-13686780
Finished dump at: 2020-08-08 11:41:59

4.4、基础后台运行脚本

vi full_mydumper_backup.sh#!/bin/bashnohup mydumper -u root -p root123 -P 3306 -S /tmp/mysql.sock  --trx-consistency-only  --complete-insert -t 4 -v 3 -c -G -E -R -L /data/backup/mydumper.log -o /data/backup/ &

5、myloader恢复重要参数

-d, --directory	导入文件目录
-q, --queries-per-transaction	每个事务中包含的SQL语句，默认1000
-o, --overwrite-tables	覆盖已存在的表
-B, --database	目标数据库名
-s, --source-db	源端数据库名
-e, --enable-binlog	恢复操作记录binlog
-h, --host
-u, --user
-p, --password
-a, --ask-password
-P, --port
-S, --socket
-t, --threads
-C, --compress-protocol	在MySQL连接上使用压缩
-v, --verbose	输出信息的级别, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

6、全量恢复测试

基础恢复脚本

vi import_myloader.sh#!/bin/bashnohup myloader -u root -p root123 -P 3306 -S /tmp/mysql.sock -d /data/backup/last_dump -t 4 -o -v 3 > myloader.log 2>&1 &

7、工作原理流程图