|
MySQL Configuration Files |
|
|
|
Page 1 of 4 MySQL Configuration Files Configuring a MySQL server is often just a matter of editing the configuration file to make any changes you need and then restarting the server. While that sounds rather simple, adjusting the server's configuration is something you're not likely to do on a daily basis. More likely, you've installed MySQL, configured it minimally or with the defaults, and then let it run. Most users never go back and adjust the server configuration until a problem arises. As a result, it's easy to forget how to configure MySQL.
Another possibility is that you didn't even know there was a configuration file for MySQL. For the majority of projects, MySQL's default configuration is more than sufficient on modern hardware. It may not be as fast as it can be (because you haven't optimized it), but it will certainly meet your basic needs.
1.2.1 File LocationsWhen MySQL starts, it reads its configuration files in a particular order, unless told otherwise. On Unix, the order is:
/etc/my.cnf
datadir/my.cnf
~/.my.cnf
On Windows, the order:
%SystemRoot%/my.ini
C:\my.cnf
Three command-line arguments affect how MySQL reads its configuration files:
--no-defaults
Tells MySQL not to read any configuration files.
--defaults-file=/path/to/file
Tells MySQL to read this file only, and any other files explicitly declared with --defaults-extra-file.
--defaults-extra-file=/path/to/file
Tells MySQL to read this file after reading the /etc/my.cnf global configuration file .
Files read later in the process override those set in previously read files. If both /etc/my.cnf and datadir/my.cnf specify a value for the TCP port that MySQL should listen to, the latter takes precedence.
This behavior can be quite helpful when you need to run multiple servers either on the same host or on several different hosts. You can give all servers an identical copy of /etc/my.cnf that specifies all the values that aren't specific to a single host. With that out of the way, the few host-specific settings can be maintained in a small supplemental file such as datadir/my.cnf.
A similar strategy works if you'd like to run multiple servers on a single host. By putting all the common settings in /etc/my.cnf and the server-specific settings in each datadir/my.cnf, it's easy to keep several servers running with a minimum of effort.
For example, perhaps you want to run a couple different instances of the MySQL server, one for each character set you plan to use (to make your life easier). You might put all your "common" settings in /etc/my.cnf and the following in /etc/my.english.cnf:
default-character-set=latin1
port=3306
socket=/var/lib/mysql/english.sock
Your /etc/my.german.cnf file has:
default-character-set=latin1_de
port=3307
socket=/var/lib/mysql/german.sock
You might even have /etc/my.korean.cnf with:
default-character-set=euc_kr
port=3308
socket=/var/lib/mysql/korean.sock
Now, when you start up the three servers, you want each to load all the settings from the shared /etc/my.cnf file, and then get settings from one of each of the previous language-based configuration files. You can use a command like the following:
$ mysqld_safe --defaults-extra-file=/etc/my.german.cnf
$ mysqld_safe --defaults-extra-file=/etc/my.english.cnf
$ mysqld_safe --defaults-extra-file=/etc/my.korean.cnf
This command yields three different mysqld instances, running on ports 3306 through 3308, each using the language-specific configuration options mentioned in the file indicated by the defaults-extra-file switch.
MySQL is usually installed as a service on Windows. As a result, Windows users must call c:\mysql\bin\mysqld directly to pass command-line arguments.
|
Linix - How to 