MySQL 日志及数据备份

忙碌滴比特流

已于 2022-09-19 23:00:12 修改

阅读量1.9k

点赞数

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

于 2022-09-19 01:39:34 首次发布

本文链接：https://blog.youkuaiyun.com/m0_62948770/article/details/126921297

版权

本文详细介绍了MySQL的各种日志类型，包括redo、undo、errorlog、slow query log、bin log、relay log和general log，以及它们的作用和开启方法。接着，探讨了数据备份的重要性，分析了数据丢失的原因，并分类讨论了物理与逻辑备份，以及完全备份、增量备份与恢复的实操步骤。此外，还详细阐述了如何进行MySQL的基于时间点和位置的恢复操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

4、slow query log慢查询日志

1、对MySQL的配置文件my.cnf进行配置

3.2、从数据库的备份策略角度,备份可分为

二、MySQL数据备份与恢复实验操作

MySQL完全备份与恢复

1、物理冷备份与恢复

2、mysqldump 备份与恢复（温备份）

2.1、mysqldump 备份（温备份）

① 完全备份一个或多个完整的库 (包括其中所有的表)

② 完全备份 MySQL 服务器中所有的库

③ 完全备份指定库中的部分表

2.2、mysqldump 恢复（温备份）

2.2、在数据库中插入数据，做断点恢复的条件

第一节：日志的类型与作用

一、日志的类型

1、redo重做日志

达到事务一致性（每次重启会重做)

作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性

2、undo回滚日志

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交隔离级别就是通过mvcc+undo实现

3、errorlog错误日志

作用：Mysql本身启动，停止，运行期间发生的错误信息

4、slow query log慢查询日志

作用①：记录执行时间过长的sql语句，时间阈值（10s）可以配置，只记录执行成功

作用②：在于提醒优化

5、bin log二进制日志

作用：用于主从复制，实现主从同步（记录的内容是：数据库中执行的sql语句）

6、relay log 中继日志

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放

7、general log普通日志

作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能

二、日志开启方法

1、对MySQL的配置文件my.cnf进行配置

[root@zwb_mysql ~]# vim /etc/my.cnf

[client]
..................................

[mysql]
port = 3306
..................................
skip-grant-tables
#default-storage-engine=MyISAM


### 开启各类型日志
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
log-bin=mysql-bin
slow_query_log=ONslow_query_log_file=/usr/local/mysql/data/mysql_slow_query.loglong_query_time=5
..................................

2、查看各日志是否开启

mysql> show variables like 'general%';     ### 查看通用查询日志是否开启    
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | ON                                      |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'log_bin%';    ### 查看二进制日志是否开启
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like '%slow%';   ### #查看慢查询日功能是否开启
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF                                        |
| log_slow_slave_statements | OFF                                        |
| slow_launch_time          | 2                                          |
| slow_query_log            | ON                                         |
| slow_query_log_file       | /usr/local/mysql/data/mysql_slow_query.log |
+---------------------------+--------------------------------------------+
5 rows in set (0.00 sec)
 
mysql> show variables like 'long_query_time';  ### 查看慢查询时间设置
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> set global slow_query_log=ON;    ### 在数据库中设置开启慢查询的方法
Query OK, 0 rows affected (0.00 sec)

第二节：数据备份与恢复

一、数据备份概念

1、数据备份的重要性

① 在生产环境中，数据的安全性至关重要

② 任何数据的丢失都可能产生严重的后果

2、造成数据丢失的原因

①程序错误

②人为操作错误

③运算错误

④磁盘故障

⑤灾难（如火灾、地震）和盗窃

3、数据库备份分类

3.1、从物理与逻辑的角度，备份可分为

① 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份方法：

① 冷备份（脱机备份)：是在关闭数据库的时候进行的（tar）

② 热备份（联机备份)∶数据库处于运行状态，依赖于数据库的日志文件(mysqlhotcopy、mysqlbackup)

③ 温备份:数据库锁定表格（不可写入但可读）的状态下进行备份操作(mysqldump)

② 逻辑备份：对数据库逻辑组件（如:表等数据库对象）的备份

3.2、从数据库的备份策略角度,备份可分为

① 完全备份:每次对数据库进行完整的备份

每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长。

② 差异备份:备份自从上次完全备份之后被修改过的文件

备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份

③ 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

三种备份的方式比较：

备份方式	完全备份	差异备份	增量备份
完全备份时的状态	表1，表2	表1，表2	表1，表2
第一次添加内容	创建表3	创建表3	创建表3
备份内容	表1，2，3	表3	表3
第二次添加内容	创建表4	创建表4	创建表4
备份内容	表1，2，3，4	表3，4	表4

二、MySQL数据备份与恢复实验操作

MySQL完全备份与恢复

InnoDB存储引擎的数据库在磁盘上存储成三个文件: db.opt(表属性文件)、表名.frm (表结构文件)、表名.ibd(表数据文件)。

1、物理冷备份与恢复

物理冷备基本实验流程：

①创库建表

②先关闭数据库，之后打包备份

③恢复数据库

④恢复数据库，采用将备份数据mv成线上库文件夹的方式

①首先创建一个ceshi库，再创建一个表

mysql> show databases;           ### 显示MySQL服务器中的所有表
+--------------------+ 
| Database           |
+--------------------+
| information_schema |
| AAA                |
| CLASS              |
| class              |
| mysql              |
| performance_schema |
| sys                |
| wanbei             |
| zwb                |
+--------------------+
9 rows in set (0.00 sec)

mysql> use wanbei;                     ### 使用wanbei这张表
Database changed
mysql> create table if not exists info1 (    ### 创建表格式
    -> id int(4) not null auto_increment,
    -> name varchar(10) not null,
    -> age char(10) not null,
    -> hobby varchar(50),
    -> primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info1 values(1,'user1',20,'running');  ## 写入记录
Query OK, 1 row affected (0.01 sec)

mysql&

最低0.47元/天解锁文章