mysql服务器参数
mysql服务器参数:
配置是从上往下读取,同一个参数项,后边的配置项会覆盖前边的配置项
mysql获取配置信息路径:
- 命令行参数 mysqld_safe --datadir=/data/sql_data
- 配置文件 centos默认读取/etc/my.cnf
默认读取配置文件顺序:
[root@localhost ~]# mysql --help --verbose | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
mysql配置参数的作用域:
- 全局参数
set global.参数名:=参数值; - 会话参数
set [session] 参数名=参数值;
内存配置相关参数:
- 确定可以使用的内存上限
- 确定每个连接使用的内存 以下参数都是为每个线程分配的,有多少个线程就会分配多少
sort_buffer_size 排序缓存区大小,注意:一旦查询需要排序,mysql会立即分配这个指定值的大小,不管这个排序查询是否需要这么大
如果设置为100M,如果有100个连接,使用的内存就是10G,会导致内存溢出,慎用
join_buffer_size 连接缓存区大小,连表查询会导致多个连接缓存区,也不建议配置太大,分配这个指定值的大小
read_buffer_size 全表扫描时候分配的读缓存区的大小,一定要是4K的倍数,分配这个指定值的大小
red_rnd_buffer_size 索引缓存区的大小,分配的是需要的,不以参数为标准 - 确定需要为操作系统保留多少内存
- 如何为缓存池分配大小 innodb的性能依赖于缓存池
innodb_buffer_poll_size 这个值调整以后需要重启mysql服务器
总内存-(每个线程所需要的内存 * 连接数)-系统保留内存
key_buffer_size 主要用于myisam缓存引擎的
IO配置相关参数 这部分参数决定了如何同步mysql缓存池中的数据到磁盘上
innodb I/O相关配置:
- innodb_log_file_size 控制了单个事务日志文件的大小
- innodb_log_file_in_group 控制事务文件个数
- 事务日志的总大小=innodb_log_file_size * innodb_log_file_in_group
- innodb_log_buffer_size 缓存池大小 建议32M~128M
- innodb_flush_log_at_trx_commit 刷新事务日志的频率
0:每秒进行一次log写入cache,并flush log到磁盘
1(默认):在每次事务提交执行log写入cache,并flush log到磁盘 最安全的设置,但是性能最差
2(建议):每秒事务提交,执行log数据写入到cache,每秒执行一次flush log 到磁盘 mysql进程崩溃时不会丢失任何的事务 - innodb_flush_method=O_DIRECT #对于linux系统是最好的选择
#决定了innodb数据文件跟日志文件 和 系统文件如何去交互,配置如下可以通知操作系统不要缓存数据,也不要预读,就是说完全关闭了操作系统的缓存,读和写都会直接通过存储设备来完成,避免了innodb和操作系统对于数据的双重缓存 - innodb_file_per_table=1 #使用独立表空间
- innodb_doublewrite=1 #控制双写缓存,避免系统崩溃造成的数据丢失,保证数据的安全性
myisam I/O相关配置:
- delay_key_write 延迟键写入
OFF:每次写操作后刷新键缓冲中的脏块到磁盘 最安全的选项,但是性能比较差
ON(默认):只对在建表时候指定了delay_key_write选项的表使用延迟刷新
ALL:对所有MYISAM表都延迟键写入
安全配置相关参数
- expire_logs_days 指定自动清理binlog的天数,以天为单位
- max_allowed_packet 控制mysql可以接收的包的大小,默认为2M,太小了,可以设置为32M 注意:主从服务器上这个配置要保持一致
- skip_name_resolve(默认关闭) 禁用DNS查找
- read_only 禁止非super权限用户写权限,建议在备库中启用
- skip_slave_start 禁用自动启动复制,从库配置
- sql_mode最好不要使用
- sync_binlog 控制mysql如何向磁盘刷新binlog,为1的时候最安全,默认为0