MysqlSyncNote

Mysql双机热备份

Mysql 主从同步配置

概述 首先主服务器把数据变化记录到主日志,然后从服务器通过I/O线程读取主服务器上的主日志,并且把它写入到从服务器的中继日志中,接着SQL线程读取中继日志,并且在从服务器上重放,从而实现MySQL复制.具体如下图所示:

replication

整个过程反映到从服务器上,对应三套日志信息,可在从服务器上用如下命令查看:

mysql> SHOW SLAVE STATUS;

Master_Log_File & Read_Master_Log_Pos:下一个传输的主日志信息.
Relay_Master_Log_File & Exec_Master_Log_Pos:下一个执行的主日志信息.
Relay_Log_File & Relay_Log_Pos:下一个执行的中继日志信息.

理解这些日志信息的含义对于解决故障至关重要

两台服务器搭建Mysql双机热备份 mysql 版本最好一致

---

一. A主B从

• 主服务器 A: 43.241.222.110
• 从服务器 B: 42.96.194.60

在主A服务器(master)下创建给从B服务器 (slave)登录用的用户名密码

GRANT REPLICATION SLAVE ON *.* TO 'syBackup'@'42.96.194.60' IDENTIFIED BY '******';

1. 设置主服务器 A 需要复制的数据库
   打开主服务器 A 的Mysql配置文件 /etc/mysql/my.cnf

#################################################
server-id=1 #主机id,整数
#开启二进制日志,并且名称为 /var/lib/mysql/mysql-bin.***
# 如果是个路径则,保存到该路径下(log-bin=/var/log/mysql-bin.log -> /var/log/mysql-bin.***)
log-bin=mysql-bin

### as master ###
read-only=0 #主机读写权限,读写都可以

binlog-do-db=CoamDatabase #记录日志的数据库:需要的备份数据,多个写多行
binlog-do-db=CoamInfo
binlog-do-db=CoamSNS
binlog-do-db=form
binlog-do-db=CommonData
binlog-do-db=Wordpress
binlog-do-db=IM_Ejabberd

binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

# 自增字段奇数递增,防止冲突(1, 11, 21, ...,)
auto-increment-increment = 10 # 每次递增的步长
auto-increment-offset = 1 # 初始值

### as slave ###
###
###############################################################

重启 Mysql 是配置生效,可以看到主(43.241.222.110) Mysql 增加了两个文件 mysql-bin.000001 和 mysql-bin.index

Tue May 17 10:31:52 yzhang@coam:/data/home/data/mysql$ ls
auto.cnf         client-key.pem  coam.pid    ib_buffer_pool  ibtmp1           performance_schema  server-key.pem
ca-key.pem       CoamDatabase    CoamSNS     ibdata1         IM_Ejabberd      private_key.pem     sys
ca.pem           coam.err        CommonData  ib_logfile0     mysql            public_key.pem      test
client-cert.pem  CoamInfo        forum       ib_logfile1     mysqld_safe.pid  server-cert.pem     Wordpress
Tue May 17 10:31:52 yzhang@coam:/data/home/data/mysql$ sudo service mysql restart       ======> 重启 Mysql 数据库服务
Tue May 17 10:31:52 yzhang@coam:/data/home/data/mysql$ ls
auto.cnf         CoamDatabase  CommonData      ib_logfile1       mysql-bin.000002  performance_schema  sys
ca-key.pem       coam.err      forum           ibtmp1            mysql-bin.000003  private_key.pem     test
ca.pem           CoamInfo      ib_buffer_pool  IM_Ejabberd       mysql-bin.000004  public_key.pem      Wordpress
client-cert.pem  coam.pid      ibdata1         mysql             mysql-bin.index   server-cert.pem
client-key.pem   CoamSNS       ib_logfile0     mysql-bin.000001  mysqld_safe.pid   server-key.pem

– 注意,这里因为配置有问题重启过几次 mysql 导致生成 [mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004] 等几个文件,不过不影响后续步骤执行

先锁定 (hello) 数据库 - 不要退出 mysql shell

mysql> FLUSH TABLES WITH READ LOCK;

然后新开一个终端,导出数据库,我这里只需要导出 hello 数据库, 如果你有多个数据库作为初态的话, 需要导出所有这些数据库:( 这里为了便于测试,我先不导出 forum 数据库,后续测试单独补加同步数据库)

mysqldump --master-data -uroot -p hello > hello.sql
mysqldump -u username -p password --databases CoamDatabase CoamInfo CoamSNS CommonData Wordpress IM_Ejabberd >backup.sql

查看主服务器的状态

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 154
     Binlog_Do_DB: CoamDatabase,CoamInfo,CoamSNS,forum,CommonData,Wordpress,IM_Ejabberd
 Binlog_Ignore_DB: mysql,test,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

然后查看A服务器的 binary 日志位置,记下 Position 和 File 的值.

主服务器已经做完了, 可以解除锁定了

UNLOCK TABLES;

1. 设置从服务器 B 需要复制的数据库
   打开从服务器 B 的 Mysql 配置文件 /etc/mysql/my.cnf

[mysqld]

####################################################################
server-id=2
log-bin=mysql-bin

### as master ###
### as slave ###
replicate-do-db=CoamDatabase #只复制某个库,多个写多行
replicate-do-db=CoamInfo
replicate-do-db=CoamSNS
replicate-do-db=form
replicate-do-db=CommonData
replicate-do-db=Wordpress
replicate-do-db=IM_Ejabberd

replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

relay-log=mysqld-relay-bin # 开启日志中继
log-slave-updates # slave 将复制事件写进自己的二进制日志
#5.5
#log-slave-updates = ON
#5.1
#log-slave-updates = 1
###############################################################

重启 Mysql 是配置生效,可以看到从(42.96.194.60) Mysql 也相应的增加了两个文件 mysql-bin.000001 和 mysql-bin.index

Tue May 17 11:07:14 yzhang@coam:/data/home/data/mysql$ ls
auto.cnf    client-cert.pem  coam.pid        ib_logfile0  mysql               private_key.pem  server-key.pem
ca-key.pem  client-key.pem   ib_buffer_pool  ib_logfile1  mysqld_safe.pid     public_key.pem   sys
ca.pem      coam.err         ibdata1         ibtmp1       performance_schema  server-cert.pem
Tue May 17 10:31:52 yzhang@coam:/data/home/data/mysql$ sudo service mysql restart       ======> 重启 Mysql 数据库服务
Tue May 17 11:08:40 yzhang@coam:/data/home/data/mysql$ ls
auto.cnf    client-cert.pem  coam.pid        ib_logfile0  mysql             mysqld_safe.pid     public_key.pem   sys
ca-key.pem  client-key.pem   ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema  server-cert.pem
ca.pem      coam.err         ibdata1         ibtmp1       mysql-bin.index   private_key.pem     server-key.pem

导入从主服务器导出的数据库 hello,然后

导入 master 导出的数据库:

分别创建需要导入的数据库

mysql> CREATE DATABASE CoamDatabase DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE CoamInfo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE CoamSNS DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE CommonData DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE Wordpress DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE IM_Ejabberd DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

mysql> source backup.sql;

# 以下方式分别导入数据库
mysql -uroot -p CoamDatabase < backup.sql
mysql -uroot -p CoamInfo < backup.sql
mysql -uroot -p CoamSNS < backup.sql
mysql -uroot -p CommonData < backup.sql
mysql -uroot -p Wordpress < backup.sql
mysql -uroot -p IM_Ejabberd < backup.sql

1. 在从 slave 服务器配置连接 master 信息:

mysql> slave stop;
mysql> CHANGE MASTER TO
-> MASTER_HOST='43.241.222.110', //主服务器的IP地址
-> MASTER_USER='syBackup', //同步数据库的用户
-> MASTER_PASSWORD='******', //同步数据库的密码
-> MASTER_CONNECT_RETRY=60,  // 如果从服务器发现主服务器断掉,重新连接的时间差(秒)
-> MASTER_LOG_FILE='mysql-bin.000001', //主服务器二进制日志的文件名(前面要求记住的 File 参数)
-> MASTER_LOG_POS=98; //日志文件的开始位置(前面要求记住的 Position 参数)
mysql> CHANGE MASTER TO MASTER_HOST='43.241.222.110', MASTER_USER='syBackup', MASTER_PASSWORD='******', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;
mysql > slave start;

重启 master, slave:

service mysqld restart

查看 slave 状态:

进入从服务器 slave mysql:

mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Connecting to master
                Master_Host: 43.241.222.110
                Master_User: syBackup
                Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
            Relay_Log_File: mysqld-relay-bin.000001
            Relay_Log_Pos: 98
    Relay_Master_Log_File: mysql-bin.000001
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
            Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
wik,trac,wzjp_net
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 98
            Until_Log_Pos: 0
    Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

注意一定要有下面两项(IO线程和SQL线程),没有的话查看错误日志(less /var/log/mysqld.log):

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果显示这个状态 则稍等一会儿

Slave_IO_State: Waiting for master to send event

测试 master 服务器

mysql> use hello;
Database changed
mysql> create table test(id int);
mysql> insert int test set id=1;

mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000002  # 注意这里
        Position: 276    # 注意这里
    Binlog_Do_DB:
CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
Binlog_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
wik,trac,wzjp_net

1 row in set (0.00 sec)

测试 slave 服务器

mysql> use hello;
mysql> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| test            | 
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
                Master_Host: 43.241.222.110
                Master_User: syBackup
                Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000002  # 跟 master 一样
        Read_Master_Log_Pos: 276  # 跟 master 一样
            Relay_Log_File: mysqld-relay-bin.000003
            Relay_Log_Pos: 413
    Relay_Master_Log_File: mysql-bin.000002
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
            Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
wik,trac,wzjp_net
                Last_Errno: 0
                Last_Error: 
            Skip_Counter: 0
        Exec_Master_Log_Pos: 276
            Relay_Log_Space: 413
    Seconds_Behind_Master: 0
1 row in set (0.00 sec)

主A服务器从B服务器配置就已经顺利完成了 –A从B主

---

二. 下面开始配置从A服务器主B服务器

• 从服务器 A: 43.241.222.110
• 主服务器 B: 42.96.194.60

一、设置主服务器 A 需要复制的数据库
打开主服务器 B 的 Mysql 配置文件 /etc/mysql/my.cnf 并添加以下配置项

###############################################################
server-id=2
log-bin=mysql-bin

### as master ###
read-only=0 #主机读写权限,读写都可

binlog-do-db=CoamDatabase #记录日志的数据库:需要的备份数据,多个写多行
binlog-do-db=CoamInfo
binlog-do-db=CoamSNS
binlog-do-db=form
binlog-do-db=CommonData
binlog-do-db=Wordpress
binlog-do-db=IM_Ejabberd

binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

# 自增字段奇数递增,防止冲突(1, 3, 5, ...,)
auto-increment-increment = 10 # 每次递增的步长
auto-increment-offset = 2 # 初始值
### as slave ###
# 之前配置过,这里保持不变
#######################################################################

重启 mysql:

serivce mysqld restart

在主 B 服务器(master)下创建给从A服务器 (slave)登录用的用户名密码

mysql> GRANT REPLICATION SLAVE ON *.* TO 'syBackup'@'43.241.222.110' IDENTIFIED BY '******';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status\G;
*************************** 1. row ***************************
         File: mysql-bin.000003
         Position: 468
     Binlog_Do_DB: CoamDatabase,CoamInfo,CoamSNS,forum,CommonData,Wordpress,IM_Ejabberd
 Binlog_Ignore_DB: mysql,test,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.01 sec)
mysql> UNLOCK TABLES;

---

在从服务器 A 的服务器配置登陆主服务器 B 中创建的复制用户登陆

mysql> slave stop;
mysql>  CHANGE MASTER TO MASTER_HOST='42.96.194.60', MASTER_USER='syBackup', MASTER_PASSWORD='yafei312', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000004, MASTER_LOG_POS=46702';
mysql> slave start;

修改从服务器A的 Mysql 配置文件 /etc/mysql/my.cnf 从配置信息

打开从服务器 A 的 Mysql 配置文件 /etc/mysql/my.cnf 并添加以下配置项

### as master ###

### as slave ###
replicate-do-db=CoamDatabase #只复制某个库,多个写多行
replicate-do-db=CoamInfo
replicate-do-db=CoamSNS
replicate-do-db=form
replicate-do-db=CommonData
replicate-do-db=Wordpress
replicate-do-db=IM_Ejabberd

replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

relay-log=mysqld-relay-bin # 开启日志中继
log-slave-updates # slave将复制事件写进自己的二进制日志
######################################################################

重启主服务器A、B并测试插入测试更新数据

从 A 服务器 43.241.222.110

mysql> show slave status\G;

---

最后的 A 服务器 配置文件 /etc/mysql/my.cnf

#######################################################
server-id=1 #主机id,整数
#开启二进制日志,并且名称为 /var/lib/mysql/mysql-bin.***
# 如果是个路径则,保存到该路径下(log-bin=/var/log/mysql-bin.log -> /var/log/mysql-bin.***)
log-bin=mysql-bin

### as master ###
read-only=0 #主机读写权限,读写都可以

binlog-do-db=CoamDatabase #记录日志的数据库:需要的备份数据,多个写多行
binlog-do-db=CoamInfo
binlog-do-db=CoamSNS
binlog-do-db=form
binlog-do-db=CommonData
binlog-do-db=Wordpress
binlog-do-db=IM_Ejabberd

binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

# 自增字段奇数递增,防止冲突(1, 11, 21, ...,)
auto-increment-increment = 10 # 每次递增的步长
auto-increment-offset = 1 # 初始值

### as slave ###
replicate-do-db=CoamDatabase #只复制某个库,多个写多行
replicate-do-db=CoamInfo
replicate-do-db=CoamSNS
replicate-do-db=form
replicate-do-db=CommonData
replicate-do-db=Wordpress
replicate-do-db=IM_Ejabberd

replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

relay-log=mysqld-relay-bin # 开启日志中继
log-slave-updates # slave将复制事件写进自己的二进制日志
#5.5
#log-slave-updates = ON
#5.1
#log-slave-updates = 1
#######################################################

最后的 B 服务器配置文件 /etc/mysql/my.cnf

###############################################################
server-id=2
log-bin=mysql-bin

### as master ###
read-only=0 #主机读写权限,读写都可以

binlog-do-db=CoamDatabase #记录日志的数据库:需要的备份数据,多个写多行
binlog-do-db=CoamInfo
binlog-do-db=CoamSNS
binlog-do-db=form
binlog-do-db=CommonData
binlog-do-db=Wordpress
binlog-do-db=IM_Ejabberd

binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

# 自增字段奇数递增,防止冲突(2, 12, 22, ...,)
auto-increment-increment = 10 # 每次递增的步长
auto-increment-offset = 2 # 初始值
### as slave ###
replicate-do-db=CoamDatabase #只复制某个库,多个写多行
replicate-do-db=CoamInfo
replicate-do-db=CoamSNS
replicate-do-db=form
replicate-do-db=CommonData
replicate-do-db=Wordpress
replicate-do-db=IM_Ejabberd

replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

relay-log=mysqld-relay-bin # 开启日志中继
log-slave-updates # slave将复制事件写进自己的二进制日志
#5.5
#log-slave-updates = ON
#5.1
#log-slave-updates = 1
###############################################################

---

其它

1. 安装MySQL.首先要在两台服务器上安装MySQL,完成之后应该确认能否两台服务器能否互相访问. 这是因为缺省的 my.cnf 设置有 bind-address = 127.0.0.1,这条语句应该被注释掉.
2. 创建账号.数据库中缺省的帐户的host值是localhost,所以应该创建一个可以远端访问的帐号.比如:

root@’%’ or root@10.x.x.x

---

Mysql双机热备份日常维护及问题分析

好不容易搭建了Mysql服务器双机热备份后,在 phpMyAdmin 管理后台更新数据,出现如下错误:

mysql Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.

因为我使用mysql2redis和udf搭建了Mysql自动同步到Redis数据库缓存,双机热备份后,在本地服务器可以及时更新当前主机的Redis数据,但是从服务器不能自动触发udf更新操作导致出现上面的错误

首先需要使用命令 show slave status\G; 检查两个数据库的的同步状态是否正确,保证没有 插入. 更新. 打开临时表 等操作,在两个服务器执行以下命令

在两个服务器上停止从服务的任务:

stop slave;
flush tables with read lock;

在两个主服务器上执行锁表并修改日志记录方式:

flush tables with read lock;
set global binlog_format='MIXED';
unlock tables;

最后在两个服务器执行以下命令启动从服务

unlock tables;
start slave;

重新修改,发现没有出现以上错误 参考 MySQL binlog format dilemma?

Statement 【基于SQL语句的复制(statement-based replication, SBR)】

Row 【基于行的复制(row-based replication, RBR)】 方式不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题.

MIXED 【混合模式复制 (mixed-based replication, MBR)】是以上(Statement和Row)两者的综合.在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种.新版本中的Statment level还是和以前一样,仅仅记录执行的语句.而新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更.
参考 MYSQL复制参数 binlog_format
由于将 binlog_format 改成 MIXED 或者 ROW 不会导致出错,但是不会再从服务器执行 udf 配置的redis复制命令(测试可以触发执行在当前数据库中的表的修改操作),所以考虑将双主机的Mysql服务器的Redis修改操作统一提交到一台主Redis服务器,搭建Redis主从同步机制

---

日常问题分析

某次不小心改了B服务器的一条数据后,A服务器一直没有更新过来,反过来A服务器修改B服务器没有更新,为了保持数据一致,于是各种折腾,清空表. 删除表再重建仍是没有解决问题并导致其他数据库的表也不能顺利同步

• 在 A 服务器上查看 A的从服务同步状态,提示如下错误

mysql> show slave status\G;
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 42.96.194.60
 Master_User: syBackup
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000028
 Read_Master_Log_Pos: 31876
 Relay_Log_File: mysqld-relay-bin.000063
 Relay_Log_Pos: 26437
 Relay_Master_Log_File: mysql-bin.000028
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
 wik,trac,wzjp_net Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1062
 Last_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'Com monData'. Query: 'INSERT INTO `CommonData`.`Sarah_PictureManager` (`pictureId`, `pictureImgShow`, `pictureGroup`, `pictureTitle`, `pictureSubTitle`, `picturePostil`, `pubTime`) VALUES (NULL, '', '', '尚无标注', '尚无子标注', '尚未批注', CURRENT_TIMESTAMP)' Skip_Counter: 0
 monData'
 Exec_Master_Log_Pos: 30844
 Relay_Log_Space: 27806
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 1062
 Last_SQL_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'Com monData'. Query: 'INSERT INTO `CommonData`.`Sarah_PictureManager` (`pictureId`, `pictureImgShow`, `pictureGroup`, `pictureTitle`, `pictureSubTitle`, `picturePostil`, `pubTime`) VALUES (NULL, '', '', '尚无标注', '尚无子标注', '尚未批注', CURRENT_TIMESTAMP)' Replicate_Ignore_Server_Ids:
 monData'
 Master_Server_Id: 2
 Master_UUID: 1c0ea40b-56be-11e5-8329-00163e043c4f
 Master_Info_File: /data/home/data/mysql/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State:
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp: 150922 17:36:35
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set:
 Executed_Gtid_Set:
 Auto_Position: 0
 1 row in set (0.00 sec)

ERROR:
 No query specified

• 在 B 的服务器同样查看B服务器的从服务器同步状态

mysql> show slave status\G;
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 43.241.222.110
 Master_User: syBackup
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000022
 Read_Master_Log_Pos: 52902
 Relay_Log_File: mysqld-relay-bin.000065
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000017
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
 wik,trac,wzjp_net Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1050
 Last_Error: Error 'Table 'Sarah_PictureManager' already exists' on query. Default database
 : 'CommonData'. Query: 'RENAME TABLE `CommonData`.`Sarah_PictureManager` TO `test`.`Sarah_PictureManager`'
 Skip_Counter: 0
 Exec_Master_Log_Pos: 120
 Relay_Log_Space: 12643
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 1050
 Last_SQL_Error: Error 'Table 'Sarah_PictureManager' already exists' on query. Default database
 : 'CommonData'. Query: 'RENAME TABLE `CommonData`.`Sarah_PictureManager` TO `test`.`Sarah_PictureManager`'
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 Master_UUID: 811367ec-52b9-11e5-a8f5-0022f75fc65a
 Master_Info_File: /data/home/data/mysql/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State:
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp: 150922 17:40:05
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set:
 Executed_Gtid_Set:
 Auto_Position: 0
 1 row in set (0.00 sec)

ERROR:
 No query specified

均有如下相同的错误状态

Slave_IO_Running: Yes
Slave_SQL_Running: No # 正常的为Yes

于是猜想是双服务器的数据库不能同步,并且有很多修改,不知从哪里开始恢复,网上建议

有时候由于BUG或者在从服务器执行了写操作可能会造成键重复错误,错误信息如下:

Error ‘Duplicate entry ...’ for key ... on query

此时最好手动确认并删除从服务器上的无效数据,然后从主服务器复制正确数据,如果错误仍然不能解决,可以在从服务器使用 SET GLOBAL sql_slave_skip_counter ,如下:

mysql> SET GLOBAL sql_slave_skip_counter = 1;  # 客户端运行,用来跳过几个事件,只有当同步进程出现错误而停止的时候才可以执行.
mysql> START SLAVE; # 记得一定要执行,不然后续查看 从服务器的状态不会及时更新过来
mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 43.241.222.110
 Master_User: syBackup
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000022
 Read_Master_Log_Pos: 52902
 Relay_Log_File: mysqld-relay-bin.000083
 Relay_Log_Pos: 2158
 Relay_Master_Log_File: mysql-bin.000022
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
wik,trac,wzjp_net Replicate_Do_Table: 
 Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
 Skip_Counter: 0
 Exec_Master_Log_Pos: 52902
 Relay_Log_Space: 2495
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
 Master_SSL_Cert: 
 Master_SSL_Cipher: 
 Master_SSL_Key: 
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 1
 Master_UUID: 811367ec-52b9-11e5-a8f5-0022f75fc65a
 Master_Info_File: /data/home/data/mysql/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

注:如果有多个错误,可能需要依次循环执行多次(提醒:主从服务器数据可能因此不一致,遇到这样的情况可以使用 pt-table-checksum 和 pt-table-sync 检查并修复从服务器数据).

发现没有错误则停止继续执行 SET GLOBAL sql_slave_skip_counter = 1; 否则执行过多会导致新插入的数据不会更新(未验证)

---

• 问题:主从复制不止何故停止了,我该怎么办?

答案:复制错误多半是因为日志错误引起的,所以首先要搞清楚是主日志错误还是中继日志错误,从错误信息里一般就能判断,如果不能可以使用类似下面的mysqlbinlog命令:

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null
shell> mysqlbinlog <RELAY_BINLOG_FILE> > /dev/null

如果没有错误,则不会有任何输出,反之如果有错误,则会显示出来.

• 同步执行SQL错误,同步自动终止问题

在一台服务器更新了一个字段值,但是在从服务器没有更新过来,于是在从服务器查看状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 42.96.194.60
                  Master_User: syBackup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 176357
               Relay_Log_File: mysqld-relay-bin.000005
                Relay_Log_Pos: 4776
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
          Replicate_Ignore_DB: mysql,test,information_schema,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table Wordpress.wp_options; , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 4884                 Skip_Counter: 0          Exec_Master_Log_Pos: 4563              Relay_Log_Space: 174245              Until_Condition: None               Until_Log_File:                Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:           Master_SSL_CA_Path:              Master_SSL_Cert:            Master_SSL_Cipher:               Master_SSL_Key:        Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 1062               Last_SQL_Error: Could not execute Write_rows event on table Wordpress.wp_options; , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 4884
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4563
              Relay_Log_Space: 174245
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table Wordpress.wp_options; , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 1e14bbca-1b46-11e6-a21a-00163e043c4f
             Master_Info_File: /data/home/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 160518 23:30:42
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

ERROR:
No query specified

发现 Slave_SQL_Running: No 问题 并且有一条SQL执行错误 Last_SQL_Error: Could not execute Write_rows event on table Wordpress.wp_options; , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000007, end_log_pos 4884

1. 执行如下语句跳过一个 SQL 语句执行错误,问题解决,如果有多个错误,以下可以多执行几次

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

1. 如果在配置文件设定自动跳过 SQL 语句执行错误自动终止功能,可以配置 /etc/mysql/my.cnf

slave-skip-errors = 1062

以上配置全局忽略1062错误 MySQL Skip Duplicate Replication Errors
记一次MySQL主从同步错误处理

• 如果是主日志错误,需要手动找到正确的日志信息,重新 CHANGE MASTER TO 即可:

mysql> CHANGE MASTER TO
       MASTER_LOG_FILE='<GOOD_LOG_FILE>',
       MASTER_LOG_POS=<GOOD_LOG_POS>;
mysql> START SLAVE;

• 如果是中继日志错误,只要在从服务器使用SHOW SLAVE STATUS结果中的日志信息重新CHANGE MASTER TO即可,系统会抛弃当前的中继日志,重新下载:

mysql> CHANGE MASTER TO
       MASTER_LOG_FILE='<Relay_Master_Log_File>',
       MASTER_LOG_POS=<Exec_Master_Log_Pos>;
       
mysql> CHANGE MASTER TO MASTER_LOG_FILE='<Relay_Master_Log_File>', MASTER_LOG_POS=<Exec_Master_Log_Pos>;

mysql> START SLAVE;

至于为什么使用的是 Relay_Master_Log_File & Exec_Master_Log_Pos,参见概述.

• MySQL主从失败, 错误 Got fatal error 1236 解决方法

1. 在从服务器

• 查看当前服务器作为从服务器同步状态

yzhang@coam:~$ mysql -u zhangyanxi -p
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 42.96.194.60
                  Master_User: syBackup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005, MASTER_LOG_POS=154
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005, MASTER_LOG_POS=154
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
          Replicate_Ignore_DB: mysql,test,information_schema,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 1e14bbca-1b46-11e6-a21a-00163e043c4f
             Master_Info_File: /data/home/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 160518 11:29:06
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

可以看到 Slave_IO_State 和 Slave_IO_Running 都不正常,而 Read_Master_Log_Pos 为 4

• 查看从服务器错误日志文件

yzhang@coam:~$ sudo vi /data/home/data/mysql/coam.err
2016-05-18T03:29:06.947349Z 30 [ERROR] Error reading packet from server for channel '': Could not find first log file name in binary log index file (server_errno=1236)
2016-05-18T03:29:06.947405Z 30 [ERROR] Slave I/O for channel '': , Error_code: 1236
2016-05-18T03:29:06.947417Z 30 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005, MASTER_LOG_POS=154', position 4

• 在主服务器查看主服务器同步状态

mysql> show master status\G;
*************************** 1. row ***************************
         File: mysql-bin.000005
         Position: 468
     Binlog_Do_DB: CoamDatabase,CoamInfo,CoamSNS,forum,CommonData,Wordpress,IM_Ejabberd
 Binlog_Ignore_DB: mysql,test,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.01 sec)

• 回到从服务器设置同步位置-记住,不要在这个时候重启主服务器,否则 File: mysql-bin.000005 会改变

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=468;
mysql> start slave;
mysql> show slave status\G;
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 42.96.194.60
 Master_User: syBackup
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000005
 Read_Master_Log_Pos: 468
 Relay_Log_File: mysqld-relay-bin.000065
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000017
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: CoamDatabase,CoamInfo,CoamSNS,form,CommonData,Wordpress,IM_Ejabberd
 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,collie,ding,inotseeyou_com,pi
 wik,trac,wzjp_net Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1050
 Last_Error: Error 'Table 'Sarah_PictureManager' already exists' on query. Default database
 : 'CommonData'. Query: 'RENAME TABLE `CommonData`.`Sarah_PictureManager` TO `test`.`Sarah_PictureManager`'
 Skip_Counter: 0
 Exec_Master_Log_Pos: 120
 Relay_Log_Space: 12643
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 1050
 Last_SQL_Error: Error 'Table 'Sarah_PictureManager' already exists' on query. Default database
 : 'CommonData'. Query: 'RENAME TABLE `CommonData`.`Sarah_PictureManager` TO `test`.`Sarah_PictureManager`'
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 Master_UUID: 811367ec-52b9-11e5-a8f5-0022f75fc65a
 Master_Info_File: /data/home/data/mysql/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State:
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp: 150922 17:40:05
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set:
 Executed_Gtid_Set:
 Auto_Position: 0
 1 row in set (0.00 sec)

ERROR:
 No query specified

MySQL主从失败, 错误Got fatal error 1236解决方法

• 出现以下错误

Slave SQL for channel ‘’: Slave failed to initialize relay log info structure from the repository, Error_code: 1872

是因为 relay-log 有问题,这时候修改从服务器mysql配置,在[mysqld]中加入relay-log-recovery=1,这样表示,服务器启动之后,删除所有已有的relay日志,重新接收主库的relay日志

Mysql之主从复制

• 问题:主服务器宕机了,如何把从服务器提升会主服务器?

答案:在一主多从的环境总,需选择数据最新的从服务器做新的主服务器.
如下图所示:

promotion

在一主(Server1)两从(Server2,. Server3)环境中,Server1宕机后,等到Server2和Server3把宕机前同步到的日志都执行完,比较Master_Log_File和Read_Master_Log_Pos就可以判断出谁快谁慢,
因为Server2从Server1同步的数据(1582)比Server3从Server1同步的数据(1493)新,所以应该提升Server2为新的主服务器,
那么Server3在CHANGE MASTER TO到Server2的时候应该使用什么样的参数呢?1582-1493=89,而Server2的最后的二进制日志位置是8167,所以答案是8167-89=8078.

参考列表

MySQL复制的概述. 安装. 故障. 技巧. 工具
Slave_SQL_Running: No mysql同步故障
Repair Replication
MySQL主从服务器数据不同步