初探MySQL主从复制

MySQL主从同步原理

• 当主数据库的数据有更新的时候，主库将按照设置的binlog格式把更新的事件类型写入主库的binlog文件，此时生成一个log dump thread （日志备份线程）通知从库的I/O线程
• I/O线程接到通知后就像log dump线程请求一份指定了binlog文件位置的副本，并将其保存至本地的relay log （中继日志）
• 从库的SQL线程检测到中继日志有更新后，读取并执行文件，执行完毕后会自动将relay log文件删除

主库配置

1.主库开启log_bin 功能，并设置server_id

2.主库创建主从同步的mysql账号，并授予replication，slave权限

mysql -e "GRANT replication slave  *.* to 'repl'@'192.168.137.110/255.255.255.0' identified by 'repl'" -uroot -p

3.主库整库锁表，然后备份已有数据生成sql文件，并拷贝至从库服务器，完成后解锁主库

mysql -e " flush table with read lock " -uroot -p123456
mysqldump -uroot -p123456 -A --master-data=1 > test.sql 
scp /tmp/test.sql root@192.168.137.111:/tmp
mysql -e " unlock   tables " -uroot -p123456

从库配置

1.设置server_id, 从库一般不需要开启log_bin功能，除非做级联，即同时还作为其他库的主库

[mysqld]
server_id=2222 #需要跟主库不一致

2.先手动复制主库的数据到从库

mysql -uroot -p123456 < repl.sql

3.如果主库配置第三步中，备份数据时没有指定–master-data=1 ，则需要在从库中指定主库信息

mysql -e  "change master to \
master_host='192.168.137.110', \
master_log_file='mysqlbin.000004',\
master_user='repl',master_password='repl',\
master_log_pos=428;" -uroot -p123456

    mysql> change master to master_host='192.168.137.110',master_port=3306,master_user='repl',master_password='repl';

4.从库开启同步开关

    mysql -uroot -p123456 -e " start slave;" 

主从同步存在的情况分析

情况一：中继日志损坏导致主从同步中断

查看从库情况show slave status\G;
通过编辑my.conf
修改参数relay_log_recovery = 1 （自动放弃所有未执行的relay-log，并且重新从master上获取日志，这个参数默认关闭）

情况二：主从同步延迟

如何判断主从延时

1. 通过show slave status\G输出的Second_Behind_Master参数来判断（0是良好，数字越大表示从库落后主库越多）
2. 通过pt-heartbeat工具

产生延时的原因

1. 主库的并发较高时，产生的SQL操作超过slave一个sql线程所能承受的范围
2. 网络延迟

主从延时的解决方法

1. 在架构上做优化，如读写分离，分库分表，加入memcache或者redis的cache层来降低mysql的压力等
2. 从库使用更好的硬件设备

binlog日志有statement、row、mixed三种格式

• statement格式(默认)：记录每一条导致数据变动的sql语句。存在的问题:（时间上可能不完全同步造成偏差，执行语句的用户也可能是不同一个用户）
• row格式：记录下每行数据的修改细节，不需要记录上下文相关信息。基于ROW模式下的行复制，是把主服务器上面改编后的内容直接复制过去，而不关心到底改变该内容是由哪条语句引发的。缺点（这种格式的日志文件较大，比如一个工资表中有一万个用户，我们把每个用户的工资+1000，那么基于行的复制则要复制一万行的内容，由此造成的开销比较大，而基于语句的复制仅仅一条语句就可以了）
• mixed：基于混合类型格式的复制下，MySQL默认使用基于语句的复制，当基于语句的复制会引发问题的时候就会使用基于行的复制，MySQL会自动进行选择。

如何修改MySQL的binlog日志格式

• 方法一：通过在数据库命令行下设置修改（暂时性的)
  

• 方法二：通过编辑配置文件my.cnf（永久性的）
  

binlog日志里是二进制格式的，需要通过mysqlbinlog的工具来查看具体内容

        mysqlbinlog mysql-bin.000001 

利用binlog日志进行数据恢复的小小实验操作

mysql> show master status;查看binlog日志确定要恢复的位置
mysqlbinlog --start-position=120 --stop-position=428 mysql-bin.000013 > /tmp/test.sql;将指定位置的binlog文件用mysqlbinlog工具导出sql语句（shell命令行执行）
mysql> source /tmp/test.sql;在数据库中将SQL文件再执行一次

###参考
https://segmentfault.com/a/1190000008942618
https://www.cnblogs.com/fllf/p/10417431.html