Mariadb 配置文件模板(/etc/my.cnf)

摘要：以下配置文件为本人所在公司安装时所用模板。微信号从库关闭重要参数从库设置为从库设置为

以下配置文件为本人所在公司安装mairadb时所用模板。

### /etc/my.cnf
### Author: makoo
### 微信号: MariaDBA
### QQ:3543400
[client]
port                           = 3306
socket                         = /data/mysql/mysql.sock

[mysqld]
port                           = 3306
socket                         = /data/mysql/mysql.sock
basedir                        = /data/mysql
datadir                        = /data/mysql/data
tmpdir                         = /data/mysql/data

### skip-character-set-client-handshake
log_bin_trust_function_creators = 1
innodb_print_all_deadlocks      = 1
skip-external-locking
skip-name-resolve
autocommit                     = 1
innodb_thread_concurrency      = 8
innodb_defragment              = 1

character_set_server           = utf8
init_connect                   = "SET NAMES utf8"
init_connect                   = "SET collation_connection = utf8_general_ci"

# 从库关闭binlog
log-bin                        = binlog
binlog_format                  = ROW
max_binlog_size                = 256M
expire_logs_days               = 1
binlog_cache_size              = 64K

# 
server-id                      = 1001
innodb_data_home_dir           = /data/mysql/data
innodb_data_file_path          = ibdata1:100M:autoextend
innodb_log_group_home_dir      = /data/mysql/data
innodb_log_file_size           = 128M
innodb_log_buffer_size         = 8M

# 重要参数
innodb_buffer_pool_size        = 12288M  #51200M  #10240M
innodb_flush_method            = O_DIRECT
innodb_file_per_table          = 1
innodb_file_format             = barracuda
innodb_flush_log_at_trx_commit = 0

# CACHES AND LIMITS #
query-cache-type               = 0
query-cache-size               = 0
open_files_limit               = 65535
innodb_open_files              = 8192
table_open_cache               = 8192
thread_cache_size              = 200
max_connections                = 16384
max_user_connections           = 10000
extra_max_connections          = 100
max_connect_errors             = 5000

# REPLICATION #
read_only                      = 0        ### 从库设置为1
event_scheduler                = 1        ### 从库设置为0
slave-net-timeout              = 60
slave-skip-errors              = 1062
slave_parallel_threads         = 4

wait_timeout                   = 3600
interactive_timeout            = 3600
lock_wait_timeout              = 600
innodb_lock_wait_timeout       = 600
concurrent_insert              = 2
key_buffer_size                = 256M
max_allowed_packet             = 64M

sort_buffer_size               = 2M
read_buffer_size               = 2M
join_buffer_size               = 2M
read_rnd_buffer_size           = 2M
myisam_sort_buffer_size        = 128M

# LOGGING #
log-error                      = db-error.log
slow_query_log                 = 1
long_query_time                = 2
#log-queries-not-using-indexes = 1
slow_query_log_file            = db-slow.log
log_slow_verbosity             = query_plan

tmp_table_size                 = 96M
max_heap_table_size            = 96M

bulk_insert_buffer_size        = 64M
group_concat_max_len           = 102400

[mysqldump]
quick
max_allowed_packet             = 64M

[mysql]
no-auto-rehash