mysql database

profilegurits

Laboratory Procedures 
DeVry University
College of Engineering and Information Sciences

 

OBJECTIVES

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

Things to do prior to beginning your lab.

 

If you are using the EDUPE-VT environment then before you begin any lab work you will want to check the current status of your VM before trying to access it.  If the VM is either suspended or halted it will be necessary to change the status to running.  To do this you will need to log into EDUPE-VT VM Control Center (https://devry.edupe.net:9090/).  First, you want to set your email notification.  Now, use the STUDENT MAINTANCE_TOOLS - CHECK VM USAGE tool to check the status of the VM.  If the VM is in a suspended status then use the VM_TOOLS - CONTROL VM tool to RESUME the VM.  If the VM is in a halted status then use the VM_TOOLS - CONTROL VM tool to RESTART the VM. 

 

Once your VM is in a state of “running” then you can go to the EDUPE-VT Remote Desktop Gateway (http://devry.edupe.net:8080/) and follow the instructions in the “Student Guide_Connecting to Your VM Remote VM Desktop” document found in Doc Sharing for accessing your VM).

 

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.  Once you have successfully logged into your VM, follow the instructions in the “Student Guide_Creating an output script of your MySQL session” document that can be found in DocSharing.

 

NOTE: If you have to stop your lab session and then go back later you need to be sure that you are using the same file name for your output file.  Doing so will allow MySQL to append the new work to the end of the file thus allowing you to save all of your lab output into one file.  If you do end up using two or more file names then you will need to 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 3 for aspects of the MySQL architecture and how it is set up and how it can be changed.

 

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.

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_rnd_buffer_size.  The size for both of these is being displayed in bytes.

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.

Now execute your original SHOW command to verify that your changes took effect.  These changes will only be good for this session (you will see the effect of this during the next part of the lab). 

 

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.

First, exit out of your current MySQL session.

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.

Now issue a CD C:\ command to change the prompt to the root.

Now issue the command to shut down your MySQL56 server service.

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:

default-storage-engine=MYISAM

myisam_max_sort_file_size=50G

myisam_sort_buffer_size=12M

key_buffer_size=6M

read_buffer_size=32K

read_rnd_buffer_size=128K

Save your ini file once your changes have been made.

Next, go back to your Windows command prompt window and issue the command to start up the MySQL56 service.

Now start up a new MySQL session and your output file using the same file name 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.

 

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.

To begin you want to repeat steps 4.a, 4.b, 4.c and 4.d that you just completed.  

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 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 for a computer named MOB075.  For each of the three logs change the computer name node to DBM438.  Save and close the file.

Now go back to your Windows command line session and issue the statement to start the MySQL56 service.

Now start up a new MySQL session and your output file.  To verify that your changes took affect issue a SHOW GLOBAL VARIABLES statement looking for variables using the LIKE delimiter with log_file in the name.

This concludes your lab.

 

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 9pt. font formatting it will look just like in the session.  This completes this lab.

 

 

 

 

 

    • 9 years ago
    • 10
    Answer(0)
    Bids(0)