Creating MySQL replicas for scaling and high availability in Ubuntu

When your application is small, you can use a single MySQL server for all your database needs. As your application becomes popular and you get more and more requests, the database starts becoming a bottleneck for application performance. With thousands of queries per second, the database write queue gets longer and read latency increases. To solve this problem, you can use multiple replicas of the same database and separate read and write queries between them.

In this recipe, we will learn how to set up replication with the MySQL server.

Getting ready

You will need two MySQL servers and access to administrative accounts on both.

Make sure that port 3306 is open and available on both servers.

How to do it…

Follow these steps to create MySQL replicas:

Create the replication user on the Master server:

$ mysql -u root -p

mysql> grant replication slave on *.* TO ‘slave_user’@’10.0.2.62’ identified by ‘password’;

mysql> flush privileges;

mysql> quit

Edit the MySQL configuration on the Master server:

$ sudo nano /etc/mysql/my.cnf

[mysqld]

bind-address = 10.0.2.61 # your master server ip

server-id = 1

log-bin = mysql-bin

binlog-ignore-db = “mysql”

Restart MySQL on the Master server:

$ sudo service mysql restart

Export MySQL databases on the Master server. Open the MySQL connection and lock the database to prevent any updates:

$ mysql -u root -p

mysql> flush tables with read lock;

Read the Master status on the Master server and take a note of it. This will be used shortly to configure the Slave server:

mysql> show master status;

Open a separate terminal window and export the required databases. Add the names of all the databases you want to export:

$ mysqldump -u root -p --databases testdb > master_dump.sql

Now, unlock the tables after the database dump has completed:

mysql> UNLOCK TABLES;

mysql> quit;

Transfer the backup to the Slave server with any secure method:

$ scp master_backup.sql ubuntu@10.0.2.62:/home/ubuntu/master_backup.sql

Next, edit the configuration file on the Slave server:

$ sudo nano /etc/mysql/my.cnf

[mysqld]

bind-address = 10.0.2.62

server-id = 2

relay_log=relay-log

Import the dump from the Master server. You may need to manually create a database before importing dumps:

$ mysqladmin -u admin -p create testdb

$ mysql -u root -p < master_dump.sql

Restart the MySQL server:

$ sudo service mysql restart

Now set the Master configuration on the Slave. Use the values we received from show master status command in step 5:

$ mysql -u root -p

mysql > change master to

master_host=’10.0.2.61’, master_user=’slave_user’,

master_password=’password’, master_log_file=’mysql- bin.000010’,

master_log_pos=2214;

Start the Slave:

mysql> start slave;

Check the Slave's status. You should see the message Waiting for master to send event under Slave_IO_state:

mysql> show slave status\G

Now you can test replication. Create a new database with a table and a few sample records on the Master server. You should see the database replicated on the Slave immediately.

How it works…

MySQL replication works with the help of binary logs generated on the Master server. MySQL logs any changes to the database to local binary logs with a lightweight buffered and sequential write process. These logs will then be read by the slave. When the slave connects to the Master, the Master creates a new thread for this replication connection and updates the slave with events in a binary log, notifying the slave about newly written events in binary logs.

On the slave side, two threads are started to handle replication. One is the IO thread, which connects to the Master and copies updates in binary logs to a local log file, relay_log. The other thread, which is known as the SQL thread, reads events stored on relay_log and applies them locally.

In the preceding recipe, we have configured Master-Slave replication. MySQL also supports Master-Master replication. In the case of Master-Slave configuration, the Master works as an active server, handling all writes to database. You can configure slaves to answer read queries, but most of the time, the slave server works as a passive backup server. If the Master fails, you manually need to promote the slave to take over as Master. This process may require downtime.

To overcome problems with Master - Slave replication, MySQL can be configured in Master-Master relation, where all servers act as a Master as well as a slave. Applications can read as well as write to all participating servers, and in case any Master goes down, other servers can still handle all application writes without any downtime. The problem with Master-Master configuration is that it’s quite difficult to set up and deploy. Additionally, maintaining data consistency across all servers is a challenge. This type of configuration is lazy and asynchronous and violates ACID properties.

In the preceding example, we configured the server-id variable in the my.cnf file. This needs to be unique on both servers. MySQL version 5.6 adds another UUID for the server, which is located at data_dir/auto.cnf. If you happen to copy data_dir from Master to host or are using a copy of a Master virtual machine as your starting point for a slave, you may get an error on the slave that reads something like master and slave have equal mysql server UUIDs. In this case, simply remove auto.cnf from the slave and restart the MySQL server.

There’s more…

You can set MySQL load balancing and configure your database for high availability with the help of a simple load balancer in front of MySQL. HAProxy is a well known load balancer that supports TCP load balancing and can be configured in a few steps, as follows:

Set your MySQL servers to Master - Master replication mode.

mysql> create user ‘haproxy_admin’@’haproxy_ip’;

mysql> grant all privileges on *.* to ‘haproxy_admin’@’haproxy_ip’ identified by ‘password’ with grant option;

mysql> flush privileges;

Next, install the MySQL client on the HAProxy server and try to log into the mysql server with the haproxy_admin account.

Install HAProxy and configure it to connect to mysql on the TCP port:

listen mysql-cluster

bind haproxy_ip:3306

mode tcp

option mysql-check user haproxy_check

balance roundrobin

server mysql-1 mysql_srv_1_ip:3306 check