Configure Master and Slave MySQL Replication On Linux

Configure Master and Slave MySQL Replication On Linux

You need two machines for this setup one for Master and another for a slave. I will install the MySQL 5.7 version. you can configure master-slave replication and also show the master configuration in my.cnf file. we can also check MySQL replication status.

This also provides you database high availability for your application if one goes down, data can be fetched from another slave instances.

Master IP: 192.168.223.196

Slave IP: 192.168.223.197

You can find another version from the official site of MySql.

https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html

Check Out: How To Block Ads In Google Chrome Using Group Policy

you have to install on MySQL both servers and the version should be the same. Create a repo file in vim /etc/yum.repos.d/mysql.repo

Master Configuration

Install MySQL server using yum install mysql mysql-server -y

I will put a few basic and required configuration under my.cnf file. The server-id should not be the same.

Put these code in this file vim /etc/my.cnf and under [mysqld] section.

server-id = 1
#binlog-do-db=testdb
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Check Out: Command To Restore And Backup Of MySQL Server On CentOS 7

After putting these code, restart the service 

systemctl restart mysqld

Now grant replication on slave users for any IP address means ‘%’. we need to give the slave IP.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_ip' IDENTIFIED BY 'F!fXkv!og26@';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Please note down the file name mysql-bin.000002 and position 417.

Check that the user must be created using the below command. 

select user,host from mysql.user;

 mysql show master configuration

Check Out: How To Setup Multi-Factor authentication In Amazon Web Service

Now take the DB dump from your master server using the below command.

mysqldump -u root -p –all-databases –master-data > /root/dump.db

 check mysql replication status

Now unlock the tables.

UNLOCK TABLES;

how to check mysql replication databases are in sync

Copy this dumb.db file on the slave server.

scp -r dump.db root@192.168.223.197:/root/

Check Out: How To Install PHPMyAdmin For Accessing Database On CentOS 7

Slave configuration

you need to install MySQL server and make these changes in vim /etc/my.cnf

If you’re not able to start the service with this configuration, you can remove all master variables because it’s not recommended to put a password.

Check Out: Check Logs of Container Running In Docker On CentOS 7

Now it’s time to configure the replication on the slave and run the below commands. you don’t forget to change IP and password.

slave stop;
CHANGE MASTER TO MASTER_HOST='192.168.223.196', MASTER_USER='slave_user', MASTER_PASSWORD='F!fXkv!og26@', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=417;
START SLAVE;

mysql master slave configuration in linux

Now check the status of a slave.

show slave status\G 

mysql master slave replication

Master and Slave replication now configured.

Testing whether it’s working or not.

Check Out: MySQL Database Backup In GZ Format On Linux 7

Master DB

I have created a database on master and that database should replica on the slave.

sql server master slave architecture

We can also check slave hosts on the Master database.

show slave hosts;

Slave DB

As you can see the database ‘abc’ is automatically replica here.

how to check mysql replication databases are in sync

You’re done check mysql replication status mysql show master configuration configure master-slave replication mysql master replication

Share on:

I'm the founder of Curious Viral. I hope this blog will provide you complete information about Linux Technology & I would like to share my technical knowledge with you which I have learned during this period.

Other Posts You May Like...

Leave a comment