企业级MySQL配置文件详解

1、MySQL配置文件如下：

cat > /etc/my.cnf <<EOF
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.htmla\
[client]                           #mysql客户端配置文件
port = 3306                        #端口号
socket = /app/mysql/mysql.sock     #socket文件
								   
[mysqld]                           #mysql服务端配置文件
basedir = /usr/local/mysql         #mysql安装路径
datadir = /app/mysql               #mysql数据路径
port = 3306                        #mysql默认端口号
socket = /app/mysql/mysql.sock     #socket文件
#transaction-isolation = READ-COMMITTED    #事务隔离级别 有四种级别：串行化（SERIALIZABLE），重复读（REPEATABLE-READ），读提交（READ-COMMITTED），读未提交（READ-UNCOMMITTED）
innodb_io_capacity = 1000          #动态调整刷新脏页的数量    #动态调整：SET GLOBAL innodb_io_capacity = 2000;
max_allowed_packet = 128M          #传输数据过大 max_allowed_packet 参数的设置，默认是1M
join_buffer_size = 4M              #join_buffer_size是按照每次操作join表的操作的次数申请和释放joib_buffer_size
interactive_timeout= 28800         #针对交互式连接超时时间
wait_timeout= 28800                #针对非交互式连接超时时间
max_connections=3000               #MySQL的最大连接数
max_user_connections=800           #限制每个用户的session连接个数
thread_concurrency=8               #mysql线程数设置，一般设置为cpu核数的两倍，说明：在mysql-5.7.2中此参数已被弃用。
key_buffer_size=300M               #MyISAM 存储引擎缓存索引
innodb_log_buffer_size=20M         #缓冲池字节大小
query_cache_size=40M               #查询缓存
read_buffer_size=4M                #MySQL读入缓冲区大小
sort_buffer_size=4M                #connection级参数
innodb_log_file_size=512M          #mysql事务日志文件（ib_logfile0）的大小
innodb_flush_log_at_trx_commit=2   #将事务日志从innodb log buffer写入到redo log中
innodb_autoextend_increment=128M   #调整InnoDB系统表空间的大小
thread_cache_size=64               #线程池缓存大小
innodb_buffer_pool_size=9G         #缓存索引和数据的内存大小
lower_case_table_names = 1         #mysql区分大小写设置
character_set_server = utf8        #mysql字符集设置
init_connect = 'SET NAMES utf8'    #服务器为每个连接的客户端执行的字符串
slow_query_log                     #查询当前慢查询日志的状态
slow_query_log_file = slowquery_   #慢查询日志
long_query_time = 2                #慢查询日志时间设置
log_bin=mysql-bin                  #binlog日志名称设置
skip-name-resolve                  #禁用dns解析
expire_logs_days = 7               #binlog日志文件保留时间
server_id = 62                     #server_id编号
gtid_mode=on                       #mysql  gtid设置
enforce_gtid_consistency=on        #mysql保障事务安全
log-slave-updates=1                #binlog日志更新
skip_slave_start=1                 #主从复制进程不会随着数据库的启动而启动
binlog_format=row                  #mysql主动复制模式，有三种复制模式：STATEMENT模式（SBR），ROW模式（RBR），MIXED模式（MBR）
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
EOF