MySQL replication process enables you to conveniently maintain multiple copies of a MySQL data. This facilitates a way to copy MySQL data from a master to a slave database. The important application of MySQL data is to spread the read access across multiple servers thereby improving the scalability. Apart from that, it is also used to analyze data on the slave to avoid master overloading and to tackle failover.

MySQL replication scheme is of 2 types

  • Asynchronous replication
  • Semi-synchronous replication

Asynchronous replication

 

Asynchronous replication, the most popular replication scheme is the default form of MySQL replication. In asynchronous replication, the master is responsible for writing events to its binary log and slave is responsible for requesting them when they are ready. We cannot guarantee that any event will ever reach any slave. Here a master does not wait for a slave, hence it’s a loosely connected master-slave relationship. The slaves are subjectively behind master in perusing and applying changes.

Semi-synchronous replication

 

In Semi-synchronous replication, the master does not confirm transactions to the client until at least one slave has copied the change to its relay log, and flushed it to disk. To enable semi-synchronous replication a plugin installation required and should be enabled on designated MySQL master and slave.

How to Setup MySQL replication in  CentOS

 

We will use two test CentOS 7 virtual servers for this tutorial with the following IP addresses:

MASTER: 143.234.892.1

SLAVE: 143.234.892.2

1)Log in to the master server as user root

ssh root@ip_address

2) Check if MySQL server is installed on the server, if not install MySQL by the following command.

yum install mysql-server mysql

3) Once step 2 is finished, make sure to start the MySQL server and enable it to start at boot time.

systemctl start mysql
systemctl enable mysql

4) Edit MySQL configuration file as mentioned below

vi /etc/my.cnf

server-id = 1

binlog-do-db=database_name

relay-log = mysql-relay-bin

relay-log-index = mysql-relay-bin.index

log-bin = mysql-bin

5) Save the configuration file and restart MySQL service to effect the changes.

systemctl restart mysql

6)Login to the MySQL as a root user and create a user for the slave server and configure it.

mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@’%’ IDENTIFIED BY ‘PASSWORD’
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
 mysql> SHOW MASTER STATUS;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.001         |      245 | database     |                  |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

mysql> exit

We will need the master’s coordinates that are going to be used for the slave server,

File: mysql-bin.001

Position: 245

Once it is finished reset the current password to a strong one. Now, if the database you want to replicate is not empty, create a dump from the database

database dump > mysqldump -u root -p –master-data database > /root/alldatabase.sql and transfer the dump file to the slave server

rsync -Waq -e ssh /root/alldatabase.sql 143.234.892.2:/root

Slave Server

7) To configure the slave server. Always make sure to log in as root user to slave server using SSH and install MySQL server as we did on the master server

Edit MySQL configuration file with the following lines.

vi /etc/my.cnf

server-id = 2

master-host=143.234.892.1

master-connect-retry=60

master-user=slaveuser

master-password=PASSWORD

replicate-do-db=database

relay-log = mysql-relay-bin

log-bin = mysql-bin

8) Save the file and restart the MySQL server for the changes to change effect.

systemctl restart mysql

Once the same is done we have to import the MySQL dump file from the master server

mysql -u root -p < /root/alldatabase.sql     

9) Login to MySQL as a root user stop the slave and configure it where to look for the ‘master log file’.

mysql -u root -p

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST=’143.234.892.1′, MASTER_USER=’slaveuser‘, MASTER_PASSWORD=’PASSWORD’, MASTER_LOG_FILE=’mysql-bin.001′, MASTER_LOG_POS=245;

mysql> slave start;

Use the MySQL username and password we created on the master server.

You can check the slave status by executing the following MySQL command.

mysql> show slave status;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 143.234.892.1

Master_User: slaveuser

Master_Port: 3306

Master_Log_File: mysql-bin.001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: database        

ServerAdminz is a server support company specialized in Outsourced 24/7 Web Hosting Support, Remote Infrastructure Management, NOC, Cloud and Enterprise Security Services. With over 10+ of years of experience in working with major Data Centers and ISPs with 130+ experienced technicians, we continue to manage more than 49,000 servers from 85+ countries and has bagged 5 international awards.

If you have any queries on how to set up MySQL replication in CentOS feel free to leave us a message and our representative will get back to you.[two_third last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”” background_image=”” background_repeat=”no-repeat” background_position=”left top” border_position=”all” border_size=”0px” border_color=”” border_style=”” padding=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”” animation_speed=”0.1″ class=”” id=””]

    [/two_third]