My.cnf最佳实践-亲试ok-20201020
[root@test206 scripts]# cat my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/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
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
port = 3309
socket =/data/my3309/run/mysql.sock
default-character-set=gbk
[mysql]
prompt="\u@mysqldb \R:\m\s [\d]> "
no-auto-rehash
no-auto-rehash
[mysqld]
user = mysql
port = 3309
basedir=/data/mysql
datadir=/data/my3309/data/
#默认编码设置为utf8mb4
character_set_server = gbk
#该参数目的是不再进行反解析，可以加快数据库的反应时间
skip_name_resolve = 1
#接受的数据包大小，有时大的插入和更新会失败，将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
## enable autocommit
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
auto_increment_increment= 2
auto_increment_offset = 1
lower_case_table_names=1
#skip_grants_tables
# system
basedir=/data/mysql
datadir=/data/my3309/data/
max_allowed_packet=134217728
max_connections=8192
max_user_connections=8000
open_files_limit=65535
pid_file=/data/my3309/run/mysqld.pid
port=3309
server_id=128
#skip_name_resolve=ON
socket=/data/my3309/run/mysql.sock
tmpdir=/data/my3309/tmp/
skip_grant_tables
# binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=15
log-bin=/data/my3309/log/binlog/log-bin
log-bin-index=/data/my3309/log/binlog/log-bin.index
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100
#relay
relay-log=/data/my3309/log/relaylog/slave-relay-bin
relay-log-index=/data/my3309/log/relaylog/slave-relay-bin.index
########日志设置########
#开启慢查询日志
slow_query_log = 1
#慢查询日志存放位置
slow_query_log_file = /data/my3309/slow.log
#错误日志存放位置
#log-error = /data/my3309/alert_3309.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
enforce_gtid_consistency = 1
#从服务器的更新写入二进制日志,便于主从切换时，从服务器已经开启二进制日志
log_slave_updates
#以row格式记录binlog
binlog_format = row
#允许从库宕机后，重新从master上获取日志，保证relay-log的完整性
relay_log_recovery = 1
#半同步（semi_sync）插件
#开启半同步无损复制
#[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
# LOGGING #
log_error = /data/my3309/log/alert_3309.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /data/my3309/log/slow.log
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1
#slave#
slave_skip_errors=OFF
log_slave_updates=ON
# innodb #
innodb_log_group_home_dir=/data/my3309/log/iblog
innodb_data_home_dir=/data/my3309/log/iblog
innodb_adaptive_flushing=1
#innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=8
innodb_change_buffering=inserts
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 4
innodb_log_file_size = 100M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_file_format=Barracuda
#innodb_file_io_threads=4
innodb_flush_neighbors=0
innodb_io_capacity=200
innodb_lock_wait_timeout=5
innodb_log_buffer_size=64M
innodb_lru_scan_depth=2048
innodb_max_dirty_pages_pct=60
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=200M
innodb_open_files=200
innodb_print_all_deadlocks=1
innodb_purge_threads=4
innodb_read_ahead_threshold=0
innodb_read_io_threads=8
innodb_rollback_on_timeout=0
innodb_sort_buffer_size=2M
innodb_spin_wait_delay=6
innodb_stats_on_metadata=0
innodb_strict_mode=1
innodb_sync_array_size=256
innodb_sync_spin_loops=30
innodb_thread_concurrency=64
innodb_use_native_aio=0
innodb_write_io_threads=8
innodb_support_xa=1
########innodb设置########
#锁等待时间
lock_wait_timeout = 3600
#并发运行的线程数，设置为0表示不限制
innodb_thread_concurrency = 0
#将事务隔离级别设置为READ-COMMITTED
transaction_isolation = READ-COMMITTED
#InnoDB缓存池大小
innodb_buffer_pool_size =4048M
#InnoDB缓存池实例数
innodb_buffer_pool_instances = 2
#在启动时把热数据加载到内存
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 = 100M
#当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M
innodb_max_undo_log_size = 4G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 1000
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
[mysqld_safe]
datadir=/data/my3309/data/
