Sounds harder than it is – especially when reading the official docs. But if you want to synchronize two DBs, just tell the “master” to write a log and slave to read it.

In detail it looks like the following:

Master

Add the following lines to my.cnf:

log-bin = <name-of-the-logfile>
binlog-do-db = <name-of-the-database>
server-id = <number-of-the-server>

The only fixed part is the name of the database that shall be replicated. Leave out completely to replicate all of them. The other two can be set arbitrarily. The name of the log-bin is usually the name of the host+“-bin”. It’s also set to it if you leave it empty (saying only log-bin in the config). However that would mean, that you won’t be allowed to rename your host without major headache. Finally, the server-id is just a number. I use “1” for the master and “2” for the slave.

Afterwards, you’ve to create a user as which the slave is going to authenticate:

GRANT REPLICATION SLAVE ON *.* TO '<user-name>'@'%' IDENTIFIED BY '<password>';

You can also use the host name of the slave instead of the “%”. Now, it’s a good moment to FLUSH PRIVILEGES; and FLUSH TABLES;. Finally we do a SHOW MASTER STATUS; whose output we’ll need for configuring the slave:

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|------------------|----------|--------------|------------------|
| foo-bin.000007   | 42       | mydb         |                  |

The first two columns (red and green) will be needed when setting up the slave. The master is done now. But, to make replication work, master and slave must have identical contents. The logs contain only the differences to this starting point. There are two ways to achieve it:

  1. Using a dump as used usually for backups et cetera. You should lock the tables, make a dump, look at the master status and unlock the tables again. This is the more secure a generally preferable way. Especially because the dump will also create the necessary database structures.
  2. Using LOAD DATA FROM MASTER; later on. No action is required for this on the master.

Slave

The my.conf gets

server-id=<number-of-the-server>

And that’s it. :) If you’ve chosen alternative 1, you should install the dump now. The only remaining step is tell the slave about the master using

CHANGE MASTER TO MASTER_HOST=’<host-name-of-master>’, MASTER_USER=’<user-name>’, MASTER_PASSWORD=’<password>’, MASTER_LOG_FILE=’<name-of-log-file>’, MASTER_LOG_POS=<log-position>;

and restart MySQL.

If you went for number 2, do STOP SLAVE; and LOAD DATA FROM MASTER;. The data should be in sync after that, granted, the master didn’t change since then. When you start the replication using START SLAVE again, the slave will stay in sync with the master and your goal is achieved!

Summary

In fact, the biggest obstacle is the synchronization at the beginning. The replication on its own is a matter of four lines in two config files and three SQL statements.

P.S. When using InnoDB with transactions, the master should also get

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

into its my.cnf. It should enhance durability and consistency.