How to Set Up Master Slave Replication in MySQL

Share via:

Post Views: 0

About MySQL replication

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.

Advantages of replication in MySQL include:

Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.

Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.

Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.

Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.

This write-up will cover a very simple example of MySQL replication, 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 one of the slave.

192.168.9.73 – Master Database
192.168.9.74 – Slave Database

Setup

This article assumes that you have user with sudo privileges and have MySQL installed.

Step One—Configure the Master Database

On master we need to have following things done

Master is allowed to listen on port 3306
Replication user on master which should be allowed to connect from slave
Binary log should be enabled on master
Integer server id which should different from slave

Open up the mysql configuration (/etc/my.cnf) file on the master server.

log_bin
server-id = 100
binlog_format = ROW

log_bin

server-id = 100

binlog_format = ROW

Refresh MySQL. service mysqld restart

Open up the MySQL shell.

mysql -u root -p

1	mysql -u root -p

We need a replication user

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

1	GRANT REPLICATION SLAVE ON . TO 'slave_user'@'%' IDENTIFIED BY 'password';

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.

FLUSH TABLES WITH READ LOCK;

1	FLUSH TABLES WITH READ LOCK;

Then type in:

SHOW MASTER STATUS;

1	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 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

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 with the database still locked, bckup 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  --routines --triggers --events –all-databases > AllDB.sql

1	mysqldump -u root -p --routines --triggers --events –all-databases > AllDB.sql

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

UNLOCK TABLES;
QUIT;

1 2	UNLOCK TABLES; QUIT;

Now you are all done with the configuration of master

Step Two—Configure the Slave Database

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.

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

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

1	mysql -u root -p < /path/to/AllDB.sql

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

sudo nano /etc/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               = 200
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log

server-id = 200

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

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

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 mysqld restart

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

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

CHANGE MASTER TO 
MASTER_HOST='192.168.9.73',
MASTER_USER='slave_user', 
MASTER_PASSWORD='password', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=  107;

CHANGE MASTER TO

MASTER_HOST='192.168.9.73',

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;

1	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

1	SHOW SLAVE STATUS\G

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform

Share via:

Note: Please test scripts in Non Prod before trying in Production.

4 thoughts on “How to Set Up Master Slave Replication in MySQL”

raj

February 10, 2018 at 3:43 pm

Very nice session with you Abdul.
Thank you.
Hemant

February 15, 2018 at 6:09 pm

Do you take trainings for MySQL if so please share course content…
Nagendar

February 16, 2018 at 3:29 am

Really nice session with detailed explanation.Easy to understand and we can become MySQL masters in short time.
Thanks for valuable session Abdul.
Nara

February 16, 2018 at 4:40 pm

awesome session, very nice.thank you