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