Over the past few years, databases have become increasingly important. Not only for businesses, but also for personal purposes. Almost every website requires a database these days, as the information we want to share is mostly dynamic. But also because the tools are freely available to just set up a website with a script and a database and get going. These databases usually keep running, but what happens if the server crashes? Or even worse, when the provider disappears? And what if you want to make a backup of your huge database without having it affect your server's performance?
In my opinion, master-slave replication for MySQL is a solution for all those things. It gives you two copies of your database: the "live" one and the backup one. You always write your data to your master and read from the master too, but you will always have an up-to-date copy on your slave. In case everything goes south, you can easily switch to the slave and make it your master.
I'm going to show you how to set up master-slave replication for MySQL with an existing database. If you don't have an existing database, you can skip some steps, which I'll indicate. I've written this tutorial for Ubuntu 12.04 LTS or higher. It should work on other Linux distributions as well, though the installation of MySQL will be different and the config files may be in a different position. I'm also assuming MySQL 5.5 or higher.
A few things you need to know
Replication is not hard, it's actually quite easy. But there are some things you need to know about master-slave replication:
- Once you've set up your replication, you shouldn't be writing data to the slave. This will get everything out of sync and could seriously break your databases.
- Once you've had the situation where your master was unavailable and you've switched to your slave, your slave has become your master. There is no turning back from that point. In such a case you could make your old master your new slave (just follow the steps for setting up your slave while there's existing data on your master).
- You can write to your master and read from your slave. Just keep in mind that when there's a lot of queries, replication could theoretically slow down. This means that there could be a small period where you've added the data to the master but it's not visible on the slave yet.
Anyway, now you know this, let's get working on the real thing!
Setting up the master
If you have a brand new server, update the APT caches and install the MySQL server:
sudo apt-get update; sudo apt-get install mysql-server
During the installation you may be asked for a root password several times. Pick a strong one and fill it in during the first request. Just press ENTER for rest of the requests, as it will just keep the password you've first filled in.
Now, open up the my.cnf file, which contains your database configuration:
sudo vim /etc/mysql/my.cnf
Change this line:
bind_address = 127.0.0.1
To this:
bind_address = 0.0.0.0
This ensures MySQL is listening on "all IP addresses", or actually, not on a specific one. This does allow any host to connect to the server, just not log in. If you want to limit the host allowed to connect to MySQL you could set up some IPtable rules.
Also, uncomment the following lines:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
The 'server-id' option indicates which server this is within the "replication network". Your slave will get number 2. The 'log_bin' option tells MySQL to maintain a binary log, which contains all your queries in binary format. This is the log used for replication. MySQL cycles the log automatically and it is limited to 100MB in size, so it will never get bigger than that. That shouldn't be necessary anyway.
After having changed these options, restart the MySQL server:
sudo service mysql restart
And log into it and root with the password you've just picked:
mysql -u root -p
The final step on the master is adding a user account for the slave server. The slave uses that account to log in to the master in order to perform the replication. Look up your slave's IPv4 address for this, as you want to limit the logins from that account to just your slave server. Now, execute the following query:
GRANT REPLICATION SLAVE ON *.* to 'replication'@192.0.2.100 IDENTIFIED BY 'yourpassword';
Replace the IP address with your slave's IPv4 address and replace 'yourpassword' with a strong password. Execute the query. It should say 'Query OK'.
Now, that's that for the master. Let's head on to the slave. We'll return to the master when the slave is done.
Setting up the slave
On the slave, update the APT caches and install the MySQL server:
sudo apt-get update; sudo apt-get install mysql-server
During the installation you may be asked for a root password several times. Pick a strong one and fill it in during the first request. Just press ENTER for rest of the requests, as it will just keep the password you've first filled in.
Now, open up the my.cnf file, which contains your database configuration:
sudo vim /etc/mysql/my.cnf
And uncomment the following line:
server-id = 2
This number can be any number, just not the same as the one on your slave.
Save the file and restart the MySQL server:
sudo service mysql restart
And log into it with the root password you've just picked:
mysql -u root -p
If you already have data on the master, follow these steps:
I'm using plain mysqldump to migrate data in my example. In certain situations this may not be a good option, as not all data can be properly migrated using mysqldump. A tool like xtrabackup () is a solution for that, as it works a lot better than mysqldump. For simple cases, though, mysqldump should suffice.
Before we start migrating data, we should note the current status of the master. On the master, run the following query:
SHOW MASTER STATUS;
This should give you the following sort of overview:
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000002 | 107 | | |
+——————+———-+————–+——————+
Please note the filename ('File') and number ('Position'). Remember these or write them down. You will use this to start replication on the slave. This will make sure you don't miss any data on your slave; data that could be written to your master between the moment of dumping data and having it imported on your slave.
Now, let's make a dump of all the databases on your master which should go to your slave:
mysqldump –databases database1 database2 database3 -u root -p > data.sql
Replace 'database1′ through 'database3′ with your database names. You can remove and add databases to cater to your needs. Once you hit enter, use the root password from before.
Copy the data to your slave and import it to MySQL:
cat data.sql | mysql -u root -p
The data should now be on your slave as well.
End of steps for masters with existing data.
It's now time to make the slave aware of the master. In other words: make it an actual slave. If you did not migrate any data, execute the following query:
CHANGE MASTER TO MASTER_HOST='192.0.2.100′, MASTER_USER='replication', MASTER_PASSWORD='yourpassword';
Replace the IP address with your master's IPv4 address and 'yourpassword' with the password you've given the replication user on the master.
If you did migrate data, use the following query:
CHANGE MASTER TO MASTER_HOST='192.0.2.100′, MASTER_USER='replication', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000002′, MASTER_LOG_POS=107
Replace the IP address with your master's IPv4 address and 'yourpassword' with the password you've given the replication user on the master. Replace 'mysqld-bin.000002′ with the filename you wrote down from your master. Replace '107′ with the number you've written down. This tells the slave from what position of the binary log it needs to start replicating. This ensures all data that has been added since you've dumped the databases is also added to the slave.
Now, start the slave:
SLAVE START;
And you're done! If you want to track the progress/status of your slave, use the following query:
SHOW SLAVE STATUS;
This should give you a bit of a data overload with your slave's status. If all is well, the first column should read something like:
Waiting for master to send event
Meaning all is well.
And now, the big moment…
It's time to actually test the replication. On the master, execute the following query:
CREATE DATABASE lowendbox;
Now, on the slave, run the following query:
SHOW DATABASES;
If your replication has been set up correctly, the 'lowendbox' database should be listed on your slave as well.
Now, go have some fun with setting up tables and executing queries. Just remember: only write to your master!
Final notes
While replication is now working fine after having followed this guide, it's still relatively insecure. Data between the master and the slave is not encrypted. That's why I'm going to show you how to enable SSL for MySQL in my next tutorial.
If you have the luxury, it is best to set up replication over an internal network. This ensures the data is not sent over the internet and reduces the risk of it being tapped somewhere (if at all).
Up next time: Getting started with SSL for MySQL
armorshark.com, exclusive, freebsd, kvm, phoenix, ssd, USA 
May 19, 2014 @ 1:00 am, by Maarten Kossen