How to Create MySQL Master Slave Cluster on Ubuntu 14

Overview

As your mobile or web application grows in popularity, there will come a time when your single database server is unable to handle the load. Eventually, you are going to need a cluster of database servers to handle your different workloads (reads and writes). The simplest solution is known as a Master\Slave configuration. Typically with this configuration, you will have at least one database server that handles all rights (the master) and at least one other to handle reads (the slave).

If the ratio of read requests is higher than writes, which is the case for most applications, and your single read database server is starting to find it more difficult to keep up, you can add additional read servers to your cluster. This is known as scaling your database servers horizontally.

However, if the ratio is the opposite – more writes than reads – you will instead want to look into sharding, which is out of the scope of this tutorial.

Server Configuration

The following configuration will be referenced in this tutorial, to make it easier to follow along.

Hostname	Role	Public IP	Private IP
mysql01.serverlab.intra	Master Server	64.54.200.32	10.2.0.15
mysql02.serverlab.intra	Slave Server	64.54.200.33	10.2.0.16

Installing MySQL

The following instruction will guide you through installing MySQL Server.

Update your local package database cache.
```
sudo apt-get update
```
Install MySQL server.
```
sudo apt-get install mysql-server
```
When prompted during the installation, set your MySQL Server’s Root account password. Ensure it is complex and difficult to guess.

Configuring the Master Server

The master server is the only database server that permits writes. This is to ensure data consistency across your entire cluster, which in our case is only three servers. You are going to want to make this server a little beefier than your slaves.

Prepare Server for Replication

The default installation of MySQL does not permit master\slave replication. We need to configure a few options that enable replication.

Open the MySQL Server configuration into a text editor.
Under the [mysqld] section of the configuration file, find the bind-address option. Un-comment it and change its value to the IP address that will communicate with your application and the other MySQL servers. You may enter 0.0.0.0 as the IP address to allow communication on any interface, however, this may be less secure. In this tutorial, we’ll want all communications to happen over our private network.
```
bind-address            = 10.2.0.15
```

Also under the [mysqld] section, find the following lines:

#server-id              = 0
#log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

Uncomment the server-id line and change its value to 1. Also, uncomment the log_bin line.

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

Save your changes and exit the text editor.
Restart MySQL to apply your changes.
```
sudo service mysql restart
```

Create Replication User Account

Your slave servers will require a user account that allows them to request replica data from the master. This account should have access to whichever database(s) you want to replicate to your slaves.

Log into the MySQL Server console.
```
mysql -u root -p
```
When prompted, enter your MySQL’s Root account password.

Create the user account.

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'supersecretpassword';

Grant replication rights to the user account.

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

Flush the privileges table and apply your changes.
```
FLUSH PRIVILEGES;
```

Get Master Information

Before your slaves can start replicating data from your databases, they need to know the log file of the master server that records changes, as well as the location in the log file of the most recent change. Do the following on the master.

Get the current status of the master server.
```
show master status
```

The output should look similar to this example.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Record the File value and the Position value.
Exit the MySQL console.

Configure the Slaves

Prepare Replication

Use the following instruction on each of your slave servers.

Open the MySQL Server configuration into a text editor.
Under the [mysqld] section of the configuration file, find the bind-address option. Un-comment it and change its value to the IP address that will communicate with your application and the other MySQL servers.
```
bind-address            = 10.2.0.16
```