MySQL 从属 I/O 线程未运行

MySQL slave I/O thread not running

提问by Champ

I have set up replication for MySQL server. I can connect from the slave machine to the master server using the replication user/password. I have got the slave SQL thread running, but the slave I/Othread is not running and the slave I/O status comes as empty when checked using 'show slave status'. What could be the problem?

我已经为 MySQL 服务器设置了复制。我可以使用复制用户/密码从从机连接到主服务器。我已经运行了从属 SQL 线程，但是从属I/O线程没有运行，并且在使用“显示从属状态”检查时从属 I/O 状态变为空。可能是什么问题呢？

How do I solve this? Restarting the slave does not help.

我该如何解决这个问题？重新启动从站没有帮助。

This was my bad: Instead of giving a 'replication slave' privilege to *.*, I was only giving it for my_db.*.

这是我的坏处：我没有给予 '复制奴隶' 特权*.*，而是只给予my_db.*.

回答by brian-brazil

Instead of giving a 'replication slave' privilege to ., I was only giving it for my_db.*.

我没有为 . 提供“复制从属”特权，而是只为 my_db.* 提供了它。

Replication slave is only a global privilege (i.e. per-user only), this means that a command such as

复制从属只是一个全局权限（即仅限每个用户），这意味着诸如

GRANT REPLICATION SLAVE on mydb.* TO 'someuser'@'%';

has no effect as you can't grant it per-database/column/table.

没有任何效果，因为您不能为每个数据库/列/表授予它。

The command you need to run is:

您需要运行的命令是：

GRANT REPLICATION SLAVE on *.* TO 'someuser'@'%';

Then do a START SLAVE. You might also find it useful to look in the mysql error log.

然后做一个START SLAVE. 您可能还会发现查看 mysql 错误日志很有用。

I'd suggest a good read of the replication setup documentation, as it explains all of this in detail.

我建议好好阅读复制设置文档，因为它详细解释了所有这些。

回答by M Arif

I faced same issue and fixed using following steps. Complete thread link is http://www.percona.com/forums/questions-discussions/percona-xtrabackup/11842-backup-stopped-working-slave-sql-running-no

我遇到了同样的问题并使用以下步骤修复。完整的线程链接是http://www.percona.com/forums/questions-discussions/percona-xtrabackup/11842-backup-stopped-working-slave-sql-running-no

Steps are same as mentioned by @Luxknight007 except his step 2. However this thread contains more detail which is very helpful. Following is solution which i used and it worked.

步骤与@Luxknight007 提到的相同，除了他的第 2 步。但是这个线程包含更多的细节，非常有帮助。以下是我使用过并且有效的解决方案。

"The first issue is that you changed the replication position instead of fixing the error, and used an incorrect binlog file name format (you likely just used the one from that post you linked I'd guess). To get back to where you started, you need to find the binlog file and position that the slave sql_thread stopped at. Based on your slave status output, it looks like the slave is reading from a new binlog file (you can see that the Read_Master_Log_Pos value is smaller than the Exec_Master_Log_Pos value, which means it has to be reading a newer binlog file than where the slave sql_thread stopped at), so you need to find the binlog file that the slave sql_thread actually failed on. So look in the error log for something like the below:

“第一个问题是您更改了复制位置而不是修复错误，并且使用了不正确的 binlog 文件名格式（我猜您可能只是使用了您链接的那个帖子中的那个格式）。回到您开始的地方，你需要找到slave sql_thread停止的binlog文件和位置。根据你的slave状态输出，看起来slave正在从一个新的binlog文件中读取（你可以看到Read_Master_Log_Pos值小于Exec_Master_Log_Pos值，这意味着它必须读取比从属 sql_thread 停止的位置更新的 binlog 文件），因此您需要找到从属 sql_thread 实际失败的 binlog 文件。因此在错误日志中查看如下内容：

Code:

代码：

2013-10-08 12:48:51 37545 [ERROR] Slave SQL: Error 'Table 'testdb.test2' doesn't exist' on query. Default database: 'testdb'. Query: 'insert into test1 select * from test2', Error_code: 1146
2013-10-08 12:48:51 37545 [Warning] Slave: Table 'testdb.test2' doesn't exist Error_code: 1146
2013-10-08 12:48:51 37545 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 3427

This is a sample I re-created, so yours will be a bit different. Note the ERROR is similar to what you see in your slave status. So find your specific error message in the error log file, and then locate the end part where is gives you the file name and position ("We stopped at log 'mysql-bin.000001' position 3427" in my example). The position should be 315098143 based on your show slave status, as that is when it the slave sql_thread stopped executing events (Exec_Master_Log_Pos ) but the io_thread kept reading in new ones (Read_Master_Log_Pos).

这是我重新创建的示例，因此您的示例会有所不同。请注意 ERROR 类似于您在从属状态中看到的内容。因此，在错误日志文件中找到您的特定错误消息，然后找到为您提供文件名和位置的结尾部分（在我的示例中，“我们停在日志 'mysql-bin.000001' 位置 3427”）。根据您的 show slave 状态，位置应该是 315098143，因为那是当从属 sql_thread 停止执行事件（Exec_Master_Log_Pos）但 io_thread 继续读取新事件（Read_Master_Log_Pos）时。

Once you find the correct binlog file name and position, re-run your change master statement on your slave using the information you located in the error log. Note that your file name should be something like "newcrmdb1-bin.XXXXXX", not mysql-bin.XXXXXX (you can see this naming convention your show slave status above).

找到正确的 binlog 文件名和位置后，使用您在错误日志中找到的信息在从属设备上重新运行更改主语句。请注意，您的文件名应该类似于“newcrmdb1-bin.XXXXXX”，而不是 mysql-bin.XXXXXX（您可以在上面的 show slave status 中看到此命名约定）。

Code:

代码：

mysql> change master to MASTER_LOG_FILE='newcrmdb1-bin.XXXXXX', Master_Log_Pos=315098143;

change master to MASTER_LOG_FILE='mysql-bin.000082' , Master_Log_Pos=47914844;

Once you get pointed back to the original replication location where the slave sql_thread failed, you need to then fix the error that it was complaining about to start with.

一旦您指向从属 sql_thread 失败的原始复制位置，您就需要修复它开始抱怨的错误。

The initial replication error appears to be telling you that the table asteriskcdr.bpleadcfdoes not exist on the slave, so the insert statement is failing when it attempts to select the data from that table. So the problem there is that your slave appears to be already out of sync with your master. If the table in question on the master is static or mostly static, you could likely solve this by exporting the data from just that table on the master using mysqldump and loading it into the slave. If that is not possible, or you do not care about that data, you could always just skip the replication statement with sql_slave_skip_counter, but then the slave would be further out of sync with the master.

初始复制错误似乎是在告诉您表asteriskcdr. bpleadcf从站上不存在，因此插入语句在尝试从该表中选择数据时失败。所以问题是你的奴隶似乎已经与你的主人不同步了。如果 master 上有问题的表是静态的或大部分是静态的，您可能可以通过使用 mysqldump 从 master 上的那个表导出数据并将其加载到从属来解决这个问题。如果这是不可能的，或者您不关心该数据，您总是可以跳过带有 sql_slave_skip_counter 的复制语句，但是这样从站将进一步与主站不同步。

And if all else fails, you can always rebuild the slave from the master as a last resort as well. =)"

如果所有其他方法都失败了，作为最后的手段，您也可以随时从主服务器重建从服务器。=)”

回答by Luxknight007

I have encountered the same problem and I try this steps

我遇到了同样的问题，我尝试了这个步骤

First add this code somewhere below [mysqld] in my.cnf or my.ini slave-skip-errors=1046this will skip all duplicate entry since we will execute the whole binary log file where the replication stop, you may comment this code after successful replication.

首先在 my.cnf 或 my.ini 中的 [mysqld] 下方添加此代码， slave-skip-errors=1046这将跳过所有重复条目，因为我们将执行复制停止的整个二进制日志文件，您可以在成功复制后注释此代码。

1.STOP SLAVE;

1.停止奴隶；

2.RESET SLAVE;

2.重置从机；

3.CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000049';

3. 将 MASTER 更改为 MASTER_LOG_FILE='mysql-bin.000049';

Note: MASTER_LOG_FILE must be the last file where it stop from replicating

4.CHANGE MASTER TO MASTER_LOG_POS=98;

4. 将 MASTER 更改为 MASTER_LOG_POS=98；

5.START SLAVE;

5.启动从机；

check if you are successful

检查你是否成功