Mysql数据库操作指引（五）——数据库逻辑备份与恢复

简介：数据备份对于数据库而言是至关重要的。当数据文件发生损坏、系统内核崩溃、计算机硬件损坏或者数据被误删等事件时，使用一种有效的数据备份方案，就可以快速解决以上所有的问题。MySQL提供了多种备份方案，一般分为物理备份和逻辑备份。

物理备份通过直接复制包含有数据库内容的目录与文件实现，这种备份方式适用于对重要的大规模数据进行备份，并且要求实现快速还原的生产环境。典型的物理备份就是复制 MySQL数据库的部分或全部目录，物理备份还可以备份相关的配置文件。但采用物理备份需要MySQL处于关闭状态或者对数据库进行锁操作，防止在备份的过程中改变发送数据。

逻辑备份通过保存代表数据库结构及数据内容的描述信息实现，保存创建数据结构以及添加数据内容的SQL语句，这种备份方式适用于少量数据的备份与还原。逻辑备份需要查询MySQL服务器获得数据结构及内容信息，因为需要查询数据库信息并将这些信息转换为逻辑格式，所以相对于物理备份而言比较慢。逻辑备份不会备份日志、配置文件等不属于数据库内容的资料。逻辑备份的优势在于不管是服务层面、数据库层面还是数据表层面的备份都可以实现，由于是以逻辑格式存储的，所以这种备份与系统、硬件无关。

本文主要介绍数据库的逻辑备份与在突发情况下的数据恢复，并通过实际操作进行检验。详细内容请参考下文。

一、访问Mysql

1、登陆linux系统

2、查看mysql服务

执行指令 # systemctl status mysqld

二、Mysql数据库开启binlog日志功能

1、在/etc/my.cnf文件里的[mysqld]区块添加如下内容

log-bin=/var/lib/mysql/mysql-bin 和server-id=123456

2、重启mysql服务

3、查看binlog日志

三、Mysql数据库逻辑备份与恢复

1、模拟数据库全量备份

（1）、查看mydb001数据库的employee表，后面通过这张表来展示对数据的备份与恢复的现象。如下图

（2）、创建一个目录用来存放备份的数据库

执行指令# mkdir /home/mysql_backup

（3）、对mydb001数据库进行全量备份，这里你可以对你自己存在的数据库进行操作

执行指令# /usr/bin/mysqldump -p -B -F -R -x --master-data=2 -uroot -p mydb001 > /home/mysql_backup/date_$(date '+%Y%m%d').sql

执行该语句。如下图

检查备份情况。如下图

2、模拟全量备份之后的操作

备份好之后对employee数据表再次插入数据

mysql> insert into employee values(4,'liqing','liqing123',6500);

mysql> insert into employee values(4,'ligao','ligao123',6700);

mysql> insert into employee values(4,'libiao','libiao123',5800);

3、模拟数据库丢失情况

删除mydb001数据库，模拟数据库丢失情况

4、准备恢复数据库

此时mydb001数据库文件已经丢失，需要对文件进行恢复。先恢复全量备份的数据，再通过binlog恢复从全量备份到数据丢失之前的操作。

（1）、查看全量备份之后新增的binlog文件

备注：这是全备时刻的binlog文件位置，即mysql-bin.000002的154行，因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了

（2）、查看mysql的数据存放目录，存在了/var/lib/mysql/目录中

mysql> show global variables like '%datadir%';

（3）、进入/var/lib/mysql/目录，查看binlog日志文件mysql-bin.000002。如下图

（4）、移动binlog文件到备份目录/home/mysql_backup中。如下图

（5）、导出为sql文件

mysqlbinlog -d mydb001 mysql-bin.000002 >002bin.sql

（6）、剔除其中的drop语句，要把drop下面的语句全部删掉，否则会报错。执行指令# vim 002bin.sql

5、恢复数据

（1）、先恢复全量备份的数据

执行指令 # mysql -uroot -p < date_20220809.sql

备注：此时可以看到，已经恢复到全量备份时的数据了。

（2）、接下来通过002bin.sql文件恢复全量备份到数据丢失之前的数据

执行指令 # mysql -uroot -p < 002bin.sql

四、Mysql数据库逻辑备份与恢复总结

1、本实例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复。

2、恢复条件为mysql必须开启binlog日志功能，并且要全备和增量的所有数据。

3、恢复时建议对外停止更新数据库。

4、先恢复全量，然后把全备时间点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除mssql查看数据库，再恢复到数据库。

（编辑：财气旺网 - 财气网）

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