Binlog, mysqldump, mydumper/myloader, xtrabackup

这份文档记录 binlog, mysqldump, mydumper/myloader, xtrabackup 四大模块。欢迎大家在评论区畅所欲言 - wayne

一、binlog

binlog 记录了所有的 DDL (Data Definition Language) 和 DML (Data Manipulation Language) 语句，以事件的形式记录，也包含了可能导致修改的事件（例如，a DELETE which matched no rows），除非使用 row-based 模式。它还包含了语句所执行的消耗时间，binlog 是事务安全型的。binlog 存在有以下两个目的：

• 对于复制，在 Master 端开启 binlog，Master 把它的 binlog 传递给 Slave 来达到数据一致性。
• 数据恢复操作需要 binlog。恢复备份后，将重新执行备份后记录的 binlog 的事件。这些事件使数据库从备份点开始更新。

binlog 不用于 select 或 show 等不修改数据的语句。要记录所有的语句（例如，识别有问题的查询），一般使用 general query log.

mysqld 会根据文件名、自增数据创建一系列有序的 binlog 文件名，每次重启服务器、刷新日志、binlog 文件大小达到阈值会创建一个新的 binlog 文件。有可能 binlog 文件会比 max_binlog_size 大，因为零界点一次使用大型事务时不会拆分成2个 binlog 文件。 show variables like 'max_binlog_size';

启动 binlog 的服务器性能会稍微变慢。但是 binlog 能够设置复制和恢复方面的操作通常超过这种轻微的性能下降。

binlog 对意外停止具有弹性。仅记录或回读完整的事件或事物。

写入 binlog 的语句中的密码由服务器重写，不会以纯文本的形式出现。

二进制日志包括两类文件：二进制日志索引文件 (.index) 用于记录所有的二进制文件，二进制日志文件 (.00000*) 记录数据库所有 DDL 和 DML 语句事件。

1、binlog formats

• Statement-based logging. 基于 SQL 语句的同步
  优点：数据量比较小 (尤其是批量 SQL 更新情况下)
  缺点：同样的 where 条件某些场景下主从执行的结果可能不一致。
• Row-based logging. 单个表行如何受到影响
  优点：数据一致性略好
  缺点：数据量会比较大，特别是批量更新
• mixed logging.
  默认使用 statement-based logging，但在某些情景下会自动转换到 row-based logging.

注：MySQL5.7 ROW 多了一个参数 binlog_row_image [full|minimal], minimal 模式下只记录影响后的行。 show variables like 'binlog_row_image';

2、binlog 常用的操作

# 查看所有 binlog 日志列表
mysql> show master logs;

# 查看 master 状态
mysql> show master status;

# 刷新日志，自此刻开始产生一个新编号的 binlog 文件
mysql> flush logs;

# 重置所有 binlog 日志
mysql> reset master;

# 系统变量的全局值，所有客户端 binlog_format 都会更改 [注意权限]
mysql> set global binlog_format = 'STATEMENT';

# 单个客户端 binlog_format 更改
mysql> set session binlog_format = 'ROW';

# 查看 binlog 二进制文件 eg. show binlog events in 'mysql-bin.000001' from 1212 limit 2,10
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

3、binlog 内容

mysql/binlog_event.h

1. QUERY_EVENT
   QUERY_EVENT 以文本的形式来记录事务的操作。通常在以下几点情况下使用：
   1.事务开始时，执行的 BEGIN 操作
   2.STATEMENT 格式中的 DML 操作
   3.ROW 格式中的 DDL 操作

# BEGIN 操作
| mysql-bin.000001 |  672 | Query          |      3000 |         770 | BEGIN
# STATEMENT 格式的 DML 操作
| mysql-bin.000001 |  233 | Query          |      3000 |         365 | create database wayne_binlog /* xid=294 */
| mysql-bin.000001 |  770 | Query          |      3000 |         915 | use `wayne_binlog`; insert into binlog_test1 values('binlog'), ('wayne')
# ROW 格式中的 DDL 操作

2. FORMAT_DESCRIPTION_EVENT
   FORMAT_DESCRIPTION_EVENT 是 binlog version 4中为了取代之前版本中的 START_EVENT_V3 事件而引入的。它是 binlog 文件中的第一个事件，并且只会在 binlog 文件中出现一次。MySQL 根据 FORMAT_DESCRIPTION_EVENT 的定义来解析其他时间。
   它通常指定了 MySQL server 的版本，binlog 的版本，和 binlog 的创建时间。

# at 4
#211201 14:52:04 server id 3000  end_log_pos 125 CRC32 0xd2229017       Start: binlog v 4, server v 8.0.27 created 211201 14:52:04 at startup

mysql-bin.000001 |    4 | Format_desc    |      3000 |         125 | Server ver: 8.0.27, Binlog ver: 4

3. ROWS_EVENT
   对于 ROW 格式的 binlog，所有 DML 语句都是记录在 ROWS_EVENT 中。
   ROW_EVENT 分为三种：WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT。
   对于 insert 操作：WRITE_ROWS_EVENT 包含了要插入的数据
   对于 update 操作：UPDATE_ROWS_EVENT 包含了修改前后的数据
   对于 delete 操作：仅仅需要指定要删除的主键（没有主键的情况下，会给定所有的列）
   通过 mysqlbinlog 查看基于 ROW 格式的 binlog 时，需要制定 -vv --base64-output=decode-rows。
4. XID_EVENT
   在事务提交时，不管是 STATEMENT 还是 ROW，都会在末尾添加一个 XID_EVENT 事件代表事务的结束。该事件记录了事务的 ID，在 MySQL 进行崩溃恢复时，根据事务在 binlog 中的提交情况来决定是否提交存储引擎中状态为 prepared 的事务。

| mysql-bin.000001 |  672 | Query          |      3000 |         770 | BEGIN                                                                              
| mysql-bin.000001 |  770 | Query          |      3000 |         915 | use `wayne_binlog`; insert into binlog_test1 values('binlog'), ('wayne')           
| mysql-bin.000001 |  915 | Xid            |      3000 |         946 | COMMIT /* xid=301 */                                                               

5. ROTATE_EVENT
   当 binlog 文件大小达到 max_binlog_size 或者执行 flush logs 命令时，binlog 会发生切换，这个时候会在当前 binlog 添加一个 ROTATE_EVENT 事件，用于指定下一个 binlog 的名称和位置。
6. GTID_LOG_EVENT
   在启用 GTID 模式后，MySQL 为每个事务都分配了 GTID
7. PREVIOUS_GTIDS_LOG_EVENT
   启用了 GTID 模式后，每个 binlog 开头都会有一个 PREVIOUS_GTIDS_LOG_EVENT 事件。它的值是上一个 binlog 的 PREVIOUS_GTIDS_LOG_EVENT + GTID_LOG_EVENT。
   数据库重启的时候，需要重新填充 gtid_executed 的值，该值即是最新一个 binlog 的 PREVIOUS_GTIDS_LOG_EVENT + GTID_LOG_EVENT。
8. STOP_EVENT
   当 MySQL 数据库停止时，会在当前 binlog 末尾添加一个 STOP_EVENT 事件表示数据库停止。

二、mysqldump

mysqldump 的实战没什么可展示的，大家去网上搜就好。
下面更新我最新：
mysqldump源码解读 - 到底是怎样dump的？
mysqldump where子句使用

三、mydumper/myloader

环境： centOS7
安装依赖

yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake

下载安装包

wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz

解压安装

tar zxvf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1/
cmake .
make
make install

测试安装

mydumper -V

备份

# 创建 /root/mydumper_test 目录
cd ~
mkdir mydumper_test

# 备份
mydumper -u root -p ** -B dump_db -o /root/mydumper_test/

-B 要备份的数据库名称， -o 输出到目录
/root/mydumper_test/ 下生成的文件

total 24
-rw-r--r-- 1 root root 195 Dec  3 15:18 dump_db.dump_tb-schema.sql
-rw-r--r-- 1 root root 179 Dec  3 15:18 dump_db.dump_tb.sql
-rw-r--r-- 1 root root 265 Dec  3 15:18 dump_db.people-schema.sql
-rw-r--r-- 1 root root 169 Dec  3 15:18 dump_db.people.sql
-rw-r--r-- 1 root root 131 Dec  3 15:18 dump_db-schema-create.sql
-rw-r--r-- 1 root root 136 Dec  3 15:18 metadata

db.tb.sql 表数据sql
db.tb-schema.sql 表结构sql
db-schema-create.sql 数据库创建sql
metadata 记录了备份信息和主从信息

还原

myloader -u root -p *** -B dump_wayne --verbose=3 -d /root/mydumper_test/

-B 要还原的数据名称
--verbose=3 输出info级别，默认是2warnings
-d 用来恢复的文件夹

mydumper 备份原理

1、主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，保证数据的一致性
2、读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3、N个（默认是4）dump线程把事务隔离级别改为可重复读 并开启事务
4、dump non-InnoDB tables, 首先导出非事物引擎的表
5、主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁
6、dump InnoDB tables, 基于事物导出InnoDB表
7、事物结束

四、xtrabackup

环境：centOS7, MySQL8.0.27, xtrabackup8.0
安装 xtrabackup

# 确保 EPEL 源
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

yum - y install libev

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 安装最新版的xb, xb24不支持 MySQL8.0 
yum -y install percona-xtrabackup-80.x86_64

# 测试一下
xtrabackup -v

# 修改 MySQL 的认证方式 - 8.0版本和5.7的认证方式不同
vim /etc/my.cnf
# 在 mysqld 部分添加下面这句
default_authentication_plugin=mysql_native_password

开始备份
在 /root 下建立 /xb_test 目录，加上 --no-server-version-check 参数，有一定风险。具体参数含义通过 xtrabackup --help 查看。

xtrabackup --user=root --password=xxxx --port=3306 --no-server-version-check --backup --target-dir=/root/xb_test

生成的文件目录

[root@VM-0-2-centos xb_test]# ll
total 69692
-rw-r----- 1 root root      478 Dec  3 10:19 backup-my.cnf
drwxr-x--- 2 root root     4096 Dec  3 10:19 dump_db
-rw-r----- 1 root root     4576 Dec  3 10:19 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec  3 10:19 ibdata1
drwxr-x--- 2 root root     4096 Dec  3 10:19 mysql
-rw-r----- 1 root root      156 Dec  3 10:19 mysql-bin.000006
-rw-r----- 1 root root       19 Dec  3 10:19 mysql-bin.index
-rw-r----- 1 root root 25165824 Dec  3 10:19 mysql.ibd
drwxr-x--- 2 root root     4096 Dec  3 10:19 performance_schema
drwxr-x--- 2 root root     4096 Dec  3 10:19 sys
-rw-r----- 1 root root 16777216 Dec  3 10:19 undo_001
-rw-r----- 1 root root 16777216 Dec  3 10:19 undo_002
drwxr-x--- 2 root root     4096 Dec  3 10:19 waynesshi_cluster_test
-rw-r----- 1 root root       21 Dec  3 10:19 xtrabackup_binlog_info
-rw-r----- 1 root root      102 Dec  3 10:19 xtrabackup_checkpoints
-rw-r----- 1 root root      520 Dec  3 10:19 xtrabackup_info
-rw-r----- 1 root root     2560 Dec  3 10:19 xtrabackup_logfile
-rw-r----- 1 root root       39 Dec  3 10:19 xtrabackup_tablespaces

恢复数据

# 停止 MySQL 服务
systemctl stop mysqld.service
# 删除 MySQL 的所有文件
rm -rf /var/lib/mysql/*

# 开始备份
xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/root/xb_test

# 权限
chown mysql.mysql -R /var/lib/mysql
# 启动 MySQL 服务
systemctl start mysqld.service

# 登录 MySQL 可以看到已经还原
mysql -uroot -p***

~~ 后续补全