Mysql备份与恢复工具 – mydumper与myloader

一. Mysql备份与恢复

mydumper与myloader

1. 编译安装mydumper

参考链接 :

https://launchpad.net/mydumper
https://github.com/maxbube/mydumper

wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz  
cmake .
make
make install

在/usr/local/bin下面产生2个文件：mydumper和myloader

2. 备份结果集

所有备份在一个目录，不指定目录则以日期事件命名：export-20180223-102900
目录中有metadata文件，记录备份时的主库binlog文件和位置

[root@docker02 mydumper]# cat metadata
Started dump at: 2018-02-23 11:56:52
SHOW MASTER STATUS:
        Log: binlog.000009
        Pos: 403849416
        GTID:

Finished dump at: 2018-02-23 11:56:52

备份包含建库sql，建表sql，表内容sql

3. mydumper工作过程

1. 连接目标数据库
2. 通过show processlist判断是否有长查询，根据long-query-guard和kill-long-queries来决定是否退出或者杀掉长查询
3. 锁定myisam表，FTWRL
4. 针对innodb引擎表开启事务，start transaction
5. 创建worker子线程
6. 确定候选表，根据类别分别插入innodb_table,non_innodb_table,table_schemas链表（表结构）
7. 将候选表通过g_async_queue_push加入任务队列，有worker子线程从队列中读取表信息并执行数据导出
8. 执行unlock tables，处理完myisam表后立即解锁，以减少锁定时间
9. 等待worker退出

二. 常用mydumper命令

1. 备份所有数据库

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --outputdir=/backup/mydumper --compress --verbose=3 --logfile=/backup/mydumper/mydumper.log

2. 备份指定库指定表 -b

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' -B db1 -T t1,t2 --outputdir=/backup/mydumper --compress --verbose=3 --logfile=/backup/mydumper/mydumper.log

3. 不带表结构备份表

只有数据，只有dbname.tabname.sql -m

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' -B db1 -m --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

4. 不带表数据备份表

只有表结构，只有dbname-schema-create.sql和dbname.tabname-schema.sql -d

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' -B db1 -d --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

5. 远程压缩及使用压缩协议 -c

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' -B db1 -d --outputdir=/backup/mydumper -c -C tar --verbose=3 --logfile=/backup/mydumper/mydumper.log

6. 匹配多张表备份

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' -B db1 --regex=db* --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

7. 过滤多个库

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql|db2))' --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

8. 即使是空表，也备份结构 -e

mydumper --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex '^(?!(mysql))' -B db1 -e --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log


-rw-r--r--. 1 root root 0 Feb 23 11:48 db1.t3.sql

9. mydumper命令选项

http://blog.itpub.net/22664653/viewspace-2132759/

[root@docker02 mydumper-0.9.1]# mydumper --help
Usage:
  mydumper [OPTION...] multi-threaded MySQL dumping

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

Application Options:
  -B, --database              Database to dump
  指定备份的数据库，不指定则备份所有数据库，包含mysql，但是不含performance_schema
  -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
  指定备份的表，多个表以逗号隔开
  -o, --outputdir             Directory to output files to
  指定目录输出为止，不指定则在当前路径生成目录：export-20180223-102900
  -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000
  指定生成insert语句的字节数，默认1000000
  测试暂时没发现区别，
  -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
  -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB
  -c, --compress              Compress output files
  -e, --build-empty-files     Build dump files even if no data available from table
  备份空表的结构
  -x, --regex                 Regular expression for 'db.table' matching
  -i, --ignore-engines        Comma delimited list of storage engines to ignore
  -m, --no-schemas            Do not dump table schemas with the data
  不备份表结构，只备份数据
  -d, --no-data               Do not dump table data
  不备份表数据，只备份表结构
  -G, --triggers              Dump triggers
  备份触发器
  -E, --events                Dump events
  备份事件
  -R, --routines              Dump stored procedures and functions
  备份存储过程和函数
  -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
  不加共享锁，会引起数据不一致
  --less-locking              Minimize locking time on InnoDB tables.
  -l, --long-query-guard      Set long query timer in seconds, default 60
  设置最长查询时间，默认60s，防止锁
  -K, --kill-long-queries     Kill long running queries (instead of aborting)
  kill掉长查询
  -D, --daemon                Enable daemon mode
  使用守护进程模式
  -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60
  -L, --logfile               Log file name to use, by default stdout is used
  --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
  --skip-tz-utc               
  --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
  --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist                  '
  --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
  -U, --updated-since         Use Update_time to dump only tables updated in the last U days
  --trx-consistency-only      Transactional consistency only
  -h, --host                  The host to connect to
  -u, --user                  Username with privileges to run the dump
  -p, --password              User password
  -P, --port                  TCP/IP port to connect to
  -S, --socket                UNIX domain socket file to use for connection
  -t, --threads               Number of threads to use, default 4
  默认4线程
  -C, --compress-protocol     Use compression on the MySQL connection
  -V, --version               Show the program version and exit
  -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

三. myloader恢复

1. myloader命令参数

[root@docker02 mydumper]# myloader --help
Usage:
  myloader [OPTION...] multi-threaded MySQL loader

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

Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -h, --host                        The host to connect to
  -u, --user                        Username with privileges to run the dump
  -p, --password                    User password
  -P, --port                        TCP/IP port to connect to
  -S, --socket                      UNIX domain socket file to use for connection
  -t, --threads                     Number of threads to use, default 4
  -C, --compress-protocol           Use compression on the MySQL connection
  -V, --version                     Show the program version and exit
  -v, --verbose                     Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

2. 单库备份与恢复

# 备份一个库
mydumper --user=root --password='123456' --host=172.16.2.154 --socket=/data/my3306/run/mysql.sock -B db1 --outputdir=/backup/mydumper --compress -C --verbose=3 --logfile=/backup/mydumper/mydumper.log

# 恢复一个库到原来的实例
myloader --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -s db1 -B db11

[root@docker02 mydumper]# myloader --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper  --verbose=3 -B db1 -o
** Message: 4 threads created
** Message: Dropping table or view (if exists) `db1`.`t1`
** Message: Creating table `db1`.`t1`
** Message: Dropping table or view (if exists) `db1`.`t2`
** Message: Creating table `db1`.`t2`
** Message: Dropping table or view (if exists) `db1`.`t3`
** Message: Creating table `db1`.`t3`
** Message: Thread 2 restoring `db1`.`t2` part 0
** Message: Thread 1 restoring `db1`.`t1` part 0
** Message: Thread 4 shutting down
** Message: Thread 3 shutting down
** Message: Thread 2 shutting down
** Message: Thread 1 shutting down

恢复一个库到其他实例
myloader --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -B db11

3. 从全备恢复一个库

备份全库
mydumper --user=root --password='123456' --host=172.16.2.154 --socket=/data/my3306/run/mysql.sock --outputdir=/backup/mydumper --compress -C --verbose=3 --logfile=/backup/mydumper/mydumper.log
恢复指定库
myloader --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -s db3
恢复指定库并改名
myloader --user=root --password='123456' --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -s db3 -B db33

以上两条如果同时导入同一个实例，后导入的库会报错

mysql> select * from t1;
ERROR 1412 (HY000): Table definition has changed, please retry transaction

4. 从全备恢复一张表

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> source /backup/mydumper/db3.t1-schema.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> source /backup/mydumper/db3.t1.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+-------+-------+
| id   | name1 | name2 |
+------+-------+-------+
|    1 | yzw1  | yzw11 |
|    2 | yzw2  | yzw22 |
|    3 | yzw3  | yzw33 |
|    4 | yzw4  | yzw44 |
+------+-------+-------+
4 rows in set (0.00 sec)