本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • MySQL主从复制(1)使用Binlog配置主从与延迟从库

MySQL主从复制(1)使用Binlog配置主从与延迟从库

发布:TangLu2020-2-8 11:44分类: MySQL 标签: mysql 主从 主从复制

一、什么是MySQL主从复制

MySQL主从复制功能在一定程度上可以实现业务读写分离(为主从节点配置不同的域名或者使用中间件来实现),还可以在一定程度上当做备份使用(比如配置一台从服务器为延迟从库)。传统的主从复制依赖于MySQL的二进制日志binlog所实现。主节点上的binlog记录了每一条影响数据的SQL语句,从库将binlog日志复制到从库的中继日志relaylog中,然后从库根据relay日志中的内容再做相同的操作,实现了数据的同步。需要注意的是虽然可以做一主多从的架构,但是从服务器的数量不能太多,否则会因为日志的传输给主库带来过多的带宽消耗,我就遇到过因为主库网卡异常导致从库一直卡住的原因,当时一直思考的是大事务原因,结果是硬件导致


二、了解binlog日志的三种格式

statement(标准语句模式,简称SBR):保存的是每一条修改数据的SQL语句,然后在从服务器上会执行相同的语句来同步数据。优点是日志记录量比较少,缺点是数据一致性不是最高级别

row(行模式,推荐,简称RBR):将数据的具体改变记录在日志中,日志量大,安全性最高,不会因为某些产生随机数的SQL而让主从数据不一致,比如包含了时间函数的语句。row格式的二进制日志无法直接阅读,需要转码

mixed(混合模式,简称MBR):statement和row的结合模式,当基于语句无法精确复制时,就会采用行模式进行复制。实际上比较鸡肋,不考虑使用


看示例更容易搞懂三种模式的区别:

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


三、MySQL主从复制配置过程

1、前提工作

保证主从节点MySQL版本一致、时间一致。可以通过mysql语句select version()函数查看MySQL的版本。

mysqlzc1.png


2、Master端配置

首先修改Master端的my.cnf文件配置二进制日志等信息并重启服务

[mysqld]
 basedir = /usr/local/mysql
 datadir = /mysqldata
 port = 3306
 socket = /tmp/mysql.sock
 server_id = 1  #开启binlog的话必须配置它,建议设置为IP最后一段+端口的形式,如103306,该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索引文件
 binlog_format = row  #推荐用row模式
 binlog_rows_query_log_events = on  #将原始sql语句加上注释记录到日志中便于分析
 expire_logs_days = 10  #二进制日志过期天数,通常设置为一个全备周期+1天,比如每周全备一次,这个时间就设置为8
 max_binlog_size = 1024M  #二进制日志大小,如果一个SQL事务产生的数据超过该限制,则突破该限制
 binlog_cache_size = 2M  #二进制日志缓存区大小,默认32K,建议1-4M
 sync_binlog = 1 #默认为1,代表每次提交事务之前将二进制日志同步到硬盘,0则是关闭。如果是其他数字,代表有N次commit的时候才写入磁盘
 innodb_flush_log_at_trx_commit=1  #默认为1,每次事务提交时会把buffer数据写入磁盘
 # master_info_repository = table  #将master-info信息保存到表中,提升性能和可靠性。默认是file 


为从库建立一个拥有复制权限的账号,等会儿从库要用到该账号信息

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO slave@'192.168.36.11' IDENTIFIED BY '123456789';
FLUSH PRIVILEGES;
查看当前主库二进制日志的position位置信息,等会儿从库需要这些信息
mysql > show master status \G


3、Slave端配置

修改Slave端的my.cnf文件以配置中继日志等信息并重启服务

[mysqld]
 basedir = /usr/local/mysql
 datadir = /mysqldata
 port = 3306
 socket = /tmp/mysql.sock
 server_id = 2    #和Master的ID不一样即可
 relay-log = relay-log  #设置中继日志路径和文件名,需注意主机名一旦更改,主从就会出错
 relay-log-recover = 1  #打开中继日志恢复模式,如果从库宕机导致当前中继日志损坏的话会放弃该日志,并重新从master上获取
 read_only = 1  #从库配置只读(该配置对root用户无效),避免从库有写操作导致主从故障
 super_read_only =1  #root用户也是只读模式
 # master_info_repository = table
 # relay_log_info_repository = table  #relay-info信息写入数据库表中,默认是file
 slave_parallel-type = LOGICAL_CLOCK  #逻辑时钟,保证了同个数据库下的事务执行顺序
 slave_parallel_workers = 8  #从库并行复制的线程数
 expire_logs_days = 10
 max_binlog_size = 1024M
 log_error = error.log
 innodb_file_per_table=ON
 skip_name_resolve=ON

###多级主从复制
 #log_bin = slave-bin
 #binlog_format = row
 #log_slave_updates = on  #是否让从服务器也记录binlog,如果需要做多级主从就要打开此项



登陆Slave服务器的MySQL,配置Master服务器二进制日志的position信息,这里指定的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也可以,从头复制


启动从库,这个时候在主库更新数据后,从库也会跟着更新。通过在主从分别执行show processlist命令也可以看到主从的状态。比如主库二进制日志已经全部发送给从库,等待更新二进制日志等然后查看从库当前状态

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


4、延迟从库配置

在需要配置为延迟从库的客户端上执行以下命令

mysql > stop slave;
mysql > change master to master_delay = 3600;  #SQL线程延迟1小时,IO线程不影响
mysql > start slave;
mysql > show slave status \G  #查看SQL_Dely的值


四、主从状态查看与异常处理

1、查看主从状态

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


在查看从库状态时主要关注点:

· Read_Master_Log_Pos:从库读取到主库二进制日志到哪个位置

· Exec_Master_Log_Pos:从库执行到主库二进制日志到哪个位置。如果和Read_Master_Log_Pos一致代表数据是同步的

· Relay_Log_File:从库中继日志记录到的位置,正常情况下中继日志内容的和主库binlog是一样的

· Seconds_behind_master:从服务器比主服务器慢了多少秒,为0代表没有延迟

· Slave_IO_Running、Slave_SQL_Running:如果有一项为No表示主从工作不正常。IO Thread的作用是从Master端请求二进制日志并存放到Slave端的中继日志中;SQL Thread的作用是将中继日志里的事件导入到SQL语句中,下图是启动slave之前的状态:

mysqlzc4.png



2、使用延迟从库恢复故障的思路

如果主库执行了错误的语句(比如drop database data1;),现在需要使用延迟从库来恢复数据。首先就是在延迟从库上停止SQL线程,让它不再继续执行relay中的SQL

stop slave sql_thread;

查看延迟从库relaylog位置点,这个作为恢复的起点,这里假设为482

show slave status \G  #查看relay_log_file和relay_log_pos信息

查看延迟从库relaylog中的故障点,通常是在最后一个relaylog中,这里需要注意Position信息只需要看左边一列的,它才是relaylog中的位置,而relaylog中的End_log_pos是主库中的位置,不需要关注。这里假设DROP操作的起始点是1402。

mysql > show  relaylog events in 'relay-bin.000005';

生成用于恢复数据的SQL

mysqlbinlog --start-position=482  --stop-position=1402 /data/mysqllog/relay-bin.00005 > /tmp/relay.sql

需要注意恢复的数据不仅是data1,在drop database data1后其他库可能还有操作,这部分也是要恢复的。


3、使用跳过事务的方法解决MySQL主从复制出错

首先在从库上执行命令查看出错原因

show slave status \G 

然后在确定出错的事务不重要的前提下可以采取跳过出错事务的方式来恢复同步,然后想办法手动恢复错误事务

#方法1:跳过指定数量的事务
mysql > stop slave ;
mysql > set global sql_slave_skip_counter=1
mysql > start slave ;

#方法2:跳过指定类型的错误或者所有错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1146,2341  #跳过指定错误类型
slave-skip-errors=all  #跳过所有错误,不建议使用



温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《MySQL主从复制(1)使用Binlog配置主从与延迟从库
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-226.html  [生成短网址]

已有 0/3593 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~