I. OBJECTIVES
1. General analysis and administration of a MySQL server and database.

II. ASSUMPTIONS
In this lab, you will look at various ways to look at how the server is set up to act and react to various situations. You will then demonstrate how to change those reactions. You will also look at ways to enhance the database architecture.

III. PROCEDURE
1. 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.
a. 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.
b. Log into a MySQL session at the root level. This will be the default beginning level for all of your iLab sessions.
c. At the mysql> prompt type the following: tee s:\dbm438\labxoutputfile..log where the x is the lab number.
d. Press enter. You should receive a Logging to ‘s:\dbm438\labxoutputfile.log message.
e. To stop writing to the log you simply type notee at the prompt.
f. 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 then paste them into the lab report document for this lab under the Lab Results section. If you will use Courier New 9-point font formatting it will look just like in the session.

NOTE: THIS INSTRUCTION IS DIFFERENT FROM YOUR PREVIOUS LAB. This lab is going to require you to shut down and then restart your MySQL server service several times which will require you to start a new output file each time you start a new MySQL session. At the end of the lab you can take the contents of each output file and merge them into one file and copy/paste the contents of that file into your iLab report document.

2. Review the lecture in Week 3 for aspects of the MySQL architecture and how it is set up and how it can be changed.

3. MySQL like other database architectures uses variables (Oracle refers to them as parameters) to control how the RDBMS reacts to various things that happen during the course of operations within the database. Now let’s look at some of these variables and how we can change the values that control database operations. First, we are going to revisit the INFORMATION_SCHEMA and look at some very specific things within this schema. NOTE: As we do this you should not ever use a SELECT * query for any of these steps as the results could be totally useless due to the amount of data return. Always be specific as to the columns or specific tables you are looking for.
a. Execute a SHOW command on the INFORMATION_SCHEMA looking for variables that have the word buffer in the name. Find the sort_buffer_size and read_buffer_size. The size for both of these is being displayed in bytes.
b. Execute a set command to change both of these two variables from 256K to 128K. You will have to use the byte value in your set command to make this change work.
c. Now close your MySQL session, start a new one along with a new output file, and execute your original SHOW command to verify that your changes took effect. These changes will only be good until the server is rebooted (you will see the effect of this during the next part of the lab).

4. Now that we have seen how we can change a single variable we are going to look at how we can change numerous variables at startup of the server. MySQL uses an options file to set variable settings at startup of the server much like Oracle uses an initialization parameter file. The file that MySQL uses is in respect to this course is the my.ini file and can be found in the file path of C:\ProgramData\MySQL\MySQL Server 5.6. Open up the file and then save it as MY.INI.COPY. We are now going to promote some changes to MySQL from the server startup.
a. First, exit out of your current MySQL session.
b. Your MySQL server is controlled by a Windows Service so we first need to shut it down. Open up a Windows command line window by going to lower left windows icon and tying CMD in the Search Programs and Files entry box.
c. Now issue a CD C:\ command to change the prompt to the root.
d. Now issue the command to shut down your MySQL56 server service.
e. Next, go into your Windows Explorer and find your my.ini file under the path previously mentioned and set up the server to use the MYISAM storage engine. Open the file and find and change the following variables to the values shown below.
i. default-storage-engine=MYISAM
ii. myisam_max_sort_file_size=50G
iii. myisam_sort_buffer_size=12M
iv. key_buffer_size=6M
v. read_buffer_size=32K
vi. read_rnd_buffer_size=128K
Save your ini file once your changes have been made.
f. Next, go back to your Windows command prompt window and issue the command to start up the MySQL56 service.
g. Now start up a new MySQL session and a new output file and execute the SHOW commands for the variables that you made the changes to and a select statement on the INFORMATION_SCHEMA.ENGINES table to verify that your changes have taken place.

5. As a final step in looking at how the MySQL server architecture is set up we will look at the different logs used and how we can rename them.
a. To begin, you want to repeat steps 4.a, 4.b, 4.c and 4.d that you just completed.
b. Again, you need to open your MY.INI file and find the section where it lists the various logs. You should see a listing for a General log, the Slow log, the Binary log and the Error log. The naming convention for logs by default uses the computer name as one of the nodes, so for example DLS-NB-MOB075.log would be one a computer named MOB075. For each of the four logs change the computer name node to DBM438. Save and close the file.
c. Now go back to your Windows command line session and issue the statement to start the MySQL56 service.
d. Now start up a new MySQL session and a new output file. To verify that your changes took affect issue a SHOW GLOBAL VARIABLES statement looking for variables with log_file in the name.
e. This concludes your lab.

6. 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 then paste them into the lab report document for this lab under the Lab Results section. If you will use Courier New 9-point font formatting it will look just like in the session. This completes this lab.

7. Grading of this lab assignment will be based on the following.

Description Points
Objectives is sufficiently filled out. 5
Steps 3 through 5 were satisfactorily completed. 15
Contents of output file showing session input and results was included in the lab report. 15
Conclusions is sufficiently filled out. 5
Total Lab Points 50