MySQL 管理规范

By | 2014/05/27

参数配置说明

1.概述

本文档目的在于标准化线上MySQL数据库的安装和管理,以保证数据库环境的统一,便于DBA管理及维护。

笔者详细介绍Percona MySQL Server的参数信息, 从个人角度上看参数选项可以分为必选项和可选项(仅供参考), 对一些需要经常设置的变量建议放到cnf配置文件中(比如expire_logs_days,read_only等)。

目前为止,MySQL安装方式主要有:RPM/DEB, Binary, Source Code, Repository。 RPM/DEB方式安装简单、方便,在发布前已经经过了极为严格的测试, 稳定性和安全性都有所保证,升级方面只需考虑有重要bug、更新等高版本,没必要有更新就升级;Binary方式相对独立,不需要依赖过多的库文件等,在安装和升级方面很方便,另外也利于线上环境的批量部署,同样Binary方式在发布前也经过了严格的测试;Source Code安装方式适合有定制功能或更改参数默认信息的需求,一般没有特殊需求的业务可以不采用源码安装的方式;Repository为第三方厂商发布的仓库版本,比如percona yum/apt版本,该方式的优点等同RPM/DEB方式。

2.安装之前

软件获取: 采用第三方厂商percona XtraDB分支版本,见 www.percona.com , 5.1版本不再维护, 最新为5.1.73版本. 5.5 和 5.6 可以选用最新的版本.

安装条件: 鉴于不同RAID级别对数据库性能和安全方面的影响,线上环境应该统一采用RAID10级别,如果硬盘不够可降级为RAID1或RAID5级别(RAID0不安全)。

RAID卡型号选取带有Cache功能的卡,如DELL的H700或H710P。

磁盘调度算法:专用数据库采用deadline方式,非专用取默认cfg方式。

3.必选项

[mysqld_safe] 

mysqld_safe区域选项参数设置,线上强制以mysql safe方式启动MySQL Server,可在该区域设置log error等输出信息,但不做强制要求。

syslog                              

将error信息输出到syslog(/var/log/message)中,设置该选项初衷在于利用LogAnalyzer工具监控错误信息起到及时告警的作用,缺点是对历史错误信息难以追踪。

syslog-tag = XXXX                   

error信息输出到syslog时增加一个标签后缀,此选项在一台主机多实例环境下达到区分来源实例的目的。

[mysqld]                              

MySQL Server端启动选项

server-id = XXXXXX 

XXXX+(3<<16) #保证server-id的唯一性即可,怎么唯一不做强制性要求,线上通过这样的计算方式 server-id = 端口号 + 内网ip地址的最后8位左移16位,举例来说 199914 = 3306+(3<<16), (3<<16)等效于00000011 0000 0000 0000 0000转为十进制为196608

port      = 3306

Server 监听端口

datadir = /web/mysql/node3306/data            

数据目录(不做其它参数设置,则共享表空间,undo日志,binlog日志,新建的实例等都存放到该目录下)。

pid-file = /web/mysql/node3306/data/mysql.pid 

pid文件保存路径,mysql.pid文件保存MySQL Server启动时的进程ID,正常关闭数据库,pid文件消失。如果非正常关闭,pid文件依旧存在,MySQL在启动的时候检查pid文件,存在则报错,否则正常启动,可手动删除pid文件避免该问题。

socket = /web/mysql/node3306/data/s3306       

socket文件路径位置,指定socket名为s3306,socket应用于本地的连接,远程无效。

back_log = xxx                      

指定MySQL 所能拥有的未处理请求的数量,该参数限制了在一段时间内存在大量请求的情况下,请求队列中能够接受请求的数量xxx,多余的请求则停止响应或丢弃。该值不要超过内核参数 /proc/sys/net/ipv4/tcp_max_syn_backlog。

max_connections    = 1000           

Server端同时可处理的最大连接数,一个线程算一个连接(一个process),根据线上环境一般实例都在300以内,考虑高峰、扩容等因素可设置到1000.线程连接超过指定值则出现 ERROR: Can’t create a new thread,可通过临时增加进程limit缓解:echo -n “Max processes=SOFT_LIMIT:HARD_LIMIT” > /proc/pidof mysqld/limits, 出现too many connections错误的时候可临时使用 gdb -p $(cat path/mysql.pid) -ex “set max_connections=5000” -batch增加max_connections的变量值,但是在并发特别大的时候可能会引起Server的Crash。

max_user_connections = xxx

指定每个用户能够同时连接的最大值,默认为0,不受限制,和max_connections一起使用,可以尽量避免出现too many connections错误.

max_connect_errors = 100000         

客户端连接请求被中断的次数超过max_connect_errors值的话,MySQL Server则阻塞该客户端的后续连接,直到管理员可通过flush hosts命令。

thread_cache_size  = 64             

MySQL Server可以缓存以再次使用的thread数量,该参数对性能提升很有帮助,减少创建新线程的开销,可以参考官方手册查看详细信息。

table_open_cache   = 1000           

从5.1.3开始引入该参数,表示所有线程能够打开表的数量,不要被cache锁迷惑。该参数和max_connections相关,有利于提高并发性能。

skip-name-resolve                   

参数指定Server端 在检查 client连接的时候不要解析host name,只使用ip 地址。

max_allowed_packet = 4M             

指定允许通过最大大小的包数据,如果有大的BLOB,string等,确保值合适,中文网平台4M即可,如果超过4M,协议会对包进行分组传送。该参数在client, master, slave的设置需保持一致,如果slave的值大于master,可能会出现无限报错,重试,relay log损坏等问题。

user      = mysql                   

以mysql用户启动mysqld服务,不要使用root用户启动

read_only = 1                       

开启read only,确保slave不会被应用帐户更新数据,master也配置保持配置的一致性,启动master后,可手工禁止read only。

skip-external-locking               

跳过外部锁,external locking是系统级别锁的一种应用,针对于MyISAM表,目的在于减少锁争用,但是容易引起死锁,所以禁掉external lock为好。

character-set-server = utf8         

指定Server端编码为utf8。

collation-server = utf8_general_ci  

编码的排序规则制定为utf8_general_ci,这也是默认排序规则。

tmpdir = /dev/shm                   

临时目录指定到 /dev/shm 临时文件系统中,glibc 2.2 版本及以上会因为 POSIX 共享内存的需要期望将临时文件系统(tmpfs) 挂载到 /dev/shm(默认值), 默认为系统内存的一半大小, 可以重新 remount /dev/shm 的大小: mount -o remount,size=32G,noatime /dev/shm ; 也可以编辑 /etc/fstab 将大小写到文件里使得重启系统也能生效: tmpfs /tmp tmpfs nodev,nosuid,size=2G 0 0

slow_query_log = 1                  

启用慢查询记录

long_query_time = 1                 

记录超过1s的查询语句,没有精确到毫秒级别。

slow_query_log_file = slow-query.log

未指定路径则保存在datadir目录中。

log-bin     = mysql-bin.log         

指定mysql-bin.为序列化二进制日志的基准名,如mysql-bin.000001,不要使用hostname等差异化的名字,命名规则都保持统一。

sync_binlog = 1                     

该参数控制写入操作刷新到二进制日志的频率: 默认为0,表示由操作系统决定什么时候刷新到binlog,这种情况是最快的,也是最危险的;线上环境统一指定为1,是最安全的,系统崩溃只丢失一条记录,但也是最慢的(除非磁盘具有电池备份缓存特性(BBU))。

relay-log   = relay-bin.log         

指定relay-bin为relay log的基准名,不要使用hostname等信息命名。

log-slave-updates                  

线上所有实例统一开启relay log选项,以达到master, slave可以互相切换的目地。

replicate-same-server-id = 0        

布尔类型,在Slave Server中生效,值为0 可以避免server-id一样而引起的循环复制。

innodb_buffer_pool_size  = xxxxM    

该选项控制缓存innodb数据信息、索引数据信息的大小,buffer pool是弥补 磁盘和cpu之间处理速度 的一种有效方式,理论上buffer pool越大,数据库性能越小,不过由于内存等因素的限制,难以做到全部缓存。专用数据库系统主机中可以设置物理内存的50%~70%;一主机多实例的环境中,预留20%的物理内存供系统使用,剩余空间按照实例数量酌情分配。

innodb_log_file_size     = 128M     

innodb log file文件存储redo log等信息,设置过大,崩溃后的恢复时间越长,设置过小,会引起磁盘的频繁操作

innodb_log_buffer_size   = 4M       

该参数控制InnoDB的事务日志所使用的缓冲区,为了提高性能,也是先将信息写入 Innofb Log Buffer 中,当满足 innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后,会将日志写到文件(或者同步到磁盘)中,默认1M,线上统一为4M(不超过max_allowed_packet大小)。

innodb_flush_log_at_trx_commit = 2  

该参数控制日志缓冲刷新到磁盘上的方式, 0:每秒刷新一次,事务提交不做操作;1:每提交一个事务,刷新一次;2:每提交一个事务,写到文件,但不对文件做磁盘刷新操作。0方式效率最高,但最不安全,1方式最安全,但性能最差,2方式相对安全(只要主机不崩溃就可以恢复,因为数据还在内存中),效率也高(接近0方式)。线上数据库统一设置为2方式。

innodb_flush_method    =  O_DIRECT 

该方式控制怎么打开表文件和刷新数据到磁盘上,O_DIRECT方式:以 direct I/O 打开数据文件,调用fsync()系统函数刷新数据文件和日志文件;O_DSYNC方式:以O_SYNC 方式打开表并刷新日志文件,调用fsync()系统函数刷新数据文件;UNIX/LINUX系统中fsync()系统函数效率很高(比较而言,但是没有确切的结论见flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow.),从这点来看O_DSYNC方式在更新方面更快些,但手册上没有明确证明,视情况而定。线上实例统一设置为O_DIRECT方式;另外O_DIRECT开启,并启用InnoDB可以禁用系统的缓存,减缓内存浪费; 使用该方式配置的时候, 如果应用存在 create temporary table ..等语句, 则需要注意以下报错信息[Warning] InnoDB: Failed to set O_DIRECT on file /dev/shm/#sql3ed_642_0.ibd: CREATE: Invalid argument, continuing anyway. O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662. percona 版本不会引起进程重新启动, 而是以其他方式打开文件, 比如使用percona 5.6.29版本测试, 对其 strace -p ‘pidof mysqld‘ -f 之后的结果如下:

392 [pid 21726] open("/dev/shm/#sql3ed_646_0.ibd", O_RDWR|O_CREAT|O_EXCL, 0660) = 104
393 [pid 21726] fcntl(104, F_SETFL, O_RDONLY|O_DIRECT) = -1 EINVAL (Invalid argument)
394 [pid 21726] fcntl(104, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0
......
......
402 [pid 21726] open("/dev/shm/#sql3ed_646_0.ibd", O_RDWR) = 104
403 [pid 21726] fcntl(104, F_SETFL, O_RDONLY|O_DIRECT) = -1 EINVAL (Invalid argument)
404 [pid 21726] fcntl(104, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0

换言之不影响程序的数据查询. 如果想忽略掉错误, 可以将改参数设置为 O_DRYNC(修改需重启进程) 或将 tmpdir 参数设置为非 tmpfs 的目录.

innodb_file_per_table  = 1          

启用该选项,控制每个表拥有一个数据文件(ibd),默认为0,所有的表数据都会写到共享表空间(ibdata1),对大库而言很难管理,线上统一强制开启。

default-storage-engine = innodb     

设置默认存储引擎为innodb,线上统一为该配置,innodb表在性能和数据安全方面有很好的保证,崩溃恢复机制不可缺少,线上环境新建的实例或表建议启用该选项,另外default的设定还有一个好处:5.1版本中以MyISAM引擎为默认,如果没有安装(或启用)innodb插件,则不能创建innodb表(显示声明引擎会采用默认的MyISAM替换),这点有悖于应用的初衷;指定了default选项且Server没有启用InnoDB特性,在服务启动的时候就会报错退出,而不是可以启动。

以下参数为percona分支版本:

userstat_running                = 1 

5.5.21以上版本改名 userstat ,用来控制是否开启统计信息的搜集(information_schema.USER_STATISTICS表),即统计用户在实例中的使用情况,如连过过少次,读了多少数据,执行命令包括那些等,建议开启,但会增加一点统计上的开销。

innodb_overwrite_relay_log_info = 1 

5.5版本以上改名innodb_recovery_update_relay_log。复制是一个异步的过程,slave上进行复制的position信息(relay log中)总是落后于master,如果在更新该position之前发生崩溃,已经提交的事务在崩溃恢复过程中会再执行一次。该选项记录了复制了相关位置信息,加了一层保护作用,确保主从尽量一致,建议开启,线上全部开启。

innodb_lazy_drop_table          = 1 

5.5.30~5.5.32,该特性废弃,其余版本存在。当开启innodb_file_per_table选项后,在一个有很大buffer pool的Server中删除一个表需要花费很长的时间,即便这个表是空的,因为innodb需要在buffer pool中扫描和表相关的page信息,然后清除;启用该选项innodb扫描到相关page信息后只是做个deleted标记(mark),然后由server的后台线程慢慢清理,启用该选项,删除表时,能降低对Server的影响。

innodb_pass_corrupt_table       = 0 

5.5.10以上版本改名innodb_corrupt_table_action [assert, warn, salvage]。系统一旦检测到存在坏的表空间,除了drop table操作,其它访问操作被禁止.当开启了innodb_file_per_table(设置为1)选项后,innodb尝试做一些修复操作,同时检测损坏的page,修复不了则锁住user table,禁止访问(即影响所有与损坏表相关的database操作,包括user信息),线上该参数统一配置为0,不做锁表操作,只返回错误。5.5版本设为warn,只返回错误,其它两个值和5.1版本设置为1情况类似。

log_slow_verbosity          = full  

该选项用来指定哪些信息需要记录到slow log文件中,包括查询语句时间精度,innodb status信息等,线上统一用full表示全部。

innodb_auto_lru_dump = 16

5.5.10更名为innodb_buffer_pool_restore_at_startup,值范围: 0-UINT_MAX32,单位秒; 该参数实现自动dump/restore buffer pool功能, 能够极大减少InnoDB重启后buffer pool预热的时间,在主机不宕掉的情况下,buffer pool还存在于系统的memory中,该参数遍历并保存buffer pool中page的标识信息, MySQL重新启动的时候会读取dump的文件信息加载到相应的page位置。详细测试见: http://www.mysqlperformanceblog.com/2010/01/20/xtradb-feature-save-restore-buffer-pool/

4.可选项

!include ~/.my.cnf                    

从MySQL 5.0.4版本开始,可以使用!include命令指定其它的选项文件(my.node.cnf或my.cnf俗称为MySQL选项文件),即my.node.cnf文件包含 ~./.my.cnf文件中的选项(家目录下的./my/cnf文件),线上在该文件中指定用户密码,特别注意.my.cnf文件的读写权限。

[mysql]                                #mysql客户端命令区域选项
 prompt = 'mysql \u@[\h:\p \d] > '     #提示符格式, 'mysql 用户@[用户host:port database]'
 default-character-set = utf8          #客户端连接默认设置字符集为utf8,目前线上业务都保持为utf8编码
[mysqladmin]                           #mysqladmin区域
default-character-set = utf8         
 [mysqlcheck]                          #mysqlcheck区域选项,设置check表相关参数,该命令默认进行锁表操作
default-character-set = utf8          
[mysqldump]                            #mysqldump区域选项,设置默认字符集为utf8
 default-character-set = utf8
[mysqlimport]                          #数据导入时的字符集设置
 default-character-set = utf8
[mysqlshow]                            #显示数据库,表,列信息时的字符集
 default-character-set = utf8
[client]                               #设置所有所有客户端命令、工具连接数据库实例时的默认端口,socket参数;
 port = XXXX
 socket = /web/mysql/node3306/data/sXXXX
sort_buffer_size = 2M                 

排序操作分配的缓存大小,Sort_merge_passes量比较大的话可以酌情增加值大小,默认256K。

read_buffer_size = 2M                 

线程做一些顺序扫描的时候,该参数很有用,但适用于MyISAM引擎的表,不做强制性要求,默认128K。

join_buffer_size = 2M                 

连接查询时,分配的buffer size,增加该值大小,有利于join效率,中文网平台很少使用join查询,默认128K。

read_rnd_buffer_size = 4M             

对MyISAM表很有用,在做排序或索引排序相关的操作时,增加该参数值可以减少硬盘的查询操作,默认256K。

expire_logs_days        = 7           

二进制日志过期天数,不做强制性要求,但是线上出现过因为binlog日志太多而导致硬盘使用紧张的案例,一般建议开启,可设置为7天(根据业务需求设定)。

binlog_format

二进制日志格式,为动态变量,默认statement格式。该选项有三种方式statement,row,mixed。statement按照更新的sql语句记录到二进制日志中;mixed混合型,除了不安全函数(uuid,sysdate等),更新结果集不确定采用row格式以避免出现主从不一次的情况外,其余语句采用statement格式;row详细记录每行记录的变化。row格式在主从结构中很安全,但是binlog更新很频繁,会吃掉很多磁盘资源,中文网平台不适合该种方式;mixed格式更有效,可以保证主从的一致性,更新量也不大,应用环境如果对数据一致性要求比较高,可以采用该种方式;statement简单,方便但是最不安全,数据的一致性需要开发人员避免不安全函数或不一致结果集的sql出现,需要投入相当多的人力。该选项不做强制,线上统一以statement格式存储Binlog日志。

key_buffer_size         = N(M|G)      

该参数主要针对于MyISAM引擎,不做强制要求,但是每更新一次变量会清空当前变量的大小,重新缓存,MyISAM表较多的实例中谨慎修改,系统默认为8M。MySQL中对于MyISAM引擎表,只缓存索引文件,不缓存数据文件,如果实例中MyISAM表很多,可尽量保证key_buffer_size大小接近实例中所有MYI文件大小之和,但不超过4G(此限制存在于32位主机中)。

wait_timeout            = xxx         

Server 关闭非交互连接之前等待的秒数,默认28800s, 该值设置过小可能使resin应用类的长连接失效,设置过大可能会使process资源紧张,中文网平台保持默认即可,其它平台可设置小点3600或7200。

sysdate-is-now          = 1           

启用sydata-is-now功能,启用后,sysdate相关的函数当作now()函数处理,以确保主从数据的一致性(sysdate在Replication中为不安全函数,每次返回函数开始执行时的时间)。如果应用端该函数很多,可以启用该功能,不在乎主从上的一致性,可以忽略。

skip-slave-start                      

该参数指定在slave server重启的时候,跳过slave 连接的自动完成,即需要手工启动slave信息。该参数可选,不启用该选项,slave重启的时候,如果出现问题可可能会导致主从关系错乱。

innodb_status_file      = 1           

不做强制性要求,启用该选项会使SHOW ENGINE INNODB STATUS的输出信息以一定周期频率写道DATADIR/innodb_status.文件中。

innodb_data_file_path

不做强制性要求,默认为ibdata1:10M:autoextend。开启innodb_file_per_table选项后,ibdata共享表空间主要存储数据字典,回滚段等信息,线上增长不会太大。如果线上实例存在频繁的事务回滚等操作可以适当调大该参数信息。

innodb_additional_mem_pool_size = xxx 

不做强制性要求,默认1M,该选项主要用来存储数据字典,内部结构等信息,实例中表越多,需要的空间越大。buffer pool不够用时,该选项会从系统内存中获取空间,并发出error信息。

innodb_log_files_in_group             

多少个log file为一组,默认为2,采用轮询方式。

 relay_log_purge

改选项控制slave sql_thread线程执行完重放完sql语句后是否清除relay log文件,默认开启该选项。不做强制性要求,关闭该选项在系统崩溃的时候,数据恢复则多了一层保障。

thread_handling

percona 5.5版本引入的参数,同max_connetions,默认为one-thread-per-connection,线程越多对服务的性能影响越大,thread_handling = pool-of-threads为动态调整线程池,对OLTP应用在性能上有很好的保障作用。该参数不做强制性要求。线上统一采用默认值
http://www.percona.com/doc/percona-server/5.5/performance/threadpool.html

slave_skip_errors = 1062

1062(Duplicate key error), slave_skip_errors忽略指定选项的错误代码, 线上统一制定为1062, 避免Duplicate error中断sql_thread线程,该参数不支持动态更改,每次做更改需要重启Server.

query_cache_strip_comments

使Server 在检查query cache hit的时候忽略 commnet信息;举例说明如下:
/first query/ select name from users where users.name like ’Bob%’;
/retry search/ select name from users where users.name like ’Bob%’;
默认情况下,这两条语句被认为不同,server端会分别执行并缓存它们,如果该选项开启,Server会忽略commnet信息,这两条语句就是相同的语句,执行和缓存值进行一次,减少开销;

flush_time

如果设置为非0值, 每隔指定的时间(seconds)对所有表进行一次closed操作以便释放一些资源并且同步未刷新到磁盘的数据到磁盘; percona默认为0, Oracle <= 5.6.5默认为1800s, >=5.6.6默认为0s;

Leave a Reply

Your email address will not be published. Required fields are marked *