DBM438 Lab 7 Creating and Managing Database Backups

Description

I. OBJECTIVES
Take a look at the process of creating and managing database backups and how these backups are used in recovery of the database when it becomes corrupted.
II. ASSUMPTIONS
At the end of this lab, you should have a better understanding of how both a binary backup of a MySQL database and a text backup of the database works.
III. PROCEDURE
For each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.
Create a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.
Log into a MySQL session at the ‘root’ level. This will be the default beginning level for all of your iLab sessions.
At the mysql> prompt, type the following: tee s:dbm438labxoutputfile.log, where the ‘x’ is the lab number.
Press enter. You should receive a ‘Logging to ‘s:dbm438labxoutputfile.log’ message.
To stop writing to the log you simply type note at the prompt.
Unfortunately, you cannot copy this file from your VM to your host computer so you will need to open the file, copy the contents, and paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session.
NOTE: If you have to stop your lab session and go back later you will need to create a new file with a different name, otherwise if you use the same file name you will overwrite what you previously recorded. You can later copy and paste the contents of the second file into the first and thus just have one file to turn in.
Review the lecture in Week 7 for additional information that can be used for this lab.
The MySQL architecture supports two primary ways to backup a database; binary and text. To start with we are going to make binary backup of the devrydbm438 database.
From the Windows Command prompt, log in to a MySQL session as the root user. Once logged in, you will need to start an output file.
Before you start making copies of the database files for the devrydbm438 database, you need to find out what the most current log file used is so that this can be captured as well. Use the SHOW MASTER STATUS command to find the name of this file and make note of it.
We now need to make sure that no changes are made to any of the tables during the backup process. For each of the four tables in the database, you need to lock the table in read-only status and then flush the table. Be sure that you have set the session to use the devrydbm438 database.
Now, you are going to physically copy the files associated with the devrydbm438 database, but first we need a place to put them under your DBM438 folder on the S: drive of your VM create a new folder named backup. Once this is done, exit out of the MySQL session.
Using Windows Explorer in your VM, find the data folder under the MySQL folder found in the Program Data folder on your C: drive. This is where all of the files you need to copy can be found. First, find the log file that you made note of using the show master status command and copy that file to your backup folder on the S: drive. Next, copy all of the files in the devrydbm438 folder to your backup folder on the S: drive.
Now you need to start a new MySQL session from the C:> prompt in the Windows Command window. Once logged in, start a new output file. After setting the session to use the devrydbm438 database, write a query to select all of the contents from the EMP table. What was the result? Add content to the Conclusions section of the iLab Report stating the results and reason.
To complete this part of the lab you need to unlock each table. Once you have done this, issue another select statement on the EMP to display all of the contents and verify that the table is unlocked. You can now exit out of the MySQL session but do not exit out of the Windows Command window.
Now that you have created a binary backup of the database, we are going to use a MySQL utility to make a text backup of the database. This utility generates a SQL text file containing SQL commands which can be used to recover and restore the database. Since this utility works from outside a MySQL session, you will issue the commands for this from the Windows Command C:> prompt.
You will be using the MYSQLDUMP utility as the root user to make your backup. Although this utility can drill down to an individual table level within a database, we are going to make a backup of the entire devrydbm438 database. From the C:> prompt, issue the command to use mysqldump to backup the devrydbm438 database and put the backup file in the backup folder under your S drive. Name the output file dbm438_yourlast name_dump.sql.
i. NOTE: Your text shows an example using the % character to indicate the prompt. Also, the text does not show the full syntax for the mysqldump utility which is:
C:> mysqldump –u user_name –p database_name > “full_pathfilname.sql”
Using this example you will be prompted for the password for the root user.
For a large database, this process could take a while to run but for what we are working with it will not. Once the process finishes, go to your folder on the S: drive and verify that the file is there and that the contents look similar to the example in the textbook. You will submit a copy of this file with your Lab Report.
Now we are going to try and recover the devrydbm438 database using the text backup that we just created. Since this not actually a working business database and the data in the tables has not been constantly changing it is a little easier to do, as we will not have to work with the binary log files.
First, start a MySQL session logged in as the root user. Once your session is running start a new output file.
Set the database for the session to the devrydbm438 database and query the EMP table to show all of the contents.
Now we are going to cause some trouble. First, exit out of your current MySQL session back to the Command Window C:> prompt. Now issue the command to shut down the mysql56 server process (NET STOP MYSQL56). Now go into the ProgramData folder in Windows Explorer and find the Data folder under MySQL and the folder for the devrydbm438 database. Find and open the emp.frm folder (you will need to select notepad to open it with), place your curser anywhere in the first two lines of binary data and hit the enter key twice. Now save the file.
Back at the Windows Command prompt, start the mysql56 service back up (NET START MYSQL56). Once started, start a new MySQL session as the root user and start a new output file.
Set the session to use the devrydbm438 database and issue a SELECT statement to show all of the data in the table—and now we have a problem, but we can fix it.
Using the SOURCE command like you did in Lab 6, run the SQL file that you created earlier using the mysqldump utility. Once it has finished and you are back at the mysql> prompt, issue a SELECT statement to show all the contents in the EMP table. You should now have a recovered and restored database and can stop the current output file and exit the session.
You will need to open the output file for this lab on the S: drive of your VM, copy the contents of the file, and paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session. You also need to remember to include either the actual backup file created with mysqldump with your lab report or copy the contents into the lab report. This completes this lab.
Grading of this lab assignment will be based on the following:
[table]
Description, Points
Objectives section is sufficiently filled out., 5
Steps 3 through 6 were satisfactorily completed. ,15
Contents of output file showing session input and results were included in the iLab Report., 15
Conclusions section is sufficiently filled out., 5
Total Lab Points:, 50
[/table]
SCREENSHOTS
SOLUTION
PAYMENT
The solution includes a zip document.
Attachments [Move over files to preview content of those files]
DBM438_Week_7_Lab.zip (53.30 KB)
DBM438-master.000004
DBM438_Week_7_iLab_Report.docx
DevryDBM438.sql
Preview DevryDBM438.sql
;
;
;
;
;
;
— — Table structure for table `author` —
DROP TABLE IF EXISTS `author`; ; ; CREATE TABLE `author` ( `AuthorID` int(4) NOT NULL, `Lname` varchar(10) DEFAULT NULL,
`xxxxx` xxxxxxx(10) xxxxxxx xxxx,
xxxxxxx xxx (`xxxxxxxx`)
) xxxxxx=xxxxxx xxxxxxx xxxxxxx=xxx8;
;
—
Price: $19
Buy Now
Checkout
Added to cart
Buy More Save More
Buy at least TWO items & save up to 30% OFF your ENTIRE order!
Rack up instant rebates in your shopping cart. Simply add items to your cart, and see the savings add up.
Discounts will automatically be applied on eligible orders.
Add to Cart
Checkout
Added to cart
You May Also Like:
DBM438 Lab 1 Comparison of MySQL Oracle and SQL Server
DBM438 Lab 2 Create a new MySQL Database
DBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database
DBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management
DBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database
DBM438 Lab 6 Creating and Managing Users in the Database