Mysql的备份回滚

最新推荐文章于 2024-07-17 00:50:40 发布

奇怪的蘑菇

最新推荐文章于 2024-07-17 00:50:40 发布

阅读量752

点赞数

分类专栏：数据库文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/weixin_52190986/article/details/126921576

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Mysql的备份回滚

一、数据库备份的分类

数据库备份逻辑备份策略大体上可以分为以下几种:

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

差异备份：备份自从上次完全备份后被修改过的文件，下次备份备份的是相对原来的完全备份后增加啊的差异的文件而不是相对于差异备份后增加的文件

增量备份：备份相对于前一次备份后被修改的文件。

二、常见的备份方法

常见的数据库备份方法有以下几种：

物理冷备：关闭数据库然后直接打包数据库文件

专用备份工具mydump或mysqlhotcopy：

mysqldump：常用的逻辑备份工具

mysqlhotcopy：仅拥有备份MylSAM和ARCHIVE表

启用二进制日志进行备份：进行增量备份，需要刷新二进制文件

第三方工具备份：免费的MySQL热备份软件Percona XtraBackup

三、MYSQL日志管理

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

日志的种类：

错误日志：用来记录MYSQL启动、停止或运行是发生的错误信息，当然默认已开启

log-error=/usr/local/mysql/data/mysql_error.log

通用查询日志：用来记录MYSQL的所有连接和语句，默认是关闭

general_log=ON 
general_log_file=/usr/local/mysql/data/mysql_general.log

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

log-bin=mysql-bin 或 log_bin=mysql-bin

中继日志:一般情况下在Mysql主从同步(复制)，读写分离集群的从节点开启。主节点一般不需要这个日志。

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

s1ow query log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long query time=5
#设置超过5秒执行的语句被记录，缺省时为10秒

日志开启：

vim /etc/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

查询开启：

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;
#在数据库中设置开启慢查询的方法
#variables代表变量

四、备份策略

在企业中备份的合理的备份频率和备份策略是很重要的，较为合理的备份区间为：

全备：一周一次，推荐时间为晚上10点到早上的5点进行全备

增量：三天/两天/一天一次增量备份

当然我们也可以在周四晚上进行完全备份，然后一天一次增量,下周继续再做一次完全备份然后在新完全备份发基础上进行增量备份

五、Mysql的完全备份与恢复

首先我们要知道InnoDB存储引擎的数据库在磁盘上存储成三个文件分别为：db.opt(表属性文件)、表名.frm(表结构文件)、表明.ibd(表数据文件)。

物理备份

#首先我们要关闭数据库
systemctl stop mysqld
#然后简单粗暴，直接将mysql的数据目录打包压缩到指定的路径下
tar -zcvf /opt/mysql.bak.gz /usr/local/mysql/data/
#然后恢复也很简单
tar -zxvf /opt/mysql.bak.gz  -C /
#注意如果前面的绝对路径压缩的解压直接从/解压

mysqldump

备份指定完整的库/多个完整的库

mysqldump -u root -p2288 --databases 库名 >保存的地址
mysqldump -u root -p2288 --databases kgc >/opt/kgc.sql
mysqldump -uroot -p2288 --databases kgc MOGU >/opt/mogu_kgc.sql

备份所有的库

mysql -u root -p2288 --all-databases >/opt/all.sql

备份所有库但只保留表结构

mysqldump -u root -p2288 -d --databases 库名 >保存的地址

备份指定的表

mysqldump -uroot -p2288  库名 表名 > 保存地址

备份指定的表但不保存表数据

mysqldump -u root -p2288 -d 表名 >保存地址

查看备份文件

grep -v "^--" XXXX(备份文件地址) |grep -v "^/" |grep -v "^$"

Mysql完全恢复

免交互使用sql语句

mysql -uroot -p2288 -e'show databases'

免交互完全恢复

mysql -uroot -p2288 kgc </opt/kgc.sql #kgc是指定恢复的数据库名字

mysql -uroot -p2288 </opt/kgc.sql #也可以不指定直接执行，本身又建库的语句直接执行可以创建库

使用Source执行SQL脚本

source /opt/MOGU.sql #后面跟指定的绝对路径

Mysql增量备份和恢复

Mysql数据库增量恢复一般又三种分别是基于二进制文件的一般恢复，基于位置ID的位置恢复和基于时间的时间节点恢复，当然增量恢复的本质就是将二进制文件翻译成sql脚本，运行sql脚本还原之前修改数据的步骤。

二进制日志（binlog）有三种不同的记录格式：

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

二进制文件的生成：

重启服务就会刷新一个新的二进制文件
mysqladmin -u root -p flush-logs 生成二进制文件

二进制文件的编译（查看）

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000004

一般恢复

首先我们刷新一下日志此时出现最新日志04，我们写新增sql语句给ceshi表

我们刷新下二进制文件此时出现二进制表出现了新的05，但是数据并不在05中，而在上一次的04中，你第一次刷新出来的日志其实就是新建开始记录你的操作，等你再次执行二进制文件刷新时前面的操作将保存到你上一次新建的日志及新日志-1。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ns4m604K-1663499419137)(C:/Users/mogu/AppData/Roaming/Typora/typora-user-images/image-20220811161715118.png)]

我们查看下04这个二进制文件

 mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000004

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-byv32EON-1663499419138)(C:/Users/mogu/AppData/Roaming/Typora/typora-user-images/image-20220811162427543.png)]

此时我们将删除我们刚新增的数据，然后再用二进制文件恢复来测试，然后看下表是否有数据了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-71pTuRos-1663499419139)(C:/Users/mogu/AppData/Roaming/Typora/typora-user-images/image-20220811162939730.png)]

很好，没有，我们再执行下二进制文件恢复

mysqlbinlog --no-defaults /opt/mysql-bin.000004 | mysql -uroot -p

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HyTUVfQd-1663499419139)(C:/Users/mogu/AppData/Roaming/Typora/typora-user-images/image-20220811160743980.png)]

我们再次查看，测试成功

 mysql -uroot -p2288 -e 'select * from a.ceshi;'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RDb4d08J-1663499419140)(C:/Users/mogu/AppData/Roaming/Typora/typora-user-images/image-20220811163154871.png)]

基于位置恢复

普通恢复很不错，但是有的时候一个备份中有错误的语句有正确的，我们需要更精确的操作来完成，我们通过指定的at后的号作为表示来完成恢复（当然选择表示号也是要有讲究的，需要选择开始前和提交后的）。

执行220行前的sql语句

mysqlbinlog --no-defaults --stop-position='22236' mysql-bin.000012 | mysql -u root -p

执行220行后的sql语句

mysqlbinlog --no-defaults --start-position='22236' mysql-bin.000012 | mysql -u root -p

执行10行后20行前的sql语句

mysqlbinlog --no-defaults --start-position='10172' --stop-position='11208' /opt/mysql-bin.000001 | mysql -u root -p

基于时间戳恢复

和上面一样，只是位置换成了时间

mysqlbinlog --no-defaults --start-datetime='2022-08-11 17:05:55' /usr/local/mysql/data/mysql-bin.00001 |mysql -uroot -p

mysqlbinlog --no-defaults --stop-datetime='2022-08-11 17:05:55' /usr/local/mysql/data/mysql-bin.00001 |mysql -uroot -p

mysqlbinlog --no-defaults --start-datetime='2022-08-11 17:05:55'--stop-datetime='2022-08-11 17:06:0'  /usr/local/mysql/data/mysql-bin.00001 |mysql -uroot -p

faults --stop-datetime=‘2022-08-11 17:05:55’ /usr/local/mysql/data/mysql-bin.00001 |mysql -uroot -p

mysqlbinlog --no-defaults --start-datetime=‘2022-08-11 17:05:55’–stop-datetime=‘2022-08-11 17:06:0’ /usr/local/mysql/data/mysql-bin.00001 |mysql -uroot -p