为什么双主只建议单节点写入?

本文永久地址:
http://fuxkdb.com/2017/10/31/%E4%B8%BA%E4%BB%80%E4%B9%88%E5%8F%8C%E4%B8%BB%E5%BB%BA%E8%AE%AE%E5%8D%95%E8%8A%82%E7%82%B9%E5%86%99%E5%85%A5/

为什么双主只建议单节点写入

通过下面的案例,你应该可以明白为啥了

问题描述

线上一套双主环境

CentOS release 6.8 (Final)Server version: 5.5.56binlog_format : STATEMENTtx_isolation  : REPEATABLE-READ主1 server_id : 32主2 server_id : 33

有一个表,每分钟load data. 由于一天会插入近1亿行数据,导致磁盘使用率增长很快,所以现在用计划任务每四天切换一次表

#mobile_ad_50表切换0 3 2,6,10,14,19,23,27 * * source /etc/profile;source /root/.bash_profile;sh /data/scripts/bin/mobile_ad_50.sh >>/data/scripts/log/mobile_ad_50.log

切换逻辑是,先rename源表,再重建表

${DB_COMMAND} dbe8je6i4c3gjd50 -ss -e "drop table mobile_ad_50_20170531"echo "drop ok"${DB_COMMAND} dbe8je6i4c3gjd50 -ss -e "rename table mobile_ad_50 to mobile_ad_50_20170531"echo "rename ok"${DB_COMMAND} dbe8je6i4c3gjd50 -ss -e "CREATE TABLE mobile_ad_50 (

表结构

*************************** 1. row ***************************       Table: mobile_ad_50Create Table: CREATE TABLE `mobile_ad_50` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',  `dtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间段 年月日时',  `union_id` int(11) unsigned NOT NULL DEFAULT '1' COMMENT '媒体ID',  `ad_id` varchar(100) DEFAULT NULL COMMENT '广告位ID',  `ifa` varchar(50) NOT NULL COMMENT 'ifa',  `mac` varchar(50) NOT NULL COMMENT 'mac',  `cb_url` varchar(1000) NOT NULL COMMENT '回调地址',  `state` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否激活',  `domain` varchar(30) NOT NULL COMMENT '游戏域名',  `game_code` varchar(50) NOT NULL DEFAULT '' COMMENT '游戏编码',  `union_app_id` char(50) NOT NULL DEFAULT '' COMMENT '渠道商的appid',  `openudid` char(50) NOT NULL DEFAULT '' COMMENT '开源广告标示符',  `is_send` tinyint(1) NOT NULL DEFAULT '0' COMMENT '同步次数a',  `ip` bigint(20) NOT NULL DEFAULT '0' COMMENT '点击ip',  `actip` bigint(20) NOT NULL DEFAULT '0' COMMENT '激活ip',  `opentime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '打开时间',  `acttime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '激活时间',  `is_open` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否打开',  PRIMARY KEY (`id`),  KEY `ifa` (`ifa`),  KEY `d_u_s` (`domain`,`union_id`,`state`),  KEY `union_id` (`union_id`),  KEY `mac` (`mac`),  KEY `dtime` (`dtime`),  KEY `ip` (`ip`),  KEY `actip` (`actip`),  KEY `union_app_id` (`union_app_id`),  KEY `openudid` (`openudid`),  KEY `state` (`state`),  KEY `acttime` (`acttime`)) ENGINE=InnoDB AUTO_INCREMENT=6154739813 DEFAULT CHARSET=utf8 COMMENT='手机广告'1 row in set (0.00 sec)

开发说load只在主1执行,并且这个表数据都是通过load进来的,然后有些update,就再没有其他insert语句了
现在发现问题就是发现auto_increment异常增大,表中有两亿数据时,auto_increment列有51亿.

mysql> select id,dtime from mobile_ad_50 order by id limit 0,20;+------------+---------------------+| id         | dtime               |+------------+---------------------+|          2 | 2017-10-29 03:00:56 ||          4 | 2017-10-29 03:00:56 ||          6 | 2017-10-29 03:00:56 ||          8 | 2017-10-29 03:00:57 ||         10 | 2017-10-29 03:00:57 ||         12 | 2017-10-29 03:00:57 ||         14 | 2017-10-29 03:00:57 ||         16 | 2017-10-29 03:00:57 ||         18 | 2017-10-29 03:00:57 ||         20 | 2017-10-29 03:00:57 ||         22 | 2017-10-29 03:00:57 ||         43 | 0000-00-00 00:00:00 || 5135418110 | 2017-10-29 03:00:10 || 5135418111 | 2017-10-29 03:00:00 || 5135418113 | 2017-10-29 03:00:00 || 5135418115 | 2017-10-29 03:00:00 || 5135418117 | 2017-10-29 03:00:00 || 5135418119 | 2017-10-29 03:00:00 || 5135418121 | 2017-10-29 03:00:00 || 5135418123 | 2017-10-29 03:00:00 |+------------+---------------------+20 rows in set (0.00 sec)

看上面的查询是按照主键排序的,id=43一下就涨到51亿
我怀疑是取到了rename之前的表的自增值,查看了一下,还真是

mysql> select max(id) from mobile_ad_50_20170531;+------------+| max(id)    |+------------+| 5135418109 |+------------+1 row in set (0.00 sec)

自己分析原因一种是插入大量数据后rollback导致自增丢失,但是实际一次load data文件也就几千行,不可能丢失这么多.
第二个可能的原因是innodb_autoinc_lock_mode=1导致的bulk insert时自增丢失,但是我模拟了load 两亿数据,自增id也就2亿1千多万,也不可能丢失到51亿
我观察binlog,找2017-10-29 03:00:10左右的,搜索5135418110,找到

BEGIN/*!*/;# at 119255971#171029  3:00:10 server id 33  end_log_pos 119255999    IntvarSET INSERT_ID=5135418110/*!*/;# at 119255999#171029  3:00:10 server id 33  end_log_pos 119256303    Query   thread_id=227034786     exec_time=6     error_code=0SET TIMESTAMP=1509217210/*!*/;INSERT INTO mobile_ad_50 ( DTIME, UNION_ID, AD_ID, IFA, MAC, CB_URL, STATE, DOMAIN, GAME_CODE, OPENTIME, IS_OPEN ) VALUES ( '2017-10-29 03:00:10', 14800, '', '865166021645612', '', '', 0, '520050', 'android', 1509217210, 1 )/*!*/;# at 119256303#171029  3:00:10 server id 33  end_log_pos 119256330    Xid = 99754915507COMMIT/*!*/;

这条之前的一个SET INSERT_ID是9000多万

BEGIN/*!*/;# at 119256931#171029  3:00:11 server id 33  end_log_pos 119257377    Query   thread_id=227034792     exec_time=5     error_code=0SET TIMESTAMP=1509217211/*!*/;UPDATE wechat SET ACCESS_TOKEN='oEBoeHxXah3WEPAm_pJbQ-E2dVR2WVTkXn0mQ7YfY20grgt3k29-e518F1OELmHepZumWfxjNuDO7agVyNZZnkfG_xao-yWbfRv90x1ZoN_uQ1ogvsyJazUIVygldMcBBGWdAHAIND', ATOKEN_EXPIRES='2017-10-29 04:58:11', JSAPI_TICKET='kgt8ON7yVITDhtdwci0qeXl3u2D35Jw6KZsyUHYlRNK5VfCPXbMWbtYLkPOWe2hDlrlrly_FyrO3yjhXqhSezg', JSTICKET_EXPIRES='2017-10-29 04:58:11' WHERE id = 20/*!*/;SET INSERT_ID=90238492/*!*/;# at 119254523#171029  3:00:08 server id 33  end_log_pos 119254827    Query   thread_id=227034786     exec_time=8     error_code=0SET TIMESTAMP=1509217208/*!*/;INSERT INTO mobile_ad_55 ( DTIME, UNION_ID, AD_ID, IFA, MAC, CB_URL, STATE, DOMAIN, GAME_CODE, OPENTIME, IS_OPEN ) VALUES ( '2017-10-29 03:00:08', 10332, '', '863777021706899', '', '', 0, '520055', 'android', 1509217208, 1 )/*!*/;# at 119254827#171029  3:00:08 server id 33  end_log_pos 119254854    Xid = 99754915501COMMIT/*!*/;

开发不是说没有其他的insert语句了吗???怎么这里有一条,看一下server id 33还是主2同步过来的
在分析一下两边binlog

主1#171029  3:00:15 server id 32  end_log_pos 118195605    Query   thread_id=2636976237    exec_time=0     error_code=0SET TIMESTAMP=1509217215/*!*/;rename table mobile_ad_50 to mobile_ad_50_20170531#171029  3:00:10 server id 33  end_log_pos 119255999    IntvarSET INSERT_ID=5135418110/*!*/;# at 119255999#171029  3:00:10 server id 33  end_log_pos 119256303    Query   thread_id=227034786     exec_time=6     error_code=0SET TIMESTAMP=1509217210/*!*/;INSERT INTO mobile_ad_50 ( DTIME, UNION_ID, AD_ID, IFA, MAC, CB_URL, STATE, DOMAIN, GAME_CODE, OPENTIME, IS_OPE主2SET TIMESTAMP=1509217215/*!*/;SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;rename table mobile_ad_50 to mobile_ad_50_20170531/*!*/;#171029  3:00:10 server id 33  end_log_pos 268758734    IntvarSET INSERT_ID=5135418110/*!*/;# at 268758734#171029  3:00:10 server id 33  end_log_pos 268759038    Query   thread_id=227034786     exec_time=0     error_code=0SET TIMESTAMP=1509217210/*!*/;

问题原因

现在分析一下
3:00:10 主2 SET INSERT_ID=5135418110 insert mobile_ad_50
3:00:15 主1 rename table

3.00.10     主1                                      主2                                                    SET INSERT_ID=5135418110 insert mobile_ad_50源                        <---通过binlog同步给主1---                    由于复制延迟,主1这一条insert还没执行3.00.15     主1                                      主2            rename    ---通过binlog同步给主2-->       rename这时主1才执行SET INSERT_ID=5135418110 insert mobile_ad_50,主1新创建的mobile_ad_50表"继承了"原来的mobile_ad_50也就是mobile_ad_50_20170531表的ID,导致了ID暴增证据是5135418110在主1的 mobile_ad_50中,而主2的mobile_ad_50中没有,实际上5135418110在主2的mobile_ad_50_20170531中mysql> select id,dtime from mobile_ad_50 where id=5135418110;Empty set (0.00 sec)mysql> select id,dtime from mobile_ad_50_20170531 where id=5135418110;+------------+---------------------+| id         | dtime               |+------------+---------------------+| 5135418110 | 2017-10-29 03:00:10 |+------------+---------------------+