Configuration of Master-Slave Mysql Replication

About Mysql Replication:

Mysql Replication is a process that allows to copied or transferred data from one MySQL server to another in a faster way.  The master server will copy all its data to slave server without consuming much time. That can helpful for many reasons like a backup of the database, debug the database without using the main database, or simply as means to scale out.

This article will helps you to setup MySQL replication. We will cover simple example of MySQL replication where one master server will send or copied information to the single slave server. For the process to work, you will need two IP addresses: one of the master server and one of the slave.

This tutorial will use the following IP addresses:

192.168.3.213 - Master Database
192.168.1.222 - Slave Database

Advantages of Mysql Replication:

  • Offload some queries from one server to other.
  • Use master for all writes and Use slave for all reads.
  • All changes can be replicating. This is the safest form of replication.
  • Data on the slave server is more likely to be up to date than on a weekly backup which is only performed periodically.

Setup

This article assumes that you have a user with sudo privileges and have MySQL installed. If you do not have MySQL, you can install it with this command:

$ apt-get update
$ apt-get install mysql-server mysql-client

Configure mysql-master server

Step 1: Master database configuration

After installing the mysql server on the system. Go to the MySQL configuration directory "/etc/mysql" and open up the configuration file "my.cnf".

In my.cnf file, we need to make some changes which helps to replicate the data to slave server.

vim /etc/my.cnf

#Add below lines under [mysqld] section,

#Change standard IP address with your system private or local address.

bind-address = 192.168.3.213

#Next uncomment the below line and make sure the server-id your using does not match with other server-ids. This id must be unique.


server-id = 1


#Move on to the log_bin line. 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. For this step, we simply need to uncomment the line that refers to log_bin.


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


#Now use the database that will be replicated on the slave server. You can include more than one database by repeating below lines with the database name.


binlog_do_db = testdb

After you make all of the changes, go ahead and save and exit out of the configuration file.

Restart the Mysql Server:

$ sudo /etc/init.d/mysql restart

Step 2: Create Slave User and Grant Privileges

In this step, we need to perform some commands in mysql shell for this open mysql in terminal using,

$ mysql -u root -p

Now we need to grant privileges to the slave. Run below command to name your slave and set up a password.

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

$ FLUSH PRIVILEGES;

Now use the database which you want to replicate to the slave server and check the master status.

$ USE testdb;
$ FLUSH TABLES WITH READ LOCK;
$ SHOW MASTER STATUS;

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

mysql> SHOW MASTER STATUS;


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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      557 | testdb       |                  |

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

1 row in set (0.00 sec)

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

Step 3: Take Dump of database

Now take dump of database using mysqldump command,

$ mysqldump -u root -p testdb > testdb.sql

Now you are all done with the configuration of the the master database.

Configure Mysql-slave database

Step 1: Create a database

Now come to the other server where we are configuring slave server. If mysql server is not installed, then run the commands which are provided at the start of this article.

Once the installation is done login to the slave server and create the new database that you will be replicating from the master.

$ mysql -u root -p
$ CREATE DATABASE testdb;
$ EXIT

Step 2: Import the database

Now you need to import the database that you previously exported from master Database.

$ mysql -u root -p testdb < testdb.sql

Step 3: Configuration of slave in my.cnf

Now we need to configure the slave server the same way we did for master server.

vim /etc/mysql/my.cnf

#Change the server-id and use different no. here we have used no. 2 as server-id.

server-id = 2

#Following that, make sure that you 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  = testdb

 

#Note: You need to create a relay-log file in mentioned path because it is not there by default.

After all the necessary changes save and close the file.

Restart Mysql Server:

$ sudo /etc/init.d/mysql restart

Step 4: Enable the replication

Now we need to enable the replication using the mysql shell. Open up the mysql shell and run the below mentioned commands.

$ mysql -u root -p

# Replace the values to match your information.

$ CHANGE MASTER TO MASTER_HOST='192.168.3.213',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=557;

#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 STATUSG

That is it. Master-Slave replication configuration is completing here. Now you can test by updating any single value in the master database and check this same on slave database. You can see the changes are updating on slave database.

I hope you like the article if you find any difficulties then please do comment your queries or problem via the comment section, till then stay tuned to techthings.org for more such valuable articles.

Latest Comments
  1. Oleg Babakov February 7, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *