Install and configure MySQL 5.7 with master-slave replication. The benefits of this include high availability, backups, disaster recovery and reporting.
Enable MySQL repository
yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
Install MySQL server
yum install -y mysql-community-server
Start MySQL server
systemctl start mysqld
Enable MySQL service
systemctl enable mysqld
Confirm MySQL service is enabled
systemctl is-enabled mysqld
Get temporary password
grep 'temporary password' /var/log/mysqld.log | tail -1
Initialize secure installation
mysql_secure_installation
- Install MySQL 5.7 server by following instructions above
- Create a
databaseor import your database mysqldump
Open MySQL config file
vim /etc/my.cnf
Add the following lines (replace database with the name of your database)
server-id = 1
binlog-do-db=database
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
log-bin = mysql-bin
Restart MySQL server
systemctl restart mysqld
Login to MySQL server
mysql -u root -p
Create slave user (replace PASSWORD with your password)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'PASSWORD';
Flush privileges
mysql> FLUSH PRIVILEGES;
Lock tables until we finish setting up the slave server
mysql> FLUSH TABLES WITH READ LOCK;
Show master status
mysql> SHOW MASTER STATUS;
You will need the master coordinate's File and Position for the slave server, so write them down
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 245 | database | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Exit MySQL server
mysql> exit
If the database you want to replicate is not empty, create a dump from the database
mysqldump -u root -p -B --events --routines --triggers database > /root/database.sql
Transfer the dumped file to the slave server
rsync -Waq -e ssh /root/database.sql 123.456.789.2:/root
- Install MySQL 5.7 server by following instructions above
Open MySQL config file
vim /etc/my.cnf
Add the following lines (replace database with the name of database to replicate)
server-id = 2
replicate-do-db=database
relay-log = mysql-relay-bin
log-bin = mysql-bin
Restart MySQL server
systemctl restart mysqld
Import master database dumped file if it was created
mysql -u root -p < /root/database.sql
Login to MySQL server
mysql -u root -p
Stop Slave
mysql> STOP SLAVE;
Configure Slave using data created earlier on the Master (replace PASSWORD with slave password)
mysql> CHANGE MASTER TO MASTER_HOST='123.456.789.1', MASTER_USER='slave', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
Start Slave
mysql> START SLAVE;
Check Slave status
mysql> SHOW SLAVE STATUS \G;
If Slave_IO_State is Waiting for master to send event, then replication was setup successfully.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 123.456.789.1
Master_User: slaveuser
Master_Port: 3306
Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: database
Exit MySQL server
mysql> exit
Login to MySQL server
mysql -u root -p
Unlock tables
mysql> UNLOCK TABLES;
Exit MySQL server
mysql> exit