Centos7下 /etc/my.cnf完整配置

Centos7下 /etc/my.cnf

完整 my.cnf

[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
#user = root  
#pass = 123456 
  
#demo,this is a simple case!  
#[mysqld3306]    
#port = 3306  
#server_id = 3306  
#basedir =/usr/local/mysql  
#datadir =/usr/local/mysql/data3306  
#socket =/tmp/mysql3306.sock    
#log-error =/var/log/mysqld3306.log  
#pid-file =/var/run/mysqld/mysqld3306.pid  
 
 
[mysqld3306]  
#
#*basic
#  
port = 3306 
server_id = 3306 
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3306
socket =/tmp/mysql3306.sock    
pid-file =/var/run/mysqld/mysqld3306.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''
 
#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3
 
#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3306.log
long_query_time=5
log-error =/var/log/mysqld3306.log  
 
#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M
 
#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1
 
#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  
 
#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728
 
#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1
 
#----------------------------------------------------------------------------------
 
[mysqld3307]  
#
#*basic
#  
port = 3307  
server_id = 3307  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3307 
socket =/tmp/mysql3307.sock    
pid-file =/var/run/mysqld/mysqld3307.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''
 
#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3 
 
#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3307.log
long_query_time=5
log-error =/var/log/mysqld3307.log  
 
#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M
 
#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1
 
#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  
 
#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728
 
#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1
 
 
#------------------------------------------------------------------------
[mysqld3308]  
#
#*basic
#  
port = 3308  
server_id = 3308  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3308 
socket =/tmp/mysql3308.sock    
pid-file =/var/run/mysqld/mysqld3308.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''
 
#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3
 
#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3308.log
long_query_time=5
log-error =/var/log/mysqld3308.log  
 
#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M
 
#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1
 
#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  
 
#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728
 
#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1