Mysql调优参数参考：本人太懒自己百度参数的意思。
[mysqld] user=mysql #datadir=/usr/local/mysql2/data datadir=/home/data#数据文件存放路径 basedir=/usr/local/mysql2 port=23306#端口
binlog
log_bin = on#开启bin_log日志 binlog_format = row#bin_log存储方式 sync_binlog = 1 #bin_log存储时间 binlog_cache_size = 524288#bin_log缓冲区大小 binlog_stmt_cache_size = 524288#bin_log单文件大小 expire_logs_days = 7#bin_log保留时间 #log_bin_basename = /home/logs/mysql #log_bin_index =/home/logs/mysql.index log_bin_trust_function_creators = ON binlog_order_commits = off
slow log
slow_query_log = on#慢查询日志 slow_query_log_file = /home/logs/mysql-slave2-slow.log#慢查询日志位置 long_query_time = 0.5#慢查询记录sql的时间 log_queries_not_using_indexes = on
interactive_timeout = 300 wait_timeout = 300 server_id = 10110075 skip_external_locking = ON skip_name_resolve = ON character_set_server = utf8 open_files_limit = 15000
transaction-isolation = READ-COMMITTED back_log = 100
常规缓存设置
join_buffer_size = 2M key_buffer_size = 128M query_cache_size = 0 query_cache_type = OFF read_buffer_size = 2M read_rnd_buffer_size = 2M max_allowed_packet = 64M sort_buffer_size = 2M table_definition_cache = 2000 table_open_cache = 4000 table_open_cache_instances = 4 lower_case_table_names=1 max_connections = 2000
线程相关设置
thread_cache_size = 500
gtid 相关设置
#gtid_mode = ON #enforce_gtid_consistency = ON log_slave_updates = ON
innodb 配置
innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON innodb_buffer_pool_instances = 8 innodb_buffer_pool_size = 40G #innodb_data_file_path = ibdata1:256M:autoextend innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 128M innodb_log_file_size = 512M innodb_open_files = 400 innodb_print_all_deadlocks = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_sort_buffer_size = 16M innodb_thread_concurrency = 48 innodb_io_capacity = 4000 innodb_io_capacity_max = 20000 innodb_lru_scan_depth = 256
sql mode
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
微信扫一扫，打赏作者吧～
