MySQL主从同步：主库数据量大时新增从库及同步历史数据

Hey there! Let's break down your questions clearly—this is a super common scenario in production environments, and there are solid, tried-and-true ways to make it work without headaches.

1. 主服务器已有大量数据时，能不能新增从服务器？ #

Absolutely yes! Even if your master has tens or hundreds of gigabytes of data, you can absolutely set up a new slave and sync both historical and real-time data smoothly. The key is to pair a full backup of the master with incremental binlog replication.

2. 主服务器数据量大时的同步实现方案（附解决MASTER_LOG_POS问题的方法） #

The core idea is: first sync the full historical dataset, then catch up with incremental changes via the master's binlogs. Below are two reliable methods that eliminate the pain of manually getting the correct MASTER_LOG_POS:

Method 1: Use mysqldump (great for medium-sized datasets, easy to operate) #

This method locks down the master's binlog position automatically during backup, so you don't have to worry about manual errors:

• Step 1: On the master server, run the dump command to create a full backup and record binlog info:

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

  Quick breakdown of flags:
  • --single-transaction: Creates a consistent snapshot for InnoDB tables without locking the entire database (note: MyISAM tables will still get locked)
  • --master-data=2: Adds a commented-out CHANGE MASTER TO statement at the top of the backup file, which includes the exact MASTER_LOG_FILE and MASTER_LOG_POS at the time of the backup
• Step 2: Transfer the backup file to the slave server and import it into the slave database:

  mysql -u root -p < full_master_backup.sql
  

• Step 3: Configure replication on the slave:
  First stop the slave process:

  STOP SLAVE;
  

  Then copy the CHANGE MASTER TO line from your backup file, update the master's IP, replication user, and password, then run it:

  CHANGE MASTER TO
  MASTER_HOST='your_master_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='the_log_file_from_backup',
  MASTER_LOG_POS=the_position_number_from_backup;
  

  Finally start the slave and verify status:

  START SLAVE;
  SHOW SLAVE STATUS\G;
  

  You're good to go when both Slave_IO_Running and Slave_SQL_Running show Yes.

Method 2: Use Percona XtraBackup (best for very large datasets, zero downtime) #

If your master has hundreds of gigabytes or more, mysqldump can be slow and impact performance. XtraBackup is a hot-backup tool that works seamlessly with InnoDB and causes almost no lock on the master:

• Step 1: Take a backup on the master—this automatically records the binlog position:

  xtrabackup --user=root --password=your_root_password --backup --target-dir=/path/to/your/backup
  

  After backup finishes, you'll find a file named xtrabackup_binlog_info in the backup directory—it contains the exact MASTER_LOG_FILE and MASTER_LOG_POS you need.
• Step 2: Prepare the backup to make it consistent (this is critical):

  xtrabackup --user=root --password=your_root_password --prepare --target-dir=/path/to/your/backup
  

• Step 3: Restore the backup to the slave (stop the slave service first):

  systemctl stop mysql
  rm -rf /var/lib/mysql/*
  xtrabackup --copy-back --target-dir=/path/to/your/backup
  chown -R mysql:mysql /var/lib/mysql
  systemctl start mysql
  

• Step 4: Set up replication on the slave using the info from xtrabackup_binlog_info, same as Method 1—run CHANGE MASTER TO with the correct log file and position, then start the slave.

Why you struggled with getting MASTER_LOG_POS manually #

The problem with manually running SHOW MASTER STATUS is that by the time you finish taking the backup, the master's binlog position has already changed (since data is still being written). The methods above automatically capture the exact binlog position at the moment the backup snapshot was taken, so you don't have to worry about mismatched data between the full backup and incremental replication.

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