Master-Slave Replication In MySQL

About MySQL Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave. 

This tutorial will use the following IP addresses:
172.16.1.28 - Master Database
172.16.1.17 - Slave Database

1.      Setup

>  sudo su

>  apt-get install mysql-client

>  apt-get install mysql-server

2.      Configure the Master Database

Open up the mysql configuration file on the master server.

>  nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes. The changes are :

bind-address              = 172.16.1.28

server-id                     = 1

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

binlog_do_db             = newdatabase

Now, Refresh MySQL 

>  service mysql restart

Open up the MySQL Shell

>  MySQL -u root –p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

>  GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

>  FLUSH PRIVILEGES;

Switch to new database

>  USE newdatabase;

>  FLUSH TABLES WITH READ LOCK;

>  SHOW MASTER STATUS

Note : Remember File name and position. Here, I’m assuming File- mysql-bin.000001 and Position- 107

>  Exit;

Proceeding the with the database still locked, export your database using mysqldump (make sure you are typing this command in the bash shell, not in MySQL)

>  mysqldump –u root –p –opt newdatabase > newdatabase.sql

>  mysql –u root –p

>  UNLOCK TABLES;

>  QUIT;

3.      Configure Slave Database

>  sudo su

>  apt-get install mysql-client

>  apt-get install mysql-server

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

>  mysql –u root –p

>  CREATE DATABASE newdatabase;

>  EXIT;

Now we need to configure the slave configuration in the same way as we did the master:

>  nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes. The changes are :

server_id                    = 2

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

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

binlog_do_db = newdatabase

Restart MySQL

>  service mysql restart

>  mysql –u root –p

>  CHANGE MASTER TO MASTER_HOST=’172.16.1.28’,MASTER_USER=’slave_user’,MASTER_PASSWORD=’password’,MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=107;

This command accomplishes several things at the same time:

1.      It designates the current server as the slave of our master server.

2.      It provides the server the correct login credentials

3.      Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

>  START SLAVE;

>  SHOW SLAVE STATUS\G

If there is an issue in connecting, you can try starting slave with a command to skip over it:

>  STOP SLAVE;

>  SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;

>  START SLAVE;

All Done.

Happy to help you !!