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:
Add the following lines to
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:
- 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.
LOAD DATA FROM MASTER;later on. No action is required for this on the master.
The my.conf gets
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!
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
my.cnf. It should enhance durability and consistency.