Get a mysql replication running again ASAP

Sometimes, mysql replication fails, because of network problems, a reboot or what have you. The fastest way to get it running again is often to reinitialise the data completely.

Here is how to do that.

This assumes the actual master - slave MySQL configuration is configured properly - the slave can read from the master, but has somehow lost the plot.

On the master server:

Dump the databases:

mysqldump -u <database_user> -p --databases <list your databases> \
                  --add-drop-database --add-drop-table --dump-date -e --skip-lock-tables \
                  --single-transaction --flush-logs --hex-blob --master-data=2  > dump.sql

Get the replication values:

head dump.sql -n 80 | grep MASTER_LOG_FILE

Should say something like

-- CHANGE MASTER TO MASTER_LOG_FILE=\'mysql-bin.001195\', MASTER_LOG_POS=106;

Send the dump to slave:

bzip2 -c1 dump.sql | ssh slave@slavehost \"cat > /home/slave/dump.sql.bz2\"

On the slave server:

bzcat dump.sql.bz2 | mysql -u root -p

Log in to mysql:

mysql -u root -p

Stop the slave:

STOP SLAVE;

Apply replication values from above:

CHANGE MASTER TO MASTER_LOG_FILE=\'mysql-bin.001195\', MASTER_LOG_POS=106; START SLAVE;