mysql调优参数

 

 my.conf

[client]
port        = 端口
socket        = sokcet位置

[mysqld]
basedir             = mysql位置
port            = 3306
socket            = sokcet位置
datadir            = data目录
pid_file        = mysqld.pid位置
bind_address        = 0.0.0.0

lower_case_table_names  = 1
collation_server        = utf8_bin
character_set_server    = utf8

skip_name_resolve
skip_external_locking
#explicit_defaults_for_timestamp=0
log_bin_trust_function_creators = 1  #开启二进制日志 

tmp_table_size  = 24M
tmpdir            = tmp文件位置
back_log        = 1024

max_connections         = 1500
#max_user_connections    = 80
max_connect_errors      = 2000  # 超过2000个连接就报错

table_open_cache        = 20480
table_definition_cache  = 20480
table_open_cache_instances = 16
open_files_limit = 655340

binlog_cache_size           = 2M
bulk_insert_buffer_size     = 64M
ft_min_word_len             = 4
join_buffer_size            = 32M
key_buffer_size             = 64M
max_allowed_packet          = 1024M
max_heap_table_size         = 64M
myisam_repair_threads       = 1
myisam_sort_buffer_size     = 24M
read_buffer_size            = 8M
read_rnd_buffer_size        = 8M
sort_buffer_size            = 14M
thread_cache_size           = 512
thread_stack                = 192K
sql_mode                    = NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

# log
general_log                 = off
general_log_file            = general位置
log_error_verbosity         = 3
log_error                   =  错误日志位置

log_timestamps              = SYSTEM
slow_query_log              = on
slow_query_log_file         = slow sql日志位置
long_query_time             = 2
log_slow_admin_statements = on
log_slow_slave_statements = on
binlog_rows_query_log_events = on

# replication
read_only=0
super_read_only=0
log_slave_updates
skip_slave_start
slave_compressed_protocol = off
transaction_write_set_extraction = off
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON
binlog_checksum = CRC32
#binlog_group_commit_sync_delay = 50
#binlog_group_commit_sync_no_delay_count =100

binlog_format               = row
binlog_row_image            = full
max_binlog_size             = 500M
expire_logs_days            = 7
gtid_mode                   = on
enforce_gtid_consistency    = on
gtid_executed_compression_period = 1000
log-bin                     = mysql-bin 位置
relay_log                   = mysqld-relay-bin位置
master_info_repository      = table
relay_log_info_repository   = table
server-id                   = 623306
slave_net_timeout           = 60
#slave_checkpoint_group      = 512
#slave_checkpoint_period     = 300
slave_load_tmpdir            = tmp位置
slave_parallel_workers      = 4
slave_pending_jobs_size_max = 128M
sync_binlog                 = 1

report_port        = 3306
#report_host        = pattern_ip

default_storage_engine          = InnoDB
disabled_storage_engines        = "MyISAM,MEMORY"
# innodb
innodb_print_all_deadlocks
innodb_autoextend_increment     = 64
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup  = on
innodb_buffer_pool_instances    = 4
innodb_buffer_pool_size         = 48G
innodb_data_file_path           = ibdata1:64M:autoextend
innodb_data_home_dir            = ibdata位置
innodb_fast_shutdown            = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_flush_neighbors          = 0
#innodb_io_capacity              = 3000
innodb_io_capacity_max          = 1000
innodb_lock_wait_timeout        = 50
innodb_log_buffer_size          = 32M
innodb_log_file_size            = 500M
innodb_log_files_in_group       = 4
innodb_log_group_home_dir       = iblog位置
innodb_max_dirty_pages_pct      = 75
innodb_online_alter_log_max_size = 1342177280
innodb_open_files               = 64535
innodb_purge_threads            = 4
#innodb_read_io_threads          = 12
innodb_read_io_threads          = 12
innodb_sort_buffer_size         = 4M
innodb_thread_concurrency       = 0
innodb_undo_directory           =iblog位置
innodb_undo_logs                = 128
innodb_undo_tablespaces         = 2
#innodb_write_io_threads         = 16
innodb_file_format              = Barracuda
innodb_file_format_max          = Barracuda
transaction_isolation           = READ-COMMITTED
secure_file_priv =

# plugin
plugin_load = rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
# Semi_sync
rpl_semi_sync_master_enabled                = 1
rpl_semi_sync_slave_enabled                 = 1
rpl_semi_sync_master_timeout                = 1000
rpl_semi_sync_master_wait_point             = AFTER_SYNC
rpl_semi_sync_master_wait_no_slave          = off
rpl_semi_sync_master_wait_for_slave_count   = 1

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash
prompt=\\u@\\v \\d \\r:\\m:\\s>
default-character-set=utf8