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.

Open an admin command window (Press Windows-X and choose Powershell (Admin) or Terminal (Admin)). Enter the following:
```
notepad C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
```
Find the section of the file that looks like this:
```
# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
#log-bin="..."
```
Edit the last line so it looks like this (no leading hash mark):
```
log-bin="binlog"
```
Save the file, then restart MySQL Server to put the option into effect. Note that closing and reopening MySql Workbench does not restart MySQL Server. If you don’t remember how to restart MySQL Server, rebooting your computer will do it (logging out and back in won’t do it).
Use Windows Explorer to navigate to the C:\ProgramData\MySQL\MySQL Server 8.0\Data folder (you may need to turn on the option in Windows Explorer to view hidden files in order to see the c:\ProgramData folder). You should see a file named binlog.00001 in the C:\ProgramData\MySQL\MySQL Server 8.0\Data folder.

Note: If binlog.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 binlog.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\binlog.?????" | 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.