Back to Basics - Sample Files
(Page 6 of 10 )
The support-files directory of the MySQL distribution * contains four sample configu ration files:
- my-small.cnf
- my-medium.cnf
- my-large.cnf
- my-huge.cnf
(*Note: These files aren’t included in the Windows distribution of older MySQL releases.)
The names of the files are meant to signify the size of the machine on which the MySQL server will run. Each contains comments describing where the size comes from. For example, my-medium.cnf says:
# This is for a system with little memory (32M - 64M) where MySQL # play s a important part and systems up to 128M very MySQL is used # together wit h other programs (like a web server )
To use a sample file, simply copy it to /etc/my.cnf (or systemdir win.ini on Windows) and making changes as necessary. While none is likely to be ideal for any par ticular setup, each file is a good starting point for setting up a new system. Failure to make adjustments to the sample configuration can lead to worse performance in some cases.
Let’s look at the sample my-medium.cnf file from a newly installed system. Some of the information may not make sense right away (depending on how much experience you have), but the more examples you see, the more you’ll begin to understand them.
The file starts with some helpful comments about the type of system this configura tion is appropriate for and information needed to install it:
# Example mysql config file for medium systems .
#
# This is for a system with little memory (32M - 64M) where MySQL
# play s a important part and systems up to 128M very MySQL is
# used together wit h other programs (like a web server)
#
# You can copy this file t o
# /etc/mf.cnf to set global options ,
# mysql-data-dir/my.cnf to set server-specific options (in thi s
# installation this directory is /usr/local/mysq/var) o r
# ~/.my.cnf to set user-specific options .
#
# One can in this file use all long options that the program supports .
# If you want to know which options a program support, run the progra m
# with --help option .
Next are the options that apply to all the client tools you might run on this host:
# The following options will be passed to all MySQL client s
[client ]
#password = your_passwor d
port = 330 6
socket = /tmp/mysql.soc k
What follows next are the parameters specific to the server. The port and socket options, of course, should agree with what the clients were just told. The remaining settings allow MySQL to allocate more RAM for various caches and buffers as well as enable some basic replication options:
# Here follows entries for some specific programs
> # The MySQL serve r > [mysqld ] > port = 330 6 > socket = /tmp/mysql.soc k > skip-lockin g > set-variable = key_buffer=16 M > set-variable = max_allowed_packet=1 M > set-variable = table_cache=6 4 > set-variable = sort_buffer=512 K > set-variable = net_buffer_length=8 K > set-variable = myisam_sort_buffer_size=8 M > log-bi n > server-id = 1
Next are a few options you probably don’t need to change if you have sufficient disk space:
# Point the following paths to different dedicated disk s
#tmpdir = /tmp /
#log-update = /path-to-dedicated-directory/hostnam e
The BDB options refer to the BDB storage engine, which provide MySQL’s first transaction-safe storage. You’ll learn more about storage engines in Chapter 2.
# Uncomment the following if you are using BDB table s
#set-variable = bdb_cache_size=4 M
#set-variable = bdb_max_lock=1000 0
InnoDB, another of MySQL’s storage engines, has numerous options that must be configured before you can use them. Because it provides transaction-safe tables with its own memory management and storage system, you need to specify where the data files will live, as well as how much RAM should be used. (InnoDB was briefly known as Innobase, so you may see that name in configuration files.)
# Uncomment the following if you are using Innobase table s
#innodb_data_file_path = ibdata1:400 M
#innodb_data_home_dir = /usr/local/mysql/var /
#innodb_log_group_home_dir = /usr/local/mysql/var /
#innodb_log_arch_dir = /usr/local/mysql/var /
#set-variable = innodb_mirrored_log_groups= 1
#set-variable = innodb_log_files_in_group= 3
#set-variable = innodb_log_file_size=5 M
#set-variable = innodb_log_buffer_size=8 M
#innodb_flush_log_at_trx_commit= 1
#innodb_log_archive= 0
#set-variable = innodb_buffer_pool_size=16 M
#set-variable = innodb_additional_mem_pool_size=2 M
#set-variable = innodb_file_io_threads= 4
#set-variable = innodb_lock_wait_timeout=5 0
The final option groups are for specific MySQL command-line utilities, including the mysql shell:
[mysqldump ]
quic k
set-variable = max_allowed_packet=16 M
[mysql ]
no-auto-rehas h
# Remove the next comment character if you are not familiar with SQ L
#safe-updates
[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk ]
set-variable = key_buffer=20 M
set-variable = sort_buffer=20 M
set-variable = read_buffer=2 M
set-variable = write_buffer=2 M
[mysqlhotcopy ]
interactive-timeou t
That file would be considerably larger and certainly more confusing if all the possi ble settings were listed. For 90% (or more) of MySQL users, there is simply never a need to adjust more than a few of the settings listed in the sample files.
 | If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!
Visit the O'Reilly Network http://www.oreillynet.com for more online content. |
Next: Reconfiguration >>
More MySQL Articles
More By O'Reilly Media
