Steps to perform recovery after restore operation

The procedure to perform post-recovery is as follows for various platforms:

For Windows (VSS):

Go to Control Panel > System and Security > Administrative Tools > Services.
Select MySQL service and stop it.
Delete or move everything from the MySQL data directory.
Note:
Post restores, change the attributes of the restored data directory and files by using the following command.
attrib -S restore_path/*.* /S /D
Copy all the contents of the restored data directory to MySQL data directory.
Delete all the temporary files from the data directory.
For example:
C:\ProgramData\MySQL\MySQL Server 8.0\Data\#innodb_temp.
Delete undo_00x files from the data directory:
For example:
C:\ProgramData\MySQL\MySQL Server 8.0\undo_001
Start MySQL service.

For Linux (LVM):

Stop MySQL services.
Copy all the contents of the restored data directory to MySQL data directory.
Change the ownership and permission of the MySQL data directory.
For example:
chmod -R 750 mysql_data_directory_path
chown -R mysql:mysql mysql_data_directory_path
Start the MySQL service.
Note:
The binlogs from the incremental backups get restored to the target directory in the MyBINLOGS directory.

Recovery steps for incremental recovery

For the recovery from incremental backups, which contain binlogs, use the following command to replay binlogs:

For Windows:
for /f "tokens=*" %i in ('dir "< restore_path\MyBINLOGS" /s /b') do (mysqlbinog "%i" | mysql -u user -P port -p)
For Linux:
mysqlbinlog restore_directory/MyBINLOGS/* | mysql -u user -P port -p

Recovery steps for backup done using xtrabackup utility

Stop MySQL services.
Delete or move everything from the MySQL data directory.
Perform an operation to bring the data files to a consistent state.
Use --apply-log-only on the full backup only to make it ready for accepting incremental changes using the following command:
xtrabackup --prepare --apply-log-only --target-dir=/restore_dir/FULL_dir
Sequentially apply each incremental backup to the full backup directory using --incremental-dir option. The following is the command:
- xtrabackup --prepare --apply-log-only --target-dir=/restore_dir/FULL_dir --inremental-dir=/restore_dir/INCR1_dir
- xtrabackup --prepare --apply-log-only --target-dir=/restore_dir/FULL_dir --inremental-dir=/restore_dir/INCR2_dir
Note:
Apply incremental backups in the exact order based on the timestamp appended to incremental directories at the end.
Once all incrementals are applied, run a final prepare without --apply-log-only to make backup fully consistent using the following command:
xtrabackup --prepare --target-dir=/restore_dir/FULL_dir
Copy the prepared data files back to the data directory using --copy-backup option of the xtrabackup utility.
The is the command to copy back the backup directory:
xtrabackup --copy-back --target-dir=/restore_dir/FULL_dir

Change the ownership and permission of the MySQL data directory.

For example:

chmod -R 750 mysql_data_directory_path
chown -R mysql:mysql mysql_data_directory_path

Recovery Steps for backup done by mysqldump utility

Recover MySQL database using the mysqldump utility.

Recover single MySQL database.

The following examples of NetBackup commands are used mostly in the Windows and Linux platform.

For Windows:
mysql --host=host --user=user --port=port -p database_name < restore_path\mysqlBackup_Dump_xxx.sqlx
For Linux:
mysql --host=host --user=user --port=port -p database_name < restore_path\mysqlBackup_Dump_xxx.sqlx

Recover MySQL instance.

The following example commands create a single dump file containing all the databases.

For Windows:
mysql --host=host --user=user --port=port -p < restore_path\mysqlBackup_Dump_xxx.sqlx
For Linux:
mysql --host=host --user=user --port=port -p < restore_path\mysqlBackup_Dump_xxx.sqlx

Note:

For GTID-enabled instances, execute RESET BINARY LOGS AND GTIDS;(MySQL 8.0.26+) or

RESET MASTER; (prior to 8.0.26) before recovery of instance-level restores.

The reset is non-fatal and does not affect database-level restores.

Recovery steps for incremental recovery

For doing recovery from incremental backups which contain binlogs, use the following commands to replay the binlogs:

For Windows:
for /f "tokens=*" %i in ('dir restore_path/s /b') do (mysqlbinog "%i" | mysql -u user -P port -p)
For Linux
mysqlbinlog restore_directory/* | mysql -u user -P port -p

Steps to perform after Restore and Recovery in case of MySQL cluster deployment

For MySQL cluster deployment, follow the following steps post Restore and Recovery:
- For mysqldump backup, run following queries on replica:
  - STOP SLAVE;
  - SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 3;
  - START SLAVE;
For snapshot, do the following steps :
On source:
- Run the command$ mysqldump -u user -p --all-databases --master-data > source-data.sql 2.
- Copy source-data.sql file from source to replica host in the following ways:
  - For Linux
    $ scp source_data.sql host_user@ source_ip_address:destination_path
  - For windows
    Either use WinSCP or do it manually.
On replica:
- mysql -u user -P port -p < destimation_path_of_source-data.sql_file
- Run the following MySQL queries:
  - On source:
    reset master;
  - On replica:
    stop slave;
    reset slave;
    reset master;
    start slave;