mysql ddl复制_如何在不兼容的DDL命令下修复MySQL复制

深藏阁楼爱情的钟 2022-10-25 00:59 179阅读 0赞

原标题：如何在不兼容的DDL命令下修复MySQL复制

【IT168 技术】MySQL支持复制到一个更高版本的slave，这允许我们通过升级从机和指向应用程序来轻松地将MySQL设置升级到一个新版本。但如果不支持或者是应用程序在旧版本的MySQL上表现得更好，我们就需要通过降级来提升slave性能。

MySQL手册表示基于行的复制可以用于复制到较低版本，前提是没有复制的DDL与从服务器不兼容。其中有一个不兼容命令是MySQL 5.7中的新特性，5.6上版本不可用：

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testuser';

执行该命令会中断复制。这里是一个在非GTID复制中被破坏的奴隶的例子：

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 127.0.0.1

Master\_User: repl

Master\_Port: 5723

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000002

Read\_Master\_Log\_Pos: 36915649

Relay\_Log\_File: mysql\_sandbox5641-relay-bin.000006

Relay\_Log\_Pos: 36174552

Relay\_Master\_Log\_File: mysql-bin.000002

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: No

\*\*\* redacted \*\*\*

Last\_Errno: 1064

Last\_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH 'mysql\_native\_password' AS '\*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql\_native\_password' AS '\*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 36174373

Relay\_Log\_Space: 36916179

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

\*\*\* redacted \*\*\*

Seconds\_Behind\_Master: NULL

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 1064

Last\_SQL\_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH 'mysql\_native\_password' AS '\*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql\_native\_password' AS '\*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 1

Master\_UUID: 00005723-0000-0000-0000-000000005723

\*\*\* redacted \*\*\*

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp: 180918 22:03:40

\*\*\* redacted \*\*\*

Auto\_Position: 0

1 row in set (0.00 sec)

跳过该语句不会恢复复制：

mysql> STOP SLAVE;

Query OK, 0 rows affected (0.02 sec)

mysql> SET GLOBAL sql\_slave\_skip\_counter=1;

Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\\G

修复非GTID复制

当检查从机状态时，复制仍然未修复。要修复它，必须手动跳转到下一个二进制日志位置。当前执行的二进制日志(Relay\_Master\_Log\_File)和位置(Exec\_Master\_Log\_Pos)分别是mysql-bin.000002和36174373。我们可以在主机上使用MySqLBiLoSQL来确定下一个位置：

mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=36174373 /ssd/sandboxes/msb\_5\_7\_23/data/mysql-bin.000002 | head -n 30

/\*!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=1\*/;

/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;

DELIMITER /\*!\*/;

\# at 36174373

\#180918 22:03:40 server id 1 end\_log\_pos 36174438 CRC32 0xc7e1e553 Anonymous\_GTID last\_committed=19273 sequence\_number=19277 rbr\_only=no

SET @@SESSION.GTID\_NEXT= 'ANONYMOUS'/\*!\*/;

\# at 36174438

\#180918 22:03:40 server id 1 end\_log\_pos 36174621 CRC32 0x2e5bb235 Query thread\_id=563 exec\_time=0 error\_code=0

SET TIMESTAMP=1537279420/\*!\*/;

SET @@session.pseudo\_thread\_id=563/\*!\*/;

SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;

SET @@session.sql\_mode=1436549152/\*!\*/;

SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;

/\*!\\C latin1 \*//\*!\*/;

SET @@session.character\_set\_client=8,@@session.collation\_connection=8,@@session.collation\_server=8/\*!\*/;

SET @@session.lc\_time\_names=0/\*!\*/;

SET @@session.collation\_database=DEFAULT/\*!\*/;

ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql\_native\_password' AS '\*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'

/\*!\*/;

\# at 36174621

\#180918 22:03:40 server id 1 end\_log\_pos 36174686 CRC32 0x86756b3f Anonymous\_GTID last\_committed=19275 sequence\_number=19278 rbr\_only=yes

/\*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED\*//\*!\*/;

SET @@SESSION.GTID\_NEXT= 'ANONYMOUS'/\*!\*/;

\# at 36174686

\#180918 22:03:40 server id 1 end\_log\_pos 36174760 CRC32 0x30e663f9 Query thread\_id=529 exec\_time=0 error\_code=0

SET TIMESTAMP=1537279420/\*!\*/;

BEGIN

/\*!\*/;

\# at 36174760

\#180918 22:03:40 server id 1 end\_log\_pos 36174819 CRC32 0x48054daf Table\_map: \`sbtest\`.\`sbtest1\` mapped to number 226

基于上述输出，下一个二进制日志位置为36174621。修复slave，运行：

STOP SLAVE;

CHANGE MASTER TO MASTER\_LOG\_FILE='mysql-bin.000002', MASTER\_LOG\_POS=36174621;

START SLAVE;

验证从属线程现在是否正在执行：SHOW SLAVE STATUS\\G

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 127.0.0.1

Master\_User: repl

Master\_Port: 5723

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000002

Read\_Master\_Log\_Pos: 306841423

Relay\_Log\_File: mysql\_sandbox5641-relay-bin.000002

Relay\_Log\_Pos: 190785290

Relay\_Master\_Log\_File: mysql-bin.000002

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

\*\*\* redacted \*\*\*

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 226959625

Relay\_Log\_Space: 270667273

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

\*\*\* redacted \*\*\*

Seconds\_Behind\_Master: 383

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 1

Master\_UUID: 00005723-0000-0000-0000-000000005723

Master\_Info\_File: /ssd/sandboxes/msb\_5\_6\_41/data/master.info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State: Opening tables

Master\_Retry\_Count: 86400

\*\*\* redacted \*\*\*

Auto\_Position: 0

为了使主从一致，对从属执行兼容查询。

SET SESSION sql\_log\_bin = 0;

GRANT USAGE ON \*.\* TO 'testuser'@'localhost' IDENTIFIED BY 'testuser';

GTID复制

对于GTID复制，除了为冒犯语句注入空事务之外，还需要使用上面提供的非GTID解决方案跳过它。一旦运行，将其翻转回到GTID。

下面是一个中断的GTID slave的例子：

![8560fa1533cfcbfa9072abc948d24138.png][]

由于执行的最后一个位置是7403，所以需要为违规序列7404创建一个空事务。

STOP SLAVE;

SET GTID\_NEXT='00005723-0000-0000-0000-000000005723:7404';

BEGIN;

COMMIT;

SET GTID\_NEXT=AUTOMATIC;

START SLAVE;

注意：如果您启用了MTS，您也可以从显示Last\_SQL\_Error of SHOW SLAVE STATUS\\G获得违反GTID坐标。

下一步是找到下一个二进制日志位置。当前执行的二进制日志(Relay\_Master\_Log\_File)和位置(Exec\_Master\_Log\_Pos)分别是mysql-bin.000003和12468343。我们可以再次在主机上使用MySqLBiLoSQL来确定下一个位置：

![5d04eb0e5537d9c76b0d335e4eb7d651.png][]

下一个二进制日志位置是36174621。修复从站，运行

STOP SLAVE;

CHANGE MASTER TO MASTER\_LOG\_FILE='mysql-bin.000003', MASTER\_LOG\_POS=12468591, MASTER\_AUTO\_POSITION=0;

START SLAVE;

注意，我在上面添加MaskAutoPosil=0，现在禁用GTID复制。您可以运行SHOW SLAVE STATUS\\G以确定MySQL运行良好：

![064587a8994597f74a057db15087c88f.png][]

因为运行良好，现在可以恢复GTID复制：

STOP SLAVE;

CHANGE MASTER TO MASTER\_AUTO\_POSITION=1;

START SLAVE;

最后，为了使主从一致，对从属执行兼容查询。

SET SESSION sql\_log\_bin = 0;

GRANT USAGE ON \*.\* TO 'testuser'@'localhost' IDENTIFIED BY 'testuser';

在本文中，我分享了如何修复由于向从属复制不兼容的命令而导致复制中断时的复制。如果有其它不兼容的命令，欢迎大家在下方评论。返回搜狐，查看更多

责任编辑：

[8560fa1533cfcbfa9072abc948d24138.png]: /images/20221024/f85f40033c714363a429477fdf000aec.png
[5d04eb0e5537d9c76b0d335e4eb7d651.png]: /images/20221024/406709a9dd6446c291ec99a28f7de4c4.png
[064587a8994597f74a057db15087c88f.png]: /images/20221024/9f1b26eebd8b4f3699a8e2e819ccce47.png