Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 369

Table 14-3. Backup verification schedule

Back-up Verify Restoration Tests Retention

Database Tables Rows

rookery - full back-up Weekly Monthly N/A Semi-monthly Two months

rookery - bird classification Weekly N/A Semi-monthly Semi-monthly One month

birdwatchers - full back-up Weekly Monthly N/A Semi-monthly Two months

birdwatchers - people Weekly N/A Semi-monthly Semi-monthly One month

birdwatchers - activities Weekly N/A Semi-monthly Semi-monthly One month

Backups will be verified on a regular basis. For testing and practicing purposes, databases, tables, and
rows will be restored regularly in a test environment.

Let's go through the verification plan in this schedule. Once a week we will inspect all of
the dump files made for that week to ensure that the back-ups are being made and contain
the tables that we want. To carry out this task, you could look to see whether the files are
created and check the file sizes of each. You could also open each with a text editor to see
whether it looks correct. You might also usethe grep command to extract the table
names used withthe CREATE TABLE within the dump file. If you want to use grep ,
you could execute something like the following to get a list of tables the rookery.sql dump
file would create if executed:

grep 'CREATE TABLE' rookery.sql | grep -oP '(?<=CREATE\ TABLE\
\`).*(?=\`)'

bird_families
bird_images
bird_orders
birdlife_list
birds
birds_bill_shapes
birds_body_shapes
birds_details
birds_habitats
birds_wing_shapes