关于mysql主从复制自增长列
现有两台mysql服务器A和B
A:auto_increment_offset = 2
auto_increment_increment = 2
binlog_format="STATEMENT"
B:auto_increment_offset = 1
auto_increment_increment = 2
A和B都有如下表,建表语句如下:
test | CREATE TABLE test
(id
int(11) NOT NULL AUTO_INCREMENT,title
varchar(45) DEFAULT NULL,
PRIMARY KEY (id
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
test1 | CREATE TABLE test1
(id
int(11) NOT NULL AUTO_INCREMENT,title
varchar(45) DEFAULT NULL,
PRIMARY KEY (id
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
test表中数据如下:
+----+-------------------------------------
| id | title
+----+-------------------------------------
| 2 | 2
| 4 | 2
| 6 | efe4aee2-e721-11e5-a858-000c2952edc5
| 8 | 2016-03-11 08:31:18
| 10 | 2016-03-11 08:31:40
| 12 | 97621dc2-e722-11e5-a858-000c2952edc5
| 14 | aaaq
| 16 | aaaq
| 18 | 13
+----+-------------------------------------
现在在A服务器执行如下sql:
insert test1 select * from test where id >= 10
虽然mysql出了警告:
Unsafe statement written to the binary log
using statement format since BINLOG_FORMAT = STATEMENT.
但是我看到B服务器同步A服务器数据后显示如下:
+----+--------------------------------------+
| id | title |
+----+--------------------------------------+
| 10 | 2016-03-11 08:31:40 |
| 12 | 01b889cf-e721-11e5-9568-000c29aa710d |
| 14 | aaaq |
| 16 | aaaq |
| 18 | 13 |
+----+--------------------------------------+
这时候查看A服务器的binlog日志发现
BEGIN
/*!*/;
at 665994732
#160314 15:47:15 server id 136 end_log_pos 665994865 CRC32 0xeb828757 Query thread_id=1675059 exec_time=0 error_code=0
SET TIMESTAMP=1457941635/*!*/;
insert test1 select * from test where id >= 10
/*!*/;
at 665994865
#160314 15:48:38 server id 136 end_log_pos 665994896 CRC32 0x5a745662 Xid = 169785483
COMMIT/*!*/;
A服务器并未提示插入的数据自增长id是多少,为什么B服务器的数据和A服务器的
数据仍然一致。
按自己的理解,B服务器的数据应该是如下,因为是靠B服务器的自己自增长产生id。
+----+--------------------------------------+
| id | title |
+----+--------------------------------------+
| 1 | 2016-03-11 08:31:40 |
| 3 | 01b889cf-e721-11e5-9568-000c29aa710d |
| 5 | aaaq |
| 7 | aaaq |
| 9 | 13 |
求各位师兄解答下为什么,谢谢!