MySQL Replication

一、配置

https://dev.mysql.com/doc/refman/8.0/en/replication-options-reference.html

MySQL 复制过滤详解：https://developer.aliyun.com/article/59268

master

[mysqld]
server-id = 1

#log_bin = on # 8 版本默认启用，5 版本需要手动开启
#log_bin_basename = binlog
#log-bin = binlog
#log-bin-index = binlog.index
# 建议在 slave 端做过滤，避免影响 master 端日志文件完整性
#binlog-do-db = test_table # 写入日志
#binlog-ignore-db = mysql,sys,performance_schema,information_schema # 不写入日志

slave

[mysqld]
server-id = 2
super_read_only = on # 禁止手动 CURD，开启后不影响主从同步

# 不需要同步的库和表，显式配置后，未配置的库表将会被同步
# replicate-ignore-db = mysql
# replicate-wild-ignore-table = mysql.%
# replicate-ignore-db = sys
# replicate-wild-ignore-table = sys.%
# replicate-ignore-db = performance_schema
# replicate-wild-ignore-table = performance_schema.%
# replicate-ignore-db = information_schema
# replicate-wild-ignore-table = information_schema.%

# 需要同步的库，不配置表示同步所有
replicate-wild-do-table = db_a.%
replicate-wild-do-table = db_b.%
# replicate-do-db = db_a
# replicate-do-db = db_b
# 需要同步的表
# replicate-do-table = db_a.table_a
# replicate-do-table = db_a.table_b

 

二、开启同步

在此之前需要保证，不需要同步之外的库表完全一致，否则会同步失败

master

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-repuser.html

-- 创建同步账户，不推荐直接使用 root
DROP USER repl;
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看 master 服务器状态，File 和 Postion 对应的值要记录下来，下面要用到
SHOW MASTER STATUS;
-- 查看从节点
SHOW SLAVE HOSTS;

slave

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slaveinit.html

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-additionalslaves.html

-- 停止同步
STOP SLAVE;

-- 配置主节点信息，MASTER_LOG_FILE 对应 File，MASTER_LOG_POS 对应 Postion
CHANGE MASTER TO MASTER_HOST = '127.0.0.1',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'binlog.000009',
MASTER_LOG_POS = 1237;

-- 开启同步
START SLAVE;

-- 查看状态，Slave_IO_Running 和 Slave_SQL_Running 必须为 YES
SHOW SLAVE STATUS;

 

查看信息

https://dev.mysql.com/doc/refman/8.0/en/replication-threads-monitor-main.html

SHOW VARIABLES LIKE '%server_id%';
SHOW VARIABLES LIKE '%log_bin%';
SHOW VARIABLES LIKE '%server_uuid%';
SHOW VARIABLES LIKE '%datadir%';

SHOW PROCESSLIST;
SHOW BINLOG EVENTS;

 

canal 方案：https://github.com/alibaba/canal

https://github.com/alibaba/canal/blob/master/admin/admin-web/src/main/resources/canal_manager.sql
默认 admin 123456

docker run -d -it \
-e server.port=8089 \
-e canal.adminUser=admin \
-e canal.adminPasswd=admin \
-e spring.datasource.address=10.74.2.71:3306 \
-e spring.datasource.database=canal_manager \
-e spring.datasource.username=root \
-e spring.datasource.password=root \
-p 8089:8089 \
--name=canal-admin -m 1024m canal/canal-admin

docker run -d -it \
-e canal.admin.manager=10.74.2.71:8089 \
-e canal.admin.user=admin \
-e canal.admin.passwd=4ACFE3202A5FF5CF467898FC58AAB1D615029441 \
-e canal.admin.port=11110 \
-e canal.port=11111 \
-e canal.metrics.pull.port=11112 \
-p 11110:11110 -p 11111:11111 -p 11112:11112 \
--name=canal-server -m 4096m canal/canal-server

 

三、主从复制原理

主从数据同步是基于 binlog 进行的。

在主从复制过程中，会基于三个线程来操作：一个主库线程，两个从库线程。

1. 二进制日志转储线程(Binlog dump thread)是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件(Event)的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
2. 从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志(Relay log)。
3. 从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制的最大问题是有延时。

 

四、日志

general query log(通用查询日志)

通用查询日志用来记录用户的所有操作 ，包括启动和关闭 MySQL 服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助定位问题。

-- 查看当前状态
SHOW VARIABLES LIKE '%general%';
-- general_log 状态
-- general_log_file 日志文件名称

-- 启动日志
-- [mysqld]
-- general_log=ON
-- general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名
-- 果不指定目录和文件名，通用查询日志将默认存储在 MySQL 数据目录中的 hostname.log 文件中，hostname 表示主机名。
SET GLOBAL general_log=on; -- 开启通用查询日志
SET GLOBAL general_log_file=’path/filename’; -- 设置日志文件保存位置

-- 删除\刷新日志
-- mysqladmin -uroot -p flush-logs

error log(错误日志)

在 MySQL 数据库中，错误日志功能是默认开启的。而且错误日志无法被禁止 。默认错误日志存储在 MySQL 数据库的数据文件夹下，名称默认为 mysqld.log(Linux) 或 hostname.err(Mac)。

SHOW VARIABLES LIKE 'log_err%';

-- 配置路径
-- [mysqld]
-- log-error=[path/[filename]] # path为日志文件所在的目录路径，filename 为日志文件名

-- 删除\刷新日志
-- https://dev.mysql.com/doc/refman/8.0/en/log-file-maintenance.html
-- mv /var/log/mysqld.log /var/log/mysqld.log.old
-- install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
-- mysqladmin -uroot -p flush-logs # 5.5.7 之前会自动备份为 filename.err_old

bin log(二进制日志)

binlog 即 binary log，二进制日志文件，也叫作变更日志(update log)。以事件形式记录数据库执行的所有 DDL 和 DML 等数据库更新事件的语句，不包含没有修改任何数据的语句(select、show 等)。binlog 主要应用于数据恢复和数据复制。

设置

SHOW VARIABLES LIKE '%log_bin%';

-- [mysqld]
-- log-bin="/var/lib/mysql/binlog/my-bin" # 启用二进制日志
-- binlog_expire_logs_seconds=600
-- max_binlog_size=100M
-- chown -R -v mysql:mysql binlog # 新建文件夹需要使用 mysql 用户

-- 不希望通过修改配置文件并重启的方式设置二进制日志
-- SET GLOBAL sql_log_bin=0; -- 在 mysql8 中只有 session 级别的设置，没有 global 级别
SET sql_log_bin=0;

当 MySQL 创建二进制日志文件时，先创建一个以 filename 为名称，以 .index 为后缀的文件。再创建一个以 filename 为名称，以 .000001 为后缀的文件。

MySQL 服务重新启动一次，以 .000001 为后缀的文件就会增加一个，并且后缀名按 1 递增。即日志文件个数与 MySQL 服务启动的次数相同。

如果日志长度超过了 max_binlog_size 的上限(默认1GB)，就会创建一个新的日志文件。

查看

SHOW BINARY LOGS; -- 查看当前的二进制日志文件列表及大小

mysqlbinlog

# 将行事件以伪 SQL 的形式查看
mysqlbinlog -v "/var/lib/mysql/binlog/my-bin.000002"
# 不显示 binlog 格式的语句
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/my-bin.000002"、
# 查看帮助
mysqlbinlog --no-defaults --help
# 查看最后 100 行
mysqlbinlog --no-defaults --base64-output=decode-rows -v my-bin.000002 | tail -100
# 根据 position 查找
mysqlbinlog --no-defaults --base64-output=decode-rows -v my-bin.000002 | grep -A 20 '4939002'

mysqlbinlog 读取出 binlog 日志的全文内容比较多，不容易分辨查看到 pos 点信息。可以用 SHOW 查看。

-- SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
-- IN 'log_name'：指定要查询的 binlog 文件名(不指定就是第一个 binlog 文件)
-- FROM pos：指定从哪个 pos起始点开始查起(不指定就是从整个文件首个 pos 点开始算)
-- LIMIT [offset]：偏移量(不指定就是 0)
-- row_count：查询总条数(不指定就是所有行)

SHOW BINLOG EVENTS IN 'my-bin.000002';

binlog 格式

SHOW VARIABLES LIKE 'binlog_format';

Statement：每一条会修改数据的 sql 都会记录在 binlog 中。优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了IO，提高性能。

Row：5.1.5 版本的 MySQL 才开始支持 row level 的复制，它不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。优点：日志内容非常清楚的记录下每一行数据修改的细节。且不会出现某些情况下的存储过程或 function 或 trigger 的调用和触发无法被正确复制的问题。

Mixed：从 5.1.8 版本开始，MySQL 提供了 Mixed 格式，是 Statement 与 Row 的结合。

使用 binlog 恢复数据

mysqlbinlog [option] filename | mysql –uuser -ppass;

这个命令可以这样理解：使用 mysqlbinlog 命令来读取 filename 中的内容，然后使用 mysql 命令将这些内容恢复到数据库中。

• filename：日志文件名。
• option：可选项，比较重要的两对参数：--start-date 和 --stop-date：可以指定恢复数据库的起始时间点和结束时间点。--start-position 和 --stop-position：可以指定恢复数据的开始位置和结束位置。

注意：使用 mysqlbinlog 命令进行恢复操作时，必须是编号小的先恢复，例如 my-bin.000001 必须在 my-bin.000002 之前恢复。

通过数据库全量备份和 binlog 中的增量信息可以完成数据库的无损失恢复。但是，如果遇到数据量大，库表很多(比如分库分表的应用)的场景，用 binlog 恢复数据是很有挑战性的，因为起止位置不容易管理。

在这种情况下，一个有效的解决办法是配置主从，甚至是一主多从，把 binlog 的内容通过 relay log(中继日志)，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。

删除

MySQL 的二进制文件可以配置自动删除，同时 MySQL 也提供了安全的手动删除的方法。

RESET MASTER; -- 删除所有二进制日志文
-- PURGE MASTER LOGS xxx 删除指定部分之前的二进制日志文件
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名';
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期';

写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候再把 binlog cache 写到 binlog 文件中。因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入。

系统会给每个线程分配一个块内存作为 binlog cache。

write(binlog cache 写到 binlog (文件系统缓存page cache)文件) 和 fsync(文件系统缓存写到磁盘) 的时机，可以由参数 sync_binlog 控制，默认是 0。

为 0 时，表示每次提交事务都只 write，由系统自行判断什么时候执行 fsync。虽然性能得到提升，但是机器宕机，page cache 里面的 binglog 会丢失。

为安全起见，可设为 1，表示每次提交事务都执行 fsync，如同 redo log 刷盘流程一样。还有一种折中方式，可设为 N(N>1)，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，则会丢失最近 N 个事务的 binlog 日志。

与 redo log 区别

redo log 是物理日志，记录内容是在某个数据页上做了什么修改，属于 InnoDB 存储引擎层产生的。

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于给 ID=2 这一行的 c 字段加 1，属于 MySQL Server 层。

两阶段提交

在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以事务为基本单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。

写入时机不一样会导致 redo log 与 binlog 之间的逻辑不一致。假如 binlog 没写完就异常，这时 binlog 里面就没有对应的修改记录。为了解决日志逻辑一致问题，InnoDB 使用了两阶段提交方案。

两阶段提交就是将 redo log 的写入拆成了两个步骤 prepare 和 commit。

写入 binlog 时发生异常，MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。

redo log commit 时发生异常，它会执行框住的逻辑，虽然 redo log 是处于 prepare 阶段，但是能通过事务 id 找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

relay log(中继日志)

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件 中，这个从服务器本地的日志文件就叫中继日志。

然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步 。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式是： 从服务器名 -relay-bin.序号。中继日志还有一个索引文件：从服务器名-relaybin.index，用来定位当前正在使用的中继日志。

 

---

https://dev.mysql.com/doc/refman/8.0/en/replication.html & https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

https://blog.jcole.us/innodb & https://github.com/jeremycole/innodb_diagrams