Lesson 7 Project Requirements CIS276DA – MySQL Database Objectives… 1. Demonstrate how to perform general database administration by using the server language. 2. Demonstrate how to configure access control and security. 3. Show the capability of checking the database status and interpreting log files. 4. Show how to relocate tables, databases, etc. (If you need help with creating script files, review the instructions from the Lesson 1 video Submitting Scripts and Query Results Demo.) 1. Your Lesson 7 assignment requires three tasks. In the first task, you will use the Workbench Server Administration Export Tab to dump the entire sampdb. Create a new folder named L7sampdb within your dumps folder that was used for the export. Next, follow the steps that are demonstrated in the lesson video for exporting the data to Dump Project Folder. Remove all tables that are not related to the U.S. Historical League data. In the end, only 4 dump files will be in the folder: member, president, pres_term and routines. Modify the database from ‘sampdb’ to ‘L7sampdb’ from each dump file and save the new files in the folder with following name: Dump_member.sql Dump_president.sql Dump_pre_term.sql Dump_routines.sql Note: There is another way to export the data: select “Export to Self-Contained File” instead of “Export to Dump Project Folder”. If you use this, modify the database from ‘sampdb’ to ‘L7sampdb’ and remove all commands for the tables that are not related to the U.S. Historical League data. In the end, only the database definition and these tables and views will be defined and inserted in your dump file: member, president, pres_term, and views pres_age & pres_age_in_office. To correctly recreate the views, be sure that these are located after the president and pres_term tables, since the views use those tables. Save the new file in that folder with the following name: Dump_ushl.sql Run the import under the Server Administration using the L7sampdb folder. Restart the query window to verify that the import worked by looking at the tables and views within L7sampdb. Run a query of all records for the pres_age and pres_age_in_office views and, using the instructions from Lesson 1, export the results of each query to the following file names: results_L7_pres_age.csv results_L7_pres_age_office.csv To show that the L7Sampdb was added to MySQL, run the query “SHOW DATABASES” and export the query results to a file: results_L7_show_databases.csv 2. The second task will involve using the Workbench Server Administration System Variables button to display all of your system variables. Click the button “Copy Global Status and Variables to Clipboard” to copy the information to your clipboard. Open a new Windows document and then click Ctrl-V to paste that list into the document. It should contain an extensive list (20+ pages) starting with these items: GLOBAL STATUS: Aborted_clients....................................... Aborted_connects...................................... Save the MS Word document for submission as L7_global_status_variables.docx 3. Using the directions from the MySQL Web site (* shown below), relocate your L7sampdb to another folder on your hard drive. Be sure that the MySQL server is not running and keep a copy of the original database. To show that your relocation worked, run a query of the members table in your L7sampdb. Next, copy your MySQL data folders – both the one listed in your Administration Options File as the ‘datadir’ and the newdata folder – to the zip file that you will submit to your instructor Since every student’s configuration can have different directories, the name of the file will be unique to your MySQL configuration, but you need two data folders, containing your sampdb and the L7sampdb.sym file, and the L7sampd database directories. Additionally, copy the my.ini used by your MySQL and include it in the submission.