Detailed explanation of MySQL cluster: one master and multiple slaves architecture implementation

Experimental environment:

1. Three CentOS 7 servers

2. mysql5.7.26 (all three machines are installed via yum)

Server List

If you haven't installed MySQL yet, please see the installation tutorial: MySQL installation

1. Overview:

Architecture diagram:

This kind of architecture is commonly used by start-ups and is also convenient for subsequent expansion.

Features:

1. It can relieve the pressure of reading.

2. Low cost, fast and convenient deployment

3. Read-write separation

4. You can also reduce the pressure of reading the database by adding slaves in time

5. Single point of failure of the main database

6. Data consistency issues (caused by synchronization delays)

7. Once the host is down, write operations cannot be performed

2. Building a cluster

1. Modify the configuration file

Use the following commands to modify the configuration files on the three servers respectively.

$ vim /etc/my.cnf

Add the following content to the /etc/my.cnf MySQL configuration files of the three servers respectively:

2. Create a replication user in the master database

Enter the following command under the mysql command to create a user for the slave library (slave) to copy the master library (master)

mysql> grant replication slave on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3. Associate the slave database with the master database

Use the following command to view the status of the master library

mysql> show master status;

Output the following information, of course yours may be different from mine

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 5141037 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

As you can see from the above results, you only need to look at File and Position here. The other two are whitelist and blacklist, which means which databases are synchronized and which are not synchronized. You can set them according to your needs. After recording the first two fields above ().

Run the following commands on the two slaves:

mysql> change master to master_host='47.100.222.111', master_port=3306, master_user='test', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=5141037;
 
mysql> flush privileges;
 
mysql> slave start;

After the execution is complete, continue to execute the following statements on the slave library:

mysql> show slave status\G;

The following information is output:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 47.100.225.121
Master_User: helper
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000002
Read_Master_Log_Pos: 5141037
Relay_Log_File: slave1-relay-bin.000003
Relay_Log_Pos: 5140628
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
----------The following is omitted

If Slave_IO_Running: and Slave_SQL_Running: are both YES, the configuration is successful.

At this point, the construction of one master and two slaves is completed, and the installation of one master and multiple slaves is similar. You can create a database or a table on the master database, and the slave database will replicate it.

The above is a detailed explanation and integration of the MySQL cluster one-master-multiple-slave architecture introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!