Ask Computer Engineering Expert

iLab 4: Using Backups for Complete Recovery

Scenario and Summary

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.

Part 1: Take Dump/Backup and restore database using command line

Step 1
Taking a backup of the database

Step 2
Expand table

Step 3
Restore and recover the database

Part 2: Backup and Recovery using MySQL Workbench

Step 1
Taking a backup of the database using Export to Dump Project Folder

Step 2
Taking a backup of the database using Export to self-contained file

Step 3
Expand table

Step 4
Restore database using Import from Dump Project Folder

Step 5
Expand table

Step 6
Restore database using Import from self-contained file

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.
a) 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
b) Enter password; press Enter;
c) 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"
d) Enter password;
Please note that c) and d) are needed only in case of error.
Save screenshot
e) To verify back-up file:
Start -All programs-Computer-Local Disk (C)-backup file.sql
Save screenshot

8. Go to MySQL Command line - Unicode Using DESCRIBE statement show the columns in the table
Mysql>describe databasename.tablename;
Save screenshot
Step 2 Expand table

1. Using ALTER command add 2 columns to the table
Mysql>ALTER table databasename.tablename
ADD col1 varchar(10);
Save screenshot

2. Using DESCRIBE statement show the columns in the table
Mysql>describe databasename.tablename;
Save screenshot

Step 3 Restore and recover the database
1. 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)
2. Set up the location: 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 bin and press Enter

3. Type:
C:\Program Files\MySQL\MySQL Server5.6\bin>Mysql -uroot -p databasename < "c:\backup file name with extension .sql"
Where sign < means to take output (from the right part) and put into the output (left part). Database will be restored.

Note: The location of backup file has to be enclosed into the double quotes.
Save screenshot

4. Go to MySQL Command line - Unicode Using DESCRIBE statement show the columns in the table
Mysql>describe databasename.tablename;
Save screenshot

Part 2 Backup and Recovery using MySQL Workbench
To Backup database
1. Start MySQL Workbench
2. Connect to database and Test connection
3. Double click on Connections to connect the database; Enter Password
4. On Navigator panel under the Management click ‘Data Export', enter password
5. On Data Export screen highlight the database name and check it;
6. Click Select tables button if you want to back up the entire database;
7. There are 2 options to save backup file.

Option 1
· Export to Dump Project Folder. Choose this option and back up all tables. Click Start Export, enter password, and review created .sql files (separated for each table in database). It allows you to restore the selected tables in a future. Save screenshots.
· Close Administration - Data Export tab.
· Go to the Windows Employer, review folder dumps content. Save screenshots.

Option 2
Repeat steps 5 and 6.
· Export to Self-contained file. All tables in database will be stored in one file. Choose this option and give the unique name to the backup file to help you to identify file in a future. Click Start Export, enter password.
· Save screenshots
· When export is finished, close Administration - Data Export tab.
· Go to the Windows Employer, review folder dumps content. Save screenshots.

8. Expand table Employee by adding 2 columns. Please use Query 1 screen.
Hint: Add columns using ALTER command. Highlight query, choose Query in toolbar and click Execute current statement
- Save screenshots

9. Show new table
- Click icon Create new SQL tab for executing queries (located under the File) to display expanded table.
Hint: Use DESCRIBE statement. Highlight query, choose Query in toolbar and click Execute current statement
- Save screenshots
To Restore database

10. Click on Data Import/Restore, enter password

Option 1
a. Choose Import from Dump Project Folder and insure that the file location is correct
b. Select Schema on Select Database object to Import (only available for project folders)
c. You have an option to select which table to restore and click Start Import; enter Password
d. Save screenshot with successful completion
e. Close Administration - Data Import/Restore tab
f. Click icon Create new SQL tab for executing queries (located under the File) to display restored table.
g. Save screenshot

Option 2

11. Repeat Step 8, Step 9 and Step 10. Enter password

12. Now, restore from Import from Self-contained file
a. Choose Import from Self-contained file
b. Select file you want to restore
c. Click on Start Import; enter password;
d. Save screenshot with successful completion
e. Close Administration - Data Import/Restore tab
f. Click icon Create new SQL tab for executing queries (located under the File) to display restored table.
g. Save screenshot
This concludes the Lab for Week 4

Computer Engineering, Engineering

  • Category:- Computer Engineering
  • Reference No.:- M91592618
  • Price:- $30

Priced at Now at $30, Verified Solution

Have any Question?


Related Questions in Computer Engineering

Does bmw have a guided missile corporate culture and

Does BMW have a guided missile corporate culture, and incubator corporate culture, a family corporate culture, or an Eiffel tower corporate culture?

Rebecca borrows 10000 at 18 compounded annually she pays

Rebecca borrows $10,000 at 18% compounded annually. She pays off the loan over a 5-year period with annual payments, starting at year 1. Each successive payment is $700 greater than the previous payment. (a) How much was ...

Jeff decides to start saving some money from this upcoming

Jeff decides to start saving some money from this upcoming month onwards. He decides to save only $500 at first, but each month he will increase the amount invested by $100. He will do it for 60 months (including the fir ...

Suppose you make 30 annual investments in a fund that pays

Suppose you make 30 annual investments in a fund that pays 6% compounded annually. If your first deposit is $7,500 and each successive deposit is 6% greater than the preceding deposit, how much will be in the fund immedi ...

Question -under what circumstances is it ethical if ever to

Question :- Under what circumstances is it ethical, if ever, to use consumer information in marketing research? Explain why you consider it ethical or unethical.

What are the differences between four types of economics

What are the differences between four types of economics evaluations and their differences with other two (budget impact analysis (BIA) and cost of illness (COI) studies)?

What type of economic system does norway have explain some

What type of economic system does Norway have? Explain some of the benefits of this system to the country and some of the drawbacks,

Among the who imf and wto which of these governmental

Among the WHO, IMF, and WTO, which of these governmental institutions do you feel has most profoundly shaped healthcare outcomes in low-income countries and why? Please support your reasons with examples and research/doc ...

A real estate developer will build two different types of

A real estate developer will build two different types of apartments in a residential area: one- bedroom apartments and two-bedroom apartments. In addition, the developer will build either a swimming pool or a tennis cou ...

Question what some of the reasons that evolutionary models

Question : What some of the reasons that evolutionary models are considered by many to be the best approach to software development. The response must be typed, single spaced, must be in times new roman font (size 12) an ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As