MySQL Replication

Submitted by admin on Fri, 02/05/2010 - 19:08

Using the following command it's possible to take a dump of an entire mysql server (all of the databases at once) and get the slave replicating without ever taking the master offline (though it may slow down during the dump). Be sure to see the caveats.

  1. mysqldump  --add-drop-table --master-data=1 --single-transaction --quick --delete-master-logs --all-databases > snapshot.sql

Explanation:

--add-drop-table

Drop each table before we import a new one. This is useful if we're resetting a slave or turning an old master into the new slave (swapping out the old one for a new one).

--master-data=1

This option specifies that the master data should be recorded as a set master sql command. This provides the binlog and log position (master-data=2 would include the info but only as a comment) but the hostname and authentication information which need to be set separately and should be set from the mysql command prompt using the 'CHANGE MASTER' command.

--single-transaction

This really is the magic that lets us keep the site online. It starts a transaction and does the entire dump before clearing the transaction. This ensures that nothing else gets committed and the log position and data aren't changing while you're creating your dump. This only works with innodb managed tables which should be fine for the default Ubuntu mysql configuration.

--quick

This forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. (Optional on Ubuntu because the default my.cnf file specifies this for all dumps anyway).

--delete-master-logs

Deletes old binary logs. If we're resetting the slaves there's no reason to keep them around.

--all-databases

Fairly self explanatory, this dumps all of the databases on the server.

Caveats

The main thing to keep in mind with this approach is that you're moving ALL of your mysql managed information over. This includes mysql users and permissions. Regardless of what the username, passwords and permissions of the old db users were they will now mirror the master exactly. This includes the root user so be sure to update your .my.cnf file if applicable.