Introduction
INFO20003 Week 11 Lab Database Administration
In this lab, you will use MySQL Workbench to backup and restore schemas and tables in the MySQL database. This lab has three sections, in the first you will perform a logical backup of a schema and an individual table. In the second section you will import a table from an external data source. In the third section you will do a backup and then simulate user error and partial logical recovery of a table.
By the end of this lab, you should be able to:
Copyright By PowCoder代写 加微信 powcoder
• Use MySQL Workbench Data Export and Table Data Export Wizards
• Import tables from a csv file using Table Data Import Wizard
• Restore data after simulating user error using Table Data Import Wizard
Section 1 Logical Exports
There are two types of backup we can perform: physical and logical. A physical backup is an exact copy of the physical file that stores the table and index information including the data dictionary. The exact row location in the physical file does not change.
A logical backup takes the data, structure and meta data information which will recreate the objects in our database including tables, indexes, constraints and views. However, the physical location within the file will be different as the object (table, index) is effectively dropped and recreated. During the life of a database rows are updated and deleted. These changes can result in row storage and index storage becoming fragmented.
TASK 1.1 Open and connect to your Engineering IT MySQL Server using MySQL Workbench
In MySQL Workbench inspect the Management section of the Navigator Window. It provides information about the Server performance and configuration parameters. We will be focussing on the Data Export management tasks.
INFO20003 L-Wk10
Figure 1: The MySQL Management Navigator. Data Export is circled
TASK 1.2 Click on the Data Export Icon
This will open the Data Export Wizard as shown below:
Figure 2: The Schema Data Export Wizard
TASK 2.3 Select your schema for export.
Your schema name should be the same as your username on the Engineering IT server. For the purposes of this lab the screenshots will be selecting labs2018 as the schema to be exported.
INFO20003 L-Wk10
Figure 3 The Data Export Wizard window.
The Data Export Wizard provides many options to export data from the MySQL database server. In Figure 3 we are choosing to take both the structure and data but have options to take the table and index information (including constraint information) without the rows, or only the row data. The export will be to a self contained file which will create the structure and the data.
TASK 1.4 Choose ‘Dump Structure and Data’ as the export format
TASK 1.5 Change the Export to Self-Contained File, file_name to the schema name
(e.g. labs2018.sql), and the directory location to your Downloads directory.
TASK 1.6 Click the Start Export button
You may see a warning dialogue window about mysqldump version mismatch. This can be safely ignored. Click ‘Continue Anyway’.
The export will run and produce a file in the downloads directory in your schema name.
TASK 1.7 Open the file
When you open the file you will notice more than the table structure and row data. The first is that there are some settings which will enable rows to be imported by disabling the foreign key checks. This is so that the table and data can be entered without checking foreign key constraints. The constraints are enabled after the rows are imported. This is in case there are PK / FK references in the one table such as the relationship of EmployeeID to BossID in the Employee table. Further to this, we create the structures at the end so as to speed up the import.
INFO20003 L-Wk10
Table Data Export Wizard
In this section of the lab we will use the MySQL Workbench Table Data Export Wizard to take a logical backup of the Sale table.
TASK 1.8 Open and connect to your Engineering server MySQL Workbench TASK 1.9. Connect to your MySQL Server and execute the following query:
SELECT * FROM sale;
This table, from the department store lab schema, is the table data we will be exporting today. Familiarise yourself with this table.
TASK 1.10. Use the Table Data Export Wizard
In the Navigation Panel on the left hand side of the MySQL Window under the Schema section Right Click on the Sale table.
TASK 1.11 Select Data Export Wizard
TASK 1.12 The Table Data Export Wizard window opens
INFO20003 L-Wk10
TASK 1.13 Select ‘Next’ and the file location window appears
TASK 1.14 Select ‘Browse’ and nominate the ‘Downloads’ folder as the location to save the table export
INFO20003 L-Wk10
TASK 1.15 Save the file as ‘save.csv’ in the Downloads folder.
TASK 1.16 The Table Data Exports option window appears and asks you to confirm Line Separator, Enclose Strings and Field Separator. Then select ‘Next’.
TASK 1.17 The following dialog window is the data export window listing what will occur when you select the next button. It will prepare the database for export and then export the table data to the nominated file.
Select ‘Next’. The table data export will now occur.
Notice that both tasks have now been checked (ticked).
Select ‘Next’ then ‘Finish’ to close the Table Data Export Window
INFO20003 L-Wk10
TASK 1.18 Using a spreadsheet program, open the sale.csv file in the ‘downloads’ folder. Confirm it has the same details as the Sale table in the Department store schema in the MySQL database. Close the csv file.
Section 2 Import Tables
TASK 2.1 Download the PrintMusic.csv file from the LMS Labs Tab. Save this file to the Downloads folder.
TASK 2.2 In MySQL Workbench Navigation window right click in the word “Tables” in the Schema panel and select “Table Data Import Wizard”.
TASK 2.3 Table Data Import Wizard window appears
Click ‘Browse’ and select the ‘PrintMusic.csv’ file from the Downloads folder.
INFO20003 L-Wk10
Select ‘Next’
TASK 2.4 Confirm that the Create new table radio button is selected and the new table is in the format
TASK 2.5 Confirm the data types and character set encoding in the Confirm Configuration window.
INFO20003 L-Wk10
When importing tables MySQL makes assumptions about the text encoding (utf8 in this example) and the data types.
TASK 2.6 Accept the defaults to complete the table import:
Select ‘Next’ to confirm this configuration.
Select ‘Next’ to perform the import.
Select ‘Next’ to confirm the import has succeeded. Select ‘Finish’ to close the Table Data Import Wizard.
TASK 2.7 Confirm the data and table are now in your schema by entering the following SQL:
FROM printmusic;
Note that the SQL Output window (the bottom frame of MySQL Workbench) reports that MySQLTable Import wizard did many things as part of this table import:
INFO20003 L-Wk10
Section 3. Restore table data
TASK 3.1 Take a table export of the printmusic table. Save the file as printmusic2.csv in the Downloads directory.
TASK 3.2 Simulate user error by using the DDL command TRUNCATE TABLE and remove the data from the table printmusic:
TRUNCATE TABLE printmusic;
This SQL statement will remove all rows from the PrintMusic table but keep the table structure.
TASK 3.2 Confirm there are no rows in the printmusic table:
FROM printmusic;
Because we have the printmusic2.csv file containing the rows of the printmusic table we can restore this table using the Table Data Import Wizard.
TASK 3.3 In MySQL Workbench Navigation Panel Right click the word “Tables” in the Schema panel and select “Table Data Import Wizard”.
The Table Data Import Wizard window appears
INFO20003 L-Wk10
Click ‘Browse’ and select the ‘PrintMusic2.csv’ file from the Downloads folder.
Select ‘Next’
Confirm that the ‘Use existing table’ radio button is selected and the table is
Confirm the data types and character set encoding in the Confirm Configuration window.
INFO20003 L-Wk10
TASK 3.4 Accept the defaults and complete the restore of the row data:
Select ‘Next’ to confirm this configuration.
Select ‘Next’ to perform the restore of the truncated rows Select ‘Next’ to confirm the import has succeeded. Select ‘Finish’ to close the Table Data Import Wizard.
TASK 3.5 Confirm the rows have been restored to the printmusic table:
FROM printmusic;
END OF LAB 10
INFO20003 L-Wk10
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com