mysql主从复制一个小错误导致从库不更新数据

小鱼儿 2022-09-24 01:26 81阅读 0赞

由于历史遗留问题，我们的MySQL主从库的表结构不一致，主库的某个表tableA比从库表tableA少了一个字段。

当尝试在主库上更改表结构时，这行alter语句会随着binlog同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停止，那样只能人为手动处理错误，然后再启动slave上的主从同步线程。场景大概是下面这个样子：

1，在主库上执行alter table aaa add column xxx int default 1  after yyy;

2，从库同时也会执行这样语句，但是由于从库上已经有xxx这个字段了，于是主从线程更改表失败，这个时候用show slave status查看主从状态就会发现类似于下面这样的语句：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Slave_IO_Running: Yes</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Slave_SQL_Running: No</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Replicate_Do_DB:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Replicate_Ignore_DB:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Replicate_Do_Table:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Replicate_Ignore_Table:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Replicate_Wild_Do_Table:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>Replicate_Wild_Ignore_Table:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Last_Errno: 1060</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Last_Error: Error</code> 
      <code>'Duplicate column name '</code> 
      <code>xxx</code> 
      <code>''</code> 
      <code>on query. Default database: </code> 
      <code>'dbxxx'</code> 
      <code>. Query:</code> 
      <code>'alter table aaa add column xxx int default 1 after yyy'</code> 
     </div> 
     <div> 
      <code>//此处省略了一些语句</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Last_SQL_Errno: 1060</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Last_SQL_Error: Error</code> 
      <code>'Duplicate column name '</code> 
      <code>xxx</code> 
      <code>''</code> 
      <code>on query. Default database: </code> 
      <code>'dbxxx'</code> 
      <code>. Query:</code> 
      <code>'alter table aaa add column xxx int default 1 after yyy'</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp; 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

可以看到，从主库复制binlog的salve\_io线程还在忙活，但是执行从库更新的slave\_sql已经罢工了，从Last\_error:可以看到是因为执行主库中国来的alter table aaa add column xxx int default 1 after yyy时出错了，具体原因是这个表上已经有了xxx这一列。

手动的解决方法是告诉从库slave\_sql线程让他忽略掉这个错误继续执行：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt;set global sql_slave_skip_counter=1;</code> 
     </div> 
     <div> 
      <code>mysql&gt;start slave;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

或者运行：SET GLOBAL SQL\_SLAVE\_SKIP\_COUNTER = 1;//跳过一行错误

上面的语句是告诉slave跳过当前卡住的event，然后重新起来干活。

上面的方法在slave比较少的时候还可以，但是当从库有几十台时，逐台去处理既费时又容易出错，怎样在主库这一侧一劳永逸地避免呢？

对，就是在主库这侧不要将这样alter这样语句写入到binlog中，MySQL不愧世界级的软件产品，它提供了一个session粒度的选项，通过关闭这个选项可以不让主库将打开这个选项或关闭连接前的SQL语句写入binlog。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt;set sql_log_bin=off;</code> 
     </div> 
     <div> 
      <code>mysql&gt;alter table aaa add column xxx</code> 
      <code>int</code> 
      <code>default</code> 
      <code>1 after yyy;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

是不是很方便呢？