Lab 5: MySQL Disaster Recovery

Overview

In this exercise, you will work with MySQL disaster recovery procedures.

Instructions

As you work through the following steps, take screen shots to record your work at key points.

In MySQL Workbench, right-click on the connection to your database and choose Edit Connection. Verify that the configuration file is correctly set as follows:
Enable binary logging. In MySQL Workbench: Server > Options File > set log-bin option to translog to turn on binary logging with a filename of translog. Restart MySQL Server to put the option into effect.
Observe that translog.00001 is created in the C:\ProgramData\MySQL\MySQL Server 8.0\Data folder.

Note: If translog.00001 does not appear, you may not have edited the correct MySQL options file in the previous step. Double-check the name of the configuration file as instructed above.
Execute in SQL editor:
```
show binary logs;
```
You should see translog.00001 appear in the list.
Make a backup of a database (command prompt):
```
mysqldump.exe --single-transaction --flush-logs simpledb -u root -ppassw0rd > backup.sql
```
This will flush the logs, create the backup, and then begin a new log file. Use show binary logs command to verify that a new log file was created.
Modify the database (change a value in a record). Note the time at which you commit the modification (using your computer’s clock).
Make a second modification to the same record. Note the time at which you commit the modification (using your computer’s clock).
Flush the logs (in SQL editor):
```
flush logs;
show binary logs;
```
Note that a new transaction log file was created. Which log file contains the modifications you performed in the previous steps? Make a note of the log file.
Make another modification to the same record as you did previously. Flush the logs. Make a note of the log file that contains the modifications performed in this step.

Perform a Full Recovery (7 points)

Drop and recreate the database (in SQL editor):

drop database simpledb;
create database simpledb;

Restore database from backup (command prompt):
```
mysql.exe simpledb -u root -p < backup.sql
```
Review database. Contents should not contain the modifications made in the earlier steps.
Restore the first group of modifications that you made previously (command prompt):
```
mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\translog.?????" | mysql -u root -p
```
Note: Substitute the name of the log file you wrote down from the earlier step that contains the first group of modifications.
At this point, review the database. The contents should now reflect the first set of modifications.
Restore the second group of modifications that you made. Review the database; it should now reflect the final set of modifications.

Perform a Point in Time Recovery (3 points)

Now, you will perform a point-in-time recovery.

Drop and recreate the database.
Use the techniques discussed in the lecture to recover the database to the point just after you made the very first modification.

Submission

Write up a lab report that demonstrates that your experiments worked, including a narrative of what you did and labeled screen shots that illustrate what occurred. Submit your lab report to Canvas as a PDF named report.pdf.