This procedure assumes that binary logging (bin-log) is already enabled on the master server, as it is a prerequisite for replication recovery.

First, create a dedicated user with appropriate privileges to facilitate the data backup process:

[root@server]# mysql -uroot -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'backup_admin'@'192.168.10.5' IDENTIFIED BY 'secure_password';

Execute the backup on the master database. The following flags are critical for ensuring data consistency without locking tables:

• --single-transaction: Uses a consistent read for InnoDB tables.
• --master-data=2: Records the binary log coordinates in the output file.
• --no-autocommit: Disables autocommit to improve performance and integrity.
• -A: Dumps all databases.

[root@server]# mysqldump -ubackup_admin -p --single-transaction --master-data=2 --no-autocommit --all-databases > full_backup_dump.sql

While it is possible to dump specific databases, backing up the entire instance is recommended to guarantee full consistency and avoid issues with cross-database dependencies.

On the slave server, prepare the environment by clearing the existing replication metadata and resetting the configuration.

1. Remove the replication metadata files: master.info, relay-log.info, and relay-bin.index.
2. Access the MySQL shell to reset the master and slave configurations.

[root@server]# mysql -uroot -p
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS\G
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> SHOW SLAVE STATUS\G

At this stage, both master and slave configurations are cleared. The next step is to import the data and re-establish the replication link using the coordinates found within the backup file, rather than the current status of the master.

Execute the following steps on the slave server to restore the data:

[root@server]# mysql -uroot -p < full_backup_dump.sql

After the import is complete, configure the slave to connect to the master using the binary log file and position extracted from the head of the full_backup_dump.sql file:

[root@server]# mysql -uroot -p
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.10.5',
    -> MASTER_USER='backup_admin',
    -> MASTER_PASSWORD='secure_password',
    -> MASTER_LOG_FILE='mysql-bin.000315',
    -> MASTER_LOG_POS=154;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Verify the replication status. Both Slave_IO_Running and Slave_SQL_Running should display Yes. The Seconds_Behind_Master metric may initially show a large value, representing the backlog of data to be synchronized, and will gradually decrease to zero once the slave has caught up with the master.