Step

MIS561 Lab 4 Using Backups for Complete Recovery – DeVry University

Successful database recovery relies on the database being backed. This is what enables the database to recover data that were lost due to a media failure, loss of power, or any other type of disaster. Each time the online line redoes logs filled up in a log group, the DBMS issues a log switch that will move the log writer process from the current redo log group to the next one in line. The archive background process begins archiving the data in the full online log group files to the archive log files. This is done simultaneously for each log file in the group. If media recovery is needed at the time that the database needs recovery, then the DBMS will search through the archived log files to find the ones that need to be used.

In Part 1, you will use mysqldump command to dump database, expand tables, and restore database. In Part 2, you will use MySQL Workbench to back up, expand tables, and restore the database. Note: Be sure that you are making screenshots and save in Lab4 Report.

Deliverables

Grading of the lab assignment will be based on the following.

Assignment Step

Part 1: Take Dump/Backup and restore database using command line Step 1 Step 2 Step 3 Part 2: Backup and Recovery using MySQL Workbench

Description

Taking a backup of the database Expand table Restore and recover the database

Step 1

Step 2 Step 3 Step 4 Step 5 Step 6 Taking a backup of the database using Export to Dump Project Folder Taking a backup of the database using Export to self-contained file Expand table Restore database using Import from Dump Project Folder Expand table Restore database using Import from selfcontained file

Total iLab Points

Points

5 5 10

5

5 5 10 5 10 60

The Report file should be a single file called yourname_Lab_4.doc to submit to the Dropbox for the Week 4 iLab.

Your report should show any queries, create statements, or other SQL code, along with the resulting return from the database.

iLAB STEPS Part 1: Take Dump/Backup and Restore Database Using Command Line Step 1: Taking a Backup of the Database

1. Go to MySQL Command line – Unicode and enter password. 2. There are several databases here now. To list them, type:

Mysql>show databases; 3. Identify required database;

You have to make database backup to be used later to restore. 4. Confirm the location of mysqldump utility on your local machine.

Usually, it is:

C:\Program Files\MySQL\MySQL Server5.6\bin 5. Go to Start – Accessories – Point to Command Prompt and right-click on Run as administrator (to be able to have all types of access on PC). 6. Set up location using cd command: type in Command prompt:

C:\user username>cd C:\ and press Enter

C:\>cd Program Files and press Enter

C:\\Program Files> cd MySQL and press Enter.

C:\Program Files\MySQL>cd MySQL Server5.6\ and press Enter.

C:\Program Files\MySQL\MySQL Server5.6>cd and press Enter. 7. Use mysqldump utility. 1. In Command prompt, type: Mysqldump –uroot –p databasename > c:\backup file name with extension .sql For example: “c:\mis561.sql” Where sign > means to take output (from the left part) and put into the input (right part). All SQL instructions will be written to back-up file. Note: The location of back-up file has to be enclosed into the double quotes.

Example: C:\Program Files\MySQL\MySQL Server5.6\bin>Mysqldump –uroot –p databasename >c:\ backup file name with extension .sql

2. Enter password; press Enter; 3. In case of error Could not execute SELECT @@GTID_Mode, re-type with GTID options:

Mysqldump –uroot –p -set-gtid-purged=OFF databasename > “c:\backup file name with extension .sql” 4. Enter password;

Please note that c) and d) are needed only in case of error.

Save screenshot 5. To verify back-up file:

Start -All programs-Computer-Local Disk (C)-backup file.sql

Save screenshot