1.4. 04.数据库文件 ¶

数据库层面：参数文件my.cnf、错误日志error log、慢查询日志slow log、全量日志general log、二进制日志binlog、审计日志audit log、中继日志relay log、套接字文件socket、进程文件pid、表结构文件存储引擎层面：redo log、undo log日志。

1.4.1. 参数文件 ¶

优先级顺序：/etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/my.cnf -> ~/.my.cnf 指定可以使用：–defaults-file参数

核心参数，可以通过show variables like "%参数名%";查看数据库中参数

1、innodb_buffer_pool
innodb_buffer_pool_size = 1024M
缓冲池位于主内存中，缓存被访问的表和索引文件，服务器只有数据库时，可设置为物理内存的50%~80%，分配过多可能导致系统内存不足，出现swap或oom现象。5.7版本支持在线修改大小，前期没必要设置太大

2、innodb_buffer_pool_instance
innodb_buffer_pool_instances = 8
默认为1，5.6.6以后可以设置为多个，表示innodb缓冲区划分区域数，可提高并发性，避免高并发下内存急用问题
只有当innodb_buffer_pool大于1GB时，多实例才生效
若发生宕机，内存中数据会消失，需要重新加载，这个过程是比较慢的，可以同时开启下面两项，实现快速加载(ib_buffer_pool文件，保存内容其实是space number和page number)use information_schema;select space,page_number from INNODB_BUFFER_PAGE limit 3;
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

innodb_data_file_path
innodb_data_file_path = ibdata1:1G:autoextend
指定系统表空间文件路径和ibdata1文件大小，默认10MB，建议1GB

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_max_dirty_pages_pct = 50
影响redo log、binlog、脏页刷新参数

innodb_thread_concurrency = 0
InnoDB内核最大并发线程数，默认为0，表示不限制

interactive_timeout = 600
服务器关闭交互式连接前等待活动的时间，默认28800s(8小时)
wait_timeout = 600
服务器关闭非交互式连接前等待活动的时间，默认28800s(8小时)
这两个参数要一起调整，值要一致，默认的太长，建议300~600s

innodb_flush_method = O_DIRECT
影响InnoDB数据文件、redo log文件的打开刷写模式
可选值：O_SYNC、O_DSYNC、O_DIRECT，建议O_DIRECT，直接从buffer写入磁盘，不通过os buffer
有高速I/O设备，或者队列卡+cache的话，O_DIRECT是最理想的选择

innodb_old_blocks_time = 1000
InnoDB缓冲池缓存数据和索引文件，内部由LRU链表管理，进一步分为old pages list和young pages list，分别存放长时间未被访问的数据页和最新最近访问的数据页。超过innodb_old_blocks_time值的会移动到old list中，默认是1000ms
innodb_old_blocks_pct = 37
old pages list占整个列表的37%(3/8)

transaction_isolation = REPEATABLE-READ
事务隔离级别
值有：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE，默认REPEATABLE-READ可重复读

innodb_open_files = 65535
InnoDB可同时打开的.ibd文件个数，最小值10，默认300，建议调整为65535

innodb_log_buffer_size = 32M
日志缓冲大小，取值范围16MB~64MB
通过show global status like "%Innodb_log_waits%";观察，若值大于0且继续增长，就可以增大buffer的大小
innodb_log_file_size = 2G
redo log日志大小，不宜过大也不宜过小，太大会导致实例恢复的时间较长，太小则会造成redo log切换频繁，产生无用的IO消耗
innodb_log_files_in_group =2
redo log文件组中日志文件的数量，默认至少有2个

max_connections = 512
数据库最大连接数，默认151
当连接数不够时"too many connections"的解决方法：
1）适当增大max_connections的值，但调整时要考虑数据库的性能能否承担这么多连接带来的压力，值过大会出现实例宕机的情况，可能的话要提前做压测
2）调整InnoDB内部并发数，innodb_thread_concurrency，从5.6起默认为0，不限制
建议调整为服务器逻辑CPU核数的两倍，可以先改成16，再根据业务调整
3）数据库随着连接数的增加性能会下降，可以考虑和开发配合设置thread pool，让连接复用
4）有的监控程序会读infomation_schema下的表，可以考虑关闭下面的参数
innodb_stats_on_metadata = 0
set global innodb_stats_on_metadata = 0
5）考虑是不是有死锁发生

expire_logs_days = 7
binlog过期时间，单位天

slow_query_log = 1
慢查询日志开关，生产环境需要开启

long_query_time = 0.5
慢查询界定时间，单位s

log_queries_not_using_indexes
若运行的SQL没有使用索引，将该条查询记录到慢查询日志中
生产环境建立开启 set global log_queries_not_using_indexes = on;

server-id = 3306100
主从环境中MySQL服务器唯一标识，不能重复

binlog_format = row
二进制日志格式，有statement、row、mixed，生产环境中使用row这种格式更安全，不会出现跨库复制丢数据的情况

lower_case_table_names = 0
表名是否区分大小写，默认为0，表示区分大小写；1代表不区分大小写

innodb_fast_shutdown = 1
影响InnoDB表关闭时的行 为，值有0，1，2，默认1
0，关闭时需执行purge all、merge change buffer、flush dirty pages，最慢，但restart时最快
1，关闭时不执行purge all、merge insert buffer等，只执行flush dirty page
2，只将日志写入日志文件，虽不会丢失数据，但下次启动时会进行recovery操作

innodb_force_recovery = 0
影响InnoDB存储引擎恢复时的行为，值有0、1、2、3、4、5、6，默认0
0，当需要恢复时执行所有恢复操作
1，忽略检查到的corrupt页
2，阻止主线程运行，如主线程要执行full purge操作，会导致crash
3，不执行事务回滚操作
4，不执行插入缓冲合并操作
5，不查看撤销日志，将未提交的事务视为已提交
6，不执行前滚操作

innodb_status_output = OFF
innodb_status_output_locks = OFF
默认关闭，建议关闭，否则会把对数据库监控的信息全部记录到error log中，使错误日志增长过快

innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
InnoDB后台进程最大IO性能指标，影响刷新脏页和插入缓冲的数量，默认是200，在高转速磁盘下，可适当提高

auto_increment_increment = 1
自增字段每次递增量，默认1
auto_increment_offset = 1
自增字段从哪个值开始，默认1

1.4.2. 参数类型 ¶

动态参数和静态参数

1、动态参数
可动态修改，通过set global或者set session命令；global代表全局参数，退出当前会话后依然有效，但重启后会失效；session只针对当前会话生效，一但退出立即失效

2、静态参数
无法在线修改，只能写到配置文件中，重启数据库。
set global lower_case_table_names = 1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
部分参数设置时会显示成功，但并没有更新(如explicit_defaults_for_timestamp)，更新完需要查看一下，以确认配置生效

1.4.3. 错误日志文件error log ¶

log-error = /data/mysql/error.log 记录MySQL启动、运行、关闭过程中出现的问题

5.7初始化中，加上–initialize参数，会生成临时数据库初始密码

explicit_defaults_for_timestamp = 1
timestamp列未显式指定not null，则可以插入null值，非current timestamp
timestamp列指定not null，且无默认值，则插入时若未指定值，且sql_mode=strict，会直接报错explicit_defaults_for_timestamp = 0
timestamp未显式指明null，则会自动加上not null属性，插入null值时，自动设置值为current timestamp
第一个timestamp列，若未指定null或默认值，也未指定on update，则自动加上default current_timestamp和on update current_timestamp属性
其它timestamp列，若未指定null或默认值，则自动加上default '0000-00-00 00:00:00'，且没有warning演示：
explicit_defaults_for_timestamp = 0时：
sql_mode严格模式
create table zs(a timestamp,b timestamp,c timestamp);
ERROR 1067 (42000): Invalid default value for 'b'
create table zs(a timestamp);
show create table zs\G
Table: zs
Create Table: CREATE TABLE zs (
a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
insert into zs (a) values (null);
2018-06-23 01:24:39 会自动变成当前系统时间explicit_defaults_for_timestamp = 1时：
create table zs(a timestamp,b timestamp,c timestamp);
show create table zs\G
Table: zs
Create Table: CREATE TABLE zs (
a timestamp NULL DEFAULT NULL,
b timestamp NULL DEFAULT NULL,
c timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
insert into zs (a,b,c) values (null,null,null);
insert into zs (a) values (null);create table zs (a timestamp,b timestamp,c timestamp not null);
show create table zs\G
Table: zs
Create Table: CREATE TABLE zs (
a timestamp NULL DEFAULT NULL,
b timestamp NULL DEFAULT NULL,
c timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
insert into zs (a) values (null);
ERROR 1364 (HY000): Field 'c' doesn't have a default value

1.4.4. 二进制日志文件binary log ¶

二进制日志的作用：完成主从复制进行恢复操作，使用mysqlbinlog命令，实现基于时间点和位置的恢复操作

binlog_format = row
log-bin = /data/mysql/mysql-binlog

查看当前binlog文件及值的大小

show binary logs;

查看当前日志及状态

show master status\G
文件目录中mysql-bin.index是二进制日志的索引文件，记录产生的二进制日志的序号

相关配置参数：
max_binlog_size = 1G
单个binlog的最大值，超过该值时自动生成新的文件，重启MySQL实例也会生成新的binlog，默认为1GB
生产环境中，控制binlog生成时间最小间隔保持在2~5分钟，所以可以设置为256MB

binlog_cache_size = 4M
max_binlog_cache_size = 1G
生产环境建议设置为1~4MB
将未提交的事务记录到缓存中，等待事务提交后，将缓存写入二进制日志文件，默认为32KB，且binlog_cache_size是基于会话的，每个线程都会起1个
注意，该值太小会使用磁盘上昨时文件来记录，可通过show global status;中binlog_cache_use和binlog_cache_disk_use来查看当前使用情况
show global status like "%binlog_cache%";

binlog_format = row
日志格式，有statement、row、mixed三种
statement，基于操作SQL语句，简称SBR，不建议在生产中使用
缺点：在某些情况下会导致master-slave数据不一致
row，基于行的变更情况，会记录变更前及变更后的样子，简称RBR，生产环境中推荐这种格式
5.6后，新增binlog_rows_query_log_events参数，可以在row模式下看见用户完整SQL语句
缺点：会产生大量日志
mixed，混合模式，不建议使用
实验：
create table t (id int(11) not null auto_increment,
-> name varchar(20) default null,
-> city varchar(10) default null,
-> primary key (id)
-> ) engine=innodb auto_increment=1 default charset=utf8;
insert into t (name,city) values ('zs','bj'),('zz','sh'),('tzy','gz');
update t set name='zz' where name='tzy';
set global binlog_format=statement; 无效，需要修改配置文件，重启

./mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-binlog.000018 > bin.log
#-v 看到具体执行信息
#--base64-output把二进制日志文件转码

sync_binlog = 1
影响binlog刷新
expire_logs_days = 7
二进制日志过期时间，单位天

binlog-do-db
binlog-ignore-db
需要写入或忽略的库，默认为空

log_slave_updates = 1
搭建 m -> s1 -> s2 这样的架构时，需要在s1上开启该参数，才能实现 s1 -> s2 的同步

binlog_checksum = crc32
写入binlog进行校验，值有none，crc32，默认为crc32

log_bin_use_v1_row_events = OFF
binlog版本信息，5.6.6以上默认使用版本2，版本1就是off状态

binlog_row_image = full
记录完整性，值有full、minimal、noblob
full，全部记录，默认
minimal，只记录要修改列的记录
noblob，记录除了blog和text的所有字段

1.4.5. 慢查询日志slow log ¶

超过long_query_time时间的所有SQL语句记录。默认是开启慢查询日志的，long_query_time默认是10s 生产环境中慢查询时间设置不用太小，0.1~0.5之间即可

slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
long_query_time = 0.5

查看慢查询日志
mysqldumpshow
percona-toolkit （pt-query-digest)
下载：
https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz
https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm
https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/6/x86_64/percona-toolkit-3.0.10-1.el6.x86_64.rpm
依赖 :
yum install perl-DBI perl-DBD-mysql perl-Digest-MD5
yum install perl-IO-Socket-IP perl-IO-Socket-SSL perl-Mozilla-CA perl-Net-LibIDN perl-Net-SSLeay
参数：
--create-review-table，当使用--review参数将分析结果输出到表中时，若没有表则自动创建
--create-history-table，当使用--history参数把分析结果输出到表中时，若没有表则自动创建
--filter，对输入慢查询按指定字符串进行匹配过滤后再进行分析
--limit，限制输出结果百分比或数量，默认值20，即将最慢的20条语句输出，若为50%，则按总响应时间占比大小排序，输出总和达到50%时止
--host，--user，--password，数据库连接参数
--history，将分析结果保存到表中，分析结果比较详细
--review，将分析结果保存到表中，分析结果比较简单
--output，分析结果输出类型，值有report标准分析报告、slowlog、json、json-anon，一般使用report，方便阅读
--since，从什么时间开始，值为字符串，如yyyy-mm-dd [hh:mm:ss]格式的时间点或时间值：s、h、m、d、
--until，截止时间
./pt-query-digest --sinece=24h /data/mysql/slow.log > report.log
报告解读：
第一部分，总体统计后的结果
Overall，查询条数；Time range，时间段；unique，唯一查询数量，对查询条件参数化后，有多少个不同的查询
95%，所有值从小到大排序，位于95%的那个值；median，中位数，50%那个值
第二部分，SQL语句占比
Response，总响应时间；time，时间占比；calls，执行次数；R/Call，平均每次执行的响应时间；Item，查询对象
第三部分，SQL语句详细输出结果
Query_time distribution，查询时间分布

1.4.6. 全量日志general log ¶

记录数据库所有操作的SQL语句，包含select和show，默认关闭，因为日志量非常庞大，通常临时开启，用于故障检测

general_log = OFF
#set global general_log = 1;
general_log_file = /data/mysql/localhost.log
log_output = FILE
该参数不仅影响general存储方式，还影响slow log存储方式，建议使用FILE
全局动态变量，可取FILE、TABLE、NONE
若设置为NONE，即使general_log开启也不会记录log
若设置为TABLE，会创建general_log表

1.4.7. 审计日志audit log ¶

数据库审计，简称DBAudit，可以实时记录网络上的数据库活动，对数据库操作进行细粒度审计，对遭受的风险进行告警，对攻击行为进行阻断

MySQL的官方收费组件需要购买企业版才可以使用，这里可以第三方开源审计插件libaudit_plugin.so

最多会降低30%的MySQL性能

libaudit_plugin.so
虽然日志信息比较大，对性能影响大，但是如果想要开启审计，那也应该忍受了
网址：https://bintray.com/mcafee/mysql-audit-plugin/release
网址：https://github.com/mcafee/mysql-audit/
下载：https://dl.bintray.com/mcafee/mysql-audit-plugin/
文件：https://dl.bintray.com/mcafee/mysql-audit-plugin/audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
文件：https://dl.bintray.com/mcafee/mysql-audit-plugin/audit-plugin-mysql-5.7-1.1.6-784-linux-i686.zip
文档：https://github.com/mcafee/mysql-audit/wiki
安装：
unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
cd audit-plugin-mysql-5.7-1.1.6-784/lib
cp libaudit_plugin.so /usr/local/mysql/lib/plugin/
chmod +x /usr/local/mysql/lib/plugin/libaudit_plugin.so
chown mysql:mysql /usr/local/mysql/lib/plugin/libaudit_plugin.so
方式一：配置文件加载
plugin-load=AUDIT=libaudit_plugin.so  ##这个参数是严禁在线卸载
audit_json_file=on
audit_force_record_logins=on
audit_json_file_sync=1
方式二：命令
install plugin AUDIT soname 'libaudit_plugin.so';
卸载：
#配置文件中添加audit_uninstall_plugin = 1
#重启数据库
#uninstall plugin audit; ##执行两次
#配置文件中删除相关配置项

show plugins;
#查看插件功能是否开启
show variables like "%audit%"\G
show variables like "%audit_json_file%";
#开启插件
前提 audit_uninstall_plugin = 1

set global audit_json_file=1;
此时，在数据库目录下，会生成mysql-audit.json的审计日志文件
json中记录了操作SQL语句的用户名、IP地址、SQL等详细内容

错误解决：
ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.
加载时初始化出现了问题，可能是数据不一致导致的，用offest-extract.sh解决
yum install gdb
bash offset-extract.sh /usr/local/mysql/bin/mysqld
//offsets for: /usr/local/mysql/bin/mysqld (5.7.22)
{"5.7.22","0f4d62bab38efe84e18b72800c21fe7f", 7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080, 13464, 148, 672},
在配置文件my.cnf的[mysqld]中加入
audit_offsets=7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080, 13464, 148, 672
注意：修改my.cnf后，并不需要重启MySQL

常用参数：
audit_json_file
#audit功能的开关
audit_json_log_file
#audit的记录文件名
audit_record_cmds
#audit记录的命令，缺省是空，即记录所有命令，也可以根据实际情况修改，如记录select和insert的语句：audit_record_cmds=select,insert
audit_record_objs
#audit记录操作的对象，缺省也是空，记录所有的对象，在这里的对象是指MySQL的库，如audit_record_objs=mysql.*,information_schema.*
audit_whitelist_users
#白名单，指哪些用户的SQL语句不做记录

#日志示例
{"msg-type":"activity","date":"1529922677494","thread-id":"6","query-id":"34","user":"root","priv_user":"root","ip":"192.168.158.1","host":"192.168.158.1","rows":"1","status":"0","cmd":"update","objects":[{"db":"test","name":"aa","obj_type":"TABLE"}],"query":"UPDATE aa SET s='pp7' WHERE (s='pp') LIMIT 1"}

通过init-connect方式

1）创建审计用的库表
create database db_monitor ;
use db_monitor ;
DROP TABLE IF EXISTS accesslog;
CREATE TABLE accesslog (
thread_id int(11) DEFAULT NULL COMMENT '进程id',
log_time datetime DEFAULT NULL COMMENT '登录时间',
localname varchar(50) DEFAULT NULL COMMENT '登录名称带详细ip',
matchname varchar(50) DEFAULT NULL COMMENT '登录用户',
KEY idx_log_time (log_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='审计表';
2）配置init-connect参数
set global init_connect='insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user())';
flush privileges;
3）授予普通用户对accesslog表的insert权限
create user demon@'%';
grant insert on db_monitor.accesslog to demon@'%';
4）普通用户对数据库进行操作
5）进入高级权限用户，查看表记录，配置binlog日志查找用户执行的命令
仅记录用户登录时间和线程ID，信息非常少

1.4.8. 中继日志relay log ¶

主从复制中，从服务器IO线程将主服务器二进制日志拉到本地relay log中，然后从服务器SQL线程读取relay-log日志内容并应用到从服务器

1.4.9. Pid文件 ¶

MySQL是单进程多线程模型数据库，实例启动后，会将自己唯一进程号记录到自己PId文件中，位于数据目录，命名规则是主机名作为前缀 localhost.localdomain.pid

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/error.log --open-files-limit=65535 --pid-file=localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

1.4.10. socket文件 ¶

MySQL数据库有两种连接方式，网络连接和本地连接

socket文件默认位置是/tmp/mysql.sock

1.4.11. 表结构文件 ¶

.frm结尾的文件为表结构文件

8.0开始，frm的定义被取消，把文件中的数据都写到了系统表空间，利用InnoDB存储引擎实现表DDL语句操作的原子性(之前版本无法实现表DDL语句操作原子性，如truncate无法回滚)

1.4.12. innodb存储引擎文件 ¶

redo日志和undo日志

InnoDB支持事务，支持MVCC多版本并发控制。多版本是通过使用undo和回滚段来实现的 InnoDB是索引组织表，每行记录都实现了三个隐藏字段：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR DB_TRX_ID代表每行记录的事务ID；DB_ROLL_PTR代表每行记录的回滚指针

redo log记录事务操作变化，记录数据被修改之后的值

undo日志
对记录做变更操作时，不仅产生redo记录，也产生undo记录(insert、update、delete)
undo只记录变更前的旧数据，undo记录默认记录到系统表空间ibdata1
从5.6起，可以使用独立的undo表空间，采用独立undo空间不用担心undo把ibdata1文件弄大，也给部署不同IO类型的文件位置带来便利(对于并发写入型负载，把undo文件部署到单独的高速存储设备上)参数说明
innodb_undo_directory = ./
undo文件存储目录，默认./innodb_undo_logs = 128
undo回滚段数量，默认128
可以将一个大的回滚段拆分成多个小的回滚段，每个undo log segments最多存放1024个事务innodb_undo_tablespaces = 0
undo tablespace个数，表空间中有undo log文件，默认10MB，表空间个数默认值为0
undo tablespaces数量最少2个，因为undo log的truncate操作由purge协调线程发起，在truncate某个undo log表空间时，保证有一个可用的undo log tablespaces提供给用户使用，实现所谓的在线truncateinnodb_max_undo_log_size = 1GB
5.7新增，控制最大undo tablespace文件大小，超过阈值，会触发truncate undo logs，truncate后undo logs大小默认恢复为10MBinnodb_undo_log_truncate = OFF
5.7.5后，在线删除无用的undo logs，默认关闭innodb_purge_rseg_truncate_frequency = 128
5.7新增，控制回收undo log的频率，默认128
purge undo轮询128次后，进行一次undo的truncate操作