Mysql数据备份与恢复

一只余
268 阅读8分钟
  • 一 . 数据库备份的分类
  • 二 . MySQL完全备份与恢复
  • 四 . MySQL增显备份与恢复
  • 五 . MySQL备份恢复案例

一 . 数据库备份的分类

1. 数据备份的重要性

1.1备份的主要目的是:灾难恢复

1.2 在生产环境中，数据的安全性至关重要任何数据的丢失都可能产生严重的后果造成数据丢失的原因

  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难(如火灾、地震）和盗窃

2 .数据库备份的方式

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

2.1物理备份:

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

物理备份方法

  • 冷备份(脱机备份)︰是在关闭数据库的时候进行的

  • 热备份(联机备份)∶数据库处于运行状态，依赖于数据库的日志文件

  • 温备份:数据库锁定表格(不可写入但可读的状态下进行备份

2.2操作逻辑备份:

对数据库逻辑组件(如:表等数据库对象)的备份

3 . 数据库备份的策略

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

3.1完全备份:

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

3.2 差异备份:

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

3.3增量备份:

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

image.png

数据库备份怎么备份？

每周3晚上的完全备份，每周每天5点增量备份。

4.常见的备份方法

4.1物理冷备

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

4.2专用备份工具mysqldump或mysqlhotcopy

  • mysqldump常用的逻辑备份工具()

  • mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

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

4.4 第三方工具备份

  • 免费的MySQL热备份软件Percona XtraBackup(常用)

二 . MySQL完全备份介绍

1 . 什么是完全备份

  • 完全备份是对整个数据库、数据库结构和文件结构的备份
  • 保存的是备份完成时刻的数据库
  • 是差异备份与增量备份的基础

2 . MySQL完全备份与恢复优缺点

优点

  • 备份与恢复操作简单方便

缺点

  • 数据存在大量的重复

  • 占用大量的备份空间

  • 备份与恢复时间长

3 . 数据库完全备份分类

物理冷备份与恢复

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

mysqldump备份与恢复

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

三 . 完全备份与恢复 操作演示

1. 物理冷备份

补充时间提取

image.png

1.1 完全备份（主机7-7）

    systemctl stop mysqld         #先关闭服务
    mkdir /backup/                #创建备份目录
     ​
    rpm -q xz                     #使用xz工具进行压缩，检查xz工具是否已安装
     yum install xz -y             #如果没安装，可以先yum安装
     tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data  #打包数据库文件。/usr/local/mysql/data 为数据库文件存放目录
     ​
    cd /backup/        #切换到备份目录 
    ls                 #查看目录内容
    tar tf mysql_all_2022-06-05.tar.xz    #查看tar包内的文件

image.png

🍗

image.png

🥐

image.png

🥫

image.png

🥞

image.png

1.2完全恢复(主机7-1)

将数据库迁移到另一台主机，测试完全恢复。

image.png

7-7主机
scp mysql_full_20230917.tar.gz 192.168.47.100:/opt
#传输数据包

image.png

7-1主机
 cd /opt  #查看压缩包传输情况
systemctl stop mysqld 
#关闭服务

tar xf mysql_full_20230917.tar.gz 
解压压缩包

cd /usr/local/mysql/
#去这个目录下将原有的data改名data.bak做备份
[root@localhost mysql]# mv data/ data.bak


[root@localhost opt]# cp -a  data/ /usr/local/mysql/data

#将data 复制保留属性 到当前目录

ls #查看data是否复制过来了
ls data/yhy #是我需要回复的表

image.png

🍔

image.png

开启mysql服务

image.png

解决方法

image.png

mysql开启成功 成功完成备份和恢复

image.png

image.png

四.逻辑热备份（使用mysqldump工具）

  • 逻辑热备份的方式实现完全备份。

  • mysqldump是常用的逻辑备份工具。

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

命令·

mysqldump命令对单个库进行完全备份

mysqldump -u用户名 -p[密码][选项][数据库名] > /备份路径/备份文件名

单库备份的示例

mysqldump -u root -p auth > /backup/auth.sql

mysqldump -u root -p mysql > /backup/mysql.sql

1 mysqldump逻辑热备份

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

image.png

 mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql#导出的就是数据库脚本文件
 ​
 示例:
 mysqldump -u root -p --databases yhy > /opt/mysql_bak/yhy.sql   
 #完全备份一个库yhy

mysqldump -u root -p --databases yhy market > /opt/mysql_bak/yhy-market.sql   
#完全备份多个库，yhy库和market库

完全备份一个库和库表里的内容（+databases）

image.png

mysqldump -uroot -p123123 yhy > /opt/backup/yhy.sql
#这个命令备份的虽然名字是库名，但是备份的是库里面表的内容

image.png

cat yhy.sql |egrep -v "^--|^/\*|^$"
过滤出备份内容

image.png

2）完全备份MySQL服务器中所有的库（包括库中所有的表）

 mysqldump -u root -p[密码] --all-databases > / 备份路径/备份文件名.sql
 ​
 示例:
 mysqldump -u root -p --all-databases > /opt/mysql_bak/all.sql   #完全备份所有的库


mysqldump -uroot -p123123 --all-databases > /opt/backup/all_data_full.sql

操作

mysqldump -uroot -p123123 --all-databases > /opt/backup/all_data_full.sql

image.png

查看

cat all_data_full.sql  |grep "^CREATE DATABASE"

image.png

新建多个库验证

image.png

操作查看

mysqldump -uroot -p123123 --all-databases > /opt/backup/all_data_full.sql

cat all_data_full.sql  |grep "^CREATE DATABASE"

image.png

2 mysqldump完全恢复

先启动mysql

systemctl start mysqld

1）恢复数据库

使用mysqldump导出的脚本，可使用导入的方法

  • source命令
  • mysql命令

使用source恢复数据库的步骤

  • 登录到mysql数据库
  • 执行source备份sql脚本的路径

source恢复的示例 MySQL[(none)]>source /backup/all-data.sql

先删除数据库，之后进行恢复，模拟环境

删除yhy库

image.png

2.1 恢复数据库source

方法1：

mysql> source /opt/backup/yhy_full.sql

mysql> show databases;

image.png

🧆

image.png

🥫

image.png

方法2：

image.png

create database yhy;
use yhy;
source /opt/backup/yhy.sql
yhy.sql文件中不包含创建库的操作，只需要建立一个库即可，就能使用

image.png

🥟

image.png

方法3：

在别的库（bdqn）中恢复需要恢复的库（yhy）

show databases;
use bdqn;
show tables;
source /opt/backup/yhy.sql

image.png

查看

image.png

2.2 恢复数据库mysql

使用mysql命令恢复数据

mysql -u 用户名 -p密码 < 库备份脚本的路径

注：

mysql -uroot -p123123 -e "show databases;"
#在linux命令行中操作sql语句

image.png

 mysql -uroot -p123123 -e "show databases;"

image.png

恢复方法1

mysql -uroot -p123123 < ./yhy_full.sql 
恢复库和数据
 mysql -uroot -p123123 -e "show databases;"
 查看库

image.png

恢复方法2

mysql -uroot -p123123 -e "drop database yhy;
删除库
mysql -uroot -p123123 yhy < ./yhy_full.sql
#恢复数据库

image.png

 mysql -uroot -p123123 -e "select * from yhy;"

image.png

上面是重定向

下面方法是通过管道符

方法3:

mysql -uroot -p123123 -e "drop database yhy;"
#删库
cat yhy_full.sql  |mysql -uroot -p123123
#恢复数据库

image.png

2.3 完全恢复总结

先登录到mysql，再执行 source XXX.sql（注：如果sql文件只包含表的备份，需要先创建库并use切换库）

  • mysql -uXXX -pXXX [库名] < XXX.sql #如果sql文件只包含表的备份，要指定库名
  • cat XXX.sql | mysql -uXXX -pXXX [库名]

五 . MySQL增量备份

1. mysql增量备份

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

  • 备份数据中有重复数据
  • 备份时间与恢复时间过长

增量备份是什么：

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

增量备份的特点

  • 没有重复数据，备份量不大，时间短
  • 恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复，而且要对所有增量备份进行逐个反推恢复

1.1 mysql增量备份的方法

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

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

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

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

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

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

1.2 mysql数据库增量恢复

  • 一般恢复

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

  • 基于位置恢复

    • 数据库在某一时间点可能既有错误的操作也有正确的操作
    • 可以基于精准的位置跳过错误的操作

  • 基于时间点恢复

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

1.3 MySQL 日志管理

vim /etc/my.cnf 
[mysqld]  

#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log-bin=mysql-bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=1 #慢查询时间

systemctl restart mysqld
#重启服务

mysql工作目录data位置：/usr/local/mysql/data/

image.png

show variables like  'general%';
#查看通用查询日志是否开启
show variables like  'log_bin%';
#查看二进制日志是否开启
show variables like  '%slow%';
#查看慢查询日志功能是否开启
show variables like 'long_query_time';
#查看慢查询

image.png

六. MySQL增量备份与恢复实操

1.先开启mysql的二进制日志功能

vim /etc/my.cnf 
[mysqld]  
log-bin=mysql-bin   
#开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下  
binlog_format = MIXED     
#可选，指定二进制日志(binlog)的记录格式为MIXED

image.png

systemctl restart mysqld

image.png

2.先创建我们实验需要的数据表

image.png

创建目录存储备份文件

image.png

实验案列：

crontab -e #编写计划任务

  • 0 2 * * 2 完全备份脚本.sh
  • 0 2 * * * 增量备份脚本.sh

具体操作：

完全备份

mysqldump -uroot -p123123 yhy yhy1 > /root/mysql-backup/full/yhy_yhy1-$(date +%Y%m%d).sql
#完全备份

image.png

mysqladmin -uroot -p123123 flush-logs
#刷新

image.png

image.png

模拟星期2的操作

mysql> insert into yhy1 values (5,'dhj',22,'男','cf');
Query OK, 1 row affected (0.00 sec)

mysql> insert into yhy1 values (6,'lyj',22,'女','hz');
Query OK, 1 row affected (0.00 sec)

mysql> insert into yhy1 values (7,'lj',22,'女','画画');
Query OK, 1 row affected (0.00 sec)

image.png

增量备份

mysqladmin -uroot -p123123 flush-logs

image.png

mv -f mysql-bin.000006 /root/mysql-backup/bin/mysql-bin.000006-$(date -d '1 day' +%Y%m%d)

image.png

image.png

星期3

mysql> mysql> insert into yhy1 values (8,'ljj',20,'女','nn');
Query OK, 1 row affected (0.00 sec)

mysql> insert into yhy1 values (9,'lyy',20,'女','mn');
Query OK, 1 row affected (0.00 sec)

mysql> insert into yhy1 values (10,'yyy',20,'女','wo');
Query OK, 1 row affected (0.03 sec)

image.png

image.png

image.png

查看备份

image.png

恢复

image.png

create database yhy；
#先恢复库

quit
#退出

image.png

恢复完全备份

image.png

查看

image.png

恢复增量备份数据1
mysqlbinlog --no-defaults /root/mysql-backup/bin/mysql-bin.000006-20230920 |mysql -uroot -p123123

image.png

恢复增量备份数据2
 mysqlbinlog --no-defaults /root/mysql-backup/bin/mysql-bin.000007-20230921 |mysql -uroot -p123123
只恢复需要恢复的行

image.png

avatar
文章
阅读
粉丝