MySQL数据备份及恢复

CAN
334 阅读3分钟

1. 数据库备份的分类

1.1 数据备份的重要性

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

1.2 数据库备份的分类

1.2.1 从物理与逻辑的角度

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

    • 物理备份方法
    1. 冷备份(脱机备份) :是在关闭数据库的时候进行的
    2. 热备份(联机备份) :数据库处于运行状态，依赖于数据库的日志文件
    3. 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

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

1.2.2 从数据库的备份策略角度

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

image.png

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

image.png

  • 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份,缺点：恢复操作会比完全备份和差异备份复杂

image.png

1.3 常见的备份方法

1.3.1 物理冷备

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

1.3.2 专用备份工具 mysqldump 或 mysqlhotcopy

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

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

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

1.3.4 第三方工具备份

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

2. MySQL完全备份与恢复

2.1 MySQL完全备份

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

2.2 MySQL完全备份优点和缺点

  • 优点

备份与恢复操作简单方便

  • 缺点

数据存在大量的重复 占用大量的备份空间 备份与恢复时间长

2.3 数据库完全备份分类

2.3.1 物理冷备份与恢复

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

2.3.2 mysqldump备份与恢复

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

2.4 MySQL物理冷备份及恢复

2.4.1 物理冷备份

使用物理冷备份必须先关闭MySQL服务

[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar Jcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

2.4.2 数据库被误删除恢复操作

[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# mv data data.bak
[root@localhost mysql]# tar Jxf /backup/mysql_all-2022-10-26.tar.gz -C /opt/
[root@localhost ~]# mv /opt/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -pabc123
show databases;

image.png

2.5 mysqldump备份数据库

补充：在不进入mysql数据库也能对数据库进行操作

[root@localhost backup]# mysql -uroot -pabc123 -e "show databases;"

image.png

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

mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名
  • 单库备份的实例
mysqldump -u root -pabc123 can > /backup/can.sql
mysqldump -u root -pabc123 mysql > /bakcup/mysql.sql

image.png

  • mysqldump 在备份库的时候加 --databases 与不加的区别
//加--databases
[root@localhost backup]# mysqldump -u root -pabc123 --databases can > /backup/can01.sql
[root@localhost backup]# cat can01.sql | egrep -v "(^--|^/|^$)"

image.png

//不加
mysqldump -u root -pabc123 can > /backup/can02.sql
[root@localhost backup]# cat can02.sql | egrep -v "(^--|^/|^$)"

image.png

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

mysqldump -u用户名 -p [密码] [选项] --databases 库名1 [库名2]... > /备份路径/备份文件名
  • 多库备份的示例
mysqldump -u root -pabc123 --databases can mysql > /backup/databases_can_mysql.sql

image.png

2.5.3 对所有库进行完全备份

mysqldump -u 用户名-p [密码] [选项] --all-databases > /备份路径/备份文件名
  • 所有库备份的示例
mysqldump -u root -pabc123 --all-databases > /backup/all-data.sql

image.png

2.5.4 mysqldump可针对库内特定的表进行备份

  • 使用mysqldump备份表的操作
mysqldump -u 用户名 -p [密码] [选项] 数据库名表名> /备份路径/备份文件名
  • 备份表的示例
mysqldump -u root -pabc123 mysql user > /backup/mysql-user.sql

-d 只保存表结构

image.png

2.6 mysqldump完全恢复

2.6.1 恢复数据库

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

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

2.6.2 恢复数据表

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

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

3. MySQL日志管理

MySQL的日志默认保存位置为/usr/local/mysql/data

vim /etc/my.cnf

[mysqld]    
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log    #指定日志的保存位置和文件名

##通用查询日志，用来记录MySQL的所有连接和语句，默认足关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启:
log-bin=mysql-bin        #也可以log_bin=mysql-bin

##慢查询日志，用来记录所有执行时问超过long_query_time秒的语句， 可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5        #设置超过5秒执行的语句被记录，缺省时为10秒

[mysqld]

log-bin=mysql-bin

binlog_format=STATEMENT|ROW|MIXED

STATEMENT(基于SQL语句记录二进制日志，写入和恢复速度较快，在高并发的情况可能会出SQL语句顺序记录偏差导致恢复时数据丢失) 老版本的默认模式

ROW（基于行记录二进制日志，写入和恢复速度较STATEMENT慢些，日志文件占用空间也较大，但是准确性较高）5.7的默认模式

MIXED（混合模式，平时负载压力较小时使用SQL语句记录二进制日志，在高并发的情况会切换成基于行记录二进制日志）

systemctl restart mysqld
mysql -uroot -p

show variables like 'general%';  //查看通用查询日志是否开启

image.png

show variables like 'log_bin%';  //查看二进制日志是否开启

image.png

show variables like '%slow%';    //查看慢查询日功能是否开启

image.png

show variables like 'long_query_time';   //查看慢查询时间设置
set global slow_query_log=ON;    //在数据库中设置开启慢查询的方法

image.png

4. MySQL增量备份与恢复

4.1 增量备份

4.1.1 开启二进制日志功能

 vim /etc/my.cnf

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

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

mysqldump -u root -p can test01 > /opt/backup/can_test01_$(date +%F).sql
mysqldump -u root -p --databases can > /opt/backup/can_$(date +%F).sql

4.1.3 可每天进行增量备份操作，刷新二进制日志文件

mysqladmin -u root -p flush-logs

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

user can;
insert into test01 values(4,'zhangan',25,'男');
insert into test01 values(5,'lisi',18,'女');

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

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

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

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

4.2 增量恢复

  • 一般恢复

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

  • 基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

  • 基于时间点恢复

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

4.2.1 一般恢复

  • 模拟丢失更改数据的恢复
user can;
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 can.test01;' ##查看表中数据
  • 模拟丢失表中的所有的数据的恢复步骤
mysql -u root -p -e 'drop table can.test01;' ##删除整个test01表
mysql -u root -p -e 'show tables from can;'   #查看can库中的表

##先完全恢复历史数据
mysql -u root -p can < /opt/backup/can_2022.10.27.sql
##再进行增量恢复今天新增的两条数据
mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p
mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

4.2.2 断点恢复

use can;
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 /bak/mysql-bin.000003 >  /opt/backup/binlog.txt  #将二进制文件内容重定向到binlog.txt文件中
  • 基于位置的断点恢复
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 can;
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='位置点' /opt/backup/mysql-bin.000003 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据
  • 示例2

只恢复到id=7的数据

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
mysql -u root -p -e 'select * from can.test01;' ##查看表中数据
  • 示例3

恢复6-7行数据

mysqlbinlog --no-defults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p
mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

4.2.3 基于时间点的断点恢复

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

mysqlbinlog --no-defults --start-datetime='时间点' 文件名 | mysql -u root -p
##从某一个时间点开始恢复，一直到日志结尾

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

mysqlbinlog --no-defults --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='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据
  • 示例2：

只恢复到id=7的数据

mysqlbinlog --no-defults --stop-datetime='时间点' 文件名 | mysql -u root -p
mysql -u root -p -e 'select * from can.test01;' ##查看表中数据
  • 示例3：
mysqlbinlog --no-defults --start-datetime='开始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

4.3 使用脚本备份

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

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

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

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

vim a.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/a.sh  ##每周三凌晨三点进行完全备份

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

vim b.sh

#!/bin/bash
##使用tail命令打印出日志文件的最后一行内容作为name变量的值
name=$(tail -1 /usr/local/mysql/data/mysql-bin.index | awk -F'/' '{print $2}')
##生成新的二进制文件
 mysqladmin -uroot -pabc123 flush-logs
##将二进制文件移动到备份目录下 并加上日期
mv /usr/local/mysql/data/$name /opt/backup/bin_$(data_ +%F)

chmod +x b.sh
crontab -e
0 3 * * 3 /root/a.sh  ##每周三凌晨三点进行完全备份
0 3 * * * /root/b.sh  ##每天凌晨三点进行增量备份
avatar
文章
阅读
粉丝