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

最新推荐文章于 2024-05-29 17:22:35 发布
最新推荐文章于 2024-05-29 17:22:35 发布
阅读量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
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql的数据备份和还原 - 如何使用mysqldump和mysqlbinlog
白泽的博客
04-06 5660
利用mysqldump和mysqlbinlog备份数据库和恢复数据库
Mysql开启binlog后启动失败
epitomizelu的专栏
12-09 4211
一，开启mysql日志 在/etc/my.cnf文件中添加配置： log-bin=mysql-bin binlog_format=row binlog-do-db=mydb 二，开启后重启失败 报错信息： Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
mysql数据库管理-mysqlbinlog备份与恢复，主备复制分析
最新发布
oradbm的博客
05-29 773
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文件的文本格式，就会用到mysqlbinlog日志管理工具。mysqlbinlog的具体用法如下：option有很多选项，常用的选项如下。-d, --database=name：指定数据库名称，只列出指定的数据库相关操作。-o, --offset=#：忽略掉日志中的前 n行命令。-r, --result-file=name：将输出的文本格式日志输出到指定文件。
MySQL binlog与增量备份的基本原理总结
Neo
06-27 8960
定义 binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE即使update没有导致数据变化也会被记录、DELETE…）的二进制日志。 binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。 作用 复制：MySQL Replication在Master端开启bin
mongodb和mysql的expect方式的异地备份
04-23
mongodb和mysql的expect方式的异地备份 mysql数据的异地备份： （1）在mysql服务器上安装.exp文件运行需要的运行环境.并检测 #yum install expect
mysqlbinlog 数据备份与恢复
qq_38274166的博客
12-03 621
经过我们之前学习的mysqlbinlog，我们已经学会如何进行备份，我们在这里开始将其进行备份恢复。 假设现在是凌晨12点，我们的任务是将数据库进行一次完整备份，将binlog数据库备份到/root下BAK.binlog.sql mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B binlog > /root/BA...
MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析
09-08
通过这样的实验，我们可以验证mysqldump配合binlog的恢复机制是否有效。在实际应用中，确保定期备份和binlog记录完整是防止数据丢失的关键。同时，为了提高恢复效率，可以考虑使用增量备份和binlog定位技术，以便更...
MySQL数据库和MySQL管理、备份与恢复
12-14
本教程将带你深入了解MySQL数据库的使用，管理以及备份与恢复的相关知识。 首先，让我们从基础开始，理解什么是数据库。数据库是存储和组织数据的系统，它允许用户以结构化方式访问、管理和更新数据。MySQL以其高效...
linux运维学习笔记：Mysql备份与恢复.pdf
08-18
Mysql 备份与恢复 在数据库管理中，备份与...备份与恢复是数据库管理中非常重要的两个步骤，通过mysqldump 工具可以轻松实现数据的备份和恢复，当然在实际应用中，我们还需要根据实际情况选择合适的备份方式和工具。
MySQL备份与恢复之保证数据一致性(5)
09-10
在MySQL备份与恢复过程中，保证数据一致性是一个复杂而关键的任务，涉及到多个层面的考虑，包括备份策略、事务处理和日志管理。理解这些概念并正确执行备份和恢复流程，可以有效地保护数据的完整性和可用性，减少因...
MySQL 备份与恢复 手把手教学视频
08-05
通过学习“MySQL备份与恢复”教程，你可以全面了解MySQL备份的各种方法，学会如何制定和执行备份策略，以及在关键时刻正确地恢复数据，保障业务的连续性。无论是初学者还是经验丰富的DBA，深入理解这些知识都将有助...
mysqlbinlog 备份脚本
11-24
这个是我在公司用的本非binlog日志的脚本，binlog日志也是需要经常备份的。需要的大家下载
深入分析MySQL 的备份和恢复机制
tomspcc的专栏
10-12 1313
备份对于数据库来说是相当重要的工作。如果数据库在使用过程中出现了问题，比如系统崩溃、硬件故障或错误的删除了数据。这时，如果我们进行了数据备份，就能比较方便的使数据库恢复工作，并使我们的数据损失到最小。下面我们来详细探讨下数据库的备份与恢复。 本文讨论 MySQL 的备份和恢复机制，以及如何维护数据表，包括最主要的两种表类型： MyISAM 和 Innodb ，文中设计的 My
mysqldump导出包含存储过程（SP）的数据库文件
现役码农的博客
11-23 3627
之前自己一直是使用mysqldump导出文件，最近项目中数据库设计到SP的时候，发现导出的数据中没有包含SP。 mysqldump默认不会直接导出SP和函数，需要添加指定的参数，如SP需要加 -R ；   mysqldump -uroot -proot -R test &gt; test.sql 除了-R之外，mysql还提供了其他的几种参数供使用：           -n...
通过mysqldump+binlog进行增量备份与基于pos位置的恢复
Allen_Young_的博客
06-12 1989
模拟场景：删除数据库，并且所有操作都是在同一个日志文件中 步骤一：进行完全备份。执行如下命令完整备份一次数据库，备份到当前目录的backup20190612.sql文件中。 参数说明： -B：备份时添加建库语句 -l：数据库表添加只读锁 -F：刷新bin-log日志文件，相当于执行flush logs指令，生成新的bin-log日志文件，此后日志都放在新的bin-log日志文件中，利...
一个简单的binlog恢复测试
weixin_30596023的博客
03-29 288
日常的数据备份及恢复测试，是DBA工作重中之重的事情，所以要做好备份及测试，日常的备份常见有mysqldump+binlog备份、xtrabackup+binlog备份，无论那一种，几乎都少不了对binlog的备份，说明了binlog在数据恢复中的重要性，下面做个小测试，是工作中不少运维或者新人DBA容易犯的错。 创建一个测试表tb1： <test>(root@loc...
MySQL mysqldump数据导出详解 --dump-slave 注意事项
weixin_34381666的博客
07-21 1816
http://www.cnblogs.com/chenmh/p/5300370.html 介绍  在日常维护工作当中经常会需要对数据进行导出操作，而mysqldump是导出数据过程中使用非常频繁的一个工具；它自带的功能参数非常多，文章中会列举出一些常用的操作，在文章末尾会将所有的参数详细说明列出来。   语法： 默认不带参数的导出，导出文本内容大概如下：创建数据库判断语句-删...
Mysql使用binlog操作恢复数据解决失误操作
Don't Call Me
08-24 6381
本文是基于centos7环境下进行操作的，如有必要请先阅读此篇文章安装mysql8.0  https://blog.csdn.net/qq_39038465/article/details/81663540 一 、binlog简介 binlog,binlog：binlog是一个二进制格式的文件，它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；它可以用来查看数据库的变更历史、数据库增...
Mysql-使用mysqldump+binlog 实现备份
makeliwei1的博客
07-20 506
mysql&amp;amp;gt; show databases; mysql&amp;amp;gt; create database test; mysql&amp;amp;gt; use test; mysql&amp;amp;gt; create table tb1(int id primary key auto_increment,name varchar(20)); mysql&amp;amp;gt; insert into tb1(name) values
MySQL备份与恢复(PDF版)借鉴.pdf
11-22
"MySQL备份与恢复的详细指南" 在MySQL数据库管理中，备份与恢复是至关重要的环节，确保数据安全并能在系统故障后迅速恢复。本文主要探讨了MySQL的备份策略，包括完全备份、增量备份以及相关的恢复技术，特别关注了...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值