MySQL MEB FULL Backup and Restore Testing (incremental)

 

MySQL MEB FULL Backup and Restore Testing

Here for this, we are testing the scenarios on Test server (TestServer). The backup and restore is little different from Oracle point of view. So we need to make sure we are testing MySQL as is with available functionality.  Do follow below steps to test the scenario to make sure we can put “Incremental Backups” in line.

1. Restore the database from Prod backup on to Test Server (TestServer). Once restored, Make sure can login into MySQL database and query tables and rows. Edit the shell with backup location.

Use this script: /opt/mysql/mysql-meb-restore_auto.sh

2. Once confirmed, take MEB backup for Test Server (TestServer).

Use this script: 

/opt/mysql/scripts/mysql-meb-backup.sh FULL 14 

3. Once backed up, make sure you can login into MySQL database and query tables and rows and truncate below tables.

select table_schema,table_name,table_rows from information_schema.tables where

table_schema='scott' and table_rows < 20000 order by 3 desc;

select count(*) from scott.Table1;

select count(*) from scott.Table2;

select count(*) from scott.Table3;

select count(*) from scott.Table4;

example: 

truncate scott.Table1;

4. We now take the incremental backup on the Test Server (TestServer).

Use this script: 

/opt/mysql/scripts/ mysql-meb-incr-backup.sh INCR 14

5. Now, we try to restore MySQL as full backup and test it. edit the shell with backup location.

Use this script: 

/opt/mysql/ mysql-meb-restore_auto_host.sh

6. Once restored, login and see the table rows of the above truncated tables. You should see all rows.

7. Now, do the incremental restore to get to the current state. Edit the shell with backup location.

Use script:

/opt/mysql/mysql-meb-restore_auto_incremental.sh

8. Once restore, login to MySQL database and check the truncate table row counts.

Point-in-time recovery:

Point-in-time is manual process in MySQL not like that we do in Oracle.

MySQL doesn’t recommend using timestamp to do point-in-time recovery. Instead we need to restore the database to a logfile before the event logfile happened and then apply the changes of the event logfile using start lsn and end lsn. Below link you can take a look for Point-in-time recovery.

• Using the --start-datetime or --stop-datetime option to specify the range of binary log segment to replay is not recommended: there is a higher risk of missing binary log events when using the option. Use --start-position and --stop-position instead.

Links for More Information: 

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/mysqlbackup.incremental.html

https://dev.mysql.com/doc/mysql-enterprise-backup/8.4/en/backup-incremental-options.html

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/restore.incremental.html

https://dev.mysql.com/doc/refman/8.4/en/analyze-table.html

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/advanced.point.html