Configuring MySQL Replication: Part 2
Welcome back to our series on creating a highly available solution using VPSs on our public cloud. In the last part we looked at configuring Master-Master configuration for MySQL on the servers. We had the MySQL configuration set to listen for public connections to the server and configured the iptables firewall to only allow remote connections from the other server in the pair. Now we’ll conclude by setting the MySQL replication going on the servers.
How to set up MySQL replication
To get things going we need to create a user to perform the replication for MySQL. So first we need to log into the MySQL server as root on the first server:
mysql -u root -p
Once signed in, issue the following commands, making sure to adjust the IP address for your setup and change password for a secure password. The IP address should be the one of the second server. We are creating a user that MySQL will connect with from the second server to replicate data.
CREATE USER ‘repl_user’@’192.168.0.3’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@’192.168.0.3′;
Now we need to get the position of the master log file: keep a copy of the output from this command:
SHOW MASTER STATUS;
With that done you can now exit the MySQL command line:
QUIT;
Now connect to the second server and repeat the process using the first server’s IP address to allow that to replicate in the other direction:
mysql -u root -p
CREATE USER ‘repl_user’@’192.168.0.2’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@’192.168.0.2′;
SHOW MASTER STATUS;
Again, make a note of the master status information.
Now we can go back to the first server and start the replication, again by logging into the MySQL command line:
mysql -u root -p
Now run the following commands, changing the IP to match the second server’s IP, the password to match the password, the master log file to match the file shown in the SHOW MASTER STATUS output on the second server, and the MASTER_LOG_POS to match the second server’s position:
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST = ‘192.168.0.3’, MASTER_USER = ‘repl_user’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘filename.log′, MASTER_LOG_POS = XX;
SLAVE START;
Now you can check the status of its replication from the other server with:
SHOW SLAVE STATUS\G;
Note that the “\G” isn’t a typo, and is used to inform MySQL to provide a different output formatting which can make this command’s output more readable.
Finally, we repeat the process on the second server, noting that this time the IP address should be the one for the first server, and then that logfile and position should be the ones we got from the first server’s master status output:
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST = ‘192.168.0.2’, MASTER_USER = ‘repl_user’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘filename.log′, MASTER_LOG_POS = XX;
SLAVE START;
With that done, again you can check the replication status with:
SHOW SLAVE STATUS\G;
To test it you can make a change on one of the servers and see if it replicates over to the other server. To do that we’ll create a test database on the second server:
CREATE DATABASE `test`;
Now if you go back to the first server you can use the following command at the MySQL command line to see if the database copied over:
SHOW DATABASES;
The replication should be almost instant unless there are network issues between the servers. If it doesn’t appear within a few minutes then you’ll need to check that the two servers are communicating ok. Otherwise, if it replicated fine we can delete the database on the first server:
DROP DATABASE `test`;
With this done you can now go back over to the second server to check if the test database has been deleted:
SHOW DATABASES;
If it has gone then you are all good, and the Master-Master replication is working and successfully duplicating the database between each server. Next time we’ll look at how we can duplicate the files for our website between the servers using Unison.