mysql 5.1.57主备配置文件备忘

mysql 5.1跟mysql 5.0有很多配置参数已经更改了，甚至最新的mysql 5.1.57和早一些的mysql 5.1,比如mysql 5.1.1x之类的，都有不少配置参数的更改，真是搞不懂为什么？其实实现的功能基本是一致的。

贴上最近做的一个Mysql 5.1.57主备的安装和配置在这里，备忘：

我这两台机器的内存都很大，每台48G。不过目前的数据量还很小。

安装：

# yum install gcc gcc-c++
# groupadd mysql
# useradd --shell /sbin/nologin -g mysql mysql
# tar zxvf mysql-5.1.57.tar.gz
# cd mysql-5.1.57

# ./configure --prefix=/data1/app/services/mysql51
            --localstatedir=/data1/app/services/mysql51/data
            --with-charset=utf8
            --with-collation=utf8_general_ci
            --with-extra-charsets=gb2312,gbk,utf8
            --with-plugins=max-no-ndb
            --with-unix-socket-path=/data1/app/tmp/mysql.sock
            --with-mysqld-user=mysql
            --enable-local-infile
            --enable-assembler
            --with-client-ldflags=-all-static
            --with-mysqld-ldflags=-all-static
            --enable-thread-safe-client

# make

# make install

配置文件：

主：

[mysqld]
datadir=/data1/app/services/mysql51/data
socket=/data1/app/tmp/mysql.sock
pid-file=/data1/app/tmp/mysqld.pid
user=mysql

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine=INNODB

skip-name-resolve
skip-external-locking
key_buffer = 1024M
max_allowed_packet = 1M
table_cache = 32512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 32
query_cache_size = 32M
tmp_table_size = 1G

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
lower_case_table_names = 1
max_connections = 1200

slow_query_log_file = /data1/app/log/mysqld-slow.log
slow_query_log = 1
log-queries-not-using-indexes
long_query_time = 1
wait_timeout = 86400

log-bin = mysql-bin
server-id = 1
expire-logs-days = 7
#binlog-do-db = db1

#binlog-do-db = db2

innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table
innodb_open_files = 65535
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

binlog_format = mixed
transaction-isolation = READ-UNCOMMITTED

[mysqld_safe]
log-error=/data1/app/log/mysqld.log

备：

[mysqld]
datadir=/data1/app/services/mysql51/data
socket=/data1/app/tmp/mysql.sock
pid-file=/data1/app/tmp/mysqld.pid
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine=INNODB

skip-name-resolve
skip-external-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 32512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 16M
tmp_table_size = 1G
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
lower_case_table_names = 1
max_connections = 1200

slow_query_log_file = /data1/app/log/mysqld-slow.log
slow_query_log = 1
log-queries-not-using-indexes
long_query_time = 1
wait_timeout = 86400

log-bin = mysql-bin
relay-log = mysql-relay-log
relay-log-index = mysql-relay-index
log_slave_updates = 1

server-id = 2
#master-host = 10.0.0.32
#master-user = repl
#master-password = password
#master-port = 3306
#master-connect-retry = 10
expire-logs-days = 7
replicate-do-db = db1
replicate-do-db = db2
#replicate-ignore-db=test
report-host = 10.0.0.33

innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table
innodb_open_files = 65535
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

binlog_format = mixed
transaction-isolation = READ-UNCOMMITTED

[mysqld_safe]
log-error=/data1/app/log/mysqld.log

备机的主服务器配置信息不再配置在my.cnf内了，启动后执行以下SQL配置：

CHANGE MASTER TO MASTER_HOST='10.0.0.32',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10;

另外关于innodb_buffer_pool_size参数，今天看了一下这篇文章：

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

主要讲的是关于innodb_buffer_pool_size参数的配置的，大致就是两个方向：

如果mysql innodb的数据量小于物理内存，那么innodb_buffer_pool_size配置为数据量大小+10%，然后再考虑下数据增长，适当调整。
如果mysql innodb的数据量小于物理内存，那么innodb_buffer_pool_size配置应该尽量大，这样可以最大限度的增加缓冲，减少IO。

但是配置时需要注意用物理内存减去其它方面的内存开销，这些开销有：

1）操作系统的内存开销，这个包括一些系统进程，页表，socket连接的缓冲等一般留个1G左右就够了。

2） innodb正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。

3）数据库连接方面的内存开销，下面有计算公式。

4） query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer等的开销。

5）其它应用程序的内存开销，如果有的话。

在这种情况下，用物理内存减去上面几个方面的内存开销。同时参考用Mysql 5.1手册上的计算公式来确定这个值：

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

每个线程使用一个堆栈（通常是2MB，但在MySQL AB二进制分发版里只有256KB）并且在最坏的情况下也使用sort_buffer_size + read_buffer_size附加内存。

另个还有两个注意的地方：

1）避免双重缓冲，innodb用了innodb_buffer_pool，操作系统层面还有一个缓冲。这样就双重缓冲了，没有必要，反而加大了系统的负载，速度也慢了。可以配置innodb_flush_method = O_DIRECT跳过操作系统的缓冲，直接写入磁盘。

2）避免操作系统对mysql进程做页面交换。如果物理内存不够的时候，操作系统会把当前进程使用的内存先存到磁盘的虚拟内存，然后把空出来的内存给新的需要的进程使用。这方面需要保持有足够的内存，在linux上不想用虚拟内存，可以配置内核参数：

# vim /etc/sysctl.conf
vm.swappiness = 5
# sysctl -p

默认是60,这个数字越高，使用虚拟内存的机率也越高，我设置5，意思意思就行了。需要注意的是，就算设成0也不表示不使用虚拟内存了，物理内存不够时，依然会用到的。

mysql 5.1.57主备配置文件备忘

阿辉