网站目录结构图网站开发交接清单

开启binlog后，如何使用binlog的功能，部署复制架构，通过Replication来实现读写分离、高可用呢?

一. 配置复制的方式

1.1 位点

通过指定主库的二进制日志的当前位点来进行数据的同步，通过change master to命令进行配置。 位点怎么获取呢？

1. 在主库执行show master status

   1. File、Position列的信息指出了当前的二进制文件和位置信息

2. 执行mysqldump --master-data=1/2记录下change master to信息

3. 使用mydumper工具进行逻辑备份，查看metadata文件

   1. v0.9.5还不支持mysql 8.0的caching_sha2_password认证插件，需要改成mysql_native_password认证插件

4. 使用percona-xtrabackup工具进行物理备份，查看备份目录下的xtrabackup_binlog_info文件，在从库上的备份(使用--slave-info选项，并行复制环境需要MySQL开启GTID)查看备份目录下的xtrabackup_slave_info文件

获取到位点后执行change master to命令：

change master to \master_host='',\master_user='xx',\master_password='xx',\master_port=,\master_log_file='',\master_log_pos=<binlog position>;

基于位置点的方式，如出现错误(在5.6版本之前常见1032找不到行、1062 主键冲突)可以通过设置sql_slave_skip_counter跳过事务(事务表，非事务表是指事件)或通过change master to语句重新指定。

1.2 GTID

5.6 开始支持GTID(Global Transaction Identifier)，全局事务标识符由UUID(实例的唯一标识，存储在数据目录的auto.cnf文件中)和sno(单调递增的序号)组成，用来唯一标识一个事务，一个事务的GTID在所有复制集群中都是一致和唯一的。

通过使用GTID，减少了用位点建立复制的困难性，如用位点时，当原master发生切换后，其余的slave要跟新master建立复制关系，需要确定位点就比较繁琐，如使用GTID模式，都不需要关心位点，通过主从自动定位(MASTER_AUTO_POSITION)就可以知道差异的事务。

基于GTID，如发生错误时，可以用空事务的方式跳过事务(有损方式，依情况而定，为了避免数据的一致性，如出现从库数据异常，建议重新搭建从库，或结合pt-table-checksum、pt-table-sync工具进行主从数据校验和差异数据补偿)：

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';BEGIN;COMMIT;SET GTID_NEXT='AUTOMATIC';

二. 如何保障复制的安全?

什么是复制安全？当slave挂掉后能够重新从挂掉的位置开始进行事件的拉取和应用，即需要保障IO线程和SQL线程的安全；同时保证redo安全写入磁盘，保障innodb存储引擎的持久性。IO线程从主库接收事件，然后将事件写入到relay log，同时也会记录位置信息，写入到master.info文件，由参数sync_master_info控制fsync操作，为0时将由OS进行刷新。SQL线程从relay log读取事件，并进行应用，同时记录位置信息，写入到relay-log.info文件，由参数sync_relay_log_info控制fsync操作，为0时将由OS进行刷新。在5.5时，位置点信息都是依赖文件进行存储，如果设置 sync_master_info=1和 sync_relay_log_info=1因每次写一个事务就要将位置信息写入磁盘，造成性能很差， 如果从库发生crash，SQL线程可能从relay log读取已经应用过的事件，引起主从中断，同时如表无唯一键时还会造成主从数据不一致的问题。5.6开始，支持将位置信息存储到数据库里面，通过参数master_info_repository=TABLE和relay_log_info_repository=TABLE进行控制，分别存储在 mysql.slave_master_info和slave_relay_log_info表中，利用事务的特性，解决了将位置信息存储在文件中，位置跟事务不一致的问题(主要是解决SQL线程，IO线程因是写relay log文件还是会存在不一致)，同时参数relay_log_recovery=1可以在slave crash时，让IO线程从SQL线程的位点开始重新拉取事件，写入到新的relay log，SQL线程从其位点进行事件的应用。使用GTID时，通过Executed_gtid_set(变量gtid_executed)判断跟主库的差异，不依赖relay log进行恢复，在5.6时，因变量gtid_executed是内存存储，需要依赖binlog进行GTID信息的获取，5.7开始使用表(mysql.gtid_executed)存储GTID信息，不需要依赖binlog进行恢复。gtid_executed表结构如下：

当使用并行复制时，多个worker线程并行进行事件的应用，可能会出现后写入的事件在从库先执行，如果从库发生crash，将会出现gap，5.7使用参数slave_preserve_commit_order=1，slave_parallel_type=LOGICAL_CLOCK避免gap发生。5.7.28开始，如果使用GTID模式，slave的恢复也不再依赖relay log，就只需要保证GTID信息的一致性，就可以保障slave crash安全，而GTID信息已经存储到了表中，即只要保证事务的持久性，设置双1(sync_binlog=1,innodb_flush_log_at_trx_commit=1)。

小结下，相关各版本保证slave crash安全的配置：

三. 自动化工具

在面临一主多从，以及从备份中进行数据恢复，进行扩容或备份恢复效验，怎么实现自动化的进行主从环境配置呢？我们可以通过编写各种脚本，实现自己所需的功能，同时我们也可以使用现成的工具，如MySQL Utilities的mysqlreplicate，现在已经没更新了，使用MySQL Shell，8.0.22 也开始支持异步复制自动FAILOVER机制，当复制源节点发生错误时，可以自动地从另一个复制源进行复制，有没有发现借鉴了MongoDB的特性 。自己编写工具时，如用Python Django编写一个视图，提供一个POST的接口(可提供用户名、密码、位点等信息)，结合celery进行任务队列调度；测试环境可以使用DBdeployer工具来快速的搭建一个环境，其支持各种版本和复制架构的部署。

四. 监控

通过show slave status的Seconds_Behind_Master值来监控延迟，Slave当前时间戳和正在处理事件记录的时间戳之间的差值，实际是测量的SQL线程和I/O线程的时间差值，如果网络慢，I/O线程慢就不准确了(如从监控上看突然一个小尖刺)。通过pt-heatbeat进行心跳监控，默认每秒通过往主库一张表里面更新时间戳，对比主从时间戳的差值来判断延迟，相对于SBM值来说更精确。心跳表的表结构：

CREATE TABLE `heartbeat` (  `ts` varchar(26) NOT NULL,  `server_id` int(10) unsigned NOT NULL,  `file` varchar(255) DEFAULT NULL,  `position` bigint(20) unsigned DEFAULT NULL,  `relay_master_log_file` varchar(255) DEFAULT NULL,  `exec_master_log_pos` bigint(20) unsigned DEFAULT NULL,  PRIMARY KEY (`server_id`)) ENGINE=InnoDB;

监控命令：

pt-heartbeat \-D test --update \--create-table \-h 127.0.0.1 \-P 3307 \--daemonize

-D指定存放心跳表的db名称，--create-table当心跳表不存在时进行自动的创建，--daemonize放置后台进行持续的心跳表更新；

使用以下命令查看延迟(连接到从库)，或其他orzdba工具，mysqld_exporter也支持该工具进行从库延迟的监控：

pt-heartbeat \-D test \--monitor \-h 127.0.0.1 \-P 3307

使用Prometheus的mysqld_exporter进行主从延迟的监控，需要给账号授权process,select(查询pt-heartbeat心跳表),replication slave,replication client；

启动命令：

mysqld_exporter \--collect.heartbeat.database="test" \--collect.heartbeat \--config.my-cnf="/root/.my.cnf"

连接数据库的用户名和密码信息写在/root/.my.cnf文件(安全性有点弱，设置好文件的权限)，如：

[client]user=password=host=127.0.0.1port=3307

关于主从延迟的监控项是：mysql_slave_lag_seconds，当使用心跳表时，延迟监控项为mysql_heartbeat_lag_seconds。

# Record slave lag seconds for pre-computed timeseries that takes# `mysql_slave_status_sql_delay` into accountmysql_slave_lag_seconds = mysql_slave_status_seconds_behind_master - mysql_slave_status_sql_delay# Record slave lag via heartbeat methodmysql_heartbeat_lag_seconds = mysql_heartbeat_now_timestamp_seconds - mysql_heartbeat_stored_timestamp_seconds

IO和SQL线程状态的监控是：mysql_slave_status_slave_io_running，mysql_slave_status_slave_sql_running。

五. 总结

Replication是MySQL高可用和读写分离的一个非常重要的机制，为了保障主从的高可用，需要确保主从库都设置为双1，同时使用GTID模式，同时避免各个版本的差异，在5.7版本开始，推荐配置如下(从库开启binlog，是在高可用环境下，从库也可能切换为主库)：

server_id = 100 #复制集内实例保证唯一relay_log_recovery = ON #IO线程安全relay_log_info_repository = TABLE #SQL线程安全binlog_format=ROWlog-bin=mysql-binlog_slave_updates = 1gtid_mode = 1enforce_gtid_consistency = 1loose_slave_preserve_commit_order = ONsync_binlog = 1innodb_flush_log_at_trx_commit = 1# 5.7.22之后版本loose_slave-parallel-type=LOGICAL_CLOCKloose_slave-parallel-workers=8loose_transaction_write_set_extraction=XXHASH64loose_binlog_transaction_dependency_tracking=writeset

全文完，辛苦你的眼睛，做个眼保健操吧

---

如果文章对您有帮助，请友情帮转发+点赞哦！