Using an Options File - Advanced Topics - Learning MySQL (2007)

Learning MySQL (2007)

Part III. Advanced Topics

Chapter 11. Using an Options File

Over the course of this book, you’ve seen that you can pass options to many of the programs and scripts that are part of the MySQL distribution. For example, you can pass the user and password options to the MySQL monitor. If you don’t specify a value for an option, the default options are used. For example, most client programs try to use the default values localhost and 3306 for the server host and port options, respectively.

If you need to use an option value that’s not the default, you have to specify it each time you run a program that needs that option; this is tedious and prone to errors. Fortunately, you can save option values to an options file, also sometimes called a configuration file, that most of the key MySQL programs and scripts can read. The programs that read options files include: myisamchk, myisampack, mysql, mysqladmin, mysqlbinlog, mysqlcc, mysqlcheck, mysqld, mysqld_safe, mysqldump, mysqlhotcopy, mysqlimport, mysql.server, and mysqlshow.

We’ll start our tour of options files with an example using the MySQL monitor.

Configuring Options for the MySQL Monitor

Throughout this book, you’ve specified the user and password options when starting the monitor program:

$ mysql --user=root --password=the_mysql_root_password

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 486 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can save yourself some typing by storing the username and password in an options file and placing it in a location where the monitor will look. The monitor will automatically read in the option values from the file instead of asking you.

In the options file, we specify the program that we’re interested in—here, it’s mysql for the MySQL monitor—and then list each option on a line of its own:

[mysql]

user=root

password=the_mysql_root_password

If you’re using a Linux or Mac OS X system, type these lines using a text editor and save it with the name .my.cnf in your home directory (~/.my.cnf). Under Windows, save this file with the name my.cnf in the root of the C: drive (C:\my.cnf). You can now start the monitor without providing the username and password options; the values are read in automatically from the options file:

$ mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 486 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

This is very convenient! Unfortunately, we now have to spoil the fun and note that it’s generally not a good idea to store passwords unencrypted (in plain-text); at the very least, you should ensure that only you can read (and write) the file. On a Linux or Mac OS X server, you can use thechmod command to do this:

$ chmod u=rw,g=,o= ~/.my.cnf

We discuss permission settings in Restricting access to files and directories” in Chapter 2. The trade-off between convenience and security is a recurring theme in discussions of protection of systems and data. You need to assess the requirements of each individual application.

Let’s look at another example. Say you want to use the MySQL monitor to connect to a MySQL server running on port 57777 of the host sadri.learningmysql.com, and wish to use the music database on this server. For this database, we have the MySQL account name allmusic and the password the_password. The command to start the monitor would be (all on one line):

$ mysql \

--host=sadri.learningmysql.com \

--port=57777 \

--user=allmusic \

--password=the_password \

--database=music

This can be tiresome to type all the time, so you could save these values in the options file as:

[mysql]

host=sadri.learningmysql.com

port=57777

user=allmusic

password=the_password

database=music

If you’re concerned about security, you can omit specifying a password and simply write the option password:

[mysql]

host=sadri.learningmysql.com

port=57777

user=allmusic

password

database=music

This way, the monitor knows that you want to use a password, and it’ll prompt you for the password before trying to connect to the server.

Structure of the Options File

We saw in the last section how you can specify options for the MySQL monitor. An options file can have a section for each program that uses it. For example, you can have a [mysql] section for the mysql program and a [mysqldump] section for the mysqldump program. Similarly, you can have a [mysqld] section for the MySQL server daemons mysqld, mysqld_safe, and mysqld-nt.

Where options are common to all client programs, they can be consolidated under a [client] section. Similarly, options common to all server programs can be listed under a [server] section.

Be careful not to make program options too generic. For example, the mysql program is a client and takes a database option. However, mysqladmin and mysqlshow are examples of client programs that don’t understand this option. If you include the database option in the [client]section, like this:

[client]

database=music

these programs will just complain and quit, as below:

$ mysqladmin status

mysqladmin: unknown variable 'database=music'

You should include the database option in a separate group for the [mysql] program, rather than including it in the [client] group.

Let’s look at a more interesting options file:

[server]

user=mysql

port=57777

basedir=/usr/local/mysql-standard-5.0.22-linux-i686

socket=/home/mysql/server1.sock

datadir=/home/mysql/data

tmpdir=/home/mysql/tmp

pid_file=/home/mysql/logs/server1.pid

# log server messages to:

log=/home/mysql/logs/server1.main.log

# log errors to this

log_error=/home/mysql/logs/server1.error.log

# log updates to this binary logfile

log_bin=/home/mysql/logs/server1_updates.bin

[client]

socket=/home/mysql/server1.sock

[mysql]

database=mysql

[mysqldump]

all-databases

result_file=/tmp/dump.sql

There are four groups here: one for the server, one for all clients, one for the mysql program, and one for the mysqldump program. The latter two are both clients, but the options we want to list in the file aren’t common to all clients, so we list them separately.

If an option appears for two applicable groups (for example [client] and [mysql]), the more specific setting (here, for [mysql]) takes precedence.

Lines starting with the hash or pound symbol (#) are ignored; this allows you to add comments to the configuration file to explain entries. Blank lines are also ignored.

Scope of Options

The directives in an options file can apply at different levels depending on where the options file is located:

System-wide

Settings apply for all MySQL programs on the system.

The default location for a system-wide options file is /etc/my.cnf for Linux or Mac OS X systems. Under Linux and Mac OS X, the MySQL server and client programs automatically read in an options file at the default location.

For a Windows system, the possible configuration file paths are <Windows_Directory>\my.ini, <Windows_Directory>\my.cnf, C:\my.ini, and C:\my.cnf. The <Windows_Directory> is the directory Windows is installed in, typically C:\Windows. Under Windows, current versions of the MySQL server (version 4.1.5 and above) don’t actually read in an options file by default, so you should always specify one as we discuss in the section on server-wide options. Note that Windows client programs do read in any existing options files.

Server-specific

Settings apply for the MySQL programs in a particular installation.

The default location for a server-specific options file is <MySQL_directory>/my.cnf for Linux and Mac OS X, and <MySQL_directory>\my.ini for Windows.

The options file is sometimes placed in the data directory, but this is not recommended for two reasons: first, it won’t work if you don’t use the default location of data directory specified when the MySQL installation was compiled. Second, the data directory must be readable by any client programs (and therefore by other users on the system) that need to see the options file. It’s better that access to the data directory be limited to only the server, so it’s best to keep the options file elsewhere.

Under Windows, the MySQL installation process places a my.ini options file in the MySQL directory. When MySQL is installed as a Windows service, the location of this options file is also specified; a typical service entry is:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt"

--defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"

If you want to use a different options file location, you’ll need to change the service entry. For example, you could ask your server to read in the options file C:\my.cnf by specifying the service as:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt"

--defaults-file="C:\my.cnf"

If you want to start the server from the command line instead of using the Windows service, you’ll need to specify the path to the options file; you can use the same file or a different one. For example, you could ask your server to read in the options file C:\my.cnf as follows:

C:\> mysqld-nt --defaults-file="C:\my.cnf"

User-specific

Settings apply for the MySQL programs run by a particular user.

The default location of a user-specific options file on a Linux or Mac OS X system is the file .my.cnf located in the user’s home directory—that is, ~/.my.cnf. There is currently no support for user-specific options files under Windows.

Search Order for Options Files

The MySQL server and client programs look for options files in the standard locations and read them in order; values from later files take precedence over earlier ones. Options specified on the command line override values from options files.

You can tell a MySQL program to ignore the default options files by telling it to read a specific file at a location given with the defaults-file option. For example, you can write:

$ mysql --defaults-file=path_to_options_file

If you’d like to use an options file alongside the default files, you can specify it using the defaults-extra-file option:

$ mysql --defaults-extra-file=path_to_local_options_file

Finally, you can prevent programs from reading in any options files by adding the no-defaults option:

$ mysql --no-defaults

On a Linux or Mac OS X system, the search order is /etc/my.cnf, then <MySQL_Directory>/my.cnf, defaults-extra-file, and finally ~/.my.cnf. Note that for security reasons, files that are world-writable are ignored. A generally appropriate permission setting is for the file owner (user) to be able to read and write the file, but for the group and others to be able to only read the file. You can set this level of access by opening a terminal window and typing:

$ chmod u=rw,g=r,o=r configuration_file

Note that if you’re trying to change the permissions of a file owned by the system root user, you’ll need to run the chmod command when logged in as the system root user, or prefix the command with the sudo keyword.

On a Windows system, clients try to access options files in this order: first <Windows_Directory>\my.ini, then <Windows_Directory>\my.cnf, C:\my.ini, C:\my.cnf, <MySQL_directory>\my.ini, <MySQL_directory>\my.cnf and then defaults-extra-file. Again, under Windows, the server doesn’t read in the options file automatically, and you need to tell it to do so using the defaults-file option.

Determining the Options in Effect

It can sometimes be unclear which options are in effect for a given program, particularly if you’ve got several options files with overlapping directives. You can use the print-defaults option to most MySQL programs to see the options in effect. For example, to see the active options formysqldump, you can type:

$ mysqldump --print-defaults

mysqldump would have been started with the following arguments:

--socket=/home/mysql/server1.sock

--all-databases

--result_file=/tmp/dump.sql

--host=localhost

--port=3306

--database=Music

--result_file=/home/saied/dump.sql

You can get a similar effect using the my_print_defaults program and specifying the command groups you’re interested in. For example, to see the settings for all clients and for the mysqldump program, you can type:

$ my_print_defaults client mysqldump

--socket=/home/mysql/server1.sock

--all-databases

--result_file=/tmp/dump.sql

--host=localhost

--port=3306

--database=Music

--result_file=/home/saied/dump.sql

Exercises

1. What issues would you consider before storing your password in an options file?

2. On a Linux or Mac OS X system, under what circumstances would the [server] section of the ~/.my.cnf options file be read?

3. How can you tell what options a program uses by default?