Last Updated on
前言
Mysql的配置对于mysql服务非常重要，一般在大公司，会有专门的DBA来管理数据库，包括数据库性能优化等，但是很多中小型公司，没有此岗位，所以很多时候mysql的性能问题也落在了运维身上，了解一些mysql性能优化的东西也能让我们更好的理解mysql。
下面为个人列举的一些常用的主要的配置，仅供参考。
正文
下面直接修改你的mysql配置文件my.cnf，根据实际情况，进行性能优化
环境： 8核16G centos7
# 修改配置文件
$ vim /etc/my.cnf下面为配置文件内容：
[mysql]
default-character-set=utf8
[mysqld]
################################# 基础配置信息 #################################
# 如果是yum安装的，以下三项不修改，直接使用默认的，胡乱修改的话容易出错。
user = mysql01
basedir = /usr/local/mysql
datadir = /data0/mysql/data
port = 3306
pid-file = /data0/mysql/data/mysql.pid
# 日志格式与路径
binlog_format=row
log_bin = /data0/mysql/data/mysql-bin
log-error = /data0/mysql/data/error.log
# 默认编码设为UTF8
character-set-server=utf8
# 表名忽略大小写，设置后所有大写都会变成小写
lower_case_table_name=1
# 数据库引擎innodb
default-storage-engine=INNODB
# binlog日志过期时间
expire_logs_days=30
# 开启慢查询日志
slow_query_log=ON
slow_query_log_file=/data0/mysql/data/slow_query.log
long_query_time=10 # 慢查询阈值时间，单位秒
# 禁用DNS反解析
skip-name-resolve
# 最大连接数
max_connections = 3000
# 最大错误连接数阈值，超过这个数的连接失败就会被拒绝报错。
max_connect_errors = 10000
# 最大允许的写入数据大小，超过会导致写入失败，且后续记录写入也会失败。为了数据完整性和业务情况。可设置较大。
max_allowed_packet = 32M
# 非交互式连接超时时间，默认28800 = 八小时
wait_timeout = 31536000
# 交互式连接超时时间，默认28800 = 八小时
interactive_timeout = 31536000
################################## 主从设置 ###################################
# 服务ID号
server_id =2
# 同步日志 relay0-log
relay-log = /data0/mysql/data/relay-bin
# 从库从relay-log中执行同步运行的的sql，是否写入bin-log日志，默认为0，不写入，这会导致从库无法作为主库，实现A->B-C的同步。
# 需要多级同步时，推荐开启
log_slave_updates = 1
# max_relay_log_size 日志文件最大大小，默认为1G
# 设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置）
relay-log-info-file = /data0/mysql/data/relay-log.info
# 当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，
# 并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，
# 将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。
relay_log_recovery = 1
# 将主从信息存放在表中， PS：不要手动去修改表中数据。需要修改时用命令行修改change
master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
# 当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，
# 这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。
# 当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。
# 这个值默认是0，可动态修改，建议采用默认值。
sync_relay_log = 0
# 这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，
# 然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。
# 当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。
# 这个值默认是0，可动态修改，建议采用默认值。
sync_relay_log_info = 0
# 是否自动清空不再需要中继日志时。默认值为1(启用)
relay_log_purge =1
## 在配置主主复制时，为了避免两台服务器同时做更新时自增长字段的值之间发生冲突。
## 需要将两台服务器的自增长步长都设为2，起始值一个为1，一个为2
# 自增长主键的步长，默认为1
auto-increment-increment = 1
# 自增长主键的其起始值，默认为1
auto-increment-offset = 1
# 异步复制（Asynchronous replication）
# MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，
# 这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
# 全同步复制（Fully synchronous replication）
# 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，
# 所以全同步复制的性能必然会收到严重的影响。
# 半同步复制（Semisynchronous replication）
# 介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，
# 而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，
# 同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
# 要想使用半同步复制，必须满足以下几个条件：
# 1. MySQL 5.5及以上版本
# 2. 变量have_dynamic_loading为YES
# 3. 异步复制已经存在
# 4. 需要在主库和从库上安装相应插件
# 有时，在高可用的架构中，需要进行主从切换，常常是两个都开启，以便在其中一台服务器挂掉时，另一台接手主库进行写入操作。
# 主库配置，启动半同步
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
# 从库配置，启动半同步
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
# 在开启了主从同步，bin-log日志的服务器上，为避免潜在数据安全，默认情况下会阻止函数的创建，有两种方法可以解决这个问题
# 1. 设置如下参数，另外如果是在master上创建函数，想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON，否则主从复制会报错。
# 2. 明确指明函数的类型，是这三种类型之一：DETERMINISTIC：确定的 ，NO SQL：没有sql语句的 或 READS SQL DATA：只是读取数据
# log_bin_trust_function_creators = 1
##################################### 性能优化 #####################################
# 参数为0时：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。
# 该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
# 参数为1时：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
# 参数为2时：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。
# 该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
# 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
# 当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
# 当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
# 推荐设置为 2
innodb_flush_log_at_trx_commit = 1
# 控制mysql怎么刷新二进制日志到磁盘，默认是0，意味着mysql并不刷新，由操作系统自己决定什么时候刷新缓存到磁盘进行持久化，
# 这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
# 多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
# 所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。
# 根据业务情况，推荐设置为0 或 0-1000
sync_binlog = 1
# 推荐配置组合：
# N=1,1 — 适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统；
# N=1,0 — 适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制；
# N=2,0或2,m(0<m<100) — 适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受；
# N=0,0 — 磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务；
# 数据安全性
# 双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，
# 推荐的做法是innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。
# back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。默认50
# 也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log.
back_log = 500
# 缓存innodb表的索引，数据，插入数据时的缓冲总大小。是innodb最重要的一个配置。可设置的总内存的50%-80%。
innodb_buffer_pool_size = 8G
# 用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。通常设置16Ｍ够用了，如果表比较多，可以适当的增大。
# 此配置在5.6.3以后的版本中不再需要，废弃的配置。
innodb_additional_mem_pool_size = 16M
# 重做日志大小，用于mysql崩溃等的重做恢复等，小日志文件使写入速度更慢，崩溃恢复速度更快，大日志文件使写入更快，崩溃恢复速度更慢
# 对于较稳定的数据库服务来说，如果有高可用，有备份，对于数据恢复要求不高的，可以适当增大日志文件大小，提升性能。
# mysql5.6 以上版本，最大上限为512G。以前版本最大上限为4G
innodb_log_file_size = 2G
# 日志组，默认为2，一般不用修改，保持默认即可。
innodb_log_files_in_group = 2
# 这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，
# 这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。
# 一般4-8M即可够用。
innodb_log_buffer_size = 4M
# 独立表空间，默认是打开的
innodb_file_per_table =1
# 同时打开的表的最大数，默认为2000，与性能影响不大。崩溃恢复时数越大，恢复越快。
innodb_open_files = 2000
# 同一时刻能够进入innodb层次并发执行的线程数，最好不要超过cpu核数太多，比如1.5倍cpu核数，一般等于cpu核数即可。
innodb_thread_concurrency = 8
# 后台服务的读写io线程数，根据cpu核数进行设置，cpu总线程数=read+write 这样会比较好发挥多核cpu的性能。
# 默认都是4
Innodb_read_io_threads = 4
innodb_write_io_threads =4
# InnoDB 缓冲池划分 的区域数量。通过在不同线程读取和写入缓存页面时减少争用，将缓冲池划分为不同的实例可以提高并发性
# 如果innodb_buffer_pool_size > 1G .则默认为8，否则为1。取默认值即可。或可考虑根据cpu核数进行设置。
innodb_buffer_pool_instances = 8
# 专门用于InnoDB 清除操作的后台线程的数量 。默认值和最小值1表示清除操作总是由后台线程执行，而不是主线程的一部分 。
# 在一个或多个后台线程中运行清除操作有助于减少内部争用 InnoDB，提高可伸缩性。将该值增加到大于1会创建多个单独的清除线程，
# 这可以提高在多个表上执行DML操作的系统的效率 。最大值是32。
innodb_purge_threads = 1
# Fdatasync,如果为空，默认使用此选项，会将io操作保存再系统缓存中，带一定数量后一次刷新
# O_DSYNC，使用系统缓存，但是必须要io操作刷新到磁盘后，才会返回io操作成功。
# O_DIRECT，不使用系统缓存，直接进行io操作，刷新到磁盘。
# 默认为空
innodb_flush_method = NULL
# join操作的缓冲空间大小，增大可有效提高多join操作的sql语句的速度
join_buffer_size = 3M
# 查询过程种生产的临时表缓存空间大小，超过则会生产基于disk的临时表，
# show global status like ‘created_tmp%‘; 查看生产的临时表情况
# 每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,
# 比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
tmp_table_size = 64M
# 定义了用户可以创建的内存表(memory table)的大小，这个值和上面值的最小值才内存临时表的最大值，低于这个值的零食表才会放内存。
max_heap_table_size = 32M
# bin-log日志缓存的大小，用于保存事务期间对二进制日志的更改，一般1M足以。
# show global status like '%binlog_cache%'; 可以查看到binlog缓存使用内存和disk的次数，disk占比过大时则需调高此参数
binlog_cache_size = 1M
# 用于控制缓存的类型和开关，0：禁用 1：将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
# 2：则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_type = 2
# 查询缓存的总大小，超过这个数则不会再缓存查询缓存
query_cache_size = 256M
# 单挑查询最大的缓存大小，超过这个大小的查询不会被缓存
query_cache_limit = 4M
# show status like ‘%Qcache%’;查看查询缓存使用状态值:
# Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目
# Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量
# Qcache_hits：Query Cache 命中次数
# Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
# Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
# Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
# Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量
# Qcache_total_blocks：Query Cache 中总的 Block 数量
# 多线程并发复制sql，可以降低从库对主从的同步延迟，但在5.6中的并发是对不同库之间的并行，对于同库不同表则还是单线程，所以并不能很好的解决延迟问题
# 在mysql5.7中引入了基于组提交的并行复制，
# slave_parallel_workers = 3
# slave_parallel_type参数是5.7中引入的，参数默认为database则5.6中基于数据库的并行。需要设置为基于组的并行才能最大化从库对于主库的延迟。
# slave_parallel_type = LOGICAL_CLOCK
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# read_buffer_size = 3M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
结束
ok，以上是个人的一些常用的简单的mysql的配置优化项，注意版本为mysql5.6，不同的版本，配置项可能会有一些不同，需要注意以下。
有什么问题，欢迎留言
