Thoughts on architecture optimization caused by master-slave replication problem

Existing problems

The master-slave replication architecture has many replication stagnation problems such as 1032 errors and 1062 errors. Among them, the 1032 errors are caused by the update or update of the slave database after the master database is successfully executed. When deleting, it was found that this record could not be found on the slave library. The 1062 error was caused by a primary key conflict that occurred when the slave library was executed after the main library insert was completed and the insert could not be successful. These problems can be fixed by skipping the error and the previous copy data verification. Solved, but the direct cause of these problems is the inconsistency of the master-slave database data. In addition to possible data inconsistencies in the logical replication itself, this inconsistency is also caused by illegal additions, deletions, and modifications on the standby database by the business side or developers.

In the master-slave replication architecture, the master-slave library implements VIP binding to specify the library as the master library, providing reading and writing, and the slave library plays the role of backup. When a problem occurs in the master library, the VIP switches to the slave library. The slave library provides reading and writing, otherwise the slave library is just a backup. Under normal circumstances, we do not allow developers to directly log in to the slave database through a fixed IP, but in actual work it is often difficult to avoid it. So how to prevent developers from operating in the slave database from a technical perspective? How to avoid this without affecting the normal operation and failover of the high-availability architecture?

2. Architecture configuration optimization

(1) Direct solution

The direct way to solve the above problems is to consider Optimize the architecture configuration, that is, configure the read-write state of the slave library to the read-only state.

The MySQL official website has the following description about read-only:

1.Whenthe read_only system variable is enabled, the server permits no client updatesexcept from users who have the SUPER privilege. 
只读情况下，super权限可读写。
2.Updates performed by slave threads, if theserver is a replication slave. In replication setups, 
it can be useful toenable read_only on slave servers to ensure that slaves accept updates only from themaster server and not from clients.
不影响主从复制线程的读写。

After turning on read-only, except for super privilege accounts and replication threads, etc., business-side developers and other personnel will not be affected even if they log in to the backup server. The database cannot also operate the standby database data.

MySQL [db1]> show global variables like'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON   |
+---------------+-------+
1 row in set (0.00 sec)
 
MySQL [test]> insert child values('1','12');
ERROR 1290 (HY000): The MySQL server is running withthe --read-only option so it cannot execute thisstatement

(2) How to perform perfect failover after configuring it as read-only?

Read-only from the slave library can avoid illegal operations, but the problem is that if a problem occurs in the main library, the VIP needs to switch to the slave library, but at this time, read-only from the slave library will cause the database External services are not available, so when switching, it is necessary to cancel the read-only function of the slave library and set the read-only function of the main library.

Taking the Keepalived+MySQL dual master (master-slave) architecture as an example, during normal operation, the VIP is on Master1, Master1 is in the read-write state, and Master2 is in the readonly state. Once a problem occurs with Master1, the VIP will automatically switch to Master2 , two steps need to be completed before switching: 1. Set Master1 to readonly; 2. Cancel Master2's readonly.

3. Automated implementation ideas

For a master-slave architecture, failover needs to be done manually, so the above two steps can also be done manually; but Keepalived+MySQL dual master ( In the master-slave architecture, automatic fault monitoring and automatic VIP switching have been implemented. The above two steps should also be embedded in scripts to achieve automation.

We mainly need to embed functions for opening and closing readonly on the database in the automatic monitoring and switching scripts. We mainly write the statements "set global read_only=ON" and "set globalread_only=OFF". At the same time, pay attention to Before setting the status, first determine the existing status. The shell calls the statement "show variables like 'read_only';" to get the read and write status. After confirming the read and write status, set the readonly parameter to the required status. Note that the trigger customization of these status settings is in Before a failure is detected and a switchover is performed.

The above ideas have now been automatically converted, and the personal test has been successful, indicating that the ideas are correct.

The above is the content of the architecture optimization thinking caused by the master-slave replication problem. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!