MySQL的备份与恢复

数据备份的重要性

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

造成数据丢失的原因

1、程序错误

2、人为操作错误

3、运算错误

4、磁盘故障

5、灾难（火灾、地震、盗窃等）

数据库备份的分类

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

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份方法：

1.冷备份（脱机备份）：是在关闭数据库的时候进行的

2.热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

3.温备份：数据库锁定表格(不可写入但可读)的状态下进行备份操作

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

热备份的备份方法：

tar zcvf /usr/local/mysql/data/

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

完全备份：每次对数据库进行完整的备份

完全备份是对整个数据库、数据库结构和文件结构的备份。

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

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

相当于基石。

差异备份：备份自从上次完全备份之后被修改过的文件

增量备份：只要在上次完全备份或者增量备份后被修改的文件才会被备份

常用的备份方式

物理冷备

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

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

关闭MySQL数据库

使用tar命令直接打包数据库文件夹  /usr/local/mysql/data

直接替换现有的MySQL目录即可

专用备份工具mysqldump或者mysqlhotcopy

mysqldump常用的逻辑备份工具

MySQL自带的备份工具，可实现对MySQL的备份

可以将指定的库、表导出为SQL脚本

使用命令mysql导入备份的数据

mysqlhotcopy仅拥有备份myisam和archive表

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

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

第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

物理冷备(完全备份)

systemctl stop mysqld.service #关闭数据库 DATE=date +%F #设置时间变量 tar zcvf /opt/mysql_all-$DATE.tar.gz /usr/local/mysql/data/ #备份数据库 tar zxvf /opt/mysql_all-2023-05-22.tar.gz -C /usr/local/mysql/data/ #恢复数据库

mysqldump逻辑备份(完全备份)

-备份库 -语法格式 mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql #导出的就是数据库脚本文件 --实验 mysqldump -u root -p --databases auth > /opt/auth.sql mysqldump -u root -p --databases mysql auth > /opt/mysql-and-auth.sql mysqldump -u root -p --opt --all-databases > /opt/all-dbs.sql --opt 优化速度和存储 --all-databases 备份所有库 --databases 备份多个库 -备份表 -语法格式 mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql mysqldump -u root -p[密码] 库名 > /备份路径/备份文件名.sql 备份库内全部的表 --实验 mysqldump -u root -p auth user > /opt/auth-table-user.sql -备份表结构 mysqldump -u root -p -d 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql --实验 mysqldump -u root -p -d auth user > /opt/auth-desc-user.sql -恢复数据库 第一种 在MySQL交互式终端内输入 source /备份路径/备份文件 第二种 在命令行内输入 mysql -u root -p < /备份路径/备份文件 -恢复数据表 恢复数据表，必须要有数据库

一周一次完全备份，一天一次增量备份，恢复时，先恢复完全备份，再恢复增量备份依次恢复

增量备份依靠二进制文件

二进制文件存储在/usr/local/mysql/data

里面叫做mysql-bin.数字,该数字数据库每次重启一次或在mysql数据库内输入flush logs;

会在/usr/local/mysql/data/下生成一个二进制日志文件，这个文件的数字就增加1

但是最新的日志内并没有保存内容，而是在最新的日志前一个内

刷新二进制文件

在mysql数据库内输入flush logs;

在命令行输入mysqladmin -uroot -p flush-logs

查看备份文件里的内容

mysqlbinlog --no-defaults 增量备份文件

常用的增量恢复的方法有三种：

一般恢复、基于位置的恢复、基于时间点的恢复。

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

命令格式

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准

命令格式

mysqlbinlog --stop-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码

1）指定开始时间到结束时间 myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ --stop-datetime=’2014-10-45 03:10:46’-r time.sql 1 2）指定开始时间到文件结束 myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ -d esen -r time.sql 1 3）从文件开头到指定结束时间 myslbinlog mysqlbin.000008 --stop-datetime=’2014-10-45 03:10:46’ -d esen -r time.sql 1 基于位置点的增量恢复 1）指定开始位置到结束位置 myslbinlog mysqlbin.000008 --start-position=510 --stop-position=1312 -r pos.sql 1 2）指定开始位置到文件结束 myslbinlog mysqlbin.000008 --start-position=510 -r pos.sql 1 3）从文件开始位置到指定结束位置 myslbinlog mysqlbin.000008 --stop-position=1312 -r pos.sql