全备+binlog恢复

最新推荐文章于 2024-07-15 19:44:21 发布
最新推荐文章于 2024-07-15 19:44:21 发布
阅读量156 收藏
点赞数 1
分类专栏： 笔记 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51597317/article/details/117149925
版权
使用mysqldump备份所有数据库并恢复。要求保持数据库的一致性的前提下尽量减少数据库的锁，然后用binlog前滚到指定时间点模拟：周一到周日每天晚上十点都会做一个全备，某天（周四）早上十点数据库down了。问怎么恢复到最新的时间点思路：周三晚上十点之前的通过全备来恢复，周三晚上十点之后到周四早上十点之间的通过binlog恢复条件：准备两台mysql服务器，且mysql服务都在开启的状态下，注意不要是主从服务器步骤：1、准备数据：（模拟周三晚上十点之前的数据）[root@lzl
摘要由CSDN通过智能技术生成

使用mysqldump备份所有数据库并恢复。要求保持数据库的一致性的前提下尽量减少数据库的锁，然后用binlog前滚到指定时间点

模拟：周一到周日每天晚上十点都会做一个全备，某天（周四）早上十点数据库down了。问怎么恢复到最新的时间点

思路：周三晚上十点之前的通过全备来恢复，周三晚上十点之后到周四早上十点之间的通过binlog恢复

条件：准备两台mysql服务器，且mysql服务都在开启的状态下，注意不要是主从服务器

步骤：

1、准备数据：（模拟周三晚上十点之前的数据）

[root@lzl ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database cool;
Query OK, 1 row affected (0.01 sec)

mysql> use cool;
Database changed
mysql> create table y1(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into y1 values(1,'qq'),(2,'ww'),(3,'ee');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

commit：提交的意思，通常用在对数据库和表做完修改之后，将修改之后的内容更新到磁盘

2、备份数据库（在gtid开启的前提下）：（模拟每天都会做的全备）

[root@lzl tmp]# mysqldump -uroot -p -A --master-data=2 --single-transaction --set-gtid-purged=OFF >/opt/full.sql

开启gtid的方法：在/etc/my.cnf配置文件里添加以下几行内容，保存之后重启service mysql restart

server_id=8
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row

#relay log
skip-slave-start=1

3、新建一个数据库及表：（模拟数据库down了之后做的新增的数据）

mysql> create database t1;
Query OK, 1 row affected (0.00 sec)

mysql> use t1;
Database changed
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1(id,name) values(1,'php'),(2,'java'),(3,'python');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | php    |
|    2 | java   |
|    3 | python |
+------+--------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

4、删库：（模拟故障）

mysql> drop database t1;
Query OK, 1 row affected (0.04 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cool               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

5、查找binlog的起点及终点：

起点：vi /opt/full.sql
     找到  -- CHANGE MASTER TO MASTER_LOG_FILE='master-binlog.000001', MASTER_LOG_POS=154;
找起点的时候注意往下找一下有没有建库建表的命令，以保证是否备好份了

终点：

    进数据库         show master status;
     

6、截取binlog起点及终点的日志：

[root@lzl ~]# mysqlbinlog --skip-gtids --start-position=154 --stop-position=911 /data/mysql/data/master-binlog.000001 > /opt/binlog.sql

7、临时库上恢复数据到最新的时间点：

在第一台服务器上（出故障的那台）复制日志到临时服务器上：

[root@lzl ~]# scp /opt/full.sql root@192.168.60.9:/opt
The authenticity of host '192.168.60.9 (192.168.60.9)' can't be established.
ECDSA key fingerprint is SHA256:yfhFf20ZhHOC8k7Gh0XWXpnCy90l99dVH07pbH+k8zw.
ECDSA key fingerprint is MD5:05:7c:5c:52:88:29:6e:9c:61:42:00:62:92:85:94:55.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.60.9' (ECDSA) to the list of known hosts.
root@192.168.60.9's password: 
full.sql                                               100%  853KB  29.8MB/s   00:00    
[root@lzl ~]# scp /opt/binlog.sql root@192.168.60.9:/opt
root@192.168.60.9's password: 
binlog.sql                                             100% 2470     1.2MB/s   00:00    

复制之前查看：

[root@hh ~]# ls /opt/
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

复制之后查看

[root@hh ~]# ls /opt
binlog.sql  full.sql  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

进数据库执行一下刚刚的文件并查看是否恢复数据：

mysql> source /opt/full.sql;
mysql> show databases;
mysql> source /opt/binlog.sql;
mysql> show databases;

练习：

1、创建一个数据库ceshi

2、在ceshi下创建一张表t1

3、插入5行任意数据

4、全备

5、插入两行数据，任意修改3行数据，删除一行数据

6、删除所有数据

7、在t1中又插入5行新数据，修改三行数据

8、现要求跳过6恢复表数据

1、

mysql> create database ceshi;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
 

2、

mysql> use ceshi;
Database changed
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.06 sec)

3、

mysql> insert into t1 values (1,'xiaoxiao'),(2,'lijia'),(3,'yueyue'),(4,'leilei'),(5,'yuge');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

4、

[root@lzl ~]#  mysqldump -uroot -p --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full_2021-5-19.sql
Enter password: 
[root@lzl ~]# ls /opt/
full_2021-5-19.sql                          –p
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

5、

mysql> insert into ceshi.t1 values (6,'help'),(7,'type'); 
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from ceshi.t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoxiao |
|    2 | lijia    |
|    3 | yueyue   |
|    4 | leilei   |
|    5 | yuge     |
|    6 | help     |
|    7 | type     |
+------+----------+
7 rows in set (0.00 sec)

mysql> update ceshi.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update ceshi.t1 set name='bbb' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update ceshi.t1 set name='ccc' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ceshi.t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | aaa    |
|    2 | lijia  |
|    3 | bbb    |
|    4 | leilei |
|    5 | ccc    |
|    6 | help   |
|    7 | type   |
+------+--------+
7 rows in set (0.00 sec)

mysql> use ceshi
Database changed
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.01 sec)

mysql>  select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | aaa   |
|    2 | lijia |
|    3 | bbb   |
|    5 | ccc   |
|    6 | help  |
|    7 | type  |
+------+-------+
6 rows in set (0.00 sec)

6、

mysql> truncate table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1;
Empty set (0.00 sec)

7、

mysql> update t1 set name='dada' where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t1 set id=88 where name='zz';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t1 set name='xiaoxiao' where id=33;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|   11 | dada     |
|   22 | ww       |
|   33 | xiaoxiao |
|   88 | zz       |
|   55 | cc       |
+------+----------+
5 rows in set (0.00 sec)

8、

[root@lzl ~]# mysqlbinlog --start-position=154 --stop-position=2758
/data/mysql/data/master-binlog.000003 > /opt/binlog.sql
[root@lzl ~]# ls /opt/binlog.sql 
/opt/binlog.sql
[root@lzl ~]# vi /opt/binlog.sql 
[root@lzl ~]# scp /opt/full_2021-5-19.sql root@192.168.60.9:/opt/
The authenticity of host '192.168.60.9 (192.168.60.9)' can't be
established.
ECDSA key fingerprint is SHA256:yfhFf20ZhHOC8k7Gh0XWXpnCy90l99dVH07pbH
+k8zw.
ECDSA key fingerprint is
MD5:05:7c:5c:52:88:29:6e:9c:61:42:00:62:92:85:94:55.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.60.9' (ECDSA) to the list of known
hosts.
root@192.168.60.9's password: 
full_2021-5-19.sql                                     100%  854KB  
26.5MB/s   00:00    
[root@lzl ~]# scp /opt/binlog.sql root@192.168.60.9:/opt/
root@192.168.60.9's password: 
binlog.sql                                             100% 9888    
94.1KB/s   00:00

[root@hh ~]# service mysql start
Starting MySQL.. SUCCESS!
[root@hh ~]# mysql -uroot -p
Enter password:
mysql> source /opt/full_2021-5-19.sql;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use ceshi;
Database changed
mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoxiao |
|    2 | lijia    |
|    3 | yueyue   |
|    4 | leilei   |
|    5 | yuge     |
+------+----------+
5 rows in set (0.00 sec)

mysql> source /opt/binlog.sql;
mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|   11 | dada     |
|   22 | ww       |
|   33 | xiaoxiao |
|   88 | zz       |
|   55 | cc       |
+------+----------+
5 rows in set (0.00 sec)

 

 

 

 

 

 

 

 

 

h飞行指挥家
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
《童虎学习笔记》11分钟学会MySQL基于时间点的恢复(gtid方式)
tonghu_note的博客
05-01 1707
1
2021-05-21
m0_57750014的博客
05-22 127
binlog恢复 例如：周一到周日，每天晚上22:00都备份了一个全备，某一天（周四）早上10:00数据库down了，怎么恢复到最新的时间点。 思路：周三晚上22:00通过全备恢复，周三晚上22:00-周四早上10:00通过binlog恢复 准备两台mysql服务器，不要是主从服务器。 一、准备工作 1.准备数据（周三晚上22：:00前的数据） mysql> create database cool; Query OK, 1 row affected (0.00 sec) mysql&
mysqldump全备份+mysqlbinlog日志备份实现将数据恢复到指定时刻
martsforever的博客
08-20 5255
首先介绍相对简单一点的数据库全备份——mysqldump： mysqldump可以备份整个数据库，也可以是其中的数张表，一下演示备份整个数据库： 1、首先查看一下已经存在的数据库： show databases 2、创建一个新的数据库用于演示数据库全备份： create database testdump 3、切换到新创建的数据库 use testdump
mysql binlog备份恢复数据库_MySQL数据库备份还原（基于binlog的增量备份）
weixin_33900697的博客
01-21 219
MySQL数据库备份还原(基于binlog的增量备份)一、简介1、增量备份增量备份 是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份...
使用binlog把数据恢复到指定时间
chenabin_的博客
03-27 1679
我们的数据库都有在凌晨3点做数据全备份，假如我们的数据库在上午10点出了故障，那我们就可以用备份把数据还原到凌晨3点时的状态，但是3点到10点间的数据变动是没有的，如果要将数据库恢复到故障点之前，那么需要有数据库全备和全备之后产生的所有二进制日志进行还原。利用全备的备份集将数据库恢复到上一次完整备份的位置之后，需要对上一次全备之后数据库产生的所有动作进行重做，而重做的过程就是解析二进制日志文件为SQL语句，然后放到数据库里面再次执行。
Xtrabackup全备+binlog增备恢复整个数据库 江湖救急
09-13
Xtrabackup全备+binlog增备恢复整个数据库 江湖救急 Xtrabackup是Percona 开发的一款开源的MySQL备份工具，它可以对MySQL数据库进行热备、冷备和增备，支持InnoDB、MyISAM和其他存储引擎。Xtrabackup全备是指使用...
MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析
09-08
5. 执行drop database命令删除数据库，并尝试通过全备和binlog恢复。 通过这样的实验，我们可以验证mysqldump配合binlog的恢复机制是否有效。在实际应用中，确保定期备份和binlog记录完整是防止数据丢失的关键。...
Xtrbackup全备恢复单表测试
09-13
Xtrabackup全备恢复单表测试 Xtrabackup是Percona开发的一款开源的MySQL热备份工具，主要用于MySQL数据库的备份和恢复。下面是Xtrabackup全备恢复单表测试的详细步骤和知识点总结： 一、实验环境 在这个实验中，...
利用binlog进行数据库的还原
01-30
比如一个很常见的恢复场景：基于时间点的恢复，oracle通过rman工具就能够很快的实现数据库的恢复，但是mysql在进行不完全恢复的时候很大的一部分要依赖于mysqlbinlog这个工具运行binlog语句来实现，本文档介绍通过...
MySQL的全量备份和增量备份以及利用binlog日志实现恢复增量数据；
qq_44930876的博客
12-08 3414
MySQL的全量备份和增量备份以及利用binlog日志实现恢复增量数据;
mysql数据完整恢复_Mysql 通过全量备份和binlog恢复整体数据
weixin_35222747的博客
01-19 561
某天工作时间，一个二货犯晕登错生产当测试环境了，直接drop了一个数据库,需要紧急恢复!可利用备份的数据文件以及增量的 binlog 文件进行数据恢复。具体思路归纳几点：1、恢复条件为 MySQL 要开启 binlog 日志功能，并且要全备和增量的所有数据。2、恢复时建议对外停止更新，即禁止更新数据库。(这点很重要)3、先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成 SQL 文件，4、然...
hana数据库异机恢复
LIMY_hm的博客
07-18 666
HANA 数据库异机恢复
Mysql使用binlog增量备份与恢复
weixin_44032384的博客
09-27 936
背景 之前都是使用mysqldump全量备份，后来数据量大了，一个是磁盘空间不足，第二是占用cpu资源严重； 使用binlog之后，执行过程都被实时记录到了日志文件中，只需要维护一次全量备份，基于此，再加上执行记录，就可以恢复到自己想要的位置或时间节点； 查询binlog相关 -- 查看当前数据库是否开启binlog show variables like '%log_bin%'; -- 查看当前数据库binary log的位置 show master status; -- 查看binlo
Mysql之binlog日志说明及利用binlog日志恢复数据操作记录
weixin_34413357的博客
10-18 498
众所周知，binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下，我们往往会想到用binlog日志功能进行数据恢复（定时全备份+binlog日志恢复增量数据部分），化险为夷！ 废话不多说，下面是梳理的binlog日志操作解说： 一、初步了解binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有...
mysql 全备+binlog恢复数据
xueba8的博客
07-14 3167
    首先确定你的服务器已开启binlog日志，开通、查看教程请自行百度。    windows版本的mysql的binlog日志一般在data这个目录下，其中mysql-bin.index这个文件相当于索引文件，而其他的00000x则是记录了除了select查询语句以外的sql语句。    为了知道我要从哪里开始恢复，哪里结束恢复，我们需要查看mysql-bin.00000x这些文件，在mys...
MySQL & NaviCat 安装及配置教程（Windows）【安装】
绯樱殇雪的博客
07-15 837
MySQL & NaviCat 安装及配置教程
来聊一聊MySQL InnoDB的LSN
神技圈子的博客
07-15 715
详细讲解了MySQL中的LSN作用已经实现方式，这部分对于从事数据库内核开发来说非常重要
MySQL中的内置函数
qhy850716的博客
07-15 452
一、时间函数 语法 函数 说明 current_date() 日期 current_time() 时间 current_timestamp() 时间戳 now() 当前时间 date(date_time) 取出时间中的日期 date_add(date, interval value_type) 时间相加，单位year, day, minute, second date_sub(date, interval value_type)
记录些MySQL题集（3）
最新发布
安静的软件工程师
07-15 778
记录些MySQL题集（3）
mysql 5.5 全备+binlog恢复数据具体过程和代码
06-09
MySQL 5.5 的数据恢复通常包括全备份和二进制日志（binlog）恢复两个步骤。以下是具体的操作步骤和代码： 1. 全备份恢复 首先，需要将最新的全备份文件拷贝到 MySQL 数据目录下，然后执行以下命令： ``` mysql -u username -p password < /path/to/backupfile.sql ``` 其中，username 和 password 分别是 MySQL 的用户名和密码，/path/to/backupfile.sql 是全备份文件的路径和文件名。 2. 二进制日志恢复 接下来，需要将 binlog 文件依次应用到数据库中，以恢复最新的数据。执行以下命令： ``` mysqlbinlog binlog.000001 binlog.000002 binlog.000003 | mysql -u username -p password ``` 其中，binlog.000001、binlog.000002、binlog.000003 是要恢复的 binlog 文件名，可以根据实际情况修改。| 符号表示将前面的命令的输出作为后面命令的输入。 注意：在执行 binlog 恢复命令之前，需要先停止 MySQL 服务，并删除数据目录下的所有文件，然后再执行上述命令。 以上就是 MySQL 5.5 全备+binlog 恢复数据的具体步骤和代码。需要注意的是，数据恢复是非常危险的操作，建议在操作前先备份数据，以免数据丢失。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值