本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • MySQL主从复制(1)使用二进制日志配置主从模式

MySQL主从复制(1)使用二进制日志配置主从模式

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

一、什么是MySQL主从复制

1、MySQL主从复制作用与原理

MySQL主从复制可以实现读写分离之外还可以在一定程度上当做备份使用。主从复制依赖于MySQL的二进制日志(binlog)来实现。主节点上的binlog记录了每一条影响数据的SQL语句,从库将binlog日志复制到从库的中继日志(relaylog)中,然后再做相同的操作,实现了数据的同步。需要注意的是虽然可以做一主多从的架构,但是从服务器的数量不能太多,否则会因为日志的传输给主库带来过多的带宽消耗。


2、二进制日志binlog_format的三种格式介绍

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

row(行模式,推荐):将数据的具体改变记录在日志中,日志量大,安全性最高,不会因为某些产生随机数的SQL而让主从数据不一致。

mixed(混合模式):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
 binlog_rows_query_log_events = on
 expire_logs_days = 10  #二进制日志过期天数
 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


2、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用户无效),否则如果写操作发生在从库的话,主库是不会同步数据过来的
 master_info_repository = table
 relay_log_info_repository = table  #relay-info信息写入数据库表中,默认是file
 slave-parallel-workers=4  #并行复制的线程数
 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  #如果需要做多级主从服务器要打开此项和记录二进制日志,让这台从服务器作为其他从服务的master


登陆Slave服务器的MySQL,配置Master服务器二进制日志的position信息。这样从库就会先把该日志之前所记录的SQL都进行执行

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也可以,从头复制


启动从库,然后查看从库当前状态

mysql > start slave;
mysql > 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


这个时候在主服务器创建数据,然后到从服务器查看,这些数据已经被同步过来了。通过在主从分别执行show processlist命令也可以看到当前主从的状态,比如主库二进制日志已经全部发送给从库,等待更新二进制日志等


三、当MySQL主从复制出错的解决办法(跳过错误事务)

1、在从库上执行命令查看出错原因

show slave status \G 

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

#方法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  #跳过所有错误,不建议使用

3、数据不一致如何修复?

清空从节点数据后重新复制


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

已有 0/3161 人参与

发表评论:

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

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