mysql配置文件
[mysqld] （已验证）
#基础设置
port = 3306
bind-address = 0.0.0.0
lower_case_table_names=1
character-set-server=utf8mb4
default-storage-engine=innoDB
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
log-error=/var/log/mysql/mysql.log
pid-file=/usr/local/mysql/mysql.pid
#扩展设置
max_connections = 500
max_connect_errors = 600
connect_timeout=10
wait_timeout = 300
interactive_timeout = 300
back_log = 300
open_files_limit = 65535
table_open_cache = 16000
max_allowed_packet = 500M
max_heap_table_size = 64M
tmp_table_size = 256M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 256M
thread_cache_size = 64
thread_stack = 512K
ft_min_word_len = 1
skip-external-locking
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 32M
net_retry_count = 100
auto_increment_increment=0
auto_increment_offset=0
explicit_defaults_for_timestamp=false
log_bin_trust_function_creators=1
performance_schema= 0
transaction-isolation = REPEATABLE-READ
query_cache_size = 0
query_cache_type = 0
#binlog日志
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id=1
max_binlog_size = 512M
binlog_format = MIXED
log_slave_updates = 0
expire_logs_days = 7
max_relay_log_size = 512M
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
binlog_cache_size = 20M
max_binlog_cache_size = 15M
#慢查询
slow_query_log=1
long_query_time=2
log-queries-not-using-indexes = TRUE
log_throttle_queries_not_using_indexes=1000
min_examined_row_limit=1000
log-slow-admin-statements = TRUE
log-slow-admin-statements = TRUE
#innodb引擎
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_buffer_pool_size = 48G
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 128M
innodb_log_file_size = 128M
innodb_max_dirty_pages_pct = 85
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
innodb_data_file_path = ibdata1:10M:autoextend
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_status_file = 1
#############################数据库配置文件基本优化内容
symbolic-links=0
server-id = 1
binlog-cache-size = 32M #设置二进制日志缓存大小
sync-binlog = 1 #每隔N秒将缓存中的二进制日志记录写回硬盘
max_binlog_cache_size = 8M #最大的二进制Cache日志缓冲尺寸
max_binlog_size = 1G #单个二进制日志文件的最大值，默认1G，最大1G
log-bin-index = /var/lib/mysql/mysql-bin.index #binlog索引文件位置
log-bin = /var/lib/mysql/mysql #binlog日志存放目录
expire_logs_days = 10
back_log = 1000 #指出在MySQL暂时停止响应新请求之前，短时间内的多少个请求
max_connections = 1000 #指定MySQL允许的最大连接进程数，show global variables like '%connections%'; http://elf8848.iteye.com/blog/1847445
max_user_connections = 1000 #单用户最大的连接数，max_user_connections < 实例 max_user_connections < max_connections
max_connect_errors = 10000 #默认为10，设置每个主机的连接请求异常中断的最大次数，超过后会blocked，连接成功后初始0，出现错误后需要flush hosts
max_allowed_packet = 8M #服务器一次能处理的最大的查询包的值
wait_timeout = 360 #指定一个请求的最大连接时间
interactive_timeout = 360
slow_query_log = on #开启慢查询
log-queries-not-using-indexes #记录所有没有使用到索引的查询语句
long_query_time = 2 #指定多少秒未返回结果的查询属于慢查询
min_examined_row_limit = 5 #记录那些由于查找了多余5次而引发的慢查询
log-slow-admin-statements #记录那些慢的OPTIMIZE TABLE,ANALYZE TABLE和ALTER TABLE语句
log-slow-slave-statements #记录由slave所产生的慢查询
slow_query_log_file = /var/log/mariadb/slow.log #指定慢查询日志文件路径
table_cache = 64 #表分配的内存，物理内存越大，设置就越大
table_open_cache = 128 #设置高速缓存表的数目
thread_cache_size = 64 #服务器线程缓存数，与内存大小有关(建议大于3G设置为64)
query_cache_size = 32M #指定MySQL查询缓冲区的大小
query_cache_limit = 2M #只有小于此设置值的结果才会被缓存
query_cache_min_res_unit = 2k #设置查询缓存分配内存的最小单位
key_buffer_size = 512M #指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能
sort_buffer_size = 2M #设置查询排序时所能使用的缓冲区大小，系统默认大小为2MB
join_buffer_size = 1M #联合查询操作所能使用的缓冲区大小
read_buffer_size = 4M #读查询操作所能使用的缓冲区大小
read_rnd_buffer_size = 16M #设置进行随机读的时候所使用的缓冲区
thread_stack = 192K #设置Mysql每个线程的堆栈大小，默认值足够大，可满足普通操作
bulk_insert_buffer_size = 8M
default-storage-engine=InnoDB #默认表的类型为InnoDB
innodb_old_blocks_time =1000 #减小单次的大批量数据查询,默认为0，调整后性能提升80% http://www.cnblogs.com/cenalulu/archive/2012/10/10/2718585.html
innodb_flush_method = O_DIRECT #从innode刷新到磁盘，不经过系统write,fdatasync(默认)，O_DSYNC，O_DIRECT http://blog.csdn.net/jiao_fuyou/article/details/16113403
innodb_additional_mem_pool_size = 16M #设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 8 #服务器有几个CPU就设置为几，建议用默认设置，一般设为8
innodb_flush_log_at_trx_commit = 2 #设置为0就等于innodb_log_buffer_size队列满后再统一存储，默认为1
innodb_lock_wait_timeout = 120 #InnoDB事务被回滚之前可以等待一个锁定的超时秒数
innodb_file_per_table = 1 #InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间,0关闭,1开启
innodb_autoextend_increment = 256
#######################################################参考借鉴###################################################################
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 100G
table_open_cache = 4096
sort_buffer_size = 16M
net_buffer_length = 4K
read_buffer_size = 16M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 256M
thread_cache_size = 512
tmp_table_size = 512M
default_authentication_plugin = mysql_native_password
lower_case_table_names = 1
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
binlog_expire_logs_seconds = 600000
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 2048M
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 32
innodb_write_io_threads = 32
[mysqldump]
quick
max_allowed_packet = 500M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
############################### 宝塔sql配置###################################
[mysqld]
###### 宝塔mysql配置################
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 100G
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 4K
read_buffer_size = 4M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
query_cache_size = 128M
tmp_table_size = 128M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/var/log/mysql/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""
innodb_data_home_dir = /var/lib/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/data
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 8
innodb_write_io_threads = 8
