You can try reset slave
mysql command to ignore the current log file. However, you should be careful as this may result in data loss/inconsistency.
I think the safe way is to rebuild the replication from the beginning to make sure you have no data loss/inconsistency.
Copying the data dir is much more faster than exporting database using mysqldump
.
First, lock the tables using:
> FLUSH TABLES WITH READ LOCK;
Then, copy the data dir. So, the downtime will be the time of copying the data dir. For a more complete guide, have a look at this. You need to copy the data dir instead of using mysqldump
. You need to fix the permissions after copying the data files/folders onto the slave server.
There are some quirks to look out for in MySQL Replication
1) Use of replicate-do-db and replicate-ignore-db at the same time
There is flowchart on the page to show the order of processing. Personally, I do not use replicate-do-db and replicate-ignore-db at the same time. I use one or the other. If other slaves do not have this same issue, then rule this out.
2) Doing LOAD DATA INFILE
The way MySQL Replication handles it is appalling. Whenever a LOAD DATA INFILE is performed in a Master, the entire input file is deposited in the Master's binary logs. The slave gathers in the input file in its relay logs. The slave rematerializes the data file in the /tmp folder and then executes the LOAD DATA INFILE on the slave. This is not counted as repliction lag during this process. As a MySQL DBA, I know this works, but that's tacky !!!
3) Slave IO Thread Communication Breakdown
Sometimes, due to firewall changes, network routing, or some other networking anomaly, the Slave IO thread may simply stop getting entries to populate its relay logs. You may also want to check that the Slave IO thread is visible in the processlist of the Master. To make sure your slave's IO thread is alive, simply do the following on all slaves:
SHOW SLAVE STATUS\G
Watch for the Relay_Log_Space. It should be growing. If it stops growing, MySQL may simply freeze without an error for another crazy reason, which leads to suggestion #4.
4) Slave is Out of Diskspace
I wrote a post about how MySQL freezes when performing a MyISAM operation. MySQL uses MyISAM tables as temp tables. Check yout default tmp table directory (tmpdir variable in MySQL)
I hope these suggestions help !!!
Best Answer
The expire_logs_days setting controls binary logging, not relay logging.
Normally, the relay logs are purged when the slave has finished applying the data from them. This can be changed with relay-log-purge, but it's default is 1.
If your slave is simply behind, it will continue to accumulate relay logs by design. You might want to consider setting relay-log-space-limit to prevent out of disk space problems. This setting provides a disk space limit for the IO thread to use for storing relay logs.