mysql常用服务配置_Mysql常见配置

配置文件所在位置

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

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会选择更安全的策略。