How to set up MySQL Replication Tutorial

One may setup MySQL replication typically either to scale out, facilitate reporting or to provide backups of MySQL databases. I wrote a post about this before.

The whole process relies on binary logs which are output to a location on the master server and read in by the slave.

This post is a MySQL replication tutorial. I am going to setup MySQL replication involving two MySQL servers, a master and a slave. This post assumes that the servers are new and that no databases have been created on them yet.

I am not going to list every possible setting or configuration. Just necessary settings to get you up and running.

The mysql config file will require changes on both the master and slave. Ensure that there are no settings in the mysql configuration file that would cause any networking issues. Turn off –skip-networking and ensure that –bind-address is correctly set.

1/ Basic settings for configuring the master server for MySQL replication

• server-id – each server in your configuration needs its own unique id. Start at 1 up to 32^
• log-bin – this is the binary log file prefix, so make this something like “mysql-bin”, “bin-log” etc. Files will be output according to the path which you specify. The user that mysql is running as needs permission to write to the path specified.
• expire-logs-days – the number of days to retain your binary logs for. Set this to ensure that logs are not removed prematurely resulting in slave data loss if it were ever to fall behind.
• binlog-format – read the documentation carefully on this one. It’s a really important config option which if set incorrectly can mean that your slave contains different data to what was written to the master. Possible values; STATEMENT, ROW and MIXED

Now restart your mysql service on the master to apply the settings and check your variables have been persisted and that the binary logs exist in the path which you set.

2/ Basic settings for configuring the slave server for MySQL replication

• server-id – each server in your configuration needs its own unique id. Start at the lowest number greater than the master server_id up to 32^
• relay-log – the slave will read it’s own relay log and apply the replicated commands to its copies of the databases. It isn’t critical that it’s set as the server will create it’s own relay log location but I always ensure that it’s set from the start so that I know where the relay logs are being output to. Once again the mysql user needs permission to the path where the relay logs are.

Restart the slave mysql service and check that those settings have been persisted.

For some useful optional settings, check out the MySQL documentation for all that are available.

3/ Create a user for replication on the master

The slave needs to authenticate to the master. You create a new user on the master and specify the user created which the slave will authenticate as.

You could use an existing user but logically you wouldn’t right? It is not recommended for a couple of reasons:

• If the user account has to be deactivated – in the event of someone leaving the company or some application login that is no longer required, it would break replication.
• Permissions – replication needs specific permission to operate and you create your MySQL user with only the permissions required to perform the specific function. If the user account is compromised it serves as damage limitation to create user accounts with defined permissions only.

[sourcecode language=’sql’]GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’yourhostname.com’ IDENTIFIED BY ‘EnterSecurePasswordHere’;[/sourcecode]
4/ Record binary log co-ordindates on the master

The purpose of the binary log is to record all changes to the database that need to be applied on the slave. At this point, you need to find the master binary log co-ordinates and record them which you will then apply to the slave. When replication is started, it will know at what point in the log to start reading and applying updates to its copies of the master server’s databases.

You should prevent any updates to the master and then record the log position. This is easy to do with a one liner command:
[sourcecode language=’sql’]FLUSH TABLES WITH READ LOCK;[/sourcecode]
Now leave the connection window open, open up a new connection and run this to get the log position:
[sourcecode language=’sql’]SHOW MASTER STATUS;[/sourcecode]
Here you can see the current file that the master is writing too and the current log position. This information will be used later to configure the slave.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      154 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5/ Configure the slave with master binary log details and start it running

Connect to the slave, run a CHANGE_MASTER_TO with the settings recorded in step 4
[sourcecode language=’sql’]CHANGE MASTER TO
MASTER_HOST=’master.yourdomain.com’,
MASTER_USER=’replication’,
MASTER_PASSWORD=’EnterSecurePasswordHere’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000002′,
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
[/sourcecode]

On the slave, start it by issuing this :
[sourcecode language=’sql’]START SLAVE;[/sourcecode]
6/ Unlock tables on the MASTER and check status on master and slave

On the master:
[sourcecode language=’sql’]UNLOCK TABLES;[/sourcecode]
On the slave:
[sourcecode language=’sql’]SHOW SLAVE STATUS;[/sourcecode]

Check to see if there are any errors reported and ensure that settings you added above are correctly set in. Check Master_Log_File and Read_Master_Log_Pos

Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154

7/ Testing

Create a new empty database and ensure that it appears on the slave.

You’re now ready to begin loading data onto your master if replication is working correctly.

This is a basic mysql master slave replication tutorial to get you going but there are more details config changes that you can make depending on your application and server requirements. I do encourage you to read the relevant documentation so that you are fully informed on all the possibilities and implications.