Mysql常见配置

  1. 云栖社区>
  2. 博客>
  3. 正文

Mysql常见配置

~信~仰~ 2019-03-17 19:20:29 浏览1925 评论0

摘要: 配置文件所在位置 查看配置文件的位置和加载顺序: mysql --help 查看: Default options are read from the following files in the given order: C:\Windows\my.

配置文件所在位置

查看配置文件的位置和加载顺序:

mysql --help

查看:

Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.7\my.ini C:\Program Files\MySQL\MySQL Server 5.7\my.cnf

对于同一配置,后面加载的文件会覆盖掉前面加载的文件,所以修改配置最好在最后加载的文件中进行修改,修改后重启mysql生效。

全局配置文件配置

max_connections
最大连接数配置,这是一个非常重要的优化点,mysql默认的很少(5.7版本为151),可以改大一些。

user = mysql 默认启动用户,一般不需要修改,可能出现启动不成功

port = 3306 端口号

character-set-server = utf8 设置数据库服务器默认编码 utf-8

basedir = /usr/local/mysql 数据库安装目录--指定此参数可解决相对路径造成的问题

datadir = /data/mysql 数据库目录,数据库目录切换时需要用到

pid-file = /var/run/mysqld/mysqld.pid mysql进程文件,可指定自己的进程文件

skip-name-resolve 跳过主机名解析,直接IP访问,可提升访问速度

log-error = /data/log/mysqld.log 错误日志文件

open_files_limit = 10240 全局只读变量,文件描述符限制 注:上限其实为OS文件描述符上限,小于OS上限时生效 可用lsof查看限制并修改相应配置

back_log = 600 在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。不超过TCP/IP监听队列,建议512倍数,如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

数据库引擎相关参数

innodb_lock_wait_timeout = 120 事务获取锁的最长等待时间,超时请求失败

innodb_buffer_pool_size = 80G 类似于myisam的key_pool_size 适当增加可提高命中率,专用服务器可设置为70-80%

innodb_flush_log_at_trx_commit = 2 默认值1 每次提交日志记录磁盘 2 日志写入系统缓存 0 不提交也记录,不安全,不推荐

innodb_log_files_in_group = 2 以循环方式将日志文件写到多个文件,默认2

innodb_log_file_size = 4G 数据日志文件大小,较大可提升性能,

innodb_log_buffer_size = 512M 日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据(开发人员推荐1-8M---不知道为什么)

transaction_isolation = REPEATABLE-READ 事务隔离级别

tmp_table_size = 10G 查询生成的临时表大小超过该值时会在硬盘生成MyISAM表,如果存在大量group by 等语句,可调整大小

interactive_timeout = 120 mysql在关闭一个交互的连接之前所要等待的秒数(客户端连接)

wait_timeout = 864000 mysql在关闭一个非交互的连接之前所要等待的秒数 默认8小时(应用程序调用),

主从复制相关--必须开启log-bin

server-id = 19911216 主从复制必须,并且各服务器具有唯一性

log_slave_updates 配置从服务器的更新是否写入二进制日志,默认是不打开的

replicate-ignore-db = mysql 主从复制默认忽略的数据库,可用","分隔或使用多条记录

replicate-do-db=qrs,login 主从复制指定数据库,","号隔开或使用多条记录

mysql内存配置参数

每一个connection 内存参数配置:
sort_buffer_size connection 排序缓冲区大小
建议256K( 默认值)-> 2M 之内
当查询语句中有需要文件排序功能时,马上为connection 分配配置的内存大小

join_buffer_size connection 关联查询缓冲区大小
建议256K( 默认值)-> 1M 之内
当查询语句中有关联查询时,马上分配配置大小的内存用这个关联查询,所以有可能在一个查询语句中会分配很多个关联查询缓冲区
上述配置4000 连接占用内存:
4000*(0.256M+0.256M) = 2G

Innodb_buffer_pool_size
innodb buffer/cache 的大小(默认128M) )

Innodb_buffer_pool中存储:数据缓存,索引缓存,缓冲数据,内部结构
大的缓冲池可以减小多次磁盘I/O 访问相同的表数据以提高性能
参考计算公式:

Innodb_buffer_pool_size = (总物理内存 - 系统运行所用 - connection 所用)* 90%
wait_timeout
服务器关闭非交互连接之前等待活动的秒数

innodb_open_files
限制Innodb能打开的表的个数

innodb_lock_wait_timeout

InnoDB事务在被回滚之前可以等待一个锁定的超时秒数

redo log相关

innodb_log_files_in_group:指定Redo log日志文件组中的数量,默认为2

innodb_log_file_size:指定Redo log每一个日志文件最大存储量,默认48M

innodb_log_buffer_size:指定Redo log在cache/buffer中的buffer池大小,默认16M

Innodb_flush_log_at_trx_commit:Redo buffer持久化Redo log的策略。

取值 0 每秒提交 Redo buffer --> Redo log OS cache -->flush cache to disk[ 可能丢失一秒内的事务数据]

取值 1 是默认值,每次事务提交执行Redo buffer --> Redo log OS cache -->flush cache to disk[ 最安全,性能最差的方式]

取值 2 每次事务提交执行Redo buffer --> Redo log OS cache 再每一秒执行 ->flush cache to disk 操作

取值0,故障时最多损失1秒的数据;取值1,故障时最多损失1个事务;取值2,最多损失1秒数据,但是会减少os到disk的io次数,推荐2。默认1是因为innodb更倾向于安全,效果相差不是非常大时,innodb会选择更安全的策略。

【云栖快讯】阿里巴巴小程序繁星计划,20亿补贴第一弹云应用免费申请,限量从速!  详情请点击

网友评论