mysql主从binlog日志清理 mysql基于binlog的主从复制机制

转载

mob6454cc6c6291 2024-01-12 11:46:03

文章标签 mysql主从binlog日志清理 mysql SQL 数据 文章分类 MySQL 数据库

一、MySQL主从复制介绍

主从复制是MySQL自带的一种横向扩展方案，通过“数据变更在主库执行，查询请求在从库执行”这样的形式可以在一定程度上实现读写分离。如果配置了无损复制，从库一定程度上还可以当做备份使用，甚至配置一台节点为延迟从库。有了主从复制后，还可以实现服务滚动升级（先升级从库进行测试，然后手动切换主从后升级其他从库）。主从结构通常可以分为一主一从、一主多从、级联复制等，如果是一主多从的架构需要注意从服务器的数量不能太多，否则会因为日志的传输给主库带来过多的带宽消耗（曾遇到主库网卡异常导致从库一直卡住的原因，当时一直思考的是大事务原因，结果是硬件导致）

二、Binlog日志介绍

传统的主从复制依赖于MySQL binlog实现。主节点通过Binlog记录了数据库所有DDL与DML操作（SELECT、SHOW等语句不对数据产生变化，不会有记录，另外临时表也不会有记录），从库将binlog日志复制到自己的中继日志Relaylog中，然后根据Relaylog做相同的操作，实现了数据的同步。

1、binlog写入机制

事务执行过程中先把日志写到binlog cache中，等事务提交的时候再把cache写到binlog文件fsync落盘并清空cache，这一点和redolog很像。一个事务的 binlog不能被拆开，不论事务多大也要确保一次性写入binlog。每个线程都会申请一块binlog cache，内存空间大小由参数 binlog_cache_size控制。如果超过参数设置的大小就要暂存到磁盘。而write和fsync 的时机由参数sync_binlog决定（双1参数之1）

2、binlog格式介绍

statement（标准模式，简称SBR）：保存的是每一条修改数据的SQL语句，然后在从服务器上会执行相同的语句来同步数据。优点是日志记录量比较少，缺点是数据一致性不是最高级别
row（行模式，简称RBR）：最安全的复制模式，将数据的具体改变记录在日志中，日志量大，不会因为某些会产生随机数的SQL而让主从数据不一致（比如包含了时间函数的语句）。row格式的二进制日志无法直接阅读，需要通过mysqlbinlog转码
mixed（混合模式，简称MBR）：statement和row的结合模式，当基于语句无法精确复制时，就会采用行模式进行复制。实际上比较鸡肋，不考虑使用

delete * from test; #假设这条SQL语句删除了100万条数据
# STATEMENT语句模式仅记录这一条SQL语句
# ROW行模式会记录执行删除时的每一个语句，会记录100万次

三、MySQL主从复制配置

在部署主从架构前先确保主从节点的MySQL版本一致、时间一致

mysql主从binlog日志清理 mysql基于binlog的主从复制机制_数据

1、binlog主从相关配置选项（由于从库也随时有可能提升为主，所以建议都配成一样的）

[mysqld]
 basedir = /usr/local/mysql
 datadir = /mysqldata
 port = 3306
 socket = /tmp/mysql.sock
 server_id = 1  #主从节点不能相同，可以避免双主架构下的循环复制问题，该ID最大值为4294967295
 innodb_file_per_table = ON

### log set ###
 log_bin = /data/mysql/log/master-bin    #binlog日志路径与日志文件前缀
 log_bin_index = /data/mysql/log/master-bin.index  #binlog索引文件
 log_bin_trust_function_creators = 1  #默认为0，开启后可以同步函数和存储过程
 binlog_format = row  #推荐用row模式
 binlog_rows_query_log_events = on  #将原始sql语句加上注释记录到日志中，否则binlog显示的是数据前后的状态而不能看到原始SQL
 expire_logs_days = 10  #二进制日志过期天数，通常设置为一个全备周期+1天，比如每周全备一次，这个时间就设置为8
 max_binlog_size = 1024M  #二进制日志大小，如果一个SQL事务产生的数据超过该限制，则突破该限制
 binlog_cache_size = 2M  #二进制日志缓存区大小，默认32K，建议1-4M
 sync_binlog = 1 #binlog buffer刷盘策略。1代表每次提交事务之前将二进制日志同步到硬盘；N为有N次commit的时候才写入磁盘；0为关闭
 innodb_flush_log_at_trx_commit=1  #默认为1，每次事务提交时会把buffer数据写入磁盘

### 主从建议必配选项 ###
 master_info_repository = TABLE  #将主库信息保存到mysql库里而非默认的master-info文件中，可以减少IO开销并保证一致性 
 relay_log_info_repository = TABLE  #同上
 log_slave_updates  = 1  #从库也记录binlog，可以实现级联复制以及起到binlog备份作用，如果从库只配置了log_bin而没有开启该选项，则binlog内容为空。不承载高可用的从库可以关闭节约IO性能
 relay_log_recovery = 1  #如果从库IO线程崩溃并且relaylog损坏，则放弃所有未执行的relaylog，重新从master获取日志保持完整

### 从库开启 ###
# read_only = 1
# super_read_only = 1
# relay-log = relay-log  #中继日志路径和文件名，默认情况下relaylog会在SQL线程执行完毕后自动删除.需注意主机名一旦更改主从就会出错
# relay-log-recover = 1  #打开中继日志恢复模式，保证数据一致性。如果从库宕机导致当前中继日志损坏的话，就放弃该日志并重新从master上获取
# slave_parallel-type = LOGICAL_CLOCK  #并行复制方式，默认database基于库进行并行复制，要求修改的数据是不同库才能并行，而LOGICAL_CLOCK基于事务实现并行复制，可以减少从库延迟
# slave_parallel_workers = 8  #该值为0代表单线程复制，大于1时表示启用多线程复制。该选项值可动态设置，但重启复制线程才会生效。通常和CPU核数一致，可以提升从库复制效率，一定程度上解决从库延迟问题。并行复制开启后通过processlist看到状态为system lock的system user用户就是并行复制的线程
# slave_preserve_commit_order = 1 #多线程复制建议开启，保证从库回放事务的顺序与主库顺序一致
# skip_slave_start  #从库mysql启动的时候是否自动start slave

2、主库建立一个账号并给与数据复制权限

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.36.11' IDENTIFIED BY '123456789';

3、查看主库当前的position信息，从库会从该位置点进行复制

mysql > show master status \G

4、从库使用主库上配置好的复制用户，并且指定好主库的position信息

stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.145.85',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=0;

#MASTER_HOST：MASTER服务器的IP
#MASTER_USER：有slave权限的用户，就是GRANT所授权的用户
#MASTER_PASSWORD：从库用户的密码
#MASTER_LOG_FILE：在主库上执行show master status语句可以查看日志名
#MASTER_LOG_POS=333：这个位置决定了从库从哪个位置开始复制，实测写0也可以，从头复制

5、启动从库复制线程，需要为两个yes

mysql > start slave;  #启动从库
mysql > show slave status \G  #查看从库状态

四、主从状态查看

1、查看主库Position信息与从库状态

show master status \G #查看主库binlog position信息
show slave status \G   #查看从库状态

2、从库状态关键指标

· Read_Master_Log_Pos：从库读取到主库二进制日志到哪个位置
· Exec_Master_Log_Pos：从库执行到主库二进制日志到哪个位置。如果和Read_Master_Log_Pos一致代表数据是同步的
· Relay_Log_File：从库会将binlog记录到中继日志中，这里标识了当前中继日志是哪一个
· Relay_Master_Log_file：当前中继日志文件所对应的binlog日志文件
· Seconds_behind_master：从服务器比主服务器慢了多少秒，为0代表没有延迟。这里的计算方式为“从库当前系统时间-从库SQL线程正在执行的事务时间戳-主从节点之间的系统时间差”。如果在复制过程中修改了系统时间会导致主从延迟不可靠的问题，除非重启IO线程
· Slave_IO_Running、Slave_SQL_Running：如果有一项为No都表示主从异常。IO Thread的作用是从Master端请求二进制日志并存放到Slave端的中继日志中；SQL Thread的作用是将中继日志里的事件导入到SQL语句中，下图是启动slave之前的状态：

mysql主从binlog日志清理 mysql基于binlog的主从复制机制_mysql_02