MySQL数据库备份与恢复

目录

一、数据库备份

（一）数据备份的重要性

（二）备份分类

1.物理备份

2.逻辑备份

（三）备份策略

1.完全备份

2.差异备份

3.增量备份

（四）常见备份方法

1.完全备份策略

（1）物理冷备

① 关闭数据库

​编辑

② 压缩备份

​编辑

③ 打好的包远程复制到新开的服务器

​编辑

④ 新服务器下data目录改名

⑤ 将opt下的data目录移动过来

⑥ 开启mysql数据库

⑦ 查看备份的库和表

（2）mysqldump逻辑热备

① 备份一个或多个库及库中所有的表数据

② 备份所有的库

​编辑

③ 备份指定库中的所有的表数据，不包含库对象

​编辑

④ 备份指定库中的一个或多个表数据，不包含库对象

​编辑

2.增量备份策略

（1）开启二进制日志功能

​编辑

（2）通过刷新二进制日志间接实现增量备份

​编辑

（3）查看二进制日志内容

​编辑

（五）恢复方法

1.完全恢复

(1)物理恢复

① 登录数据库

② 执行 source

​编辑

(2)mysqldump 恢复

① 恢复库

​编辑

② 恢复库中的表

​编辑

2.增量恢复

（1）一般恢复

① 添加数据

② 查看日志文件

③ 删除表

④ 二进制日志增量恢复

（2）断点恢复

① 基于位置点恢复

​编辑

② 基于时间点恢复

---

一、数据库备份

（一）数据备份的重要性

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

（二）备份分类

1.物理备份

      直接对数据库的物理文件（数据文件、日志文件等）进行备份

2.逻辑备份

     对数据库的库和表对象以SQL语言的形式导出进行备份

（三）备份策略

1.完全备份

      每次备份都备份完整的库或者表数据

2.差异备份

      只备份上一次完全备份后的更新数据

3.增量备份

      每次备份只备份上一次完全备份或增量备份后的更新数据

（四）常见备份方法

1.完全备份策略

（1）物理冷备

① 关闭数据库

systemctl stop mysqld
#关闭数据库

② 压缩备份

    使用tar命令打包备份数据库的数据目录和文件 

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#用tar命令打包备份数据库的数据目录和文件 

③ 打好的包远程复制到新开的服务器

④ 新服务器下data目录改名

⑤ 将opt下的data目录移动过来

⑥ 开启mysql数据库

⑦ 查看备份的库和表

（2）mysqldump逻辑热备

① 备份一个或多个库及库中所有的表数据

mysqldump -uXXX -pXXX  --databases 库1 [库2 ....] > XXX.sql
#备份一个或多个库及库中所有的表数据

② 备份所有的库

mysqldump -uXXX -pXXX  --all-databases > XXX.sql 
#备份所有库

③ 备份指定库中的所有的表数据，不包含库对象

mysqldump -uXXX -pXXX  库名 > XXX.sql 
#备份指定库中的所有的表数据，不包含库对象

④ 备份指定库中的一个或多个表数据，不包含库对象

mysqldump -uXXX -pXXX  库名 表1 [表2 ....] > XXX.sql 
#备份指定库中的一个或多个表数据，不包含库对象

2.增量备份策略

（1）开启二进制日志功能

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

vim /etc/my.cnf
  [mysqld]
  log-bin=mysql-bin
  binlog_format = MIXED/STATEMENT/ROW
#指定二进制日志(binlog)的记录格式为 MIXED，STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
  server-id = 1
#开启二进制日志功能
systemctl start mysqld
#开启数据库
ls -l /usr/local/mysql/data/mysql-bin.*
#长格式查看文件内容

（2）通过刷新二进制日志间接实现增量备份

mysqladmin -uXXX -pXXX flush-logs

（3）查看二进制日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v <BINLOG>

（五）恢复方法

1.完全恢复

(1)物理恢复

① 登录数据库

mysql -uXXX -pXXX

② 执行 source

如果sql文件是只备份了表数据的文件，需要先创建库并 use 切换库后再执行

source XXX.sql

(2)mysqldump 恢复

① 恢复库

mysql -uXXX -pXXX < XXX.sql  

cat XXX.sql | mysql -uXXX -pXXX
#恢复库

② 恢复库中的表

mysql -uXXX -pXXX 库名 < XXX.sql   

cat XXX.sql | mysql -uXXX -pXXX 库名                    
#恢复表

2.增量恢复

（1）一般恢复

       使用二进制日志增量恢复

mysqlbinlog --no-defaults <BINLOG> | mysql -uXXX -pXXX
#使用二进制日志增量恢复

① 添加数据

② 查看日志文件

③ 删除表

④ 二进制日志增量恢复

（2）断点恢复

    如果需要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前。

    如果需要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start。

① 基于位置点恢复

mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点'  <BINLOG> | mysql -uXXX -pXXX
#基于位置点恢复

② 基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-position='YYYY-mm-dd HH:MM:SS'  <BINLOG> | mysql -uXXX -pXXX
#基于时间点恢复