MySQL slave I/O thread not running

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/O thread is not running and the slave I/O status comes as empty when checked using 'show slave status'. What could be the problem?

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 gi开发者_如何转开发ving it for my_db.*.

Instead of giving a 'replication slave' privilege to ., I was only giving it for 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.

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

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=1046 this 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.

1.STOP SLAVE;

2.RESET SLAVE;

3.CHANGE MASTER TO 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;

5.START SLAVE;

check if you are successful

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

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.

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

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).

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).

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.

The initial replication error appears to be telling you that the table asteriskcdr.bpleadcf does 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.

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