wiki:MySQLReplication

Using MySQL with Replication

So, you have two thiblo servers up and running, and now you'd like them to be mirrors of each other. This section describes the steps to set up mutual replication between two servers.

I'll call the first server 1st and the second 2nd. The setup is almost symmetrical except that you have to decide which one should be mirrored to the other initially.

In the following I suppose that you run all mysql and similar commands as root user. Of course it's only important that you are able to connect as MySQL's root user. Copy root's credentials to your .my.cnf or replace mysql to sudo -H mysql if you running these from your local user.

MySQL replication setup

Shut down the mysql daemons (on both servers) and apply the following changes to [mysqld] section of the global config file /etc/mysql/my.cnf.

server-id = 1
skip-slave-start
slave-load-tmpdir = /var/lib/mysql_misc/tmp
auto_increment_increment = 10
auto_increment_offset = 1
report-host = 1st
replicate-do-db = thiblo

On the 2nd server the configuration differs:

server-id = 2
auto_increment_offset = 2
report-host = 2nd

Please note that the log-bin option also have to be set! I didn't provide it here, as it's already set in the default ubuntu installation. But it is a crucial option, without it the replication won't work!

Also, feel free to tweak other options relevant to binary logging and replications. For example expire-logs-days and max-binlog-size are also set in ubuntu. For full list see  http://dev.mysql.com/doc/refman/5.0/en/replication-options.html.

Create the temporary directory mentioned in the above config. The temporary files created for replication sould reside in a place which is not cleared on reboot, that's why we don't use /tmp.

sudo mkdir -p /var/lib/mysql_misc/tmp
sudo chown -R mysql. /var/lib/mysql_misc
sudo chmod 770 /var/lib/mysql_misc

Restart the mysqld.

Master declaration

Set up the two servers to be masters for each other. I'll show two ways to set up the servers. First, when you allow the direct connection to mysql port from the other server. And another, when you establish the connection using an ssh client.

For the direct method (on the 1st, on 2nd change the address appropriately):

mysql <<'EOF'
CHANGE MASTER TO
  MASTER_HOST='2nd.address',
  MASTER_PORT=3306,
  MASTER_USER='thiblorepl',
  MASTER_PASSWORD='thibloreplpw';

For ssh tunnel method:

mysql <<'EOF'
CHANGE MASTER TO
  MASTER_HOST='localhost',
  MASTER_PORT=3307,
  MASTER_USER='thiblorepl',
  MASTER_PASSWORD='thibloreplpw';

Replication users

Create the replication user on both machines.

mysql <<'EOF'
GRANT REPLICATION SLAVE ON *.* TO 'thiblorepl'@'%'
        IDENTIFIED BY 'thibloreplpw';
EOF

You can chose different passwords (or even different users) on the two servers, but then you have to change the two config files appropriately...

For the ssh tunnel method you may choose to use more specific 'thiblorepl'@'localhost' user definition.

You may grant the REPLICATION CLIENT privilege, then you can use the same user for monitoring the replication status.

Dump and restore, master data

On 1st server dump the databases:

mysqldump --master-data --lock-all-tables --databases thiblo > dbdump.db

Copy this file to 2nd and restore from it.

mysql < dbdump.db

By this, 2nd have the exact copy of our databases on 1st, and also have all the information needed for replication set up. But we still have to set the correct starting information for replication on 1st. This is done as follows:

On 2nd:

mysql <<'EOF'
SHOW MASTER STATUS;
EOF
# File    Position        Binlog_Do_DB    Binlog_Ignore_DB
# mysql-bin.000018        97283

On 1st:

mysql <<'EOF'
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=97283;
EOF

Be sure that there is no database access on 2nd between restoring the dump and querying master status.

Creating a tunnel between two servers

By default the mysql daemon only listens on the loopback interface. So it's not accessible from outside the host. And this is usually good. But this also means that we cannot just start just start replication now, because the two daemons won't be able to connect to each other.

But if your two servers both have public IP addresses, you are reasonably confident in MySQL's authentication and strength of your passwords and/or your firewall configuration... You can make them connect to each other directly. Just change bind-address to 0.0.0.0.

Otherwise, run the following ssh command. For example from 1st to 2nd.

ssh -N -L localhost:3307:localhost:3306 -R localhost:3307:localhost:3306 2nd

Start the slaves

Start the slave processes on both servers:

echo START SLAVE | mysql

Enjoy!

You can check the status of replication by issuing the following commands in mysql:

SHOW SLAVE STATUS \G
SHOW SLAVE HOSTS;

If all went well and both of your servers are permanently online, you can remove the skip-slave-start option from the config files. So after reboot the replication is started automatically. Of course you have to ensure that the tunnel is also established automatically on reboot. (Don't be afraid of starting replication and the tunnel in wrong order, or killing the tunnel without stopping the slave processes before it, and so on... The replication is designed to be quite robust and slave will automatically try to reconnect to the master if connection is lost. And they try it for quite a long time, as I understand. :)

Notes on MySQL replication

MySQL uses statement based replication. This means that modifying SQL statements like INSERT, UPDATE and DELETE are simply recorded on the master, transfered to the slave and then replayed there. This have some advantages (it's very easy to implement, a short statement that affects many rows don't require a lot of network traffic) and some very significant disadvantages that we need to understand to use it properly.

First, and most important consequence of the statement-based replication: database modifying statements have to be deterministic. Because, if there are non-deterministic statements the replicas will get out-of-sync. There are some important exceptions to this rule, most notably you can use NOW() and similar functions, as timestamps are recorded in the binary log.

Second, MySQL replication is asynchronous. This also have very important implications. Basically, they can be summarised in the following: modifying statements executed on different masters have to be order-independant.

Further tasks

This is only a very basic description on how to set up master-master replication in MySQL. If we want to use it in production, there is a lot of problems to solve.

We need to monitor the status of replication. We have to decide what to do if replication fails for some reason. Preferably, we need some mechanism to periodically check whether the replicas are still in sync.

We have to carefully examine whether our application satisfies the requirements posted by asynchronous statement-based replication, described in the previous section.

But for now, I suggest to set up replication and just do the development with it enabled. Then we'll get some impression on how it works, and can solve the problems as they arise.