LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

How to setup MySQL master slave replication

 lowendtutorial

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 about master slave replication

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 replication

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…testing the replication

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 on setting up MySQL master slave replication

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

mpkossen

20 Comments

  1. Nice tutorial Maarten.

    May 3, 2014 @ 1:19 pm | Reply
  2. Great work!, this is what I am looking for so far, mysql master slave and securing with ssh.

    May 3, 2014 @ 3:59 pm | Reply
    • May I ask why you don’t just use SSL on the server and require it for the connection?

      GRANT REPLICATION SLAVE ON *.* to ‘replication’@192.0.2.100 REQUIRE SSL IDENTIFIED BY ‘yourpassword’  REQUIRE SSL;
      

      May 3, 2014 @ 9:39 pm | Reply
  3. 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.

    Never ever run a slave without read_only

    Yes there are times when it’s legitimate, if you encounter those you KNOW! If you have to think about it you need read_only!

    May 3, 2014 @ 9:34 pm | Reply
  4. NeoNeo aka Zamah:

    Superp ^_^ just like I need ^_^

    May 4, 2014 @ 12:23 am | Reply
  5. So in the event of a failure, how do I switch? What do I do then? Except for that, every Nice tutorial.

    May 4, 2014 @ 7:40 am | Reply
  6. sepei:

    Is the same possible with mariadb and when how?

    May 5, 2014 @ 6:38 am | Reply
  7. Savas:

    Really great tutorial both the topic and tutorial itself. “Up next time: Getting started with SSL for MySQL” can’t wait to see the next episode :)

    May 5, 2014 @ 10:35 pm | Reply
  8. Tahir:

    Thanks For Sharing :)

    May 7, 2014 @ 12:49 pm | Reply
  9. Olaf:

    Thank you very much! As mariadb is “mysql reloaded”, it should be the same thing in the end.

    May 8, 2014 @ 2:23 pm | Reply
  10. agentmishra:

    can mysql replication be done in a cpanel-dns-only and a cpanel server?

    do reply

    May 13, 2014 @ 8:17 pm | Reply
  11. Really great work @Maarten Kossen. I appreciate it.

    May 14, 2014 @ 3:56 pm | Reply
  12. nice article to follow, better prepare than nothing

    January 26, 2015 @ 1:35 am | Reply
  13. its work. Thanks for sharing

    August 16, 2015 @ 4:01 am | Reply
  14. thx for sharing, works well.

    a follow-up with continually integrity checks between master and slave would be nice. All the replication is nice, until you find out there was a slight increasing drift between master and slave at dayX when you need it. with some monitoring in munin/nagios/inicinga

    Maatkit and checksum verify

    http://www.iheavy.com/2012/04/26/bulletproofing-mysql-replications-with-checksums/

    October 14, 2015 @ 1:15 pm | Reply
  15. Tutorial is very attractive, and easy to understand

    August 9, 2016 @ 10:49 am | Reply
  16. Thank you for sharing the information very useful. It is very pleasant to read this article from your website.
    http://gejalastroke.tasikstore.com/

    August 31, 2016 @ 3:56 am | Reply
  17. idwebsite:

    thanks great article.
    now i can install mysql into my vps debians

    May 12, 2018 @ 3:33 am | Reply

Leave a Reply to serverhorror Cancel reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *