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

5. Log into MySQL and use the DROP TABLE statement to delete the
birds_bill_shapes and birds_body_shapes tables.
Next, use the dump file you made in the second exercise here to restore these
tables from the rookery.sql dump file. When you finish, log into MySQL to verify
that they were restored and contain the data.
6. Log into MySQL and use the UPDATE statement to change the common_name
in the birds table to NULL for any rows where the common_name contains
the word Parrot . There should be about 185 rows.
Make a copy of the rookery.sql dump file. Name it rookery_temp.sql . Edit this
new dump file to change the name of the database to rookery_temp . This
method was described in Restoring Only Rows or Columns .
Next, use the rookery_temp.sql dump file to create the rookery_temp database
on your server. When that's done, restore the Parrot common names in rook-
ery.birds from rookery_temp.birds using the UPDATE statement.
7. If you haven't already, enable binary logging on your server as described in Re-
covering from a Binary Log . Remember to restart the server once you've set it to
be enabled. Use mysqldump to make a backup of just the birds table in the
rookery database. Be sure to include the --flush-logs option.
After you've enabled binary logging and made the backup of the table, log into
MySQL and execute a DELETE statement to delete any birds with the word Gray .
Then insert a few rows of data into the birds table. You can just make up values
for the common_name column and leave the other columns blank.
Now use the dump file to restore the birds table. Using the point-in-time recov-
ery method described in Recovering from a Binary Log , restore all of the transac-
tions in the binary logs up until the DELETE statement that deleted the gray birds
with mysqlbinlog . This will require you to find the position number in the binary
log when the DELETE statement was executed.
Next, using the position number for the transaction immediately after the
DELETE statement in the binary logs, restore the transactions from that point un-
til the end of the binary logs.
Log into MySQL to see whether you were successful in restoring the data. When
you're done, remember to disable binary logging if you don't want to continue
logging transactions.