MySQL master-slave replication realizes data synchronization through Binary Log. The main library records write operations to Binary Log. The slave library obtains logs through I/O threads and writes them to Relay Log. Then the SQL thread executes the statements in the log to keep the data consistent. 1. The main library needs to enable Binary Log and set a unique server-id; 2. Create a dedicated replication account and authorize it; 3. Set different server-ids for the slave library and enable relay-log; 4. Use mysqldump to obtain a snapshot of the main library and import the slave library; 5. Configure the CHANGE MASTER parameters and start the replication thread; 6. Check the Slave_IO_Running and Slave_SQL_Running status to ensure that the replication is running normally.

MySQL master-slave replication is a common database architecture scheme used to implement asynchronous or semi-synchronous replication of data. It can record data changes from one MySQL server and replay these changes on another server, thereby achieving data redundancy, read and write separation, high availability and other purposes.

Let’s take a look at its basic mechanism and configuration methods.

1. How MySQL Replication works

The core of MySQL replication is the binary log (Binary Log) , and the main library will record all write operations (such as INSERT, UPDATE, DELETE) into this log. Then connect to the main library from the library, request these log contents, and save them as your own Relay Log, and finally execute these statements from the relay log to maintain consistency with the main library.

It mainly involves three threads:

• Dump thread on the main library : Responsible for sending Binary Log to the slave library.
• I/O thread on the slave library : Responsible for receiving logs from the master library and writing to Relay Log.
• SQL thread from the library : Responsible for reading Relay Log and executing SQL statements inside.

This method can realize asynchronous replication , or semi-synchronous replication can be implemented through configuration to improve data consistency.

2. Basic steps to configure the main library

To enable replication, you must first make sure that the main library has Binary Log enabled and set a unique server-id.

The configuration points are as follows:

• Edit my.cnf or my.ini file:

   server-id=1
  log-bin=mysql-bin

• Create a dedicated account for copying:

   CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  FLUSH PRIVILEGES;

• Check the current location of Binary Log:

   SHOW MASTER STATUS;

After this step is completed, the main library is ready, and the next step is to configure the slave library.

3. Configure key details of the slave library

The slave library also needs to set a unique server-id, but it cannot be the same as the main library. It is also recommended to enable relay_log, although it is not necessary, but it helps in performance optimization.

The specific steps are as follows:

• Modify the configuration file from the library:

   server-id=2
  relay-log=relay-log

• Before starting replication, make sure that the slave library has an initial data snapshot of the main library, which can be implemented through mysqldump:

   mysqldump -h main library IP -u Username -p --master-data=2 --single-transaction Database name> dump.sql

  After restoring to the slave library, configure it according to the CHANGE MASTER command in the dump file.

• Use SQL to set up the main library information:

   CHANGE MASTER TO
    MASTER_HOST='Main Library IP',
    MASTER_USER='repl',
    MASTER_PASSWORD='Password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS= 4;

• Finally start the copy thread:

   START SLAVE;

Remember to check the status:

 SHOW SLAVE STATUS\G

Focus on whether Slave_IO_Running and Slave_SQL_Running are both Yes.

4. Frequently Asked Questions and Precautions

Some common problems may be encountered during the replication process, such as network interruption, SQL execution errors, primary key conflicts, etc. Here are some practical suggestions:

• Avoid manually modifying data from the library , otherwise it may cause replication failure or data inconsistency.

• Regularly check for replication delays, which can be viewed through the Seconds_Behind_Master field.

• If a SQL error occurs, the error can be skipped (for non-critical errors):

   SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  START SLAVE;

• For production environments, it is recommended to use GTID (global transaction identifier) ??to manage replication to simplify the failure recovery process.

• If a firewall is enabled, make sure that the 3306 port of the master library is open to the slave library.

Basically that's it. The MySQL replication mechanism is not complicated, but you need to pay attention to details when configuring, especially server-id, Binary Log file name and location, which are prone to errors. As long as you follow the steps step by step, you can usually run smoothly.

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