How to Setup Master-Slave Mysql Replication On Debian 9

Master-Slave Mysql
Master-Slave Mysql

Master-Slave Replication

Master-Slave 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 the 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 a means to scale out.

In this guide, You are going to perform steps to setup Master-Slave replication on Debian 9.

Here, we are going to cover up a simple example where master server sync or copy information to another single slave server.

Advantages of Master-Slave Mysql Replication

  • Offload some queries from one server to another.
  • 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.

Prerequisites

In this guide, We have 2 servers that are running with the Debian 9 Operating System and the following IP assigned to each server.

192.168.3.213 - Master Database
192.168.1.222 - Slave Database

Steps for Master-Slave Replication Setup

Debian 9 repositories contain the Mysql 5.7 version. Always install the latest version of the application which provides new features and security measures with the latest version.

We are assuming that you have Sudo privileges to install MySQL and perform other administrative tasks to complete the replication setup.

Step 1: Install Mysql Server

Run the following command on both the Master-Slave Machine to update the repository and install Mysql,

$ wget http://repo.mysql.com/mysql-apt-config_0.8.9-1_all.deb
$ dpkg -i mysql-apt-config_0.8.9-1_all.deb
$ apt-get install dirmngr
$ apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5
$ apt-get update
$ apt-get install mysql-server

Step 2: Master Database Configuration

After successful installation Mysql Server, Open the MySQL configuration file my.cnf which is present in “/etc/mysql/my.cnf” directory.

Change the “bind-address” from loopback address to server internal IP address,

vim /etc/mysql/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

Step 3: Restart Mysql Service

Restart the MySQL service for the changes to take effect on master server,

$ service mysql restart

Step 4: Create Slave User & Grant Privileges

Now we need to perform some commands in MySQL shell. For that login to the MySQL using the following command,

$ mysql -u root -p

To create replica user and grant privileges to replication slave proceed and execute below queries,

$ CREATE USER 'slave_user'@'192.168.1.222' IDENTIFIED BY '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 below table,

mysql> SHOW MASTER STATUS;


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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

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

1 row in set (0.00 sec)

The position (557) from which the slave database will start replicating.

The Position no is an important part, record this number because it will be helpful later in Slave configuration.

Step 5: Take Dump of Database

Now take the latest database dump using the following command,

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

Perfect! Now you are done with the master server configuration.

Step 6: Slave Database Configuration

Now we need to perform the same steps that we performed for the master server.

Create New database

Create a new database that you will be replicating from the master.

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

Import the Database

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

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

Change bind-address

Change the “bind-address” in “/etc/mysql/my.cnf” configuration file,

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 the mentioned path because it is not available by default.

Restart the Mysql Server

$ sudo /etc/init.d/mysql restart

Step 7: 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 STATUS\G

The Master-Slave Mysql Replication setup is completed now.  Awesome! you can test by updating any single value in the master database and check this same on the slave database. You can see the changes are updating on the slave database.

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

Prashant

Welcome to Linuxgrow. I'm Prashant, a tech-blogger from Mumbai, India. I started Linuxgrow as a passion and to share my knowledge about technologies. Here at Linuxgrow, I write about Linux technologies, Aws Cloud, Wordpress blogging and scripting knowledge. You can read more about me at About us page. Thank You :)

You may also like...

Leave a Reply

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

Facebook