首页 > 数据库 >mysql的备份与恢复

mysql的备份与恢复

时间：2024-07-03 19:52:39浏览次数：24  
标签：opt -- 恢复 备份 mysql sql 数据库

一、备份

1.数据备份的必要性

备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃

2.数据备份分类

物理备份

对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。

  • 冷备份(脱机备份):是在关闭数据库的时候进行的；
  • 热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件；
  • 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。

逻辑备份

对数据库的表或者对象进行备份。

3.数据库备份策略

完全备份：整个数据集，每次都备份完整的数据库。

差异备份：仅备份最近一次完全备份后变化的数据，备份较慢，还原简单

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

4.常用的备份方法和工具

物理冷备

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的。

专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具（逻辑热备）

备份速度较慢（如果数据库超过100G，一般不建议使用mysqldump)

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

archive

启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志

第三方工具备份
免费的MySQL热备份软件Percona;XtraBackup

XtraBackup(物理热备)

https://mp.weixin.qq.com/s/d3spGhku154dOgWSct-3Ww

5.数据库上云迁移

冷迁移

热迁移

二、MySQL完全备份

1.简介

优点
备份与恢复操作简单方便
缺点
数据存在大量的重复占用大量的备份空间
备份与恢复时间长

  • 完全备份是对整个数据库、数据库结构和文件结构的备份
  • 保存的是备份完成时刻的数据库
  • 是差异备份与增量备份的基础

2.物理冷备份与恢复

  • 关闭MySQL数据库
  • 使用tar命令直接打包数据库文件夹
  • 直接替换现有MySQL目录即可

物理冷备份

#先关闭数据库
systemctl stop mariadb
#建立备份文件存放目录
mkdir /opt/bak

#压缩备份
cd /var/lib
tar zcvf /opt/bak/mysql_full_$(date +%F).tar.gz ./mysql

$(date -d "-1 day" +%Y%m%d)

解压恢复

主机1：192.168.100.146

主机2：192.168.100.150

备份到另一台主机

#先关闭数据库
systemctl stop mariadb

scp 192.168.100.146:/opt/bak/* /opt 
#将主机1的备份包远程传输到主机2

#先备份数据，防止恢复失败
mv /var/lib/mysql/ /var/lib/mysql.bak

tar xf mysql_full_2024-07-03.tar.gzcp -a /../data /usr/local/mysql/data  
#不用-a 权限不足，无法启动
#如果是从主机2传输回主机1，需要将属主和属组改为mysql

systemctl start mysqld
mysql -u root -p 
#登录到数据库

3.mysqldump备份与恢复

选项含义
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name… #指定备份的数据库，包括create database语句
-E, --events： #备份相关的所有event scheduler
-R, --routines： #备份所有存储过程和自定义函数
--triggers： #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复 制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启-- single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， 配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度

1）完全备份一个或多个完整的库（包括其中所有的表）

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql    
#导出的就是数据库脚本文件

如果不加 --databases ,只备份库中的表，不会备份库
#举个例子

#单个数据库
mysqldump -u root -p --databases hx > /opt/bak/hx.sql

#多个数据库
mysqldump -u root -p --databases db1 db2 > /opt/bak/db12.sql

2）完全备份 MySQL 服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
#举个例子
mysqldump -u root -p --all-databases > /opt/bak/all.sql

cat /opt/bak/all.sql | grep "^CREATE DATABASE"

3）完全备份指定库中的部分表

 

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql

 

举个例子

#备份byyb的student表
mysqldump -u root -p [-d] db2 student > /opt/bak/db2-student.sql

#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

4）查看备份文件

 

cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$"

egrp -v "^--|^/\*|^$" #或运行符
#举个例子
cat db2-student.sql|grep -v "^--" | grep -v "^/" | grep -v "^$"

三、MySQL 完全恢复

1.恢复数据库

当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在。

在生产环境中，可以使用Shell脚本自动实现定时备份

通过source命令恢复

先使用mysqldump工具，生成备份sql脚本；

然后登录到MySQL数据库；

再执行source 备份sql脚本的路径

MySQL[(none)]> source /backup/all-data.sql

举个例子

#先备份目标数据库，以hx为例
mysqldump -uroot -p123 --databases hx > /opt/bak/hx.sql
#模拟数据库被删除
mysql -uroot -p123 -e "drop database hx;"
mysql -uroot -p123 -e "show databases;"

source命令执行sql脚本

#先登录到数据库
mysql -u root -p
#执行脚本
source /opt/bak/byyb.sql;

通过mysql命令

输入重定向

#先模拟数据库删除，这里不再赘述

#恢复数据库
mysql -u root -p123 < /opt/bak/hx.sql
mysql -u root -p123 -e 'show databases;'

 管道符

cat byyb.sql | mysql -uroot -p123123

2.恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

#备份byyb的student表
mysqldump -u root -p123 [-d] db2 student > /opt/bak/db2-student.sql
#模拟删除表
mysql -u root -p -e 'drop table db2.student;'
mysql -u root -p -e 'show tables from db2;'

#恢复表数据
mysql -uroot -p123 db2 < /opt/bak/db2-student.sql
#查看表数据，验证是否成功恢复
mysql -uroot -p123 -e "select * from db2.student;"

四、MySQL增量备份与恢复

1.MySQL增量备份

开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED                #可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /var/lib/mysql-bin.*

通过刷新二进制日志文件间接实现增量备份

mysqladmin -uroot -p[密码] flush-logs
#flush-logs可简写为-F

 查看二进制日志文件的内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件（绝对路径）
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容
#举个例子
cp /usr/local/mysql/data/mysql-bin.000001 /opt 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001

2.MySQL增量恢复

MySQL没有提供直接的增量备份方法，可通过MySQL提供的二进制日志间接实现增量备份。

一般恢复

1）模拟丢失更改的数据的恢复步骤

use byyb;
delete from student where id=3;
delete from student where id=4;

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

2）模拟丢失所有数据的恢复步骤

use kgc;
drop table info1;

mysql -u root -p kgc < /opt/kgc_info1_2020-11-22.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

断点恢复

#查看二进制文件 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

1）基于位置恢复

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码]
#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

2）基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码]
#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

3.备份与恢复配置实例（完全+增量）

为了测试备份数据是否可用，模拟数据丢失故障，进行数据还原。

在数据库表中，分三次录入学生考试成绩。

首次录入成绩后，做该表的完全备份。

后两次成绩的录入之后，分别作增量备份。

模拟数据丢失，并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩。

1）录入第一次数据，并进行完全备份

mysql -u root -p 
create database hx;
use hx;
create table student (name varchar(10),class varchar(10),id int,chinese int,math int,english int);
insert into student values ('aa','1',20170822,110,105,92);
insert into student values ('bb','1',20170820,95,115,110);
insert into student values ('cc','1',20170818,95,103,108);
#其余数据添加格式类似

通过mysqldump工具完全备份
mysqldump -uroot -p123  hx student > /opt/bak/hx-student.sql;

2）录入第二次数据，进行增量备份

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED                #可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1
insert into student values ('dd','2',20170824,92,98,105);
insert into student values ('ee','2',20170826,111,107,96);

drop table student;
#删除该表
insert into test values (4,test4,ddd,24);
#在test表中增加数据

mysqlbinlog /data/mysql-bin.000001 
#查看二进制日志文件

#备份  完全备份后的二进制日志
mysqlbinlog --start-position=245 /data/mysql-bin.000001 > /opt/inc.sql
#找到  删除的语句
grep -i "^drop table"  /opt/inc.sql 

#删除 删表的那一行
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql 

mysql -u root -p 123
set sql_log_bin=0;
#关闭二进制，恢复完全备份和二进制日志备份
source /opt/bak/hx-student.sql;
source /opt/inc.sql;

 

标签：opt,--,恢复,备份,mysql,sql,数据库
From： https://www.cnblogs.com/hxqwe/p/18282454

相关文章

  • MySQL索引事务
    开启学习之旅吧！活到老~学到老！文章目录目录文章目录前言一、索引1.查看索引2.创建索引3.删除索引3.索引实例二、事务1.事务的概念2.事务的使用前言本篇内容主要介绍MySQL的索引与事务，并附上实例以便大家更容易理解。一、索引索引是一种特殊的文件，包含着对......
  • NBU脚本：查看启用或未启用加速的备份策略
    #bppllist:Field41-Optimizedbackup.0=no,1=yes.EnablesthebackupofdeduplicateddatathatsomeMicrosoftWindowsoperatingsystemversionsprovide.Ifaclienthasadeduplicatedfilesystemconfigured,NetBackupbacksupthededuplicateddat......
  • windows的桌面docker安装mysql5.7
    dockerrun-p3306:3306--namemysql^-vE:/mysql/data:/var/lib/mysql^-vE:/mysql/conf:/etc/mysql/conf.d^-vE:/mysql/log:/var/log/mysql^-eMYSQL_ROOT_PASSWORD=123456^-dmysql:5.7命令解析：dockerrun-p3306:3306--namemysql：创建一个docker容......
  • 小白也能懂的Mysql数据库索引详解
    核心概念主键索引/二级索引聚簇索引/非聚簇索引回表/索引覆盖索引下推联合索引/最左联合匹配前缀索引explain一、[索引定义]1.索引定义在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现......
  • 什么是 MySQL 锁等待？
    本文基于MySQL8.0.32源码，存储引擎为InnoDB。先排队不管是加表锁，还是加行锁，如果不能立即获得锁，加锁事务都需要进入锁等待状态。事务进入锁等待状态，需要用锁结构来排队。和立即获得锁时的锁结构一样，这个锁结构的各属性都已经初始化完成。不同之处在于，它被设置为等待状态。......
  • ETL数据集成丨使用ETLCloud实现MySQL与Greenplum数据同步
    我们在进行数据集成时，MySQL和Greenplum是比较常见的两个数据库，我们可以通过ETLCloud数据集成平台，可以快速实现MySQL数据库与数仓数据库（Greenplum）的数据同步。MySQL数据库：优点：轻量级与高性能：MySQL体积小、启动快，对资源消耗相对较低，适合处理高并发的OLTP（在线事务处理）场景。开源......
  • 服务-mysql
    目录安装范例：二进制安装mysql基础范例：mysql客户端登录，执行脚本范例：管理用户、权限，增改锁范例：查看默认字符集和排序规则常用SQL范例：数据库DDL：CREATE，DROP，ALTER范例：数据表DDL：CREATE，DROP，ALTER范例：DML：INSERT,DELETE,UPDATE范例：DQL单表查询语句范例：DQL多表子查询范例：DQL多表查......
  • C4D崩溃，出现错误的文件构造如何恢复？
    C4D这款业界领先的3D建模、动画、模拟和渲染软件时，用户可能会遇到各种挑战，其中软件崩溃和错误提示往往是最令人头疼的问题之一。特别是当C4D崩溃后出现“错误的文件构造”这样的提示，不仅会中断创作流程，还可能意味着辛苦工作的成果面临丢失的风险。C4D错误的文件构造怎么办1、文......
  • Mysql中视图的使用以及常见运算符的使用示例和优先级
    场景基础知识回顾：mysql中视图的基础使用以及常见运算符的使用示例。注：博客：https://blog.csdn.net/badao_liumang_qizhi实现Mysql中视图的使用视图的创建CREATEVIEWstu_viewASSELECT*FROMbus_student;视图查询SELECT*FROMstu_view;查看视图基本信息SHOWTAB......
  • MySQL 中 SQL 查询语句的执行顺序
    在MySQL中，SQL查询的执行顺序通常按照以下顺序进行：FROM：从指定的表中选择数据。WHERE：对数据进行筛选，只选择满足条件的行。GROUPBY：按照指定的列对数据进行分组。SELECT：选择要返回的列或表达式。HAVING：对分组后的数据进行筛选，只选择满足条件的分组。UNION[ALL]ORDERBY：对......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99