MySQL数据库备份与恢复

2021/9/12 19:06:43

编程Tag： mysql 数据库 root 备份 def opt

本文主要是介绍MySQL数据库备份与恢复，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

--------数据库备份--------

1、数据备份的重要性

备份的主要目的是灾难恢复
在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果

造成数据丢失的原因：

程序错误
人为操作错误
运算错误
磁盘故障
不可控因素

2、从物理与逻辑的角度，备份分为：

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
- 物理备份方法：
  - 冷备份（脱机备份）：是在关闭数据库的时候进行的
  - 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
  - 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

3、从数据库的备份策略角度，备份可分为：

完全备份：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

常见的备份方法

物理冷备

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的

专用备份工具mydump或mysqlhotcopy

myaqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MyISM和ARCHIVE表

启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

第三方工具备份

免费MySQL热备份软件Percona XtraBackup

--------MySQL完全备份与恢复--------

完全备份的概念

是对整个数据库，数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

优点

备份与恢复操作简单方便

缺点

数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

数据库完全备份分类

物理冷备份与恢复

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可

mysqldump备份与恢复

Mysql自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据

--------MySQL 完全备份--------

InnoDB存储引擎的数据库在磁盘上存储成三个文件：

db.opt(表属性文件)
表名.frm(表结构文件)
表名.ibd(表数据文件)

1、物理冷备份与恢复

systemctl stop mysqld
yum -y install xz
 
cd /usr/local/mysql
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
#解压恢复
tar Jxvf /opt/mysql_all_2021-8-30.tar.xz /usr/local/mysql/data

2、mysqldump 备份与恢复

1）完全备份一个或多个完整的库（包括其中所有的表）

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

例：

mysqldump -u root -p --databases def > /opt/def.sql
mysqldump -u root -p --databases mysql def > /opt/mysql-def.sql

2）完全备份 MySQL 服务器中所有的库

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

例：

mysqldump -u root -p --all-databases > /opt/all.sql

3）完全备份指定库中的部分表

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

例：

mysqldump -u root -p [-d] def mysql > /opt/def_mysql.sql

#使用“-d”选项，说明只保存数据库的表结构

#不使用“-d”选项，说明表数据也进行备份

4）查看备份文件

grep -v "^--" /opt/def_test1.sql | grep -v "^/" | grep -v "^$"

--------MySQL 完全恢复--------

systemctl start mysqld

1、恢复数据库

mysql -u root -p -e 'drop database def;'
#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出
mysql -u root -p -e 'SHOW DATABASES;'
 
mysql -u root -p < /opt/def.sql
mysql -u root -p -e 'SHOW DATABASES;'

2、恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

mysqldump -u root -p def test1 > /opt/def_test1.sql
 
mysql -u root -p -e 'drop table def.test1;'
mysql -u root -p -e 'show tables from def;'
 
mysql -u root -p def < /opt/def_test1.sql
mysql -u root -p -e 'show tables from def;'

--------MySQL增量备份与恢复--------

使用mysqldump进行完全备份存在的问题

备份数据中有重复数据

备份时间与恢复时间过长

增量备份的概念

是自上一次备份后增加/变化的文件或者内容

增量备份的特点

没有重复数据，备份不大，时间短

恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增备份进行逐个反推恢复

增量备份的方法

MySQL没有提供直接的增量备份方法

可通过MySQL提供的二进制日志间接实现增量备份

MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作

二进制日志在启动MySQL服务器后开始记录，并在文件达到max_ binlog_ size所设置的大小或者接收到flush logs命令后重新创建新的日志文件

只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了-一个时间段的增量备份

MySQL数据库增量恢复

一般恢复

将所有备份的二进制日志内容全部恢复

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复

--------MySQL 增量备份--------

1、开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED   指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

#只要重启服务就会生成二进制文件

systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

2、可每周对数据库或表进行完全备份

mysqldump -u root -p def test1 > /opt/def_test1_$(date +%F).sql
mysqldump -u root -p --all-databases> /opt/all_$(date +%F).sql

3、可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)

mysqladmin -u root -p flush-logs

4、插入新数据，以模拟数据的增加或变更

use def;
insert into test1 values(4,'rose','female',23);
insert into test1 values(5,'jack','male',26);

5、再次生成新的二进制日志文件(例如 mysql-bin.000003)

mysqladmin -u root -p flush-logs

#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

6、查看二进制日志文件的内容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

--------MySQL 增量恢复--------

1、一般恢复

1）模拟丢失更改的数据的恢复步骤

use def;
delete from test01 where id=4;
delete from test01 where id=5;
 
mysqlbinlog --no-defaults /opt/mysql-bin.000005 | mysql -u root -p

2）模拟丢失所有数据的恢复步骤

use def;
drop table test01;
 
mysql -u root -p def < /opt/def_test1_2021-8-31.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000005| mysql -u root -p

2、断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000009 #查看二进制日志文件

例：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210902 11:00:41 server id 1  end_log_pos 123 CRC32 0x4c5a25b0 	Start: binlog v 4, server v 5.7.20-log created 210902 11:00:41 at startup
ROLLBACK/*!*/;
# at 123
#210902 11:00:41 server id 1  end_log_pos 154 CRC32 0xdec225a7 	Previous-GTIDs
# [empty]
# at 154
#210902 11:02:51 server id 1  end_log_pos 219 CRC32 0x165d0c2a 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210902 11:02:51 server id 1  end_log_pos 344 CRC32 0x39c1648b 	Query	thread_id=3	exec_time=0	error_code=0
use `def`/*!*/;
SET TIMESTAMP=1630551771/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table Total_Sales (Name char(10),Sales int(5))
/*!*/;
# at 344
#210902 11:03:41 server id 1  end_log_pos 409 CRC32 0x640746ee 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 409
#210902 11:03:41 server id 1  end_log_pos 480 CRC32 0x1198d876 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1630551821/*!*/;
BEGIN
/*!*/;

（1）基于位置恢复

#仅恢复到操作 ID 为“219”之前的数据，即不恢复“219”的数据

mysqlbinlog --no-defaults --stop-position='219' /opt/mysql-bin.00002 | mysql -uroot -p

#仅恢复“344”的数据，跳过“219”的数据恢复

mysqlbinlog --no-defaults --start-position='219' /opt/mysql-bin.000002 | mysql -uroot -p

（2）基于时间点恢复

#仅恢复到 11∶03∶41 之前的数据，即不恢复“344”的数据

mysqlbinlog --no-defaults --stop-datetime='2021-09-02 11:03:41'/opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“11∶03∶41”的数据，跳过“219”的数据恢复

mysqlbinlog --no-defaults--start-datetime='2021-09-02 11:03:41' /opt/mysql-bin.000002 |mysql -uroot -p

这篇关于MySQL数据库备份与恢复的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！