MySQL is one of the highly used open source relational database. Configuring a standalone MySQL is really simple and straightforward, and it only involves installing the mysql-server package and creating the required databases inside for your applications to read and write to.  Although a single standalone MySQL database server does serve the purpose in a majority of the cases, a replicated MySQL is recommended for production use cases for the below reasons.

• High Availability of the database
• Easy backups without locking actively accessed database

What is MySQL Replication? 

MySQl replication is nothing but a method available in MySQL to create multiple copies of the same database. Modifications on the active database will be automatically replicated to the slave MySQL node.

One Server will be MySQL Master and the other servers will be acting as MySQL slave servers. MySQL master server will write all transaction details to a log file (generally called as binary log file), and slave servers will request updates from MySQL Master server.

Slave MySQl server will maintain the place till where it has already got updates from master, and will only ask for the transactions that are next in the master binary log file.

The main important fact to keep in mind is that, the master does not care about what updates the slave already has, or what slave does not have. It only gives out the details of the transactions that the slave asks for(Provided the requesting slave server has access credentials for getting details from master server).

On the slave, the transactional updates fetched from MySQL master server is written to another log file called relay log(Relay log is nothing but numbered files on the slave server, that has details about different transactions fetched from master).

MySQL Slave server then executes the transactions it recorded on itself from relay log files.

Another important fact to keep in mind is the fact that you can benefit hugely from MySQL replication, if your database is read intensive(because in that case you can spread the read requests across multiple slave servers). However, if your application is write intensive, then at times replication can even be an overkill and can reduce performance as well. 

Please do not confuse replication with backup. If you do a DELETE operation on MySQL master server, that DELETE operation will also be executed on the slave server. 

First let’s get started by installing MySQL on our master and Slave Server. This can be done by simple apt-get or yum commands as shown below.

Installing MySQL Server

Installing MySQL in Ubuntu is just an apt-get command away. 

#sudo apt-get install mysql-server mysql-client

In CentOS, you can do this as shown below.

#sudo yum install mysql-server

Configuring MySQL Master Server 

The first step is to modify my.cnf file with required parameters(/etc/my.cnf in CentOS & /etc/mysql/my.cnf in Ubuntu).

bind-address            = 10.1.1.23

server-id               = 1

binlog-do-db            = testdb

log-bin                 = /var/log/mysql/mysql-bin.log

bind-address: This is pretty straight forward, and stands for the ip address where MySQL service should listen.

server-id: This option will be used on both the master and the slave, to identify each other uniquely.

binlog-do-db: This specifies the database that you want to replicate (basically operations on these databases will be written to binary log file on the master server.). If you want multiple databases to be replicated, you can add multiple binlog-do-db parameter multiple times.

If you do not specify the above option, the default operation is to record all database operations in binary log file. Let’s say you want to sync all operations made on all databases to the slave server, in that case the best method to exclude some databases of your interest from replication, is to use another option called “binlog-ignore-db” in my.cnf file(using this option will replicate all but not the databases mentioned under this option).

binlog-ignore-db            = testdatabase1,testdatabase2

log-bin: Specifies the location of the log file, as evident from the above shown configuration.

Now you need to restart MySQL Service as shown below.

#sudo service mysql restart

The next step is to create a database user on MySQL Master server, that our slave server will use to fetch transaction updates from master.

On the master server, login to MySQL and execute the below Command.

GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@’10.1.1.24’ IDENTIFIED BY ‘acomplexpassword’; 

In the above command 10.1.1.24 is our slave server IP address (You can replace it with % sign to allow access from any source address, but is not recommended). Now execute the below command.

FLUSH PRIVILEGES;

Now, without leaving the MySQL client session run the below commands.

USE testdb;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

+—————+———-+————–+——————+

| File          | Position | Binlog_do_db | Binlog_ignore_db |

+—————+———-+————–+——————+

| mysql-bin.002 | 108      | testdb    |                  |

+—————+———-+————–+——————+

1 row in set (0.00 sec)

Note down the output of the above command (we will be using that while we start our slave synchronization from master.)

Configuring MySQL Slave Server

On the slave server, let’s first create our “testdb” database (which we are replicating in this example.). This can be done by running the below simple commands.

#CREATE DATABASE testdb;

#quit;

Now we will have to configure our Slave server with master details, and also tell that its a slave server, and has to fetch updates of our required database from master.

This is done the same way as we configured our MySQL master server (ie: by modifying my.cnf file)

bind-address            = 10.1.1.24

server-id               = 2

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = testdb

master-host             = 10.1.1.23

master-user             = replication_user

master-password         = ourmasterpassword

master-connect-retry    = 60

Note that we gave server-id as 2 here, and also a relay log path, and binary log path, and the database name(most of the options are same as MySQL master server we did earlier). The additional options that we used are master-user(use the MySQL username that we setup on master server for replication), master-password(password of that replication user), and master-connect-retry.

Now let’s restart MySQL on slave as follows.

#sudo service mysql restart

In case your master server database (testdb in our case, which we are replicating in this example) already contains some data, then you can easily replicate that to the slave using the below command(from the mysql console).

#LOAD DATA FROM MASTER;

Once the above command succeeds, run the below command(from mysql console).

#SLAVE STOP;

Please keep the fact in mind that running the above command can lock the master server database. So do not do this on a production database with huge amount traffic. In such case, you can simply take a mysqldump from the master db, and restore it on slave database server.

Now run the below command in mysql console.

CHANGE MASTER TO MASTER_HOST=’10.1.1.23′,MASTER_USER=’replication_user’, MASTER_PASSWORD=’somecomplexpassword’, MASTER_LOG_FILE=’mysql-bin.002′, MASTER_LOG_POS=  108;

Please note the MASTER_HOST, MASTER_USER, MASTER_LOG_FILE (which we noted down when we ran SHOW MASTER STATUS on master server), and MASTER_LOG_PAS(which was also noted down from master server) values we have used in the command above.

Once the above command succeeds, execute the below.

SLAVE START;

quit;

Now we have a replicated MySQL database, where every change on master will be updated to slave server.