mysql配置文件生产环境参数配置_mysql生产环境配置-优快云博客

本文链接：https://blog.youkuaiyun.com/Kate20180425/article/details/128938057

vi /etc/my.cnf

##### /etc/mysql/my.cnf ####
### Part 1 basic settings ###
### Part 2 logsettings ###
### Part 3 replication settings ###
### Part 4 innodb settings ###

[mysql]
prompt=(\u@\h) [\d]>
max_allowed_packet = 64M
no_auto_rehash

[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_slave_enabled=1
########basic settings########

server-id = 1620
port = 3306
user = mysql
socket = /data/3306/mysql.sock
autocommit = 1
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 2048
max_user_connections=300
max_connect_errors = 10
open_files_limit=65565
innodb_open_files=2048

#init-connect='INSERT INTO PERCONA.T_LOGIN_INFO VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER());'

basedir= /usr/local/mysql
datadir = /data/3306/data
tmpdir = /data/3306/tmp

transaction_isolation = READ-COMMITTED
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
read_buffer_size = 2097152
read_rnd_buffer_size = 2097152
sort_buffer_size = 2097152
join_buffer_size = 2097152
tmp_table_size = 67108864
max_allowed_packet = 64M
query_cache_size = 0
query_cache_type = 0
lower_case_table_names=1

########log settings########
log_error = /data/3306/log/error.log
log_timestamps = SYSTEM
slow_query_log = 1
log_queries_not_using_indexes = 0
slow_query_log_file = /data/3306/log/slow.log
log_slow_admin_statements = 1
log_slow_slave_statements = 1
expire_logs_days = 7
long_query_time = 1

########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin =/data/3306/binlog/mysql-bin
sync_binlog = 1
binlog_format = row
relay_log = /data/3306/binlog/relay.log
relay_log_recovery = 1
#relay_log_purge = 0
skip_slave_start = 1
log_slave_updates = 1
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers = 48

########innodb settings########
innodb_buffer_pool_size = 80G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 120
innodb_io_capacity = 10000
innodb_io_capacity_max = 12000
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_format=Barracuda
innodb_log_group_home_dir = /data/3306/redo
innodb_undo_directory = /data/3306/undo
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
#innodb_flush_neighbors = 0
innodb_log_file_size = 1G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1

########other settings########
key_buffer_size = 256M
#myisam_recover = BACKUP,FORCE
max_heap_table_size = 67108864
read_only=on

[mysqldump]
max_allowed_packet = 64M

[client]
port = 3306
socket = /data/3306/mysql.sock

vi /etc/sysctl.cnf

net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
net.ipv4.tcp_max_tw_buckets = 20000
net.ipv4.tcp_sack = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.netdev_max_backlog = 262144
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_synack_retries = 1
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 20
net.ipv4.tcp_keepalive_time = 30
net.ipv4.ip_local_port_range = 1024 65000
vm.overcommit_memory = 1
vm.swappiness = 1
vm.vfs_cache_pressure = 200
vm.zone_reclaim_mode = 0
##add
vm.dirty_background_ratio=5
vm.dirty_ratio=10
##
net.ipv4.ip_forward = 0
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2