mysql物理文件 02

最新推荐文章于 2023-11-06 21:54:07 发布

哒哒-blog

最新推荐文章于 2023-11-06 21:54:07 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44102152/article/details/109381468

版权

mysql 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

mysql物理文件

一、数据库的数据存储文件
二、慢查询日志
三、错误日志与二进制文件
四、二进制文件基本操作
五、使用二进制文件恢复数据
六、中继日志（暂时先了解）
- 中继日志

一、数据库的数据存储文件

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

1 .FRM 文件

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

2 .MYD 文件

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

3 .MYI 文件

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

4 .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）独立表空间：
优点:

每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不同的数据库中移动。
空间可以回收
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回缩不用的空间。
b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。缺点：单表增加过大，如超过100 个G。相比较之下，使用独占表空间的效率以及性能会更高一点

共享表空间和独立表空间之间的转换

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

二、慢查询日志

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

1. 慢查询相关参数

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

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’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

2. 慢查询日志配置

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

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.01 sec)

使用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语句运行时间，之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

mysql> show variables like "long_query_time%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> set session long_query_time=10.0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "long_query_time%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 3.000000  |
+-----------------+-----------+
1 row in set (0.04 sec)

如上所示，修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。你用show variables like 'long_query_time’查看是当前会话的变量值，你也可以不用重新连接会话，而是用show global variables like ‘long_query_time’; 如下所示：

在MySQL里面执行下面SQL语句，然后我们去检查对应的慢查询日志，就会发现类似下面这样的信息。

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

mysql> quit
Bye

[root@localhost ~]# cat /www/server/data/mysql-slow.log
/www/server/mysql/bin/mysqld, Version: 8.0.19 (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2020-09-24T22:46:31.232753Z
# User@Host: root[root] @ localhost []  Id:   442
# Query_time: 10.000493  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1600987581;
select sleep(10);

log_output 参数是指定日志的存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

mysql> show variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select sleep(15) ;
+-----------+
| sleep(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.01 sec)

mysql> mysql> select * from mysql.slow_log\G;
*************************** 1. row ***************************
    start_time: 2020-09-25 06:50:13.094222
     user_host: root[root] @ localhost []
    query_time: 00:00:15.000808
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 1
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: 0x73656C65637420736C65657028313529
     thread_id: 443
1 row in set (0.00 sec)

ERROR:
No query specified

3. 日志分析工具

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息：

[root@localhost bin]# ./mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

参数详解：

-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 使用，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /www/server/data/mysql-slow.log | more

三、错误日志与二进制文件

1. 错误日志

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

1.1 启动错误日志

在 MySQL 数据库中，默认开启错误日志功能。一般情况下，错误日志存储在 MySQL 数据库的数据文件夹下，通常名称为 hostname.err。其中，hostname 表示 MySQL 服务器的主机名。

在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义，其中，log-err 定义是否启用错误日志功能和错误日志的存储位置，log-warnings 定义是否将警告信息也记录到错误日志中。

将 log_error 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：
[mysqld]
log-error=dir/{filename}

其中，dir 参数指定错误日志的存储路径；filename 参数指定错误日志的文件名；省略参数时文件名默认为主机名，存放在 Data 目录中。

重启 MySQL 服务后，参数开始生效，可以在指定路径下看到 filename.err 的文件，如果没有指定 filename，那么错误日志将直接默认为 hostname.err。

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

1.2查看错误日志

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

在 MySQL 中，通过 SHOW 命令可以查看错误日志文件所在的目录及文件名信息。

mysql> show variables like "log_error";
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| log_error     | ./localhost.localdomain.err |
+---------------+-----------------------------+
1 row in set (0.01 sec)

错误日志以文本文件的形式存储，直接使用普通文本工具就可以查看。这里通过记事本打开，从上面可以知道错误日志的文件名。该文件在默认在mysql安装目录下或者是mysql目录data下，打开 localhost.localdomain.err 文件，部分内容如下：

2020-09-23T05:26:20.198963Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2020-09-23T05:26:20.290501Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2020-09-23T05:26:20.461211Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-09-23T05:26:20.527833Z 0 [System] [MY-010931] [Server] /www/server/mysql/bin/mysqld: ready for connections. Version: '8.0.19'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.
2020-09-23T05:26:20.606839Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-09-23T05:59:19.305255Z 8 [Warning] [MY-010055] [Server] IP address '192.168.199.172' could not be resolved: Name or service not known
2020-09-23T05:59:54.822964Z 9 [Warning] [MY-010055] [Server] IP address '192.168.199.186' could not be resolved: Name or service not known
2020-09-23T11:48:13.352488Z 10 [Warning] [MY-010055] [Server] IP address '192.168.199.1' could not be resolved: Name or service not known
2020-09-23T14:19:33.749679Z 93 [Warning] [MY-010055] [Server] IP address '192.168.199.187' could not be resolved: Name or service not known
2020-09-24T00:03:16.041761Z 104 [Warning] [MY-010058] [Server] Hostname 'localhost.localdomain' does not resolve to '192.168.199.188'.

以上是错误日志文件的一部分，主要记载了系统的一些运行错误。

1.3 删除错误日志

在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。mysqladmin 命令的语法如下：

mysqladmin -uroot -p flush-logs

执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old。

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

2. 二进制文件

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

如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启，命令如下：

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)

从结果可以看出，二进制日志是开启的。

启动和设置二进制日志
在 MySQL 中，可以通过在配置文件中添加 log-bin 选项来开启二进制日志，格式如下:

[mysqld]
log-bin=dir/[filename]

其中，dir 参数指定二进制文件的存储路径；filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等。

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

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

案例配置如下：

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

在mysql的data目录下查看生成的binlog日志文件，如下：
在这里插入图片描述

四、二进制文件基本操作

1. 查看二进制文件

1.1 查看二进制日志文件列表

可以使用如下命令查看 MySQL 中有哪些二进制日志文件：

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000035 |    686110 | No        |
| mysql-bin.000036 |     96148 | No        |
+------------------+-----------+-----------+
2 rows in set (0.05 sec)

1.2 查看当前正在写入的二进制日志文件

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000036 |    96148 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

1.3 查看二进制日志文件内容

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

mysql> show binlog events in 'mysql-bin.000036'\G;
*************************** 825. row ***************************
   Log_name: mysql-bin.000036
        Pos: 95772
 Event_type: Query
  Server_id: 1
End_log_pos: 96117
       Info: use `lms_2006_users`; insert into `lms_admin_operation_log` (`user_id`, `path`, `method`, `ip`, `input`, `updated_at`, `created_at`) values (1, 'admin/products', 'GET', '127.0.0.1', '{\"_pjax\":\"#pjax-container\"}', '2020-09-24 09:52:05', '2020-09-24 09:52:05')
*************************** 826. row ***************************
   Log_name: mysql-bin.000036
        Pos: 96117
 Event_type: Xid
  Server_id: 1
End_log_pos: 96148
       Info: COMMIT /* xid=16643 */
826 rows in set (0.01 sec)

ERROR:
No query specified
mysql>

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

2. 删除二进制文件

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

2.1 删除所有二进制日志

使用 RESET MASTER 语句可以删除的所有二进制日志，该语句的形式如下：

RESET MASTER;

2.2 根据编号删除二进制日志

每个二进制日志文件后面有一个 6 位数的编号，如 000001。使用 PURGE MASTER LOGS TO 语句，可以删除指定二进制日志的编号之前的日志。该语句的基本语法形式如下：

PURGE MASTER LOGS TO 'filename.number';

该语句将删除编号小于 filename.number 的所有二进制日志

下面删除 mylog.000004 之前的二进制日志，代码如下：

PURGE MASTER LOGS TO 'mylog.000004';

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

2.3 根据创建时间删除二进制日志

使用 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 之前创建的所有二进制日志将被删除。

五、使用二进制文件恢复数据

环境准备

创建一个用于测试的库create database mytest;

mysql> create database mytest;
Query OK, 1 row affected (0.03 sec)

mysql> use mytest;
Database changed
mysql> show tables;
Empty set (0.02 sec)

创建一张用于测试的用户表并新增数据，如下：
建表语句：

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `user`(username,age)values('starsky',10);
insert into `user`(username,age)values('ShineYork',10);
insert into `user`(username,age)values('Will',10);
mysql> CREATE TABLE `user` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `username` varchar(20) DEFAULT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.09 sec)

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user             |
+------------------+
1 row in set (0.02 sec)

mysql> insert into `user`(username,age)values('starsky',10);
Query OK, 1 row affected (0.02 sec)

mysql> insert into `user`(username,age)values('ShineYork',10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into `user`(username,age)values('Will',10);
Query OK, 1 row affected (0.01 sec)

mysql>

删除数据或者表

mysql> select * from user;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  1 | starsky   |   10 |
|  2 | ShineYork |   10 |
|  3 | Will      |   10 |
+----+-----------+------+
3 rows in set (0.00 sec)

mysql> delete from `user` where id=1;
Query OK, 1 row affected (0.02 sec)

mysql> delete from `user` where id=2;
Query OK, 1 row affected (0.02 sec)

mysql> delete from `user` where id=3;
Query OK, 1 row affected (0.02 sec)

mysql> drop table `user`;
Query OK, 0 rows affected (0.04 sec)

mysql> drop database `mytest`;
Query OK, 0 rows affected (0.06 sec)

恢复数据

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

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

[root@localhost mysql]# ./bin/mysqlbinlog --start-position=96148 --stop-position=97789 /www/server/data/mysql-bin.000036 | mysql -uroot -p
Enter password:
[root@localhost mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 453
Server version: 8.0.19 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mytest;
Database changed
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user             |
+------------------+
1 row in set (0.05 sec)

mysql> select * from `user`;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  1 | starsky   |   10 |
|  2 | ShineYork |   10 |
|  3 | Will      |   10 |
+----+-----------+------+
3 rows in set (0.01 sec)

mysql>

其它恢复数据命令

//根据时间恢复数据
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

六、中继日志（暂时先了解）

中继日志

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

mysql> show variables like '%relay%';
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| max_relay_log_size        | 0                                          |
| relay_log                 | localhost-relay-bin                        |
| relay_log_basename        | /www/server/data/localhost-relay-bin       |
| relay_log_index           | /www/server/data/localhost-relay-bin.index |
| relay_log_info_file       | relay-log.info                             |
| relay_log_info_repository | TABLE                                      |
| relay_log_purge           | ON                                         |
| relay_log_recovery        | OFF                                        |
| relay_log_space_limit     | 0                                          |
| sync_relay_log            | 10000                                      |
| sync_relay_log_info       | 10000                                      |
+---------------------------+--------------------------------------------+
11 rows in set (0.01 sec)

mysql>

参数详情：

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

下一篇：mysql性能问题 03
上一篇：mysql大体结构与执行流程 01

哒哒-blog

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
mysql物理文件 02

mysql物理文件一、数据库的数据存储文件1 .FRM 文件2 .MYD 文件3 .MYI 文件4 .ibd 文件与 .ibdata 文件二、慢查询日志1. 慢查询相关参数2. 慢查询日志配置3. 日志分析工具三、错误日志与二进制文件1. 错误日志1.1 启动错误日志1.2查看错误日志1.3 删除错误日志2. 二进制文件四、二进制文件基本操作1. 查看二进制文件1.1 查看二进制日志文件列表1.2 查看当前正在写入的二进制日志文件1.3 查看二进制日志文件内容2. 删除二进制文件2.1 删除所有二进制日志2
复制链接

扫一扫

专栏目录