mysql 执行时间 配置文件_mysql配置文件详解

[client]

user = root

host = localhost

password = password

[mysqld]

federated

#开启Federated存储引擎

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

character_set_server=utf8mb4

#服务器字符集，默认情况下所采用的

skip_name_resolve = 1

#禁止域名解析

max_connections = 800

# MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小

max_connect_errors = 1000

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

transaction_isolation = READ-COMMITTED

# MySQL支持4种事务隔离级别，他们分别是：

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED

explicit_defaults_for_timestamp = 1

join_buffer_size = 8M

# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享

read_buffer_size = 16M

# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

# 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size = 32M

# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，

# MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

sort_buffer_size = 32M

# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

# 如果不能，可以尝试增加sort_buffer_size变量的大小

tmp_table_size = 16M

# 临时表的内存缓存大小，临时表是指sql执行时生成临时数据表 ，首先在优化sql的时候就应该尽量避免临时表，如果必须使用临时表， 且同时执行大量sql ，生成大量临时表时适当增加 tmp_table_size，如果生成的临时表数据量大于 tmp_table_size 则会将临时表存储与磁盘而不是内存

tmpdir = /tmp

#临时目录

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"

#定义mysql支持的sql语法，

interactive_timeout = 7200

#mysql在关闭一个交互的连接之前所要等待的秒数

wait_timeout = 7200

#mysql在关闭一个交互的连接之前所要等待的秒数

lower_case_table_names = 1

#表名不区分大小写

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

log_error = master_error.log       #错误日志文件名称

slow_query_log = 1                      #开启慢日志查询

long_query_time=2 #超过2秒返回查询的结果为慢查询

slow_query_log_file = slow.log    #慢查询日志

log_queries_not_using_indexes = 1   #记录所有没使用到索引的查询语句

log_slow_admin_statements = 1       #记录慢的管理语句( 缺省情况下数据库相关管理型SQL(例如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE)不会被记录到日志。)

log_slow_slave_statements = 1      #记录slave端执行时间超过long_query_time的请求语句

log_throttle_queries_not_using_indexes = 10

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

expire_logs_days = 30

#binlog日志保存时间

min_examined_row_limit = 100

#扫描记录数大于该值的才会记录到慢查询日志，和long_query_time是慢查询的两个指标

binlog-do-db=aibici_mysql_2018   #开启记录binlog日志的数据库，

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

master_info_repository = TABLE

relay_log_info_repository = TABLE

#当一个从库是多源复制结构，那么master_info和relay_log_info不能用file方式来存储，必须使用table，它不支持file

log_bin = mysql-bin

#二进制日志名称

sync_binlog = 1

#控制mysql怎么刷新二进制日志到磁盘，默认是0，意味着mysql并不刷新，由操作系统自己决定什么时候刷新缓存到磁盘，如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作，如果没有设置它为1，那么崩溃后可能导致二进制日志没有同步事务数据，这可以轻易的导致复制中断，并且使得及时恢复变得不可能，无论如何，可以把这个值设置为1来获得安全的保障，这样就会要求mysql同步把二进制日志和事务日志这两个文件刷新到两个不同的位置，这可能需要磁盘寻道，相对来说是个很慢的操作。

innodb_flush_log_at_trx_commit = 1

#log_buff ---mysql写 (write)---> log_file ---OS刷新 (flush)---> disk

设置为”0“  log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

设置为”1“  每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.

设置为”2“  每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作

注意：双1参数同时设置为1的时候，性能最差

参考：http://blog.itpub.net/22664653/viewspace-1063134/

binlog_format = ROW

#mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

relay_log = relay.log

#定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn(By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory)

relay_log_recovery = 1

#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

binlog_gtid_simple_recovery = 1

#5.7.6以下中默认 simplified_binlog_gtid_recovery=flase5.7.6以上中默认 binlog_gtid_simple_recovery=true    (默认就是最合理的设置)

slave_skip_errors = ddl_exist_errors

#slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors。参考https://blog.csdn.net/qq_36592808/article/details/79964826

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

innodb_page_size = 8k

innodb_buffer_pool_size = 2G

# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%

innodb_buffer_pool_instances = 8

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

innodb_buffer_pool_load_at_startup = 1

#在启动时把热数据加载到内存。

innodb_buffer_pool_dump_at_shutdown = 1

#在关闭时把热数据dump到本地磁盘。

innodb_lru_scan_depth = 2000

#LRU列表中可用页的数量，默认值为1024

innodb_lock_wait_timeout = 30

#mysql 事务锁超时时间

innodb_io_capacity = 4000

#动态调整刷新脏页的数量， innodb_io_capacity默认是200，单位是页，该参数的设置大小取决于硬盘的IOPS，即每秒每秒的输入输出量(或读写次数)

innodb_io_capacity_max = 8000

#在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量

innodb_flush_method = O_DIRECT

#设置为O_DIRECT以避免双重缓冲.

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

#表空间启用压缩

#innodb_log_group_home_dir = /mysqldata/mysql_data/

innodb_undo_directory = /mysqldata/mysql_data/

#undo独立表空间的存放目录(undo log 保存事务发生之前的数据的一个版本，用于回滚)

innodb_undo_logs = 128

#回滚段为128KB

innodb_undo_tablespaces = 3

#指定有三个undo log文件

innodb_log_file_size = 512M

#mysql事务日志文件的大小

innodb_log_buffer_size = 16M

#确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。

innodb_purge_threads = 1

#从innodb1.2版本开始，可以指定多个innodb_purge_threads来进一步加快和提高undo回收速度

innodb_large_prefix = 1

#这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072

innodb_thread_concurrency = 0

#   innodb_thread_concurrency默认是0，则表示没有并发线程数限制，所有请求都会直接请求线程执行。注意：当 innodb_thread_concurrency 设置为0时，则innodb_thread_sleep_delay的设置将会被忽略，不起作用。如果数据库没出现性能问题时，使用默认值即可；

innodb_print_all_deadlocks = 1

#打开配置时，死锁日志存放到error_log配置的文件里面

innodb_strict_mode = 1

#开启InnoDB严格检查模式，尤其采用了页数据压缩功能后，最好是开启该功能。开启此功能后，当创建表(CREATE TABLE)、更改表(ALTER TABLE)和创建索引(CREATE INDEX)语句时，如果写法有错误，不会有警告信息，而是直接抛出错误。

innodb_sort_buffer_size = 1M

#在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小。利用这块内存把数据读进来进行内部排序然后写入磁盘。这个参数只会在创建索引的过程中被使用，不会用在后面的维护操作；在索引创建完毕后innodb_sort_buffer会被释放。

innodb_flush_neighbors = 0

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

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

plugin_dir=/usr/local/mysql/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

#半同步复制，在有的高可用架构下，master和slave需同时启动，以便在切换后能继续使用半同步复制

loose_rpl_semi_sync_master_timeout = 5000

#切换复制的timeout(5s)

#GTID

# 即全局事务ID(global transaction identifier)，GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行(不会重复执行同一个事务，并且会补全没有执行的事务)。

log-slave-updates=1

#开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的binlog里，

#从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。

#MySQL5.7 开启GTID复制模式不用开启log_slave_updates参数了

#gtid-mode=on

#GTID复制模式

MySQL 5.6

gtid_mode=ON(必选) 、

log_bin=ON(必选)

log-slave-updates=ON(必选)

enforce-gtid-consistency(必选)

MySQL 5.7

MySQL5.7.13 or higher

gtid_mode=ON(必选)

enforce-gtid-consistency(必选)

log_bin=ON(可选)--高可用切换，最好设置ON

log-slave-updates=ON(可选)--高可用切换，最好设置ON(如果在密集型写的环境，会增加从库不必要的磁盘IO开销。)

enforce-gtid-consistency=true

#MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporary table语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行

sync-master-info=1

#若master-info-repository为FILE，当设置为0，则每次sync_master_info事件都会刷新到磁盘，默认为10000次刷新到磁盘；若master-info-repository为TABLE，当设置为0，则表不做任何更新，设置为1，则每次事件会更新表 #默认为10000

slave-parallel-workers=4

#有4个SQL Thread(coordinator线程)来进行并行复制

binlog-checksum=CRC32

#当一个event被写入binary log的时候，checksum也同时写入binary log，然后在event通过网络传输到slave之后，再在slave上对其进行验证并写入slave的relay log

master-verify-checksum=1

#默认关闭，开启后主库会对每个binlog event进行checksum验证，如果失败则停止写入并报错；

slave-sql-verify-checksum=1

#默认关闭，开启后备库读relay log时会对每个event进行checksum验证；

binlog-rows-query-log_events=1

#在row模式下..开启该参数,将把sql语句打印到binlog日志里面

socket = /tmp/mysql.sock

port = 3306

pid-file = /mysqldata/master_data/mysqlmaster.pid

datadir = /mysqldata/master_data

basedir = /usr/local/mysql

innodb_log_group_home_dir = /mysqldata/master_data/

innodb_undo_directory = /mysqldata/master_data/

server-id = 10

report-host=10.45.19.179

report-port=3306

#report-系列Report系列是设置在从库上的，包含四个参数 report-[host|port|user|password]. 当my.cnf中设置了report-host时，在从库执行start slave的时候，会将report-host和report-port(默认3306)发给主库，主库记录在全局哈希结构变量 slave_list 中。

[mysqld-5.7]

innodb_buffer_pool_dump_pct = 40

#指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是40。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为40，则dump每个缓冲池中最近使用的40个page。

innodb_page_cleaners = 4

#Innodb中page clean线程将脏数据写入到磁盘，脏数据写盘后相应的redo就可以覆盖，然后达到redo循环使用的目的。在5.7中参数可以开启多个page clean线程服务于多个innodb buffer

innodb_undo_log_truncate = 1

#开启在线回收(收缩)undo log日志文件，支持动态设置。

innodb_max_undo_log_size = 2G

#undo表空间文件超过此值即标记为可收缩，默认1G，可在线修改

innodb_purge_rseg_truncate_frequency = 128

#控制回收(收缩)undo log的频率。undo log空间在它的回滚段没有得到释放之前不会收缩，

想要增加释放回滚区间的频率，就得降低innodb_purge_rseg_truncate_frequency设定值。默认128是最大值

log_timestamps=system

#主要是控制 error log、genera log，等等记录日志的显示时间参数。

show_compatibility_56=on

#MySQL5.6中INFORMATION_SCHEMA 中存在系统变量和状态变量的表，show variables 和show status也是基于此库中的表，在5.7.6时被Performance Schema也存在这四张表，show 语句开始基于Performance Schema中的表，如果show_compatibility_56参数开启，则兼容5.6