●配置实例
在mysql的发布版本里的support-file目录下通常会包含4个配置文件的样例:
my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
以上这些文件的名字就表示了该配置文件适用的服务器大小。在每一份样例中
都有注释说明了使用服务器的范围。例如在my-medium.cnf文件中有;
# This is for a system with little memory (32M - 64M) where MySQL
plays
# a important part and systems up to 128M very MySQL is used
together with
# other programs (like a web server)
要使用样例文件,简单的拷贝带/etc/my.cnf目录下,再做一个必要的改动即可。因为
开始运行一个新的mysql时都很难一次配置到完美,所以使用样例文件作为系统最开始
的配置是比较合适的。但是在某些情况如果不对样例配合作出一些必要的调整,那么
性能可能会十分糟糕。
还是以my-medium.cnf文件为例,对于一个刚刚安装好的系统,有一些信息还不能完全理解。
但是经历了更多的例子后,就会慢慢的理解。该文件开头首先一些很有用的注释信息,说明
该配置文件适合的系统种类,以及安装所需要的信息:
# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL
plays
# a important part and systems up to 128M very MySQL is used
together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in
this
# installation this directory is /usr/local/mysq/var)
or
# ~/.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
program
# with --help option.
下面是所有客户端工具都会使用的配置项:
# The following options will be passed to all MySQL
clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
下面是服务端所使用的各种配置项。端口和socket选项需要与前面的客户端选项保持一致。
其余的一些选项设置为了各种缓存分配的内存大小,以及复制所需的配置。
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=64
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=8M
log-bin
server-id = 1
下面的选项如果有足够磁盘空间的话,可能就不需要改动:
# Point the following paths to different dedicated
disks
#tmpdir = /tmp/
#log-update =
/path-to-dedicated-directory/hostname
BDB选项是专门针对BDB存储引擎的。BDB引擎是mysql第一个支持事务安全的引擎。在第二章将
专门介绍存储引擎。
# Uncomment the following if you are using BDB
tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
InnoDB,是另一种mysql存储引擎,要使用它必须要预先设置好一些配置项。因为InnoDB使用
它自身的内存管理和存储系统来提供事务安全的数据表,所以必须设置数据文件的格式,以及
分配的内存大小。(InnoDB是Innobase的简写,所以在配置文件里面看到的是Innobase)
# Uncomment the following if you are using Innobase
tables
#innodb_data_file_path = ibdata1:400M
#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=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50
最后介绍的一组配置是关于mysql的命令行工具,包括mysql的shell:
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with
SQL
#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=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
假如把所有可能的配置项都列出来的话,那么篇幅实在太大了。90%以上的mysql使用者几乎没有必要了解那些在样例配置文件之外的那些配置项。