MySQL备份与恢复

数据备份的重要性

• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果
• 造成数据丢失的原因
  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难（如火灾、地震）和盗窃

数据备份的分类

• 从物理与逻辑的角度，备份可分为
  • 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
    • 物理备份的方法
      • 冷备份（脱机备份）:是在关闭数据库的时候进行的
      • 热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件
      • 温备份:数据库锁定表格（不可写入但可读）的状态下进行备份操作
  • 逻辑备份:对数据库逻辑组件(如：表等数据库对象)的备份
• 从数据库的备份策略角度，备份可分为
  • 完全备份:每次对数据库进行完整的备份
  • 差异备份:备份自从上次完全备份之后被修改过的文件
  • 增量备份:只有在上次完全备份或者增量备份后修改的文件才会被备份

常见的备份方法

• 物理冷备

  • 备份时数据库处于关闭状态，直接打包数据包文件
  • 备份数据库速度快，恢复时也是最简单的

• 专用备份工具mydump或mysqlhstcopy

  • mysqldump常用的逻辑备份工具
  • mtsqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

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

• 第三方工具备份

  • 免费的MySQL热备份软件Percona XtraBackup

数据库完全备份操作

物理冷备份与恢复

​ 物理冷备份一般用tar命令直接打包数据库文件夹，而在备份之前需要使用命令关闭mysqld服务

#备份数据库
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/
[root@localhost local]# ls /backup/
mysql_2023-06-08.tar.gz
#模拟故障
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root ppwd123 -e 'drop database auth;'
[root@localhost backup]# mysql -u root ppwd123 -e 'select * from auth.users;'
#恢复数据库
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /backup/
[root@localhost backup]# tar zxvf mysql_2023-06-08.tar.gz
[root@localhost backup]# mv mysql/ usr/local/mysql.bak
root@localhost backup]# mv mysql /usr/local/
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -ppwd123 -e 'select * from auth.users;'

mysqldump备份与恢复

• 通过mysqldump命令可以将指定的库、表或全部的库导出未SQL脚本，便于该命令在不同版本的MySQL服务器上使用。
• 可以在MySQL服务启动的情况下使用

1. 备份数据库

   使用mysqldump命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合Shell的">"重定向输出操作。常用操作如下

   • 备份指定库中的指定表

     mysqldump [选项] [表名1][表名2]...>/备份路径/备份文件名
     

   • 备份一个或多个完整的库

     mysqldump [选项] --dabases 库名1[库名2]...>/备份路径/备份文件名
     

   • 备份MySQL服务器中所有的库

     mysqldump [选项] --all--dabases >/备份路径/备份文件名
     

     选项包括：

     • -u:指定要登录的用户名
     • -p:指定用户名密码

2. 查看备份文件

   通过mysqldump工具导出的SQL脚本是文本文件，其中"/…/"部分或以“–”开头的行表示注释信息。使用grep、less、cat等文本工具可以查看脚本内容

   [root@localhost ~]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
   

3. 恢复数据库

   使用mysqldump命令导出的SQL备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作，命令如下:

   mysqldump [选项] [库名][表名]</备份路径/备份文件名
   

4. mysqldump备份与恢复实例：

   #导出数据表
   [root@localhost ~]# mysqldump -u root -ppwd123 auth users > /opt/auth-users.sql    
   #导出数据库
   [root@localhost ~]# mysqldump -u root -ppwd123 auth > /opt/auth.sql      
   #备份所有库
   [root@localhost ~]# mysqldump -u root -ppwd123 --opt --all-databases >/opt/all-data.sql   
   --opt:优化执行速度\
   #导入表
   [root@localhost ~]# mysql -u root -ppwd123 auth < /opt/auth-users.sql   
   [root@localhost ~]# mysql -u root -ppwd123 -e 'select * from users;' 
   #导入库
   #如果备份了完整的数据库，可以在mysql中没有该库信息的情况下直接导入该库
   #先删除该库
   [root@localhost ~]# mysql -u root -ppwd123-e 'drop database auth;'  
   [root@localhost ~]# mysql -u root -ppwd123 -e 'show databases;' 
   [root@localhost ~]# mysql -u root -ppwd123 -e 'create database auth;' 
   [root@localhost ~]# mysql -u root -ppwd123 auth</opt/auth.sql[root@localhost ~]# mysql -u root -ppwd123 -e 'show databases;' 
   [root@localhost ~]# mysql -uroot  -ppwd123 -e 'select * from auth.users;'
   #全库还原
   [root@localhost ~]# mysql -u root -ppwd123 </opt/all-data.sql 
   

MySQL增量备份与恢复

mysql增量备份概述

1. 增量备份特点
   • 与完全备份不同，增量备份没有重复数据，备份量不大，时间段
   • 但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复
   • 而且要对所有的增量备份进行逐个反推恢复
   • mysql没有提供直接的增量备份方法，可以通过MySQL提供的二进制日志间接实现增量备份
2. MySQL二进制日志对备份的意义
   • 二进制日志保存了所有更新数据库的操作。二进制日志在启动MySQL服务器后开始记录并在文件达到二进制所设置的最大值或者接收到flush logs命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置即可完成一个时间段的增量备份
   • 使用max_binlog_size配置项可以设置二进制文件的最大值，如果二进制文件的大小超过了max_binlog_size，它就会创建新的二进制文件。

MySQL增量恢复

常用的增量恢复的方法有三种：一般恢复、基于位置的恢复、基于时间点的恢复

• 一般恢复:

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

• 基于位置的恢复:

  • 格式1:恢复数据到指定位置

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

  • 格式2：从指定的位置开始恢复数据

  mysqlbinlog  --start-position=''操作 id' 二进制文件 | mysql -u 用户名 -p 密码
  

• 基于时间点的恢复:

  • 从日志开头截止到某个时间点恢复

  mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制文件 | mysql -u 用户名 -p 密码
  

  • 从某个时间点到日志结尾的恢复

  mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制文件 | mysql -u 用户名 -p 密码
  

  • 从某个时间点到某个时间点的恢复

  mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制文件 | mysql -u 用户名 -p 密码
  

MySQL增量备份案例

#修改主配置文件
[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format = MIXED
[root@localhost ~]# systemctl restart mysqld
#生成新的日志文件
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -u root -p flush-logs
#向表添加数据（多添加一些）
[root@localhost data]# mysqladmin -u root -p flush-logs
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
#模拟误操作将auth.users表删掉
[root@localhost data]# mysql -u root -p -e 'drop table auth.users;'
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;' #常规恢复操作
#先恢复完全备份
[root@localhost data]# mysql -u root -p auth </opt/auth.sql 
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;' 
#再恢复增量备份
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'    
#模拟误操作
[root@localhost mysql]# mysql -uroot -p -e 'drop table auth.users;'
#查看没有数据
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'   
#恢复完全备份
[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql 
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p
#恢复到指定的地方
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p      ##恢复到521之前的一个数据，不包含521
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'   
#从指定的位置向后开始恢复
[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' mysql-bin.000002 | mysql -u root -p     ##从521之后开始恢复，包含521
#恢复指定范围内的数据
[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p
#恢复到指定的时间
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p
#从指定的时间开始恢复
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p
#恢复指定时间范围内的数据
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p