006、参数文件
MySQL数据库中的各类文件主要包括：
- 参数文件
- 日志文件
- Socket文件
- Pid文件
- Mysql表结构文件
- 存储引擎文件(以innodb为主介绍)
MySQL的参数文件
[root@ning ~]# cat /etc/my.cnf #本配置文件适用于64G内存，32核CPU，RAID10磁盘阵列
[client]
port = 3306 #默认端口号
socket = /tmp/mysql.sock #本地socket文件
#default-character-set=utf8
[mysql]
#default-character-set=utf8
[mysqld]
port = 3306 #服务端口号
socket = /tmp/mysql.sock
basedir = /usr/local/mysql #安装目录
datadir = /u01/data/mysql #数据目录
open_files_limit = 3072 #打开文件数的限制，和操作系统部分也有关联ulimit -a,一般设置为不限制
back_log = 103 #打开的标签数，保持默认即可
max_connections = 512 #数据库能承受的最大连接数
max_connect_errors = 100000 #链接能承受的最大错误数
table_open_cache = 512 #打开的表缓存的数量
external-locking = FALSE #用于多进程条件下为MyISAM数据表进行锁定
max_allowed_packet = 32M #能承受的最大包的大小，应用程序传入的，调整数，倍数调整
sort_buffer_size = 2M #针对sql 的排序缓存、索引排序缓存
join_buffer_size = 2M #针对sql 的连接缓存
thread_cache_size = 51 #当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁
#query_cache_size = 32M #数据库查询缓存大小，该参数没太大用，可以注释掉，该区域保存的是静态sql，mysql 8已废弃
tmp_table_size = 96M #控制内存临时表的最大值,超过限值后就往硬盘写(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)
max_heap_table_size = 96M #用户可以创建的内存表(memory table)的大小。
slow_query_log = 1 #慢查询日志，优化部分会讲到，1代表开启
slow_query_log_file = /u01/data/mysql/slow.log #慢查询日志的位置
log_error = /u01/data/mysql/error.log #MySQL的错误日志，非常重要
long_query_time = 0.05 #慢查询的时间，超过该值，就会记录该sql语句到慢查询日志中，生产中建议该时间0.1以上。
server_id = 1113306 #该机器的唯一标识号，一般以IP地址的最后一栏加端口号命名
log_bin = /u01/data/mysql/mysql_bin #binlog日志的位置，mysql_bin代表日志名称
sync_binlog = 1 #binlog日志的刷新机制，详情见第9课
binlog_cache_size = 4M #binlog日志缓存的大小，指一个事务占用的最大的缓存
max_binlog_cache_size = 8M #表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size = 1024M #最大的binlog日志的大小，到最大之后会发生日志切换
expire_logs_days = 7 #binlog日志的过期时间，即保存期限，如果空间足够，建议该时间长一点
key_buffer_size = 32M #myisam存储引擎使用，可以删除，myisam缓存区(buffer pool)的大小
read_buffer_size = 1M #myisam存储引擎使用，可以删除
read_rnd_buffer_size = 16M #myisam存储引擎使用，可以删除
bulk_insert_buffer_size = 64M #插入缓冲的大小，64m足够使用
character_set_server=utf8 #数据库的字符集
default_storage_engine=InnoDB #默认的存储引擎，不写该参数，存储引擎默认也是innodb
binlog_format=row #binlog日志的格式，有三种：statement,row,mixed
#gtid_mode=on
#log_slave_updates=1
#enforce_gtid_consistency=1
interactive_timeout=300 #交互式等待时间
wait_timeout=300 #非交互式等待时间，与上面的参数会同步修改，即修改一个另一个同时发生改变，值要一致
transaction_isolation = REPEATABLE-READ #事务的隔离级别，MySQL针对innodb 的默认事务隔离级别，该值代表的是可重复读
innodb_additional_mem_pool_size = 16M #额外缓冲池大小，没太大用，可以注释掉
innodb_buffer_pool_size = 1434M #数据库内存值的大小，建议设置为物理内存的50% - 80%,前提条件是：该服务器只提供给数据库使用
innodb_data_file_path = ibdata1:1024M:autoextend #innodb共享表空间的大小，默认值是10M，建议调整为1G，autoextend代表可自动扩展
innodb_flush_log_at_trx_commit = 1 #redo log buffer的刷新机制，详情见第9课
innodb_log_buffer_size = 16M #redo log buffer的大小
innodb_log_file_size = 300M #redo log文件的大小
innodb_log_files_in_group = 2 #redo log每组文件的个数
innodb_max_dirty_pages_pct = 50 #脏页占比大小，占比到达该值，会把脏数据写入物理文件
innodb_file_per_table = 1 #代表数据库使用的是独立表空间，若不指定，则所有表数据都在ibdata1中
innodb_locks_unsafe_for_binlog = 0 #事务隔离级别相关，控制innodb是否对gap加锁
[mysqldump]
quick
max_allowed_packet = 32M #Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败binlog日志的格式：statement，row，mixed，生产中，一定要选择row模式，最不能用的一种是statement,是记录成sql形式，容易丢数据。而mixed，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
[client]--这3行不也能省略，若省略，则本地认证时，就回去/usr/local/mysql下找socket文件，造成连不上数据库的报错。
指定了默认端口和socket文件(本地连接认证)
[mysqld]--MySQL的服务，若添加新的参数，在这部分添加，不要再参数文件的最底部添加，在最底部添加会不生效，因为最底部属于[mysqldump]
MySQL的参数分为动态参数和静态参数，动态参数是指，在数据库启动时可以修改，立即生效。静态参数是指数据库启动之后无法修改，只能修改参数文件，并且重启数据库生效。
数据库中调整参数的方法：
1、直接在数据库中修改：
mysql> set global max_connections=1000;
Query OK, 0 rows affected (0.00 sec)在数据库中可以直接修改的参数叫做动态参数。set global代表全局生效，global表示全局修改，退出session依旧生效。
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.00 sec)max_connections参数不支持会话级修改：
mysql> set max_connections=1000;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL会话级修改：set session ，只支持当前会话，退出会话后，参数修改立即消失。
2、直接修改参数文件
global表示全局修改，退出session依旧生效，但是重启数据库会失效，若想参数一直生效，则直接修改参数文件：
[root@ning ~]# vi /etc/my.cnf3、静态参数只能通过参数文件修改
例如：
mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable1、max_connections=512
该参数代表数据库能接受的最大连接数。
在参数文件中查看：
[root@ning ~]# cat /etc/my.cnf|grep connect
max_connections = 512在数据库中查看：
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 512 |
+-----------------+-------+
1 row in set (0.00 sec)数据库经常因为该参数设置不正确导致出现问题：外部应用连接数若超过该参数的设置，则会连接不上数据库，会报错：too many connections。
解决方法1，根据数据库的压力：set global max_connections=1000适当调大参数的大小
解决方法2，调整参数的大小，控制并发：innodb_thread_concurrency，该参数默认值为0，代表Innodb的并发控制，0代表不受控制。可以调整为几百到几千。
解决方法3，修改会话的等待时间参数： interactive_timeout(交互式等待时间)，wait_timeout(非交互式等待时间)，默认值是28800秒，表示在等待这么长时间后无任何操作，就断开连接。一个链接占用大概20M内存。生产环境建议参数值设置为300-500秒。这两个参数会同步修改，修改一个，另一个会同步修改。
解决方法4，直接结束会话：建议结束状态是查询的会话，不要结束状态是update的会话：
mysql> show full processlist;
+----+------+------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+-------+-----------------------+
| 15 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 16 | root | ning:45004 | NULL | Sleep | 13 | | NULL |
+----+------+------------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
mysql> kill 16;
Query OK, 0 rows affected (0.00 sec)
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 15 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+1、innodb_log_file_size
Mysql中的redo log文件：
[root@ning mysql]# pwd
/u01/data/mysql
[root@ning mysql]# ls -lh *logfile*
-rw-rw----. 1 mysql mysql 256M Mar 17 19:51 ib_logfile0
-rw-rw----. 1 mysql mysql 256M Mar 16 20:59 ib_logfile1查看参数设置：
mysql> show variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| innodb_log_file_size | 314572800 |
+----------------------+-----------+
1 row in set (0.00 sec)redo日志文件的大小，在配置文件中有定义：
[root@ning mysql]# cat /etc/my.cnf|grep file_size
innodb_log_file_size = 256M修改完该参数大小，并且重启数据库后，日志文件大小会跟随改变：
[root@ning mysql]# ls -lh *logfile*
-rw-rw----. 1 mysql mysql 300M Mar 17 21:19 ib_logfile0
-rw-rw----. 1 mysql mysql 300M Mar 17 21:19 ib_logfile12、innodb_flush_log_at_trx_commit
查看该参数：
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | ON |
| binlog_order_commits | ON |
| innodb_api_bk_commit_interval | 5 |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
5 rows in set (0.00 sec)innodb_flush_log_at_trx_commit参数的值影响redo log buffer的刷新，该参数有3个值：
- 0：每隔1秒刷新一次
- 1：默认值，实时刷新，只要有写入，就刷新到redo log file，该值也是最安全的设置
- 2：交由操作系统管理
3、innodb_max_dirty_pages_pct
该参数是脏页占有buffer pool的比例：
mysql> show variables like '%dirty%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_max_dirty_pages_pct | 50 |
| innodb_max_dirty_pages_pct_lwm | 0 |
+--------------------------------+-------+
2 rows in set (0.00 sec)该值表示，当脏页占比达到50%的时候，触发写机制，将脏页写入磁盘。
该值需要经过压力测试报告后确定，一般在25-50之间。
4、sync_binlog
该参数值表示binlog的刷新条件。
mysql> show variables like '%sync_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)该参数有0,1,2……N个值。
- 0：交由操作系统管理
- 1：实时刷新
- 2：每2个事务刷新一次，n代表每N个事务刷新一次。
5、innodb_purge_threads
该参数默认值是1，参数值代表purge thread线程的数量，最大值为32，建议直接调整为32，线程数在1-32之间根据系统需求自动调起，并不是直接启用32个线程。
mysql> show variables like '%purge_thread%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_purge_threads | 1 |
+----------------------+-------+6、binlog_rows_query_log_events
该参数是mysql5.6的新特性，意义在于：在binlog row格式下，也可以看到完整的sql语句
mysql> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF |
+------------------------------+-------+
1 row in set (0.00 sec)7、innodb_doublewrite
查看数据库的二次写是否开启，该参数是保证数据安全的机制。
mysql> show variables like 'innodb_doublewrite';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_doublewrite | ON |
+--------------------+-------+
1 row in set (0.00 sec)8、innodb_adaptive_hash_index
数据库的自适应哈希索引
mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON |
+----------------------------+-------+
1 row in set (0.00 sec)9、innodb_file_per_table
该参数开启之后，每个表都会以单独的文件存放
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)10、secure_file_priv
mysql文件的导入和导出路径有默认的设置，即 secure-file-priv,当传入或传出的csv文件路径与默认的路径冲突时就会报错。修改该参数需要修改参数文件，并重启数据库。
mysql> show variables like '%secure%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_auth | ON |
| secure_file_priv | |
+------------------+-------+
2 rows in set (0.00 sec)
浙公网安备 33010602011771号