MySQL应该如何备份与还原

发布时间：2023-09-23 15:16:36 所属栏目：MySql教程来源：

导读：在进行热备时，备份操作和应用服务在同时运行，这样十分消耗系统资源，导致数据库服务性能下降，这就要求我们选择一个合适的时间（一般在应用负担很小的时候）再来进行备份操作。

需要注意的是，不是备份就万事大

在进行热备时，备份操作和应用服务在同时运行，这样十分消耗系统资源，导致数据库服务性能下降，这就要求我们选择一个合适的时间（一般在应用负担很小的时候）再来进行备份操作。

需要注意的是，不是备份就万事大吉了，最好确认备份是否可用，所以备份之后的恢复测试是非常有必要的。同时备份时间也要灵活调整，如：

数据更新频繁，则应该频繁地备份。

数据的重要性，在有适当更新时进行备份。

在数据库压力小的时间段进行备份，如一周一次完全备份，每天进行增量备份。

中小公司，完全备份一般一天一次即可。

大公司可每周进行一次完全备份，每天进行一次增量备份。

尽量为企业实现主从复制架构，以增加数据的可用性。

数据库备份类型可以从两个角度来看待：

MySQL应该如何备份与恢复

1、从物理与逻辑的角度：

物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

物理备份有可以分为以下几种类型：

①、冷备份：在数据库关闭状态下进行备份操作；

②、热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件；

③、温备份：数据库锁定表格（不可写入，但可读取）的状态下进行备份；

逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（create database、create table语句）和内容（insert语句或分隔文本文件）的信息。这种类型的备份使用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系上重新创建数据。

2、从数据库的备份策略角度：

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份。其中呢，完整备份是实现差异、增量备份的基础。

完整备份：每次对数据进行完整的备份，即对整个数据库的备份。备份与恢复的操作非常简单，但是数据存在大量的重复，会占用大量的磁盘空间，备份的时间也很长。

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

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

备份实例：

1、物理冷备份与恢复：

[root@mysql /]# systemctl stop mysqld        #先停掉服务

[root@mysql /]# mkdir /backup            # 创建一个备份目录

[root@mysql /]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/         # 将整个数据库文件夹打包备份，（date +%F）当前日期

[root@mysql /]# ls -l /backup/      # 查看备份文件

total 732         # 总用量

-rw-r--r-- 1 root root 746839 Aug 2 14:48 mysql_all-2019-08-02.tar.gz     # 备份文件

来模拟数据库文件丢失：

[root@mysql /]# mkdir /diushi

[root@mysql /]# mv /usr/local/mysql/data/ /diushi/         # 将数据库存放目录移动到另一个目录

恢复数据库：

[root@mysql /]# mkdir /restore/

[root@mysql /]# tar zxf /backup/mysql_all-2019-08-02.tar.gz -C   /restore/     # 要先将备份文件释放到一个空目录中，然后将需要的恢复到原位置

[root@mysql /]# mv /restore/usr/local/mysql/data/   /usr/local/mysql/         # 将数据库目录恢复到原位置

[root@mysql /]# systemctl restart mysqld             # 重启服务验证

2、mysqldump 备份与恢复：

备份数据库

备份指定库中的表：

mysqldump    [选项]    库名   表名表名2   …… > /备份路径/备份文件名

[root@mysql /]# mysqldump -u root -p test user   >   /backup/user-table.sql          # 将test库中的user表备份到backup目录中

Enter password:                   # 输入密码

备份一个或多个完整的库：

mysqldump   [选项]   --databases      库名1   库名2   …… > /备份路径/备份文件名

[root@mysql /]# mysqldump -u root -p --databases test mysql    >   /backup/databases.sql            # 将 test 和 mysql 库备份到backup中

Enter password:                       # 输入密码

备份 MySQL 中的所有库：

mysqldump   [选项]     --all-databases   >    /备份路径/备份文件名

[root@mysql /]# mysqldump -u root -p   --opt   --all-databases   >   all-data.sql          # --opt：优化执行速度

Enter password:          # 输入密码

[root@mysql /]# ls backup/            # 查看备份文件

all-data.sql      databases.sql       user-table.sql

恢复数据库：

恢复库中的表

mysql     [选项]   库名   <   /备份路径/备份文件名

[root@mysql /]# mysql -u root -p test   <   /backup/user-table.sql

Enter password:

[root@mysql /]# mysql -u root -p -e ' show tables from test;'           // 验证导入结果

Enter password:

+----------------+

| Tables_in_test |

+----------------+

| user           |

+----------------+

恢复单个或多个库：

[root@mysql /]# mysql -u root -p -e ' drop database test;'     // 删除 test 数据库，模拟故障

Enter password:

[root@mysql /]# mysql -u root -p -e ' show databases;'        // 验证 test 数据库是否存在

Enter password:

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

[root@mysql /]# mysql -u root -p   <   /backup/databases.sql     // 执行导入恢复操作

Enter password:

[root@mysql /]# mysql -u root -p -e ' show databases;'       // 确认恢复后结果

Enter password:

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

MySQL 增量备份与恢复：

与完全备份不同，增量备份没有重复数据，备份量不大，时间段，但其恢复比较麻烦，需要上次完全备份及完全备份之后的所有增量备份之后才能恢复，而且要对所有增量备份逐个反推恢复。MySQL没有提供直接的增量备份办法，所以一般是通过MySQL提供的二进制日志来间接实现增量备份。

要进行MySQL的增量备份，首先需要开启二进制日志功能：

[root@mysql /]# mkdir /usr/local/mysql/logs            # 创建一个存放二进制日志文件的目录

[root@mysql /]# cd /usr/local/mysql/

[root@mysql mysql]# chown mysql:mysql logs/       # 设置目录归属，使其能够写入

[root@mysql /]# vim /etc/my.cnf                # 编写配置文件

[mysqld]

log-bin=/usr/local/mysql/logs/mysql-bin

[root@mysql /]# systemctl restart mysqld          # 重启服务，使配置生效

[root@mysql /]# ll /usr/local/mysql/logs/mysql-bin.*         # 目录下自动生成日志文件

-rw-rw---- 1 mysql mysql 120 Aug 2 17:04 /usr/local/mysql/logs/mysql-bin.000001

-rw-rw---- 1 mysql mysql 39 Aug 2 17:04 /usr/local/mysql/logs/mysql-bin.index

现在所有对数据库的修改，都将记录mysql-bin.000001文件中，当执行“mysqladmin -u root -p flush-logs”刷新二进制日志后，将会继续生成一个名为mysql-bin.000002的文件，之后所有的更改又将存在mysql-bin.000002文件中，以此类推，每刷新一次，就会生成一个新文件！

首先我们在表中先录入一些信息，然后进行一次完整备份：

mysql> select * from user_info；

+------+----------+----------+

| id   | xingming | nianling |

+------+----------+----------+

| 001 | zhangsan |       20 |

| 002 | lisi     |       25 |

| 003 | wangwu   |       20 |

+------+----------+----------+

[root@mysql /]# mkdir /mysql_bak                # 创建一个备份存放位置

[root@mysql /]# mysqldump -u root -p   test user_info   > /mysql_bak/test_userinfo$(date +%F).sql           # 进行完整备份

Enter password:

[root@mysql /]# ls /mysql_bak/               # 验证备份结果

test_userinfo2019-08-02.sql

[root@mysql /]# mysqladmin -u root -p flush-logs            # 刷新日志文件

Enter password:

[root@mysql /]# ll /usr/local/mysql/logs/mysql-bin.*         # 生成新的日志文件000002

-rw-rw---- 1 mysql mysql 1192 Aug 2 17:18 /usr/local/mysql/logs/mysql-bin.000001

-rw-rw---- 1 mysql mysql 120 Aug 2 17:18 /usr/local/mysql/logs/mysql-bin.000002

-rw-rw---- 1 mysql mysql   78 Aug 2 17:18 /usr/local/mysql/logs/mysql-bin.index

继续录入新的数据，并进行增量备份：

mysql> select * from user_info;

+------+----------+----------+

| id   | xingming | nianling |

+------+----------+----------+

| 001 | zhangsan |       20 |

| 002 | lisi     |       25 |

| 003 | wangwu   |       20 |

| 004 | zhaoliu |       20 |

| 005 | sunqi    |       30 |

+------+----------+----------+

[root@mysql /]# mysqladmin -u root -p flush-logs            # 刷新日志文件，这样在000002中只有两条数据的操作

Enter password:

[root@mysql /]# cp /usr/local/mysql/logs/mysql-bin.000002 /mysql_bak/      　＃将日志文件复制到备份目录中

模拟user_info 这个表被误删除了，恢复：

[root@mysql /]# mysql -u root -p test   <   /mysql_bak/test_userinfo2019-08-02.sql    # 先恢复完整备份

Enter password:

[root@mysql /]# mysql -u root -p -e ' select * from test.user_info;'      # 查看一下确认，恢复成功

Enter password:

+------+----------+----------+

| id   | xingming | nianling |

+------+----------+----------+

| 001 | zhangsan |       20 |

| 002 | lisi     |       25 |

| 003 | wangwu   |       20 |

+------+----------+----------+

[root@mysql /]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p                              # 恢复增量备份，--no-defaults 选项必须要有

[root@mysql /]# mysql -u root -p -e ' select * from test.user_info;'       # 确认，增量备份恢复成功

Enter password:

+------+----------+----------+

| id   | xingming | nianling |

+------+----------+----------+

| 001 | zhangsan |       20 |

| 002 | lisi     |       25 |

| 003 | wangwu   |       20 |

| 004 | zhaoliu |       20 |

| 005 | sunqi    |       30 |

+------+----------+----------+

下来就是基于位置恢复和基于时间点恢复了，这两种恢复是有很大的相同之处的，想要实现，必需先查看二进制日志文件来确认恢复的位置或时间点。

[root@mysql /]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002

……      // 省略部分内容

#at 199               # 这一行就是操作ID号了

#190802 17:21:40 server id 1 end_log_pos 346 CRC32 0xc61c38c9 Query   thread_id=4 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1564737700/*!*/;

insert into user_info (id,xingming,nianling) values('004','zhaoliu','20')

/*!*/;

#at 346

#190802 17:21:40 server id 1 end_log_pos 377 CRC32 0xea2c7707 Xid = 50

COMMIT/*!*/;                   # 操作确认标记谨记一条操作在此才算结束

#at 377

#190802 17:22:09 server id 1 end_log_pos 456 CRC32 0x6265a2a6 Query   thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1564737729/*!*/;

BEGIN

/*!*/;

#at 456

#190802 17:22:09 server id 1 end_log_pos 601 CRC32 0x3727aeb7 Query   thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1564737729/*!*/;

insert into user_info (id,xingming,nianling) values('005','sunqi','30')

/*!*/;

#at 601

#190802 17:22:09 server id 1 end_log_pos 632 CRC32 0x17c4779a Xid = 51

COMMIT/*!*/;

#at 632

#190802 17:24:05 server id 1 end_log_pos 679 CRC32 0x9c698f03 Rotate to mysql-bin.000003 pos: 4

DELIMITER ;

#End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mysql /]# mysqlbinlog --no-defaults --stop-position='456' /mysql_bak/mysql-bin.000002 | mysql -u root -p               # 恢复操作ID‘456’ 之前的操作

Enter password:

--start-position='456'：表示为从操作456开始恢复，该日志文件456之前的数据不会恢复；

以上选项可更改为下面类型：

--stop-position='456'：表示恢复到操作456就停止，该日志文件456之后的数据不会恢复；

基于时间点的恢复：

--start-datetime='2019-08-2 17:22:09'：表示恢复该时间之后的数据；

--stop-datetime='2019-08-2 17:22:09'：表示仅恢复该时间之前的数据；

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!