MySQL-10mysql物理文件

2021-09-23  本文已影响0人  安晓生

大家好，本篇文章记录数据库日志跟讲解。（个人理解）

数据库日志种类

  1. 数据库的数据存储文件
  2. 慢查询日志
  3. 错误日志与二进制文件
  4. 二进制文件基本操作
  5. 使用二进制文件恢复数据
  6. 中继日志(了解)

1.数据库的数据存储文件

我们都是MySQL数据库是储存在磁盘上的，那么他们都有那些文件呢？

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

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

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

这两种文件都是存放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 配置方式
两者对比

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

2.独立表空间：
优点:

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

2.慢查询日志

简介介绍：

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这 个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

慢查询相关参数

MySQL 慢查询的相关参数解释:

默认情况下slow_query_log的值为OFF
表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启。

查看show variables like "%slow_query_log%";
设置：set global slow_query_log=1;

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如 果要永久生效，就必须修改配置文件my.cnf(其它系统变量也是如此)。

修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。

slow_query_log=1 
slow-query-log-file=/www/server/data/mysql-slow.log 
long_query_time=3

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢? 这个是由参数long_query_time控 制，默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运 行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间， 之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

查看慢日志多少秒
show variables like "long_query_time%";
设置慢日志多少秒
set session long_query_time=10.0;

3.错误日志与二进制文件

3.1 错误日志

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

启动错误日志

默认开启错误日志功能（重点，面试会问）。

//将 log_error 选项加入到 MySQL 配置文件的 [mysqld] 组中
log-error=dir/{filename}
//dir 参数指定错误日志的存储路径;
//filename 参数指定错误日志的文件名;
//省略参数时文件名默认为 主机名，存放在 Data 目录中。
//重启 MySQL 服务后，参数开始生效，可以在指定路径下看到 filename.err 的文件。
//如果没有指定 filename，那么错误日志将直接默认为 hostname.err。

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

查看错误日志

错误日志中记录着开启和关闭 MySQL 服务的时间，以及服务运行过程中出现哪些异常等信息。如果 MySQL 服务出现异常，可以到错误日志中查找原因。

通过 SHOW 命令可以查看错误日志文件所在的目录及文件名信息。
show variables like "log_error";
删除错误日志
mysqladmin -uroot -p flush-logs

建议：

MySQL 服务器发生异常时，管理员可以在错误日志中找到发生异常的时间、原因，然后根据这些信息来解决异常。对于很久之前的错误日志，查看的可能性不大，可以直接将这些错误日志删除。

3.2 二进制日志

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

//二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启
show variables like "log_bin";
+---------------+-------+
| Variable_name | Value | 
+---------------+-------+
| log_bin             | ON     | 
+---------------+-------+
1 row in set (0.02 sec)
//从结果可以看出，二进制日志是开启的。

log-bin=dir/[filename]
dir 参数指定二进制文件的存储路径;
filename 参数指定二进制文件的文件名：
其形式为 filename.number，number 的形式为 000001、000002 等。
每次重启会生成一个文件6位数字。

注意：
每次重启 MySQL 服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中 filename 部分不会改变，number 会不断递增。

如果没有 dir 和 filename 参数，二进制日志将默认存储在数据库的数据目录下，默认的文件名为 hostname-bin.number，其中 hostname 表示主机名。

案例配置如下:

log-bin=mysql-bin 
binlog_format=mixed 
server-id = 1

在mysql的data目录下查看生成的binlog日志文件mysql-bin.000001

4.二进制文件基本操作

4.1查看二进制文件

命令查看
show binary logs;

show master status;//命令用来查看当前的二进制日志;

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

注意：如果不指定查询的二进制文件，则默认为第一个二进制文件。

4.2 删除二进制文件

二进制日志中记录着大量的信息，如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进制日志的方法很多，下面介绍几种删除二进制日志的方法。

使用 RESET MASTER 语句可以删除的所有二进制日志。
RESET MASTER;

每个二进制日志文件后面有一个 6 位数的编号。如 000001。
语句如下：
PURGE MASTER LOGS TO 'filename.number';
该语句将删除编号小于 filename.number 的所有二进制日志下面删除
mylog.000004 之前的二进制日志，代码如下:
PURGE MASTER LOGS TO 'mylog.000004';

注意：代码执行完后，编号为 000001、000002 和 000003 的二进制日志将被删除。

使用 PURGE MASTER LOGS TO 语句，可以删除指定时间之前创建的二进制日志，该语句的基本语法格式如 下:

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

其中，“hh”为 24 制的小时。该语句将删除在指定时间之前创建的所有二进制日志。 下面删除 2019-12-20 15:00:00 之前创建的二进制日志，代码如下:

PURGE MASTER LOGS TO '2019-12-20 15:00:00";

注意：代码执行完后，2019-12-20 15:00:00 之前创建的所有二进制日志将被删除。

5. 使用二进制文件恢复数据

前提准备就是一个数据库，一张表，添加点数据，然后在删除数据，或者表，接下来我们开始进行操作。

根据节点需要我们查看日志文件提供给我们的数据库创建，表创建，数据新增等时创建的语句节点，从而 恢复数据。

./bin/mysqlbinlog --start-position=96625 --stop-position=97758 /www/server/data/mysql- bin.000036 | mysql -uroot -p

  //根据时间恢复数据
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
//直接执行binlog日志
mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p

6 中继日志

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

show variables like '%relay%';

参数详情:

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 参数一样。

先分享到这里，有写地方也不是全面。

上一篇 下一篇

猜你喜欢

热点阅读