mysql5.5的全量备份和增量备份及误删除库后的全量和增量恢复(单库场景都innodb)

最新推荐文章于 2024-10-13 21:08:42 发布
最新推荐文章于 2024-10-13 21:08:42 发布
阅读量1.3k 收藏 31
点赞数 24
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_34953582/article/details/142186738
版权

mysql5.5的全量备份和增量备份及误删除库后的全量和增量恢复(单库场景都innodb)

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频

模拟mysql主库误删除一个库的故障案例：(以多实例的一个主库3306实例为例)

如:每天0:00都是全量备份，在10:00时候，对主库操作，误删除了一个库。10:10分发现故障。

一般的当主库故障，如误操作删除了一个库，需要数据恢复的时候，最理想情况，首先数据库对外不要让用户访问了（因为数据不一致到时恢复会很麻烦）。可以考虑用iptables设置drop规则，禁止web等应用写数据库或者锁表，让主库暂时停止更新，只要求自己的客户端能连接数据库，其他都不让连接数据库。然后开始恢复数据，首先恢复全量备份（0:00之前的数据）。第二，恢复0:00到10:00之间的增量备份。做法：将该段时间的binlog日志转成sql语句，然后将sql语句里面的drop语句删除掉，然后再导入数据库。此时恢复了0:00到10:00之间的数据。10:00到10:10这段时间，因为库已经被删除掉，所以数据写不进来，也就没有新的数据，不用恢复。

注意：myisam引擎和innodb引擎的备份方式区别

myisam引擎的备份：(全量备份所有数据库为例)

#mysqldump  -u用户名  -p密码 -A -B -F --master-data=1/2 -x --events |gzip > /路径/…/all.sql.gz  #-x锁表  

innodb引擎的备份：(全量备份所有数据库为例)

#mysqldump  -u用户名  -p密码 -A -B -F --master-data=1/2 --events  --single-transaction|gzip > /路径/…/all.sql.gz  #全备数据库时，--single-transaction是锁表的，不过时间很短，该参数仅仅使用于innodb引擎。

在服务器192.168.37.128上：（双实例myslq5.5版本，以3306主库实例为例）myslq5.5版本默认存储引擎是innodb

1)主库上建立一些数据，模拟生产环境有数据

[root@localhost ~]# mysql -uroot -p123 -S /data/3306/mysql.sock

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

mysql> create database ku;

mysql> use ku;

mysql> create table biao (id int(2),name varchar(8));

mysql> insert into biao values (1,'shi1');

mysql> insert into biao values (2,'shi2');

mysql> insert into biao values (3,'shi3');

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ku                 |

| mysql              |

| performance_schema |

| test               |

+--------------------+

mysql> select * from ku.biao;

+------+------+

| id   | name |

+------+------+

|    1 | shi1 |

|    2 | shi2 |

|    3 | shi3 |

+------+------+

mysql> show create table biao\G

*************************** 1. row ***************************

       Table: biao

Create Table: CREATE TABLE `biao` (

  `id` int(2) DEFAULT NULL,

  `name` varchar(8) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> quit

2)开启binlog日志

[root@localhost ~]# vim /data/3306/my.cnf

[mysqld]

……

log-bin = /data/3306/mysql-bin              #开启binlog日志,在mysqld模块下

server-id = 1                               #主从的server-id不能一样,在mysqld模块下

wq

[root@localhost ~]# ls /data/3306

data  my.cnf  mysql  mysqld.pid  mysql_oldboy3306.err  mysql.sock

[root@localhost ~]# mysql -uroot -p123 -S /data/3306/mysql.sock -e "show variables like 'log_bin'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | OFF   |

+---------------+-------+

[root@localhost ~]# /data/3306/mysql stop

[root@localhost ~]# /data/3306/mysql start

[root@localhost ~]# ls /data/3306

data  my.cnf  mysql  mysql-bin.000001  mysql-bin.index  mysqld.pid  mysql_oldboy3306.err  mysql.sock

[root@localhost ~]# mysql -uroot -p123 -S /data/3306/mysql.sock -e "show variables like 'log_bin'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

3)模拟0:00主库上做全量备份（innodb的锁表（--single-transaction）、刷新binlog（切换）、）

[root@localhost ~]# mysqldump -uroot -p123 -S /data/3306/mysql.sock -A -B -F --master-data=1 --events --single-transaction|gzip > /opt/all.bak_$(date +%F).sql.gz

[root@localhost ~]# ls /opt/

all.bak_2017-11-25.sql.gz

[root@localhost ~]# ls /data/3306/

data  my.cnf  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysqld.pid  mysql_oldboy3306.err  mysql.sock

4)模拟0:00主库全量备份完毕后，之后时间又增量写入数据

[root@localhost ~]# mysql -uroot -p123 -S /data/3306/mysql.sock

mysql> use ku;

mysql> insert into biao values (10,'shi10');

mysql> insert into biao values (11,'shi11');

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ku                 |

| mysql              |

| performance_schema |

| test               |

+--------------------+

mysql> select * from ku.biao;

+------+-------+

| id   | name  |

+------+-------+

|    1 | shi1  |

|    2 | shi2  |

|    3 | shi3  |

|   10 | shi10 |

|   11 | shi11 |

+------+-------+

mysql> quit

5)模拟10:00的误操作，删除了库ku

[root@localhost ~]# mysql -uroot -p123 -S /data/3306/mysql.sock

mysql> drop database ku;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

mysql> quit

6)模拟10:10发现问题（通过各种途径），发现ku被删除掉了，检查全量备份文件和增量备份文件,准备恢复数据

[root@localhost ~]# cd /opt/

[root@localhost opt]# ls

all.bak_2017-11-25.sql.gz                                   #检查全量备份文件

[root@localhost opt]# gzip -d all.bak_2017-11-25.sql.gz     #解压全量备份文件

[root@localhost opt]# ls

all.bak_2017-11-25.sql

[root@localhost opt]# cd /data/3306/             #检查增量备份文件，并判断哪个新增里面是新插入数据的

data  my.cnf  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysqld.pid  mysql_oldboy3306.err  mysql.sock

[root@localhost 3306]# mysqlbinlog mysql-bin.000001 |grep -E "insert|drop"

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

[root@localhost 3306]# mysqlbinlog mysql-bin.000002 |grep -E "insert|drop"

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

insert into biao values (10,'shi10')

insert into biao values (11,'shi11')

drop database ku

7)模拟10:10之后，恢复数据（按常规情况，数据库停止对外访问时候的恢复，先设置不让用户写数据了）

a)首先立刻刷新binlog(切换)，此时如果主库有写的需求，会记录到新的binlog日志中，恢复时候新的先无需管，先将之前的binlog增量恢复，会方便点（如果停止数据库对外访问，此步骤也可不做，为了保险，做了还是好点吧。）

[root@localhost 3306]# mysqladmin -uroot -p123 -S /data/3306/mysql.sock flush-logs

[root@localhost 3306]# ls

data    mysql             mysql-bin.000002  mysql-bin.index  mysql_oldboy3306.err

my.cnf  mysql-bin.000001  mysql-bin.000003  mysqld.pid       mysql.sock

此时的增量恢复，只需恢复mysql-bin.000002，而就暂时可以不考虑新的mysql-bin.000003了

b)准备选择好的增量备份文件，转成sql语句，并删除sql语句中的drop语句,以备增量恢复使用

[root@localhost 3306]# cp mysql-bin.000002 /opt/

[root@localhost 3306]# cd /opt/

[root@localhost opt]# ls

all.bak_2017-11-25.sql  mysql-bin.000002

[root@localhost opt]# mysqlbinlog mysql-bin.000002 > zl.bin.sql    #因就一个库，可以不分库

[root@localhost opt]# ls

all.bak_2017-11-25.sql  mysql-bin.000002  zl.bin.sql

[root@localhost opt]# vim zl.bin.sql

--drop database ku    #找到该语句，删除掉，这里为了标识，其实是要删除掉的。

wq

c)恢复全量备份数据(0:00之前的全量数据恢复)

[root@localhost opt]# ls

all.bak_2017-11-25.sql  mysql-bin.000002  zl.bin.sql

[root@localhost opt]# mysql -uroot -p123 -S /data/3306/mysql.sock < all.bak_2017-11-25.sql #无需指定库

d)恢复增量备份数据(0:00到10点的增量数据恢复)

[root@localhost opt]# grep insert zl.bin.sql

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

insert into biao values (10,'shi10')

insert into biao values (11,'shi11')

[root@localhost opt]# mysql -uroot -p123 -S /data/3306/mysql.sock ku < zl.bin.sql #需指定库,因无-B的备份

8)登录进去查看数据恢复情况：全量备份和增量备份数据都恢复。

[root@localhost opt]# mysql -uroot -p123 -S /data/3306/mysql.sock

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ku                 |

| mysql              |

| performance_schema |

| test               |

+--------------------+

mysql> select * from ku.biao;

+------+-------+

| id   | name  |

+------+-------+

|    1 | shi1  |

|    2 | shi2  |

|    3 | shi3  |

|   10 | shi10 |

|   11 | shi11 |

+------+-------+

9)然后对外开放服务，允许用户写入数据。

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频

运维实战课程
关注
mysql的备份和恢复（全量备份和增量备份）
qq_34953582的博客
09-22 1564
x锁表，--single-transaction相当于锁表（仅适合innodb引擎）,-F刷新切换binlog日志,-A备份全部数据库，-B加建库切空库语句，恢复时不用指定具体库名，--master-data=1/2,备份的文件里都带binlog的位置点和binlog文件名，等于2的是加了注释，等于1的是没加注释，一般可以等于2，如果做主从复制时可以等于1/2（尽量用1），这样change master时可以不用自己找位置点。-B的另一个作用还可以备份多个库，恢复时候也不需指定库，也不需事先存在该库。
mysql5.5的全量备份和增量备份及误删除库后的全量和增量恢复(分库脚本全备，binlog拆库,都innodb)
qq_34953582的博客
09-15 756
10:00到10:10这段时间，因为库已经被删除掉，所以数据写不进来，也就没有新的数据，不用恢复。#mysqldump -u用户名 -p密码 -A -B -F --master-data=1/2 --events。#全备数据库时，--single-transaction是锁表的，不过时间很短，该参数仅仅使用于innodb引擎。#mysqldump -u用户名 -p密码 -A -B -F --master-data=1/2。如:每天0:00都是全量备份，在10:00时候，对主库操作，误删除了一个库。
mysql 误删找回_mysql 删除恢复
weixin_30230059的博客
01-21 516
一、模拟误删除数据表的恢复1 二进制日志功能启用vim /etc/my.cnf[mysqld]log-bin2 完全备份mysqldump -A -F --master-data=2 --single-transaction |gzip > /data/all.sql.gz3 修改数据表insert students (name,age)values('a',20); 修改第一次i...
Mysql数据备份与恢复实战
flytalei的博客
03-29 1110
为什么要备份？
MySQL数据库误删恢复--超详细
Linux运维老纪的博客
08-06 3882
MySQL误删数据库,造成了数据的丢失,这是非常尴尬的,但是有许多方案可以用来尝试恢复丢失的数据库,本章主要给大家介绍了关于MySQL数据库误删恢复的详细过程。
innobackupex备份mysql大数据(全量+增量）操作记录
eagle89的专栏
06-22 533
在日常的linux运维工作中，大数据量备份与还原，始终是个难点。关于mysql的备份和恢复，比较传统的是用mysqldump工具，今天这里推荐另一个备份工具innobackupex。innobackupex和mysqldump都可以对mysql进行热备份的，mysqldump对mysql的innodb的备份可以使用single-transaction参数来开启一个事务，利用innodb的mvcc来不进行锁表进行热备份，mysqldump备份是逻辑备份，备份出来的文件是sql语句，所以备份和恢复的时候很慢，但
Mysql备份系列（3）--innobackupex备份mysql大数据(全量+增量）操作记录
weixin_33854644的博客
11-25 138
  在日常的linux运维工作中，大数据量备份与还原，始终是个难点。关于mysql的备份和恢复，比较传统的是用mysqldump工具，今天这里推荐另一个备份工具innobackupex。innobackupex和mysqldump都可以对mysql进行热备份的，mysqldump对mysql的innodb的备份可以使用single-transaction参数来开启一个事务，利用innodb的mvc...
MySQL中的备份和恢复
qq_40907977的博客
06-24 1093
MySQL备份 **MySQL中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。**在MySQL中，使用mysaldump工具来完成备份。有以下3种来调用mysqldump： 备份指定的数据库，或者此数据库中某些表 shell>mysqldump [option] db_name [tables] 备份指定的一个或多个数据库 shell>mysqldump [option] --database DB1 [DB2 DB3...] 备份所有数据库 she
MySQL逻辑备份和物理备份及恢复
ygp12345的博客
08-08 1029
1.1 日志（接日志篇） binlog如何开启? log_bin binlog_format=row ****** RBR SBR sync_binlog=1 每次事务提交都立即刷写binlog到磁盘 ****** show master status ; show binlog events in '' 非GTID mysqlbinlog --start-position --stop-position --base64-o
mysql5.5的全量和增量备份及误删除库后的全量和增量恢复(单库)
07-07
MySQL 5.5 全量和增量备份及误删除库后的恢复 本文主要讲述 MySQL 5.5 的全量和增量备份及误删除库后的恢复方法，并附带了实际操作步骤和命令示例。全量备份和增量备份是 MySQL 数据库的两种备份方式，全量备份是...
mysql5.5的全量和增量备份及误删除库后的全量和增量恢复(分库)
07-07
MySQL 5.5 全量和增量备份及误删除库后的恢复 MySQL 5.5 全量和增量备份是数据库管理员必须掌握的技能之一。本文将详细介绍 MySQL 5.5 的全量和增量备份方法，以及误删除库后的恢复步骤。 MySQL 5.5 全量备份 ...
【MySQL】入门篇—数据库基础：数据库的定义与用途
thinking_chou的专栏
10-10 1486
数据库（Database）是一个有组织的数据集合，通常以电子形式存储在计算机系统中。数据库管理系统（DBMS）是用于创建、管理和操作数据库的软件。数据库可以支持多种数据类型，如文本、数字、图像等，并允许用户通过查询语言（如SQL）对数据进行操作。二、数据库的用途数据库在社会中扮演着至关重要的角色，几乎每个行业都依赖于数据库来管理和分析数据。通过高效的数据存储和管理，企业和组织能够更好地理解客户需求、优化运营流程、提高决策能力，并在竞争中保持优势。
MySQL中表的约束
最新发布
pouop的博客
10-13 841
表中一定要有各种约束，通过约束，让我们来插入数据库中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员插入正确的数据；反过来，站在MySQL的角度来单，内部已经插进来的数据，都i是符合数据约束的数据。约束的最终目标：保证数据完整性和可预期性为了完成以上的所有目标，就需要除了数据类型自带的范围约束以外的更多约束。
MySQL(B站CodeWithMosh)——2024.10.10(13)
unicorn_lemon的博客
10-10 1400
运算符：LENGTH、UPPER、LOWER、LTRIM、RTRIM、SUBSTRING、LOCATE、REPLACE、CONCAT、NOW、CURDATE、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、DAYNAME、EXTRACT、DATE_FORM、DATE_ADD、INTERVAL、IFNULL、COALESCE、IF
【MySQL】入门篇—SQL基础：SQL语言概述
thinking_chou的专栏
10-12 965
数据定义语言（DDL）用于定义数据库结构，包括创建、修改和删除数据库对象（如表、索引等）。示例：创建用户表表示创建一个名为users的表。定义了一个自增的主键列。定义了一个不允许为空的用户名列。定义了一个不允许为空且唯一的邮箱列。
MySQL数据的增删改查
2301_79985750的博客
10-09 1272
基于比较运算符和逻辑运算符，可以对复杂的条件进行过滤，从而达到要求，其中，区间查询，模糊查询以及null查询都是条件查询。限制查询结果集的条数，通过分页查询可以有效的控制一次查询出的结果集中的记录条数，可以有效的减少数据库的压力，同时对用户也比较友好。例如，查姓唐同学的成绩，%代表唐后面所有字符都被选中，类似还有%唐，%唐%，分别表示唐前面的字符被选中，唐左右的字符被选中。这是一个非常危险的操作，如果数据量太大，会消耗大量的内存和CPU资源，可能导致数据库服务器的性能下降。，特别是在多表查询的情况下。
MySQL 基本概念
weixin_44965579的博客
10-10 993
MySQL 基本概念
【MySQL】入门篇—数据库基础：数据库管理系统（DBMS）的功能
thinking_chou的专栏
10-11 768
示例：使用SQL创建一个"书籍"表该SQL语句使用数据定义语言(DDL)创建了一个名为"books"的表。表包含5个字段:book_id(主键)、title、author、publication_year和category。每个字段都定义了数据类型和约束,如主键、非空等。
MySQL 5.5误删库后全量与增量恢复策略
本文将详细介绍如何在MySQL 5.5环境中执行全量备份和增量备份，以及在误删除库后如何进行恢复，特别是在多库分库的场景下。 全量备份与增量备份： MySQL 的全量备份（也称为冷备份）是复制整个数据库的最新状态，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

运维实战课程

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值