1. Home
  2. Help Articles
  3. How to set up MySQL Master-Slave setup using CentOS 7 nodes?

How to set up MySQL Master-Slave setup using CentOS 7 nodes?

Please follow the below steps to setup MySQL Master-Slave setup.

These steps have been tested and are working on CentOS 7.x nodes.

To follow, you will at least need two compute nodes. You can launch compute nodes on-demand on E2E Public Cloud. Click here to know more.

Lets assume the below servers.

Master server IP: 192.168.1.1
Slave Server IP: 192.168.1.2

Step 1: Prepare Master Server

#yum -y update

Step 2: Add Repo

rpm -ivh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm 

Step 3: Install mysql-server 5.6 (stable Version)

yum -y install mysql-server

Step 4: Start/stop/restart MySQL Server

#systemctl start mysqld
#systemctl stop mysqld
#systemctl restart mysqld
#systemctl status mysqld

Step 5: Reset MySQL root password

 
#mysql_secure_installation
Enter current password for root (enter for none):
Enter a secure password
Set root password? [Y/n] y
New password:
Re-enter new password:
To Remove anonymous users
Remove anonymous users? [Y/n] y
To disable remote root login
Disallow root login remotely? [Y/n] y
To reload privileges
Reload privilege tables now? [Y/n] y

Step 6: Configure a MySQL in Master Server

Add the following entries in the [mysqld] section.

# vim /etc/my.cnf
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
Restart MySQL service
#systemctl restart mysqld

Step 7: Create and grant replication access

# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.1.2' IDENTIFIED BY replication-password;
mysql> FLUSH PRIVILEGES;
mysql> \q

Step 8: Take all database dump in a screen so that the dump process will be running in the session even if the terminal session closed.

#screen -S 
#mysqldump -u root -p --all-databases --master-data > /root/all-databases.sql

Sync the dump to the slave server.
#scp /root/all-databases.sql root@:/root/
Or
#rsync -arvP /root/all-databases.sql root@:/root/

Step 9: Prepare slave server

Follow step 1 to 5 to build the slave server.

Add the following entries in the [mysqld] section.

# vim /etc/my.cnf
server-id = 2

Restart MySQL service
#systemctl restart mysqld

Note: The server-id should be unique.

Step 10: Restore dump

#mysql -u root -p  /root/all-databases.sql

Step 11: Get to know the MASTER_LOG_FILE and MASTER_LOG_POS

#head -n 30 /root/all-databases.sql | grep MASTER_LOG_FILE
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

Note: Follow step 1 to 5 to create a slave server.

Step 12: Configure slave server

#mysql -u root -p
mysql> SLAVE STOP;

mysql> CHANGE MASTER TO MASTER_HOST='[192.168.1.1]',
MASTER_USER='['slave_user']',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G
The above command will show the complete slave status and verify the Slave_IO_Running and Slave_SQL_Running should be Yes.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Updated on July 2, 2019

Was this article helpful?

Related Articles

Add A Comment