mysql主从同步原理，配置，同步延迟处理
2018/07/12/10:02:19 阅读：5189 来源：谷歌算法 标签：
1原理2在windows主机上搭建mysql57主从复制1首先在同一台主机上搭建两个mysql服务2然后配置文件注意修改后mysql得重启21主库配置文件22从库配置文件3在主库添加一个用户repl并制定replication权限4保持主从mysql的rtzc_pnc_base数据库初始状态一致5在主数据库里面运行showmasterstatusG6在从库设置它的master7在从库开启从数据库复制功能71但是发现有错误slave_io_running为no启动正常的话红框应该都是yes8此时在主库创建表或插入数据在从库就会很快也能看到了如下左边主右边从9注意3主从复制延迟处理1原理2解决方法224监控主从复制是否停止1写shell脚本监控2Nagios1、原理
Mysql的Replication是一个异步的复制过程，从一个Mysqlinstace(我们称之为Master)复制到另一个Mysqlinstance(我们称之Slave)。在Master与Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在Slave端，另外一个线程(IO线程)在Master端。
要实现MySQL的Replication，首先必须打开Master端的BinaryLog(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开MySQL的BinaryLog可以通过在启动MySQLServer的过程中使用“—log-bin”参数选项，或者在my.cnf配置文件中的mysqld参数组([mysqld]标识后的参数部分)增加“log-bin”参数项。
MySQL复制的基本过程如下：
1.Slave上面的IO线程连接上Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
2.Master接收到来自Slave的IO线程的请求后，通过负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在Master端的BinaryLog文件的名称以及在BinaryLog中的位置;
3.Slave的IO线程接收到信息后，将接收到的日志内容依次写入到Slave端的RelayLog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
4.Slave的SQL线程检测到RelayLog中新增加了内容后，会马上解析该Log文件中的内容成为在Master端真实执行时候的那些可执行的Query语句，并在自身执行这些Query。这样，实际上就是在Master端和Slave端执行了同样的Query，所以两端的数据是完全一样的。
2、在windows主机上搭建mysql5.7主从复制2.1首先在同一台主机上搭建两个mysql服务
（需要自定义不同的端口，主：3306，从：3307）；
远程连接mysql：mysql-h127.0.0.1-uroot-padmin-P3307（在mysql的bin文件夹下执行）
2.2然后配置文件（注意修改后mysql得重启）2.2.1主库配置文件#主库配置server-id=1binlog-do-db=rtzc_pnc_base#要同步的数据库#binlog-ignore-db=mysql#不同步的数据库,如果指定了binlog-do-db这里应该可以不用指定的log-bin=mysql-bin#要生成的二进制日记文件名称2.2.2从库配置文件#从库配置server-id=2replicate-do-db=rtzc_pnc_baselog-bin=mysql-bin2.3在主库添加一个用户repl，并制定replication权限
createuser‘repl’@’127.0.0.1’identifiedby‘admin’;
GRANTREPLICATIONSLAVEON.TO‘repl’@’127.0.0.1’;【这里我指定数据库（rtzc_pnc_base.）时报错，而指定全库（.*）时会成功。】
2.4保持主从mysql的rtzc_pnc_base数据库初始状态一致
一般是先将所有的表加锁（只读），然后copy磁盘上的数据库文件夹。我这里直接停止服务，然后将数据文件拷贝过去
2.5在主数据库里面运行showmasterstatus\G
（注意有\G后面不加;，否则报错）记下file和position字段对应的参数
2.6在从库设置它的master
changemastertomaster_host=’127.0.0.1’,master_port=3306,master_user=’repl’,master_password=’admin’,master_log_file=’mysql-bin.000001’,master_log_pos=154;
注意：master_port对应主库的端口，master_log_file和master_log_pos对应刚才showmasterstatus记下的参数。
2.7在从库开启从数据库复制功能
startslave;（mysql5.5是slavestart;启动）
在从库可以通过showslavestatus\G来查看一些参数。
2.7.1但是发现有错误，slave_io_running为no，启动正常的话红框应该都是yes。
寻找问题的方法：到从库的data目录下找到WIN-20151017TLX.err文件，发现里面有一行error日志，如下
解决问题方法：
只有按照原格式修改data目录下的auto.cnf文件里面的server-uuid，随便写就ok了。
2.8此时在主库创建表或插入数据，在从库就会很快也能看到了，如下左边主，右边从
2.9注意
主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。*如果主从的网络断开，从会在网络正常后，批量同步。*如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据。
3、主从复制延迟处理3.1原理
当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。
3.2解决方法
3.2.1插入后，更新缓存服务如redis，其他节点需先读缓存中的数据。然后再写入从mysql。
3.2.2
丁奇的transefer是一个不错的方案，不过一般公司受限于对mysql的代码修改能力的限制和对mysql的掌控能力，还是不太适合；
最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如
sync_binlog=1，innodb_flush_log_at_trx_commit=1之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率。
另外就是使用比主库更好的硬件设备作为slave；
mysql-5.6.3已经支持了多线程的主从复制。原理和丁奇的类似，丁奇的是以表做多线程，oracle使用的是以数据库(schema)为单位做多线程，不同的库可以使用不同的复制线程。
4、监控主从复制是否停止4.1写shell脚本监控4.2Nagios
热门评论