MYSQL数据库的日志管理、备份

最新推荐文章于 2024-05-21 09:46:31 发布

池棠逍荷

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量538

点赞数 25

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_63162792/article/details/137921471

版权

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。

日志类型

错误日志

错误日志记录数据库服务器在运行过程中发生的错误、警告和其他信息的文件。这个日志文件包含了数据库服务器的运行状态信息，可以帮助管理员诊断和解决数据库服务器的问题。

错误日志主要记录如下几种日志：

服务器启动和关闭过程中的信息
服务器运行过程中的错误信息
事件调度器运行一个时间是产生的信息
在从服务器上启动从服务器进程是产生的信息

查看当前错误日志配置:
mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';
是否记录警告日志：
mysql> SHOW GLOBAL VARIABLES LIKE '%log_warnings%';

查询日志

是一种记录MySQL服务器接收到的所有查询（包括连接和断开连接的信息）的日志。这个日志文件包含了对数据库执行的所有操作，如SELECT、INSERT、UPDATE、DELETE等。通用查询日志可以用来跟踪数据库活动、诊断问题以及监视数据库性能。通过查看通用查询日志，管理员可以了解数据库的运行情况，发现潜在的性能问题或者安全隐患。

启动开关：general_log={ON|OFF}
全局日志开关：log={ON|OFF}  该开关打开后，所有日志都会被启用
记录类型：log_output={TABLE|FILE|NONE}：
而general_log_file如果没有指定，默认名是host_name.log。

看看上述几个值的默认配置：
mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';

mysql> SHOW GLOBAL VARIABLES LIKE '%log_output%';

二进制日志

记录所有更改数据的语句。主要用于复制和即时点恢复。用于记录数据库的更改操作，包括INSERT、UPDATE、DELETE等操作。二进制日志文件以二进制形式记录数据库中的更改，以便在需要时进行数据恢复、复制或数据分析。二进制日志对于数据库的备份和恢复非常重要，可以用于实现数据库的点对点复制、主从复制、故障恢复等功能。通过分析二进制日志，可以还原数据库的历史操作记录，进行数据分析和审计等操作。

注意：在5.6及以上版本一定要手动指定开启。5.6以下版本默认file_name为$datadir/mysqld-binlog。查看二进制日志的工具为：mysqlbinlog。

查看有哪些二进制日志文件：
mysql> SHOW BINARY LOGS;
查看当前正在使用的是哪一个二进制日志文件：
mysql> SHOW MASTER STATUS; 
查看二进制日志内容：
mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002';

二进制日志还原数据

使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql服务器上导入即可，如下：

mysqlbinlog mysqld-binlog.000002 >/root/temp_date.sql

慢日志

MySQL如果启用了 slow_query_log=ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）或不使用索引的查询。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

默认没有启用慢查询，为了服务器调优，建议开启
开启方法,当前生效，永久有效配置文件中设置
SET GLOBAL slow_query_log=ON;

那么多久算是慢呢？
如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

查看慢日志
mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';

事务日志

记录InnoDB等支持事务的存储引擎执行事务时产生的日志。

用于记录数据库中发生的所有事务操作的日志文件。事务日志对数据库的持久性和一致性起着重要作用。在MySQL中，有两种类型的事务日志：

二进制日志（Binary Log）：二进制日志记录了所有对数据库进行更改的SQL语句，它是用于数据恢复、主从复制和数据同步的重要组成部分。
重做日志（Redo Log）：重做日志是InnoDB存储引擎特有的日志，用于确保数据库在发生故障时能够恢复到一致的状态。重做日志记录了事务所做的更改，以便在数据库崩溃时重新应用这些更改。

这些事务日志对于数据库的安全性和可靠性至关重要，可以确保数据的一致性并提供故障恢复功能。MySQL会将这些日志持久化到磁盘上，以防止数据丢失或损坏。

MYSQL的备份

备份类型

根据服务器状态，可以分为热备份、温备份、冷备份

热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；

从对象来分，可以分为物理备份与逻辑备份

物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；

从数据收集来分，可以完全备份、增量备份、差异备份

完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；

备份内容

MYSQL备份通常包括以下内容：

数据库结构：包括数据库中的表、字段、索引、触发器等结构信息。
数据库数据：包括数据库中的所有数据文件，日志文件等。
存储过程和函数：如果数据库中有存储过程和函数，备份通常也会包括这些内容。
视图：数据库中的视图定义也会被备份。
用户权限和角色：数据库中的用户权限和角色设置也可以包括在备份中。
事件和调度任务：用于实现数据库备份的脚本，如果数据库中有定时任务或调度任务，备份也会包括这些信息。
其他配置信息：例如数据库参数配置、存储引擎设置等。

MySQL自带的备份工具

mysqldump，是mysql数据库管理系统，它允许用户将数据库中的数据和结构导出到一个SQL文件中，以便在需要时可以恢复数据库。mysqldump可以备份整个数据库、单个表或者指定的查询结果。自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。

使用mysqldump备份数据库非常简单，以下是基本的使用方法：

打开命令行窗口或终端。

输入以下命令格式来备份整个数据库：

mysqldump -u 用户名 -p 数据库名 > 保存路径/备份文件名.sql

例如，备份名为"testdb"的数据库到名为"testdb_backup.sql"的文件中：

mysqldump -u root -p testdb > testdb_backup.sql

输入密码后，mysqldump将开始备份数据库，并将备份文件保存在当前目录中。

除了备份整个数据库，还可以备份单个表或者指定的查询结果。

例如，备份名为"users"的表到名为"users_backup.sql"的文件中：

mysqldump -u root -p testdb users > users_backup.sql

将sql脚本还原为数据库：

mysql -u root -p testdb < 保存路径