Configuring the MySQL Server

Configuring the MySQL Server

Like most server software in Red Hat Linux, 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.


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 by typing the following (as root user): /etc/init.d/mysqld start

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 the UID and GID for the mysql user is 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.


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 create at least one administrative account. You can do this 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 my47gmc):

# mysqladmin -u root password my47gmc

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 once the MySQL account is created. You would typically use different passwords for the two accounts.


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 contains the following lines (substituting your password for the last argument shown).


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

  • /var/lib/mysql/my.cnf — Contains options primarily for the mysqld 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



Group names

mysqld (in /usr/libexec)

The MySQL server daemon.




Run by the mysql start-up script to start the MySQL server.





Offers a text-based interface for displaying and working with MySQL databases.




Used to create and maintain MySQL databases.




Used to check, fix, and optimize ISAM databases (.ism suffix).



Used to check, fix, and optimize MyISAM databases (.myi suffix).



Used to pack ISAM databases (.ism suffix).



Used to compress MyISAM database tables.



Offers a text-based interface for backing up MySQL databases.




Loads plain-text data files into MySQL databases.




Shows MySQL databases and tables you select.



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:


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 above options are associated with the mysqld daemon ([mysqld]), the MySQL server ([mysql.server]), and the safe_mysqld script that starts the mysqld daemon ([safe_mysqld]). (See Table 24-1 for a list of these clients.)

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.

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/ The safe_mysqld script actually starts the mysqld daemon from the mysqld start-up script.


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 see available mysqld options (as well as other information) type the following from a Terminal window:

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

Then press the Spacebar to step through the information one screen at a time.

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

Below are a few examples of additional options that you may 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:

set-variable = sort_buffer=10M

The following is a list of 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.


For variables that require you to enter a size, indicate Megabytes using an M (e.g. 10M) or Kilobytes using a K (e.g. 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.

  • record_buffer = size — Sets the buffer size used for threads doing sequential scans. Each process doing a sequential scan allocates a buffer of the size set here.

  • 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 — 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 -–help | less
The default values (after parsing the command line arguments) are:

basedir:     /usr/
datadir:     /var/lib/mysql/
tmpdir:      /tmp/
language:    /usr/share/mysql/english/
pid file:    /var/lib/mysql/
TCP port:    3306
Unix socket: /var/lib/mysql/mysql.sock
system locking is not in use
Possible variables for option --set-variable (-O) are:
back_log              current value: 50
bdb_cache_size        current value: 8388600
bdb_log_buffer_size   current value: 0
bdb_max_lock          current value: 10000
bdb_lock_max          current value: 10000
binlog_cache_size     current value: 32768
connect_timeout       current value: 5
table_cache           current value: 64
thread_concurrency    current value: 10
thread_cache_size     current value: 0
tmp_table_size        current value: 33554432
thread_stack          current value: 65536
wait_timeout          current value: 28800

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 it here):

# mysqladmin 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.57                                  |
| 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 can 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 paragraphs describe each of the sample my.cnf files:


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.


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.


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


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.

Part IV: Red Hat Linux Network and Server Setup