Home arrow MySQL arrow Page 7 - Back to Basics

Reconfiguration - MySQL

This chapter from High Performance MySQL by Jeremy Zawodny and Derek J. Balling. (O'Reilly Media, ISBN: 0-596-00306-4, April 2004) talks about binary distributions, the sections in a configuration file, and some SHOW commands that provide a window into whatís going on inside MySQL. This book is for the MySQL administrator who has the basics down but realizes the need to go further.

  1. Back to Basics
  2. Binary Versus Compiled From Source Installations
  3. MySQL.com Binary Versus Distribution Binary
  4. Configuration Files
  5. File Format
  6. Sample Files
  7. Reconfiguration
  8. The SHOW Commands
By: O'Reilly Media
Rating: starstarstarstarstar / 19
June 08, 2004

print this article



When an administrator adjusts the server parameters, itís common to go through an iterative process that involves making changes, restarting the server, performing some tests, and repeating the process. In fact, weíll look at doing just that in Chapter 3. In the meantime, itís worth mentioning that you should strongly con sider putting your MySQL configuration files into some sort of revision control sys tem (RCS, CVS, Subversion, etc.). Doing so gives you an easy way to track changes and back out of a bad configuration change.

As of MySQL 4.0, itís possible to change server variables on the fly at runtime. For example, if you wanted to increase the size of the key buffer from what it was set to at startup, you might do the following:

mysql> SET GLOBAL key_buffer=50M;

This sets the global value for key_buffer to 50 MB.

Some variables, such as sort_buffer_size , can be set globally so that they affect all new threads on the server, or they can be defined so that they apply only to the cur rent MySQL client session. For example, if you wish to make a series of queries that might better use a large sort buffer, you can type:

mysql> SET SESSION sort_buffer_size=50M;

Variables set using the SESSION syntax are thread-specific and donít alter the values other threads use.

Itís important to note that any change you make here, using either GLOBAL or SESSION syntax, will not survive a restart of the MySQL server; itís completely transient in that regard. Runtime changes like this are excellent for testing scenarios such as, ďIf I increase my key_buffer value, will it improve my query performance?Ē Once youíve found a value that works for you, though, remember to go back to your /etc/my.cnf file and put that value into your configuration file, or you may find yourself wonder ing weeks or months later why performance was so horrible after that reboot, com pletely forgetting the variable change you made on the fly months prior.

Itís also possible to use arguments on the mysqld_safe command line to override val ues defined in the configuration files. For example, you might do something like the following:

$ mysqld_safe -O key_buffer=50M

Like the earlier set-variable syntax, the -O syntax is deprecated as of Version 4.0. Here is a better way to issue that command:

$ mysqld_safe --key_buffer=50M

Command-line argument changes made in the mysql.server startup script will, obvi ously, survive from server restart to server restart, as long as that startup script is used to disable and reenable the server. Itís important to point out, though, that itís usually better to have all your configuration declarations in a single place, so that maintenance doesnít become a game of hide-and-seek with the configuration options, trying to remember where you set which values. 

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: