In this chapter, we'll follow up on the brief introduction to configuration you received in Chapter 1, “Installing MySQL.” We'll cover the various configuration options, with a special section on internationalization.
We will cover the following:
• Setting MySQL configuration options
• Multi-install configuration options
• Configuring for internationalization
As you will have seen throughout this book, many of the MySQL programs have configuration options. These can be specified on the command line, but in many cases they can also be specified via an options file. A single options file can be used to specify default command-line options for several of the MySQL programs, specifically mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, and myisampack.
The advantage of using an options file is that it allows you to set all your standard options in one place. Under Unix, MySQL also supports the use of separate options files for the whole server and for individual users.
The options file approach is particularly useful if you are managing multiple servers. If the servers have the same configuration, as is often the case when replication is involved, you can simply use the same configuration file across machines.
We first looked at options files in Chapter 1. Let's recap with more detail this time. You can find the options file(s) in the locations detailed next.
Under Windows, you have a choice of putting the options file in your Windows directory and calling it my.ini or putting it in the root directory of the drive that the server is on (for example, C:) and calling it my.cnf. This is a global options file—that is, these options will be applied to all users on the server.
Under Unix, you can have a global options file, an options file for each MySQL server on the machine, and an options file for each user. (You can run more than one MySQL server per physical machine, as we will discuss later in this chapter.)
The global options file is located in /etc/my.cnf. Per-server files are in the data directory for each server, and per-user files are in the home directory of each user. Note that per-user files are prefixed with a dot—that is, .my.cnf instead of my.cnf.
Let's look back at the options file we began with in Chapter 1 as an example of the syntax of these files. It is repeated here for your reference in Listing 12.1.
Listing 12.1. Sample my.cnf File
[mysqld]
# turn on binary logging and slow query logging
log-bin
log-slow-queries
# InnoDB config
# This is the basic config as suggested in the manual
# Datafile(s) must be able to
# hold your data and indexes.
# Make sure you have enough
# free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
# Set buffer pool size to
# 50 - 80 % of your computer's
# memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
# Set the log file size to about
# 25 % of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
# Set ..flush_log_at_trx_commit
# to 0 if you can afford losing
# some last transactions
innodb_flush_log_at_trx_commit=1
Let's discuss the format of this file.
The first line in this file is
[mysqld]
This means that the options specified following this line are options for mysqld. If we want to specify options for other programs, we must specify the program at the start of the options. You just need to list the name of the program in square brackets.
As well as specifying options for individual programs, you can specify options for [client], which sets options for all client programs.
Lines beginning with # are comments.
There are three forms of syntax for setting individual options:
• Specifying the option you want switched on; for example:
log-bin
This is equivalent to specifying mysqld --log-bin.
• Specifying the option you want with a value; for example:
innodb_flush_log_at_trx_commit=1
• Specifying the option you want with a value using the set-variable syntax; for example:
set-variable = innodb_log_buffer_size=8M
This third syntax is deprecated, but we include it for completeness. You will also note that some sample files use this syntax, so it's important to understand what it means. For instance, the examples in the sample my.cnf file that use it are taken from the simple InnoDB configuration given in the MySQL manual.
Certain options that have to do with how options files are used will work for all these programs:
• --no-defaults means that no options files are to be read.
• --print-defaults will tell you what the values of all the options are being set to for this program.
• --defaults-file=/path/to/file will tell the program to use the specified file instead of any other options files it has. This is useful for testing configuration changes.
• --defaults-extra-file=/path/to/file will read the specified file after reading the global options file but before reading any individual user options files.
Most of the programs you can configure using an options file have their options discussed elsewhere in this book. The exception is mysqld. We will now take an overview of the more important and useful command-line options for the MySQL server.
This list of options is not comprehensive, but it aims to give you a guide to the more frequently used options for mysqld. Each of these options can be set via the command line when starting mysqld or, as we have been discussing, through an options file.
You can obtain a complete list (warning: it's long) by opening a command prompt and typing
mysqld --help
Here are some useful options:
• ansi: Run the server in ANSI compatibility mode. This makes MySQL use ANSI-99 SQL.
• basedir: Set the base directory of your installation if you want to put it in a nonstandard location.
• datadir: The same thing as basedir, but for the data directory.
• log-bin: Turn on binary logging. You can specify a filename for the location of the log.
• log-error: Turn on error logging. Again, you can specify the location of the log.
• log-slow-queries: Turn on slow query logging.
• port: Specify the port that the server should listen on. The default is 3306.
• user: Specify the user that the MySQL server should run as.
We will look at a few other options through the course of this chapter and some others in Chapter 17, “Optimizing Your MySQL Server Configuration.”
In the sample options file we looked at, we set some options relating to InnoDB. You can use InnoDB without setting any of these options, but you should set them for better performance. These are the options we set previously:
• innodb_data_file_path = ibdata1:10M:autoextend
This option tells MySQL where to store InnoDB data. Unlike MyISAM tables, in which each table gets its own file, InnoDB tables are stored in a shared tablespace, which may consist of one or more files. This particular example tells MySQL to store all the InnoDB data in a single file called ibdata1, to set the initial file size to 10MB, and to automatically make it bigger (8MB at a time) if the tablespace becomes full.
The general format of this option is
filename:filesize[;filename:filesize;...][:autoextend[:max:size]]
The autoextend option allows the tablespace to grow. The max option allows you to set a maximum size to which it can grow.
• innodb_buffer_pool_size=70M
This option sets the size of the buffer used to cache InnoDB table data and indexes. As with any cache, the bigger it is, the less disk I/O you will have. How much you put into the buffer pool will depend on whether there are other applications and users on the server and how much memory you have.
• innodb_additional_mem_pool_size=10M
This option sets aside memory to store internal MySQL data structures. If MySQL is running out of room here, it will begin writing warnings to the error log.
This option sets the size of each log file. InnoDB rotates between n log files—where n is the value set in the innodb_log_files_in_group option, which defaults to 2, the recommended value.
• innodb_log_buffer_size=8M
This option sets the size of the buffer in which logs are stored before they are written to disk.
• innodb_flush_log_at_trx_commit=1
Setting this option to 1 means that every time a transaction is committed the log will be flushed to disk. This is the normal behavior. If it is set to zero, the log will be written to and flushed to disk only roughly once per second. If it is set to 2, the log will be written to with each commit, but flushed only once per second. Values of 0 or 2 will improve performance, but are obviously a fairly risky proposition.
There are various other InnoDB configuration options. See the MySQL manual for details.
It is often useful to be able to run multiple MySQL servers on the same machine. For example, different ISP users may have their own installation. We use this feature to allow students learning about MySQL to each set up and configure their own server.
For each server you start, you must set different values for each of the following options to mysqld:
• port: Each server must listen on a different port.
• socket: Under Unix, each server must use a different socket file. Under Windows, the socket option sets the name of the named pipe used by the server. In both cases, this value for socket must be different for each server.
• shared-memory-base-name (Windows only): Each server must use a different piece of shared memory.
• pid-file (Unix only): Each server needs a different file in which to write its process id (pid).
• Logging options: If you set any of the log file options, such as log-bin, you will need to set up different log file locations for each server.
An easy way to get some of these is to set the basedir option differently for each server. This will force the data directories and log files to be different. We strongly recommend that each server has its own separate data directory to avoid all sorts of unpleasantness.
The --defaults-file option is really useful for starting each server instance with a different set of defaults (or for installing each server as a Windows service with a separate set of defaults).
One point to note is that if you are running multiple servers, you will need to specify to client programs and other programs that connect to a server, such as mysqladmin, which server you want to connect to. You can use the --port command switch to these programs to do this. In this situation, it can be very useful to set up my.conf files on a per-user basis with the client port set appropriately so that users automatically connect to their own servers.
Two options to mysqld allow you to set the default character set and collation. The character set is the set of symbols used by default on the server. The collation is the set of rules for performing comparisons—that is, the sort order—on the character set, which varies from country to country.
You can set the default character set with the --default-character-set option. Each character set has an associated default collation, but you can specify another one with the --default-collation option. If the combination of default character set and default collation is not valid, mysqld will give you an error message.
The default character set, if these options are not specified, is latin1, and the default collation is latin1_swedish_ci. This character set can also be described as ISO-8859-1 West European, which is the one used in this book. The collation represents the sort order for latin1 used by the Swedes and Finns. (If you are a monolinugal English speaker, you may not know that different groups who use the same character set you use sort vowels into different orders.) There are also collations for latin1 that represent the ways that Germans, Danes, and Norwegians sort strings.
For more information on the character sets supported by MySQL, especially if you are looking for one in particular, consult the MySQL manual.
• The MySQL programs mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, and myisampack all support the use of a common options file.
• You can set options on a global basis, on a server basis, on a user-by-user basis, or all by of these.
• The syntax of the file begins with the name of the program in square brackets, followed by options for that program. Comments start with #. Each option may be expressed as option, option=value, or set-variable option=value.
• You can obtain a complete list of options to mysqld with mysqld --help.
• You can run multiple MySQL servers on the same physical machine as long as options are set to avoid clashes. You must set up different ports, sockets, and log files for each server.
• You can use the options file to set the default character set and collation for the server. The character set is the set of symbols used. The collation is the sort order.
In the next chapter, “Administering Your Database,” we will discuss the day-to-day maintenance tasks you will need to perform on your MySQL database.