MySQL Master-Slave Replication: Setting Up

Very recently, at Fraudpointer.com, having reached a database of 25Gb after just six months of operation, we decided to start using replication for our backend persistent storage.

Why would you want to do that

Replication of MySQL can be a solution to various problems that you might want to solve:

• Scale-out solutions
• Data security
• Analytics
• Long-distance data distribution
• Backup taken from slaves, rather than from master

MySQL documentation on replication, that can be found here, is quite explanatory and gives more detail about these uses.

Where have we been?

Before implementing replication, we had a configuration such as the following: We had a debian squeeze machine (name: Plato) with MySQL 5.1. installed and serving all of Fraudpointer traffic. When we decided to implement replication it was already 25Gb. A lot of transactions took place per minute, and we were facing two major problems. 1)    We wanted to take daily backups and backup put a lot of load on the machine. Though the backup utility that we used (Percona xtrabackup) didn’t lock the database, the machine was getting quite unresponsive during backup time. 2)   We wanted to run heavy queries and get reports, very frequently. Just this was a good reason to start discussing replication.

Where are we after replication?

After MySQL replication, we are now in the following picture: We now have two machines with MySQL installed. The old one, Plato, is now acting as the Master and the new one, Ares, is now acting as the Slave of the replication scheme. In our setup, the advantages of this configuration are as follows: 1)    We can get the daily backups from the Slave. No load on the production Master machine for this task. 2)   We can get our reports from the Slave node. 3)   If the Slave node is down, this is not a problem at all. Replication is performed asynchronously and when the Slave node is up and live after a downtime, it continues replication from the point it has been paused. 4)   Records that are created or updated at Master machine, are almost instantly “transferred” to Slave machine (even if asynchronously).

How did we do that?

Here is the process that we followed to carry out the replication:

Assumptions

1. Both Master and Slave are debian squeeze machines.
2. Master has static IP: 10.100.10.80.
3. Slave has static IP: 10.100.10.103.
4. Master and Slave are on the same LAN.
5. Master has MySQL installed and you have the root password to connect to it from ‘localhost’. MySQL does not allow remote connections by default, and that was our case too.
6. Slave does not have MySQL installed yet.
7. Master debian allows incoming connections on 3306 port, the standard MySQL listening port.

PHASE 1

Overview

In the first phase of the work, the overview of what we did is as follows: 1)    On Master

1. Allow remote connections
2. Set bind-address
3. Enable binary logging
4. Establish a unique server ID
5. Restart server
6. Create a user for replication

2)   On Slave

1. Install MySQL
2. Stop MySQL
3. Set bind-address
4. Enable binary logging
5. Set unique server ID

Detailed Actions

On Master

1)    Allow remote connections Since you are about to change the bind address on your MySQL server, you are essentially allowing access to your MySQL server from “remote” machines. If you do not do that, after the change of the bind address and restarting of your MySQL server, users from remote or local machines will not be able to connect to your server. All the remote and local machine IPs need to be granted access for the users that access MySQL from these machines. The following mysql commands (which can be issued using mysql shell)

mysql> grant all on *.* to bar@’10.100.10.55’ identified by ‘bar_password’;

mysql> flush privileges;

allow the user ‘bar’ to connect to this MySQL server and to all databases from the machine with IP ’10.100.10.55’. This is necessary even if the user connects from the machine that MySQL server is installed. Hence, connect to your MySQL server using mysql and set these commands accordingly before proceeding to the following.  Don’t forget that this is true for the ‘root’ user too, even if he connects from the local machine. 2)   Set bind-address to the IP of the machine. This is done inside the my.cnf file. bind-address is a configuration parameter of the section [mysqld]. Set it to the IP of your Master machine. In our case, it was 10.100.10.80. Note that if you have enabled skip-networking you need to disable it. 3)   Set binary log file pattern The parameter is called log_bin and it should exist in the [mysqld] section. Note that the full path to the binary log file should be given. Example: /var/log/mysql/mysql-bin.log. Important gotcha: make sure that the folder/disk has enough space to write the binary logs there. 4)   Set server id This is the Master replication server ID. It has to be unique among all the ids used in the whole replication setup. For example, set this to “100” and make sure that you set “101” on your Slave (see later). The parameter is server-id and should be set in the section [mysqld]. 5)   innodb_flush_log_at_trx_commit and sync_binlog These should be set to “1”, both of them. According to MySQL documentation for the greatest possible durability and consistency in a replication setup using InnoDB with transactions … you have to do this. More on this you can find here. These parameters are set in section [mysqld]. 6)   RESTART MySQL Server Having done all the necessary changes on the configuration file of MySQL Server and having granted access to all existing users to be able to connect using their IP, you can restart your MySQL Server. 7)   Create user for replication You need to create a user that will have the right to work as the replication/slave client. The following commands:

mysql> create user ‘repl’@’10.100.10.103’ identified by ‘slavepassword’;

mysql> grant all on *.* to ‘repl’@’10.100.10.103’;

mysql> flush privileges;

are creating the user ‘repl’ and give him the right to do replication from the slave machine with IP 10.100.10.103 using the specified password.

On Slave

1)    Install MySQL Server On Slave machine you need to install MySQL Server. 2)   Stop MySQL server You do not need to have it running, for the time being. Proceed with changing MySQL configuration parameters to support Slave replication. 3)   bind-address Set bind-address to the value of the IP of your Slave machine. In our case was 10.100.100.103. The parameter should be set in the [mysqld] section. 4)   log_bin Set the full path to the binary log file. For example: /var/mysql/log/mysql-bin.log. The parameter is set in the [mysqld] section of the my.cnf file. Important gotcha:: make sure that your binary log folder has lots of space. You will need it. 5)   server-id You need to give the server ID for the Slave node for this replication setup. Put for example “101”. It has to be different from the server IDs of the Master and other Slave nodes in your configuration. Do not start Slave MySQL server. You still do not need to do that. You are done for now. Phase 1 is finished. In Phase 2, we will start replication.

Frequently Asked Questions on MySQL Master-Slave Replication