安装MySQL主备服务

Install MySQL on master and slave

yum install wget -y
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
rpm -ivh mysql57-community-release-el7-9.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
rpm -qa gpg-pubkey*
yum install mysql-server -y
systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation

Configure MySQL Master Node

First, we will configure the master node with the required configuration.

The key configurations are

1. Binding static IP address to MySQL server.
2. Unique ID config for master. Each server in the cluster should be identified uniquely.
3. Enable Binary log – It contains information about the data changes made to MySQL.
4. Create a replication user that will be used by the slaves to replicate the data from the master.

Note: In this installation, MySQL data dir will default to /var/lib/mysql in the root file system. For production use cases, this path should be an external disk attached to the VM.

Step 1: Open /etc/my.cnf file.

sudo vi /etc/my.cnf

Step 2: Add the following three parameters under the [mysqld] section and save it. Replace 10.128.0.11 your server IP.

bind-address           = 10.128.0.11
server-id              = 1
log_bin                = mysql-bin

Step 3: Restart the MySQL server for the configurations changes to take place.

sudo systemctl restart mysqld

Step 4: Check the MySQL status to make sure all the configurations are applied as expected without any errors.

sudo systemctl status mysqld

Step 5: Login to MySQL server as the root user.

mysql -uroot -p

Step 6: Create a user named replicauser with a strong password. This user will be used by the slaves to replicate the data from the master. Replace 10.128.0.11 with your master IP

CREATE USER 'replicauser'@'10.128.0.11' IDENTIFIED BY 'my-secret-password';

Step 7: Grant privileges to the slave user for slave replication.

GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'%' IDENTIFIED BY 'my-secret-password';

Step 8: From the MySQL prompt, Check the master status. Note down the file [mysql-bin.000001] and Position[706] parameters from the output. It is required for the slave replication configuration.

SHOW MASTER STATUS\G

The output would look like the following.

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 706
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Configure MySQL Replication Slave Node

Execute the following steps in all the slaves.

Step 1: Add the same configurations as the master to the /etc/my.cnf file with the Slave Ip address and unique server ID.

bind-address           = 10.128.0.12
server-id              = 2
log_bin                = mysql-bin

Note: If you more than one slave, make sure you replace the respective slave IP and add a unique server-id per slave.

Step 2: Restart the MySQL service.

sudo systemctl restart mysqld

Step 3: Login to MySQL with root credentials.

mysql -uroot -p

Step 4: Stop the slave threads using the following command.

STOP SLAVE;

Step 5: Execute the following statement from MySQL prompt replacing the master IP [10.128.0.15], replicauser password [replicauser-secret-password].

Replace MASTER_LOG_FILE & MASTER_LOG_POS with the values, you got from step 8 in master configuration.

CHANGE MASTER TO MASTER_HOST='10.128.0.15',MASTER_USER='replicauser', MASTER_PASSWORD='replicauser-secret-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  706;

Step 6: Now, start the slave threads.

START SLAVE;

Step 7: Check the MySQL replication slave status.

SHOW SLAVE STATUS\G

Slave_SQL_Running_State parameter will show the current slave status.

Test MySQL Master-Slave Replication

In this section, we will test master-slave replication.

On Master Server

Login to master mySQL CLI.

mysql -uroot -p

Create a database named testdb

CREATE DATABASE testdb;

On Slave Server

Login to slave mySQL CLI.

mysql -uroot -p

List the Databases. You should see the testdb database created from the master server.

show databases;

Example, output.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+