Home arrow Blog arrow Resync mysql replication including MyISAM databases

Resync mysql replication including MyISAM databases

This works just the same for MariaDB and MySQL. Replication means there is a master server and a slave server. I'm assuming that you have command line access to both. It is handy to set up two terminal sessions on each server. If you use Terminator, you can do that as four panes within a single tab. On each server, one terminal session is logged in to the MySQL client and the other is ready to issue commands. The first step is on the slave SQL session: STOP SLAVE;

Then in the master SQL session: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

The master server is now effectively stopped. Make a note of the information on the master's status and then go to the master's command line and run:

mysqldump -u root -p --replace --opt --all-databases > backup-YYY-MM-YY.sql

Note that the option --replace is important, and is often not mentioned in instructions for resync. What it does is avoid conflicts that can arise with MyISAM tables, resulting in a failure of the restore because of duplicate keys. With the --replace option, later inserts will overwrite earlier ones.

People say the next command on the master's SQL session can be done as soon as the mysqldump is started, although I usually wait for it to complete just to be certain nothing is out of line:

UNLOCK TABLES;

The master server is operational again after that. Now the backup can be transferred from the master to slave server. Then, at the slave command line, install the backup into the slave database:

mysql -u root -p  \< /path/to/backup-YYY-MM-YY.sql

When that is completed, the slave needs to be reset with a SQL command something like:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000341', MASTER_LOG_POS=3478636, MASTER_USER='replicator', MASTER_PASSWORD='??????', MASTER_HOST='127.0.0.1', MASTER_PORT=4002;
START SLAVE;

That may need a fair amount of tweaking, according to your situation. The name of the log file has to be taken from the MASTER STATUS obtained earlier, as does the master log position. The command shown assumes that an SSH tunnel has been created that uses local port 4002 on the slave server and connects to the master database at the other end.

With those commands executed, the slave server should be running and replicating the master.

#128008 • 04/03/2019 8:58am by Martin Brampton • Vote: Up votes (5) Down votes (0)