mysql主主会出现什么情况_mysql主主同步问题排查

MYSQL数据库延迟问题的定位及解决

一 、状态查看与定位

MYSQL主主同步有时候会出现莫名的延迟，为了排查这一问题，首先要知道主主复制是否还在进行？

1.

(1).在从数据库上：

>show  slave  status\G

先看从数据库的状态，看到IO线程和SQL线程都为yes(正常开启)，

在看下面有一条 seconds_behind_master :(通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp而得出的差值)

注：一般情况下若后面为0，表示主从复制良好，不存在延迟；为NULL表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes；若为正数，表明传输存在延迟，正数越大，延迟越多；负数是个bug。

在show processlist进程信息，查看相关联的主数据库的command若为Binlog Dump，表示此时主数据库正在向从数据库同步。

通过以上几条参数，我们可以判断出主主结构数据库目前正在进行同步复制。下面，就该查找其他问题。

2 .

监控目前主从数据库同步的状态信息(同步到哪一行语句，哪张表，在什么时间，甚至已同步和没同步的数据)：

(1). 依然是在从数据库上：

在状态中注意红框中的部分，

分别是：目前所同步的主数据库的binlog日志名，目前所同步的主数据库的binlog日志的偏移位置和目前所执行的relay_log日志偏移位置。

我们到主数据库上查看主的状态，比对两库所同步的binlog名字和偏移位置信息是否一致；一致说明IO同步进程没有问题。

找到系统中对应的relay_log文件，然后将它导出：

(2).这样就可以通过vi，cat，more等命令去查看relay_log文件里面的内容了

通过定位，很快就可以找到它当前正在同步的位置，

下面分别是它的日期和时间，数服的id号码，以及对哪一个表，哪条数据做了哪些操作；

由于我们目前的库较少，大部分所操作的表都是在一个库里，可以通过/^use来查找到是哪一个数据库。

二 、问题处理

MYSQL主主复制的基本运行问题已经检查完毕，主从库的结构、状态等都属正常，那么是哪里出现的延迟呢，我们还要从以下几个方面去考虑一下？

(一般容易出现的数据库延迟问题有：网络延时、SQL语句延迟、单线程延迟、参数调整)

依次来看

1 . 网络延时

在 MySQL的复制里，由Slave发送一个请求命令后，就完全由 Master来推送数据。如果Master没有更新，也就不会有数据流，Slave就不会收到任何数据包。但是如果由于网络故障或其他原因导致Master无法把数据发送到Slave，就没法知道收不到数据是因为 Master没有更新还是出了故障。但是MySQL 5.5以后有个‘复制心跳’的功能可以解决这一问题。

配置方法如下：>stop slave;>change master to master_heartbeat_period = 10;>set global slave_net_timeout = 25;>start slave;中间两条配置的意思是：Master 在没有数据的时候，每10秒发送一个心跳包，能够知道 Master是不是还正常。slave_net_timeout(默认3600)是设置在25秒没收到数据后认为网络超时，之后Slave的IO线程会重新连接Master。结合这两个设置就可以避免由于网络问题导致的复制延误。

2 . SQL语句延迟

如果网站在运营过程中遇到突然变慢的情况，一般和MySQL慢有关系，可以通过开启慢查询，找到影响效率的SQL，然后采取相应的措施。

(1)开启慢查询方法：

1)在数据库中操作：

mysql>show variables like‘%slow%’;      //查看慢日志查询是否开启

mysql>set global long_query_time = 2;   //SQL 执行时间超过2秒即记录

mysql>set global slow_query_log = ON;   //开启

2)添加到配置文件my.cnf中(需要重启)：

#long_query_time=2#log-slow-queries= /usr/local/mysql/mysql-slow.log   //自定义的日志记录文件位置

(2)在mysql-slow.log中会记录下sql语句的执行情况、锁定时间等等，如果数据过多的话，可以使用慢查询分析工具。(简介一下官方自带的分析工具mysqldumpslow的使用)

#mysqldumpslow  --help       //可以查看到帮助信息

图中红线是几个常用的参数；

-s，表示按照何种方式排序，

c、t、l、r分别是按照记录次数、查询时间、锁定时间、返回的记录数来排序(从大到小)，ac、at、al、ar代表相应的平均值；

-r，是反向排序；

-t，表示返回前面多少条数据；

-g，后边可以写一个正则匹配模式(忽略大小写)。

例如：

#mysqldumpslow -s t -t 10 -g left join host-slow.log

(按照时间返回前10条里面含有左连接的sql语句)

使用mysqldumpslow工具可以明确的得到各种我们需要的查询语句，对MySQL语句的监控、分析、优化起到非常大的帮助。

3 . 单线程延迟

(1).mysql的数据库主从复制原理 ：

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高。slave的Slave_IO_Running线程到主库取日志，然后，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施；DML和DDL的IO操作是随即的，不是顺序的，效率要低很多，还可能与slave上的其他查询产生lock争用，由于Slave_SQL_Running是单线程的，所以一个DDL卡住了10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。

那为什么只有slave会延时？如果主库上那个相同的DDL也需要执行10分，是不是有一种状态能达到相对无延时的呢？ 答案是“NO”，因为master可以并发，但是Slave_SQL_Running线程却不可以。这样，当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，或者与slave的大型query语句产生了锁等待，那么延时就产生了。

(2).处理办法：

1)首先一个MYSQL实例不要创建太多database，否则一旦其中一个库压力大经常被锁，会导致所有库同步都延迟【目前我们的状况在这一点上还是比较符合的】

2)压力较大的情况下使用几个从数据库值得考量，如果使用多个从数据库也是可以适当缓解上面lock的情况发生【主-从-从结构在大并发运行环境中可行，作为备用方案】

3)在MySQL5.6版本中新引入了GTID和多线程复制，在新版中支持基于库的多线程(队列)复制,但是库里的表不能多线程，只能是基于不同库里的表进行多线程。【从我们目前的延迟情况看暂不涉及到，略过】

4 . 参数调整

1、介绍两个重要的参数是：sync_binlog和innodb_flush_log_at_trx_commit

(1)、sync_binlog =  N

(This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction)N=0，

默认值，不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；

N=1，

值1是最安全的，当系统或服务器崩溃时，最多丢掉二进制日志中的一个语句或事务，但也是最慢的(除非硬盘有电池备份缓存，从而使同步工作较快)；

N>0，

每向二进制日志文件写入N条SQL或N个事务后，MySQL服务器把二进制日志文件的数据刷新到磁盘上。

如果对响应速度要求比较高的设成0，对安全要求性比较高的就设成1。

(2)、innodb_flush_log_at_trx_commit = NN=0，

每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；

(log buffer 会每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响，也就是log buffer的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果mysqld进程崩溃，会导致上一秒钟所有事务数据的丢失)N=1，默认值，

每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；

(当取值为 1时，每次事务提交时，log buffer会被写入到日志文件并刷写到磁盘。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢)N=2，

每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；

(当取值为 2时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果mysqld进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失)

上文的「最后 1s」并不是绝对的，有的时候会丢失 更多数据。有时候由于调度的问题，每秒刷写(once-per-second flushing)并不能保证100%执行。对于一些数据一致性和完整性要求不高的应用，配置为2就足够了；如果为了最高性能，可以设置为0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为1。备注：当innodb_flush_log_at_trx_commit和sync_binlog  都为 1 时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双11 会导致频繁的io操作，因此该模式也是最慢的一种方式。 双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

2 、一些其他的参数：

#skip-locking

避免MySQL的外部锁定，减少出错，增强稳定性。

#key_buffer_size=256M

指定用于索引缓冲区大小，适当增加可以得到更好的索引处理性能。(内存4G建议为256M或384M，不宜过大)

#max_connections=1000

指定MySQL允许的最大连接进程数。如果访问出现TOO MANY CONNECTION错误，则应增大该值。

#wait_timeout=10

指定一个请求最大连接时间。(内存4G可设为5-10)

#max_connect_errors=30

每个客户端允许连接的最大错误数量。

#query_cache_size=128M

查询缓冲区的大小，常被用来缓冲SELECT 的结果并且在下一次同样查询时直接返回结果。(查询缓冲可以提高服务器速度，如果有大量的相同查询而不是修改表的话)

#transaction_isolation = REPEATABLE-READ

设定默认事务隔离级别。(可设为READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE)

#server_id=1

唯一服务器标识号(主、从服务器均要设置)

#binlog_format=MIXED

二进制日志的复制格式。(可选为混合，防止出错；特殊情况下可选为row，默认是statement)