MySQL Troubleshooting

Problems with the I/O Thread

Common I/O errors include:

• The slave cannot connect to the master.

• The slave connects to master, but repeatedly disconnects.

• The slave is far behind master.

When an I/O error happens, the slave status that we saw in the previous section becomes Slave_IO_Running: No and the reason appears in the Last_IO_Errno and Last_IO_Error fields. The error logfile also contains messages about I/O thread failures if log_warnings is set to 1 (the default).

When the slave cannot connect, the first thing to check is whether the replication user has the correct permissions on the master. The replication user (the user you specify as master_user in the CHANGE MASTER query that begins replication) must have the REPLICATION SLAVE privilege on the master. If it does not, just grant such a privilege to this user on the master.

Once you are sure the replication user has the correct permissions, you need to check the network. Use the ping utility to find out whether the master host can be reached. Here is an example:

$ping 192.168.0.4
PING 192.168.0.4 (192.168.0.4): 56 data bytes
64 bytes from 192.168.0.4: icmp_seq=0 ttl=64 time=0.113 ms
64 bytes from 192.168.0.4: icmp_seq=1 ttl=64 time=0.061 ms
^C
--- 192.168.0.4 ping statistics ---
2 packets transmitted, 2 packets received, 0% packet loss
round-trip min/avg/max/stddev = 0.061/0.087/0.113/0.026 ms

If ping fails to connect to the master host, this clearly locates the problem in the network and you need to fix it. You can also use