MySQL数据库的备份与恢复

LcWanf

已于 2023-11-18 16:57:01 修改

阅读量1.4k

点赞数 3

文章标签： linux mysql 数据库

于 2023-09-04 21:53:49 首次发布

本文链接：https://blog.csdn.net/qq_70246330/article/details/132678471

版权

文章目录

数据库的备份与恢复

一、数据库备份

备份就是为了防止原数据丢失,保证数据的安全。
当数据库因为某些原因造成部分或者全部数据丢失后,备份文件可以帮我们找回丢失的数据。
因此,数据备份是很重要的工作。

常用备份方案：

全量备份（全备）
增量备份（增备）
差异备份（差备）

需要根据具体的需求和情况选择合适的备份方案，综合考虑备份恢复时间、存储资源消耗和备份成本等因素。在实际应用中，用的比较多的是全量备份和差异备份，增量备份一般不用。

备份方案	优点	缺点
全量备份	全量备份会将源数据的完整副本保存下来，能够提供最高级别的数据保护。在恢复数据时，只需要访问单个备份就可以完全还原数据。	全量备份占用的存储空间和带宽较大，备份过程可能会比较耗时。如果频繁进行全量备份，会增加备份成本和资源消耗。
增量备份	增量备份只备份自上次备份以来新增或修改的数据，可以减少存储空间和备份时间，提高备份效率。	恢复数据时需要依次还原每个增量备份和基础全量备份，恢复过程相对复杂。如果增量备份链断裂或丢失，可能导致无法完全恢复数据。
差异备份	差异备份只备份上次全量备份后的新增或修改的数据，相比增量备份，减少了恢复过程中需要操作的备份数据量，更节省存储空间和备份时间。	差异备份会保存多个备份副本，恢复数据时需要还原基础全量备份和差异备份，而且差异备份的大小随着时间增长会逐渐增大。

mysql备份工具mysqldump

在日常维护工作当中经常会需要对数据进行导出操作，而mysqldump是导出数据过程中使用非常频繁的一个工具。

//语法：  mysqldump [选项] 数据库名 > 导出文件名

    mysqldump [OPTIONS] database [tables ...]          //导出某个数据库里面的某张表
    mysqldump [OPTIONS] --all-databases [OPTIONS]      //导出所有数据库（全备）
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //导出指定的数据库
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址（mysql服务器的ip）
    -pPASSWORD      //指定数据库用户的密码
    -P (大写)        //指定数据库监听的端口，这里的-P后面要加对应的端口号，如-P3307
    
//其他OPTIONS：
    -f			// --force 即使发现sql错误，忽略错误继续备份
    -d			// --no-data 只导出表结构只导出表结构
    -q			// --quick 快速导出快速导出
（省略）

全量备份

全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。

准备数据库和表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
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_test |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)

备份数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

备份全部数据库

[root@lc ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-data-202309041500.sql
Enter password: 
[root@lc ~]# ls
all-data-202309041500.sql  anaconda-ks.cfg

查看备份文件中的内容

备份数据库其实就是把创建数据库、创建表、插入数据的过程中的命令给记录下来，恢复数据的时候照着这个流程再来一遍数据就可以恢复了

注意：备份文件中的内容千万不要修改

-- Current Database: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--

备份表

mysql> use test;
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_test |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
+----------------+
5 rows in set (0.00 sec)

备份数据库test中的表class和student

[root@lc ~]# mysqldump -uroot -p -h127.0.0.1 --databases test --tables class student > data.test-table.class.student.sql
Enter password: 
[root@lc ~]# ls
all-data-202309041500.sql  data.test-table.class.student.sql
anaconda-ks.cfg

查看备份文件的内容

与备份数据库不同的是，在备份表时不会有创建数据库的命令。
因此在恢复表的时候，可以指定将这张表恢复到哪一个数据库中

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'三年二班'),(2,'三年三班'),(3,'一年二班'),(4,'二年九班');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

备份数据

（条件导出）

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)

备份该表中cid等于1的数据

[root@lc ~]# mysqldump -uroot -p -h127.0.0.1 --databases test --tables class --where='cid=1' > tables.class-cid=1.sql 
Enter password: 
[root@lc ~]# ls
 all-data-202309041500.sql   data.test-table.class.student.sql
 anaconda-ks.cfg            'tables.class-cid=1.sql'

查看备份文件的内容

在备份某条数据时，备份文件的内容中有创建表的记录。那是因为，这条数据只能在这个表结构的表中使用

-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--
-- WHERE:  cid=1

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'三年二班');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

二、数据库恢复

数据库恢复指在数据库发生异常或损坏时，使数据库恢复正常状态的过程。
当数据库数据丢失后不能恢复或者不能及时恢复都将对整个生产环境造成严重的破坏

数据丢失场景

人为操作失误造成某些数据被误删除
软件 BUG 造成部分数据或全部数据丢失
硬件故障造成数据库部分数据或全部数据丢失
安全漏洞被入侵数据恶意破坏

恢复数据的方法

方法一：

mysql -u用户名 -p密码 < 备份文件

[root@lc ~]# mysql -uroot -plcwanf001 < all-data-202309041500.sql

方法二：

登录到数据库里面

如果使用方法二恢复的是表的话，要先准备一个数据库，进入到数据库中在执行恢复表的操作

mysql> source all-data-202309041500.sql

全量备份的数据恢复

恢复数据库

先备份test库

[root@lc ~]# mysqldump -uroot -p -h127.0.0.1 --databases test > test.sql
Enter password: 
[root@lc ~]# ls
test.sql		anaconda-ks.cfg

删除test库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database test;
Query OK, 5 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

恢复test库

//方法一：
[root@lc ~]# mysql -uroot -p < test.sql 
Enter password: 

//查看test库和其中的表数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
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_test |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)
//数据成功恢复

//方法二：
mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
(省略)

//查看test库和其中的表数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from test.class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)
//成功恢复

恢复表

备份test库中的class表

[root@lc ~]#  mysqldump -uroot -p -h127.0.0.1 --databases test --tables class> class.sql
Enter password: 
[root@lc ~]# ls
 test.sql

删除test表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
+----------------+
5 rows in set (0.00 sec)

mysql> drop table class;
Query OK, 4 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| course         |
| score          |
| student        |
| teacher        |
+----------------+
4 rows in set (0.00 sec)

恢复表class

//方法一：
mysql -uroot -p test < class.sql

//查看表class是否恢复
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)

//方法二：
mysql> use test;
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> source class.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
（省略）

//查看class表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)

三、差异备份和数据恢复

差异备份

差异备份所基于的是最近一次的完整备份。这称为差异“基准”。
差异备份仅包括自建立差异基准后更改的数据。

要进行差异备份就要先开启MySQL服务器的二进制日志功能

//添加如下两行
[root@lc ~]# vim /etc/my.cnf 
[root@lc ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=1              //设置服务器标识符
log-bin=mysql_bin        //开启二进制日志功能

//重启服务
[root@lc ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

对库test进行一次全量备份

//备份的数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
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_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | lrf  |   23 |
|  3 | cl   |   25 |
|  4 | mk   |   28 |
|  5 | xc   |   26 |
|  6 | lxy  |   20 |
+----+------+------+
6 rows in set (0.00 sec)



//备份(此时的test里面有一张student表，表里面有6条数据)
[root@lc ~]# mysqldump -uroot -p -hlocalhost --all-databases > all-data.sql 
Enter password: 
[root@lc ~]# ls
all-data.sql

对库test中的表student进行操作修改表的数据

//插入一条新数据
mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | lrf  |   23 |
|  3 | cl   |   25 |
|  4 | mk   |   28 |
|  5 | xc   |   26 |
|  6 | lxy  |   20 |        
+----+------+------+
6 rows in set (0.00 sec)


mysql> insert student(name,age) values ('smt',20);
Query OK, 1 row affected (0.00 sec)


mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | lrf  |   23 |
|  3 | cl   |   25 |
|  4 | mk   |   28 |
|  5 | xc   |   26 |
|  6 | lxy  |   20 |
|  7 | smt  |   20 |              //插入了一条数据，现在有7条数据了
+----+------+------+
7 rows in set (0.00 sec)

误删除库

mysql> drop database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

差量备份的数据恢复

上面删除了test库，现在我们想要恢复到做完全量备份和插入一条数据的时候，也就是有7条数据的时候。

我们就要先恢复到全量备份的时候（6条数据的时候），然后再恢复到有7条数据的时候。

发现自己误删除数据后要立马刷新创建新的二进制日志

//刷新二进制日志
[root@lc ~]# mysqladmin -uroot -p flush-logs
Enter password:

//查看二进制日志文件
[root@lc ~]# ll /opt/data/
total 122976
-rw-r-----. 1 mysql mysql       56 Aug 31 14:05 auto.cnf
-rw-------. 1 mysql mysql     1680 Aug 31 14:05 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 31 14:05 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 31 14:05 client-cert.pem
-rw-------. 1 mysql mysql     1680 Aug 31 14:05 client-key.pem
-rw-r-----. 1 mysql mysql      362 Sep  4 20:13 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep  4 20:39 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep  4 20:39 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 31 14:05 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep  4 20:32 ibtmp1
-rw-r-----. 1 mysql mysql    11646 Sep  4 20:13 lc.err
drwxr-x---. 2 mysql mysql     4096 Aug 31 14:05 mysql
-rw-r-----. 1 mysql mysql      625 Sep  4 20:49 mysql_bin.000001 //二进制日志
-rw-r-----. 1 mysql mysql      154 Sep  4 20:49 mysql_bin.000002 //二进制日志
-rw-r-----. 1 mysql mysql       38 Sep  4 20:49 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Sep  4 20:13 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Aug 31 14:05 performance_schema
-rw-------. 1 mysql mysql     1680 Aug 31 14:05 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Aug 31 14:05 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 31 14:05 server-cert.pem
-rw-------. 1 mysql mysql     1676 Aug 31 14:05 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Aug 31 14:05 sys

注意：

mysql_bin.000001和mysql_bin.000002都是二进制日志，这两个有什么区别呢？

在没刷新之前，只有mysql_bin.000001，刷新之后就会有mysql_bin.000001和mysql_bin.000002两个。刷新后mysql_bin.000001就不再继续记录日志了，而新产生的日志就会记录在mysql_bin.000002里面。

所以恢复数据的时候，是要通过读取mysql_bin.000001里面的日志进行恢复

恢复数据

//先进行全量数据恢复
（恢复前）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@lc ~]# mysql -uroot -p < all-data.sql 
Enter password: 
[root@lc ~]# 

//恢复后
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
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_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | lrf  |   23 |
|  3 | cl   |   25 |
|  4 | mk   |   28 |
|  5 | xc   |   26 |
|  6 | lxy  |   20 |
+----+------+------+
6 rows in set (0.00 sec)

此时还没有达到要求，要恢复到有7条数据的时候。
可以推理出有7条数据的时候就是在删除test库之前的那个时刻

我们查看日志信息

发现’drop database test’这一步操作的位置是 578 ，那么它前一步就是有7条数据的时候，也就是486

mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 219 | Query          |         1 |         291 | BEGIN                                 |
| mysql_bin.000001 | 291 | Table_map      |         1 |         345 | table_id: 109 (test.student)          |
| mysql_bin.000001 | 345 | Write_rows     |         1 |         390 | table_id: 109 flags: STMT_END_F       |
| mysql_bin.000001 | 390 | Xid            |         1 |         421 | COMMIT /* xid=33 */                   |
| mysql_bin.000001 | 421 | Anonymous_Gtid |         1 |         486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 486 | Query          |         1 |         578 | drop database test                    |
| mysql_bin.000001 | 578 | Rotate         |         1 |         625 | mysql_bin.000002;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)

恢复数据到486这个位置

//使用mysqlbinlog恢复差异备份
[root@lc ~]# mysqlbinlog --stop-position=486 /opt/data/mysql_bin.000001 |mysql -uroot -p
Enter password: 
[root@lc ~]# 

//恢复后
mysql> select * from test.student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | lrf  |   23 |
|  3 | cl   |   25 |
|  4 | mk   |   28 |
|  5 | xc   |   26 |
|  6 | lxy  |   20 |
|  7 | smt  |   20 |
+----+------+------+
7 rows in set (0.00 sec)