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.
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
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
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 '[email protected]'; 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;
Now take the DB dump from your master server using the below command.
mysqldump -u root -p –all-databases –master-data > /root/dump.db
Now unlock the tables.
Copy this dumb.db file on the slave server.
scp -r dump.db [email protected]:/root/
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.
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='[email protected]', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=417; START SLAVE;
Now check the status of a slave.
show slave status\G
Master and Slave replication now configured.
Testing whether it’s working or not.
I have created a database on master and that database should replica on the slave.
We can also check slave hosts on the Master database.
show slave hosts;
As you can see the database ‘abc’ is automatically replica here.