自动生成cnf配置文件：http://imysql.com/my-cnf-wizard.html

常用配置：

[mysql]字段

no-auto-rehash#开启命令补全

[mysqld]字段

#port=3306#默认#bind-address=0.0.0.0#默认pid-file=/data/mysql/mysql.pidbasedir=/usr/local/mysql/symbolic-link=0#等同于skip-symbolic-links，不能使用连接文件，多个客户可能会访问同一个数据库，因此这防止外部客户锁定MySQL服务器。该选项默认开启#tmpdir=/usr/local/mysql/tmp/#此目录被MySQL用来保存临时文件.例如,它被用来处理基于磁盘的大型排序,如果你不创建非常大的临时文件,将其放置到swapfs/tmpfs文件系统上也许比较好。另一种选择是你也可以将其放置在独立的磁盘上.你可以使用”;”来放置多个路径，他们会按照roud-robin方法被轮询使用.open_files_limit=65535#MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit-n的值，哪个大用哪个，当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。需要修改服务脚本，例centos7中/etc/systemd/system/mysql.service在mysql.service中最后一行添加LimitNOFILE=65535重启服务，修改了mysql.server有可能需要systemctldaemon-reload同时ulimit-n65535重启后失效所以还需要修改系统配置文件修改系统配置文件/etc/security/limits.conf*softnofile65535*hardnofile65535datadir=/data/mysqllog-error=/data/mysql/error.logslow_query_log=on#慢查询日志相关long_query_time=2#默认10秒slow_query_log_file=/var/log/mariadb/slow_query.loglog-queries-not-using-indexes#如果运行的SQL语句没有使用索引，则mysql数据库同样会将这条SQL语句记录到慢查询日志文件中。#log_output=FILE#参数log_output指定了慢查询输出的格式，默认为FILE，你可以将它设为TABLE，然后就可以查询mysql架构下的slow_log表了

socket=/var/lib/mysql/mysql.sock#该条配置需在[client]段同时配置，port也要配置。否则可能出错default_storage_engine=InnoDBinnodb_file_per_table=on#InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间innodb_buffer_pool_size=4G#生产中要改，建议为操作系统内存的70%-80%，需重启服务生效skip_name_resolve=on#忽略主机名解析，提高访问速度（注意配置文件中使用主机名将不能解析）lower_case_table_names=1#忽略表单大小写character-set-server=utf8mb4#设定默认字符为utf8mb4

1.使用innodb注意事项

a)所有InnoDB数据表都创建一个和业务无关的自增数字型作为主键，对保证性能很有帮助；b)杜绝使用text/blob，确实需要使用的，尽可能拆分出去成一个独立的表；c)时间戳建议使用TIMESTAMP类型存储；d)IPV4地址建议用INTUNSIGNED类型存储；e)性别等非是即非的逻辑，建议采用TINYINT存储，而不是CHAR(1)；例可以使用0,1,2来表示，未知,男,女，优点搜索快，缺点显示/存储都要转换f)存储较长文本内容时，建议采用JSON/BSON格式存储；

2.查询缓存相关

query_cache_type=1#0表示禁用缓存，1表示会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存，2表示只缓存在select语句中通过SQL_CACHE指定需要缓存的查询query_cache_limit=2M#不缓存查询大于该值的结果.只有小于此设定值的结果才会被缓冲,此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.query_cache_size=64M或32M或128M#此值为0表示未启用查询缓存功能，查看是否支持SHOWVARIABLESLIKE'have_query_cache';值为yes表示支持查询缓存缓存相关字段意义showstatuslike‘%Qcache%’;Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。Qcache_free_memory剩余查询缓存大小，根据此值调整query_cache_sizeQcache_hits:表示有多少次命中缓存Qcache_inserts:表示多少次未命中然后插入，SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小

3.系统资源相关

back_log=500#接受队列，对于没建立tcp连接的请求队列放入缓存中，队列大小为back_log，受限制与OS参数，查看方式cat/proc/sys/net/ipv4/tcp_max_syn_backlog。可以编辑/etc/sysctl.conf去调整它。如：net.ipv4.tcp_max_syn_backlog=2048，改完后执行sysctl-p让修改立即生效。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。如果系统在一个短时间内有很多连接，则需要增大该参数的值max_connections=1000#指定MySQL允许的最大连接进程数。如果在访问数据库时经常出现"TooManyConnections"的错误提示，默认100生产则需要增大该参数值。MySQL服务器允许的最大连接数16384

4.二进制日志相关

server_id=1log_bin=master-bin#log-bin-index=master-bin.indexexpire_logs_days=7#binlog_format=row#默认为mix，新版中设为这两项可提高安全性#binlog_row_image=minimalmax_binlog_size=100m#默认是1Gbinlog_cache_size=4m#binlog-do-db=DBNAME#指定mysql的binlog日志只记录哪个库max_binlog_cache_size=512m#生产4g#skip-slave-start#注意：当从库有数据恢复时，从库应该关闭slave进程自动启动避免数据不一致，要在从库上手动启动。

5.附配置主从相关操作：

准备工作：

确认开启binlog #show global variables like 'log_bin';

记录二进制日志文件及编号：show master status;

主节点创建有复制权限的用户账号

GRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*TO'repluser'@'192.168.5.%'identifiedby'repluser';flushprivileges;

从节点配置：

server_id=2relay_log=relay-log#开启中继日志#max_relay_log_size=200M#标记relaylog允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；relay_log_index=relay-log.indexread_only=ON#重启生效，对有super或allprivileges权限的用户不受限制#replicate_wild_do_table=DB_NAME.%#仅复制某库某表,加wild可以使用通配符#replicate_wild_ignore_table=DB_NAME.%#忽略复制某库某表。忽略某库replicate_ignore_db，或者主节点指定数据库不记录某库的binlog，binlog-ignore-db=#slave_skip_errors=all

#定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors。

默认情况下该参数值是off，我们可以列出具体的error code，也可以选择all，mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）

一些error code代表的错误如下：

1007：数据库已存在，创建数据库失败1008：数据库不存在，删除数据库失败1050：数据表已存在，创建数据表失败1051：数据表不存在，删除数据表失败1054：字段不存在，或程序文件跟数据库有冲突1060：字段重复，导致无法插入1061：重复键名1068：定义了多个主键1094：位置线程ID1146：数据表缺失，请恢复数据库1053：复制过程中主服务器宕机1062：主键冲突Duplicateentry'%s'forkey%d

#从相关操作

指定主节点

changemastertomaster_host='192.168.5.106',master_user='repluser',master_password='repluser',master_log_file='master_bin.000002',master_log_pos=343;#MASTER_PORT=默认3306,startslave[[IO_THREAD|SQL_THREAD]#启动复制线程检查：showslavestatus;如下则正常启动Slave_IO_Running=yesSlave_SQl_Running=yesSeconds_Behind_Master:0#落后主库的秒数，0比较正常看从库relay-log.info给sql线程用记录上一次同步到的位置，一般情况主从切换后，需要resetslave，清除relay-log.info。skip-slave-start#注意：当从库有数据恢复时，从库应该关闭slave进程自动启动避免数据不一致，要在从库上手动启动。

安装谷歌插件实现半同步复制：

Master：installpluginrpl_semi_sync_mastersoname'semisync_master.so';SETGLOBALrpl_semi_sync_master_enabled=1;Slave:installpluginrpl_semi_sync_slavesoname'semisync_slave.so';SETGLOBALrpl_semi_sync_slave_enabled=1;#备库而言，为了保证半同步立即生效，需要重启slave的IO线程

安装插件后：

master在配置文件添加

rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000#单位是毫秒，默认是10000。master等待超时，则切换为普通的异步复制。

slave添加

rpl_semi_sync_slave_enabled=1

笔者不常使用待发掘的配置：

[mysqld]

slave-load-tmpdir=/usr/local/mysql/tmp/#当slave执行loaddatainfile时用skip-external-locking#不使用系统锁定，要使用myisamchk,必须关闭服务器,避免MySQL的外部锁定，减少出错几率增强稳定性。skip-networking#开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！如果所有的进程都是在同一台服务器连接到本地的mysqld,这样设置将是增强安全的方法。sysdate-is-now=1#把SYSDATE函数编程为NOW的别名default-time-zone=system#服务器时区，或者'+08:00'default_table_type=InnoDB#默认表类型default-storage-engine=InnoDB#默认存储引擎

#系统资源相关

max_connect_errors=10000#如果某个用户发起的连接error超过该数值，则该用户的下次连接将被阻塞，直到管理员执行flushhosts命令或者服务重启，防止非法的密码以及其他在链接时的错误会增加此值connect-timeout=10#连接超时之前的最大秒数,在Linux平台上，该超时也用作等待服务器首次回应的时间wait-timeout=28800#等待关闭连接的时间interactive-timeout=28800#关闭连接之前，允许interactive_timeout（取代了wait_timeout）秒的不活动时间。客户端的会话wait_timeout变量被设为会话interactive_timeout变量的值。如果前端程序采用短连接，建议缩短这2个值,如果前端程序采用长连接，可直接注释掉这两个选项，默认配置(8小时)slave-net-timeout=600#从服务器也能够处理网络连接中断。但是，只有从服务器超过slave_net_timeout秒没有从主服务器收到数据才通知网络中断net_read_timeout=30#从服务器读取信息的超时net_write_timeout=60#从服务器写入信息的超时net_retry_count=10#如果某个通信端口的读操作中断了，在放弃前重试多次。net_buffer_length=16384#包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节max_allowed_packet=64M#服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)，每个连接独立的大小.大小动态增加。设置最大包,限制server接受的数据包大小，避免超长SQL的执行有问题默认值为16M，当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时，将发出“信息包过大”错误，并关闭连接。对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与MySQL服务器的连接”错误。默认值16M。table_cache=512#所有线程所打开表的数量.增加此值就增加了mysqld所需要的文件描述符的数量这样你需要确认在[mysqld_safe]中“open-files-limit”变量设置打开文件数量允许至少4096thread_stack=192K#线程使用的堆大小.此容量的内存在每次连接时被预留.MySQL本身常不会需要超过64K的内存如果你使用你自己的需要大量堆的UDF函数或者你的操作系统对于某些操作需要更多的堆,你也许需要将其设置的更高一点.默认设置足以满足大多数应用thread_cache_size=20#在cache中保留多少线程用于重用.当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size,则客户端线程被放入cache中.这可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)thread_concurrency=8#允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.该参数取值为服务器逻辑CPU数量×2query_cache_min_res_unit=2K#查询缓存分配的最小块大小.默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks*100%如果查询缓存碎片率超过20%，可以用FLUSHQUERYCACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。查询缓存利用率=(query_cache_size–Qcache_free_memory)/query_cache_size*100%查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多。查询缓存命中率=(Qcache_hits–Qcache_inserts)/Qcache_hits*100%tmp_table_size=512M#临时表的最大大小，如果超过该值，则结果放到磁盘中,此限制是针对单个表的,而不是总和max_heap_table_size=512M#独立的内存表所允许的最大容量.此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源

#日志相关

log_slave_updates=1#表示slave将复制事件写进自己的二进制日志log-warnings=1log_long_format#在慢速日志中记录更多的信息.一般此项最好打开，打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里min_examined_row_limit=1000　　　　#记录那些由于查找了多余1000次而引发的慢查询long-slow-admin-statements　　　　#记录那些慢的optimizetable，analyzetable和altertable语句log-slow-slave-statements#记录由Slave所产生的慢查询general_log=1#将所有到达MySQLServer的SQL语句记录下来,默认关闭general_log_file=/usr/local/mysql/log/mysql.log#general_log路径relay-log-purge=1#是否自动清空不再需要中继日志时。默认值为1(启用)

#MyISAM 相关选项

key_buffer_size=256M#指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的key_buffer_size可以设置较小，8MB已足够。如果是以MyISAM引擎为主，可设置较大，但不能超过4G.在这里，强烈建议不使用MyISAM引擎，默认都是用InnoDB引擎.sort_buffer_size=2M#查询排序时所能使用的缓冲区大小。排序缓冲被用来处理类似ORDERBY以及GROUPBY队列所引起的排序.一个用来替代的基于磁盘的合并分类会被使用.查看“Sort_merge_passes”状态变量.在排序发生时由每个线程分配注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100×6＝600MB,所以,对于内存在4GB左右的服务器推荐设置为6-8M。read_buffer_size=2M#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！用来做MyISAM表全表扫描的缓冲大小.当全表扫描需要时,在对应线程中分配.join_buffer_size=8M#联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!此缓冲被使用来优化全联合(fullJOINs不带索引的联合).类似的联合在极大多数情况下有非常糟糕的性能表现,但是将此值设大能够减轻性能影响.通过“Select_full_join”状态变量查看全联合的数量，当全联合发生时,在每个线程中分配。read_rnd_buffer_size=8M#MyISAM以索引扫描(RandomScan)方式扫描数据的buffer大小bulk_insert_buffer_size=64M#MyISAM使用特殊的类似树的cache来使得突发插入(这些插入是,INSERT…SELECT,INSERT…VALUES(…),(…),…,以及LOADDATAINFILE)更快.此变量限制每个进程中缓冲树的字节数.设置为0会关闭此优化.为了最优化不要将此值设置大于“key_buffer_size”.当突发插入被检测到时此缓冲将被分配MyISAM用在块插入优化中的树缓冲区的大小。注释：这是一个perthread的限制（bulk大量）.此缓冲当MySQL需要在REPAIR,OPTIMIZE,ALTER以及LOADDATAINFILE到一个空表中引起重建索引时被分配.这在每个线程中被分配.所以在设置大值时需要小心myisam_sort_buffer_size=64M#MyISAM设置恢复表之时使用的缓冲区的尺寸,当在REPAIRTABLE或用CREATEINDEX创建索引或ALTERTABLE过程中排序MyISAM索引分配的缓冲区。myisam_max_sort_file_size=10G#mysql重建索引时允许使用的临时文件最大大小myisam_repair_threads=1#如果该值大于1，在Repairbysorting过程中并行创建MyISAM表索引(每个索引在自己的线程内).如果一个表拥有超过一个索引,MyISAM可以通过并行排序使用超过一个线程去修复他们.这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.myisam_recover=64K#允许的GROUP_CONCAT()函数结果的最大长度transaction_isolation=REPEATABLE-READ#设定默认的事务隔离级别.可用的级别如下:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE1.READUNCOMMITTED-读未提交2.READCOMMITTE-读已提交3.REPEATABLEREAD-可重复读4.SERIALIZABLE-串行

#INNODB 相关选项

skip-innodb#如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,使用此选项会节省内存以及磁盘空间,并且加速某些部分innodb_status_file=1#启用InnoDB的statusfile，便于管理员查看以及监控等showengineinnodbstatus\Ginnodb_open_files=2048#限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300innodb_additional_mem_pool_size=100M#设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQLInstance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。innodb_write_io_threads=4innodb_read_io_threads=4#innodb使用后台线程处理数据页上的读写I/O(输入输出)请求,根据你的CPU核数来更改,默认是4#注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从1-64innodb_data_home_dir=/usr/local/mysql/var/#设置此选项如果你希望InnoDB表空间文件被保存在其他分区.默认保存在MySQL的datadir中.innodb_data_file_path=ibdata1:500M;ibdata2:2210M:autoextend#InnoDB将数据保存在一个或者多个数据文件中成为表空间.如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.其他情况下.每个设备一个文件一般都是个好的选择.你也可以配置InnoDB来使用裸盘分区innodb_file_io_threads=4#用来同步IO操作的IO线程的数量.此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好.innodb_thread_concurrency=16#在InnoDb核心内的允许线程数量,InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制,最优值依赖于应用程序,硬件以及操作系统的调度方式.过高的值可能导致线程的互斥颠簸.默认设置为0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量innodb_flush_log_at_trx_commit=1#如果设置为1,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的ACID行为.如果你愿意对事务安全折衷,并且你正在运行一个小的食物,你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.innodb_log_buffer_size=8M#用来缓冲日志数据的缓冲区的大小.当此值快满时,InnoDB将必须刷新数据到磁盘上.由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)innodb_log_file_size=500M#事物日志大小.在日志组中每个日志文件的大小，你应该设置日志文件总合大小到你缓冲池大小的5%~100%，来避免在日志文件覆写上不必要的缓冲池刷新行为.不论如何,请注意一个大的日志文件大小会增加恢复进程所需要的时间.innodb_log_files_in_group=2#在日志组中的文件总数.通常来说2~3是比较好的.innodb_log_group_home_dir=/usr/local/mysql/var/#InnoDB的日志文件所在位置.默认是MySQL的datadir.你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能innodb_max_dirty_pages_pct=90#innodb主线程刷新缓存池中的数据，使脏数据比例小于90%,这是一个软限制,不被保证绝对执行.innodb_lock_wait_timeout=50#InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCKTABLES语句注意到锁定设置。默认值是50秒innodb_flush_method=O_DSYNC#InnoDB用来刷新日志的方法.表空间总是使用双重写入刷新方法.默认值是“fdatasync”,另一个是“O_DSYNC”.innodb_force_recovery=1#如果你发现InnoDB表空间损坏,设置此值为一个非零值可能帮助你导出你的表.从1开始并且增加此值知道你能够成功的导出表.innodb_fast_shutdown#加速InnoDB的关闭.这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.但是取而代之的是InnoDB可能在下次启动时做这些操作.

#其他相关：

[mysqldump]quick

[mysql]auto-rehash#允许通过TAB键提示default-character-set=utf8#数据库字符集connect-timeout=3

[client]default-character-set=utf8

[mysqld_safe]open-files-limit=8192#增加每个进程的可打开文件数量.确认你已经将全系统限制设定的足够高!打开大量表需要将此值设大

参考：https://www.cnblogs.com/panwenbin-logs/p/8360703.html