ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MOD

 

 

今日研究数据库rowid的问题,

mysql的binlog是server层的东西,而innodb中的Rowid是存储引擎的东西,server层根本感知不到Rowid的存在,那么复制时从库如何感知哪一行对应哪一个Rowid呢?

这话视乎是老生常谈了,但总感觉有些官方,让人理解一些东西,最好还是拿出一些实证来,
首先创建一个没有主键的表:

 

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

然后插入5条数据:
mysql> select * from t2;
+---------+----------+
| id      | name     |
+---------+----------+
|    1212 | aaaaaaaa |
|   99999 | bbbb     |
| 8888888 | cccccc   |
|   77777 | ddddd    |
|    6666 | eeeeeee  |
+---------+----------+
5 rows in set (0.00 sec)

 

# at 1562
#171110  8:19:57 server id 138  end_log_pos 1610 CRC32 0xa3e1f26e     Table_map: `test`.`t2` mapped to number 109
# at 1610
#171110  8:19:57 server id 138  end_log_pos 1659 CRC32 0x62f3974a     Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=1212 /* INT meta=0 nullable=1 is_null=0 */
###   @2='aaaaaaaa' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
# at 1659
#171110  8:19:57 server id 138  end_log_pos 1690 CRC32 0x0431e3c4     Xid = 36
COMMIT/*!*/;
# at 1690
#171110  8:20:06 server id 138  end_log_pos 1755 CRC32 0x2342e151     GTID    last_committed=6    sequence_number=7    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '807de6b3-c4d3-11e7-954f-000c29b46750:22'/*!*/;
# at 1755
#171110  8:20:06 server id 138  end_log_pos 1827 CRC32 0xe6e39538     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1510273206/*!*/;
BEGIN
/*!*/;
# at 1827
#171110  8:20:06 server id 138  end_log_pos 1875 CRC32 0x337f333b     Table_map: `test`.`t2` mapped to number 109
# at 1875
#171110  8:20:06 server id 138  end_log_pos 1920 CRC32 0x9491028c     Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=99999 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbbb' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
# at 1920
#171110  8:20:06 server id 138  end_log_pos 1951 CRC32 0xbecc04ae     Xid = 37
COMMIT/*!*/;
# at 1951
#171110  8:20:16 server id 138  end_log_pos 2016 CRC32 0x289f3f89     GTID    last_committed=7    sequence_number=8    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '807de6b3-c4d3-11e7-954f-000c29b46750:23'/*!*/;
# at 2016
#171110  8:20:16 server id 138  end_log_pos 2088 CRC32 0x10b6410e     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1510273216/*!*/;
BEGIN
/*!*/;
# at 2088
#171110  8:20:16 server id 138  end_log_pos 2136 CRC32 0x262cc6df     Table_map: `test`.`t2` mapped to number 109
# at 2136
#171110  8:20:16 server id 138  end_log_pos 2183 CRC32 0xf8c13659     Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=8888888 /* INT meta=0 nullable=1 is_null=0 */
###   @2='cccccc' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
# at 2183
#171110  8:20:16 server id 138  end_log_pos 2214 CRC32 0xeed466a1     Xid = 38
COMMIT/*!*/;
# at 2214
#171110  8:20:25 server id 138  end_log_pos 2279 CRC32 0x25e0f8b3     GTID    last_committed=8    sequence_number=9    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '807de6b3-c4d3-11e7-954f-000c29b46750:24'/*!*/;
# at 2279
#171110  8:20:25 server id 138  end_log_pos 2351 CRC32 0x052aa478     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1510273225/*!*/;
BEGIN
/*!*/;
# at 2351
#171110  8:20:25 server id 138  end_log_pos 2399 CRC32 0x92e342d3     Table_map: `test`.`t2` mapped to number 109
# at 2399
#171110  8:20:25 server id 138  end_log_pos 2445 CRC32 0xef7424dd     Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=77777 /* INT meta=0 nullable=1 is_null=0 */
###   @2='ddddd' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
# at 2445
#171110  8:20:25 server id 138  end_log_pos 2476 CRC32 0x81d1a8ea     Xid = 39
COMMIT/*!*/;
# at 2476
#171110  8:20:34 server id 138  end_log_pos 2541 CRC32 0xeba7347c     GTID    last_committed=9    sequence_number=10    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '807de6b3-c4d3-11e7-954f-000c29b46750:25'/*!*/;
# at 2541
#171110  8:20:34 server id 138  end_log_pos 2613 CRC32 0x10236511     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1510273234/*!*/;
BEGIN
/*!*/;
# at 2613
#171110  8:20:34 server id 138  end_log_pos 2661 CRC32 0x6e186084     Table_map: `test`.`t2` mapped to number 109
# at 2661
#171110  8:20:34 server id 138  end_log_pos 2709 CRC32 0xf30f3e98     Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=6666 /* INT meta=0 nullable=1 is_null=0 */
###   @2='eeeeeee' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
# at 2709
#171110  8:20:34 server id 138  end_log_pos 2740 CRC32 0x9fb2a4ce     Xid = 40
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


从这些内容中,并没有看出来有Rowid信息,接下来想在主库随机更细一条数据,结果悲剧了。

update t2 set name ='AAAAAA' order by rand();
mysql> select * from t2;
+---------+--------+
| id      | name   |
+---------+--------+
|    1212 | AAAAAA |
|   99999 | AAAAAA |
| 8888888 | AAAAAA |
|   77777 | AAAAAA |
|    6666 | AAAAAA |
+---------+--------+
5 rows in set (0.00 sec)
本想更新一条的,这下子闯祸了,没办法,恢复吧,

 mysqlbinlog --start-position="1562" --stop-position="2740" mysql-bin.000003|mysql -uroot -p
Enter password: 
ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

居然报错^_^
 

查了下资料,需要更改


 

mysql> set global gtid_mode='off_permissive';
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

 

根据提示gtid_mode不能越级修改,需要一步步修改,如下

mysql> set global gtid_mode='on_permissive';
Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_mode='off_permissive';
Query OK, 0 rows affected (0.01 sec)

修改成功,机修恢复,

[root@localhost data]# mysqlbinlog --start-position="1562" --stop-position="2740" mysql-bin.000003|mysql -uroot -p
Enter password: 
[root@localhost data]# 

终于恢复成功了,为什么恢复数据要配置成off_permissive?这个问题稍后再处理。
 

相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页