刘同学
文章
114
分类
11
评论
0

Mysql备份方式与热温冷备份

| 数据库 •
字数总计 30338 | 阅读时长 75分钟 | 阅读量 38

备份技术:

备份方法

备份速度

恢复速度

便捷性

功能

一般用于

cp

快

快

一般、灵活性低

很弱

少量数据备份

mysqldump

慢

慢

一般、可无视存储引擎的差异

一般

中小型数据量的备份

lvm2快照

快

快

一般、支持几乎热备、速度快

一般

中小型数据量的备份

xtrabackup

较快

较快

实现innodb热备、对存储引擎有要求

强大

较大规模的备份

https://cloud.tencent.com/developer/article/2096467

https://blog.csdn.net/qq_43108153/article/details/136790531

热备冷备:

热备份: 指的是当数据库进行备份时, 数据库的读写操作均不是受影响

温备份: 指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作

冷备份: 指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

物理与逻辑备份:

物理备份: 一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果

逻辑备份: 一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

全量备份与增量备份:

全量备份：备份数据库中的所有数据和对象。这是最基本的备份类型。

增量备份：仅备份自上次备份以来发生变化的数据。这种方式可以节省存储空间，但恢复时需要依次应用所有增量备份。

备份工具

这里我们列举出常用的几种备份工具

mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备

cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份

lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份

mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎

xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

https://cloud.tencent.com/developer/article/2096467

以上的几种解决方案分别针对于不同的场景:

  1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件

  2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果

  3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果

  4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

mysql数据库备份脚本

service crond start    //启动服务
service crond stop     //关闭服务
service crond restart  //重启服务
service crond reload   //重新载入配置
service crond status   //查看服务状态

简单版本

  1. 添加定时任务

# crontab –e

#添加环境变量
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

#再添加一条具体的任务
0 1 * * * . /etc/profile && /root/dump/backup.sh

#保存定时任务

#修改脚本执行权限
chmod +x /root/dump/backup.sh

#如果crontab未执行成功. 有可能是环境变量未设置
1.crontab的任务上增加PATH
2.执行的backup.sh中指定mysql绝对路径,
linux的crond服务不会将mysqldump的脚本在mysql安装路径bin下执行的。故需要在脚本前面手动指定mysql的bin路径，即：/usr/local/mysql/bin/mysqldump -uroot -pBroot_2020 tlj-ptmis_basic-b | gzip > /mysqlbackup/backupfiles/tlj-ptmis_basic-b_$(date +%Y%m%d_%H%M%S).sql.gz

  1. 创建备份脚本

#创建backup.sh脚本

#!/bin/bash
curday=`date +%Y%m%d%H%M%S`
backname="feiyun.sql."

filename=$backname$curday

if [ ! -f "$filename" ];then
mysqldump -u root -p123456 feiyun > $filename

else
filename="${backname}${curday}-1"
mysqldump -u root -p123456 feiyun > $filename
fi

https://cloud.tencent.com/developer/article/1524079?from=15425

电银备份脚本



#!/bin/bash
/var/www/shdy-packages/mysql-5.7.43/bin/mysqldump -u root -ppass@word111 --set-gtid-purged=OFF --databases xpos_dyb | gzip > /var/www/pro/xpos_dyb`date +%Y%m%d%H%M%S`.dump.gz
#!/bin/bash

# 数据库配置
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_db_name"
BACKUP_DIR="/path/to/backup/directory"
DATE=$(date +%Y%m%d%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/$DB_NAME-$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/$DB_NAME-$DATE.sql

# 删除7天前的备份文件
find $BACKUP_DIR -type f -mtime +7 -name "*.sql.gz" -exec rm {} \;

增量恢复

适用场景:

  • 只能基于上一次的全量数据,恢复上次时间点到本次指定时间点的binlog

  • 根据binlog日志,生成指定操作的反向sql语句.

  • 基于上一次的全量数据,恢复指定点位/时间段的binlog数据,跳过错误的sql位置.

打开binlog

修改配置文件：/etc/my.cnf
log-bin=mysql-bin
binlog_format=MIXED

#查看
SHOW VARIABLES LIKE 'log_bin';

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

重启服务

SHOW VARIABLES LIKE 'binlog_format';
将返回一个结果集，其中包含当前的binlog格式。可能的值有：

ROW： 表示使用行模式（row-based replication），这是推荐的设置，因为它提供了更好的数据一致性。

STATEMENT： 表示使用语句模式（statement-based replication），在这种模式下，可能会丢失一些数据，因为它仅记录执行的SQL语句。

MIXED： 表示混合模式（mixed-based replication），在这种模式下，MySQL会根据需要自动切换行模式和语句模式

二进制文件所在目录：

/usr/local/mysql/data

查看二进制内容命令：

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

利用binlog查询执行的sql记录

#查找binlog
> mysqlbinlog --no-defaults --start-datetime="2024-07-16 14:50:27" --stop-datetime="2024-07-16 14:50:28" --base64-output=DECODE-ROWS --verbose binlog.000034 > cjh_get_parsed_binlog_2024-07-16-17-05.sql

##--base64-output=DECODE-ROWS --verbose 
##字段命令的作用是base64解码：是因为binlog是Base64 编码的二进制数据，需要解码

如下内容:
###update  xxx.xx
###where
###  @1 = 1
##   @2 = 1623
###SET
###  @1 = 1
###  @2 = 1555

#保存日志记录
mysqlbinlog --no-defaults --start-datetime="2024-07-16 14:50:27" --stop-datetime="2024-07-16 14:50:28" --base64-output=DECODE-ROWS --verbose binlog.000034 > cjh_get_parsed_binlog_2024-07-16-17-05.sql

#根据update生成反向sql
UPDATE 数据库.表名 SET @2的字段名 = @2 WHERE @1的字段名 = @1;

刷新命令：会新增一个二进制文件

mysqladmin -u root -p flush-logs

恢复：

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003

1、从某一个点开始恢复到最后

mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p123456

2、从开头一直恢复到某个位置

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

3、从指定点开始———指定结束点

mysqlbinlog --no-defaults --start-position='位置点' --stop-position'位置点' 文件名 | mysql -u root -p

查看位置点：

at后面的数字就是位置点

选commit后面的位置点

恢复被删除的表

# 查看 binlog 已启用
SHOW VARIABLES LIKE 'log_bin';
如果返回值为 ON，则已启用。

# 查找 binlog 文件
SHOW BINARY LOGS;
# 使用 mysqlbinlog 工具读取 binlog 文件
mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 23:59:59" binlog.000001

# 查找删除表的操作
# 使用 grep 来筛选出 DROP TABLE 语句：
mysqlbinlog binlog.000001 | grep 'DROP TABLE'

# 重放删除之前的操作
#确认了删除表之前的状态后，提取出在删除之前的 CREATE TABLE 语句，然后手动重新创建该表。

# 恢复数据
# 如果在 binlog 中找到插入数据的操作，可以通过相应的 SQL 语句恢复数据。

#注意事项
#进行此操作时请确保停止对数据库的写入，以避免数据不一致。
#操作前最好备份当前数据库状态，以防万一。

闪回实战

真实的闪回场景中，最关键的是能快速筛选出真正需要回滚的SQL。

我们使用开源工具binlog2sql来进行实战演练。binlog2sql由美团点评DBA团队(上海)出品，多次在线上环境做快速回滚。

首先我们安装binlog2sql：

shell> git clone https://github.com/danfengcao/binlog2sql.git 
shell> cd binlog2sql
shell> pip install -r requirements.txt

-- 安装pip
# wget https://bootstrap.pypa.io/get-pip.py 
# python get-pip.py
# pip -V　　#查看pip版本
/usr/lib/python2.7/site-packages (python 2.7)
# pip install -r requirements.txt

背景：小明在11:44时误删了test库user表大批的数据，需要紧急回滚。

test库user表原有数据

mysql> select * from users;
+----+--------+---------------------+
| id | name   | addtime             |
+----+--------+---------------------+
|  1 | 小赵   | 2013-11-11 00:04:33 |
|  2 | 小钱   | 2014-11-11 00:04:48 |
|  3 | 小孙   | 2016-11-11 20:25:00 |
|  4 | 小李   | 2013-11-11 00:00:00 |
.........
+----+--------+---------------------+
16384 rows in set (0.04 sec)

11:44时，user表大批数据被误删除。与此同时，正常业务数据是在继续写入的

mysql> delete from users where addtime>'2014-01-01';
Query OK, 16128 rows affected (0.18 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|      261 |
+----------+

使用flashback用户执行sql

select, super/replication client, replication slave权限建议授权

mysql > GRANT SELECT ,REPLICATION SLAVE,REPLICATION CLIENT  ON *.* to flashback@ 'localhost' identified  by 'flashback';

基本用法

解析出标准SQL

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000002'  --start-datetime= '2017-01-12 18:00:00' --stop-datetime='2017-01-12 18:30:00' --start-pos=1240

解析出回滚SQL

shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

示例:

解析出标准sql:

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-datetime='2017-07-11 15:10:00' --stop-datetime='2017-07-11 15:12:00'
DELETE FROM `ttt`.`users` WHERE `uid`='0e8e2609c748bbb052d7' AND `ip`='172.16.208.32' AND `sex`=0 AND `app_ver`='5.2.3' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602129 AND `id`=1 AND `latitude`='' AND `add_time`=1481602080 AND `recharge_time`=0 AND `token_change_time`=1481602129 AND `expire_time`=0 AND `nickname`='阿超' AND `device_id`='cc0e154d9b5dd703eccc7d8a0dbc0f67d64b79e8' AND `push_key`='' AND `level`=0 AND `mobile`='18810895535' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50

解析出回滚sql

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053> /data/backup/rollback.sql
# cat /data/backup/rollback.sql 
`id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ....
INSERT INTO `ttt`.`users`(`uid`, `ip`, `sex`, `app_ver`, `device_type`, `guides`, `last_login_time`, `id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES('77e50b4910a9389057ed','172.16.218.37', 0, '5.2.1.14'...); #start 79078 end 83053 time 2017-07-11 15:11:50

解析模式

  • --realtime 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。

  • --popPk 对INSERT语句去除主键。可选。

  • -B, --flashback 生成回滚语句。可选。与realtime或popPk不能同时添加。

解析范围控制

  • --start-file 起始解析文件。必须。

  • --start-pos start-file的起始解析位置。可选。默认为start-file的起始位置；

  • --end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为realtime，此选项失效。

  • --end-pos end-file的末尾解析位置。可选。默认为end-file的最末位置；若解析模式为realtime，此选项失效。

对象过滤

  • -d, --databases 只输出目标db的sql。可选。默认为空。

  • -t, --tables 只输出目标tables的sql。可选。默认为空。

注意:

  • insert、update、delete大部分时候可以解析出来标准sql和回滚sql

一种情况例外:insert、updete、delete操作之后，drop/truncate table。 此时虽然在binlog中记录了所有的event，但是使用binlog2sql生成标准sql、回滚sql的时候已经找不到了dml操作的相应的表

  • DDL无法使用binlog2sql闪回数据。

文章作者： 刘同学
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 刘同学的小站！
数据库 oracle Mysql
喜欢就支持一下吧