• 【MySQL必知必会】 日志Log详解


    通用查询日志

    • 通用查询日志记录了所有用户的连接开始时间和截止时间,以及发给 MySQL 数据库服务 器的所有 SQL 指令。
    • 控制通用查询日志的系统变量 %general%
    • show variables like '%general%';
    • ① 系统变量 general_log 的值是 OFF表示通用查询日志处于关闭状态, 系统默认是 OFF 的, 打开会消耗资源并占有磁盘空间, 我们可以手动进行修改这个变量在需要的时候进行开启。
    • ② 通用查询日志文件的名称是 GJTECH-PC.log

    在这里插入图片描述

    开启通用查询日志:

    SET GLOBAL general_log = 'ON';  --  通用查询日志开启
    SET @@global.general_log_file = 'C:\mytest.log'; -- 修改日志名称 
    
    • 1
    • 2

    在这里插入图片描述

    查看通用查询日志

    打开这个 , 用记事本就可以看了
    在这里插入图片描述在这里插入图片描述

    删除通用查询日志

    • 为了节省磁盘空间, 我们可以移除旧的日志文件,创建新的日志文件,来对通用查询日志文件进行维护。
    -- 第一步,关闭通用查询日志
    SET GLOBAL general_log = 'OFF'; -- 关闭通用查询日志  
    SHOW VARIABLES LIKE '%general_log%'; -- 查看通用查询日志状态  general 也可以
    -- 第二步,把通用查询日志文件“C:\mytest.log”移至备份文件夹,空出磁盘 H 的空间。 就是把这个文件移到别的地方
    
    -- 第三步,开启通用查询日志
    SET GLOBAL general_log = 'ON';
    SHOW VARIABLES LIKE '%general_log%'; -- 这个时候 目录下会重新生产一个 mytest.log 
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    在这里插入图片描述

    慢查询日志

    • 慢查询日志用来记录执行时间超过指定时长的查询。它的主要作用是,帮助我们发现那些 执行时间特别长的 SQL 查询,并且有针对性地进行优化,从而提高系统的整体效率。

    • C:\ProgramData\MySQL\MySQL Server 8.0) MySQL 的配置文件“my.ini” 记事本打开即可

    • 如果修改了 MySQL 的配置文件“my.ini”,就需要重启服务器,这样才 能使修改生效。
      在这里插入图片描述

    • 控制慢查询日志的还有一个系统变量:min_examined_row_limit。 这个变量的意思是,查询扫描过的最少记录数。
      在这里插入图片描述

    • 查询扫描过的记录数大于等于min_examined_row_limit,并且查询执行时间超过 long_query_time 的值,那么,这个查询就被记录到慢查询日志中.

    例子
    在这里插入图片描述

    错误日志

    • 错误日志记录了 MySQL 服务器启动、停止运行的时间,以及系统启动、运行和停止过程 中的诊断信息,包括错误、警告和提示等
    • 错误 日志是发现问题、解决故障的首选。
    • 默认就是数据目 录:“C:\ProgramData\MySQL\MySQL Server 8.0\Data”。
      在这里插入图片描述
      在这里插入图片描述

    二进制日志

    • 二进制日志主要记录数据库的更新事件 , 二进制日志是进行数据恢复和数据复制的利器。

    操作

    -- 查看当前正在写入的二进制日志的 SQL 语句是:
    SHOW MASTER STATUS; -- 查看当前正在写入的二进制日志的名称和当前写入的位置:
    
    -- 查看所有的二进制日志的 SQL 语句是:
    SHOW BINARY LOGS;
    
    -- 查看二进制日志中所有数据更新事件的 SQL 语句是:
    SHOW BINLOG EVENTS IN 二进制文件名;
    
    
    -- 刷新 二进制日志
    FLUSH BINARY LOGS;
    -- 关闭服务器正在写入的二进制日志文件,并重新打开一个新文件,文件名的后缀在现有的基础上加 1。
    
    
    -- 用二进制日志恢复数据
    mysqlbinlog –start-positon=xxx --stop-position=yyy 二进制文件名 | mysql -u 用户 -p
    -- 执行二进制日志中从位置 xxx 开始,到 yyy 截止的所有数据更新操作。这里的截止位置也可以不写,意思是从位置 xxx 开始,执行二进制文件中的所有数据更新操作。
    
    -- 删除二进制日志
    RESET MASTER;
    -- 所有二进制日志文件都被删除了,MySQL 从头准备了一个“.000001”为后缀的新的二进制日志文件。
    SHOW BINARY LOGS;
    
    PURGE MASTER LOGS TO 'GJTECH-PC-bin.000005'; -- 删除指定的日志
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 每当服务器启动、刷新二进制日志或者二进制日志大小超过系统变量 max_binlog_size 指定的值时,系统就会生成一个新的二进制日志文件。

    案例

    就是指对数据库中存储的全部数据进行备份。备份完成之后,我又在商品信息表中插入了新数据。
    在这里插入图片描述

    -- 第一步,做数据库备份。
    mysqldump -u 用户 -p 密码 数据库 > 备份文件
    
    
    H:\>mysqldump -u root -p demo > mybackup.sql  // 把demo全备份了
    Enter password: *****
    -- 第二步,用“FLUSH BINARY LOGS;” 产生新的日志。
    -- 这步操作的目的是:产生一个新的二进制日志文件,使这个文件只保存数据备份之后的数据更新事件,这样可以方便我们查看文件的内容
    -- 第三步,给商品信息表插入一条新的数据
    -- 第四步,准备从备份文件恢复数据。
    SHOW BINARY LOGS; --  xxbin.000012
    FLUSH BINARY LOGS; -- 刷新产生新的二进制log
    SHOW MASTER STATUS;
    DROP DATABASE demo;CREATE DATABASE demo; -- 删除demo 并且 创建一个同名的
    
    -- 第五步,从备份恢复数据
    mysql -u 用户 -p 密码 数据库名称 < 备份文件
    
    H:\>mysql -u root -p demo<mybackup.sql  -- 这个表保存的是没有增加内容的
    Enter password: *****
    SELECT * FROM demo.goodsmaster;
    -- 增加的内容在 上面刷新前的xx bin.00000012 中
    
    C:\>mysqlbinlog --start-position=306 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\GJTECH-PC-bin.000012" | mysql -u root -p
    Enter password: *****
    -- 更新增加的内容部分
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    在这里插入图片描述

    • 二进制日志:主要就是不容易找准起始位置或者截止位置,建议你在数据备份结束之后,把当前的二进制日志位置记录下来,存放在一个安全的地方,这关系到数据恢复的完整性,一定不要怕麻烦。

    中继日志

    • **中继日志只在主从服务器架构的从服务器上存在。**从主服务器读取二进制日志的内容写入本地的日志文件中,这个从 服务器本地的日志文件就叫中继日志,根据中继日志的 内容对从服务器的数据进行更新,完成主从服务器的数据同步。
    • 搭建好主从服务器之后,中继日志默认会保存在从服务器的数据目录 (C:\ProgramData\MySQL\MySQL Server 8.0\Data)下,文件名的格式是:从服务器名 -relay-bin. 序号。
    • 中继日志还有一个索引文件:从服务器名 -relay-bin.index,用来定位当前正在使用的中继 日志。
    • 中继日志与二进制日志的格式相同,可以用 mysqlbinlog 工具进行查看。下面是中继日志 的一个片段:
    • 如果从服务器宕机,有的时候为了系统恢复,你 要重装操作系统,这样就可能会导致你的服务器名称与之前不同。 解决 把服务器名称改回之前的名称就可以

    回滚日志

    • 回滚日志的作用是进行事务回滚。
    • 当事务需要回滚的 时候,可以通过读取回滚日志,恢复到指定的位置。另一方面,回滚日志也可以让其他的 事务读取到这个事务对数据更改之前的值,从而确保了其他事务可以不受这个事务修改数 据的影响.
      在这里插入图片描述
    
    mysql> SHOW VARIABLES LIKE '%innodb_undo%';
    +--------------------------+-------+
    | Variable_name | Value |
    +--------------------------+-------+
    | innodb_undo_directory | .\ |  -- 表示回滚日志的存储目录是数据目录。  数据目录的位置可以通过查询变量“datadir”来查看
    | innodb_undo_log_encrypt | OFF | -- 表示回滚日志不加密。
    | innodb_undo_log_truncate | ON | -- 表示回滚日志是否自动截断回收,前提是设置了独立表空间。
    | innodb_undo_tablespaces | 2 |  -- 表示回滚日志有自己的独立表空间,而不是在共享表空间ibdata文件中。
    +--------------------------+-------+
    4 rows in set, 1 warning (0.00 sec)
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    在这里插入图片描述

    重做日志

    • 在系统遇到故障的恢复过程中,可以修复被未完成的事务修改的数据。
    • MySQL 为了提高数据存取的效率,减少磁盘操作的频率,对数据的更新操作不会立即写到磁盘上,而是把数据更新先保存在内存中,积累到一定程度,再集中进行磁盘读写操作。这样就存在一个问题:一旦出现宕机或者停电等异常情况,内存中保存的数据更新操作可能会丢失。这个时候就可以通过读取重做日志中记录的数据更新操作,把没来得及写到磁盘上的数据更新写到磁盘上,确保数据的完整性。
    
    mysql> SHOW VARIABLES LIKE '%innodb_log_files_in_group%';
    +---------------------------+-------+
    | Variable_name | Value |
    +---------------------------+-------+
    | innodb_log_files_in_group | 2 | -- 有 2 个重做日志文件
    +---------------------------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 量 innodb_log_files_in_group 值的取值范围是 1~4,这四个文件分别用于记录不同的操作.
    • 用户创建表的插入操作;
    • 用户创建表的更新和删除操作;
    • 临时表的插入操作;
    • 临时表的更新和删除操作。

    总结

    • 通用查询日志:可以记录所有连接的起始时间和终止时间,以及连接发送给数据库服务 器的所有指令,对我们复原操作的实际场景、发现问题,甚至是对数据库操作的审计都 有很大的帮助。
    • 慢查询日志:可以记录运行时间和检查记录数超过指定值的查询,方便我们对查询进行 优化。
    • 错误日志:它记录了服务器启动、运行和停止过程中的诊断信息,方便我们了解服务器 的状态,从而对服务器进行维护
    • 二进制日志:主要用于主从服务器之间的数据同步,以及服务器遇到故障时数据的无损失恢复
    • 中继日志:就是主从服务器架构中,从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容,来同步主服务器上的操作
    • 回滚日志:用来存储事务中数据更新前的状态,以便回滚和保持其他事务的数据一致性
    • 重做日志:是为了确保数值持久性、防止数据更新丢失的一种日志。

    数据表备份

    • 物理备份 : 通过把数据文件复制出来,达到备份的目的;(收费)
    • 逻辑备份:通过把描述数据库结构和内容的信息保存起来,达到备份的目的。

    备份

    • 数据备份的工具 mysqldump。它总共有三种模式:
    • 1、 备份数据库中的表; 2. 备份整个数据库; 3. 备份整个数据库服务器。
    -- 备份表
    mysqldump -h 服务器 -u 用户 -p 密码 数据库名称 [表名称 … ] > 备份文件名称
    -- -h 服务器  默认本机“localhost”
    H:\>mysqldump -u root -p demo goodsmaster membermaster > test.sql;  -- 在cmd.exe 中输入就可以
    
    mysqldump -h 服务器 -u 用户 -p 密码 --databases 数据库名称 … > 备份文件名   -- 备份数据库
    mysqldump -h 服务器 -u 用户 -p 密码 --all-databases > 备份文件名 -- 备份整个数据库服务器
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    恢复操作

    • 坑点 : 用win10的cmd就可以 , 语句不用带
    • 如果 备份文件xx.sql 保存的是表的信息 , 需要指定 数据库名称 。
    -- 恢复1
    mysql -u root -p demo < test.sql
    
    -- 恢复2  : 还不会用 
    USE demo;
    SOURCE H:\\test.sql
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    导出和导入表里的数据

    SELECT 语句导出数据

    SELECT 字段列表 INTO OUTFILE 文件名称  
    -- 文件名称为绝对路径 secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
    FIELDS TERMINATED BY 字符  -- 列之间的分隔符是“字符”;
    LINES TERMINATED BY 字符  -- 行之间的分隔符是“字符”。
    FROM 表名;
    
    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    需要my.ini 中的参数设定路径secure-file-priv
    在这里插入图片描述
    在这里插入图片描述

    使用“LOAD DATA”语句导入数据

    LOAD DATA INFILE 文件名 
    INTO TABLE 表名 FIELDS 
    TERMINATED BY 字符 
    LINES TERMINATED BY 字符;
    
    DELETE FROM demo.goodsmaster ;--  删除表的数据
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

  • 相关阅读:
    [JavaScript]_[初级]_[不使用JQuery原生Ajax提交表单]
    Chrome插件精选 — 网页截图插件
    numpy 笔记:重视 numpy 广播
    JavaScript高级编程
    DUBBO中的一致性哈希算法
    编译LoongArch的llvm交叉工具链
    商家收款码1000需要多少手续费
    python 将字节字符串转换成十六进制字符串
    C++多线程编程(第四章 案例1,C++11和C++17 多核并行计算样例)
    记录自签tomcat所用TLS1.2链接所需SSL证书
  • 原文地址:https://blog.csdn.net/weixin_49486457/article/details/125990650