MySQL Replication Strategies with SQL

Common SQL operation strategies in MySQL replication include: 1. Skip errors: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; Suitable for temporary processing of non-fatal errors; 2. Do not copy SQL from the library: Use SET SQL_LOG_BIN=0 to turn off binary logging, and then turn it on after performing the operation, requiring SUPER permission; 3. Manually repair data differences: After closing the copy thread, missing data is manually inserted from the library. It is recommended to combine the pt-table-checksum tool to verify consistency; 4. Ignore specific SQL replication: use replicate-wild-ignore-table parameters or CHANGE REPLICATION FILTER dynamically sets filtering replication tables. The above operations should be carefully evaluated to avoid causing data inconsistencies or other problems.

MySQL replication (Replication) is a common data synchronization mechanism, which is often used in master-slave architecture to achieve data redundancy, read-write separation and high availability. If you are using MySQL Replication, you may encounter situations where you need to execute certain SQL statements during the replication process, such as tuning configuration, repairing data, or performing batch operations. At this time, it is particularly important to master some commonly used SQL strategies.

The following are some common and practical strategies for using MySQL Replication in actual operations.

How to skip copy errors

During the replication process, some non-fatal errors are often encountered, such as primary key conflicts caused by inconsistency in master-slave data or statement execution failure. If you do not want these errors to interrupt replication, you can use SQL to skip the error.

Common practices are:

• Stop copying threads from the library:

  

   STOP SLAVE;

• Skip an error event:

   SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

• Restart the replication:

   START SLAVE;

⚠️ Note: This method is suitable for temporary treatment and is not recommended for long-term use. Skip errors may lead to further inconsistencies in the data. It is recommended to operate on the premise of confirming that the risks are controllable.

How to execute SQL that does not copy from a library

Sometimes you want to execute some SQL statements on the slave library, but you don't want these statements to be copied to other nodes. For example, you might want to add a temporary index to the slave library, but you don't want to affect the main library structure.

You can use SET SQL_LOG_BIN = 0; to temporarily close the binary logging of the current session:

 SET SQL_LOG_BIN = 0;
CREATE INDEX idx_temp ON your_table(temp_column);
SET SQL_LOG_BIN = 1;

In this way, this CREATE INDEX statement will not be recorded in the binary log and will not be copied to other slave libraries.

⚠️ Note: SUPER permission is required to perform this operation. In addition, this operation only takes effect in the current session and will not affect other connections.

How to manually fix master-slave data differences

If you find that the data is inconsistent between the master and slave, sometimes you need to manually execute SQL statements to synchronize the data. For example, the main library has a data record ID 100, while the slave library does not.

You can manually insert this record from the library:

 INSERT INTO your_table (id, col1, col2) VALUES (100, 'value1', 'value2');

However, there are risks in direct operation from the inventory. It is recommended:

• Make sure the insert statement also exists in the main library, otherwise an error may occur next time you copy it
• Close the copy thread before insertion, and then turn on after insertion
• Automatically detect and fix data differences using pt-table-checksum and pt-table-sync tools

How to ignore certain SQL statements in replication

If you want certain SQL statements not to participate in replication, you can set replication filtering rules in the main library, such as through replicate-wild-ignore-table parameter configuration.

For example, add:

 replicate-wild-ignore-table=your_db.temp_%
replicate-wild-ignore-table=mysql.%

In this way, all tables matching your_db.temp_* and tables under the mysql database will not be copied.

If you don't want to modify the configuration file, you can also use SQL to dynamically set it:

 CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (your_db.temp_table);

This method is suitable for temporarily ignoring the copying of certain tables.

Basically that's it. There are many usage scenarios in MySQL Replication, and the key is to understand the scope of action and potential impact of each statement. Although operations such as skipping errors, closing copy and executing SQL, and manually repairing data, are not complicated, but they are easily more serious problems due to misoperation, so it is best to confirm clearly before execution.

The above is the detailed content of MySQL Replication Strategies with SQL. For more information, please follow other related articles on the PHP Chinese website!