在MySQL中設置異步主要復制複製

要設置MySQL的異步主從復制，請按以下步驟操作：1. 準備主服務器，啟用二進制日誌並設置唯一server-id，創建複製用戶並記錄當前日誌位置；2. 使用mysqldump備份主庫數據並導入到從服務器；3. 配置從服務器的server-id和relay-log，使用CHANGE MASTER命令連接主庫並啟動複製線程；4. 檢查常見問題，如網絡、權限、數據一致性及自增沖突，並監控複製延遲。按照上述步驟操作可確保配置正確完成。

Setting up asynchronous primary-replica replication in MySQL is a common way to offload read traffic, provide redundancy, and help with backups. It's not overly complicated, but there are several important steps you need to follow carefully.

1. Prepare the Primary Server

Before setting up replication, make sure your primary server is configured correctly. You'll need to enable binary logging and assign a unique server ID.

• Edit your MySQL configuration file (usually my.cnf or my.ini ) and add these lines under the [mysqld] section:

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

• Restart MySQL to apply the changes.
• Create a dedicated replication user on the primary:

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

This user will be used by the replica to connect and read binary logs from the primary.

Now check the current binary log position on the primary:

 SHOW MASTER STATUS;

Take note of the File and Position values — you'll need them later when configuring the replica.

2. Take a Backup of the Primary Data

To get the replica in sync, you need a consistent snapshot of the primary data. The easiest way is to use mysqldump .

Run this command on the primary:

 mysqldump --all-databases --master-data=2 --single-transaction -u root -p > backup.sql

• --master-data=2 adds the binary log position as a comment in the dump file.
• --single-transaction ensures a consistent view of the database without locking tables for long.

Transfer the backup file to the replica server using scp , rsync , or any method you prefer.

Then import it into the replica:

 mysql -u root -p < backup.sql

3. Configure the Replica Server

On the replica, edit its MySQL config and set a different server ID (it must be unique across the replication topology):

 server-id=2

Also, if you want the replica to keep a record of the replicated events, you can enable relay logs:

 relay-log=mysql-relay-bin

Restart MySQL after making changes.

Now, configure the replica to connect to the primary using the credentials and log position you recorded earlier:

 CHANGE MASTER TO
  MASTER_HOST='primary_server_ip',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='recorded_log_file_name',
  MASTER_LOG_POS=recorded_position;

Once that's done, start the replication threads:

 START SLAVE;

You can check the status with:

 SHOW SLAVE STATUS\G

Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes . If both are yes and there are no errors, replication is working.

4. Common Issues and Tips

Replication setups can fail for various reasons. Here are some common issues and how to avoid them:

• Network connectivity : Make sure the replica can reach the primary on port 3306.
• Firewall rules : Double-check that the firewall allows traffic between the servers.
• User permissions : Confirm that the replication user has the correct privileges.
• Data inconsistency : If the replica's data doesn't match the primary, replication might break. Use tools like pt-table-checksum to verify consistency.
• Auto-increment conflicts : If you ever switch to multi-source or circular replication, consider adjusting auto_increment_offset and auto_increment_increment .

Also, don't forget to monitor replication lag. You can see it via SHOW SLAVE STATUS — look at the Seconds_Behind_Master field.

That's basically how you set up asynchronous replication in MySQL. It's straightforward once you've done it a few times, but always double-check each step — especially the server IDs and log positions.

以上是在MySQL中設置異步主要復制複製的詳細內容。更多資訊請關注PHP中文網其他相關文章！