mysql 跳过一个事物_MYSQL GTID跳过指定事务

￡神魔★判官ぃ 2023-01-10 05:16 121阅读 0赞

主库删除了ttt表，从库上没有ttt表，出现了报错。

\[root@mysqlstu2:demo\]10:49:52>show slave status\\G

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

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

Master\_Host: 192.168.55.97

Master\_User: bak

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql\_log\_bin.000005

Read\_Master\_Log\_Pos: 683

Relay\_Log\_File: mysqlstu2-relay-bin.000002

Relay\_Log\_Pos: 410

Relay\_Master\_Log\_File: mysql\_log\_bin.000005

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: No

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 1051

Last\_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE \`ttt\` /\* generated by server \*/'

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 338

Relay\_Log\_Space: 958

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: 1051

Last\_SQL\_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE \`ttt\` /\* generated by server \*/'

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 97

Master\_UUID: 9f7152cc-913d-11ea-b983-000c298beb69

Master\_Info\_File: mysql.slave\_master\_info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State:

Master\_Retry\_Count: 86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp: 200605 10:49:39

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set: 9f7152cc-913d-11ea-b983-000c298beb69:15-16

Executed\_Gtid\_Set: 9f7152cc-913d-11ea-b983-000c298beb69:1-14

Auto\_Position: 1

Replicate\_Rewrite\_DB:

Channel\_Name:

Master\_TLS\_Version:

1 row in set (0.00 sec)

\[root@mysqlstu2:demo\]10:49:58>show tables;  从库没有ttt表

\+----------------+

| Tables\_in\_demo |

\+----------------+

| dd |

| tt |

\+----------------+

2 rows in set (0.00 sec)

\[root@mysqlstu2:demo\]10:53:31>stop slave;

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:56:24>set gtid\_next ='9f7152cc-913d-11ea-b983-000c298beb69:14';

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:56:57>begin;commit;

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:57:05>set gtid\_next='AUTOMATIC';

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:57:24>start slave;

Query OK, 0 rows affected (0.01 sec)

这次操作失败了，gtid\_next 指定错误，与Executed\_Gtid\_Set一样了，理解错了，这个应该是要跳过的事务。

\[root@mysqlstu2:demo\]10:57:29>show slave status\\G

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

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

Master\_Host: 192.168.55.97

Master\_User: bak

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql\_log\_bin.000005

Read\_Master\_Log\_Pos: 683

Relay\_Log\_File: mysqlstu2-relay-bin.000002

Relay\_Log\_Pos: 410

Relay\_Master\_Log\_File: mysql\_log\_bin.000005

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: No

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 1051

Last\_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE \`ttt\` /\* generated by server \*/'

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 338

Relay\_Log\_Space: 1461

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: 1051

Last\_SQL\_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE \`ttt\` /\* generated by server \*/'

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 97

Master\_UUID: 9f7152cc-913d-11ea-b983-000c298beb69

Master\_Info\_File: mysql.slave\_master\_info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State:

Master\_Retry\_Count: 86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp: 200605 10:57:29

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set: 9f7152cc-913d-11ea-b983-000c298beb69:15-16

Executed\_Gtid\_Set: 9f7152cc-913d-11ea-b983-000c298beb69:1-14

Auto\_Position: 1

Replicate\_Rewrite\_DB:

Channel\_Name:

Master\_TLS\_Version:

1 row in set (0.00 sec)

再次执行， gtid\_next='9f7152cc-913d-11ea-b983-000c298beb69:15'，成功跳过指定事务。

\[root@mysqlstu2:demo\]10:57:36>stop slave;

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:57:58>set gtid\_next='9f7152cc-913d-11ea-b983-000c298beb69:15';

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:58:44>begin;commit;

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:58:55>set gtid\_next='AUTOMATIC';

Query OK, 0 rows affected (0.00 sec)

\[root@mysqlstu2:demo\]10:59:17>start slave;

Query OK, 0 rows affected (0.01 sec)

\[root@mysqlstu2:demo\]10:59:25>show slave status\\G

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

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

Master\_Host: 192.168.55.97

Master\_User: bak

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql\_log\_bin.000005

Read\_Master\_Log\_Pos: 683

Relay\_Log\_File: mysqlstu2-relay-bin.000004

Relay\_Log\_Pos: 450

Relay\_Master\_Log\_File: mysql\_log\_bin.000005

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 683

Relay\_Log\_Space: 953

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: 0

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: 97

Master\_UUID: 9f7152cc-913d-11ea-b983-000c298beb69

Master\_Info\_File: mysql.slave\_master\_info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for more updates

Master\_Retry\_Count: 86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp:

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set: 9f7152cc-913d-11ea-b983-000c298beb69:15-16

Executed\_Gtid\_Set: 9f7152cc-913d-11ea-b983-000c298beb69:1-16

Auto\_Position: 1

Replicate\_Rewrite\_DB:

Channel\_Name:

Master\_TLS\_Version:

1 row in set (0.00 sec)

mysql 跳过一个事物_MYSQL GTID跳过指定事务

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 SonarQube跳过指定代码检查方法

相关 mysql主从跳过错误

相关 C++跳过（忽略）指定字符

相关如何跳过MySQL的root密码

相关 mysql 跳过一个事物_MYSQL GTID跳过指定事务

相关 SonarQube 跳过指定检查

相关 MySQL 跳过表中的n行

相关 GTID：跳过错误

相关 mysql gtid 复制跳过错误

相关 mysql从库在开启GTID时跳过错误的方法