The configuration steps of MySQL master-slave replication include: 1. Prepare the master-slave server environment, ensure consistent versions and network communication, configure different server-ids, and enable binary logs; 2. Create a dedicated replication account in the master library and authorize it; 3. Use mysqldump to initialize the slave library data and import it; 4. Execute the CHANGE MASTER command to start the replication thread and check the status; 5. Optionally set up automatic failover, achieve high availability with external tools, or manually switch the master library and adjust the replication relationship to ensure data consistency.

MySQL master-slave replication is a basic means to implement a highly available architecture. If configured properly, you can quickly switch to the slave library when the master library fails to ensure service continuity. The key is to ensure the stability of the replication link, ensure data consistency, and cooperate with appropriate monitoring and switching mechanisms.

1. Prepare the master-slave server environment

Before you start, you need at least two servers: one as the master, and the other or multiple as the slave. It is recommended that the MySQL versions of these two servers are consistent and the operating systems are as similar as possible to reduce compatibility issues.

• Ensure network interoperability, the master library allows slave library to access its 3306 port
• server-id of the master and slave server is different, which is one of the basic requirements for replication.
• Turn on the binary log function of the main library because replication depends on it to record data changes

For example, add in the main library configuration file (such as /etc/my.cnf ):

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

The slave library sets a different server-id , such as 2, and can also enable read-only mode to prevent misoperation of writing:

 server-id=2
read_only=1

After modification, restart the MySQL service to enable the configuration.

2. Create a dedicated copy account

For security reasons, do not use root or other users with excessive permissions to copy. An account should be created specifically for replication and granted REPLICATION SLAVE permissions.

Execute the following command on the main library:

 CREATE USER 'repl'@'from library IP' IDENTIFIED BY 'Secure Password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'From Library IP';
FLUSH PRIVILEGES;

Be careful to replace從庫IP and password to ensure that the user can only connect from the specified IP.

3. Initialize the slave database data

To keep the master-slave data consistent, the data from the master library must be fully imported into the slave library. You can use mysqldump to export the main library data:

 mysqldump -u root -p --all-databases --master-data > dump.sql

Then transfer this dump file to the slave library and import:

 mysql -u root -p < dump.sql

After the import is completed, the data status of the slave library is consistent with the export of the master library.

4. Start the replication process

Execute the following SQL command on the slave library, connect to the main library and start the copy thread:

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

START SLAVE;

The values ??of MASTER_LOG_FILE and MASTER_LOG_POS come from the CHANGE MASTER statement in the dump file generated when you execute mysqldump .

You can use the following command to check whether the copy status is normal:

 SHOW SLAVE STATUS\G

Focus on whether Slave_IO_Running and Slave_SQL_Running are both Yes.

5. Set up automatic failover (optional)

MySQL's own replication mechanism does not support automatic failover itself, and needs to be implemented with external tools or middleware, such as MHA (Master High Availability), Orchestrator, or using high availability solutions provided by cloud vendors.

If you just switch manually, you can after the main library goes down:

• Check the copy status of the slave library and confirm that you have caught up with the main library
• Point the application to the new master library (original slave library)
• You can restore the old master library as the slave library of the new master library

This step needs to be handled with caution, otherwise it is easy to cause data inconsistency.

Basically these steps. Although the configuration process is not complicated, every detail is critical, especially in terms of network permissions, log location and security of replication accounts. A slight negligence may lead to replication failure or security risks.

The above is the detailed content of Configuring MySQL replication for high availability. For more information, please follow other related articles on the PHP Chinese website!