MySQL - MySQL 8.0(七)备份：使用mydumper进行备份

此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖

  mydumper是一个类似mysqlpump的逻辑备份工具。
  与mysqlpump相比，mydumper在以下方面具有优势。

• 并行（因此速度更快）和性能（避免使用复杂的字符集转换例程，因而代码总体上很高效）。
• 一致性。 mydumper维护所有线程的快照，提供准确的主库和从库日志位置等。mysqlpump不保证一致性。
• 更易于管理输出（将表和元数据文件分离，并且方便查看/解析数据）。mysqlpump将所有内容写入一个文件，这限制了加载部分数据库对象的选项。
• 使用正则表达式包含和排除数据库对象。
• 有用于终止阻塞备份和所有后续查询的长事务的选项。

安装

mydumper是一款开源的备份工具，需要单独安装。
官网下载、GitHub下载

1. Apt

# nangy @ nangy-vm in ~/Downloads [17:15:01] C:127
$ wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper_0.9.5-2.stretch_amd64.deb

# nangy @ nangy-vm in ~/Downloads [17:15:56] 
$ sudo gdebi mydumper_0.9.5-2.stretch_amd64.deb 

# 重启
# nangy @ nangy-vm in ~/Downloads [17:15:56] 
$ shutdown -r now

2. Yum

# nangy @ nangy-vm in ~/Downloads [17:15:56] 
$ sudo yum install -y "https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm"

完全备份

1. 语法

  以下命令会将所有数据库备份到/data/backups文件夹中：

$ mydumper -u root --password='{password}' --outputdir /data/backups/full_backup

  多个文件将在/data/backups文件夹中被创建，（如果目录未创建，则使用sudo mkdir -pv /data/backups/full_backup)

• 每个数据库的CREATE DATABASE语句均为<database_name>-schema-create.sql
• schema文件存储为<database_name>.<table>-schema.sql
• 数据文件存储为<database_name>.<table>.sql
• 视图存储为<database_name>.<table>-schema-view.sql
• 存储的例程、触发器和事件存储为<database_name>-schema-post.sql

# 执行备份
# nangy @ nangy-vm in ~ [18:32:54]
$ mydumper -u root --password='{password}' --outputdir /data/backups/full_backup

# 查看备份
# nangy @ nangy-vm in ~ [18:49:20] 
$ ll /data/backups/full_backup/company*
-rw-r--r-- 1 nangy nangy 131 8月  13 18:15 /data/backups/company-schema-create.sql
-rw-r--r-- 1 nangy nangy 404 8月  13 18:15 /data/backups/company.customers-schema.sql
-rw-r--r-- 1 nangy nangy 273 8月  13 18:15 /data/backups/company.customers.sql
-rw-r--r-- 1 nangy nangy 301 8月  13 18:15 /data/backups/company.payments-schema.sql
-rw-r--r-- 1 nangy nangy 181 8月  13 18:15 /data/backups/company.payments.sql

2. 踩坑

• 身份验证插件报错
    mydumper不兼容MySQL 8.0 的默认身份验证插件caching_sha2_password，修改备份用户（学习嘛，简单点，我用的root用户备份）的插件类型为mysql_native_password即可。
• 参考：
  Linux - MySQL 8.0(二)基本操作：远程连接（Yum、Apt） → 坑！
  MySQL - MySQL 8.0(二)基本操作：用户 → 修改密码&身份验证插件类型

# nangy @ nangy-vm in ~ [18:01:38] 
$ mydumper -u root --password --outputdor /data/backups/full_backup

** (mydumper:6667): CRITICAL **: Error connecting to database: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

# 登录root用户，修改成旧的身份验证插件
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '{new_password}';

• 查询超时报错
    如果有任何查询超过60秒，mydumper将失败并出现以下错误提示：

** (mydumper:18754): CRITICAL **： There are queries in PROCESSLIST running longer than 60s, aborting dump,use --long-query-guard to change the guard value, kill queries (--kill-long-queries) or use different server for dump

  为了避免这种情况，可以使用--kill-long-queries选项，或将--long-query-guard设置为更大的值。
  --kill-long-queries选项会结束所有长于60秒（或--long-query-guard设置的值）的查询。请注意--kill-long-queries还会因为一个 bug 而杀死复制线程（ https://bugs.launchpad.net/mydumper/+bug/1713201 ）：

# nangy @ nangy-vm in ~ [18:01:38] 
$ sudo mydumper --kill-long-queries --outputdir /data/backups/full_backup
** (mydumper:18915): WARNING **： Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.
** (mydumper:18915): WARNING **： Killed a query that was running for 368s

一致的备份

  备份目录中的元数据文件包含用于一致备份的二进制日志坐标。
  在主服务器上，备份目录中的元数据文件会捕获二进制日志位置：

# nangy @ nangy-vm in ~ [9:13:04] C:1
$ sudo cat /data/backups/full_backup/metadata 
Started dump at: 2019-08-14 09:08:12
SHOW MASTER STATUS:
	Log: binlog.000065
	Pos: 155
	GTID:

Finished dump at: 2019-08-14 09:08:16

  在从服务器上，备份目录中的元数据文件会捕获主服务器和从服务器的二进制日志位置

备份单独表

  以下命令会将employees数据库的employees表备份到/data/backups/table_backup目录中

# nangy @ nangy-vm in ~ [9:13:16] 
$ mydumper -u root --password='{password}' -B employees -T employees --triggers events --routines --outputdir /data/backups/table_backup

# nangy @ nangy-vm in ~ [9:30:47] 
$ ll /data/backups/table_backup
总用量 17M
-rw-r--r-- 1 nangy nangy 133 8月  14 09:29 employees-schema-create.sql
-rw-r--r-- 1 nangy nangy 597 8月  14 09:29 employees.employees-schema.sql
-rw-r--r-- 1 nangy nangy 977 8月  14 09:29 employees-schema-post.sql
-rw-r--r-- 1 nangy nangy 17M 8月  14 09:29 employees.employees.sql
-rw-r--r-- 1 nangy nangy 133 8月  14 09:29 metadata

这些文件的命名规则如下：

• employees-schema-create.sql包含CREATE DATABASE语句。
• employees.employees-schema.sql包含CREATE TABLE语句。
• employees-schema-post.sql包含ROUTINES、TRIGGERS和EVENTS。
• employees.employees.sql包含INSERT语句形式表示的实际数据。

使用正则表达式来备份特定的数据库

  可以使用regex选项包含/排除特定数据库。以下命令将从备份中排除mysql和test数据库：

# nangy @ nangy-vm in ~ [9:37:43]
$ mydumper -u root --password='{password}' --regex '(?!(mysql|test))' --outputdir /data/backups/regex_backup

# nangy @ nangy-vm in ~ [9:42:47] 
$ ll /data/backups/regex_backup/company*
-rw-r--r-- 1 nangy nangy 404 8月  14 09:42 /data/backups/regex_backup/company.customers-schema.sql
-rw-r--r-- 1 nangy nangy 273 8月  14 09:42 /data/backups/regex_backup/company.customers.sql
-rw-r--r-- 1 nangy nangy 301 8月  14 09:42 /data/backups/regex_backup/company.payments-schema.sql
-rw-r--r-- 1 nangy nangy 181 8月  14 09:42 /data/backups/regex_backup/company.payments.sql
-rw-r--r-- 1 nangy nangy 131 8月  14 09:42 /data/backups/regex_backup/company-schema-create.sql

采用mydumper备份大表

  为了加速大表的转储和恢复，可以将它分成小块。块的大小可以通过它包含的行数来指定，每个块将被写入一个单独的文件中。

# nangy @ nangy-vm in ~ [9:51:11] C:1
$ mydumper -u  root --password='{password}' -B employees -T employees --triggers --events --routines --rows=10000 -t 8 --trx-consistency-only --outputdir /data/backups/bigtable_backup

** (mydumper:9911): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.

• --rows：将表分成块，每一块的数据行数。
• -t：指定线程的数量。
• --trx-consistency-only：如果只使用事务表，例如InnoDB，那么使用此选项将使锁定最小化。

  对于每一个块，都会创建一个名为<database_name>.<table_name>.<number>.sql的文件，其中数字用零填充，补足5位：

# nangy @ nangy-vm in ~ [9:51:29] 
$ ll /data/backups/bigtable_backup
总用量 17M
-rw-r--r-- 1 nangy nangy 964K 8月  14 09:51 employees.employees.00000.sql
-rw-r--r-- 1 nangy nangy 964K 8月  14 09:51 employees.employees.00001.sql
-rw-r--r-- 1 nangy nangy 963K 8月  14 09:51 employees.employees.00002.sql
-rw-r--r-- 1 nangy nangy 964K 8月  14 09:51 employees.employees.00003.sql
-rw-r--r-- 1 nangy nangy 964K 8月  14 09:51 employees.employees.00004.sql
-rw-r--r-- 1 nangy nangy 896K 8月  14 09:51 employees.employees.00005.sql
-rw-r--r-- 1 nangy nangy  562 8月  14 09:51 employees.employees.00006.sql
-rw-r--r-- 1 nangy nangy 741K 8月  14 09:51 employees.employees.00011.sql
-rw-r--r-- 1 nangy nangy 981K 8月  14 09:51 employees.employees.00012.sql
-rw-r--r-- 1 nangy nangy 981K 8月  14 09:51 employees.employees.00013.sql
-rw-r--r-- 1 nangy nangy 980K 8月  14 09:51 employees.employees.00014.sql
-rw-r--r-- 1 nangy nangy 980K 8月  14 09:51 employees.employees.00015.sql
-rw-r--r-- 1 nangy nangy 981K 8月  14 09:51 employees.employees.00016.sql
-rw-r--r-- 1 nangy nangy 160K 8月  14 09:51 employees.employees.00017.sql
-rw-r--r-- 1 nangy nangy 901K 8月  14 09:51 employees.employees.00023.sql
-rw-r--r-- 1 nangy nangy 980K 8月  14 09:51 employees.employees.00024.sql
-rw-r--r-- 1 nangy nangy 981K 8月  14 09:51 employees.employees.00025.sql
-rw-r--r-- 1 nangy nangy 980K 8月  14 09:51 employees.employees.00026.sql
-rw-r--r-- 1 nangy nangy 981K 8月  14 09:51 employees.employees.00027.sql
-rw-r--r-- 1 nangy nangy 980K 8月  14 09:51 employees.employees.00028.sql
-rw-r--r-- 1 nangy nangy  597 8月  14 09:51 employees.employees-schema.sql
-rw-r--r-- 1 nangy nangy  133 8月  14 09:51 employees-schema-create.sql
-rw-r--r-- 1 nangy nangy  977 8月  14 09:51 employees-schema-post.sql
-rw-r--r-- 1 nangy nangy  133 8月  14 09:51 metadata

无阻塞备份

  为了提供一致的备份，mydumper通过执行FLUSH TABLES WITH READ LOCK来获取GLOBAL LOCK。
  如果有任何长时间运行的事务，使用FLUSH TABLES WITH READ LOCK是很危险的。为了避免这种情况，可以传递--kill-long-queries选项来终止阻塞查询，而不是中止mydumper。

• --trx-consistency-only：相当于mysqldump的--single-transaction，但具有binlog位置。显然，这个位置只适用于事务表。使用这个选项的好处之一是全局读锁只保持在线程的协调过程中，所以一旦事务开始，该选项就会被释放。
• --use-savepoints：减少元数据锁定问题（需要SUPER权限）。

压缩备份

  可以指定--compress选项来进行压缩备份：

# nangy @ nangy-vm in ~ [9:52:21] 
$ mydumper -u  root --password='{password}' -B employees -T employees -t 8 --trx-consistency-only --compress --outputdir /data/backups/compress_backup

** (mydumper:10893): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.

# nangy @ nangy-vm in ~ [10:14:25] 
$ ll /data/backups/compress_backup
总用量 5.3M
-rw-r--r-- 1 nangy nangy  353 8月  14 10:14 employees.employees-schema.sql.gz
-rw-r--r-- 1 nangy nangy 5.3M 8月  14 10:14 employees.employees.sql.gz
-rw-r--r-- 1 nangy nangy  136 8月  14 10:14 employees-schema-create.sql.gz
-rw-r--r-- 1 nangy nangy  133 8月  14 10:14 metadata

仅备份数据

  可以使用--no-schemas选项来跳过schema并且仅备份数据：

# nangy @ nangy-vm in ~ [10:15:49] 
$ mydumper -u  root --password='{password}' -B employees -T employees -t 8 --no-schemas --trx-consistency-only --outputdir /data/backups/no-schemas_backup

** (mydumper:11187): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.

# nangy @ nangy-vm in ~ [10:18:12] 
$ ll /data/backups/no-schemas_backup
总用量 17M
-rw-r--r-- 1 nangy nangy 17M 8月  14 10:18 employees.employees.sql
-rw-r--r-- 1 nangy nangy 133 8月  14 10:18 metadata