Blog Details

How to setup master slave live replication in mysql in centos 7

MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analysing data on the slave in order not to overload the master

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

10.0.0.207 - Master Database
10.0.0.205 - Slave Database

This article assumes that you have root privilege and installed mysql in both servers.

Configure the master database.

Open up the mysql configuration and add the following lines to the file or uncomment those lines if already exist.

#    vi /etc/my.cnf

bind-address            = 127.0.0.1
bind-address            = 10.0.0.207   #this is the ip adress of master servre.
server-id                = 1      #this number should be unique.         log_bin                 = /var/log/mysql/mysql-bin.log 

Note:- Create the above file is not exists and change owner and group to mysql.

binlog_do_db            = newdatabase   

Note: Here we use newdatabase for the replication. You can add any number of databases by repeating the line multiple times with mentioning database name.

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

Then restart Mysql

#   systemctl restart mysql

Open Mysql shell.

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;

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.

Switch to the datebase:

USE newdatabase;

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

FLUSH TABLES WITH READ LOCK;

Then type:

SHOW MASTER STATUS;

You will see a table that should look like this:

+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 |      342 | newdatabase,has |                  |
+------------------+----------+-----------------+------------------+

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 newdatabase > newdatabase.sql

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

UNLOCK TABLES;

QUIT;

Now you have completed configuring master database.

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 newdatabase;
EXIT;

Import the database that you previously exported from the master database.
Note: Here we export old data's to the database, don’t worry all the new data after the replication will automatically saved to the slave database.

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

Now we need to configure the slave configuration

# vi /etc/my.cnf
 
	Add following lines to the file.

	Server-id               = 2
	relay-log               = /var/log/mysql/mysql-relay-bin.log
	log_bin                 = /var/log/mysql/mysql-bin.log
	binlog_do_db            = newdatabase

Save the file.

Restart MySQL once again:

# systemctl restart mysql

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='10.0.0.207’,MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  342;

Note : on the above command change the following as per your data.

		MASTER_HOST
		MASTER_USER
		MASTER_PASSWORD
		MASTER_LOG_FILE
		MASTER_LOG_POS

Then , Activate the slave server:

START SLAVE;
SHOW SLAVE STATUS\G

You be able to see the details of the slave replication by typing in above command.

If there is an issue in connecting,

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

Try this command.

All done. Please test it is working fine by adding a sample table in master database.

Thank you for reading !!
Need help in WHMCS CUSTOMIZATION or WEB DEVELOPMENT ?
We are here for you:
https://webrins.com

https://webrins.com/outsourced-customer-support/