本文档从MySQL数据库和存储引擎层面介绍各种类型的文件。
在MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数。在默认情况下，MySQL实例会按照一定的顺序在指定的位置进行读取。
#mysql--help|grepmy.cnf.../etc/my.cnf/etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf~/.my.cnf
如果想指定默认的参数文件，需要配合--defaults-file选项，如：
mysqld--defaults-file=/etc/my3306.cnf&
下面介绍一下常见参数文件/etc/my.cnf中的参数的含义。
最好是添加适当的索引而不是纯粹加大join_buffer_size任何两个表间的全表join就会分配一次join_buffer也就是说，如果3个表join就会分配2次joinbuffer（而不是一个session只分配一次）
在SSD存储上应设置为0(禁用),因为使用顺序IO没有任何性能收益.在使用RAID的某些硬件上也应该禁用此设置,因为逻辑上连续的块在物理磁盘上并不能保证也是连续的.
错误日志文件对MySQL的启动、运行、关闭过程进行了记录。
下面示例是一个根据错误日志来处理步骤。
示例准备
1).在MySQL数据目录手动创建mydir目录，并更改为属主为mysql#cd/data/mysql/mysql3306/data#mkdirmydir#chownmysql:mysql
2).登陆mysql，使用命令showdatabases可以看到mydir。mysql>showdatabases;+--------------------+|Database|+--------------------+...|mydir|+--------------------+13rowsinset(0.00sec)
3).在mydir数据库中，创建表t1mysql>usemydirmysql>createtablet1(c1int);
4).删除datadir中的mydir目录#cd/data/mysql/mysql3306/data#rm-rfmydir
5).重启MySQL根据错误日志，来处理数据库错误
2).错误日志中提示找不到/mydir/t1.ibd表，mydir目录不存在了。mysql>SELECT*FROMINFORMATION_SCHEMA.INNODB_SYS_TABLESWHERENAMELIKE'%t1%';+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+|TABLE_ID|NAME|FLAG|N_COLS|SPACE|FILE_FORMAT|ROW_FORMAT|ZIP_PAGE_SIZE|SPACE_TYPE|+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+|196|mydir/t1|33|4|326|Barracuda|Dynamic|0|Single||155|sbtest/sbtest1|33|7|280|Barracuda|Dynamic|0|Single||160|sbtest/sbtest10|33|7|285|Barracuda|Dynamic|0|Single||120|test/t11|33|6|229|Barracuda|Dynamic|0|Single||121|test/t12|33|5|230|Barracuda|Dynamic|0|Single|+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+5rowsinset(0.00sec)
3).查看mydir的表结构mysql>SELECTa.NAME,->b.nameAScol_name,->CASE->WHENb.MTYPE=1THEN'VARCHAR'->WHENb.MTYPE=2THEN'CHAR'->WHENb.MTYPE=3THEN'FIXBINARY'->WHENb.MTYPE=4THEN'BINARY'->WHENb.MTYPE=5THEN'BLOB'->WHENb.MTYPE=6THEN'int'->WHENb.MTYPE=7THEN'SYS_CHILD'->WHENb.MTYPE=8THEN'SYS'->WHENb.MTYPE=9THEN'FLOAT'->WHENb.MTYPE=10THEN'DOUBLE'->WHENb.MTYPE=11THEN'DECIMAL'->WHENb.MTYPE=12THEN'VARMYSQL'->WHENb.MTYPE=13THEN'MYSQL'->WHENb.MTYPE=14THEN'GEOMETRY'->ELSEMTYPE->END->ASType->FROMINNODB_SYS_TABLESa->JOININNODB_SYS_COLUMNSbONa.TABLE_ID=b.TABLE_ID->WHEREa.NAME='mydir/t1';+----------+----------+------+|NAME|col_name|Type|+----------+----------+------+|mydir/t1|c1|int|+----------+----------+------+1rowinset(0.01sec)
4).在test库中，创建同样表结构的t1表。mysql>createtablet1(c1int);
5).在datadir目录创建mydir目录，并且把test目录中的t1.frm复制到mydir目录中#mkdir/data/mysql/mysql3306/data/mydir#cp/data/mysql/mysql3306/data/test/t1.frm/data/mysql/mysql3306/data/mydir#chown-Rmysql:mysql/data/mysql/mysql3306/data/mydir
6).删除mydir数据库mysql>dropdatabasemydir;
7).重新查询mysql>SELECTa.NAME,->b.nameAScol_name,->CASE->WHENb.MTYPE=1THEN'VARCHAR'->WHENb.MTYPE=2THEN'CHAR'->WHENb.MTYPE=3THEN'FIXBINARY'->WHENb.MTYPE=4THEN'BINARY'->WHENb.MTYPE=5THEN'BLOB'->WHENb.MTYPE=6THEN'int'->WHENb.MTYPE=7THEN'SYS_CHILD'->WHENb.MTYPE=8THEN'SYS'->WHENb.MTYPE=9THEN'FLOAT'->WHENb.MTYPE=10THEN'DOUBLE'->WHENb.MTYPE=11THEN'DECIMAL'->WHENb.MTYPE=12THEN'VARMYSQL'->WHENb.MTYPE=13THEN'MYSQL'->WHENb.MTYPE=14THEN'GEOMETRY'->ELSEMTYPE->END->ASType->FROMINNODB_SYS_TABLESa->JOININNODB_SYS_COLUMNSbONa.TABLE_ID=b.TABLE_ID->WHEREa.NAME='mydir/t1';Emptyset(0.01sec)
二进制日志的主要作用：
mysql>select@@gtid_mode;+-------------+|@@gtid_mode|+-------------+|ON|+-------------+1rowinset(0.00sec)mysql>usetest;Databasechangedmysql>createtablepitr(namevarchar(30),birthdaytimestamp);QueryOK,0rowsaffected(0.07sec)mysql>insertintopitrvalues('张三',sysdate());QueryOK,1rowaffected(0.02sec)mysql>insertintopitrvalues('李四',sysdate());QueryOK,1rowaffected(0.02sec)mysql>select*frompitr;+--------+---------------------+|name|birthday|+--------+---------------------+|张三|2018-08-2713:00:05||李四|2018-08-2713:00:14|+--------+---------------------+2rowsinset(0.00sec)
误删除数据'李四',并插入数据'王五'，删除表pitr
mysql>deletefrompitrwherename='李四';QueryOK,1rowaffected(0.01sec)mysql>insertintopitrvalues('王五',sysdate());QueryOK,1rowaffected(0.01sec)mysql>select*frompitr;+--------+---------------------+|name|birthday|+--------+---------------------+|张三|2018-08-2713:00:05||王五|2018-08-2713:00:55|+--------+---------------------+2rowsinset(0.00sec)
恢复pitr表中三条数据。
#mysqlbinlog--no-defaults--stop-position='1072'/data/mysql/mysql3306/logs/my3306_binlog.000002|mysql-S/tmp/mysql3306.sock#mysqlbinlog--no-defaults--start-position='1398'--stop-position='1739'/data/mysql/mysql3306/logs/my3306_binlog.000002|mysql-S/tmp/mysql3306.sockmysql>usetest;Databasechangedmysql>select*frompitr;ERROR1146(42S02):Table'test.pitr'doesn'texist
并没有恢复。。。是不是缺了什么步骤，理下思路，是不是binlog里的gtid小于当前的gtid，然后恢复的时候就跳过了。为了确认自己想法的正确性，动手操作吧。1).备份当前binlog#cpmy3306_binlog.000002../2).重置gtidmysql>resetmaster;QueryOK,0rowsaffected(0.05sec)3).重新恢复#mysqlbinlog--no-defaults--stop-position='1072'/data/mysql/mysql3306/my3306_binlog.000002|mysql-S/tmp/mysql3306.sock#mysqlbinlog--no-defaults--start-position='1398'--stop-position='1739'/data/mysql/mysql3306/my3306_binlog.000002|mysql-S/tmp/mysql3306.sock4).重新查询mysql>select*frompitr;+--------+---------------------+|name|birthday|+--------+---------------------+|张三|2018-08-2713:00:05||李四|2018-08-2713:00:14||王五|2018-08-2713:00:55|+--------+---------------------+3rowsinset(0.00sec)恢复成功
下面尝试变更gtid模式能不能恢复
不要在输出中显示任何GTID。从一个或多个包含GTID的二进制日志写入转储文件时需要这样做，如下例所示：
#mysqlbinlog--no-defaults--skip-gtids--stop-position='1106'/data/mysql/mysql3306/logs/my3306_binlog.000019|mysql-S/tmp/mysql3306.sock也能恢复数据。
使用mysqlbinlog命令，实现基于位置的恢复操作
通过mysqldumpslow工具可以查看慢查询日志，如：
#mysqldumpslow-sal-n10/data/mysql/mysql3306/slow.logReadingmysqlslowquerylogfrom/data/mysql/mysql3306/slow.logCount:1Time=0.18s(0s)Lock=0.41s(0s)Rows=1.0(1),root[root]@localhostselect*fromschema_auto_increment_columnsCount:1Time=0.00s(0s)Lock=0.09s(0s)Rows=8.0(8),root[root]@localhostSELECTSUBSTRING_INDEX(event_name,'S',N)AScode_area,sys.format_bytes(SUM(current_alloc))AScurrent_allocFROMsys.x$memory_global_by_current_bytesGROUPBYSUBSTRING_INDEX(event_name,'S',N)ORDERBYSUM(current_alloc)DESC...
下面介绍利用percona-toolkit工具，查看慢查询日志。
1）解压percona-toolkit工具包
#tar-zxvfpercona-toolkit-3.0.11_x86_64.tar.gz
2）添加至环境变量中
#echo"exportPATH=$PATH:/opt/percona-toolkit-3.0.11/bin">>/etc/profile#source/etc/profile
3）分析slowlog
#pt-query-digest/data/mysql/mysql3306/slow.logCan'tlocateData/Dumper.pmin@INC(@INCcontains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.)at/opt/percona-toolkit-3.0.11/bin/pt-query-digestline75.BEGINfailed--compilationabortedat/opt/percona-toolkit-3.0.11/bin/pt-query-digestline75.
又提示缺包。。#yum-yinstallperl-Digest-MD5
generallog会记录MySQL数据库所有操作的SQL语句，包含select和show。默认情况下，禁用常规查询日志。
数据库审计能够实时记录网络上的数据库活动，对数据库操作进行细粒度审计的合规性管理，对数据库遭受到的风险行为进行告警，对攻击行为进行阻断。
它通过对用户访问数据库行为的记录、分析和汇报，用来帮助用户事后生成合规报告，事故追根溯源，同时加强内外部数据库网络行为记录，提高数据资产安全。
MySQL数据库官方的收费组件需要购买企业版才可以使用审计功能。下面利用第三方开源审计插件完成审计工作。
2).解压#unzipdownload_file\file_path\=audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
3).复制插件至MySQLlib库目录下#cdaudit-plugin-mysql-5.7-1.1.6-784/#lsCOPYINGlibplugin-name.txtREADME.txtTHIRDPARTY.txtutils#cdlib/#lslibaudit_plugin.so#cplibaudit_plugin.so/usr/local/mysql/lib/plugin/
4).安装插件mysql>installpluginauditsoname'libaudit_plugin.so';ERROR29(HY000):File'mysqld'notfound(Errcode:2-Nosuchfileordirectory)
5).查看错误日志#tail-100ferror.log2018-08-27T12:53:17.364957Z8[Note]McAfeeAuditPlugin:setup_offsetsaudit_offsets:(null)validate_checksum:1offsets_by_version:12018-08-27T12:53:17.364977Z8[ERROR]McAfeeAuditPlugin:Failedfileopen:[mysqld],errno:2.Retryingwith/proc/2193/exe.2018-08-27T12:53:20.765251Z8[Note]McAfeeAuditPlugin:mysqld:mysqld(630d78960dfa79b5da11bfbec180899a)2018-08-27T12:53:20.765280Z8[Note]McAfeeAuditPlugin:Couldn'tfindproperTHDoffsetsfor:5.7.23-log2018-08-27T12:53:20.765284Z8[ERROR]Plugin'AUDIT'initfunctionreturnederror.2018-08-27T12:53:20.765287Z8[ERROR]Plugin'AUDIT'registrationasaAUDITfailed.
6).设置偏移量#cdaudit-plugin-mysql-5.7-1.1.6-784/utils#shoffset-extract.sh/usr/local/mysql/bin/mysqld//offsetsfor:/usr/local/mysql/bin/mysqld(5.7.23){"5.7.23","630d78960dfa79b5da11bfbec180899a",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},编辑my3306.cnf文件，添加以下内容。重启mysql#vi/etc/my3306.cnfplugin-load=AUDIT=libaudit_plugin.soaudit_json_file=ONaudit_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,672audit_json_log_file=/data/mysql/mysql3306/mysql3306_audit.log
7).插件已成功安装root@localhost[(none)]21:32:22>showplugins;+----------------------------+----------+--------------------+--------------------+---------+|Name|Status|Type|Library|License|+----------------------------+----------+--------------------+--------------------+---------+...|AUDIT|ACTIVE|AUDIT|libaudit_plugin.so|GPL|+----------------------------+----------+--------------------+--------------------+---------+
需要关心的参数7.中继日志主从复制中，slave服务器上一个很重要的文件。
复制的工作原理分为以下3个步骤：
当MySQL实例启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。该文件由参数pid_file控制，默认位于数据库目录下，文件名为主机名.pid
mysql>showvariableslike'%pid_file%';+---------------+--------------+|Variable_name|Value|+---------------+--------------+|pid_file|mysqldb1.pid|+---------------+--------------+1rowinset(0.00sec)
在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字（socket）文件。
由参数socket控制：
mysql>showvariableslike'socket';+---------------+---------------------+|Variable_name|Value|+---------------+---------------------+|socket|/tmp/mysql3306.sock|+---------------+---------------------+1rowinset(0.00sec)
MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。
frm可以存放视图的定义，存放视图定义的frm文件是文本文件，可以直接用cat查看，例如：
#catGLOBAL_STATUS.frmTYPE=VIEWquery=select`information_schema`.`GLOBAL_STATUS`.`VARIABLE_NAME`AS`VARIABLE_NAME`,`information_schema`.`GLOBAL_STATUS`.`VARIABLE_VALUE`AS`VARIABLE_VALUE`from`INFORMATION_SCHEMA`.`GLOBAL_STATUS`md5=dac896d268861732d0c40425f1e66cc6updatable=0algorithm=0definer_user=rootdefiner_host=localhostsuid=0with_check_option=0timestamp=2018-08-2006:29:43create-version=1source=SELECT*FROM`INFORMATION_SCHEMA`.`GLOBAL_STATUS`client_cs_name=utf8connection_cl_name=utf8_general_ciview_body_utf8=select`information_schema`.`GLOBAL_STATUS`.`VARIABLE_NAME`AS`VARIABLE_NAME`,`information_schema`.`GLOBAL_STATUS`.`VARIABLE_VALUE`AS`VARIABLE_VALUE`from`INFORMATION_SCHEMA`.`GLOBAL_STATUS`