Setting MySQL Configuration Options


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.

Setting Options for mysqld

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."



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net