PostgreSQL 9.4主库迁移后搭建从库报错问题咨询

Hey there! Let's start by mapping out your database migration journey to make sure we're aligned, then jump into the most common fixes for that slave setup error you're facing.

Migration Timeline #

• Original master: Server 01
• First migration: Promoted Server 02 (previously a slave of 01) to be the new master
• Current goal: Set up Server 04 as a slave of Server 02, but hitting errors during configuration

Backup & Restore Context #

You mentioned you have the initial backup and restore files in place—let's walk through critical checks for this step first:

Pro tip: If you can share specifics about how you took the backup (e.g., mysqldump, physical backup tools like Percona XtraBackup) and restored it to Server 04, that'll help narrow things down faster!

Top Fixes for Slave Replication Errors #

Here are the most likely issues causing your setup to fail, along with how to resolve them:

1. Inconsistent Backup

   • If your backup from Server 02 wasn't taken as a consistent snapshot, you'll end up with mismatched data that breaks replication. For InnoDB, use mysqldump --single-transaction to avoid locking tables while getting a consistent backup. For MyISAM, you'll need to lock all tables with FLUSH TABLES WITH READ LOCK before taking the backup.
   • Verify the backup file isn't corrupted: Run md5sum /path/to/backup.sql on Server 02 and the restored file on Server 04—they should have the same hash.

2. Incorrect Replication Position

   • When you took the backup from Server 02, did you capture the master log file and position? If you used mysqldump --master-data=2, this info is already in the backup as commented lines (look for CHANGE MASTER TO).
   • If you missed this step, you'll need to take a fresh backup with --master-data to get the correct log position, then re-restore on Server 04. Using an outdated position will cause replication to fail immediately.

3. Configuration Mismatches

   • Double-check that Server 04 has a unique server-id (it can't be 1, 2, or any other ID used in the replication cluster).
   • Ensure Server 02 has log_bin enabled (required for it to act as a master) and Server 04 has relay_log enabled (required for slave operations).
   • Confirm the replication user on Server 02 has REPLICATION SLAVE privileges, and that Server 04 can connect to Server 02 over the MySQL port (default 3306)—test connectivity with nc -zv 02-server-ip 3306 and check firewall rules.

4. Check the Error Log

   • The best way to get a precise diagnosis is to look at Server 04's MySQL error log (usually at /var/log/mysql/error.log on Debian/Ubuntu, or /var/log/mysqld.log on RHEL/CentOS). Look for lines starting with [ERROR] related to replication—common ones include:
     • Got fatal error 1236 from master when reading data from binary log: This means the slave is trying to read a log position that doesn't exist on the master (almost always a position mismatch).
     • Access denied for user 'repl'@'04-server-ip': Points to a permission issue with the replication user, or a network block.

If you can share the exact error message from Server 04's log, or more details about your backup/restore process, we can get this sorted even quicker!

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