我的编程空间,编程开发者的网络收藏夹
学习永远不晚

《MySQL数据库》MySQL备份恢复

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

《MySQL数据库》MySQL备份恢复


	《MySQL数据库》MySQL备份恢复
[数据库教程]

前言

MySQL数据库最重要的部分就是数据,所以保证数据不被损坏尤为重要,大家都知道911事件,当时非常多的数据丢失,导致经济混乱。接下来我们就来讲讲MySQL是如何保障数据完整,应对特殊情况,如何恢复等。

备份

备份检查:保证备份没有问题,并且定期演练恢复数据。

备份方式:逻辑备份,物理备份。

逻辑备份方式:mysqldump(MDP),replication,mydumper,load data in file。

物理备份方式:MySQL Enterprise Backup(企业版) , percona Xtrabackup (PBK,XBP)

 mysqldump(MDP)

InnoDB 可以使用快照备份,通过建库,建表,插入语句备份数据。

非Innodb 表备份需要锁表, 非Innodb 表主要是MySQL系统表。

参数说明:

mysqldump  需要连接到数据库,连接方式和mysql 一致。

创建备份目录

mkdir -p backup    -- linux 下执行创建文件夹
chown -R mysql.mysql /usr/local/mysql/backup   -- linux 下执行赋权

备份命令:

mysqldump -uroot -proot -A > /usr/local/mysql/backup/mysqlbackup.sql  -- -A 备份全库的意思

技术图片

备份多库

mysqldump -u cop -p -B cop copdb   > copdb1.sql       -- -B参数导出多个库。

备份某些表

mysqldump -uroot -proot castledata test test1 > /usr/local/mysql/backup/mysqlbackup1.sql -- 导出一张表的结构和数据:castledata 库名;cop_toperator,cop_tsys是表名。

其实这个时候你会发现一个问题,库备份了,但是数据还是会继续操作的,真的出现误删除数据库,如何才能完整的恢复呢。

第一反应就是 备份加binlog ,但是binlog的开始位置点却不好找, 如何才能准确无误的找到这个开始位置点呢?

重点:

--master-data=2    -- 2:表示会再备份文件中加入一句注释,写下binlog文件和开始位置点。 默认是0。
mysqldump -uroot -proot --master-data=2  castledata test test1 > /usr/local/mysql/backup/mysqlbackup2.sql     -- 使用上面的参数备份

技术图片

mysqldump -uroot -proot -F -B castledata > /usr/local/mysql/backup/mysqlbackup3.sql    

-F 参数是备份的时候切一个新的binlog日志。

--single-transaction           开启事务,获取快照,对innodb存储引擎有效。

-R                                      备份存储过程及函数

--triggers                            备份触发器

-E                                       备份事件

--max_allowed_packet       客户发数据包到服务端的大小,备份的是表示服务端发到客户端的大小

完整生产备份语句:

mysqldump -uroot -proot -R --triggers -E --single-transaction --master-data=2 --max_allowed_packet=64 -B castledata > /usr/local/mysql/backup/mysqlbackup5.sql

恢复

模拟备份到恢复的过程:

第一步准备原始数据:

create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(11),(22),(33);
insert into t1 values(111),(222),(333);
insert into t1 values(1111),(2222),(3333);
insert into t1 values(11111),(22222),(33333);
commit;

第二步备份数据:

mysqldump -uroot -proot -R --triggers -E --single-transaction --master-data=2 --max_allowed_packet=64 -B backup > /usr/local/mysql/backup/mysqlbackup_`date +%F `.sql

第三步模拟后续数据操作:

create table t2 (id int);
insert into t2 values(1),(2),(3);
insert into t2 values(11),(22),(33);
insert into t2 values(111),(222),(333);
insert into t2 values(1111),(2222),(3333);
insert into t2 values(11111),(22222),(33333);
commit;

第四步逻辑操作出错:

drop database backup;

第五步恢复数据:

获取最近一次备份文件中的binlog 起点信息:

技术图片

binlog 日志原理:https://www.cnblogs.com/jssj/p/13472394.html

恢复备份数据

set sql_log_bin = 0;
source /usr/local/mysql/backup/mysqlbackup_2020-08-18.sql;
set sql_log_bin = 1;

截取binlog

起点:在第一步中已经获取, 终点:使用命令 " show binlog events in ‘mysql-bin.000005‘ ; "  查询

技术图片

取 3362 即可。  执行一下命令截取

mysqlbinlog --start-position=1861 --stop-position=3362 /usr/local/mysql/binlog/mysql-bin.000005 > /usr/local/mysql/backup_binlog.sql

恢复binlog 日志

set sql_log_bin = 0;
source /usr/local/mysql/backup_binlog.sql;
set sql_log_bin = 1;

ok 恢复失败。binlog的恢复。原因为我们开启了GTID 所以截取命令需要加入 参数  --skip-gtids   。 不校验GTID号。

重新截取binlog:

mysqlbinlog --skip-gtids --start-position=1861 --stop-position=3362 /usr/local/mysql/binlog/mysql-bin.000005 > /usr/local/mysql/backup_binlog.sql
set sql_log_bin = 0;
source /usr/local/mysql/backup_binlog.sql;
set sql_log_bin = 1;

ok 恢复成功。验证数据。

技术图片

 

额外扩展:从全备中获取单库,创建表,插入数据

1、获得表结构
# sed -e/./{H;$!d;} -e x;/CREATE TABLE `city`/!d;q  full.sql>createtable.sql
2、获得INSERT INTO 语句,用于数据的恢复
# grep -i INSERT INTO `city`  full.sqll >data.sql &
3.获取单库的备份
# sed -n /^-- Current Database: `world`/,/^-- Current Database: `/p all.sql >world.sql

 

总结

上面讲的都是逻辑备份,MySQL还可以支持物理备份,可以使用工具Percona-XtraBackup 来实现。

《MySQL数据库》MySQL备份恢复

原文地址:https://www.cnblogs.com/jssj/p/13514597.html

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

《MySQL数据库》MySQL备份恢复

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

《MySQL数据库》MySQL备份恢复

前言MySQL数据库最重要的部分就是数据,所以保证数据不被损坏尤为重要,大家都知道911事件,当时非常多的数据丢失,导致经济混乱。接下来我们就来讲讲MySQL是如何保障数据完整,应对特殊情况,如何恢复等。备份备份检查:保证备份没有问题,并且定期演练恢复数据。备
《MySQL数据库》MySQL备份恢复
2016-12-27

MySQL数据库备份和恢复

目录 MySQL数据库备份和恢复 备份恢复概述 为什么要备份 备份注意要点 还原要点 备份类型: 备份时需要考虑的因素 备份
MySQL数据库备份和恢复
2015-03-06

如何备份和恢复MySQL数据库

备份和恢复MySQL数据库可以通过多种方式实现,以下是其中一种常用的方法:备份MySQL数据库:使用命令行工具备份数据库:mysqldump -u [用户名] -p [数据库名] > [备份文件路径]使用MySQL Workbench备份
如何备份和恢复MySQL数据库
2024-04-09

MySQL INSERT锁与数据库备份恢复

INSERT锁是指在MySQL数据库中,当执行INSERT操作时,会对表进行锁定,其他操作无法对该表进行修改或查询,直到INSERT操作完成。这种锁可以确保数据的一致性,防止在插入数据时发生冲突。数据库备份恢复是指在数据库发生意外情况导致
MySQL INSERT锁与数据库备份恢复
2024-08-18

MySQL数据库备份恢复自动化

MySQL数据库备份恢复自动化是指通过编写脚本或使用现有的备份工具,自动执行数据库备份和恢复的过程。这样可以大大提高数据库管理的效率,减少人为错误,并确保在发生数据丢失或损坏时能够快速恢复。以下是实现MySQL数据库备份恢复自动化的几种方法
MySQL数据库备份恢复自动化
2024-10-20

mysql数据库怎么备份 mysql数据库怎么恢复数据库

如何备份和恢复 mysql 数据库?备份:使用 mysqldump 工具转储数据库架构和数据到 sql 文件。使用 phpmyadmin 图形界面导出数据库。恢复:使用 mysql 命令加载 sql 文件中的内容到数据库。使用 phpmya
mysql数据库怎么备份 mysql数据库怎么恢复数据库
2024-08-13

MySQL数据库备份恢复实现代码

数据库的备份#语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql#示例: #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot
2022-05-26

编程热搜

目录