Home > Database > Mysql Tutorial > body text

mysql主从服务器不同步

WBOY
Release: 2016-06-07 15:15:15
Original
1117 people have browsed it

欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入 今天检查数据库发现一台MySQL Slave未和主机同步,查看Slave状态: mysql show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1062 .... Seconds_Behind_Master:NULL 原

欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入

  今天检查数据库发现一台MySQL Slave未和主机同步,查看Slave状态:

  mysql> show slave status\G

  Slave_IO_Running: Yes

  Slave_SQL_Running: No

  Last_Errno: 1062

  ....

  Seconds_Behind_Master:NULL

  原因:

  1.程序可能在slave上进行了写操作

  2.也可能是slave机器重起后,事务回滚造成的.

  解决办法I:

  1.首先停掉Slave服务:slave stop

  2.到主服务器上查看主机状态:

  记录File和Position对应的值。

  mysql> show master status;

  +------------------+-----------+--------------+------------------+

  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

  +------------------+-----------+--------------+------------------+

  | mysql-bin.000020 | 135617781 | | |

  +------------------+-----------+--------------+------------------+

  1 row in set (0.00 sec)

  3.到slave服务器上执行手动同步:

  mysql> change master to

  > master_host='master_ip',

  > master_user='user',

  > master_password='pwd',

  > master_port=3307,

  > master_log_file='mysql-bin.000020',

  > master_log_pos=135617781;

  1 row in set (0.00 sec)

  mysql> slave start;

  1 row in set (0.00 sec)

  再次查看slave状态发现:

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

  ...

  Seconds_Behind_Master: 0

  解决办法II:

  mysql> slave stop;

  mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

  mysql> slave start;

  方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.方法二不一定会有效果.

mysql主从服务器不同步

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template