muyl
3/8/2018 - 12:44 AM

mysql 配置文件配置

[mysqld]
########basic settings######## 
port = 3306
user = mysql
basedir = /usr
datadir = /mysql/data
socket = /tmp/mysql.sock
pid_file = mysql.pid
character_set_server = utf8
skip-name-resolve = 1
skip_external_locking = 1
lower_case_table_names = 1
local_infile = 0
interactive_timeout = 1800
wait_timeout = 1800
max_connections = 1024
max_connect_errors = 1000000
thread_cache_size = 400
max_allowed_packet = 16M
table_open_cache = 512
table_definition_cache = 1024
query_cache_type = 0
query_cache_size = 0
tmp-table-size = 32M
max-heap-table-size = 32M
########log settings########
log_error = mysql-err.log
slow_query_log = 1
long_query_time = 2
log-queries-not-using-indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_file = mysql-slow.log
########replication settings###########################
auto_increment_increment = 2
auto_increment_offset = 1 #从机为2
server-id = 1 #从机为2
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
relay-log = mysqld-relay
expire_logs_days = 15
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
######################### slave crash safe #############
relay_log_info_repository = TABLE 
master_info_repository = TABLE
relay_log_recovery = ON 
########innodb settings#####################################
innodb_io_capacity = 500 # SSD 2000 ~ 20000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_file_per_table = ON
innodb_lock_wait_timeout = 5
innodb_buffer_pool_size = 196G
innodb_log_buffer_size = 8388608
innodb_print_all_deadlocks = ON
innodb_flush_log_at_trx_commit = 2
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:512M:autoextend
innodb_autoextend_increment = 64
innodb_thread_concurrency = 0
innodb_old_blocks_time = 1000
innodb_buffer_pool_instances = 8
innodb_lru_scan_depth = 512
innodb_flush_neighbors = 1 # SSD必须设置为0
innodb_checksum_algorithm = crc32
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 40
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
[mysql]
socket = /tmp/mysql.sock
prompt=(\\u@\\h) [\\d]>\\_