【MySQL运维】MySQL 5.7 配置文件优化模板及详细说明
一、MySQL配置文件路径规则
MySQL对于配置文件有一个顺序规则，它是按照/etc/my.cnf > /etc/mysql/my.cnf > /usr/local/mysql/etc/my.cnf > ~/.my.cnf 这样的顺序进行自动寻找，如果某一个参数同时存在于多个配置文件中则遵循参数替换原则，以最后一个配置文件中的配置生效
二、my.cnf作用域相关配置
· [mysqld]：服务端选项作用域，大部分配置都在该作用域下
· [mysql]：mysql客户端作用域，该作用域下的配置仅对mysql这个命令客户端生效
· [client]：其他客户端的作用域。该作用域下的配置对mysqladmin、myqsldump等客户端命令都生效。由于不是所有客户端都有相同的配置，可能会因为多余的配置导致启动报错
· [mysqldump]：mysqldump客户端作用域
· [mysqladmin]：mysqladmin客户端作用域
· [mysqld-5.7]：对指定版本的服务端生效
三、my.cnf客户端相关配置
· prompt：自定义提示符，方便查看当前所操作数据库，如prompt= "[\\u@linuxe][\\d]>"
· no-auto-rehash：禁止自动读取元数据，虽然无法再进行命令补齐，但是可以降低负载。如果在使用mysql命令连接服务卡在提示符就是元数据被锁导致，用mysql -A选项也可以启用该功能
· default-character-set：客户端字符集设置，建议和服务端一致，如default-character-set=utf8mb4
· tee：将操作记录写到日志中作为审计，如tee= "/data/dblog/tee.log"
四、my.cnf服务端相关配置
1、数据库基本信息
· user：指定管理数据库服务的用户，如user=mysql
· basedir：MySQL安装路径，如basedir=/usr/local/mysql
· datadir：MySQL数据文件存放路径，如datadir=/data/mysql/data
· port：MySQL实例端口，如port=3306
· extra_port：额外的连接端口，通常用于连接数用光后给root账号处理使用
· extra_max_connections：额外的连接数，配合extra_port使用
· socket：MySQL sock文件路径，如socket=/tmp/mysql.sock
· character-set-server：字符集设置，如character-set-server=utf8mb4
· skip-character-set-client-handshake：强制客户端字符集和服务端一致
· default-storage-engine：默认存储引擎，如default-storage-engine=INNODB
· skip-name-resolve：建议开启，避免把IP反解析成域名（比如127.0.0.1被解析成localhost），这样使用IP来做权限控制更方便
· server_id：主从复制会用到，每个节点ID不能相同，可以避免双主架构带来的循环复制问题
· tmpdir：临时表路径，如tmpdir=/data/mysqltmp/mysql3306
· max_connect_errors：单个客户端连接失败的最大次数，密码错误或者网络问题产生的连接失败都会计数，超过该次数就会被服务永久拒绝连接，直到使用mysqladmin flush-hosts才会恢复
· max_user_connections：每一个用户所能产生的最大连接数，默认为0不限制。设置该值后，可以避免某个程序连接池配置不合理导致大量连接被占用
· back_log：如果客户端连接数达到上限后，允许多少个客户端进入一个队列排队，可以设置为最大连接数的2倍
· max_allowed_packet：MySQL与客户端建立连接后能传输的数据包限制（数据包会经过封装及压缩，所以不同等SQL语句或插入数据的大小）。如果一个包含大批量数据更新的SQL超过了该参数的限制就会导致数据插入失败或者主从同步失败，该变量需要将客户端和服务端设置为同样的值，最大可配置为1G
· time_zone：默认为SYSTEM，即系统的操作时区。对于国内业务建议配置为default-time-zone='+08:00'，其他地区和开发确认取对应时区即可。明确时区相比设置时区为system的好处有2个，1是system多了一层系统调用，对性能有轻微损耗；2是操作系统的时区设置可能不归DBA管，可能会因为运维人员的疏忽导致配置不当，如果设置的时区不正确则会导致NOW()、CURTIME()这类系统函数以及timestamp类型字段存储的数据和理想值不一样
· performance_schema：显式启用performance_schema数据库，该选项在5.7开始默认是启用的
· lower_case_table_names：建议设置为1，表示不对表名进行大小写区分
· init_connect：设置每个会话连接后自动执行的命令，比如SET NAMES或者SET AUTOCOMMIT
· open_files_limit：当使用非root用户对数据库进行操作时（比如xtrabackup备份）所能打开的文件数量
2、数据库内存信息（尽可能的为数据库分配更多的内存，减少与磁盘的IO）
max_connections：客户端最大连接数，超过该连接后就会出现Too many connections的错误。如果没有配置extra_port参数，那么root账号还能连接一次。该配置建议参考show status like '%Threads_running%'的值进行调整，不能盲目调大，因为MySQL申请内存的计算方式是max_connections*(sort_buffer_size+read_buffer_size+read_rnd_buffer_size+join_buffer_size+binlog_cache_size+thread_stack)，也就是说每个客户端连接后还要单独申请内存空间，如果程序配置存在不合理的现象很容易OOM
sort_buffer_size：排序语句生成的结果会临时存放到内存空间中，默认256K，建议4-8M。如果查询结果超过这个大小则会在硬盘生成临时表进行存放，调大后可以提升order by语句的效率。经过Percona测试发现盲目调大会起到反效果，通常Sort_merge_passes/(Sort_scan+Sort_ range)>2说明sort_buffer_size设置偏小
show global status like '%sort%'
read_buffer_size：顺序读缓冲区大小，默认128K，建议调整为4-8M
read_rnd_buffer_size：随机读缓冲区大小，默认256K，建议调整为4-8M
join_buffer_size：默认256K，建议调整为4-8M
binlog_cache_size：当事务产生的binlog超过该参数值就会使用临时文件来存放binlog cache，默认32K，适当调大可以加快binlog写入效率
thread_stack：每个线程连接时申请的内存空间，默认256K
key_buffer_size：默认64M
tmp_table_size：内存临时表的最大值，默认64M，临时表可以提升group by和联合查询的性能。如果内存临时表超过该参数限额，则开始生成基于磁盘的临时表（tmp_disk_tables），此时查询性能是有明显影响的。通过show status like '%tmp%' 查看tmp_disk_tables的值，如果增长很快说明tmp_table_size设置过小，可以适当调大。需注意该参数还受到max_heap_table_size的影响，如max_heap_table_size设置得比tmp_table_size小，那max_heap_table_size的值才是最大的内存临时表的上限
max_heap_table_size：用户可以创建的内存临时表大小
3、数据库超时配置
interactive_timeout：交互模式下会话超时时间，比如mysql客户端，单位为秒，建议600-1800秒之间
wait_timeout：应用会话连接超时时间，比如jdbc，单位为秒，默认为8小时。应用程序在连接成功后如果没有后续操作，则连接处于Sleep空闲状态，建议600-1800秒之间
rpl_stop_slave_timeout：停止从库时的超时时间，如果超时会报错，避免从库回滚大事务一直卡住。如果从库存在大事务建议先直接停止mysql，增加索引后再stop slave
innodb_lock_wait_timeout：InnoDB行锁等待超时时间，比如事务A对某行数据进行修改但未提交，此时事务B也修改该行数据时就会被锁，直到超时。该值默认50，建议5-20秒
lock_wait_timeout：元数据锁超时时间，默认是1年，设置30秒足够
max_execution_time=60000：控制查询语句执行的最长时间，单位是毫秒。避免单个语句意外执行太长时间，比如一些程序框架会使用begin\commit声明事务或者代码错误的把SELECT语句放入到事务中
4、数据库日志信息
· errorlog
log_error：错误日志路径，如/data/mysql/logs/error.log
· slowlog
slow_query_log：慢日志开关，on代表打开
slow_query_log_file：慢日志路径，如/data/mysql/logs/slow.log，不指定的话默认在数据库文件目录下，名为hostname-slow.log
long_query_time：慢日志标准，达到这个标准的才会被记录慢日志，单位为秒，设为0代表记录所有查询
log_queries_not_using_indexes=1：将没有使用索引的语句都记录到慢日志中，即便没有达到阈值
log_throttle_queries_not_using_indexes=60：和上面选项配合使用，如果没有使用索引的语句执行频繁，那每分钟最多只记录60次
log-slow-admin-statements：记录由ALTER TABLE等语句引发的慢查询
· binlog
log_bin：binlog日志路径，如/data/mysql/logs/master-bin
log_bin_index：binlog索引文件路径，如/data/mysql/logs/master-bin.index
binlog_format：binlog日志格式，推荐row
binlog_rows_query_log_events：将所执行的原始sql语句记录到日志中，更便于分析
expire_logs_days：binlog过期天数
max_binlog_size：单个binlog文件大小
binlog_cache_size：binlog缓存配置，和性能优化相关
log_timestamps：默认为UTC时间，这样查看日志会存在时区问题，建议修改为SYSTEM，和系统时间一致
· relaylog
relay-log：relaylog日志路径与文件名，如/data/mysql/logs/relay-bin
relay-log-purge：是否自动清理已经应用完的relaylog，1为自动清理
relay_log_recovery：建议为1，开启中继日志恢复模式，可以保证数据一致性。如果从库宕机导致当前中继日志损坏的话，就放弃该日志并重新从master上获取
max_relay_log_size：单个中继日志文件的大小，如果没有设置的话会按照max_binlog_size的大小进行滚动
· Redo Log
innodb_log_file_size（需重启数据库才能生效）：redo log日志大小，默认是48M，SSD硬盘建议配置到4G。如果设置太小会导致数据库经常flush日志到磁盘影响性能，配置越大写操作的效率越高，但是崩溃恢复时间会稍长一点
innodb_log_buffer_size：redo log缓存区域大小，增加缓存大小可以减少I/O，建议配置1-32M即可。当满足以下几种情况时将刷新缓存中的数据到磁盘中：master thread每秒刷新buffer、buffer写满时刷新、事务提交时刷新、服务重启时刷新，
innodb_log_files_in_group：redo log数量，默认2，最多支持4个，redolog日志命名为ib_logfile0~ib_logfileN
5、双1参数
innodb_flush_log_at_trx_commit：redolog刷盘策略，建议为1。0是每秒刷新一次redo buffer到磁盘，当服务崩溃可能丢失1秒数据；1是每次事务提交时都立即刷新到磁盘；2是每次事务提交都立即刷新到os cache中，随后刷新到磁盘，机器断电才会丢失1秒数据
sync_binlog：binlog刷盘策略，建议为1。当值为0代表事务提交时仅将Binlog信息写入OS Cache，但是由系统自己控制刷盘时机，如果系统宕机则丢失缓存中的所有数据；当值为1时代表每个事务提交时同步把Binlog刷新到磁盘，当数据库或操作系统宕机恢复后，由于Binlog中缺少的任何事务都是处于准备阶段，所以MySQL会回滚这些事务，保证无数据丢失，数据库安全性最高，但是性能损耗也最大；当值为N时代表每N次事务提交才会刷盘，如果操作系统在这个时候宕机，数据库可能会丢失N个事务
6、主从复制相关配置
log_slave_updates：开启该选项后从库也会记录binlog，否则从其它库同步的数据不会写入binlog（如果从库配置了binlog而没有开启该选项则binlog为空）。不承载高可用的从库可以关闭节约IO性能，如果需要级联复制的场景可以开启
slave_parallel_workers：该值为0代表单线程复制，大于1时表示启用多线程复制。为了提升从库复制效率通常和CPU核数一致，该变量动态设置后需要重启复制线程才会生效。并行复制开启后通过processlist看到状态为system lock的system user用户就是并行复制的线程
slave_parallel-type：并行复制方式，5.7之前是基于DATABASE级别的粒度进行并行复制，并行效率很差。而5.7开始采用LOGICAL_LOCK实现了GroupCommit并行回放，同一个Group内的事务可以并行执行。在binlog中通过last_committed的值来标记事务所属的组，如果两个事务last_committed值一致，这两个事务就属于同一个组
slave_preserve_commit_order：保证从库回放事务的顺序与主库提交的顺序完全一致。比如主库执行了A、B、C 3个事务，且last_commited相同，从库满足并行回放的条件。但是如果slave_preserve_commit_order没有开启的话，则顺序可能是乱的。该选项动态开启时需要先停止SQL线程，并且在8.0.19以前从库必须同时开启log_bin和log_slave_updates才能进行配置
binlog_group_commit_sync_delay：并行复制时延迟多少微妙后才会调用fsync
binlog_group_commit_sync_no_delay_count：多线程复制时累计多少次以后就调用fsync
transaction_write_set_extraction（该配置在5.7.22开始支持）：指定多线程复制时的HASH算法，建议配置为XXHASH64
binlog_transaction_dependency_tracking（该配置在5.7.22开始支持）：指定多线程复制时并行事务依赖检查的方法，默认为COMMIT_ORDER，表示只有事务是同一时间提交的前提下才能生成一组相同的last_committed形成并行回放。而使用WRITESET_SESSION后可以实现"只要事务不存在锁冲突，即便提交的时间不相同、会话不相同，也可以归到一个组中进行提交"的效果
relay_log_info_repository：建议设置为table，表示将从库中继日志位点信息写入到mysql.slave_relay_log_info表，可以保证服务崩溃后信息不丢失。默认为file表示是将这些信息写入relay_log.info文件，如果服务器崩溃可能导致文件中的位置信息是错误的
master_info_repository：建议设置为table，将从库与主库的连接状态和用户账号、密码等配置信息写入到mysql.slave_master_info表中，否则默认为file，写入的是master.info文件中
7、InnoDB信息
· InnoDB性能
innodb_buffer_pool_size：MySQL最重要的性能参数，该变量用于设置数据和索引的缓存区大小，使用缓存区中的数据就可以避免与磁盘产生大量交互，从而提升效率，该变量建议设置为系统最大内存的70%（注意该变量的值必须是innodb_buffer_pool_instance * innodb_buffer_pool_chunk_size（默认128M，不能动态调整）的倍数）。5.7开始支持动态配置，但是在线修改的话值只能为字节格式，配置文件可以使用M、G单位。通过SHOW GLOBAL STATUS LIKE '%innodb%'命令查看MySQL剩余buffer，如果Innodb_buffer_pool_pages_free值很小或为0说明buffer_pool已经用光了，而Innodb_buffer_pool_wait_free不为0，说明产生过对应次数的等待内存释放行为。这2种情况都需要增加innodb_buffer_pool_size的值。
innodb_buffer_pool_instances：将buffer_pool_size平均划分多个区域，提升并发性能，建议和CPU核数一致
innodb_flush_method=O_DIRECT：默认为fsync，代表刷数据到磁盘的时候会先申请系统级别的缓存，这样性能虽然会更好但是会申请额外的内存。由于MySQL除了innodb_buffer_pool_size占用内存，每个连接还会单独申请内存空间，再加上fsync申请的内存，很容易导致OOM。改为O_DIRECT会跨过系统缓存直接写到磁盘。
innodb_thread_concurrency：并发查询数（非并发连接），默认为0不限制。如果为了避免并发太多导致CPU跑满可以设置一个合理值。由于锁等待产生的并发并不会带来CPU开销，所以这些并发不会占用这个连接名额，避免锁等待而让数据库不可用
innodb_strict_mode：开启严格模式，可以避免非空字段插入null等情况
innodb_stats_persistent：是否持久化数据和索引的统计信息到磁盘中，优化器将使用到这些统计数据。持久化以后当数据库发生重启也可以重新加载这些统计数据而不用重新去计算。默认为ON，开启后统计信息是存放在mysql.innodb_table_stats和mysql.innodb_index_stats中
innodb_stats_auto_recalc：是否开启统计信息重新计算，默认为ON，当数据量变更超过10%就会自动重新统计并刷新统计数据
innodb_stats_on_metadata：建议为off，关闭动态统计被触发
innodb_io_capacity：脏页正常刷新频率，单位为页，默认200即代表每次刷新200页（每个页默认16K）。如果磁盘速度快就可以调大，建议设置为磁盘IOPS的一半或更高，SSD的话通常可以达到1W+，可以通过fio来测试磁盘IO
innodb_io_capacity_max：脏页最大刷新频率，建议和磁盘IOPS一样
innodb_flush_neighbors：默认为1代表刷脏页时会把邻近脏页一起刷到磁盘。对于机械硬盘能够减少磁盘寻道的开销，提升性能；但是对于SSD提升很小，建议为0关闭
innodb_data_file_path：共享表空间设置，该文件默认是创建一个10M的文件并且自动扩展，为了防止文件过大可以限制其上限
innodb_file_per_table：是否开启独立表空间，5.6开始默认打开也建议打开。开启后每个表数据都会单独存放在一个以表命名后缀为.ibd文件中分散IO提升性能，也便于表的维护，否则所有表数据会全部存放在一个共享表空间文件ibdata1中，这个文件即便删除了数据也无法释放空间，只是将表空间被标记为可复用，所以建议开启独立表空间并限制共享表空间的大小
innodb_buffer_pool_dump_at_shutdown：ON代表每次停机会dump出buffer pool中的数据
innodb_buffer_pool_filename：dump出的数据文件名，如ib_buffer_pool
innodb_buffer_pool_load_at_startup：ON代表启动服务时加载dump文件到内存进行缓存，提升性能
table_open_cache：指定打开表的缓存区大小，也可以理解为允许缓存客户端打开多少张表。这个配置需要结合open_tables（当前打开的表数量）与opened_tables（服务启动以来总共打开的表数量）两个状态值来调整。如果open_tables的值接近table_open_cache代表缓存区已经使用完，如果此刻opened_tables又在不断增加，说明mysql还在不断打开表。这个使用缓存是没有起到作用的，所以需要调大缓存区的值，但是不能大于系统文件描述符。
innodb_large_prefix：是否允许单列的索引长度超过767字节
innodb_old_blocks_pct：MySQL为避免LRU算法把冷数据放到头部，所以将LRU链表拆分为热、冷数据两个部分，优先对冷数据进行淘汰。该参数控制了冷热数据比例，默认37代表冷数据占37%
innodb_old_blocks_time：控制冷数据转为热数据的策略，默认1000毫秒，代表数据页被加载到缓存页N毫秒后如果再次被访问，就挪动到热数据区域表头部。这个思想也可以用在Redis上，可以每天统计出来哪些商品被访问的次数最多，然后通过定时作业把这些热门商品预加载到Redis里，第二天对热门商品的访问就会优先走Redis缓存了
innodb_adaptive_hash_index：自适应哈希索引，建议关闭，避免因为哈希索引带来的锁竞争
innodb_page_size：InnoDB页大小，默认是16KB。在MySQL中页是磁盘和内存交互的基本单位，也就是数据库IO操作的最小单位。在一个页中可以存放多条数据，当数据库读取数据时，不管是一行还是多行，至少需要一个页来完成
· Undo Log
innodb_undo_tablespaces：undolog独立表空间个数，默认为0表示不为undolog设置独立表空间，而是记录到ibdata文件中。如果设置为4则代表创建命名为undo001~undo004的undolog。该配置在mysql初始化后就无法进行修改
innodb_max_undo_log_size：单个回滚日志最大占用空间大小，默认为10M
innodb_undo_log_truncate：当undolog超过innodb_max_undo_log_size阀值时对undolog空间进行收缩到10M
五、my.cnf配置文件示例
[client] port = 3310 socket = /tmp/mysql3310.sock [mysql] no-auto-rehash prompt = "\\u@\\d \\R:\\m> " default-character-set = utf8mb4 [mysqld] ### General ### user = mysql port = 3310 extra_port = 13310 extra_max_connections = 10 basedir = /usr/local/mysql datadir = /data/mysql3310/data socket = /tmp/mysql3310.sock tmpdir = /data/mysql3310/tmp/ character_set_server = utf8mb4 default-storage-engine = InnoDB default-time-zone = "+8:00" server-id = 30241 transaction_isolation = READ-COMMITTED max_connect_errors = 500 #max_user_connections = 100 back_log = 1024 open_files_limit = 65535 max_allowed_packet = 512M init_connect ='set names utf8mb4' group_concat_max_len = 10240 table_open_cache = 8192 table_definition_cache = 8192 log_timestamps = system performance_schema = ON lower_case_table_names = 1 explicit_defaults_for_timestamp = 0 sql_mode="NO_ENGINE_SUBSTITUTION" skip-character-set-client-handshake = 1 skip-name-resolve skip-ssl skip-external-locking #skip-grant-tables ### Timeout ### connect_timeout = 20 interactive_timeout = 3600 wait_timeout = 3600 lock_wait_timeout = 300 max_execution_time = 60000 innodb_lock_wait_timeout = 10 rpl_stop_slave_timeout = 180 ### Memory ### innodb_buffer_pool_size = 32G innodb_log_buffer_size = 32M tmp_table_size = 64M max_heap_table_size = 64M key_buffer_size = 64M max_connections = 500 read_buffer_size = 4M sort_buffer_size = 4M read_rnd_buffer_size = 4M join_buffer_size = 4M binlog_cache_size = 16M ### Error Log ### log_error = /data/mysql3310/log/error.log ### Slow log ### slow_query_log = 1 slow_query_log_file = /data/mysql3310/log/slowlog/mysql.slow long_query_time = 1 log_slow_admin_statements = 1 #log_queries_not_using_indexes = 1 #log_throttle_queries_not_using_indexes = 10 #log_slow_slave_statements = 1 #min_examined_row_limit = 100 ### Binlog ### sync_master_info = 1 log_bin = /data/mysql3310/log/binlog/mysql-bin binlog_format = row binlog_rows_query_log_events = on max_binlog_size = 1024M expire_logs_days = 10 ### Relay Log ### relay-log = /data/mysql3310/log/relaylog/relay-bin relay_log_recovery = 1 relay-log-purge = 1 ### Replication ### master_info_repository = TABLE relay_log_info_repository = TABLE skip_slave_start = 1 log_slave_updates = 1 slave_parallel-type = LOGICAL_CLOCK slave_parallel_workers = 8 slave_preserve_commit_order = 1 slave_net_timeout = 10 # read_only = 1 # super_read_only = 1 transaction_write_set_extraction =XXHASH64 binlog_transaction_dependency_tracking = WRITESET_SESSION ### Gtid ### gtid_mode = on enforce_gtid_consistency = 1 binlog_gtid_simple_recovery = 1 ### 半同步复制 ### plugin_dir=/usr/local/mysql/lib/plugin plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl_semi_sync_master_enabled=1 rpl_semi_sync_slave_enabled=1 rpl_semi_sync_master_wait_no_slave=OFF rpl_semi_sync_master_wait_for_slave_count=1 rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_master_timeout=3000 #loose_rpl_semi_sync_master_enabled = 1 #loose_rpl_semi_sync_slave_enabled = 1 #loose_rpl_semi_sync_master_timeout = 5000 ### 双1 ### innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 ### Innodb ### innodb_data_home_dir = /data/mysql3310/data innodb_data_file_path = ibdata1:1024M:autoextend:max:16G innodb_buffer_pool_instances = 16 innodb_support_xa = 1 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_log_file_size = 4G innodb_log_files_in_group = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_purge_threads = 8 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_max_dirty_pages_pct = 75 innodb_flush_neighbors = 0 innodb_thread_concurrency = 0 innodb_strict_mode = 1 innodb_stats_on_metadata = 0 innodb_print_all_deadlocks = 1 innodb_autoinc_lock_mode = 2 innodb_sort_buffer_size = 67108864 innodb_large_prefix = 1 innodb_page_size = 16384 innodb_open_files = 65535 innodb_rollback_on_timeout = 1 innodb_adaptive_hash_index = 0 ### Undo Log ### #innodb_max_undo_log_size = 1G #innodb_undo_tablespaces = 4 #innodb_undo_logs = 128 #innodb_undo_log_truncate = 1 [mysqldump] quick max_allowed_packet = 512M [mysqld_safe] open-files-limit = 65535
评论
silent3035
回复请问 ,咱们站点的广告在哪里, 我给你点点.
TangLu
回复@silent3035 @silent3035：感谢 在网站右侧标签栏下方有2个广告区