mysql主从不同步问题解决

千家信息网最后更新 2025年01月23日mysql主从不同步问题解决环境介绍： 我这里一两台，主主复制的mysql： 192.10.0.243 192.10.0.244 通过keepalived映射出来了vip:192.10.0.118，目前vip在243上。 由于某种原因244服务器异常down机，服务器启动之后，很幸运的是mysql服务器正常启动了，查看按着习惯马上查看错误日志，发现报错：有一个表需要repair. 2017-03-21 10:46:40 3178 [ERROR] /usr/sbin/mysqld: Table './info/v_publish_text' is marked as crashed and should be repaired 2017-03-21 10:46:40 3178 [Warning] Checking table: './info/v_publish_text' 马上处理这个报错： mysql> repair table info.v_publish_text; 这个表很大，尝试repair了整整6个小时，依旧没有成功，错误日志没有任何报错，并且正好本身主从这两个表就差将尽4万的数据（业务性质允许这样的偏差），正好借这个机会从新初始化下这个表好了， 具体初始化这个表的流程： 整体流程：首先把业务都切到243服务器上。 1.在243上mysqldump这个表并把dump文件传到244， 2.关闭243的主从复制。 3.在244上恢复出来数据。 4.在244上查看当前的mater 信息。 5.在243上重新change此时244的master的信息。 6.在244开启slave，（244一直处于stop slave 的状态）。 下面展示具体操作流程以及相关注意事项 一：在主库（243）上mysqldump表v_publish_text,然后把dump文件传给244服务器。 [root@S243 web_backup]#mysqldump -u root -p******* info v_publish_text |gzip > /mysql2/web_backup/v_publish_text.sql [root@S243 web_backup]#scp v_publish_text.sql root@192.10.0.244:/mysql 二：停掉主库243的复制进程 mysql> stop slave; 验证io和sql进程都为no,一定要确保slave进程已经被关闭了，目的是要跳过接下来在244执行的恢复过程产生的binlog. mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.244 Master_User: info_syncer Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001511 Read_Master_Log_Pos: 625205966 Relay_Log_File: S243-relay-bin.000079 Relay_Log_Pos: 479217301 Relay_Master_Log_File: mysql-bin.001511 Slave_IO_Running: No Slave_SQL_Running: No 三：在244上恢复出数据，过程是：先drop table ，然后create table ,最后insert数据。 [root@S244 mysql]# gunzip show master status; +------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+------------------+-------------------+ | mysql-bin.001472 | 127771389 | | | | +------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 在243从新change指向244新的位置， mysql> CHANGE MASTER TO MASTER_HOST="192.168.0.244", MASTER_USER="info_syncer", MASTER_PASSWORD="z=w@yLFh=su.VE7Oiw;e1QF,1", master_port=3306, MASTER_LOG_FILE="mysql-bin.001472", MASTER_LOG_POS=127771389 ; 注意：写binlog的时机是：sql语句或transaction执行完，但任何相关的locks还未释放或事务还未最终commit前。这样保证了binlog记录的操作时序与数据库实际的数据变更顺序一致。也就是说当你在244上恢复完数据后，show master status;显示的master的binlog不一定把刚才写完的sql或事务都写进了binlog,所以最好是在244上恢复完数据后，等一会再执行change，反正此时244没有写的业务，索性就多等一会，再在243change; 四：启动244的主从复制，这样会从当时stop slave的位置继续接受主库的binlog,同时也会继续从当时stop时relay log的位置开始应用。这时候肯定会有问题，因为针对v_publish_text表，里面好多数据已经通过mysqldump恢复出来了，再次应用日志肯定会报主键冲突，进而导致主从复制失败，这时候提前写好跳过一个事务的脚本，准备在报错的时候执行就可以了，反正现在从库244上没有任何业务， mysql> start slave; mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.244 Master_User: info_syncer Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001511 Read_Master_Log_Pos: 625205966 Relay_Log_File: S243-relay-bin.000079 Relay_Log_Pos: 479217301 Relay_Master_Log_File: mysql-bin.001511 Slave_IO_Running: YES Slave_SQL_Running: YES 跳过一个事务的脚本，以便于遇到问题之后快速执行跳过一个事务。一定注意当遇到报错的时候，再跳过，否则过多的跳过会造成数据不同步,因为正常的事务是不能跳过的。 [root@S244 ~]# cat /root/skip_erro.sh #!/bin/bash /usr/bin/mysql -u root -p'c!*]nnnn$' <补充内容： mysqlbinlog主从写的机制： 一：在主库243操作数据库，然后分别在243和244上格式化当前的binlog,并且查看相关内容，结果如下： create table liuliuliu ( id int) insert into liuliuliu values(111) insert into liuwenhe.liuliuliu values(11)
[root@S243 mybinlog]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001473 > binlog [root@S243 mybinlog]# cat binlog | grep liuliuliu create table liuliuliu ( id int) insert into liuliuliu values(111) insert into liuwenhe.liuliuliu values(11) [root@S244 mybinlog]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001513 > binlog [root@S244 mybinlog]# cat binlog | grep liuliuliu create table liuliuliu ( id int) insert into liuliuliu values(111) insert into liuwenhe.liuliuliu values(11) 结论：在主库操作的数据库，相关记录必然记录到主库binlog，值得注意的是从库也把相关的信息记录进它自己的binlog中. 然后我猜应该是做了特别的标记，使得244并不会把从243接收到的相关操作信息再次传回给243， 二：binlog的三种格式以及binlog的组提交（摘自网络）： 2.1：Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW！ 2.1.1.Statement：每一条会修改数据的sql都会记录在binlog中。 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。) 缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题). 使用以下函数的语句也无法被复制： * LOAD_FILE() * UUID() * USER() * FOUND_ROWS() * SYSDATE() (除非启动时启用了 --sysdate-is-now 选项) 同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁 2.1.2：Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。 优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。 2.1.3：Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。 2.1.4：Binlog基本配制与格式设定 1.基本配制 Mysql BInlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。如以下： binlog_format = MIXED //binlog日志格式 log_bin =目录/mysql-bin.log //binlog日志名 expire_logs_days = 7 //binlog过期清理时间 max_binlog_size 100m //binlog每个日志文件大小 2.1.5：Binlog日志格式选择 Mysql默认是使用Statement日志格式，推荐使用MIXED. 由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。 2.1.6：针对binlog的三种格式而产生相应的主从复制的三种方式： （１）：基于语句（Statement）的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。 （２）：基于行（row)的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持 （３）：混合类型（mixed)的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。 2.2： binlog组提交（5.6），5.6默认就是组提交，不需要开启，这是它的内部机制 它的基本思想是：引入队列机制保证innodb commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。binlog提交将提交分为了3个阶段，FLUSH阶段，SYNC阶段和COMMIT阶段。每个阶段都有一个队列，每个队列有一个mutex保护，约定进入队列第一个线程为leader，其他线程为follower，所有事情交由leader去做，leader做完所有动作后，通知follower刷盘结束。在 mysql 5.5 中，只有当 sync_binlog = 0 时，才能使用 group commit，在 mysql 5.6中都可以进行 group commit log组提交基本流程如下： FLUSH 阶段 1) 持有Lock_log mutex [leader持有，follower等待] 2) 获取队列中的一组binlog(队列中的所有事务) 3) 将binlog buffer到I/O cache 4) 通知dump线程dump binlog SYNC阶段 这个阶段和参数sync_binlog有关系， 1) 释放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待] 2) 将一组binlog 落盘(sync动作，最耗时，假设sync_binlog为1)。 COMMIT阶段 1) 释放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待] 2) 遍历队列中的事务，逐一进行innodb commit 3) 释放Lock_commit mutex 4) 唤醒队列中等待的线程 说明：由于有多个队列，每个队列各自有mutex保护，队列之间是顺序的，约定进入队列的一个线程为leader，因此FLUSH阶段的leader可能是SYNC阶段的follower，但是follower永远是follower。 通过上文分析，我们知道MYSQL目前的组提交方式解决了一致性和性能的问题。通过二阶段提交解决一致性，通过redo log和binlog的组提交解决磁盘IO的性能。 2.3：关于参数sync_binlog的理解： sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。 sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘，当数据库crash的时候至少会丢失N-1个transactions sync_binlog=1，每一个transaction commit都会调用一次fsync()，此时能保证数据最安全但是性能影响较大。 总结：mysql主从复制，正常情况下slave读取master的binlog_buffer中的binlog,并不是等写到binlog底层文件后才读取的，只有当slave出现故障后，但是此时maser库依旧在跑业务，当从新开始start slave；这时候读取的binlog就会从磁层磁盘binlog文件读取。 延伸内容： 异 步复制：咱们现在大多数都是异步复制的，MySQL本身支持单向的、异步的复制。异步复制意味着在把数据从一台机器拷贝到另一台机器时有一个延时 - 最重要的是这意味着当应用系统的事务提交已经确认时数据并不能在同一时刻拷贝/应用到从机。通常这个延时是由网络带宽、资源可用性和系统负载决定的。然 而，使用正确的组件并且调优，复制能做到接近瞬时完成。 同步复制：使用MyISAM或者InnoDB存储引擎的MySQL本身并不支持同步复制，同步复制可以定义为数据在同一时刻被提交到一台或多台机器，通常这是通过众所周知的"两阶段提交"做到的，也就是说保证数据至少在一台slave上正常commit。虽然这确实给你在多系统中保持一致性，但也由于增加了额外的消息交换而造成性能下降。 半同步复制：是基于Google为MySQL开发的半同步复制的插件。半同步复制的原理是，一个事务在主服务器上执行完成后，必须至少确保至少在一台从服务器上执行完成后，事务才算提交成功。如果在一定时间内从服务器没有响应，则会自动降级为异步复制。 这个半同步复制是建立在异步复制的基础之上进行的。