mysql数据同步迁移_【MySQL】MySQL数据库主从配置之数据迁移及同步报错故障处理...

这篇主要讲的是在数据库原有数据的情况下配置主从，即主库的数据怎么迁移到从库，一共有两种方式，这里都会讲解，并且会指出推荐使用哪种方式

需要注意的是，这篇并不讲解主从的配置方式，主要讲的是数据的迁移方案，如果需要了解基本配置方式的话请移步到：

在我们一般配置主从的步骤大概如下(有数据的情况)：

1、检查两个服务器上数据库的版本

2、在配置文件上配置相关的信息，如：server-id等

3、从主库上导数据到从库(在无数据的情况下可忽略)

4、从库上使用change master配置主从

5、start slave开启主从同步

6、查看主从状态

除了第三个步骤外，其他都可通过上面提供的链接进行了解。这也是这篇文章要讲的内容。

从主库上导数据到从库，一般有两种方法(针对需要配置主从的情况)：

方法一：

1、先使用show master查看binlog文件名称和position

2、在主库使用mysqldump或者其他第三方工具(如Navicat的结构同步和数据同步)导出数据

3、在从库导入刚刚导出的数据(Navicat的可忽略)

问题：

如果可停服务器或者是新数据库配置主从的话这种方法还没什么问题，但对于不能停服务器的情况如果还这样导数据的话会出现比较麻烦的问题(当然后面会给出最基本的解决方案)

此话怎讲呢，首先我们回去看看这个步骤，先记录binlog，然后再导数据，那么记录binlog和导数据之间存在一个时间差，在这个时间里面，很有可能会有数据在进出，所以就会导致一个问题，就是在我们将这份数据导入到从库并start slave之后会提示数据重复(什么主键冲突、什么找不到要删除的数据之类的)

解决方案：

关于这个解决方案可以有多种：

一、一个个冲突解决：

问题1：记录删除失败(在master上删除一条记录，而在slave上提示找不到)：

提示：

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;

Can't find record in 't1',

Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

the event's master log mysql-bin.000006, end_log_pos 254

解决方案：master要删除一条记录，而slave上找不到报错，这种情况主都已经删除了，那么从机可以直接跳过。

stop slave;

set global sql_slave_skip_counter=1;

start slave;

问题2：主键重复/唯一键重复，在slave已经有该记录，又在master上插入了同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1;

Duplicate entry '2' for key 'PRIMARY',

Error_code: 1062;

handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

解决方案：在slave上用desc hcy.t1; 先看下表结构确认主键/唯一键，删除重复的记录，当然如果确定slave上的数据是和master要插入的数据是一致的，可通过第一步的方法跳过

mysql> delete from t1 where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

问题3：更新丢失：在master上更新一条记录，而slave上找不到，丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;

Can't find record in 't1',

Error_code: 1032;

handler error HA_ERR_KEY_NOT_FOUND;

the event's master log mysql-bin.000010, end_log_pos 794

解决方案：

在master上，用mysqlbinlog 分析下出错的binlog日志在干什么。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794

#120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F

### UPDATE hcy.t1

### WHERE

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */

### SET

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */

# at 794

#120302 12:08:36 server id 22 end_log_pos 821 Xid = 60

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

在slave上，查找下更新后的那条记录，应该是不存在的。

mysql> select * from t1 where id=2;

Empty set (0.00 sec)

然后再到master查看

mysql> select * from t1 where id=2;

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

| id | name |

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

| 2 | BTV |

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

1 row in set (0.00 sec)

把丢失的数据在slave上填补，然后跳过报错即可。

mysql> insert into t1 values (2,'BTV');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 where id=2;

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

| id | name |

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

| 2 | BTV |

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

1 row in set (0.00 sec)

mysql> stop slave ;set global sql_slave_skip_counter=1;start slave;

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

二、使用MySQL的参数来解决：

1、静态服务器设置，需要重启MySQL

[mysqld]

slave_skip_errors=1032,1064

重启MySQL之后，会自动加载配置文件，同步自动跳过更新，与主键冲突错误。

2、动态设置跳过错误(推荐)：

set global slave_exec_mode =idempotent;

set global slave_exec_mode =strict;

严格执行策略，默认是strict。大多数情况下遇到错误，同步就会终止。等待错误解决。

set global slave_exec_mode =idempotent;

这个设置，可以允许同步跳过

duplicate-key and no-key-found错误

当这个开启之后，相关的冲突解决原理如下：

1.insert场景

此时insert into语句在备机的效果就跟replace into一样,但却并不是把insert into转换成replace into来执行,分两种情况:

a.MySQL配置成autocommit,直接一条insert into ...

如这样的insert

insert into test set c1='a',c2='b';

此时insert into语句在备机执行时假如遇到主键冲突就先转化为delete再insert

delete from test where c1='old_value' and c2='old_value';

insert into test set c1='a',c2='b';

假如遇到非主键的唯一键冲突就转换为update

update test set set c1='a',c2='b' where c1='old_value' and c2='old_value';

b.当显示开始事务时(begin...insert into...commit;)

如这样的sql

begin;

insert into test set c1='a',c2='b';

commit;

此时begin...commit里的insert into语句在备机执行时假如遇到主键冲突、唯一键冲突都是先转化为delete再insert

begin;

delete from test where c1='old_value' and c2='old_value';

insert into test set c1='a',c2='b';

commit;

2.update场景

当备机不存在要更新的记录,这条update不执行

3.delete场景

同update场景一样，备机啥也不干

这个参数很好用，但要注意的是：使用冥等模式时表要有主键

冥等模式并不是万能的,除了不能对DDL操作冥等,对字段长度不同导致的错误也不是冥等(譬如主机一个字段是char(20)而备机是char(10)),还有一个限制就是表有主键才会对insert的冥等设置有效:因为insert的冥等行为是通过主键来判断备机是否有重复值从而产生覆写操作,如果表没有主键,则备机即使设了冥等也可能会比主机多重复数据。

好了，终于总结完方法一以及相关问题的解决方案了，咋们接着：

方法二：

主角终于要出场了：

在我们导数据之前不需要记录binlog，而在mysqldump导出数据的时候使用master-data，在mysqldump中加入这个参数后，会自动将binlog以change master语句的方式记录在导出的sql文件中，然后我们在从库导入这个文件的时候就会自动配置该从库对应的主库的binlog信息了，然后我们再使用change master配置相关的IP什么的就好了

但这里有坑：

坑1：在slave导完数据时，在Navicat上会先删除原有的数据，然后再将数据写入，在我第一次使用这个参数的时候，数据一导完我就上slave的库上看，发现数据不对劲，而且一直在减少，但过了一阵子后，他会自动还原，所以这个可以不管他，只是当个提示丢在这里

坑2：关于master-data=1和master-data=2

当master-data=1时，binlog相关信息会以注释的形式写入sql文件，即在slave导入的时候不会执行该语句，而当=2的时候会取消注释

坑3：会锁库：

使用--single-transaction参数就好了，再不行就--skip-add-locks

参考语句：

mysqldump -uroot -p123456 --events --master-data=2 --single-transaction db_test tb_order > /usr/backup/test.sql

关于上面提到的导数据的两种方法，这里还是比较推荐第二种方法，直接mysqldump导出来，然后再导入到从库，然后配置相关IP等参数就好了

文章难免会有误，如发现错误请拍砖，鞠躬......