Install and configure MySQL with master-slave replication. The benefits of this include high availability, backups, disaster recovery and reporting.
For tutorial purposes, we will be using one master node and one slave node.
Master Node: 10.0.0.10
Slave Node: 10.0.0.11
Install the mysql repo.
5.7
yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
8.0
yum localinstall -y https://dev.mysql.com/get/mysql80-community-release-el7-3.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 default generated temporary password.
grep 'temporary password' /var/log/mysqld.log | tail -1
Login with your temporary password from the step above.
mysql -u root -p temppassword
Once logged into mysql, change the root password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your new password';
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Exit out of mysql.
mysql> \q
Initialize secure installation.
mysql_secure_installation
Open MySQL config file.
vim /etc/my.cnf
Add the following parameters under the [mysqld] section. Replace 10.0.0.10 with your server IP.
bind-address = 10.0.0.10
server-id = 1
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> CREATE USER 'slave'@'10.0.0.11' IDENTIFIED BY 'PASSWORD';
Grant replication to slave user.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.11';
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)
Open MySQL config file.
vim /etc/my.cnf
Add the following parameters under the [mysqld] section. Replace 10.0.0.11 with your server IP.
bind-address = 10.0.0.11
server-id = 2
log-bin = mysql-bin
Restart MySQL server.
systemctl restart mysqld
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
Login to MySQL server.
mysql -u root -p
Create a database called testdb.
mysql> CREATE DATABASE testdb;
Login to MySQL server.
mysql -u root -p
List the databases. You should see testdb.
mysql> SHOW DATABASES;