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

MySQL主从复制(1)MySQL二进制日志实现主从复制教程

发布:TangLu2018-11-23 18:21分类: MySQL 标签: mysql 主从 主从复制

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


一、MySQL主从复制配置前提:

主节点需要:启动二进制日志;设置全局唯一的server-id;建立有复制权限的用户账号

从节点需要:启动中继日志;设置全局唯一的server-id;使用有复制权限的用户账号启动2个复制线程(I/O thread将数据从主库复制到从库中,然后写入到Relay Log中继日志,最后通过SQL thread读取中继日志进行数据同步,这2个线程缺一不可)


二、MySQL主从复制配置过程:

1、首先修改Master端的my.cnf配置文件,增加以下内容到[mysqld]模块中:

[mysqld]
 basedir = /usr/local/mysql
 datadir = /mysqldata
 port = 3306
 socket = /tmp/mysql.sock
 server_id = 1  #该ID最大值为4294967295,主从不能一样。开启binlog的话必须配置server_id,否则服务会出错
#### log set ###
 log_bin = master-bin    #开启binlog并设置日志路径与名称
 log_bin_index = master-bin.index  #binlog索引文件
 binlog_format = row
 expire_logs_days = 10  #二进制日志过期天数
 max_binlog_size = 1024M  #二进制日志大小,最大可设置1G,但是如果一个SQL事务产生了5G日志,这个大小就会被突破
 binlog_cache_size = 32768  #二进制日志缓存区大小
 sync_binlog = 1 #主动将缓存同步到硬盘,0则是关闭。如果是其他数字,代表有N次commit的时候才写入磁盘
 log_error = error.log  #打开错误日志,没有配置的话默认以主机名作为错误日志名
 ### other set ###
innodb_file_per_table=ON
skip-name-resolve=ON  #禁用DNS解析



上面binlog_format选项有三种日志格式,使用show variables like '%binlog_format%'命令可以查看当前日志格式

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

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

mixed:statement和row的结合模式,会根据语句自动选择

看示例更容易搞懂他们之间的区别:

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


2、修改Slave端my.cnf配置文件

[mysqld]
 basedir = /usr/local/mysql
 datadir = /mysqldata
 port = 3306
 socket = /tmp/mysql.sock
 server_id = 2    #和Master的ID不一样即可
 # log_bin = slave-bin  #可不开启
 # binlog_format = row #可不开启
 relay-log = relay-log  #开启中继日志,在主从配置好了后就会生成日志出来。不指定的话默认是使用主机名作为中继日志的名字,主机名一旦更改,主从就会出错
read_only = 1  #从库需要配置为只读,否则如果写操作发生在从库的话,主库是不会同步数据过来的
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


3、在Master端创建一个数据库用户并授权,该用户用于主从数据的复制工作

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

4、登陆Slave服务器的MySQL客户端,开始启动slave线程。这里需要写下Master服务器当前二进制日志的名字以及位置(在主库执行show master status \G可以查看),这样从库就会先把该日志之前所记录的SQL都进行执行。

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、在从库执行show slave status \G命令可以用于查看从库当前状态,主要关注以下几个指标:

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

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

mysqlzc4.png

Read_Master_Log_Pos:从库读取到主库哪个位置了,和主库show master status的值一致最好

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


6、执行START SLAVE语句启动复制线程,再次查看已经是Yes状态mysqlzc5.png

7、这个时候在主服务器创建数据测试,再到从服务器查看,这些信息已经被同步过来了。通过在主从分别执行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  #跳过所有错误,不建议使用

四、MySQL主从复制的其他注意事项:
1、主从服务器保持MySQL版本一致、时间一致。可以通过mysql语句select version()函数查看MySQL的版本。

mysqlzc1.png

2、对从服务器进行只读设置在从服务器的my.cnf中设置read_only=ON以限制从服务器为只读,不可写入(此限制对拥有super权限的用户无效)

3、对于一主多从的架构建议使用半同步复制。半同步复制是指主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待一个速度最快的从库接收到并写到relay log中才将结果返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟。所以半同步复制最好在低延时的网络中使用。

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

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

5、如何判断从服务器是否落后于主服务器?

mysql > SHOW SLAVE STATUS\G  #查看Second_behind_master的值


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

已有 0/2896 人参与

发表评论:

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

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