mysql database

profilegurits

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
    • 20
    Answer(0)