MySQL 8.0.28 数据库完整备份及恢复以及binlog日志增量恢复

已于 2023-11-27 22:20:28 修改
阅读量1.4k 收藏 3
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql 服务器
于 2022-02-27 01:37:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43243110/article/details/123158804
版权

模拟环境说明：

服务器说明
192.168.37.185主库
192.168.37.186备用库
数据库开启binlog日志 /etc/my.cnf（此配置文件可直接使用）
[mysqld]
server-id=1
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data/mysql
socket=/tmp/mysql.sock
## 开启binlog 指定日志存放路径 日志文件为 mysql-bin.00000x
log-bin                       = /usr/local/mysql/data/mysql-bin
log-bin-index                  =/usr/local/mysql/data/mysql-bin.index

relay-log                      = mysql-relay-bin
skip-name-resolve
back_log=1000
## 忽略大小写
lower-case-table-names=1
max_connections=1000

备份命令以及参数说明

 [root@bogon /]# mysqldump -uroot -proot --single-transaction --flush-logs --master-data=2 --databases testdb --events --routines --triggers > /testdb.sql
参数说明
–single-transaction使用一致的读取并保证mysqldump开始后看到的数据不会更改
–flush-logs创建一个新的binlog日志文件
–master-data=2将二进制日志信息写入备份文件
–databases指定备份数据库 如果没有声明这个参数 备份文件将不包含 CREATE DATABASE 或 USE 语句
testdb备份的数据库名称
–events导出事件 mysqldump 默认不携带此参数
–routines导出存储过程和函数 mysqldump 默认不携带此参数
–triggers导出触发器 mysqldump 默认携带此参数
–no-data只备份表定义 (此文章内备份未使用)
–no-create-info只备份数据 (此文章内备份未使用)
> /testdb.sql将备份文件输出到根目录

binlog日志相关的操作命令


查看binlog日志文件
[root@bogon /]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000002 | more

指定时间范围 指定binlog日志文件 是否包含某项操作
[root@bogon /]# mysqlbinlog --start-datetime="2022-02-26 20:50:00" --stop-datetime="2022-02-26 23:55:00" --verbose /usr/local/mysql/data/mysql-bin.000002 | grep -C 15 "DROP TABLE"

指定指定binlog日志事件开始和结束位置 指定binlog日志文件 恢复binlog日志
[root@bogon /]# mysqlbinlog --start-position=157 --stop-position=543 /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -proot

批量执行binlog日志 
[root@bogon /]# mysqlbinlog  mysql-bin.000003  mysql-bin.000004 |mysql -uroot -proot

机器：192.168.37.185（主库）

  1. 创建测试数据库
mysql> create database testdb;
mysql> use testdb;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> INSERT INTO pet VALUES ('Fluffy','Harold','cat','f','1993-02-04',NULL);
mysql> INSERT INTO pet VALUES ('Claws','Gwen','cat','m','1994-03-17',NULL);
mysql> INSERT INTO pet VALUES ('Buffy','Harold','dog','f','1989-05-13',	NULL);
mysql> INSERT INTO pet VALUES ('Fang','Benny','dog','m','1990-08-27',NULL);
mysql> INSERT INTO pet VALUES ('Bowser','Diane','dog','m','1979-08-31','1995-07-29');	
mysql> INSERT INTO pet VALUES ('Chirpy','Gwen','bird','f','1998-09-11',NULL);	
mysql> INSERT INTO pet VALUES ('Whistler','Gwen','bird','','1997-12-09',NULL);
mysql> INSERT INTO pet VALUES ('Slim','Benny','snake','m','1996-04-29',NULL);
-- 存储过程
mysql> delimiter $ 
CREATE PROCEDURE getpetcountbyowner (IN petowner varchar(20), OUT petcounts INT)
     BEGIN
         SELECT COUNT(*) INTO petcounts FROM testdb.pet
         WHERE owner = petowner;
	END 
$
-- 函数
mysql> delimiter $ 
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
       RETURN CONCAT('Hello, ',s,'!');
$
-- 视图 		 
mysql> delimiter $ 
CREATE VIEW testdb.petview AS SELECT * FROM testdb.pet;
$

-- 事件
mysql> delimiter $ 
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE testdb.pet SET birth = birth + INTERVAL 1 DAY;
	$		
-- 触发器			
mysql> delimiter $
CREATE TRIGGER birthadd BEFORE INSERT ON pet
       FOR EACH ROW SET NEW.birth = INTERVAL 1 DAY + NEW.birth;
       $
  1. 数据库备份
如果没有配置mysql的环境变量,  备份方式为   /mysql安装路径/bin/mysqldump ...
[root@bogon /]# mysqldump -uroot -proot --single-transaction --flush-logs --master-data=2 --databases testdb --events --routines --triggers > /testdb.sql
mysql> show master status; --此信息会写入备份文件内
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
将备份文件传到备用库服务器上
[root@bogon /]# scp /testdb.sql root@192.168.37.186:/

切换机器：192.168.37.186（备用库）

mysql> source testdb.sql
mysql> use testdb;
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    |      | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

上述步骤完成之后，testdb的完整备份已在 192.168.37.186 恢复完成。接下来根据binlog日志进行增量恢复
(例如 ：我们在2022-12-01 23:59:59 进行了完整备份 。在2022-12-02 12:59:59数据库出现了故障，我们通过2022-12-01 23:59:59 的完整备份恢复了数据。而 2022-12-01 23:59:59 到 2022-12-02 12:59:59 的数据库操作我们需要通过binlog日志进行恢复

切换机器：192.168.37.185（主库） 模拟数据删除

mysql> use testdb;
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    |      | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

mysql> delete from pet where name ='Slim';

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    |      | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+
mysql> exit

由于我们在备份时使用了 --flush-logs --master-data=2 所以通过查看备份文件是可以知道备份时新开启的binlog日志文件是什么

[root@bogon /]# more /testdb.sql

在这里插入图片描述
进入binlog日志目录，将备份文件内 master_log_file 指定的binlog日志以及之后生成的日志发送到 备用库

[root@bogon /]# cd /usr/local/mysql/data
[root@bogon /]# ls
mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003 mysql-bin.000004
[root@bogon /]# scp mysql-bin.000003 mysql-bin.000004 root@192.168.37.186:/

切换机器：192.168.37.186 进行binlog日志恢复

[root@bogon /]# mysqlbinlog  mysql-bin.000003  mysql-bin.000004 |mysql -uroot -proot

mysql> use testdb;
mysql> select * from pet; -- 可以看到我们模拟删除的数据已经不见了
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    |      | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+

至此 整个数据库已从 192.168.37.185 迁移至 192.168.37.186

参考 MySQL官网文档 - 备份以及恢复 章节

猿途豆子
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
MySQL数据库的binlog日志是数据安全的重要保障，它记录了所有的DDL（Data Definition Language，如CREATE、ALTER、DROP等）和DML（Data Manipulation Language，如INSERT、UPDATE、DELETE等）语句，但不包括SELECT...
浅谈MySQL数据库的备份与恢复
09-09
MySQL数据库的备份与恢复是数据库管理系统中至关重要的环节，它涉及到数据安全性、系统稳定性和灾难恢复能力。在本文中，我们将深入探讨MySQL的几种备份方式、物理数据文件结构以及如何使用`mysqldump`进行备份和...
【MySQL篇】使用mysqldump全量+mysqlbinlog增量完成实例的全库恢复（第四篇，总共四篇）
最新发布
奈斯DBA的博客
04-29 1863
单纯的mysqldump逻辑备份只能将MySQL实例恢复到备份文件所对应的时间点，所以MySQL在备份时必须同时保留二进制日志，以便我们能够使用mysqlbinlog工具追溯并恢复备份之间的数据变更。然后使用mysqldump全量+mysqlbinlog增量完成实例的全库恢复。
MySQL8.0中binlog的深入讲解
01-21
1 简介 二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制形式保存在磁盘。 2 Binlog 的作用 主要作用：复制、恢复和审计。 3 开启Binlog 3.1 查询当前 MySQL 是否支持 binlog 如下OFF代表不支持 3.2 修改 my.cnf 文件以支持 binlog 查看my.cnf路径 mysql –help –verbose | grep my.cnf     在/etc 新建文件my.cnf并添加如下内容 注意添加 mysqld 组   重启 MySQL 再次查看是否支持binlog   3 binlog管理命令
常见数据库备份方式：MySQLbinlog数据恢复详解
2301_82242670的博客
04-21 895
Java面试核心知识点一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！Java面试核心知识点《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！” />[外链图片转存中…(img-ng4M7bvl-1713664779566)]Java面试核心知识点一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！[外链图片转存中…(img-hVieKLoD-1713664779566)]
mysql 利用binlog增量备份，还原实例
linfengfeiye的专栏
12-14 920
mysql 利用binlog增量备份，还原实例
测试mysql8.0通过binlog恢复数据
ajax_yan的博客
09-16 545
首先在装数据库时，查看是否开启，如果没有，先开启，然后重启mysql。环境变量Path添加mysql安装路径:.../mysqlxx/bin/ 不要等到用的时候再开启，晚了 show variables like '%log_bin%' 指定时间范围，去查找符合条件的日志 mysqlbinlog -v binlog.000001 --start-datetime="2020-10-24 00:00:00" --stop-datetime="2020-10-24 00:00:00" ..
mysql8.0 使用binlog回复数据库
凌晨的博客
12-03 681
1找到mysqlbinlog命令 一般在mysql/bin文件夹下 2找到当前的需要恢复的日志show binary logs 3 把二进制日志 转换成能读取的日志 开始寻找数据回复的 开始点 和 结束点 /www/server/mysql/bin/mysqlbinlog /www/server/data/mysql-bin.000004 >> /www/1.txt 4进行回复步骤 /www/server/mysql/bin/mysqlbinlog /www/server...
Mysql8.0的bin log日志
阿干tkl的博客
07-12 1858
mysql bin log 日志
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
08-04
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
MySQL数据库和MySQL管理、备份与恢复
12-14
这包括如何使用mysql命令行工具来恢复数据库，以及在灾难性情况下如何利用binlog进行点-in-time恢复。 4. 备份策略：讨论制定合适备份策略的重要性，包括定期备份频率、备份保留时间等，以适应不同的业务需求和风险...
教你自动恢复MySQL数据库的日志文件(binlog)
09-10
如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始
mysql 备份binlog,mysql binlog 日记备份
weixin_39978276的博客
03-17 156
mysql binlog 日志备份【基本介绍】日志是把数据库的每一个变化都记录到一个文件里面(日志文件)。MySQL默认只打开出错日志，因为过多的日志会影响系统的处理性能。5.0前支持文本和二进制格式，5.0后只支持二进制格式，因为二进制日志在性能、信息处理方面有更多的优点。【配置】在my.cnf里面配置binlog相关#binloglog-bin = mysql-bin #这些路径相对于data...
mysql bin日志备份_mysql binlog 日志备份
weixin_34419203的博客
02-01 126
#binloglog-bin = mysql-bin #这些路径相对于datadirmax_binlog_size #binlog的最大默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。log-bin-index = mysql-b...
18，MySQL 8.0参考手册4.6.8.3使用mysqlbinlog备份二进制日志文件
dandan520520的博客
03-26 341
默认情况下，mysqlbinlog读取二进制日志文件并以文本格式显示其内容。这使您可以更轻松地检查文件中的事件并重新执行它们（例如，通过将输出用作mysql的输入）。mysqlbinlog可以直接从本地文件系统读取日志文件，或者使用该 --read-from-remote-server 选项可以连接到服务器并从该服务器请求二进制日志内容。mysqlbinlog会将文本输出写入其标准输出，或者 如果...
mysql bin日志备份_mysql之binlog日志备份还原
weixin_39631572的博客
02-01 135
实例:在mysql有时执行了错误的update或者delete时导致大量数据错误恢复的办法。执行时没有开启事务，也没有对数据进行。这时就需要使用到sqlbinlog工具。sqlbinlog需要开启，具体的打开方法就不说了。使用sqlbinlog会产生bin文件，恢复就需要用到这些文件。文件中记录着数据库的所有操作。(此方法的操作是将数据库之前所执行的语句重新执行一次，以达到恢复效果)具体步骤：(注...
mysql8 配置日志文件,mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤详解
weixin_36248959的博客
03-17 452
第一步 安装1.安装MySQL2.安装Python33.下载binlog2sql文件到本地(文件在百度云盘)4.修改binlog2sql中的requirements.txt，把PyMySQL==0.7.11改为0.9.3,保存退出5.安装和检查，确保是0.9.3 不然出错第二步 准备MySQL数据1.配置文件最好加入安全目录secure-file-priv=/test，重启MySQL注：每个人都配...
MySQL 8.0.11 binlog文件查看
vkingnew 的技术博客
07-23 5634
运行环境：MySQL8.0.11+centos7.4 1.查看binlog文件的列表： 方法1：查看binlog的日志文件： # cat node1_bin.index /home/mysql/node1_bin.000001 方法2：通过MySQL的命令查看： mysql> show binary logs; +------------------+-----------+ | Log...
MySQL备份集binlog位置_mysql 备份 和binlog日志还原
weixin_28729205的博客
01-19 362
1、开启binlog日志vi /etc/my.cnfdatadir=/var/lib/mysql //默认的数据位置 默认binlog日志的位置server-id = 1binlog_format=STATEMENT #这个是binlog日志的模式这个模式可以在binlog日志里面显示出inser|update|delete语句log-bin=mysql-bin #binlog日志名子的前缀exp...
mysql如果进行增量备份及快速恢复
05-31
MySQL进行增量备份的常用方法是使用binlog日志。binlog日志记录了MySQL中所有的数据修改操作，包括INSERT、UPDATE和DELETE等操作，可以通过将binlog日志备份到另一台服务器上，来实现增量备份。 具体的步骤如下： ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值