Configuring MySQL Replication: Part 1
Previously in our high availability series, we looked at what high availability entails and how we can achieve such a system using Virtual Private Servers on the VPS.net public cloud. In this part, we’ll look at setting up Master-Master replication using MySQL for keeping the database in sync.
What Is Replication?
First, we’ll look at how this works. Replication is a process whereby the MySQL server can take changes made to the database, then pass them on to another MySQL server for it to make those same changes to its database too. There are two types of server in a replication set up: master servers where the database can be edited directly, and slave servers where the database is only updated by a master server. In a Master-Slave setup, the changes are made to the database on the master server, but data can be read from the master or any of the slave servers. What we’ll be looking at is a Master-Master setup where changes can be written to either database. This is generally not a recommended setup unless your software is specifically designed for it, as it is possible for changes made to one database to unintentionally overwrite changes on another. This will not pose a problem here because only one database will be written to at any given time, and the changes will be replicated to the other database.
Setting Up a Master-Master Replication
The first thing we need to do is to modify the MySQL configuration file to get it ready to work with replication. For these examples we’ll be using the IPs of 192.168.0.2 and 192.168.0.3 for the two VPSs, and 192.168.0.4 for the additional IP that can be assigned between them. You’ll need to substitute these IPs where they appear for the IPs of your servers. So let’s edit the MySQL configuration file. In this example I’ll be using nano, but you can use your preferred editor.
sudo nano /etc/mysql/my.cnf
Now find the line that reads:
bind-address = 127.0.0.1
Then, amend it to:
bind-address = 0.0.0.0
This will allow MySQL to communicate with remote services in addition to just local ones.
Next, find the lines starting with ‘server-id’ and ‘log_bin’ and uncomment them (remove the # before them). On the first server, the ‘server-id’ value should be set to 1, and on the second it should be 2. Now uncomment the ‘binlog_ignore_db’ line and set it to:
binlog_ignore_db = mysql
With these changes made, save and exit the file and then restart the MySQL server.
sudo service mysqld restart
With these changes made your MySQL server can now be accessed from remote systems which can present a security risk for your system. So the next step is to ensure that your firewall is blocking access from anywhere except your servers. Your VPS should have already come with the default firewall set to block all incoming traffic unless it is specified otherwise. To confirm, use the following command:
sudo iptables -L
You are looking for a line that says:
Chain INPUT (policy DROP)
If you find this line, then before the replication will work you’ll need to add a firewall rule on each server to allow MySQL to communicate. The following line can be used the same on each of the two servers, and the IP address will need to be changed to be the IP address of the other server than it is being run on. So on the first server you need to use the second server’s IP, and vice versa.
sudo iptables -I INPUT -m state –state NEW -p tcp –dport 3306 -s 192.168.0.3 -j ACCEPT
If you don’t have that line, then there will be more work involved in configuring your firewall. We’ll discuss configuring a firewall from scratch for the servers in a later article.
Next time, we’ll be looking at concluding the configuration of the MySQL servers and getting the Master-Master replication up and running.