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:
Certain options that have to do with how options files are used will work for all these programs:
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. Setting Options for mysqldThis 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:
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." |