Mariadb/MySQL生产环境的my.cnf配置示例
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.针对MySQL主程序mysqld定义参数[mysqld]
1>.skip-networking=1
关闭网络连接，只侦听本地客户端， 所有和服务器的交互都通过一个socket实现，socket的配置存放在"/var/lib/mysql/mysql.sock",可在/etc/my.cnf修改
[root@node102.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# grep -v ^# /etc/my.cnf #关闭网络，将"skip-networking"的Value设置为1即可。 [mysqld] skip-networking #关闭网络，此处我们只需要把该属性写在这里就表示启用了，除非你显示指定其值为0(表示不启用),如果我们这样写默认是启用了 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# systemctl restart mariadb #重启服务，发现MySQL服务在正常运行但是并未对外暴漏端口。 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ps aux | grep mysql | grep -v grep #MySQL服务处于正常运行状态 root 8556 0.0 0.1 234060 4184 pts/0 S+ 07:50 0:00 mysql mysql 10132 0.0 0.0 113308 1624 ? Ss 10:38 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr mysql 10306 0.0 2.0 968688 81204 ? Sl 10:38 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/li b64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sockroot 10342 0.0 0.1 234060 4180 pts/1 S+ 10:38 0:00 mysql -uroot -px xxxxxxxxx [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie #本机基于socket文件依旧是可以正常访问的哟~ Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 2 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.64-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 19 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.210 -------------- MariaDB [(none)]>
2>.datadir
指定MySQL数据库存放数据的目录。
3>.socket
指定socket文件路径。
4>.symbolic-links
是否支持软连接，若不支持指定的路径不能为软连接。
5>.innodb_file_per_table
该参数在mysql5.6.6及其后续版本默认开启，开启该参数的时候，Innodb将每个新创建的表的数据及索引存储在一个独立的.ibd文件里，而不是系统的表空间。当这些innodb表被删除或清空的时候，存储空间会被回收。 开启innodb_file_per_table(=ON)： 在ALTER TABLE操作重建表的情况下，会将innodb表从系统共享表空间移动到独立的.ibd文件。 不开启innodb_file_per_table(=OFF): innodb会将所有表及索引的数据存储在构成系统表空间的ibdata文件。这样会降低文件系统操作的性能开销，例如DROP TABLE 或 TRUNCATE TABLE。它最适合于将整合磁盘都用于存储mysql数据的情况，因为系统表空间不会收缩，所有的数据库都在一个空间实例里面。当innodb_file_per_table=OFF的时候，应避免在空间受限的系统表空间里导入大量临时数据。 innodb_file_per_table 可通过SET GLOBAL动态的修改为ON或OFF，也可以在my.cnf中做永久性修改，在my.cnf中修改后生效的话需要重启mysqld服务。
6>.skip_name_resolve
禁止主机名解析，建议使用。他会将IP地址解析成主机名而这个过程依赖于DNS服务，而你的网络中没有做名称解析可能会导致网络连接非常慢。 博主推荐阅读: https://www.cnblogs.com/ivictor/p/5311607.html
7>.character-set-server
指定服务端的字符集，例如:character-set-server=utf8mb4
8>.innodb_file_per_table("innodb_file_per_table = 1" 打开独立表空间)
Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间. 共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。 优点： 可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。 缺点： 所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。 独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。 优点： 1>.每个表都有自已独立的表空间。 2>.每个表的数据和索引都会存在自已的表空间中。 3>.可以实现单表在不同的数据库中移动。 4>.空间可以回收（除drop table操作处，表空不能自已回收） Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。 缺点： 1>.单表增加过大，如超过100个G。 2>.相比较之下，使用独占表空间的效率以及性能会更高一点。 开启独立表空间，在配置文件（my.cnf）的[mysqld]中设置： innodb_file_per_table = 1（MariaDB 10.2.x版本默认是开启的哟~）
9>.skip-grant-tables
忽略授权表，这意味着将所有用户的授权操作给取消了。换句话说，关闭MySQL服务器的认证,从而可以重置root的密码。
10>.userstat
开启查看索引使用工具。
11>.max_connections = 8000
MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值。
这个值不是越大越好，而是需要根据你的服务器性能而定，若这个值设置过小可能会出现上面的报错信息，但是设置过大就会导致服务器性能扛不住啦！
12>.back_log = 300
back_log 是操作系统在监听队列中所能保持的连接数
13>.max_connect_errors = 1000
每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息
14>.open_files_limit = 10240
所有线程所打开表的数量。
15>.max_allowed_packet = 32M
每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
16>.wait_timeout = 10
指定一个请求的最大连接时间
17>.sort_buffer_size = 16M
排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
18>.join_buffer_size = 16M
不带索引的全表扫描.使用的buffer的最小值
19>.query_cache_size = 128M
查询缓冲大小
20>.query_cache_limit = 4M
指定单个查询能够使用的缓冲区大小，缺省为1M
21>.transaction_isolation = REPEATABLE-READ
设定默认的事务隔离级别
22>.thread_stack = 512K
线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次连接时被预留.
23>.log-bin
开启二进制日志功能,若不指定路径则默认和数据存放在同一目录，生产环境强烈建议将MySQL日志和数据分开存放。
24>.binlog_format=row
指定默认的二进制日志格式。
二进制日志记录三种格式: 基于“语句”记录：
statement，只记录语句，默认模式。该模式存在弊端，比如执行"UPDATE students SET birth = now();"无法保存具体的时间戳，若按照该语句进行还原数据准确性肯定出现问题。 基于“行”记录：
row，只记录数据，即直接将数据存储下来，但日志量较大。适合数据相对来说重要的场景。推荐使用这种模式，数据恢复时准确的最高，但带来的代价就是得牺牲更多的磁盘空间。建议健康磁盘剩余空间进行及时扩充。 混合模式：
mixed,相对来说比较折中的方式,让系统自行判定该基于哪种方式进行。 查看格式配置: SHOW VARIABLES LIKE 'binlog_format';
25>.innodb_buffer_pool_size = 24G
InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小的80%
26>.innodb_file_io_threads = 4
用来同步IO操作的IO线程的数量
27>.innodb_thread_concurrency = 16
在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
28>.innodb_log_buffer_size = 16M
用来缓冲日志数据的缓冲区的大小
29>.innodb_log_file_size = 512M
在日志组中每个日志文件的大小
30>.innodb_log_files_in_group = 3
在日志组中的文件总数
31>.innodb_lock_wait_timeout = 120
SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
32>.long_query_time = 2
慢查询时长
33>.log-queries-not-using-indexes
将没有使用索引的查询也记录下来
34>.更多服务器参数信息
由于Mysqld参数有很多，我这里就不能一一列举啦，我后面博客中也整理了很多参数，但并没有统一罗列再次，不过万变不离其宗，更多服务器参数咱们都可以在官网查阅到。官方给出了最权威的说明。 博主推荐阅读: https://www.cnblogs.com/yinzhengjie/p/11758208.html https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/
二.针对MySQL安全性的参数[mysqld_safe]
1>.pid-file
指定进程的pid号存放路径。
案例:
pid-file = /mysql/3306/pid/mariadb.pid
2>.log-error
存放错误日志文件。
案例:
log-error = /mysql/3306/log/mariadb.log
三.针对MySQL客户端参数[mysql]
1>.default-character-set
指定客户端默认字符集，例如：default-character-set=utf8mb4
2>.safe-updates
当用户使用UPDATE或者DELETE语句时需要加WHRER关键词进行过滤，否则无法对表中的数据进行修改。
如果用户没有加WHERE继续过滤会有如下报错信息:
"ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"
五.MYSQL配置最佳实践
高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能” 博主推荐阅读： 阿里巴巴Java开发手册 http://zhuanlan.51cto.com/art/201702/531364.htm(58到家数据库30条军规解读)