My.cnf 文件-20201020

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

#[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

[mysql]

# CLIENT #

port = 3306

socket =/data/my3306/run/mysql.sock

disable-auto-rehash

default-character-set=utf8                                 

[mysqld]                                                        

# GENERAL #                                                     

lower_case_table_names=1                                        

server_id = 128                                                 

port = 3306                                                     

user = mysql                                                    

explicit_defaults_for_timestamp=true                            

default-storage-engine = InnoDB                                 

character_set_server = utf8                                     

auto_increment_increment = 2                                    

auto_increment_offset = 1                                       

lower_case_table_names = 1                                      

socket =/data/my3306/run/mysql.sock                             

pid_file=/data/my3306/run/mysqld.pid                            

# MyISAM #                                                      

key-buffer-size                = 32M                            

myisam-recover-options         = FORCE,BACKUP                   

# SAFETY #                                                      

max_allowed_packet = 134217728                                  

max_connections = 8192                                          

max_user_connections = 8000                                     

open_files_limit = 65535                                        

skip-name-resolve                                               

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES             

sysdate-is-now                 = 1                              

# DATA STORAGE #                                                

basedir = /data/mysql                                           

datadir=/data/my3306/data/                                      

tmpdir=/data/my3306/tmp/                                        

# BINARY LOGGING #                                              

log-bin=/data/my3306/log/binlog/log-bin                         

log-bin-index=/data/my3306/log/binlog/log-bin.index             

expire-logs-days = 15                                           

sync-binlog = 1                                                 

binlog_format = ROW                                             

#RELAY LOGGING                                                  

relay-log=/data/my3306/log/relaylog/slave-relay-bin             

relay-log-index=/data/my3306/log/relaylog/slave-relay-bin.index

sync_relay_log=1                                                

# CACHES AND LIMITS #                                           

tmp-table-size                 = 32M                            

max-heap-table-size            = 32M                            

query-cache-type               = 0                              

query-cache-size               = 0                              

max-connections                = 500                            

thread-cache-size              = 50                             

open-files-limit               = 65535                          

table-definition-cache         = 1024                           

table-open-cache               = 2048                           

# INNODB #                                                      

innodb_log_group_home_dir=/data/my3306/log/iblog                

innodb_data_home_dir=/data/my3306/log/iblog                     

innodb-flush-method = O_DIRECT                                  

innodb-log-files-in-group      = 2                              

innodb-log-file-size           = 256M                           

innodb-flush-log-at-trx-commit = 1                              

innodb-file-per-table          = 1                              

innodb-buffer-pool-size        = 6G                             

# LOGGING #                                                     

general_log = off                                               

log-error = /data/my3306/log/alert_3306.log                     

log-queries-not-using-indexes  = 1                              

slow-query-log                 = 1                              

slow-query-log-file            = /data/my3306/log/mysql-slow.log

log_slave_updates=ON                                            

#服务器物理内存16G my.cnf配置及参数说明

## dbeye auto generate

#

[client]

port = 3306

[mysql]

prompt="\u@mysqldb \R:\m:\s [\d]> "

no-auto-rehash

[mysqld]

########基础设置########

user = mysql

port = 3306

basedir = /usr/local/mysql

datadir = /data/mysql/

#默认编码设置为utf8mb4

character-set-server = utf8mb4

#该参数目的是不再进行反解析，可以加快数据库的反应时间

skip_name_resolve = 1

#允许的最大连接数

max_connections = 512

#允许的最大错误连接数，超过该值客户端将被屏蔽，默认为100，一般将该值设的较大避免客户端被屏蔽引发难以预料的问题

max_connect_errors = 1000000

#接受的数据包大小，有时大的插入和更新会失败，将max_allowed_packet设置适当避免该问题

max_allowed_packet = 32M

#排序缓存大小，在排序大量数据时该值将影响order by子句的执行效率

sort_buffer_size = 32M

#连接缓存大小，在连接大表时，该值将影响连接查询的效率

join_buffer_size = 128M

#使用MySQL客户端连接超时时间设为3分钟

interactive_timeout = 1800

#使用JDBC连接超时时间设为3分钟

wait_timeout = 1800

#临时表大小，在排序和连接较多时，适当

tmp_table_size = 64M

#MySQL读入缓冲区大小

read_buffer_size = 16M

#MySQL随机读缓冲区大小

read_rnd_buffer_size = 32M

#设置处理TIMESTAMP列的方式，详见官方文档说明

explicit_defaults_for_timestamp = 1

########日志设置########

#开启慢查询日志

slow_query_log = 1

#慢查询日志存放位置

slow_query_log_file = slow.log

#错误日志存放位置

log-error = error.log

#超过多少秒的查询，被视为慢查询

long_query_time = 1

########复制设置########

#开启binlog，指定日志名称

log_bin = bin

#当每进行1次事务提交之后，MySQL将进行一次fsync磁盘同步，以此来保证无损复制

sync_binlog = 1

#二进制日志过期时间

expire_logs_days = 7

#master.info保存在表中

master_info_repository = TABLE

#relay.info保存在表中

relay_log_info_repository = TABLE

#启动GTID模式

gtid_mode = on

#启动GTID模式

enforce_gtid_consistency = 1

#从服务器的更新写入二进制日志,便于主从切换时，从服务器已经开启二进制日志

log_slave_updates

#以row格式记录binlog

binlog_format = row

#允许从库宕机后，重新从master上获取日志，保证relay-log的完整性

relay_log_recovery = 1

#开启半同步无损复制

[mysqld-5.7]

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

#server-id

server-id = 127.0.0.1

########innodb设置########

#锁等待时间

lock_wait_timeout = 3600

#并发运行的线程数，设置为0表示不限制

innodb_thread_concurrency = 0

#将事务隔离级别设置为READ-COMMITTED

transaction_isolation = READ-COMMITTED

#InnoDB缓存池大小

innodb_buffer_pool_size = 12000M

#InnoDB缓存池实例数

innodb_buffer_pool_instances = 8

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

innodb_buffer_pool_load_at_startup = 1

#数据库关闭时自动dump数据

innodb_buffer_pool_dump_at_shutdown = 1

#每次提交事务都写入日志，保证数据安全

innodb_flush_log_at_trx_commit = 1

#尚未执行的事务分配的缓存，如果事务比较大，适当调整该值

innodb_log_buffer_size = 16M

#redo日志的大小

innodb_log_file_size = 2G

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

innodb_max_undo_log_size = 4G

# 根据您的服务器IOPS能力适当调整

# 一般配普通SSD盘的话，可以调整到 10000 - 20000

# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

#innodb其他配置

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_purge_threads = 4

innodb_page_cleaners = 4

innodb_open_files = 65535

innodb_max_dirty_pages_pct = 50

innodb_flush_method = O_DIRECT

innodb_lru_scan_depth = 4000

innodb_checksum_algorithm = crc32

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_lock_wait_timeout = 10

innodb_rollback_on_timeout = 1

innodb_print_all_deadlocks = 1

innodb_file_per_table = 1

innodb_online_alter_log_max_size = 4G

internal_tmp_disk_storage_engine = InnoDB

innodb_status_file = 1

innodb_status_output = 1

innodb_status_output_locks = 1

innodb_stats_on_metadata = 0

#将保存在permanforce_schema中的SQL语句长度设置的足够长，便于查看

performance_schema_max_sql_text_length = 4048