mysql 复制跳过语句。是否可以？

mysql replication skip statement. is it possible?

提问by Volodymyr Linevych

There is a system with ROW-based replication. Yesterday i have executed a heavy statement on my master accidently and found my slaves far behind master. I have interrupted the query on master, but it was still running on slaves. So i got my slaves 15 hours behind master.

有一个基于 ROW 的复制系统。昨天我无意中对我的主人执行了一个沉重的声明，发现我的奴隶远远落后于主人。我已经中断了对 master 的查询，但它仍在从属服务器上运行。所以我的奴隶比主人晚了 15 个小时。

I have already tried to step over one position by resetting slave and increasing MASTER_LOG_POS, but with no luck: position wasn't found, because relay log wasn't read further than a heavy query event.

我已经尝试通过重置从站并增加 MASTER_LOG_POS 来跨越一个位置，但没有运气：找不到位置，因为中继日志没有被读取到比重查询事件更远的位置。

Read_Master_Log_Pos == Exec_Master_Log_Pos

• Is there any way to skip the heavy query? (i don't care about data that has to be changed by query)
• Is there a way to kill a query on a slave taken from relay log?
• Is there a way to roll the slaves back in 1 position, remove the event from master bin-log and resume the replication?

• 有没有办法跳过繁重的查询？（我不关心必须通过查询更改的数据）
• 有没有办法终止对从中继日志中获取的从属设备的查询？
• 有没有办法将奴隶回滚到 1 个位置，从主 bin-log 中删除事件并恢复复制？

回答by Flo Doe

Try the following on the slave:

在从站上尝试以下操作：

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

This will stop the slaves threads and skips the next event from the master. This you normally use when you have problems with statements to skip over them.

这将停止从线程并跳过来自主线程的下一个事件。当您对跳过它们的语句有问题时，通常会使用它。

Also read following part of the mysql docs: set-global-sql-slave-skip-counter

另请阅读 mysql 文档的以下部分：set-global-sql-slave-skip-counter

回答by A.Badger

First explore the binary logs on the master to find the SQL statement that is causing the issue, using the following on the master:

首先在 master 上查看二进制日志以查找导致问题的 SQL 语句，在 master 上使用以下内容：

SHOW BINLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 100;

Then set the slave to only sync up to the statement before that:

然后将 slave 设置为只同步到之前的语句：

STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;

When you want it to carry on replication after the bad statement (warning, this can be dangerous if the statement changed data) you can tell the slave to continue from a specific point in the masters log. To do this get the position using the first command on the master, then set the slave to go:

当您希望它在错误语句之后继续复制时（警告，如果语句更改数据，这可能很危险），您可以告诉从服务器从主日志中的特定点继续。为此，使用 master 上的第一个命令获取位置，然后将 slave 设置为 go：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000663', MASTER_LOG_POS=4;
START SLAVE;

回答by Sean Fahey

For those on Amazon RDS MySQL you can skip one error at a time on the slave with:

对于使用 Amazon RDS MySQL 的用户，您可以使用以下命令一次跳过从属服务器上的一个错误：

CALL mysql.rds_skip_repl_error;

No need to stop replication before running this.

在运行之前无需停止复制。

回答by Rodo

I found the starting the io_thread first

我首先找到了启动 io_thread

start slave io_thread;

and checking the relay logs with the command

并使用命令检查中继日志

SHOW RELAYLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 100;

This saved me a lot of time.

这为我节省了很多时间。

回答by Teun Ouwehand

You can set a skip counter as follow:

您可以按如下方式设置跳过计数器：

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE; 

To see the processlist:

查看进程列表：

mysql> show [full] processlist;
kill "number from first col";

Start slave from specific position:

从特定位置启动 slave：

START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

Ref: http://dev.mysql.com/doc/refman/5.0/en/start-slave.html

参考：http: //dev.mysql.com/doc/refman/5.0/en/start-slave.html