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. *Here are the directions for relocating a database, modified for this assignment. 8.11.3.1.3. Using Symbolic Links for Databases on Windows On Windows, you can use symbolic links for database directories. This enables you to put a database directory at a different location (for example, on a different disk) by setting up a symbolic link to it. Use of database symlinks on Windows is similar to their use on Unix, although the procedure for setting up the link differs. On any version of Windows supported by MySQL, you can create a symbolic link to a MySQL database by creating a .sym file in the data directory that contains the path to the destination directory. The file should be named db_name.sym, where db_name is the database name. Suppose that you want to place the database directory for a database named L7sampdb at C:\newdata\L7sampdb. To do this, create a symbolic link in the MySQL data directory that points to C:\newdata\L7sampdb. However, before creating the symbolic link, make sure that the C:\newdata\L7sampdb directory exists by creating it if necessary. If you already have a database directory named L7sampdb in the data directory, move it to C:\newdata. Otherwise, the symbolic link will be ineffective. To avoid problems, make sure that the server is not running when you move the database directory. Support for database symbolic links on Windows using .sym files is enabled by default. If you do not need .sym file symbolic links, you can disable support for them by starting mysqld with the --skip-symbolic-links option. To determine whether your system supports .sym file symbolic links, check the value of the have_symlink system variable using this statement: SHOW VARIABLES LIKE 'have_symlink'; In the WorkBench Administration Options File Advanced Tab, scroll to the bottom and click symbolic-links to set it to YES. Now, the SHOW VARIABLES command above will indicate that it is ready to use symlink. To create a .sym file symlink, use this procedure: 1. Change the location into the data directory: 2. C:\> cd \path\to\datadir 3. In the data directory, create a text file named L7sampdb.sym that contains this line to set up the symbolic path name: C:\newdata\L7sampdb\ Note The path name to the new database and tables should be absolute. If you specify a relative path, the location will be relative to the L7sampdb.sym file. After this, all tables created in the database L7sampdb are created in C:\newdata\L7sampdb.