基于Percona的MySQL主主复制搭建咨询：双服务器高可用备份需求

Hey there! As a web dev with limited MySQL hands-on experience, I’ll walk you through setting up a Percona MySQL master-master replication setup step by step—plus add-ons for high availability with load balancing and a solid backup plan that fits your needs.

Master-master (aka dual-master) replication lets both servers act as active writable nodes, with changes synced bidirectionally. Here’s how to build it:

1. Pre-Requisites #

First, get your environment ready:

• Two servers (let’s call them Server A and Server B) running the same OS and Percona Server version (I recommend Percona Server 8.0 for stability)
• Firewall/security group rules allowing traffic on port 3306 between the two servers
• Synchronized system time (use chrony or ntpd to avoid replication timestamp conflicts)
• Install Percona Server on both machines (example for Ubuntu/Debian):

  wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
  dpkg -i percona-release_latest.generic_all.deb
  apt update && apt install percona-server-server -y
  

2. Configure Server A (First Master) #

Edit your Percona config file (usually /etc/my.cnf or /etc/mysql/my.cnf) to enable replication:

[mysqld]
server-id = 1  # Must be unique across all nodes (use 2 for Server B)
log_bin = /var/log/mysql/mysql-bin.log  # Enable binary logging for replication
binlog_format = ROW  # Recommended for consistent data sync
relay_log = /var/log/mysql/relay-bin.log
log_slave_updates = ON  # Critical: lets this server log replicated changes for the other master
auto_increment_increment = 2  # Avoid duplicate auto-increment IDs
auto_increment_offset = 1  # Server A uses odd-numbered IDs
# Optional: Restrict replication to specific DBs (omit to sync all)
# binlog_do_db = your_target_database

Restart Percona to apply changes:

systemctl restart mysql

Next, create a dedicated replication user on Server A:

# Log into MySQL as root
mysql -u root -p

# Create user (replace server_b_ip with your actual Server B IP)
CREATE USER 'repl_user'@'server_b_ip' IDENTIFIED BY 'your_strong_repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'server_b_ip';
FLUSH PRIVILEGES;

# Lock tables temporarily to get a consistent master state
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Save the File and Position values (e.g., mysql-bin.000001 and 156) from the output, then unlock tables:

UNLOCK TABLES;

3. Configure Server B (Second Master) #

Repeat the config edit for Server B, adjusting unique values:

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
relay_log = /var/log/mysql/relay-bin.log
log_slave_updates = ON
auto_increment_increment = 2
auto_increment_offset = 2  # Server B uses even-numbered IDs
# binlog_do_db = your_target_database

Restart Percona:

systemctl restart mysql

Now sync Server A’s data to Server B using mysqldump:

# On Server A, dump your database
mysqldump -u root -p --databases your_target_database > db_backup.sql

# Copy the dump to Server B (replace user/server_b_ip with your details)
scp db_backup.sql your_ssh_user@server_b_ip:/tmp/

# On Server B, import the dump
mysql -u root -p < /tmp/db_backup.sql

Set Server B to replicate from Server A:

mysql -u root -p

CHANGE MASTER TO
MASTER_HOST='server_a_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='your_strong_repl_password',
MASTER_LOG_FILE='mysql-bin.000001',  # Use the File value from Server A's SHOW MASTER STATUS
MASTER_LOG_POS=156;  # Use the Position value from Server A

START SLAVE;

# Verify replication is working (check that Slave_IO_Running and Slave_SQL_Running are 'Yes')
SHOW SLAVE STATUS\G

4. Complete Master-Master Setup #

Now make Server A replicate from Server B to close the loop:

# On Server B, get its master state
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Save the File and Position values, then unlock tables:

UNLOCK TABLES;

On Server A, configure it as a slave to Server B:

mysql -u root -p

CHANGE MASTER TO
MASTER_HOST='server_b_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='your_strong_repl_password',
MASTER_LOG_FILE='mysql-bin.000001',  # Use Server B's File value
MASTER_LOG_POS=156;  # Use Server B's Position value

START SLAVE;

# Verify replication works both ways
SHOW SLAVE STATUS\G

Test by creating a table on Server A—you should see it appear on Server B, and vice versa.

To distribute traffic across your two masters and handle failover, use HAProxy (a lightweight, reliable load balancer):

1. Install HAProxy on a dedicated server (or one of your MySQL nodes, but dedicated is better for uptime)
2. Edit /etc/haproxy/haproxy.cfg with this basic config:

frontend mysql_frontend
    bind *:3306
    mode tcp
    default_backend mysql_backends

backend mysql_backends
    mode tcp
    balance roundrobin  # Distributes traffic evenly; use 'leastconn' for read-heavy workloads
    option tcp-check
    server server_a server_a_ip:3306 check port 3306 inter 2000 rise 2 fall 3
    server server_b server_b_ip:3306 check port 3306 inter 2000 rise 2 fall 3

3. Restart HAProxy:

systemctl restart haproxy

Now point your app to HAProxy’s IP:3306—it’ll route traffic to healthy MySQL nodes automatically.

Since you have two active masters, you can take backups without disrupting production:

• Percona XtraBackup: Hot, incremental backups (no table locks needed):

  # Install XtraBackup (Ubuntu example)
  apt install percona-xtrabackup-80 -y
  
  # Full backup
  xtrabackup --user=root --password=your_root_password --backup --target-dir=/path/to/backup/full
  

• Automate backups: Use cron jobs to run weekly full backups and daily incremental backups, then sync backups to remote storage (like a NAS or object storage)
• Point-in-time recovery: Use mysqlbinlog with your binary logs to recover data up to a specific timestamp if needed.

• If replication breaks, check the Last_Error field in SHOW SLAVE STATUS\G for details
• Double-check that server-id is unique on both nodes (a common mistake)
• Verify your replication user can connect from the opposite server (test with mysql -u repl_user -p -h server_b_ip from Server A)
• Ensure port 3306 is open between all nodes in your setup

内容的提问来源于stack exchange，提问作者CMOS