MySQL数据库备份

寒_嗷呜
124 阅读7分钟

数据备份的重要性

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

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因

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

数据库备份的分类

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

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

物理备份的方法：

  • 冷备份（脱机备份）：是在关闭数据库的时候进行的
  • 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份 通过命令的形式备份

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

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

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

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

常见的备份方法

物理冷备

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

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

专业备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

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

第三方工具备份

免费的MySQL热备份软件Percona XtraBackup(最多)

MySQL完全备份

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

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

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

优点：

备份与恢复操作简单方便

缺点：

数据存在大量的重复

占用大量的备份空间

备份与恢复时间长

完全备份的分类

物理冷备份与恢复

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

mysqldump备份与恢复

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

完全备份与恢复操作

物理冷备份

完全备份

先关闭数据库，之后打包备份

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

Snipaste_2022-10-25_18-53-48.png

Snipaste_2022-10-25_18-57-36.png

备份恢复

将数据库迁移到另一台主机，测试是否恢复

#主机A，使用scp命令将tar包传给另一台主机B
scp /opt/mysql_all_2022-10-25.tar.xz 192.168.46.120:/opt
​
##主机B的操作##
systemctl stop mysqld     #关闭mysql
cd /opt/                
tar Jxf mysql_all_2022-06-05.tar.xz  #将tar包解压到opt目录
cd /usr/local/mysql              #切换数据库目录
mv data data.bak                 ##将之前空数据库移动并改名
mv /opt/usr/local/mysql/data/ /usr/local/mysql   #将data目录移动到/usr/local/mysql/目录下
 
systemctl start mysqld       #启动mysql
mysql -u root -p             #登录数据库查看

Snipaste_2022-10-25_19-04-11.png

Snipaste_2022-10-25_19-07-57.png

Snipaste_2022-10-25_19-09-41.png

mysqldump备份数据库

mysqldump完全备份

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

mysqldump -u 用户名 -p [密码][选项][数据库名] > /备份路径/备份文件名
##只保存表的结构和表数据 不保存库的命令
示例：
mysqldump -u root -p bbc > /opt/backup/bbc.sql
cd /opt/backup/
ls
vim bbc.sql  ##查看备份文件

Snipaste_2022-10-26_09-07-18.png

对一个或多个库进行完全备份

mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql#导出的就是数据库脚本文件
##保存表结构和数据 同时也保存库的命令
示例：
mysqldump -uroot -p --databases bbc mysql > /opt/backup/bbc_mysql.sql

Snipaste_2022-10-26_09-10-16.png

对所有库进行完全备份

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
​
示例：
mysqldump -u root -p --all-databases > /opt/backup/all_data.sql

Snipaste_2022-10-26_09-12-01.png

mysqldump可针对特定指定的表进行备份

 mysqldump -u root -p[密码] [-d] 库名 表名1 [表名2] ... > /备份路径/备份文件名.sql
 #使用“-d"选项，说明只保存数据库的表结构,不保存表数据
 #不使用“-d”选项，说明表数据也进行备份

示例：

mysqldump -u root -p bbc account > /opt/backup/bbc_account.sql

Snipaste_2022-10-26_09-15-15.png

查看备份文件

备份文件中保存的是sql语句。即以sql语句的形式，把库、表结构、表数据保存下来

cd /opt/backup/
cat bbc.sql | grep -v "^--" |grep -v "^/" |grep -v "^$"

Snipaste_2022-10-26_09-18-11.png

mysqldump完全恢复

恢复数据库

先删除数据库，之后进行恢复

##删除数据库yuji##
mysql -u root -p -e 'drop database bbc;'
#"-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出
​
mysql -u root -p -e 'show databases;'         #查看当前有哪些数据库
​
##恢复数据库bbc##
mysql -u root -p < /opt/backup/bbc.sql     #重定向输入库文件
​
mysql -u root -p -e 'show databases;'       #查看当前有哪些数据库

Snipaste_2022-10-26_09-37-01.png

恢复数据表

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

##备份bbc库中的account表##
mysqldump -uroot -p bbc account > /opt/backup/bbc_account.sql
​
##删除bbc库中的account表##
mysql -u root -p -e 'drop table bbc.account;'
​
mysql -u root -p -e 'show tables from bbc;'     #查看bbc库中的表，已无account表
​
##恢复bbc库中的account表##
mysql -u root -p bbc < /opt/backup/bbc_account.sql   #重定向导入备份文件，必须指定库名，且目标库必须存在
​
mysql -u root -p -e 'show tables from bbc;'

Snipaste_2022-10-26_09-45-18.png

MySQL增量备份

增量备份

1、开启二进制日志功能

 vim /etc/my.cnf
 [mysqld]
 log-bin=mysql-bin    #开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下
 binlog_format = MIXED     #可选，指定二进制日志(binlog)的记录格式为MIXED
​
 -------------- 以下是注释 -----------------
#二进制日志(binlog)有3种不同的记录格式: 
STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
 
STATEMENT (基于SQL语句)：记录修改的sql语句。高并发的情况下，记录操作的sql语句时可能顺序会有错误，导致恢复数据时，数据丢失或有误差。效率高，但数据可能有误差。
 
ROW(基于行)：记录每一行数据，准确，但恢复时效率低。
 
MIXED(混合模式)：正常情况下使用STATEMENT，高并发的情况下会智能地切换到ROW。

Snipaste_2022-10-27_10-58-25.png

Snipaste_2022-10-27_11-31-53.png

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

mysqldump -u root -p bbc class > /opt/backup/bbc_class_$(date +%F).sql
mysqldump -u root -p --databases bbc > /opt/backup/bbc_$(date +%F).sql

Snipaste_2022-10-27_11-39-11.png

3、可每天进行增量备份操作，生产新的二进制日志文件

mysqladmin -u root -p flush-logs

Snipaste_2022-10-27_11-40-53.png

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

use bbc;
insert into class values(4,'王天',25,'男');
insert into class values(5,'张琳',18,'女');

Snipaste_2022-10-27_11-54-43.png

5、再次生成新的二进制日志文件

mysqladmin -u root -p flush-logs
​
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.00003文件中

Snipaste_2022-10-27_11-56-04.png

6、备份二进制日志文件 并查看内容

mv  /usr/local/mysql/data/mysql-bin.000002 /opt/backup
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/backup/mysql-bin.000002
 
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

Snipaste_2022-10-28_09-04-24.png

增量恢复

  • 一般恢复

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

  • 基于位置恢复

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

  • 基于时间点恢复

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

一般恢复

1、模拟丢失更改数据的恢复

user bbc;
delete from class where id=4;  ##删除新增数据
delete from class where id=5;
​
##增量恢复
mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_09-06-47.png

Snipaste_2022-10-28_09-08-15.png

2、模拟丢失表中的所有的数据的恢复步骤

mysql -u root -p -e 'drop table bbc.class;' ##删除整个class表
mysql -u root -p -e 'show tables from bbc;'   #查看bbc库中的表
​
##先完全恢复历史数据
mysql -u root -p bbc < /opt/backup/bbc_class_2022-10-28.sql
##再进行增量恢复今天新增的两条数据
mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_09-14-11.png

断点恢复

use bbc;
insert into class values(6,'李一',29,'男');
insert into class values(7,'张小',19,'男');
insert into class values(8,'欧阳',49,'男');
​
mysqladmin -u root -p flush-logs        #刷新日志，生成新的二进制日志
mv /usr/local/mysql/data/mysql-bin.000003 /opt/backup/   #将前一个日志移动到/opt/backup/目录下
​
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/backup/mysql-bin.000003 >  /opt/backup/binlog.txt  #将二进制文件内容重定向到binlog.txt文件中

Snipaste_2022-10-28_09-20-53.png

Snipaste_2022-10-28_09-36-48.png

1、基于位置的断点恢复

mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
#从某一个位置点开始恢复，一直到日志结尾
​
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
#从日志开头，一直恢复到某一个位置点前结束
​
mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p
#从某一个位置点开始恢复，一直到某一个位置点前结束

示例1：

use bbc;
delete from class where id=6;
delete from class where id=7; ##删除id为6-8的数据记录
delete from class where id=8;
​
cat /opt/backup/binlog.txt ##查看二进制文件内容，找到id=6数据的位置点
​
##从6的位置点恢复数据
mysqlbinlog --no-defaults --start-position='3387' /opt/backup/mysql-bin.000003 | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_09-39-39.png

Snipaste_2022-10-28_09-46-44.png

示例2

只恢复到id=7的数据

mysqlbinlog --no-defaults --stop-position='3677' /opt/backup/mysql-bin.000003  | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_09-50-03.png

示例3

恢复6-7行数据

mysqlbinlog --no-defaults --start-position='3387' --stop-position='3967' /opt/backup/mysql-bin.000003  | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_09-53-41.png

2、基于时间点的断点恢复

注意：日期格式为：yyyy-mm-dd hh:mm:ss

mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | mysql -u root -p
##从某一个时间点开始恢复，一直到日志结尾
​
mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | mysql -u root -p
##从日志开头，一直恢复到某一个时间点前结束
​
mysqlbinlog --no-defaults --start-datetime='开始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p
#从某一个时间点开始恢复，一直到某一个位置点前结束

示例1

use bbc;
delete from class where id=6;
delete from class where id=7; ##删除id为6-8的数据记录
delete from class where id=8;
​
cat /opt/backup/binlog.txt ##查看二进制文件内容，找到id=6数据的时间点
​
##从6的时间点恢复数据
mysqlbinlog --no-defaults --start-datetime='2022-10-28 9:19:54' /opt/backup/mysql-bin.000003 | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_09-58-29.png 示例2：

只恢复到id=7的数据

mysqlbinlog --no-defaults --stop-datetime='2022-10-28 9:20:02' /opt/backup/mysql-bin.000003 | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_10-00-26.png

示例三

只恢复id=6-7数据

mysqlbinlog --no-defaults --start-datetime='2022-10-28 9:19:54' --stop-datetime='2022-10-28  9:20:10' /opt/backup/mysql-bin.000003 | mysql -u root -p
​
mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

Snipaste_2022-10-28_10-01-19.png

使用脚本备份

使用脚本每周三进行完全备份，之后每天进行增量备份

获取最新的二进制文件名称

查看索引文件 获取最新的二进制文件
tail -1 /usr/local/mysql/data/mysql-bin.index
或者
sed -n '$p' /usr/local/mysql/data/mysql-bin.index

完全备份（每周三完全备份）

vim wanquan.sh
#!/bin/bash
mysqldump -u root -p --all-databases > /bak/all_data_$(date +%F).sql
chmod +x wanquan.sh
​
crontab -e
0 3 * * 3 /root/wangquan.sh  ##每周三凌晨三点进行完全备份

增量备份文件（每天凌晨3点进行备份）

vim zengliang.sh
#!/bin/bash
##使用tail命令打印出日志文件的倒数第二行内容作为name变量的值
name=$(tail -2 /usr/local/mysql/data/mysql-bin.index | head -1 | awk -F'/' '{print $2}')
##生成新的二进制文件
mysqladmin -uroot -pabc123 flush-logs
##将二进制文件移动到备份目录下 并加上日期
mv /usr/local/mysql/data/$name /opt/backup/bin_$(data_ +%F)
chmod +x zengliang.sh
​
crontab -e
0 3 * * 3 /root/wangquan.sh  ##每周三凌晨三点进行完全备份
0 3 * * * /root/zengliang.sh  ##每天凌晨三点进行增量备份
avatar
文章
阅读
粉丝