Using fpsync in a two-stage approach for MySQL 8.4 replica setup – is data corruption a risk during the first stage?

MySQL & MariaDB Other MySQL® Questions
1

Hello everyone,

I’m planning to set up a MySQL 8.4 replica and I’m looking for the most efficient way to minimize downtime. My proposed strategy involves a two-stage process using fpsync.

  1. First Synchronization (With MySQL origin online): I will run fpsync to copy the majority of the data from the primary server to the replica server while the primary is still accepting writes.

  2. Second Synchronization (With MySQL origin offline): After the initial copy, I will stop the primary MySQL server and run fpsync a second time. This final sync should be very fast, as it will only need to copy the changes that occurred during the first stage. This will minimize the downtime significantly.

My main concern, which I hope to clarify, is the potential for data corruption during the first stage of fps synchronization on the source node?

Does fpsync risk corrupting the source server’s files when copying them while the database is actively being written to?

I understand that the primary risk is data inconsistency on the replica, not corruption on the source. Is this correct?

Thank you!

2

Why not use Percona Xtrabackup to perform a streaming backup?

On hostB, the new replica, switch to MySQL $datadir and start the listener:

$ cd /var/lib/mysql
$ socat - TCP-LISTEN:3306 | xbstream -vx

On hostA, the source, take the backup and stream it over the network:

xtrabackup --backup --stream=xbstream --user root --compress 
    --compress-threads 4 --parallel 4 | socat - TCP:hostB:3306

This will perform a hot, non-blocking backup of the source, and send it over the network to the replica. 1 “synchronization” step, to use your term.

After the backup completes, decompress the backup, prepare it, then start MySQL.

-- replica host
$ xtrabackup --decompress --remove-original --parallel 4 --target-dir ./
$ xtrabackup --prepare --use-memory 4G --target-dir ./
$ chown -R mysql:mysql ./
$ systemctl start mysql

From here, create a replica user on the source, then configure replication on the replica to finish catch up.

There is absolutely no reason to use some other tool that runs the risk of data corruption, or inconsistency. Xtrabackup has been the de-facto standard for physical MySQL backups for over 15 years. It is proven, solid, and gains new features and improvements with every release.

3 
I would add --decompress-threads=4