出产环境mysql主主同步主键抵触处理51CTO博客 - 众发娱乐

出产环境mysql主主同步主键抵触处理51CTO博客

2019-03-06 10:21:42 | 作者: 宇熙 | 标签: 同步,主主,主上 | 浏览: 409

收到短信报警,两台数据库都报slave同步失利了,先阐明一下环境,架构:lvs+keepalived+amoeba+mysql,主主仿制,单台写入,

主1:192.168.0.223(写)主2:192.168.0.230好吧,先show slave status \G看一下同步失利的详细报错吧登录主2库检查:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.223
Master_User: slave
Master_Port: 13204
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 50419
Relay_Log_File: mysqld-relay-bin.000014
Relay_Log_Pos: 34626
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema,test,mysql,information_schema,performance_schema,test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error Duplicate entry 1329544 for key PRIMARY on query. Default database: data. Query: insert into kn_chongzhi(orderid,aa,buyNum,state,type,create_time,fac,cc,flag)
values(20130702173025036581,15935779926,1,0,SJ,1372757425,30.27,30,100)
Skip_Counter: 0
Exec_Master_Log_Pos: 34480
Relay_Log_Space: 51171
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 1329544 for key PRIMARY on query. Default database: data. Query: insert into kn_chongzhi(orderid,aa,buyNum,state,type,create_time,fac,cc,flag)
values(20130702173025036581,15935779926,1,0,SJ,1372757425,30.27,30,100)
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)

尼玛,苦逼的又是主键抵触,先检查一下这张表的结构:

mysql> desc  kn_chongzhi;
+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | int(10)         | NO   | PRI | NULL    | auto_increment |
| aa    | varchar(32)     | NO   | MUL | NULL    |                |
| bizOfferId  | varchar(32)     | NO   |     | NULL    |                |
| number      | varchar(20)     | NO   | MUL | NULL    |                |
| cc       | float(10,2)     | NO   |     | NULL    |                |
| fac   | float(10,2)     | YES  |     | 0.00    |                |
| buyNum      | int(10)         | NO   |     | NULL    |                |
| state       | tinyint(4)      | NO   |     | 0       |                |
| type        | enum(SJ,QB) | NO   |     | SJ      |                |
| create_time | int(11)         | NO   |     | NULL    |                |
| update_time | int(11)         | NO   |     | NULL    |                |
| flag        | int(10)         | NO   |     | 0       |                |
+-------------+-----------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
想必我们现已知道问题是这么发生的了,这儿我再大体的说一下,或许有些人还不理解哈,回头看前面的架构,引起 这个问题的原因是主1的网络颤动,导致amoeba把写切到了主2,主1的网络好了,写又切回了主1,因为主键ID是自曾的,所以就呈现了这个问题,我举个比如:开端是写主1的,现已写6条数据(id=1、2、3、4、5、6),俄然主1网络颤动,开端在主2写了三条(id=7、8、9),主1的网络又康复了,写又在主1上了(id=7、8、9、10、。。。。),这时,主1要把id=7、8、9、10.。。。。的数据仿制给主2,主2 要把id=7、8、9三条数据仿制给主1,这不就傻逼了吗?处理的进程:1、在两个库上stop slave;2、在主2上履行select * from kn_chongzhi where id>=1329544\G (检查在主2上写了几条数据)
mysql> select * from kn_chongzhi where id>=1329544\G
*************************** 3661. row ***************************
id: 1329545
aa: 20130702213504529562
bizOfferId: DK201307021139565210
number: 13991056094
cc: 30.00
fac: 30.22
buyNum: 1
state: 2
type: SJ
create_time: 1372772104
update_time: 1372772474
flag: 100
*************************** 3662. row ***************************
id: 1329546
aa: 20130702213506629648
bizOfferId: DK201307021139588209
number: 15511391791
cc: 30.00
fac: 30.17
buyNum: 1
state: 0
type: SJ
create_time: 1372772106
update_time: 0
flag: 100
*************************** 3663. row ***************************
id: 1329547
aa: 20130702213516595293
bizOfferId: DK201307021139758209
number: 13615611693
cc: 100.00
fac: 99.85
buyNum: 1
state: 2
type: SJ
create_time: 1372772116
update_time: 1372772315
flag: 101

 

3、在主2上delete from kn_chongzhi where id>=1329544;  并设置自曾ID从1329545开端
mysql> delete from kn_chongzhi where id>=1329544;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table kn_chongzhi auto_increment=1329545;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
4、主2上slave start,show slave  status \G,发现主2同步主1现已ok了;5、在主2上show master  status \G,获取binlog文件名和Position点,在主1上从头change master6、把上面三条数据保存好,发给程序猿手到录入主1,PS:当然,假如我按一下设置,必定不会呈现这个问题,假如事务有要求,ID有必要接连,那就不能设置这两个参数了:
主1:
auto-increment-increment=2
auto-increment-offset=1
主2:
auto-increment-increment=2
auto-increment-offset=2

 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表众发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章