MySQL备份与恢复

最新推荐文章于 2025-04-05 11:05:44 发布

Au杨

最新推荐文章于 2025-04-05 11:05:44 发布

阅读量281

点赞数 1

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/javaaaa123/article/details/127526558

版权

一、数据库备份基础

1.1数据备份的重要性

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

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

1.2数据库备份的分类

从物理与逻辑角度，可以分为：

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份 物理备份方法： 冷备份（脱机备份）：实在关闭数据库的时候进行的热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件温备份：数据库锁定表格（不可写入但可读）的状态下进行备份
逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

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

完全备份：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

1.3常见的备份方法

物理冷备： 备份时数据库处于关闭状态，直接打包数据库文件备份速度快，恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy ： mysqldump常用的逻辑备份工具 mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份： 进行增量备份，需要刷新二进制日志
第三方工具备份： 免费的MySQL热备份软件Percona XtraBackup

二、数据库备份方式

2.1物理冷备份及恢复

备份：

systemctl stop mysqld
mkdir /backup
tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

恢复

 mkdir bak
 mv /usr/local/mysql/data/ /bak
 mkdir restore
 tar zxf /backup/mysql_all-2022-10-26.tar.gz -C restore/
 mv restore//usr/local/mysql/data/ /usr/local/mysql/
 systemctl start mysqld

2.2mysqldump备份数据库

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

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

单库备份的示例

mysqldummp -uroot -p auth > /backup/auth.sql
mysqldummp -uroot -p mysql > /backup/mysql.sql

查看备份文件

cat yzx.sql |grep -v '^/'|grep -v '^$'|grep -v '^-'

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

mysqldump -u 用户名 -p 密码 选项 --databases 库名1 库名2 > /备份路径/备份文件名

mysqldump命令对所有数据库进行完全备份

mysqldump -u 用户名 -p 密码 选项 --all-databases > /备份路径/备份文件名

mysqldump命令针对库内特定的表进行备份

mysqldump -u root -p 选项 数据库名 表名 > /备份路径/备份文件名

恢复数据库

恢复数据表

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

mysqldump -uroot -p yzx  info1 info2 > /opt/backup/yzx_stu.sql
 
1. #删除yzx中的stu表 
mysql -u root -p -e 'drop table yzx.stu;'
mysql -u root -p -e 'show tables from yzx;'
 
2. #恢复yzx库中的表 
mysql -u root -p yzx < /opt/backup/yzx_stu.sql 
mysql -u root -p -e 'show tables from yzx;'

2.3MySQL 增量备份与恢复

开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#指定二进制日志(binlog)的记录格式为 MIXED
binlog_format = MIXED				
server-id = 1

二进制日志(binlog)有3种不同的记录格式：

STATEMENT(基于SQL语句)
ROW(基于行)
MIXED(混合模式)，默认格式是STATEMENT

#只要重启就会自动生成二进制文件
systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

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

#对指定的表做备份
mysqldump -uroot -p stu student > /opt/stu_student_$(date +%F).sql				
mysqldump -uroot -p --all-databases > /opt/data_bak_$(date +%F).sql

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

mysqladmin -u root -p flush-logs

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

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

查看二进制日志文件的内容

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

MySQL 增量恢复

1．一般恢复

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

（一）模拟丢失更改的数据的恢复步骤

use stu;
delete from student where id=3;
 
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000007 | mysql -u root -p

模拟丢失所有数据的恢复步骤

先回复整表，然后在恢复二进制日志文件

2. 基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

# 刷新生成新的二进制日志文件
mysqladmin -u root -p flush-logs 	
# 进入到data目录
cd /usr/local/mysql/data	
#查看二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000012

3）测试恢复到ID为609之前的数据，不恢复“后面一条”的数据

删除数据之前必须先刷新一下日志文件

mysqladmin -u root -p flush-logs

删除表中数据

恢复数据

mysqlbinlog --no-defaults --stop-position='581' mysql-bin.000009 | mysql -u root -p

4）仅恢复“最后一条”的数据，跳过“前面多条”的数据恢复

删除表中所有数据

基于时间恢复

测试恢复到ID为609之前的数据，不恢复“后面一条”的数据

仅恢复“最后一条”的数据，跳过“前面多条”的数据恢复

三、Mysql日志管理

MySQL 的日志默认保存位置为/usr/local/mysql/data MySQL 的日志配置文件为/etc/my.cnf，里面有个[mysqld]项。

1.1 日志的分类

1.1.1错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

vim /etc/my.cnf
log-error=/usr/local/mysql/data/mysql_error.log

1.1.2 通用查询日志

用来记录MySQL的所有连接和语句，默认是关闭的

vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

1.1.3 二进制日志

用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

vim /etc/my.cnf
#也可以 log_bin=mysql-bin
log-bin=mysql-bin

1.1.4 慢查询日志

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

1.1.5 配置日志

1. #修改my.cnf配置文件
 
#错误日志
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=5
 
2. #重新mysql服务
systemctl restart mysqld.service

1.2 日志的查询

#登入mysql
mysql -u root -p[密码]
 
#查看通用查询日志是否开启
show variables like 'general%';	
 
#查看二进制日志是否开启
show variables like 'log_bin%';									
#查看慢查询日功能是否开启
show variables like '%slow%';								
#查看慢查询时间设置
show variables like 'long_query_time';						
#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;