【赵渝强老师】使用mydumper备份MySQL

MySQL在备份方面包含了自身的mysqldump工具，但其只支持单线程工作，这就使得它无法迅速的备份数据。而mydumper作为一个实用工具，能够良好支持多线程工作，这使得它在处理速度方面十倍于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper能更好的完成备份任务。

mydumper具备以下特点：

• 轻量级C语言写的
• 执行速度比mysqldump快10倍
• 事务性和非事务性表一致的快照(适用于0.2.2以上版本)
• 快速的文件压缩
• 支持导出binlog
• 多线程恢复(适用于0.2.1以上版本)
• 以守护进程的工作方式，定时快照和连续二进制日志(适用于0.5.0以上版本)
• 开源组件(GNU GPLv3)

下面通过具体的步骤来演示如何使用mydumper进行数据库的热备份与恢复。
（1）安装mydumper

yum install -y \
https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm

（2）查看mydumper的帮助信息

mydumper --help

# 输出的信息如下：
Usage:
  mydumper [OPTION...] multi-threaded MySQL dumping

Help Options:
  -?, --help             Show help options

Application Options:
  -B, --database         Database to dump
  -T, --tables-list      Comma delimited table list to dump 
                            (does not exclude regex option)
  -O, --omit-from-file   File containing a list of database.table 
entries to skip, one per line 
(skips before applying regex option)
  -o, --outputdir        Directory to output files to
  ......

下表是mydumper常用参数的说明。

（3）创建mydumper备份的存储目录

mkdir -p /databackup/mydumper/all/

（4）备份所有数据库

mydumper -u root --password=Welcome_1 \
--socket /tmp/mysql.sock \
--outputdir /databackup/mydumper/all/

# 提示：这里的--socket /tmp/mysql.sock参数是必须的。在默认情况下mydumper在加载
       /var/lib/mysql/mysql.sock文件。如果该文件不存在，将出现下面的错误信息。
** (mydumper:69463): CRITICAL **: Error connecting to database: 
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

（5）查看目录/databackup/mydumper/下的内容

tree /databackup/mydumper/all | more

# 输出的信息如下：
/databackup/mydumper/all
├── demo1.audit_message-schema.sql
├── demo1.audit_message.sql
├── demo1.classes-schema.sql
├── demo1.dept-schema.sql
├── demo1.dept.sql
├── demo1.emp-schema.sql
├── demo1.emp.sql
├── demo1.indextable1-schema.sql
├── demo1.indextable1.sql
......

（6）查看文件/databackup/mydumper/demo1.emp.sql的内容

cat /databackup/mydumper/demo1.emp.sql

# 输出的信息如下：
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
INSERT INTO `emp` VALUES
(7369,"SMITH","CLERK",7902,"1980/12/17",800,NULL,20),
(7499,"ALLEN","SALESMAN",7698,"1981/2/20",1600,300,30),
(7521,"WARD","SALESMAN",7698,"1981/2/22",1250,500,30),
(7566,"JONES","MANAGER",7839,"1981/4/2",3670,NULL,20),
(7654,"MARTIN","SALESMAN",7698,"1981/9/28",1250,1400,30),
(7698,"BLAKE","MANAGER",7839,"1981/5/1",2850,NULL,30),
(7782,"CLARK","MANAGER",7839,"1981/6/9",2550,NULL,10),
(7788,"SCOTT","ANALYST",7566,"1987/4/19",3000,NULL,20),
(7839,"KING","PRESIDENT",-1,"1981/11/17",5950,NULL,10),
(7844,"TURNER","SALESMAN",7698,"1981/9/8",1500,NULL,30),
(7876,"ADAMS","CLERK",7788,"1987/5/23",1100,NULL,20),
(7900,"JAMES","CLERK",7698,"1981/12/3",950,NULL,30),
(7902,"FORD","ANALYST",7566,"1981/12/3",3000,NULL,20),
(7934,"MILLER","CLERK",7782,"1982/1/23",1400,NULL,10);

# 提示：可以看出mydump也将数据备份成了SQL语句。

（7）备份指定数据库demo1

mkdir -p /databackup/mydumper/demo1/

mydumper -u root --password=Welcome_1 \
--socket /tmp/mysql.sock \
--database demo1 \
--outputdir /databackup/mydumper/demo1/

（8）备份指定数据库demo1下指定的表（员工表和部门表）

mkdir -p /databackup/mydumper/multi_tables/

mydumper -u root --password=Welcome_1 \
--socket /tmp/mysql.sock \
--database demo1 \
--tables-list emp,dept \
--outputdir /databackup/mydumper/multi_tables/

（9）删除demo1数据库

mysqladmin -uroot -pWelcome_1 drop demo1

# 输出的信息如下：
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'demo1' database [y/N] y
Database "demo1" dropped

（10）使用myloader执行恢复数据库demo1

mysqladmin -uroot -pWelcome_1 create demo1 

myloader -u root -p Welcome_1 --socket /tmp/mysql.sock \
--database demo1 \
-d /databackup/mydumper/demo1/

# 提示：在执行恢复之前，demo1如果没有，则需要事先创建。

（11）检查数据是否恢复