How To Set Up Master Slave Replication in MySQL

[Total: 6    Average: 5/5]

Master Slave Replication in MySQL is probably the most popular high availability solution for MySQL. Master Slave Replication in MySQL 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 tutorial will use the following things to carry the Master Slave Replication in MySQL

Linux OS : CentOS 6.3
Master and Slave are CentOS 6.3 Linux Servers.
Master IP Address is: 192.99.152.195
Slave IP Address is: 192.99.152.196
Master and Slave has MySQL version installed.
Master allow remote MySQL connections on port 3306.
Step 1: Configure the Master Server (192.99.152.195)

This article assumes that you have MySQL installed. If you do not have mysql, you can install it with this command:

yum install mysql-server mysql

Configure the Master Database

Open up the mysql configuration file on the master server.

vi /etc/my.cnf

Add the following entries under [mysqld] section and don’t forget to replace replication with database name that you would like to replicate on Slave.

server-id = 1
binlog-do-db=replicationdb
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
server-id = 1

server-id must be unique and cannot match any other server-id in your replication group. You can choose any number for this spot (it may just be easier to start with 1). I’m going to go ahead and call this one 1.

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

This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log.

binlog-do-db=replication

we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

Restart the MySQL service.

# /etc/init.d/mysqld restart

The next steps will take place in the MySQL shell, itself.

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';

Follow up with:

FLUSH PRIVILEGES;

The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.

In your current tab switch to “replicationdb”.

USE newdatabase;

Following that, lock the database to prevent any new changes:

FLUSH TABLES WITH READ LOCK;

Then type in:

SHOW MASTER STATUS;

You will see a table that should look something like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | replicationdb  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

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

mysqldump -u root -p --opt replicationdb > replicationdb.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

UNLOCK TABLES;
QUIT;

Now you are all done with the configuration of the the master database. We have successfully completed Master Slave Replication in MySQL step 1.

Step 2: Configure the Slave Server (192.99.152.196)

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.

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):

CREATE DATABASE replicationdb;
EXIT;

Import the database that you previously exported from the master database.

mysql -u root -p replicationdb < /path/to/replicationdb.sql

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

nano /etc/mysql/my.cnf

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.

server-id = 2

Following that, make sure that your have the following three criteria appropriately filled out:

relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = replicationdb

You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

service mysql restart

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST='192.99.152.195',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:

It designates the current server as the slave of our master server.
It provides the server the correct login credentials
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.
With that—you have configured a master and slave server.

Activate the slave server:

START SLAVE;

You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.

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

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

All done with Master Slave Replication in MySQL.