Basic Configuration Options
Because MySQL is open source and very flexible in how it runs, there are numerous ways you can configure the software. By installing MySQL from the source files, as I did on Linux, you can establish certain parameters at the time of installation that affect how the software functions. For starters, you can set the location of the installed files using the prefix option (as I did in my Linux example). You can also choose whether or not to install the MySQL server (leaving only the client), and select what language to use. These are just examples; the full listing of options is available by typing ./configure --help at the command line from within the MySQL source code directory (e.g., ~/Desktop/mysql-5.0.18) (Figure 1.20).
Figure 1.20. The MySQL manual details the most common configuration options, while ./configure --help gives the entire list.
Another way you can affect how MySQL runs is to use a configuration file. On Windows, the easiest way to create and edit such a file is to use the MySQL Server Instance configuration wizard utility, introduced earlier in this chapter. This utility will manage the my .ini file, found within the MySQL installation directory.
On any operating system, you can also manually edit a configuration file or even create your own. The installation comes with several configuration samples, which you can tweak. I'll run through how that process would work.
To change MySQL's configuration:
Find a MySQL sample configuration file.
The sample configuration files will be installed when you install MySQL; the only question is where. These should be in the directory where MySQL was placed, but they may turn up somewhere else on Mac OS X and Unix (just do a search in the Terminal to find them).
The configuration files have the names my-huge.cnf, my-large.cnf, my-medium.cnf, and my-small.cnf. Each file represents a sample configuration for an expected server load. I would start with either my-medium.cnf or my-small.cnf.
Make a copy of the file.
You'll want to make a copy just in case you ever want to use the original version again.
Rename the file as my.cnf.
Move the file to the correct destination.
On Windows, this should be either the directory where MySQL was installed (C:\Program Files\MySQL\MySQL Server x.x) or the root of the hard drive (C:\my.cnf).
If you installed on Unix using the source distribution method, you'll want to put the configuration file in /etc.
Open the file in any text editor and edit the file as needed.
The file is pretty easy to understand and contains copious notes (Script 1.1). Note that anything after the number symbol (#) is considered a comment and has no effect.
Script 1.1. This is a sampling of what a basic configuration file looks like.
1 # Example MySQL config file for medium systems.
3 # This is for a system with little memory (32M - 64M) where MySQL plays
4 # an important part, or systems up to 128M where MySQL is used together with
5 # other programs (such as a web server)
7 # You can copy this file to
8 # /etc/my.cnf to set global options,
9 # mysql-data-dir/my.cnf to set server-specific options (in this
10 # installation this directory is /var/mysql) or
11 # ~/.my.cnf to set user-specific options.
13 # In this file, you can use all long options that a program supports.
14 # If you want to know which options a program supports, run the program
15 # with the "--help" option.
17 # The following options will be passed to all MySQL clients
19 # password= your_password
20 #port = 3306 #
21 #socket = /var/mysql/mysql.sock
23 # Here follows entries for some specific programs
25 # The MySQL server
27 port= 3306
28 socket = /var/mysql/mysql.sock
30 key_buffer = 16M
31 max_allowed_packet = 1M
32 table_cache = 64
33 sort_buffer_size = 512K
34 net_buffer_length = 8K
35 read_buffer_size = 256K
36 read_rnd_buffer_size = 512K
37 myisam_sort_buffer_size = 8M
39 # Don't listen on a TCP/IP port at all.
This can be a security enhancement,
40 # if all processes that need to connect
to mysqld run on the same host.
Any changes will take effect the next time you start the MySQL server.
When entering pathnames on Windows in the configuration file, use either forward slashes (/) or two backslashes (\\).
In this section I talk about configuration files with respect to how the MySQL server runs. You can also create configuration files for MySQL's client applications and utilities. Such a file would be named and stored as C:\WINDOWS\my.ini or C:\WINNT\my.ini on Windows and ~/.my.cnf on Mac OS X and Unix, where ~ refers to your home directory.
You can also adjust how MySQL will run by using configuration settings when you start the MySQL server.