MySQL 5.7 配置

1.3、mysql优化配置（主从复制）

Mysql做主从复制，怎么么说呢，主要是为了安全，一般来说，在master有两台slave就可以了，已经足够应对很多的意外情况了。做主从复制和备份，要注意得是，主从两台mysql配置一样，对于备份的数据，不要放在mysql目录下，要另起路径，并给与mysql权限。配置可参考 mysql主从复制配置

1.4、mysql监控系统

对于Mysql做监控，个人认为是很有必要的，首先我们可以在无人值守的情况之下，我们可以对mysql的状态进行监控；通过监控，我们不但可以对mysql的负载情况进行告警，而且可以对mysql本身性能的一些优化处理，所以我在应用对mysql的监控中，使用了zabbix对负载情况做告警处理，并结合pmm-server对mysql系统的优化。对于pmm-sercer在这里先贴个图，如图1.2、1.3所示

 

 

图1.2 mysql资源数据图

 

图1.3 mysql资源数据图

由以上图可以发现，我们可以大体的可以看到mysql的资源配置情况，根据数值我们可以适当的优化，并调整一些mysql的自身的参数，这就是pmm-sever的监控的好处了。

对于zabbix，我现在就是拿来做告警处理，在对mysql的监控中，zabbix本身自带的模板和结合percona插件，基本实现对整个mysql的监控（配合可参考 percona监控mysql数据），因为两个结合基本比较全面的实现对mysql的监控了，如图1.4所示，可看到模板所提供的监控项。

 

图1.4 zabbix的mysql模板监控项

总结以上，我们可以发现，一个良好的mysql优化架构，需要做的包括redis做读写分离，mysql主从，监控系统完善等等。如果你还有跟好的方案，也记得分享一下；接下来，我们是针对mysql本身性能的优化，×××能的容错率，在基础上进一步提升mysql的性能。

2.、mysql自身的优化

总的来说还是自身因素影响的比较多，我们可以通过修改my.cnf配置文件来对mysql进行进一步的优化。我们可以通过修改mysql的参数使得mysql拥有更可靠的性能。下面是我的数据库配置，自己通过百度谷歌，找很多配置选项的解析（配置适合mysql5.5以上的版本），然后总结。希望对你有帮助。（注意一下优化配置均在【mysqld】选项下配置，不要搞错成【mysql】）

[mysqld]
back_log = 300
binlog_format = MIXED
character-set-server=utf8mb4
long_query_time = 1
log-bin=/databack/data_logbin/mysql_binlog
innodb_log_file_size=2G
innodb_log_buffer_size=4M
innodb_buffer_pool_size=4G
#innodb_file_per_table = ON
innodb_thread_concurrency=8
innodb_flush_logs_at_trx_commit=2
#innodb_additional_mem_pool_size=4M
join_buffer_size = 8M
key_buffer_size=256M
max_connections = 1000
max_allowed_packet = 4M
max_connect_errors = 10000
myisam_sort_buffer_size = 64M
port = 3306
query_cache_type=1
query_cache_size = 64M
read_buffer_size=4M
read_rnd_buffer_size=4M
server-id = 1
skip-external-locking
slow_query_log = 1
#skip-name-resolve
#skip-networking
sort_buffer_size = 8M
socket = /tmp/mysql.sock
table_open_cache=1024
thread_cache_size = 64
thread_stack = 256K
tmp_table_size=64M
wait_timeout = 10
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

下面是对上面配置的解析：

 

back_log = 300：该参数的值表示在MySql的连接数据达到#max_connections 时，在它暂时停止响应新请求之前的短时间内有300个请求可以被存在堆栈中，即新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，等 mysql处理完其他请求之后会对其作出响应，如果等待连接的数量超过#back_log，将不被授予连接资源。你可以合理的设置你的back_log，但是该值不要高于操作系统的限制的值。系统的默认值为50。Linux系统一般设置小于512的整数。

 

binlog_format = MIXED：配置主从模式下，选取同步的模式，Mysql主从的复制可以有三种复制类型，分别是:语句的复制STATEMEN,行的复制ROW和混合类型的复制MIXED，语句的复制顾名思义就是在主服务器上执行的SQL语句，在从服务器上执行同样的语句，行的复制就是把改变的内容复制过去，而不是把命令在从服务器上执行一遍。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置，复制类型可以通过binlog_format =在配置文件上配置

 

character-set-server=utf8mb4 ：utf-8编码可能2个字节、3个字节、4个字节的字符，但是MySQL的utf8编码只支持3字节的数据，而移动端的表情数据是4个字节的字符。如果直接往采用utf-8编码的数据库中插入表情数据，Java程序中将报SQL异常utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。 采用utf8mb4编码的好处是，存储与获取数据的时候，不用再考虑表情字符的编码与解码问题。

 

long_query_time = 1：设置慢查询响应的时间，记录超过1秒的SQL执行语句。

 

log-bin=/databack/data_logbin/mysql_binlog ：设置二进制日志的存放路径，如果不设置系统会默认存放到mysql的目录下，建议创建新的目录来存放二进制日志，且该目录不要同数据库同个目录，存放目录拥有者为mysql。

 

innodb_log_file_size=2G ：在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，跟据服务器大小而异。这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。在MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。而MySQL 5.6里可以设置允许大于4G。你可以一开始就把它设置成4G。这个值的设置其实是可以计算的 你可以通过命令SHOW GLOBAL STATUS的输出看Innodb_os_log_written的值，把该值除以1024*1024 得到的结果是每分钟处理的redo日志大小，然后再乘以60得到每小时处理的日志大小，因为在5.5以上版本都是默认有两个日志重做日志文件ib_logfile0和ib_logfile1，所得到结果再除以2，再取整就是你的redo该设置大小了。

 

innodb_log_buffer_size=4M：默认为1M，在默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。在 InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。通常情况下，如果不是写入大量的超大二进制数据，4MB-8MB已经足够了。

 

innodb_buffer_pool_size=4G：这配置对Innodb表来说非常重要。该参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲由于Innodb把数据和索引都缓存起来，因此在配置该参数时，可以设置它高达60-80% 的可用内存（官网是建议的也是系统内存的80%左右）。缓冲池是数据和索引缓存的地方这能保证你在大多数的读取操作时使用的是内存而不是硬盘。一般配置的值是5-6GB(8GB内存)，19-25GB(32GB内存)，38-50GB(64GB内存)仅供参考。

 

#innodb_file_per_table = ON：在5.6中，该选项属性默认值是ON，由于对新建的表有影响，所以在之前的版本中你需要把它设置成ON。这项设置告知InnoDB是否需要将所有表的数据单独放在一个.ibd文件，这样做的好处是使得每个表都有自已独立的表空间。每个表的数据和索引都会存在自已的表空间中。也实现单表在不同的数据库中移动，且空间可以回收。

 

innodb_thread_concurrency=8：指服务器逻辑线程数可以设置成与系统一样数量，参数可配置成逻辑CPU数量的两倍。

系统CPU查看命令如下：

查看逻辑CPU个数：

#cat /proc/cpuinfo |grep "processor"|sort -u|wc –l
1.

查看物理CPU个数：

# cat /proc/cpuinfo | grep "physical id" |sort -u|wc -l
1.
              

查看每个物理CPU内核个数：

# cat /proc/cpuinfo | grep "cpu cores" |uniq
1.

innodb_flush_logs_at_trx_commit=2：系统默认值是 1，但是这样设置会使得提交更新事务都会刷新到磁盘中，会造成资源耗费。所以需要值设置为 2，这样就不用不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。但然啦也可以设置为0， 这样设置是很快，但也造成了相对的不安全，会导致MySQL服务器崩溃时就会丢失一些事务。而设置为 2 刚好尼补了。

 

#innodb_additional_mem_pool_size=4M：该参数默认为1M适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的，主要用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。在mysql5.6.3可以忽略。

 

join_buffer_size = 8M：表示#联合查询操作所能使用的缓冲区大小。

 

key_buffer_size=256M：指定索引缓冲区的大小，它决定索引处理的速度，你可以设置成系统的物理内存的1/4，它主要针对的是MyISAM引擎，但是设置大少不要超过4G，不然会出现问题。

 

max_connections = 1000：设置置MySQL的最大连接，按你实际情况适当设置就好。如果你经常看到‘Too many connections'错误，是因为max_connections的值太低了，所以需要设置更高的链接数，如果max_connection值被设高之后的缺陷是当服务器运行超过设置阈值或更高的活动事务时会变的没有响应。

 

max_allowed_packet = 4M：这个参数mysql消息缓冲区的大小，如果这个过小可能会影响到部分操作，默认是1M，一般设置成4-16M就可以了。

 

max_connect_errors = 10000：表示如果有同一个主机访问的参数值超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

 

myisam_sort_buffer_size = 64M：这个参数默认是8M，表示MyISAM表发生变化时重新排序所需的缓冲，一般64M就已经足够了。

 

port = 3306：表示使用3306来做mysql启动端口

 

query_cache_type=1：表示控制缓存的类型，有三个参数可选（0、1、2）设置为0，表示缓存没有应用，也就相当于禁用了，设置为1，表示缓存所有的结果，设置为2表示只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

 

query_cache_size=32M：参数表示mysql查询结果的缓冲区大小，一般不建议设置太大，因为设置太大会增加开销，一般设置成32M-256M左右即可，设置参数一般为2的倍数。

 

read_buffer_size=4M：表示按顺序查询操作包括读、查询等操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享，一般不建议太大，对于4G到16G内存的服务器2M-8M就可以了。

 

read_rnd_buffer_size=4M：表示是MySQL的随机读缓冲区大小。当任意顺序读取行时将分配一个随机读取缓冲区，进行排序查询时，便分配随机缓冲作为该操作的缓冲区大小，同样的对于4G到16G内存的服务器2M-8M就可以了。

 

server-id = 1：表示做主从同步所定义的serverid，作为master的server_id必须必slave端的要小，越小表示优先级越高，但是在同个网段内的mysql服务，不允许设置同样的sever_id。参数可设参考范围（1-200）。

 

skip-external-locking：开启该选项表示避免MySQL的外部锁定，减少出错几率增强稳定性，适用于单服务器环境。

 

slow_query_log = 1：开启慢查询日志，作用于慢查询日志,顾名思义,就是查询慢的日志。

 

skip-name-resolve：禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求。

 

skip-networking：开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项，否则将无法正常连接。

 

sort_buffer_size = 8M：表示查询排序时所能使用的缓冲区大小。它直接与实时连接的个数 有关，实时连接的个数乘以sort_buffer_size的大小就是实际分配的总共排序缓冲区大小。所以，对于内存在4GB-8G左右的服务器可以设置为6-16M。

 

socket = /tmp/mysql.sock：mysql.sock 文件作用主要是server和client在同一台服务器，当使用本地连接时，就会使用socket进行连接，该文件一般是放在/var/lib/mysql/mysql.sock下，也常常使用ln –s 在/tmp目录下做软连接。

 

table_open_cache=1024：table_cache主要用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表，因此此参数的值与max_connections有关。你可以通过命令show variables like '%open%'; 查看open_files_limit参数,大量使用MyISAM的环境里，应该保证open_files_limit表类型至少是table_cache的二到三倍，调到512-1024最佳。

 

thread_cache_size = 64 ：这个变量值表示的是可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用 根据物理内存设置规则可以做以下配置2G-4G可以设置为16-64左右，当然大于4G的服务器，设置64也已经足够了。

 

thread_stack = 256K：表示每个连接线程被创建时，MySQL给它分配的内存大小，对于8-16G的服务器设置成256K就可以了，再大一点的，可以适当增加呢。

 

tmp_table_size=64M：表示定义一个临时表的大小，该值默认为16M，可调到64-256最佳，线程独占，太大可能内存不够造成I/O堵塞，如果动态页面可以适当调大点。

 

wait_timeout = 100：表示指定一个请求的最大连接时间，该值过大会导致，MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题。  系统默认是8个小时，感觉太大，可以设置小点。

 

3、总结

    预防Mysql病发的情况，是每个企业所要面对的事情，大数据的到来，更加使得mysql的性能要求更高，所以对mysql的优化升级，也是迫在眉睫。以上是本人总结，仅仅提供参考，希望能帮到你。

 

 

 

配置样例

首先提供一个我使用的配置样例

[client]  
#password=88888888  
socket=/data/var/mysql/mysql.sock  
  
[mysqld_safe]  
pid-file=/data/var/mysql/mysqld.pid  
log-error = /data/local/mysql-5.7.19/log/mysql-error.log  
  
[mysql]  
socket=/data/var/mysql/mysql.sock  
  
[mysqld]  
user = mysql  
port = 31306  
datadir = /data/var/mysql  
socket=/data/var/mysql/mysql.sock  
symbolic-links=0  
########basic settings########  
server-id = 11  
#bind_address = 10.166.224.32  
autocommit = 1  
character_set_server=utf8mb4  
skip_name_resolve = 1  
max_connections = 800  
max_connect_errors = 100  
transaction_isolation = READ-COMMITTED  
explicit_defaults_for_timestamp = 1  
join_buffer_size = 128M  
tmp_table_size = 128M  
tmpdir = /dev/shm  
max_allowed_packet = 16M  
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"  
interactive_timeout = 60  
wait_timeout = 60  
read_buffer_size = 16M  
read_rnd_buffer_size = 32M  
sort_buffer_size = 32M  
########log settings########  
#log_error = /data/local/mysql-5.7.19/log/mysql-error.log  
slow_query_log = 1  
slow_query_log_file = /data/local/mysql-5.7.19/log/mysql-slow.log  
log_queries_not_using_indexes = 1  
log_slow_admin_statements = 1  
log_slow_slave_statements = 1  
log_throttle_queries_not_using_indexes = 10  
expire_logs_days = 90  
long_query_time = 1  
min_examined_row_limit = 100  
########replication settings########  
#master_info_repository = TABLE  
#relay_log_info_repository = TABLE  
log_bin = /data/local/mysql-5.7.19/log/mysql-bin  
#sync_binlog = 4  
gtid_mode = on  
enforce_gtid_consistency = 1  
#log_slave_updates  
binlog_format = row  
#relay_log = /data/local/mysql-5.7.19/log/mysql-relay.log  
#relay_log_recovery = 1  
#binlog_gtid_simple_recovery = 1  
#slave_skip_errors = ddl_exist_errors  
########innodb settings########  
innodb_page_size = 16K  
innodb_buffer_pool_size = 4G  
#innodb_buffer_pool_instances = 8  
#innodb_buffer_pool_load_at_startup = 1  
#innodb_buffer_pool_dump_at_shutdown = 1  
#innodb_lru_scan_depth = 2000  
innodb_lock_wait_timeout = 5  
#innodb_io_capacity = 4000  
#innodb_io_capacity_max = 8000  
#innodb_flush_method = O_DIRECT  
#innodb_log_group_home_dir = /data/local/mysql-5.7.19/log/redolog/  
#innodb_undo_directory = /data/local/mysql-5.7.19/log/undolog/  
#innodb_undo_logs = 128  
#innodb_undo_tablespaces = 0  
#innodb_flush_neighbors = 1  
#innodb_log_file_size = 4G  
#innodb_log_buffer_size = 16M  
#innodb_purge_threads = 4  
innodb_large_prefix = 1  
innodb_thread_concurrency = 64  
#innodb_print_all_deadlocks = 1  
#innodb_strict_mode = 1  
innodb_sort_buffer_size = 64M  
########semi sync replication settings########  
#plugin_dir=/data/local/mysql-5.7.19/lib/plugin  
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"  
#loose_rpl_semi_sync_master_enabled = 1  
#loose_rpl_semi_sync_slave_enabled = 1  
#loose_rpl_semi_sync_master_timeout = 5000  
  
[mysqld-5.7]  
#innodb_buffer_pool_dump_pct = 40  
innodb_page_cleaners = 4  
#innodb_undo_log_truncate = 1  
#innodb_max_undo_log_size = 2G  
#innodb_purge_rseg_truncate_frequency = 128  
#binlog_gtid_simple_recovery=1  
log_timestamps=system  
#transaction_write_set_extraction=MURMUR32  
#show_compatibility_56=on  

详细解释

[client]

#password=88888888

mysql默认密码

socket=/data/var/mysql/mysql.sock

mysql以socket方式运行的sock文件位置

 

[mysqld_safe]

log-error=/var/log/mysqld.log

错误日志位置

pid-file=/var/run/mysqld/mysqld.pid

进程id文件

 

[mysql]

socket=/data/var/mysql/mysql.sock

mysql以socket方式运行的sock文件位置

 

[mysqld]

user = mysql

mysql以什么用户运行

port = 31306

mysql运行在哪个端口

datadir = /data/var/mysql/

mysql的数据目录

socket=/data/var/mysql/mysql.sock

mysql以socket方式运行的sock文件位置

symbolic-links=0

是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启

 

########basic settings########

server-id = 11

mysql的服务器分配id，在启用主从和集群的时候必须指定，每个节点必须不同

#bind_address = 10.166.224.32

mysql监听的ip地址，如果是127.0.0.1，表示仅本机访问

autocommit = 1

数据修改是否自动提交，为0不自动提交

character_set_server=utf8mb4

服务器使用的字符集

skip_name_resolve = 1

禁用DNS主机名查找，启用以后用内网地址向mysqlslap请求响应快了一半

max_connections = 800

mysql最大连接数

max_connect_errors = 1000

某台host连接错误次数等于max_connect_errors（默认10） ，主机'host_name'再次尝试时被屏蔽。可有效反的防止dos攻击

transaction_isolation = READ-COMMITTED

数据库事务隔离级别

1.READ-UNCOMMITTED(读取未提交内容)级别
2. READ-COMMITTED（读取提交内容）
3. REPEATABLE-READ(可重读)
4.SERIERLIZED(可串行化)
默认级别REPEATABLE-READ

explicit_defaults_for_timestamp = 1

mysql中TIMESTAMP类型和其他的类型有点不一样(在没有设置explicit_defaults_for_timestamp=1的情况下）

join_buffer_size = 128M

当我们的join是ALL,index,rang或者Index_merge的时候使用的buffer。 实际上这种join被称为FULL JOIN

tmp_table_size = 128M

规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

tmpdir = /dev/shm/mysql-tmp/

保存临时文件的目录

max_allowed_packet = 16M

mysql最大接受的数据包大小

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

sql_mode 模式，定义了你MySQL应该支持的sql语法，对数据的校验等等，限制一些所谓的‘不合法’的操作

interactive_timeout = 60

服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端

wait_timeout = 60

服务器关闭非交互连接之前等待活动的秒数，在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)

read_buffer_size = 16M

读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区

read_rnd_buffer_size = 32M

随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度

sort_buffer_size = 32M

是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存

 

########log settings########

#log_error = /data/local/mysql-5.7.19/log/mysql-error.log

错误日志位置

slow_query_log = 1

是否开启慢查询日志收集

slow_query_log_file = /data/local/mysql-5.7.19/log/mysql-slow.log

慢查询日志位置

log_queries_not_using_indexes = 1

是否记录未使用索引的语句

log_slow_admin_statements = 1

慢查询也记录那些慢的optimize table，analyze table和alter table语句

log_slow_slave_statements = 1

记录由Slave所产生的慢查询

log_throttle_queries_not_using_indexes = 10

设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间

expire_logs_days = 90

日志自动过期清理天数

long_query_time = 1

设置记录慢查询超时时间

min_examined_row_limit = 100

查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

 

########replication settings########

#master_info_repository = TABLE

从机保存主节点信息方式，设成file时 会生成master.info 和 relay-log.info2个文件，设成table，信息就会存在mysql.master_slave_info表中。不管是设置的哪种值，都不要移动或者编辑相关的文件和表

#relay_log_info_repository = TABLE

用于保存slave读取relay log的位置信息，可选值为“FILE”、“TABLE”，以便crash重启后继续恢复

log_bin = /data/local/mysql-5.7.19/log/mysql-bin

binlog的保存位置，不能指定确定的文件名如mysql-bin.log，只能指定位置和前缀，会生成以前缀为开头的一系列文件

#sync_binlog = 4

这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

gtid_mode = on

启用gtid类型，否则就是普通的复制架构

enforce_gtid_consistency = 1

强制GTID的一致性

#log_slave_updates

slave更新是否记入日志，在做双主架构时异常重要，影响到双主架构是否能互相同步

binlog_format = row

binlog日志格式，可选值“MIXED”、“ROW”、“STATEMENT”，在5.6版本之前默认为“STATEMENT”，5.6之后默认为“MIXED”；因为“STATEMENT”方式在处理一些“不确定”性的方法时会造成数据不一致问题，我们建议使用“MIXED”或者“ROW”

#relay_log = /data/local/mysql-5.7.19/log/mysql-relay.log

从机保存同步中继日志的位置

#relay_log_recovery = 1

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性

#binlog_gtid_simple_recovery = 1

这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件的。 这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快

#slave_skip_errors = ddl_exist_errors

跳过指定error no类型的错误，设成all 跳过所有错误

 

########innodb settings########

innodb_page_size = 16K

innodb每个数据页大小，这个参数在一开始初始化时就要加入my.cnf里，如果已经创建了表，再修改，启动MySQL会报错

innodb_buffer_pool_size = 4G

缓存innodb表的索引，数据，插入数据时的缓冲，专用mysql服务器设置的大小： 操作系统内存的70%-80%最佳

#innodb_buffer_pool_instances = 8

可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写

#innodb_buffer_pool_load_at_startup = 1

默认为关闭OFF。如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中

#innodb_buffer_pool_dump_at_shutdown = 1

默认为关闭OFF。如果开启该参数，停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘

#innodb_lru_scan_depth = 2000

根据 官方文档 描述，它会影响page cleaner线程每次刷脏页的数量， 这是一个每1秒 loop一次的线程

innodb_lock_wait_timeout = 5

事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒

#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000

这两个设置会影响InnoDB每秒在后台执行多少操作. 大多数写IO(除了写InnoDB日志)是后台操作的. 如果你深度了解硬件性能(如每秒可以执行多少次IO操作),则使用这些功能是很可取的,而不是让它闲着

#innodb_flush_method = O_DIRECT

默认值为 fdatasync. 如果使用 硬件RAID磁盘控制器, 可能需要设置为 O_DIRECT. 这在读取InnoDB缓冲池时可防止“双缓冲(double buffering)”效应,否则会在文件系统缓存与InnoDB缓存间形成2个副本(copy). 如果不使用硬件RAID控制器,或者使用SAN存储时, O_DIRECT 可能会导致性能下降

#innodb_log_group_home_dir = /data/local/mysql-5.7.19/log/redolog/

innodb重做日志保存目录

#innodb_undo_directory = /data/local/mysql-5.7.19/log/undolog/

innodb回滚日志保存目录

#innodb_undo_logs = 128

undo回滚段的数量， 至少大于等于35，默认128

#innodb_undo_tablespaces = 0

用于设定创建的undo表空间的个数，在mysql_install_db时初始化后，就再也不能被改动了；默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为4，那么就会创建命名为undo001~undo004的undo tablespace文件，每个文件的默认大小为10M。修改该值会导致Innodb无法完成初始化，数据库无法启动，但是另两个参数可以修改

#innodb_flush_neighbors = 1

InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。

#innodb_log_file_size = 4G

这个值定义了日志文件的大小，innodb日志文件的作用是用来保存redo日志。一个事务对于数据或索引的修改往往对应到表空间中的随机的位置，因此当刷新这些修改到磁盘中就会引起随机的I/O，而随机的I/O往往比顺序的I/O更加昂贵的开销，因为随机的I/O需要更多的开销来定位到指定的位置。innodb使用日志来将随机的I/O转为顺序的I/O，只要日志文件是安全的，那么事务就是永久的，尽管这些改变还没有写到数据文件中，如果出现了当机或服务器断电的情况，那么innodb也可以通过日志文件来恢复以及提交的事务。但是日志文件是有一定的大小的，所以必须要把日志文件记录的改变写到数据文件中，innodb对于日志文件的操作是循环的，即当日志文件写满后，会将指针重新移动到文件开始的地方重新写，但是它不会覆盖那些还没有写到数据文件中的日志，因为这是唯一记录了事务持久化的记录

如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了，就会增加恢复的时间，因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复

#innodb_log_buffer_size = 16M

事务在内存中的缓冲。 分配原 则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次

#innodb_purge_threads = 4

控制是否使用，使用几个独立purge线程（清除二进制日志）

innodb_large_prefix = 1

mysql在5.6之前一直都是单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.6以后，开始支持4个字节的uutf8。255×4>767, 于是增加了这个参数。这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072

innodb_thread_concurrency = 64

InnoDB kernel并发最大的线程数。 1) 最少设置为(num_disks+num_cpus)*2。 2) 可以通过设置成1000来禁止这个限制

#innodb_print_all_deadlocks = 1

是否将死锁相关信息保存到MySQL 错误日志中

#innodb_strict_mode = 1

开启InnoDB严格检查模式，尤其采用了页数据压缩功能后，最好是开启该功能。开启此功能后，当创建表（CREATE TABLE）、更改表（ALTER TABLE）和创建索引（CREATE INDEX）语句时，如果写法有错误，不会有警告信息，而是直接抛出错误，这样就可直接将问题扼杀在摇篮里

innodb_sort_buffer_size = 64M

ORDER BY 或者GROUP BY 操作的buffer缓存大小

########semi sync replication settings########

#plugin_dir=/data/local/mysql-5.7.19/lib/plugin

指定mysql的插件目录

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

指定载入哪些插件

#loose_rpl_semi_sync_master_enabled = 1

控制主库上是否开启semisync

#loose_rpl_semi_sync_slave_enabled = 1

控制备库是否开启semisync

#loose_rpl_semi_sync_master_timeout = 5000

单位毫秒，防止半同步复制在没有收到确认的情况下，发送堵塞。master在超时之前没有收到确认，将恢复到异步复制，继续执行半同步没有进行的操作

 

[mysqld-5.7]

#innodb_buffer_pool_dump_pct = 40

表示转储每个bp instance LRU上最热的page的百分比。通过设置该参数可以减少转储的page数

innodb_page_cleaners = 4

为了提升扩展性和刷脏效率，在5.7.4版本里引入了多个page cleaner线程。从而达到并行刷脏的效果

在该版本中，Page cleaner并未和buffer pool绑定，其模型为一个协调线程 + 多个工作线程，协调线程本身也是工作线程。因此如果innodb_page_cleaners设置为8，那么就是一个协调线程，加7个工作线程

#innodb_undo_log_truncate = 1

是否开启在线回收（收缩）undo log日志文件，支持动态设置

#innodb_max_undo_log_size = 2G

当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M

#innodb_purge_rseg_truncate_frequency = 128

控制回收（收缩）undo log的频率。undo log空间在它的回滚段没有得到释放之前不会收缩， 想要增加释放回滚区间的频率，就得降低设定值

#binlog_gtid_simple_recovery=1

这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件的。 这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快。该参数为真时，mysql-server只需打开最老的和最新的这2个binlog文件

log_timestamps=system

在MySQL 5.7.2 新增了 log_timestamps 这个参数，该参数主要是控制 error log、genera log，等等记录日志的显示时间参数。 在 5.7.2 之后改参数为默认 UTC 这样会导致日志中记录的时间比中国这边的慢，导致查看日志不方便。修改为 SYSTEM 就能解决问题

#transaction_write_set_extraction=MURMUR32

这个神奇的参数5.7.6版本引入，用于定义一个记录事务的算法，这个算法使用hash标识来记录事务。如果使用MGR，那么这个hash值需要用于分布式冲突检测何处理，在64位的系统，官网建议设置该参数使用 XXHASH64 算法。如果线上并没有使用该功能，应该设为off

#show_compatibility_56=on

从mysql5.7.6开始information_schema.global_status已经开始被舍弃，为了兼容性，此时需要打开 show_compatibility_56

 

#table_open_cache = 512

table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。

可通过 SHOW GLOBAL STATUS LIKE 'Open%tables'; 获得

 

注意，不能盲目地把table_open_cache设置成很大的值，设置太大超过了shell的文件描述符(通过ulimit -n查看)，造成文件描述符不足，从而造成性能不稳定或者连接失败。