Mysql数据库的备份和恢复

最新推荐文章于 2025-04-05 11:05:44 发布

_Pikachu

最新推荐文章于 2025-04-05 11:05:44 发布

阅读量128

点赞数

文章标签： mysql

原文链接：https://blog.youkuaiyun.com/weixin_56422027?spm=1000.2115.3001.5343

版权

本文详细介绍了MySQL的各种日志类型及其作用，包括redo日志、undo日志、errorlog、slowquerylog和binlog。同时，讨论了二进制日志在主从复制中的角色以及中继日志的作用。还涵盖了MySQL配置文件中关于日志的相关设置。此外，文章讲解了物理备份、逻辑备份的区别，并阐述了完全备份、差异备份和增量备份的策略。最后，通过实例演示了如何进行数据库的备份与恢复，包括物理冷备份、增量恢复以及二进制日志的使用。

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

在这里插入图片描述

重中之重
日志的类型与作用

重中之重

MySQL的日志默认保存位置为 /usr/local/mysql/data

日志的类型与作用

redo重做日志：达到事务一致性（每日重启时会重做）
确保了日志的持久性，当值在发生故障、脏页未写入磁盘，重启数据库会进行重做，从而达到事务一致性
undo回滚日志：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交，隔离级别是通过mvcc+undo实现
errorlog错误日志：Mysql启动、运行、停止期间发生的错误信息
slow query log慢查询日志：记录执行事件过长的sql，事件阈值（10s）可以配置，只记录执行成功
bin log二进制日志：用于主从复制，实现主从同步。记录内容为：数据库中执行的sql语句
relay log中继日志：用于数据库主从同步，讲主库发赖的bin log保存在本地，然后从库进行回访
genral log普通日志：记录数据库的操作明细，默认关闭，开启后会降低数据库性能

配置文件

[root@localhost ~]# vim /etc/my.cnf 进入配置文件添加以下字段

在这里插入图片描述

log-error=/usr/local/mysql/data/mysql_error.log
//错误日志，用来记录当mysql启动、停止或运行发生的错误信息，默认已开启
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
//通用查询日志，用来记录mysql的所有连接和语句，默认时关闭的
log-bin=mysql-bin
//二进制日志，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更爱，可用于数据恢复，默认已开启
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5         //设置超过五秒执行的语句被记录，缺省为10秒
//慢查询日志，用来记录所有执行事件超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的

重启数据库

[root@localhost ~]# systemctl restart mysqld

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)

二进制日志开启后，重启mysql会在目录中看到二进制日志
在这里插入图片描述
重启后会更新索引（记录更新语句）文件，用于记录新的更新语句或者刷新二进制日志

mysql-bin.index为二进制文件的索引
[root@localhost data]# vim mysql-bin.index

在这里插入图片描述

物理备份

数据库备份可以为物理备份（物理备份又可以称为冷备份（脱机备份）、热备份（连接备份）和温备份）和逻辑备份，物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。

逻辑备份

逻辑备份时对数据库逻辑组件的备份，表示为逻辑数据库结构这种类型的备份适用于可以编辑数据值或表结构，从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份。

完全备份：每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份于增量备份的基础，完全备份的备份与恢复操作都很简单，但是数据存在当量的重复并且占用大量的磁盘空间，备份的时间也很长
差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量越来越大，恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份
增量备份
只在上次完全备份或者增量备份后被修改的文件才会备份，以上次完全备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用的空间也很小，备份速度比完备和差异都快，但是恢复时，需要从上一次饿完整备份开始到左后一次增量备份之间的所有增量依次恢复，如果中间的某次备份数据损坏，将导致数据丢失

完全备份与恢复

对整个数据库、数据库结构和文件结构的备份，保存的时备份完成时刻的数据库，也是差异备份和增量备份的基础，优点时备份与恢复操作简单方便，缺点时数据存在大量的重复，占用大量的备份空间，且备份与恢复的时间很长

[root@localhost data]# ls
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  sys  xy
[root@localhost data]# cd xy
[root@localhost xy]# ls
db.opt  info1.frm  info1.ibd     
// db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

[root@localhost data]# systemctl stop mysqld.service        //关闭mysql数据库
[root@localhost data]# yum -y install xz

压缩备份

[root@localhost data]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/   //压缩日志
[root@localhost data]# mv /usr/local/mysql/data/ /opt/      //备份到opt目录下

解压恢复

[root@localhost data]# tar Jxvf /opt/mysql_all_2021-07-18.tar.xz -C /usr/local/mysql/data/    //解压
[root@localhost data]# cd /usr/local/mysql/data/     //查看

mysqldump备份与恢复（温备份）
完全备份一个或多个完整的库（包括其中所有表）
格式

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    //导出的就是数据库脚本文件

[root@localhost ~]# mysqldump -u root -p --databases xy > /opt/xy.sql    //备份一个xy库
Enter password: 
[root@localhost ~]# mysqldump -u root -p --databases mysql xy > /opt/mysql-xy.sql   //备份mysql和xy库
Enter password: 
[root@localhost opt]# ls
mysql-5.7.20  mysql-boost-5.7.20.tar.gz  mysql-xy.sql  rh  xy.sql

完全备份mysql服务器中的所有库
格式

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

[root@localhost ~]# mysqldump -u root -p --all-databases > /opt/all.sql      //备份所有的库
Enter password: 
[root@localhost opt]# ls
all.sql  mysql-5.7.20  mysql-boost-5.7.20.tar.gz  mysql-xy.sql  rh  xy.sql

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

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql

[root@localhost ~]# mysqldump -u root -p [-d] xy info1 info2 > /opt/xy_info1.sql
Enter password: 
[root@localhost opt]# ls
all.sql  mysql-5.7.20  mysql-boost-5.7.20.tar.gz  mysql-xy.sql  rh  xy_info1.sql  xy.sql

使用-d说明只保存数据库的表结构，不使用，说明表数据也保存

查看备份文件

[root@localhost ~]# grep -v "^--" /opt/xy_info1.sql | grep -v "^/" | grep -v "^$"
DROP TABLE IF EXISTS `info1`;
CREATE TABLE `info1` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` char(10) NOT NULL,
  `hobby` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
LOCK TABLES `info1` WRITE;
INSERT INTO `info1` VALUES (1,'user1','20','running'),(2,'user2','30','singing');
UNLOCK TABLES;

物理冷备份及恢复

备份数据库中的数据打包，模拟数据丢失，利用备份打包恢复数据

[root@localhost ~]# mysql -uroot -p
Enter password: 
 
mysql> create database zz;               //创建数据库zz
Query OK, 1 row affected (0.00 sec)

mysql> use zz;
Database changed

mysql> create table a(id int(10));        //插入数据
Query OK, 0 rows affected (0.01 sec)

mysql> desc a;                     //查看
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

[root@localhost ~]# systemctl stop mysqld.service    //关闭数据库
[root@localhost ~]#  tar zcvf /opt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/    //备份打包所有数据库为当天日期
[root@localhost ~]# cd /opt
[root@localhost opt]# ls
mysql-5.7.20  mysql_all-2021-07-18.tar.gz  mysql-boost-5.7.20.tar.gz  rh

[root@localhost opt]# systemctl start mysqld.service   //开启数据库

[root@localhost opt]# mysql -uroot -p            //进入数据库
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, 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> show databases;              //查看库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xy                 |
| zz                 |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database zz;     //删除zz库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;         //查看库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xy                 |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# systemctl stop mysqld.service        //关闭数据库
[root@localhost ~]# cd /opt
[root@localhost opt]# tar zxvf mysql_all-2021-07-18.tar.gz           //解压备份
[root@localhost opt]# mkdir mysql_old                 //创建目录
[root@localhost opt]# mv /usr/local/mysql/data/ mysql_old/            //剪切在备份完成之前的数据
[root@localhost opt]# mv usr/local/mysql/data/ /usr/local/mysql/          
[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# ls
bin      COPYING-test  docs     lib  mysql-test  README-test  support-files
COPYING  data          include  man  README      share        usr
[root@localhost ~]# systemctl start mysqld.service           //开启mysql
[root@localhost ~]# mysql -uroot -p          //登录
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, 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> show databases;            //查看库zz已经在了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xy                 |
| zz                 |
+--------------------+
6 rows in set (0.00 sec)

mysql> use zz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;        //查看表
+--------------+
| Tables_in_zz |
+--------------+
| a            |
+--------------+
1 row in set (0.00 sec)

mysql> desc a;          //表格式
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

增量与恢复

mysql数据库增量恢复有

一般恢复：将所有备份的二进制日志内容全部恢复
基于位置恢复：数据库在某一个时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作，在发生错误节点之前的一个节点，也就是上一次正确操作的位置点停止
基于时间点恢复：跳过某个发生错误时间点实现数据恢复
开启二进制日志功能

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

在这里插入图片描述
二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（默认模式），默认格式为STATEMENT

STEMENT：每一条涉及到被修改的sql都会记录在binlog中，但是日志量过大、主从复制等架构记录日志会出现问题
ROW：只记录变动的记录，不记录sql的上下文环境
MIXED：一般的语句使用statement，函数使用ROW方式存储

[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001                    //查看二进制日志的内容

增量备份是基于完全备份的，所有需要执行完备

[root@localhost ~]# mysqldump -uroot -p zz a  > /opt/zz_a_$(date +%F).sql
Enter password: 
[root@localhost ~]# mysqldump -uroot -p zz > /opt/zz_all_$(date +%F).sql
Enter password: 
[root@localhost ~]# mysqladmin -uroot -p flush-logs         //进行增量备份操作，生成新的二进制文件
Enter password: 
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql   sys  zz
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.index     xx
ibdata1         ibtmp1       mysql-bin.000002  performance_schema  xy
[root@localhost data]# mysqladmin -u root -p flush-logs

mysql> create database xx;
Query OK, 1 row affected (0.00 sec)

mysql> use xx;
Database changed
mysql> create table test1(id int(4),name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,zhangsan);
ERROR 1054 (42S22): Unknown column 'zhangsan' in 'field list'
mysql> insert into test1 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test1 values(2,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from xx;
ERROR 1146 (42S02): Table 'xx.xx' doesn't exist
mysql> select * from test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)



[root@localhost data]# mysqladmin -u root -p flush-logs
Enter password: 

[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.000003  performance_schema  xy
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.000004  sys                 zz
ibdata1         ibtmp1       mysql-bin.000002  mysql-bin.index   xx

增量恢复

[root@localhost data]# mysqldump -uroot -p xx test1 > /opt/xx_test1.sql     //备份表

mysql> drop table xx.test1;          //删除xx库中的test1
Query OK, 0 rows affected (0.01 sec)

mysql> use xx;
Database changed

mysql> show tables;
Empty set (0.00 sec)

[root@localhost opt]# mysql -uroot -p zz < zz_test1_2021-07-18.sql  //恢复
Enter password: 

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | one  |
|    2 | two  |
+------+------+
2 rows in set (0.00 sec)

基于mysql-bin.000002恢复

[root@localhost data]# mysql -uroot -p
Enter password: 

mysql> drop database zz;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
[root@localhost data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p          
Enter password: 

mysql> show databases;     //查看以恢复zz
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zz                 |
+--------------------+
5 rows in set (0.00 sec)