mysql配置参数详解

浏览数：21 / 时间：2015年06月12日

查看配置参数可以用下面的命令：

show variables like ‘%innodb%‘; #查看innodb相关配置参数

show status like ‘%innodb%‘; #查看innodb相关的运行时参数

show global status like ‘open%tables‘; # 查看全局的运行时参数，加上global是对当前mysql服务器中运行的所有数据库实例进行统计。不加global则只对当前数据库实例进行统计。

my.cnf参数配置

[client]

port = 3306

socket = /opt/mysql/data/mysql.sock

default-character-set = utf8 # 设置mysql客户端默认字符集

[mysqld]

port = 3306 # mysql服务端默认监听(listen on)的TCP/IP端口

socket = /opt/mysql/data/mysql.sock

skip-external-locking #不使用系统锁定，要使用myisamchk,必须关闭服务器

skip-name-resolve #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。

key_buffer_size = 64M #myisam索引buffer,只有key没有data

max_allowed_packet = 20M

table_cache=3096 #用于设置table高速缓存的数量。

table_open_cache = 6144 #表描述符缓存大小，可减少文件打开/关闭次数

table_definition_cache=4096 #默认400

tmp_table_size=64M # 内存中的每个临时表允许的最大大小。如果临时表大小超过该值，临时表将自动转为基于磁盘的表

max_heap_table_size=64M #该变量设置MEMORY (HEAP)表可以增长到的最大空间大小

sort_buffer_size = 512k #排序buffer大小；线程级别

read_buffer_size = 512k #读查询操作所能使用的缓冲区大小。

read_rnd_buffer_size = 512K#MyISAM以索引扫描(Random Scan)方式扫描数据的buffer大小；线程级别

join_buffer_size=512K #联合查询操作所能使用的缓冲区大小。

myisam_sort_buffer_size = 64M#MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区

query_cache_type=0

query_cache_size= 0 # 查询缓存大小，用于缓存SELECT查询结果。

bulk_insert_buffer_size=32M #默认8M参数来提高数据插入的效率，这个参数设置的是 bulk insert 的缓存大小

thread_cache_size = 64 #缓存的最大线程数。

thread_stack=256K

# Try number of CPU‘s*2 for thread_concurrency

thread_concurrency = 8

character_set_server = utf8 # 服务端使用编码的utf8字符集

max_connection=1024 #mysql服务器支持的最大并发连接数据（用户数）

max_user_connections=1000

max_connect_errors=10000 #如果某个用户发起的连接error超过该数值，则该用户的下次连接将被阻塞，直到管理员执行flush hosts ; 命令；防止黑客

wait_timeout=100 #等待关闭连接的时间

connect_timeout = 20#连接超时之前的最大秒数,在Linux平台上，该超时也用作等待服务器首次回应的时间

interactive_timeout=100 #关闭连接之前，允许interactive_timeout（取代了wait_timeout）秒的不活动时间。客户端的会话wait_timeout变量被设为会话interactive_timeout变量的值。

back_log = 500 #back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。默认值为50。对于Linux系统推荐设置为小于512的整数。

server-id = 1

###################binlog############

log-bin=/opt/mysql/logs/mysql-bin

binlog_format=row

max_binlog_size=128M

binlog_cache_size=2M #session级别

expire-logs-days = 5 #保存5天binlog日志

###################replication################

slave-net-timeout=10 #默认3600 #从服务器也能够处理网络连接中断。但是，只有从服务器超过slave_net_timeout秒没有从主服务器收到数据才通知网络中断

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_wait_no_slave=1

rpl_semi_sync_master_timeout=1000

skip-slave-start #启动mysql,不启动复制

log_slave_updates=1

relay_log_recovery=1

#############slow log 慢查询###########

slow_query_log =1 #1为开启慢查询 on

slow_query_log_file=/opt/mysql/logs/mysql.slow #慢查询的日志目录

long_query_time=2 #慢查询时间超过2秒则为慢查询

##################errlog################

log-error=/opt/mysql/logs/error.log #日志目录

############## InnoDB###########

innodb_buffer_pool_size =11G # InnoDB使用缓冲池来缓存索引和行数据。该值设置的越大，则磁盘IO越少。一般将该值设为物理内存的80%。我一般都是设置物理内存70%。16G内存算的

innodb_buffer_pool_instances=8 #默认1

innodb_additional_mem_pool_size = 16M # InnoDB用于存储元数据信息的内存池大小

innodb_flush_log_at_trx_commit = 2 # 事务相关参数，如果值为1，则InnoDB在每次commit都会将事务日志写入磁盘（磁盘IO消耗较大），这样保证了完全的ACID特性。而如果设置为0，则表示事务日志写入内存log和内存log写入磁盘的频率都为1次/秒。如果设为2则表示事务日志在每次commit都写入内存log，但内存log写入磁盘的频率为1次/秒。

innodb_log_buffer_size=64M # InnoDB日志数据缓冲大小，如果缓冲满了，就会将缓冲中的日志数据写入磁盘（flush）。由于一般至少都1秒钟会写一次磁盘，所以没必要设置过大，即使是长事务。

innodb_log_file_size=1024M # 每一个InnoDB事务日志的大小。一般设为innodb_buffer_pool_size的25%到100%

innodb_log_files_in_group=3 #默认2

innodb_lock_wait_timeout = 10 #默认50

innodb_sync_spin_loops =40 #默认30

innodb_max_dirty_pages_pct=90 #与检查点有关系，默认75

innodb_support_xa=1 #有0、1、2三个值分别代表不同的使redo log落地策略。0表示每秒进行一次flush，但是每次事务commit不进行任何操作（每秒调用fsync使数据落地到磁盘，不过这里需要注意如果底层存储有cache，比如raid cache，那么这时也不会真正落地，但是由于一般raid卡都带有备用电源，所以一般都认为此时数据是安全的）。1代表每次事务提交都会进行flush，这是最安全的模式。2表示每秒flush，每次事务提交时不flush，而是调用write将redo log buffer里面的redo log刷到os page cache。

innodb_thread_concurrency=0 #默认为0

innodb_thread_sleep_delay=500 #默认10000

innodb_concurrency_tickets=1000 #默认500

innodb_flush_method= O_DIRECT #这个参数控制着innodb数据文件及redo log的打开、刷写模式，对于这个参数，文档上是这样描述的：
有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT
默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件

为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

innodb_file_per_table #开启独立表空间

innodb_read_io_threads=8 #用于从磁盘读文件块的线程数

inodb_write_io_threads=8 #用于写脏页的线程数

inodb_io_capacity = 2000

innodb_file_format=Barracuda #有两参数1.Antelope（默认），2. Barracuda 注意在安装数据库时就要调整

innodb_purge_threads=1 #默认0在之前版本，碎片回收操作是主线程的一部分，这经定期调度的方式运行，但会阻塞数据库的其他操作；到5.5以后，可以将这个线程独立出来；方法：innodb_purge_threads =1
这个应该能让碎片回收得更及时而且不影响其他线程的操作；

innodb_purge_batch_size=32 #是否开启独立的线程清除undo页，默认是0不开启,每次删除的页数由Innodb_purge_batch_size控制，默认是20

innodb_old_blocks_pct=75 #默认37一个是sublist of old blocks区域（不经常访问的数据）

innodb_change_buffering=all #默认all

[mysqldump]

quick