Configuring the MySQL Server

Like most server software in Fedora and RHEL, the MySQL server relies on a start-up script and a configuration file to provide the service. Server activities are logged to the mysqld.log file in the /var/log directory. There are also mysql user and group accounts for managing MySQL activities. The following sections describe how these components all work together.

Tip 

For many of the steps described in this section, the MySQL server daemon must be running. Starting the server is described in detail later in this chapter. For the moment, you can start the server temporarily (as root,type service mysqld start ). Then add a password, as shown in the next section.

Using mysql User/Group Accounts

When the MySQL software is installed, it automatically creates a mysql user account and a mysql group account. These user and group accounts are assigned to MySQL files and activities. In this way, someone can manage the MySQL server without needing to have root permission.

The mysql user entry appears in the /etc/password file as follows :

 mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash 

The mysql entry just shown indicates that both the UID and GID for the mysql user are 27. The text string identifying this user account is MySQL Server . The home directory is /var/lib/mysql and the default shell is /bin/bash . The home directory identified will contain directories that hold each table of data you define for the MySQL server.

The group entry for mysql is even simpler. The following entry in the /etc/group file indicates that the mysql group has a group ID (GID) of 27.

 mysql:x:27: 

If you care to check the ownership of files associated with MySQL, you will see that most of these files have mysql assigned as the user account and group account that own each file. This allows daemon processes that are run by the mysql user to access the database files.

Adding Administrative Users

To administer MySQL, you need to have at least one administrative account. By default, the root user has full access to your MySQL server database and no password assigned. You can assign a password to the root user using the mysqladmin command. To add the root user as a MySQL administrator, log in as the root user and type the following from a Terminal window (substituting your own password in place of myownpasswd ):

 #  mysqladmin -u root password  myownpasswd   

After this command is run, the root user can run any MySQL administrative commands using the password.

If you happen to be logged in as another user when you want to use administrative privilege for a MySQL command, you can do that without re-logging in. Simply add the -u root argument to the command line of the MySQL command you are running. In other words, the Linux root user account has no connection to the MySQL root user account after the MySQL account is created. You would typically use different passwords for the two accounts.

Tip 

To save yourself the trouble of typing in the password each time you run a MySQL client command, you can add a password option under the [client] group in one of the option files. The most secure way to do that is to create a .my.cnf file in the root user's home directory that can only be accessed by root ( chmod 600 /root/.my.cnf ) and contains the following lines (substituting your password for the last argument shown):

 [client] password=  myownpasswd  

Setting MySQL Options

You can set options that affect how the MySQL applications behave by using options files or command-line arguments. The MySQL server (as well as other administrative tools) reads the following options files when it starts up (if those files exist):

• /etc/my.cnf - Contains global options read by mysqld (server daemon) and mysql.server (script to start the server daemon).

• -defaults-extra-file - You can identify a file on the command line that contains options to be used by the server. For example, the following command would cause the file /home/jim/my.cnf to be read for options after the global options and before the user-specific options:

   #  mysqld --defaults-extra-file=/home/jim/my.cnf  

• $HOME/.my.cnf - Contains user-specific options. (The $HOME refers to the user's home directory, such as /home/susyq .)

Table 24-1 shows the MySQL commands that read the options files (in the order shown in the previous bullet list) and use those options in their processing. Options are contained within groups that are identified by single words within brackets. Group names that are read by each command are also shown in the table.

Table 24-1: Option Groups Associated with MySQL Commands
Open table as spreadsheet

Command	Description	Group names
mysqld (in /usr/libexec )	The MySQL server daemon	[mysqld] [server]
mysqld_safe	Run by the mysqld start-up script to start the MySQL server	[mysql] [server] [mysql.server]
mysql	Offers a text-based interface for displaying and working with MySQL databases	[mysql] [client]
mysqladmin	Used to create and maintain MySQL databases	[mysqladmin] [client]
myisamchk	Used to check, fix, and optimize MyISAM databases (.myi suffix)	[myisamchk]
myisampack	Used to compress MyISAM database tables	[myisampack]
mysqldump	Offers a text-based interface for backing up MySQL databases	[mysqldump] [client]
mysqlimport	Loads plain-text data files into MySQL databases	[mysqlimport] [client]
mysqlshow	Shows MySQL databases and tables you select	[mysqlshow] [client]

Note 

If you are using the old ISAM tables instead of MyISAM, support for ISAM tables was removed for MySQL 5.0. You need to convert your tables to MyISAM using a statement such as:

 ALTER TABLE  table  ENGINE=MYISAM 

where table is your table name . Refer to the MySQL Reference Manual ( http://dev.mysql.com/doc/refman/5.0/en/upgrade.html ) for further information on upgrading to MySQL 5.0.

Though you can use any of the options files to set your MySQL options, begin by configuring the /etc/my.cnf file. Later, if you want to override any of the values set in that file you can do so using the other options files or command-line arguments.

Creating the my.cnf Configuration File

Global options that affect how the MySQL server and related client programs run are defined in the /etc/my.cnf file. The default my.cnf file contains only a few settings needed to get a small MySQL configuration going. The following is an example of the /etc/my.cnf file that comes with MySQL:

 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 [mysql.server] user=mysql basedir =/var/lib [mysql_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

Most of the settings in the default my.cnf file define the locations of files and directories needed by the mysqld server. Each option is associated with a particular group, with each group identified by a name in square brackets. The previous options are associated with the mysqld daemon ( [mysqld] ), the MySQL server ( [mysql.server] ), and the safe_mysqld script that starts the mysqld daemon ( [mysql_safe] ). (See Table 24-1 for a list of these groups.)

The default datadir value indicates that /var/lib/mysql is the directory that stores the MySQL databases you create. The socket option identifies /var/lib/mysql/mysql.sock as the socket that is used to create the MySQL communications end-point associated with the mysqld server. The basedir option identifies /var/lib as the base directory in which the mysql software is installed. The user option identifies mysql as the user account that has permission to do administration of the MySQL service. Based on the old_passwords=1 value, your MySQL database will use the password format use in previous MySQL 3.x databases.

The err-log and pid-file options tell the safe_mysqld script the locations of the error log ( /var/log/mysqld.log ) and the file that stores the process ID of the mysqld daemon when it is running ( /var/run/mysqld/mysqld.pid ). The safe_mysqld script actually starts the mysqld daemon from the mysqld start-up script.

Note 

Each option that follows a group name is assigned to that group. Group assignments end when a new group begins or when the end of file is reached.

Choosing Options

There are many values that are used by the MySQL server that are not explicitly defined in the my.cnf file. The easiest way to see which options are available for MySQL server and clients is to run each command with the --help option. For example, to view the available mysqld options (as well as other information) type the following from a Terminal window:

 #  /usr/libexec/mysqld --verbose --help  less  

Then press the Spacebar to step through the information one screen at a time. (An example of this output is shown in the next section.)

Another way to find which options are available is with the man command. For example, to see which options are available to set for the mysqld daemon, type the following:

  man mysqld  

It's quite likely that you can try out your MySQL database server without changing any options at all. However, after you set up your MySQL database server in a production environment, you will almost surely want to tune the server to match the way the server is used. For example, if it is a dedicated MySQL server, you will want to allow MySQL to consume more of the system resources than it would by default.

The following list shows a few examples of additional options that you might want to set for MySQL:

• password = yourpwd - Adding this option to a [client] group in a user's $HOME/.my.cnf file allows the user to run MySQL client commands without having to enter a password each time. (Replace yourpwd with the user's password.)

• port = # - Defines the port number to which the MySQL service listens for MySQL requests . (Replace # with the port number you want to use.) By default, MySQL listens to port number 3306 on TCP and UDP protocols.

• safe-mode - Tells the server to skip some optimization steps when the server starts.

• tmpdir = path - Identifies a directory, other than the default /tmp , for MySQL to use for writing temporary files. (Substitute a full path name for path .)

In addition to the options you can set, MySQL clients also have a lot of variables that you can set. Variables set such things as buffer sizes, timeout values, and acceptable packet lengths. These variables are also listed on the --help output. To change a variable value, you can use the -- set-variable option, followed by the variable name and value. For example, to set the sort_buffer variable to 10MB, you could add the following option under your [mysqld] group:

 [mysqld] set-variable = sort_buffer=10M 

The following list identifies other variables you could set for your server. In general, raising the values of these variables improves performance, but also consumes more system resources. So you need to be careful raising these values on machines that are not dedicated to MySQL or that have limited memory resources.

Note 

For variables that require you to enter a size , indicate Megabytes using an M (for example, 10M) or Kilobytes using a K (for example, 256K).

• key_buffer_size = size - Sets the buffer size that is used for holding index blocks that are used by all threads. This is a key value to raise to improve MySQL performance.

• max_allowed_packet = size - Limits the maximum size of a single packet. Raise this limit if you require processing of very large columns .

• myisam_sort_buffer_size = size - Sets the buffer size used for sorting while repairing an index, creating an index, or altering a table.

• sort_buffer = size - Defines how much buffer size is allocated for each thread that needs to do a sort . Raising this value makes sorting threads go faster.

• table_cache = # - Limits the total number of tables that can be open at the same time for all threads. The number of this variable represents the total number of file descriptors that MySQL can have open at the same time.

• thread_cache_size = size - Sets the number of threads that are kept in cache, awaiting use by MySQL. When a thread is done being used, it is placed back in the cache. If all the threads are used, new threads must be created to service requests.

Checking Options

In addition to seeing how options and variables are set in the options files, you can also view how all variables are set on your current system. You can view both the defaults and the current values being used by the MySQL server.

The --help command-line argument lets you see the options and variables as they are set for the server and for each MySQL client. Here is an example of the output showing this information for the mysqld server daemon:

 #  /usr/libexec/mysqld --verbose --help  less  . . . Variables (--variable-name=value) and boolean options {FALSETRUE} Value (after reading options) -------------------------------- ----------------------------- help TRUE abort-slave-event-count 0 allow-suspicious-udfs FALSE auto-increment-increment 1 automatic-sp-privileges TRUE basedir: /usr/ . . . tmp_table_size 33554432 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 updatable_views_with_limit 1 wait_timeout 28800 To see what values a running MySQL server isusing, type 'mysqladmin variables' instead of 'mysqld --verbose --help 

After the server is started, you can see the values that are actually in use by running the mysqladmin command with the variables option. (Pipe the output to the less command so you can page through the information.) Here is an example (if you haven't stored your password, you will be prompted to enter your password before you see any output):

 #  mysqladmin -u root -p variables  less   +-------------------------+------------------------------------------   Variable_name  Value   +-------------------------+-------------------------------------------   back_log  50   basedir  /usr/   bdb_cache_size  8388600   bdb_log_buffer_size  32768   bdb_home  /var/lib/mysql/   bdb_max_lock  10000   bdb_logdir   bdb_shared_data  OFF   bdb_tmpdir  /tmp/   .   .   .   tmp_table_size  33554432   tmpdir  /tmp/   version  3.23.58   wait_timeout  28800   +-----------------------+---------------------------------------+  

If you decide that the option and variable settings that come with the default MySQL system don't exactly suit you, you don't have to start from scratch. Sample my.cnf files that come with the mysql package let you begin with a set of options and variables that are closer to the ones you need.

Using Sample my.cnf Files

Sample my.cnf files are available in the /usr/share/doc/mysql-server* directory. To use one of these files, do the following:

1. Keep a copy of the old my.cnf file:

    #  mv /etc/my.cnf /etc/my.cnf.old  

2. Copy the sample my.cnf file you want to the /etc/my.cnf file. For example, to use the my-medium.cnf file, type the following:

    #  cp /usr/share/doc/mysql-server*/my-medium.cnf /etc/my.cnf  

3. Edit the new /etc/my.cnf file (as root user) using any text editor to further tune your MySQL variables and options.

The following bullets describe each of the sample my.cnf files.

• my-small.cnf - This options file is recommended for computer systems that have less than 64MB of memory and are only used occasionally for MySQL. With this options file, MySQL won't be able to handle a lot of usage but it won't be a drag on the performance of your computer.

  For the mysqld server, buffer sizes are set low - only 64K for the sort_buffer and 16K for the key_buffer . The thread_stack is only set to 64K and net_buffer_length is only 2K. The table_cache is set to 4.

• my-medium.cnf - As with the small options file, the my-medium.cnf file is intended for systems where MySQL is not the only important application running. This system also has a small amount of total memory available - between 32MB and 64MB - however more consistent MySQL use is expected.

  The key_buffer size is set to 16M in this file, while the sort_buffer value is raised to 512K for the mysqld server. The table_cache is set to 64 (allowing more simultaneous threads to be active). The net_buffer_length is raised to 8K.

• my-large.cnf - The my-large.cnf sample file is intended for computers that are dedicated primarily to MySQL service. It assumes about 512M of available memory.

  Server buffers allow more active threads and better sorting performance. Half of the system's assumed 512M of memory is assigned to the key_buffer variable (256M). The sort_buffer size is raised to 1M. The table_cache allows more simultaneous users (up to 256 active threads).

• my-huge.cnf - As with the my-large.cnf file, the my-huge.cnf file expects the computer to be used primarily for MySQL. However, the system for which it is intended offers much more total memory (between 1G and 2G of memory).

  Sort buffer size ( sort_buffer ) is raised to 2M while the key_buffer is set to consume 384M of memory. The table_cache size is doubled to allow up to 512 active threads.

• my-innodb-heavy-4G.cnf - This sample configuration file is best suited for computers with 4GB of RAM that are expected to service complex queries without too many connections (using the InnoDB transaction storage engine). Special innodb options in this file allow MySQL to take advantage of a large buffer pool size (2G).