24.4. Runtime MySQL Configuration


By default, the server uses built-in values for its configuration variables when it runs. If the default values aren't suitable, you can use runtime options to tell the server to use different values:

  • Several options specify the locations of important directories and files. For example, under Windows, the built-in default value for the installation directory (base directory) is C:\mysql. If you install MySQL somewhere else, you must tell the server the correct location by using the --basedir option or the server will not start. Similarly, if you use a data directory other than the directory named data under the installation directory, you must use a --datadir option to tell the server the correct location.

  • Options control which log files the server writes.

  • Options can be used to override the server's built-in values for performance-related variables, such as those that control the maximum number of simultaneous connections, and the sizes of buffers and caches.

  • Some storage engines that are built in can be enabled or disabled at server startup. For example, if the server has been compiled with InnoDB support (which is true by default), the --skip-innodb option can be given to save memory if you are not using InnoDB tables. You can also specify that the default storage engine should be different from the built-in default of MyISAM.

  • Several options configure the InnoDB tablespace. If InnoDB is enabled, it creates a default tablespace in the absence of explicit configuration. However, the default tablespace is rather small, so it's better to configure the tablespace yourself.

The examples in this section concentrate on options that relate to general directory and file layout. Section 29.4, "The InnoDB Engine," covers InnoDB-specific configuration. Chapter 39, "Optimizing the Server," concentrates on using performance-related options to tune the server to run more efficiently.

You can specify runtime options when you start the server to change its configuration and behavior. In general, options can be given either on the command line or in option files. (The exception is that if you run the server as a Windows service, you cannot specify options on the command line. You must use a --defaults-file option, as described in Section 24.2.3, "Running MySQL Server as a Windows Service." Keep this in mind for the following discussion.)

For general background on option file syntax, see Section 1.2.3, "Using Option Files." That discussion occurs in the context of running client programs, but the bulk of it also applies to specifying server options.

To find out what options the server supports, invoke it manually as follows:

 shell> mysqld --verbose --help 

Any of the server options shown in the help message may be specified on the command line. However, it's more typical to list them in an option file, for several reasons:

  • By putting options in a file, you need not specify them on the command line each time you start the server. This is not only more convenient, it's less error-prone for complex options such as those used to configure the InnoDB tablespace.

  • If you invoke the server using the mysql.server startup script, you cannot specify server options on the command line of the script. It understands arguments of start or stop only, which makes use of an option file mandatory.

  • If you list all server options in a single option file, you can look at this file to see immediately how you've configured the server to run.

The server looks for option files in several standard locations. It uses any that exist, but it is not an error for an option file to be missing. The standard files are different for Windows and Unix.

On Windows, programs look for option files in the following order: my.ini and my.cnf in the Windows directory (for example, the C:\Windows or C:\WinNT directory), and then C:\my.ini and C:\my.cnf.

Note: If you used the Configuration Wizard to install the server as a Windows service, the server does not look in the standard option file locations. Instead, it looks for options only in the my.ini file in the MySQL installation directory. Similarly, if you installed the server as a service yourself and specified a --defaults-file option to name an option file, the server looks for options only in that file. In either case, to make any option changes, you must make them in the single file that the server reads.

On Unix, the search order includes two general option files, /etc/my.cnf and $MYSQL_HOME/my.cnf. The second file is used only if the MYSQL_HOME environment variable is set. Typically, you set it to the MySQL installation directory. (The mysqld_safe script attempts to set MYSQL_HOME if it is not set before starting the server.)

The Unix option file search order also includes ~/.my.cnf, that is, the .my.cnf file located in the home directory of the person running the program. However, because ~/.my.cnf is a user-specific file, it isn't an especially suitable location for server options. (Normally, you invoke the server as mysql, or as root with a --user=mysql option. The user-specific file read by the server would depend on which login account you invoke it from, possibly leading to inconsistent sets of options being used.)

To specify server startup options in an option file, use the [mysqld] option group. If the file does not exist, create it as a plain text file using an editor. To create or modify an option file, you must have write permission for it. The server itself needs only read access; it reads option files but does not create or modify them.

The following examples illustrate some ways to use option files to specify server options:

  • If you install MySQL on Windows, the server assumes by default that the installation directory is C:\mysql and the data directory is named data in the installation directory. If you install MySQL somewhere else, such as E:\mysql, you must tell the server that location with a --basedir option. Options in option files are given without the leading dashes, so to indicate the installation directory, specify the option as follows:

     [mysqld] basedir=E:/mysql 

    If you use the data directory under E:\mysql as the data directory, the basedir value is sufficient for telling the server the data directory location as well. If you use a different data directory location, you must also specify a --datadir option:

     [mysqld] basedir=E:/mysql datadir=D:/mysql-data 

    Note that in this case you'll also need to copy the data directory from under the installation directory to the new location of D:\mysql-data before starting the server. If the server does not find the data directory in the location that you specify in the option file, it will not start up.

  • For any option that specifies a Windows pathname, write any backslashes in the name as slashes or as doubled backslashes. For example, to specify a basedir value of E:\mysql, you can write it using either of the following formats:

     basedir=E:/mysql basedir=E:\\mysql 

  • If a pathname contains spaces, quote it. For example:

     basedir="C:/Program Files/MySQL/MySQL Server 5.0" 

  • On Windows, shared-memory connections are not enabled by default, as discussed in Section 23.2, "Communication Protocols." To use this capability, use the following option:

     [mysqld] shared-memory 

    Similarly, the mysqld-nt and mysql-max-nt servers are capable of supporting named-pipe connections but do not enable them by default. To turn on named-pipe support, use this option:

     [mysqld] enable-named-pipe 

  • To enable logging, use the options that turn on the types of logs you want. The following options turn on the general query log, the binary log, and the slow query log:

     [mysqld] log log-bin log-slow-queries 

    Section 24.5, "Log and Status Files," further discusses the contents of these logs.

  • To specify a default storage engine different from MyISAM, use the --default-storage-engine option:

     [mysqld] default-storage-engine=InnoDB 

  • Option files also can be used to set many server system variable values. For example, to increase the maximum allowed number of client connections from the default of 100 to 200, and to increase the size of the MyISAM key cache from the default of 8MB to 128MB, set the max_connections and key_buffer_size variables, respectively:

     [mysqld] max_connections=200 key_buffer_size=128M 

MySQL distributions contain several sample option files. On Windows, they have names like my-small.ini and my-large.ini and are located in the MySQL installation directory. On Unix, they have names like my-small.cnf and my-large.cnf. Likely locations are in /usr/share/mysql for RPM installations or the share directory under the MySQL installation directory for tar file installations.

You can use a sample option file by copying it to one of the standard option file locations. However, before doing this, be sure to read the file and make sure that you understand the effect that its settings will have on server operation. For example, the file might contain settings that enable certain log files or that change the size of memory buffers.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

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