mysql备份和恢复机制-mysqldump+binlog备份与恢复

最新推荐文章于 2024-10-11 15:31:09 发布
最新推荐文章于 2024-10-11 15:31:09 发布
阅读量1.5k 收藏 2
点赞数 1
分类专栏： linux 运维 mysql和vsftp 文章标签： 数据库 mysql db2 lvm 存储
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sqj155/article/details/79600790
版权
Mysql备份方案分为以下三种
1.mysqldump+binlog:(推荐)
完全备份，通过备份二进制日志实现增量备份
2.xtrabckup:
对InnoDB:热备，支持完全备份和增量备份
对MyISAM:温备，支持完全备份
3.lvm2快照+binlog:几乎热备，物理备份

mysql备份工具

mysqldump:适用于所有存储引擎，适用于温备，能实现完全备份，部分备份；对InnoDB存储支持热备；
cp,tar等文件系统工具：物理备份，适用于所有引擎；用于冷备，能实现完全备份，部分备份；
lvm2的快照：几乎热备；借助于文件系统工具实现物理备份；
mysqlhotcopy:几乎热备；仅适用于 MyISAM存储引擎；但事实上mysqlhotcopy只支持冷备
先来介绍一下mysqldump+binlog的语法格式 

mysqldump [options] database [tables]:备份单个库，或库指定的一个或多个表
mysqldump [options] --database [options] DB1 [DB2 DB3...]: 备份一个或多个库
mysqldump [options] --all-databases [options] :备份所有
实验：使用mysqldump+binlog备份与恢复

1、准备备份目录 
[root@@mysql-master~]#mkdir -pv /backup/binlog
mkdir: created directory ‘/backup’
mkdir: created directory ‘/backup/binlog’
[root@@mysql-master~]#chown -R  mysql.mysql /backup/
2、准备备份数据库及表 
MariaDB [(none)]> create database class;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use class;
Database changed
MariaDB [class]> create table tb(id int);
Query OK, 0 rows affected (0.10 sec)
3、进行完整备份 
mysqldump -p123456 --all-databases --lock-all-tables --flush-log --master-data=2 > /backup/`date +%F_%T`-all.sql;  
说明：这里的密码是之前装mariadb的时候设置的，虽然现在是重新装的mariadb但是只要你装过mariadb，你对它所有的设置都在
/var/lib/mysql下有记录，当时对数据库设置了密码，这个密码就存放在/var/lib/mysql/mysql下的user表中；如果你现在不想要密码了
你就把/var/lib/mysql下的大小全部删除，当你再次重启mariadb时这个目录下就会自动生成mariadb所需要的文件，不需要的就没有了，是
不是很神奇没有密码在完整备份的时候就是mysqldump  --all-databases --lock-all-tables --flush-log --master-data=2 >
 /backup/`date +%F_%T`-all.sql;
4、向表中插入数据 
MariaDB [(none)]> show master status\G;
MariaDB [class]> create table tb1(id int);
Query OK, 0 rows affected (0.29 sec)

MariaDB [class]> insert into tb1 values (10),(20),(30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [class]> select * from tb1;
+------+
| id   |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)

MariaDB [class]>
5、进行增量备份，备份二进制日志
先查看一下二进制日志文件中的position位置，以便在进行增量恢复的时候指定start position和stop position的位置 
[root@mysql-master /var/lib/mysql]#ls
aria_log.00000001  class    ib_logfile0  mysql             mysql-bin.000002  mysql-bin.000004  mysql.sock          test
aria_log_control   ibdata1  ib_logfile1  mysql-bin.000001  mysql-bin.000003  mysql-bin.index   performance_schema
[root@mysql-master /var/lib/mysql]#mysqlbinlog mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171226 21:27:16 server id 73  end_log_pos 245 	Start: binlog v 4, server v 5.5.52-MariaDB created 171226 21:27:16
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
NE5CWg9JAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAYE0+vw==
'/*!*/;
# at 245
#171226 21:30:15 server id 73  end_log_pos 333 Query	thread_id=18	exec_time=0	error_code=0
use `class`/*!*/;
SET TIMESTAMP=1514295015/*!*/;
SET @@session.pseudo_thread_id=18/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table tb1(id int)
/*!*/;
# at 333
#171226 21:30:18 server id 73  end_log_pos 402 	Query	thread_id=18	exec_time=0	error_code=0
SET TIMESTAMP=1514295018/*!*/;
BEGIN
/*!*/;
# at 402
#171226 21:30:18 server id 73  end_log_pos 503 	Query	thread_id=18	exec_time=0	error_code=0
SET TIMESTAMP=1514295018/*!*/;
insert into tb1 values (10),(20),(30)
/*!*/;
# at 503
#171226 21:30:18 server id 73  end_log_pos 530 	Xid = 335
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql-master /var/lib/mysql]#mysqlbinlog --start-position=333 --stop-position=530 /var/lib/mysql/mysql-bin.000004 > /backup/binlog-`date +%F_%T`.sql
注意：position一定要指对位置
6、继续插入数据，在没备份的情况下删除数据库，模拟误操作 
MariaDB [(none)]> use class;
Database changed
MariaDB [class]> insert into tb1 values (40),(50);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [class]> drop database class;
Query OK, 2 rows affected (0.02 sec)

MariaDB [(none)]> 
此时查看数据库发现class就没有了
7、数据恢复，由于最后我们没有备份就删除了数据库，所以我们首先 需要保护最后的二进制日志，查看删除操作之前的position值 
mysqlbinlog /mydata/data/mysql-bin.000004
[root@mysql-master ~]#mysqlbinlog /var/lib/mysql/mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171226 21:27:16 server id 73  end_log_pos 245 	Start: binlog v 4, server v 5.5.52-MariaDB created 171226 21:27:16
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
NE5CWg9JAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAYE0+vw==
'/*!*/;
# at 245
#171226 21:30:15 server id 73  end_log_pos 333 	Query	thread_id=18	exec_time=0	error_code=0
use `class`/*!*/;
SET TIMESTAMP=1514295015/*!*/;
SET @@session.pseudo_thread_id=18/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table tb1(id int)
/*!*/;
# at 333
#171226 21:30:18 server id 73  end_log_pos 402 	Query	thread_id=18	exec_time=0	error_code=0
SET TIMESTAMP=1514295018/*!*/;
BEGIN
/*!*/;
# at 402
#171226 21:30:18 server id 73  end_log_pos 503 	Query	thread_id=18	exec_time=0	error_code=0
SET TIMESTAMP=1514295018/*!*/;
insert into tb1 values (10),(20),(30)
/*!*/;
# at 503
#171226 21:30:18 server id 73  end_log_pos 530 	Xid = 335
COMMIT/*!*/;
# at 530
#171226 21:52:48 server id 73  end_log_pos 599 	Query	thread_id=43	exec_time=0	error_code=0
SET TIMESTAMP=1514296368/*!*/;
BEGIN
/*!*/;
# at 599
#171226 21:52:48 server id 73  end_log_pos 695 	Query	thread_id=43	exec_time=0	error_code=0
SET TIMESTAMP=1514296368/*!*/;
insert into tb1 values (40),(50)
/*!*/;
# at 695
#171226 21:52:48 server id 73  end_log_pos 722 	Xid = 343
COMMIT/*!*/;
# at 722
#171226 21:53:03 server id 73  end_log_pos 805 	Query	thread_id=43	exec_time=0	error_code=0
SET TIMESTAMP=1514296383/*!*/;
drop database class
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
8、将最后操作的二进制日志备份 
[root@mysql-master ~]#mysqlbinlog  --stop-position=722 /var/lib/mysql/mysql-bin.000004 > /backup/binlog-`date +%F_%T`.sql
9、导入之前的所有备份 
[root@mysql-master /backup]#ls
2017-12-26_21:27:16-all.sql        binlog         binlog-2017-12-26_21:49:12.sql
 binlog-2017-12-26_21:57:42.sql
[root@mysql-master /backup]#mysql < /backup/2017-12-26_21:27:16-all.sql
[root@mysql-master /backup]#mysql < binlog-2017-12-26_21:49:12.sql
[root@mysql-master /backup]#mysql < binlog-2017-12-26_21:57:42.sql
10、查看数据库及数据 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [test]> use class;
Database changed
MariaDB [class]> select * from  class.tb1;
+------+
| id   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
|   50 |
+------+
5 rows in set (0.00 sec)

MariaDB [class]> 
到此数据成功全部恢复！
下一篇博客将介绍另一种数据库恢复 工具xtrabackup的使用

sqj155
关注
专栏目录
mysql 备份binlog,Mysql备份与还原——mysqldump结合binlog
weixin_42467768的博客
03-17 885
MySQL 备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。一、mysqldump 备份结合 binlog 日志恢复1、binlog介绍mysql 的二进制日志记录着该数据库的所有增删改的操作日志，还包括了这些操作的执行时间。我们可以使用mysqlbi...
MySQL全量备份与增量备份-mysqldump&mysqlbinlog+binlog2sql
Anon
01-29 354
MySQL数据备份全量备份mysqldump1、全量备份数据库增量备份mysqlbinlog2、保证bin-log已开启查看binlog相关系统参数值binlog的三种格式3、导出binlog查看binlog文件的2种方式binlog2sql3、binlog转化为可执行的sql4、执行增量备份的sql参考文档 上周对测试环境的MySQL进行了一次迁移，学习了一些MySQL数据备份相关的知识。主要...
深入分析MySQL 的备份和恢复机制
12-16
本文讨论 MySQL 的备份和恢复机制，以及如何维护数据表，包括最主要的两种表类型： MyISAM 和 Innodb ，文中设计的 MySQL 版本为 5.0.22。 目前 MySQL 支持的免费备份工具有： mysqldump、mysqlhotcopy ，还可以用 SQL 语法进行备份： BACKUP TABLE 或者 SELECT INTO OUTFILE ，又或者备份 二进制日志（binlog） ，还可以是 直接拷贝数据文件和相关的配置文件 。MyISAM 表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。 Innodb 所有的表都保存在同一个数据文件 ibdat
mysql binlog备份
weixin_33921089的博客
09-12 103
1、binlog备份[mysql@mysql5 binlog]$mysqlbinlog --read-from-remote-server --raw --host=192.168.1.210 --port=65535 --user=yang --password=oracle --stop-never --result-file=/mysqldb/backup/backup_192.168.1....
Mysql-数据备份
最新发布
qq_42181617的博客
10-11 1660
MySQL 数据备份是数据库管理中的一个重要环节，确保数据的安全性和可恢复性。以下是 MySQL 数据备份的详细步骤和方法，包括逻辑备份和物理备份。
mysql binlog 备份_mysql binlog备份
weixin_42132354的博客
01-27 218
1、binlog备份[mysql@mysql5 binlog]$mysqlbinlog --read-from-remote-server --raw --host=192.168.1.210 --port=65535 --user=yang --password=oracle --stop-never --result-file=/mysqldb/backup/backup_192.168.1....
mysql使用binlog备份
qq_36923648的博客
02-21 4104
一、bin-log备份作用 二、开启bin-log 设置配置文件my.inf 配置文件中加入以下内容 log-bin=/var/lib/mysql/mysql-bin //开启并设置备份文件目录，以及备份命名格式 server-id=1 //集群中数据库服务器的唯一id，非集群环境可随意调写。mysql5.7以上版本需填写 其他可选配置项举例 binlog_format =mixed expire_logs_days = 10 三、使用bin-log恢复数据 查看binlog文件
用Mysqlbinlog备份BinLog文件
weixin_33875839的博客
10-30 289
默认情况下, mysqlbinlog读取二进制文件【BinLog】并以文本的方式呈现【text format】.mysqlbinlog可以直接地从本地读取Log，也可以读取远程的Log【--read-from-remote-server】,默认是直接输出，当然也可以保存到指定的输出文件中。如： 1.显示有哪些Log文件： show binary logs;如： 2.Load指定的文件...
MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析
09-08
通过这样的实验，我们可以验证mysqldump配合binlog的恢复机制是否有效。在实际应用中，确保定期备份和binlog记录完整是防止数据丢失的关键。同时，为了提高恢复效率，可以考虑使用增量备份和binlog定位技术，以便更...
通过mysqldump+binlog进行增量备份与基于pos位置的恢复
Allen_Young_的博客
06-12 2164
模拟场景：删除数据库，并且所有操作都是在同一个日志文件中 步骤一：进行完全备份。执行如下命令完整备份一次数据库，备份到当前目录的backup20190612.sql文件中。 参数说明： -B：备份时添加建库语句 -l：数据库表添加只读锁 -F：刷新bin-log日志文件，相当于执行flush logs指令，生成新的bin-log日志文件，此后日志都放在新的bin-log日志文件中，利...
mysql备份恢复机制
构思昨天
07-26 1324
本文讨论 MySQL 的备份和恢复机制，以及如何维护数据表，包括最主要的两种表类型：MyISAM 和 Innodb，文中设计的 MySQL 版本为 5.0.22。 目前 MySQL 支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用 SQL 语法进行备份：BACKUP TABLE 或者 SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还
mysqlbinlog 备份脚本
11-24
这个是我在公司用的本非binlog日志的脚本，binlog日志也是需要经常备份的。需要的大家下载
mysql binlog 备份_MySQL高可用架构设计
weixin_39780784的博客
11-21 325
文章来自于https://www.jianshu.com/p/d3107bda2963PHP进阶学习交流QQ群：983229225Mysql复制功能介绍Mysql的复制功能提供分担读负载，对的，只是读负载而不是写负载，而且Mysql的复制为高可用、灾难恢复、备份提供更多的选择。Mysql的复制是基于主库的二进制日志。复制是异步的，所以同一时间点上，主从可能不一致，这也是不可控的。复制解决...
mysql之 innobackupex备份+binlog日志的完全恢复（命令行执行模式）
张冲andy的博客
06-09 2649
前言： MySQL的完全恢复，我们可以借助于完整的 备份+binlog 来将数据库恢复到故障点。 备份可以是热备与逻辑备份(mysqldump)，只要备份与binlog是完整的，都可以实现完全恢复。 1. 准备实验环境 mysql> select version(); +------------+ | version() | +------------+ | 5.6.25-lo
mysql binlog文件如何备份_使用mysqlbinlog备份恢复二进制文件
weixin_35260153的博客
01-31 910
目录我们知道mysqlbinlog的功能可以让MySQL的二进制文件以文本格式显示输出，让文件易读。mysqlbinlog还可以结合管道符，让mysqlbinlog的输出作为mysql的输入，在恢复二进制日志时可以用到。另外，mysqlbinlog可以用来备份二进制文件。1 mysqlbinlog的备份功能mysqlbinlog可以读取二进制日志文件并将读取到的内容完全一致以二进制格式写入到新文...
讲一讲 MySQL 数据备份杀手锏 binlog
IT学习小镇
01-06 425
我们都知道，数据非常重要网上也经常看到一些段子，某公司程序员对工作不满，删库跑路，老板损失惨重，欲哭无泪。那么有没有什么解决方案？即使数据库真的被删了，也有备份数据，能快速恢复。甚至可以做到实时热备，即使内部炸掉外部用户也感知不到，一片风平浪静。MySQL 作为当下流行数据库，在数据备份、高可用方面非常有竞争力，今天，我们就重点来讲下什么是 MySQL 主备情况一：客户端的业务操作，读、写访问的是...
mysql通过mysqldump+binlog进行数据恢复
qq_44955537的博客
01-12 287
show variables like 'log_%'; #查看binlog是否打开 首先binlog一定是要先打开的 第一行我们可以看到是ON代表binlog已经打开了，如果你的是OFF那证明并没有打开binlog,在my.ini文件中添加以下配置然后重启mysql就可以看到binlog打开了 log-bin=mysql-bin server-id=1 use demo 然后查看数据库 我现在有一个students表 查看是有两个记录，现在我们先来mysqldump备份一下现在的数据，然后
使用mysqlbinlog 备份 binlog日志文件
pang_2899的博客
06-18 1988
使用mysqlbinlog 命令进行binlog 日志文件的备份及实时备份功能。
MySQL误删数据库恢复：mysqldump+binlog完整恢复策略解析
总结来说，MySQL的mysqldump配合binlog提供了强大的数据库恢复能力，对于误操作导致的数据丢失，只要备份及时且binlog完整，就能有效恢复。但要注意，定期备份和监控binlog是非常重要的预防措施，以确保在出现问题时...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值