数据库的数据存储文件

存储文件分类

MySQL数据库会在data目录下面建立一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不同
的数据库引擎，每个表的扩展名也不一样 ，例如： MyISAM用”.MYD”作为扩展名，Innodb用”.ibd”，Archive 用”.arc”，CSV 用”.csv”。

  • “.FRM”文件

    无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的’.frm’文件。frm文件主要存放与表相关的数据信息，主要包括表结构的定义信 息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。

  • “.MYD”文件

    “.MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个”.MYD”文件与之对应，同样存放于所属数据库的文件夹 下， 和”.frm”文件在一起。

  • “.MYI”文件

    “.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说，可以被cache 的内容主要就是来源 于“.MYI”文件中。 每一个MyISAM表对应一个”.MYI”文件，存放于位置和”.frm”以及”.MYD”一样。

  • “.ibd”文件与”.ibdata”文件

    这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决 定是使用共享 表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件 ，文件存放在和MyISAM数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个， 可自行配置）ibdata文件。 ibdata文件可以通过innodb_data_home_dir（数据存放目录）和innodb_data_file_path(配置每个文件的名称)两个参数配置组成 innodb_data_file_path中可以一次配置多个ibdata文件 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方式，共享表空间以及独占表空间都是针对数据的存储方式而言的。 共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。 独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有 一个.ibd文件。其中这个文件包括了 单独一个表的数据 内容以及索引内容。

共享表与独占表的对比

共享表空间

优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间。

独立表空间

优点:

  1. 每个表都有自已独立的表空间。
  2. 每个表的数据和索引都会存在自已的表空间中。
  3. 可以实现单表在不同的数据库中移动。
  4. 空间可以回收
    1. Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回 缩不用的空间。
    2. 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：
单表增加过大，如超过100 个G。 相比较之下，使用独占表空间的效率以及性能会更高一点 

  1. show variables like "innodb_file_per_table"; 
  2. ON代表独立表空间管理，OFF代表共享表空间管理； 
  4. 修改数据库的表空间管理方式修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经 
  5. 使用过的共享表空间和独立表空间； 
  6. innodb_file_per_table=1 为使用独占表空间 
  7. innodb_file_per_table=0 为使用共享表空间

慢查询日志

功能

记录Mysql中响应时间超过阈值的语句，即运行时间超过long_query_time值得SQL。
long_query_time的默认值为0，默认情况下慢查询日志不开启，开启会有一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

相关参数

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。
log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据 库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。

日志分析工具-mysqldumpslow

参数详情

-s, 是表示按照何种方式排序:
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如：得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /www/server/data/mysql-slow.log 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /www/server/data/mysql-slow.log 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /www/server/data/mysql-slow.log 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况

错误日志与二进制文件

错误日志（Error Log）是 MySQL 中最常用的一种日志，主要记录 MySQL 服务器启动和停止过程中的信
息、服务器在运行过程中发生的故障和异常情况等。

启动错误日志

  1. [mysqld]
  2. log-error=dir/{filename}
  3. log-warnings=dir/{filename}

注意：错误日志中记录的并非全是错误信息，例如Mysql如何启动表空间文件、如何初始化自己的存储引擎等，也记录在错误日志中。

错误日志

  1. show variables like "log_error";

该文件在默认在mysql安装目录下或者是mysql目录data下

二进制日志（bin log）

二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。 

  1. show variables like "log_bin";
  1. [mysqld]
  2. log-bin=dir/[filename]

每次重启mysql都会生成一个新的二进制文件。

相关操作

  1. show binary logs;

image.png

  1. show master status

image.png
二进制日志使用二进制格式存储，不能直接打开查看。如果需要查看二进制日志，使用show binlog events in ‘mysql-bin.000001’; 命令。

  1.  show binlog events in 'mysql-bin.000036'\G;

image.png

  1. RESET MASTER
  1. PURGE MASTER LOGS TO 'filename.number';
  1. PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

数据修复

  1. ./bin/mysqlbinlog --start-position=96625 --stop-position=97758 /www/server/data/mysql- bin.000036 | mysql -uroot -p
  1. mysqlbinlog --start-datetime='2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin.000036 | mysql -uroot -p
  1. mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p

中继日志

从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读
取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致
image.png
参数详情

max_relay_log_size

relay log 允许的最大值，如果该值为0，则默认值为 max_binlog_size (1G)，如果不为0，则 max_relay_log_size 则为最大的relay_log文件大小；

relay_log

定义 relay_log 的位置和名称，如果值为空，则默认位置在数据文件的目录；

relay_log_index

定义 relay_log 索引的位置和名称，记录有几个 relay_log 文件，默认为2个

relay_log_info_file

定义 relay-log.info 的位置和名称
relay-log.info 记录 master 主库的 binary_log 的恢复位置和 从库 relay_log 的位置；

relay_log_purge

是否自动清空中继日志，默认值为1(启用)；

relay_log_recovery

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执
行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该
功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启；

sync_relay_log

当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后
刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造
成磁盘的大量I/O；
当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低
了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改；

sync_relay_log_info

这个参数和 sync_relay_log 参数一样。