MySQL 逻辑备份工具

简介：

Mydumper、Myloader 是一个第三方的、开源的 MySQL 逻辑备份工具。

支持多线程，比起 mysqldump 要快很多，也能解决 innobackupex 备份工具对 MyISAM 引擎备份的不便。

多线程快速逻辑备份，恢复速度不咋地，支持快照功能。

呃，虽然这样好、那样好，但如果单库、又很大的时候，备份还是选择 innobackupex 物理备份吧，速度就是快。

下载地址：https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz

一、安装 Mydumper

shell > yum -y install gcc gcc-c++ glib2-devel pcre-devel zlib-devel mysql-devel

shell > cd /usr/local/src; wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
shell > tar zxf mydumper-0.9.1.tar.gz
shell > cd mydumper-0.9.1
shell > cmake . && make && make install

shell > /usr/local/bin/mydumper --help
/usr/local/bin/mydumper: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

shell > find / -name "libmysqlclient.so.18"
/usr/local/src/mysql-5.5.52/libmysql/libmysqlclient.so.18
/usr/local/mysql-5.5.52/lib/libmysqlclient.so.18

shell > ln -s /usr/local/mysql-5.5.52/lib/libmysqlclient.so.18 /usr/lib64/

二、Mydumper 全备

shell > /usr/local/bin/mydumper --help

-B, --database           指定备份的数据库，不指定时为备份全库(除 information_schema、performance_schema)
-T, --tables-list        指定备份的表，逗号分隔
-o, --outputdir          指定备份目录
-c, --compress           压缩备份输出
-e, --build-empty-files  即使表中无数据也备份成一个文件
-x, --regex              支持正则表达式 db.table，如 --regex '^(?!(mysql|test))' 不备份 mysql、test 库
-m, --no-schemas         不备份表结构
-d, --no-data            不备份数据
-G, --triggers           备份 triggers
-E, --events             备份 events
-R, --routines           备份存储过程跟函数
-k, --no-locks           不适用共享读锁，会导致数据不一致
-D, --daemon             以守护进程的方式启动
-I, --snapshot-interval  快照间隔时间，默认 60 秒
-h, --host
-u, --user
-p, --password
-P, --PORT
-S, --socket
-t, --threads            启动几个线程，默认 4
-C, --compress-protocol  MySQL 连接上使用压缩协议，建议远程备份时使用
...                      还有一些不常用的指令 通过 --help 获取

shell > /usr/local/bin/mydumper -u xxxx -p xxxx -e -c -B db_xxx -o /data/backup_db/2017-07/db_xxx_05  # 目录 db_xxx_05 会自动创建

shell > ls -1 /data/backup_db/2017-07/db_xxx_05
db_xxx-schema-create.sql.gz
db_xxx.table_1.sql.gz
db_xxx.table_1-schema.sql.gz
...
metadata

# 备份目录下会生成如上几种类型的文件，可以使用 gunzip 解压后读取、分析
# db_xxx-schema-create.sql.gz  备份的数据库创建语句，也就是说恢复时不需要事先创建数据库
# db_xxx.table_1.sql.gz        数据表数据文件
# db_xxx.table_1-schema.sql.gz 数据表结构文件

shell > cat /data/backup_db/2017-07/db_xxx_05/metadata 
Started dump at: 2017-07-05 14:57:56
SHOW MASTER STATUS:
    Log: mysql-bin.000024
    Pos: 760137384
    GTID:(null)

Finished dump at: 2017-07-05 15:01:20

# metadata 记录了备份时刻的 MASTER 信息(如果备份的是从库，还会记录 SLAVE STATUS)，备份开始时间、结束时间

三、Myloader 全备恢复

shell > /usr/local/bin/myloader --help

-d, --directory           指定备份数据存放目录
-o, --overwarite-tables   如表存在，则覆盖写入
-B, --database            指定恢复的数据库
-s, --source-db
-e, --enable-binlog 
-h, --host
-u, --user
-p, --password
-P, --PORT
-S, --socket
-t, --threads
-C, --compress-protocol
...

shell > /usr/local/bin/myloader -u xxxx -p xxxx -B db_xxx -d /data/backup_db/2017-07/db_xxx_05

# 这是恢复单库到一个新的 MySQL(不需要提前创建数据库)，不指定 -B 时，恢复备份中的所有数据
# 恢复单表时，解压后直接 source .sql 即可
# 恢复到原库时，需要添加 -o 参数，覆盖写入
# 机器性能允许的情况下，添加 -t 参数，开启多个并行线程恢复

四、如何加快恢复速度 ?

shell > mysql -u xxxx -p xxxx

mysql > show processlist;

| 26 | root | localhost | baofeng_tv | Query   |    2 | update | INSERT INTO `std_tj_sinideo` VALUES
       |
| 27 | root | localhost | baofeng_tv | Query   |    2 | update | INSERT INTO `std_alum_kwords` VALUES

| 28 | root | localhost | baofeng_tv | Query   |    1 | update | INSERT INTO `std_epode_qyi` VALUES

| 29 | root | localhost | baofeng_tv | Query   |    2 | update | INSERT INTO `std_alum_stams` VALUES

# 默认情况下，可以看到有 4 个线程在插入数据，一个线程负责一张表，执行完换下一张 ( myloader -u -p -B -d )
# 19G 的数据库恢复了 82分22秒，简直不能忍

shell > /usr/local/bin/mydumper -u root -c -e -B baofeng_tv -F 500 -o /data/backup_db/baofeng_tv_06 -t 8
shell > /usr/local/bin/myloader -u root -B baofeng_tv -o -d /data/backup_db/baofeng_tv_06 -t 8

# -F 按大小拆分备份，恢复的时候也并没什么效果，备份速度挺快，恢复不咋地！

# 那个，快照咋搞 ？