MySQL replication how-to

This may be awkward to set up, but will work once you're done.

Fun facts:

• In case of a connection loss the slave will try to reconnect to the master server and resume replication for the next 24 hours
• If you want to use your slave as a "real" MySQL server, you basically need to switch off replication (STOP SLAVE; RESET SLAVE; and reset your my.cnf) and restart the MySQL daemon.

Master server configuration

• Create replication user

  In the MySQL shell:

  CopyCREATE USER 'replicator'@'%' IDENTIFIED BY 'some_password';
  GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
  

• Adjust MySQL configuration

  Edit /etc/mysql/my.cnf:

  Copyserver-id           = 1
  log_bin             = /var/log/mysql/mysql-bin.log
  replicate-do-db     = some_project_production
  replicate-do-db     = other_project_production
  replicate-ignore-db = mysql
  

  • server-id needs to be unique among all connected master and slave servers.
  • When replicate-do-db is provided only the chosen databases will be replicated; this can be set inside both the master and the slave configuration -- setting it on the master means a smaller binlog.
  • Setting replicate-ignore-db = mysql is generally recommended if you are not 100% sure you want to replicate the master's mysql database onto slaves; on Debian systems (and derivates like Ubuntu) there is a maintenance user which uses a different password on every system. Unless you change it to be the same among masters and slaves you most likely want to skip the mysql database.

• Restart your MySQL server

Dump the database for the slave

You can skip the following and use LOAD DATA FROM MASTER on the slave. Although, this is deprecated and may result in data loss!

• Stop your database via the MySQL shell:

  CopyFLUSH TABLES WITH READ LOCK;
  

• Do not close the shell as this would remove the read lock we just applied!

• Print and write down the current replication status. You need the File and Position values for the slave:

  CopySHOW MASTER STATUS;
  

  The result might look like this:

  Copy+------------------+----------+--------------+------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------------+----------+--------------+------------------+
  | mysql-bin.000001 |     1219 |              |                  |
  +------------------+----------+--------------+------------------+
  

• Dump the database (in a new terminal):

  Copymysqldump -uroot -p -r the_dump.sql some_project_production
  

• Remove the read lock (from the MySQL shell):

  CopyUNLOCK TABLES;
  

The master server will now resume operations. New queries will be appended to the log file and can be processed by the slave later on (which knows at which state the database was dumped).

Slave configuration

• Edit /etc/mysql/my.cnf:

  Copyserver-id           = 2
  replicate-do-db     = some_project_production
  replicate-ignore-db = mysql
  

Take care you pick a new unique identifier for server-id. You may also set replicate-do-db and replicate-ignore-db (see master configuration).

• Restart your (slave) MySQL server

• Load the dumped database

  • Copy the dump from the master to the slave (with scp)
  • Load it (for example SOURCE the_dump.sql in the MySQL shell)

Set up an SSH tunnel

• Manually, for testing purposes:

  Copyssh -L 3307:localhost:3306 10.50.0.101
  

  This maps the local port 3307 to port 3306 on 10.50.0.101 (you can check the connection via telnet localhost 3307)

• Is the ssh tunnel working you should use a tool like autossh to reconnect the ssh tunnel in case of a connection loss:

  Copy/usr/local/bin/autossh -f -M 5122 -g -N -C -L 3307:localhost:3306 10.50.0.101 -i /root/ssh_keys/keyfile -l username
  

  • -M 5122 defines a port for monitoring if the connection is up.
  • You should create an init script for this so the tunnel is set up upon the next server reboot.
  • Use keyfiles as you don't need to supply a password for connecting to the remote host.

Configure Replication

In the MySQL shell:

CopyCHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='replicator', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1219;

• MASTER_HOST and MASTER_PORT are set to the slave's side of the SSH tunnel (localhost:3307).
• MASTER_USER and MASTER_PASSWORD are the credentials of the previously created replication user.
• Values for MASTER_LOG_FILE and MASTER_LOG_POS are those you wrote down above (take care to provide the log position as a number, not a string).

Those data will reside in /var/lib/mysql/master.info and be loaded upon the next start of the MySQL slave server.

Start replicating

CopySLAVE START;

You can check the status of your slave with SHOW SLAVE STATUS.

If you don't get Yes for Slave_IO_Running the replication is not working -- you need to check /var/log/mysql.err and similar files.