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语句删除，再恢复到数据库。