Master to Slave and Master to Master replication on MySQL 5.7
OS version: Centos 7
MySQL version: 5.7.23
Install MySQL:
wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
rpm -Uvh mysql57-community-release-el7–10.noarch.rpm
yum install mysql-server
Start service:
systemctl start mysqld
systemctl enable mysqld
mysql_secure_installation
Configure Master to Slave replication:
Master MySQL ip: 192.168.88.130
Slave MySQL ip: 192.168.88.131
First configure master database.
Create log folder:
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
Add config files undo [mysqld] in /etc/my.cnf.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
Restart service:
systemctl restart mysqld
Login to mysql and add slave_user for slave server :
1)mysql -u root -p
2)CREATE USER 'slave_user'@'192.168.88.131' IDENTIFIED BY 'password';
3)GRANT REPLICATION SLAVE ON * . * TO 'slave_user'@'192.168.88.131';
FLUSH PRIVILEGES;
Second configure slave database.
Install MySQL.
Create log folder.
Add config files undo [mysqld] in /etc/my.cnf.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
Restart service:
systemctl restart mysqld
Login to mysql and add master server credentials:
1)mysql -u root -p
2)STOP SLAVE;
3)CHANGE MASTER TO MASTER_HOST='192.168.88.130',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=155;
3)START SLAVE;
Finally show slave status:
SHOW SLAVE STATUS\G;
Configure Master to Master replication:
Master-1 MySQL ip: 192.168.88.132
Master-2 MySQL ip: 192.168.88.133
Configure first master database.
Create log folder:
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
Add config files undo [mysqld] in /etc/my.cnf.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
bind-address=0.0.0.0
Restart service:
systemctl restart mysqld
Login to mysql and add slave_user for second master:
1)mysql -u root -p
2)CREATE USER 'slave_user'@'192.168.88.133' IDENTIFIED BY 'password';
3)GRANT REPLICATION SLAVE ON * . * TO 'slave_user'@'192.168.88.133';
4)FLUSH PRIVILEGES;
Configure second master database.
Install MySQL.
Create log folder.
Add config files undo [mysqld] in /etc/my.cnf.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
bind-address=0.0.0.0
Restart service:
systemctl restart mysqld
Login to mysql and add first master server credentials:
1)mysql -u root -p
2)STOP SLAVE;
3)CHANGE MASTER TO MASTER_HOST='192.168.88.132',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=155;
4)START SLAVE;
Finally show second master status:
SHOW SLAVE STATUS\G;
Create user for first master on second master server.
1)CREATE USER 'slave_user'@'192.168.88.132' IDENTIFIED BY 'password';
2)GRANT REPLICATION SLAVE ON * . * TO 'slave_user'@'192.168.88.132';
3)FLUSH PRIVILEGES;
Login to first master and add second master credentials:
1)mysql -u root -p
2)STOP SLAVE;
3)CHANGE MASTER TO MASTER_HOST='192.168.88.133',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=155;
4)START SLAVE;
Finally show first master status:
SHOW SLAVE STATUS\G;