当前位置:   article > 正文

Mysql数据备份与恢复

Mysql数据备份与恢复

目录

1.数据备份的重要性

2.数据备份的类型

2.1物理备份

2.2逻辑备份

2.3数据库备份策略分类

3.常见的备份方法

3.1物理冷备

3.2专用备份工具mydump或mysqlhotcopy

3.3启用二进制日志进行增量备份

3.4第三方工具备份

4.完全备份

4.1完全备份的优缺点

4.2数据库完全备份分类

4.2.1物理冷备份与恢复

4.2.2mysqldump备份与恢复

5.完全备份实验

5.1MySQL物理冷备份及恢复

5.1.1删除库zhouyi

5.1.2恢复库zhouyi

5.2mysqldump 完全备份与恢复(温备份)

5.2.1完全备份一个或多个完整的库 (包括其中所有的表)

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

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

5.2.4查看备份文件 

5.3恢复数据库

5.3.1source恢复

5.3.2mysql恢复

5.4恢复表

5.4.1source恢复

5.4.2mysql恢复

6.Mysql的增量备份与恢复

6.1恢复的方式

6.1.1一般恢复

6.1.2基于位置恢复

6.1.3基于时间点恢复

6.2增备实验

6.2.1开启二进制日志功能

6.2.2二进制日志(binlog)的3种不同的记录格式 

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

6.2.4二进制日志中需要关注的部分

6.3进行全备+增备

6.4增量恢复

6.4.1一般恢复

6.5断点恢复

6.5.1基于位置恢复

6.6基于时间节点


1.数据备份的重要性

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

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

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

造成数据丢失的原因

  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难(如火灾、地震)和盗窃

2.数据备份的类型

从物理与逻辑的角度,备份可分为物物理备份与逻辑备份

从数据库的备份策略角度,备份可分为完全备份、差异备份、 增量备份

2.1物理备份

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

2.2逻辑备份

对数据库逻辑组件(如:表等数据库对象)的备份

2.3数据库备份策略分类

  • 完全备份:每次对数据库进行完整的备份;
    导致备份文件占用巨大空间,有大量重复数据;
    恢复时,直接把文件导入进去即可
  • 差异备份:备份自从上次完全备份之后被修改过的文件;
    可能会出现备份重复数据,占用额外的磁盘空间;
    恢复时,先恢复完全备份,再导入差异备份的数据
  • 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份;不会出现重复数据,也不会占用额外的磁盘空间;
    恢复数据时,需要完全恢复,再增量恢复(按次序)

3.常见的备份方法

3.1物理冷备

  • 备份时数据库处于关闭状态,直接打包数据库文件
  • 备份速度快,恢复时也是最简单的

3.2专用备份工具mydump或mysqlhotcopy

  • mysqldump常用的逻辑备份工具
  • mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3.3启用二进制日志进行增量备份

  • 进行增量备份,需要刷新二进制日志

3.4第三方工具备份

  • 免费的MySQL热备份软件Percona XtraBackup

4.完全备份

是对整个数据库、数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

4.1完全备份的优缺点

优点

  • 备份与恢复操作简单方便

缺点

  • 数据存在大量的重复
  • 占用大量的备份空间
  • 备份与恢复时间长

4.2数据库完全备份分类

4.2.1物理冷备份与恢复

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

4.2.2mysqldump备份与恢复

  • MySQL自带的备份工具,可方便实现对MySQL的备份
  • 可以将指定的库、表导出为SQL脚本
  • 使用命令mysql导入备份的数据

5.完全备份实验

5.1MySQL物理冷备份及恢复

  1. systemctl stop mysqld
  2. #关闭mysql服务
  3. yum -y install xz
  4. #使用xz工具进行压缩,查看本系统中是否安装xz工具,如果没安装用yum安装 yum install xz -y

  1. #压缩备份
  2. tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
  3. systemctl start mysqld

5.1.1删除库zhouyi

  1. #模拟故障,删除数据库
  2. drop database zhouyi;

5.1.2恢复库zhouyi

  1. #解压恢复
  2. tar jxf mysql_all_2024-03-25.tar.xz -C /usr/local/mysql/data/
  3. cd /usr/local/mysql/data
  4. mv usr/local/mysql/data/* ./

5.2mysqldump 完全备份与恢复(温备份)

5.2.1完全备份一个或多个完整的库 (包括其中所有的表)

  1. mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql
  2. #导出的就是数据库脚本文件
  3. 例:
  4. mysqldump -u root -pabc123 --databases school > /opt/school.sql #备份一个school库

备份school库

备份school和zhouyi两个数据库

mysqldump -u root -pabc123 --databases school zhouyi > /opt/s_z.sql

备份zhouyi库中qq表

mysqldump -u root -pabc123 zhouyi qq > /opt/qq.sql

备份zhouyi库中shuju表的数据结构

mysqldump -u root -pabc123 -d zhouyi shuju > /opt/sj.sql

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

  1. mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
  2. 例:
  3. mysqldump -u root -pabc123 --all-databases > /opt/all.sql

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

  1. mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
  2. 例:
  3. mysqldump -u root -pabc123 zhouyi shuju > /opt/ll.sql
  4. #使用“-d”选项,说明只保存数据库的表结构
  5. #不使用“-d"选项,说明表数据也进行备份
  6. #做为一个表结构模板

5.2.4查看备份文件 

  1. grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"
  2. 例:
  3. cat /opt/s_z.sql|grep -v "^--"|grep -v "^/"|grep -v "^$"

5.3恢复数据库

5.3.1source恢复

删除备份的数据库

  1. #删除已备份数据库
  2. mysql -u root -p[密码] -e 'drop database 数据库;'
  3. #-e"选项,用于指定连接MySQL后执行的命令,命令执行完后自动退出
  4. #查看所有的库
  5. mysql -u root -p[密码] -e 'SHOW DATABASES;'
  6. mysql -u root -pabc123 -e 'drop database school;'
  7. mysql -u root -pabc123 -e 'show databases;'

恢复刚刚删除的数据库

  1. mysql -u root -pabc123 -e 'source /opt/school.sql;'
  2. mysql -u root -pabc123 -e 'show databases;'

5.3.2mysql恢复

先删除备份的数据库

  1. mysql -u root -pabc123 -e 'drop database school;'
  2. mysql -u root -pabc123 -e 'show databases;'

恢复刚刚删除的数据库

  1. mysql -u root -pabc123 < /opt/school.sql
  2. mysql -u root -pabc123 -e 'show databases;'

5.4恢复表

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

  1. mysqldump -u root -p123123 my t1 > /opt/backup/t1.sql #备份my库中的表t1
  2. mysql -u root -p123123 -e 'drop table my.t1;' #删除my库中的已备份的表t1
  3. mysql -u root -p123123 qyd < /opt/backup/t1.sql #恢复表,讲备份的表重定向输入到qyd库中
  4. mysql -u root -p123123 -e 'show tables from qyd;' #查看所有qyd库中的表
  5. mysql -u root -p123123 -e 'select * from qyd.t1;' #查看t1表数据记录

5.4.1source恢复

删除已经备份的zhouyi数据库下的qq表

mysql -u root -pabc123 -e 'drop table zhouyi.qq;'

恢复qq表

5.4.2mysql恢复

先删除qq表

恢复qq表

  1. mysql -u root -pabc123 zhouyi < /opt/qq.sql
  2. mysql -u root -pabc123 -e 'use zhouyi;show tables;select * from qq;'

前提:在全量备份与恢复实验中,现在有一个zhouyi库,zhouyi库中有一个qq表

① 当备份时加 --databases ,表示针对于zhouyi库

  1. 当备份时不加 --databases,表示针对school库下的所有表
  2. #备份命令
  3. mysqldump -u root -pabc123 --databases zhouyi > /opt/zy.sql
  4. #恢复过程:
  5. mysql -u root -pabc123
  6. drop database zhouyi;
  7. exit
  8. mysql -u root -pabc123 < /opt/zy.sql
  9. #查看恢复内容
  10. mysql -u root -pabc123 -e 'use zhouyi;select * from qq;'

② 当备份时不加 --databases,表示针对zhouyi库下的所有表 

  1. 当备份时不加 --databases,表示针对school库下的所有表
  2. #备份命令
  3. mysqldump -u root -pabc123 zhouyi > /opt/zz.sql
  4. #恢复过程:
  5. mysql -u root -pabc123 -e 'drop database zhouyi;'
  6. mysql -u root -pabc123 zhouyi < /opt/zz.sql

记得再把zhouyi恢复过来 

6.Mysql的增量备份与恢复

6.1恢复的方式

6.1.1一般恢复

将所有备份的二进制日志内容全部恢复

6.1.2基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

发生错误节点之前的一个节点,上一次正确操作的位置点停止

6.1.3基于时间点恢复

跳过某个发生错误的时间点实现数据恢复

在错误时间点停止,在下一个正确时间点开始

6.2增备实验

6.2.1开启二进制日志功能

  1. vim /etc/my.cnf
  2. [mysqld]
  3. log-bin=mysql-bin
  4. binlog_format = MIXED
  5. #可选,指定二进制日志(binlog)的记录格式为MIXED(混合输入)
  6. server-id = 1
  7. #可加可不加该命令
  8. systemctl restart mysqld

6.2.2二进制日志(binlog)的3种不同的记录格式 

STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT

① STATEMENT(基于SQL语句)

每一条涉及到被修改的sql 都会记录在binlog中

缺点:日志量过大,如sleep()函数,last_insert_id()>,以及user-defined fuctions(udf)、主从复制等架构记录日志时会出现问题

② ROW(基于行)

只记录变动的记录,不记录sql的上下文环境

缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大

③ MIXED 推荐使用

一般的语句使用statement,函数使用ROW方式存储。

总结:

update、delete以多行数据起作用,来用行记录下来,
只记录变动的记录,不记录sql的上下文环境,
比如sql语句记录一行,但是ROW就可能记录10行,但是准确性高,高并发的时候由于操作量,性能变低 比较大所以记录都记下来

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

  1. cp /usr/local/mysql/data/mysql-bin.000001 /opt/
  2. mysqlbinlog --no-defaults /opt/mysql-bin.000001
  3. mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
  4. #--base64-output=decode-rows:使用64位编码机制去解码(decode)并按行读取(rows)
  5. #-v: 显示详细内容
  6. #--no-defaults : 默认字符集(不加会报UTF-8的错误)
  7. PS: 可以将解码后的文件导出为txt格式,方便查阅
  8. mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 > /opt/mysql-bin.000001

6.2.4二进制日志中需要关注的部分

1、at :开始的位置点

2、end_log_pos:结束的位置

3、时间戳: 210712 11:50:30

4、SQL语句

6.3进行全备+增备

  1. mysqldump -u root -pabc123 --databases zhouyi > /opt/zhouyi_01.sql
  2. mysqldump -u root -pabc123 zhouyi qq > /opt/zhouyi-qq.sql

生成一个新的二进制备份文件 

  1. 可每天进行增量备份操作,生成新的二进制日志文件(例如:mysql-bin.000002)
  2. mysqladmin -u root -p flush-logs

插入新数据,以模拟数据的增加或变更
PS:在第一次完全备份之后刷新二进制文件,在第二个二进制文件中记载着"增量备份的数据"

在school数据库中创建表t1;并且插入数据

  1. create table t1(id int(4),name char(10),hobby char(20));
  2. insert into t1 values(1,'kfs','ds');
  3. insert into t1 values(2,'kzy','xx');

创建gc库,复制t1表给另一个表,并命名为sps

  1. create database gc;
  2. create table sps(select * from t1);
  3. select * from sps;

  1. 再次生成新的二进制日志文件(例如:mysql-bin.000007)
  2. mysqladmin -u root -pabc123 flush-logs;
  3. #之前的步骤创建school库中的t1表和创建gc库以及库中的sps表的操作会保存到mysql-bin.000002文件中

6.4增量恢复

6.4.1一般恢复

模拟所有数据丢失,直接删除school和gc两个库

完全恢复被删的库zhouyi

  1. mysql -u root -pabc123 < /opt/school.sql
  2. mysql -u root -pabc123 -e 'show databases;'

  1. 基于mysql-bin.000002恢复增量,恢复所有
  2. mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -pabc123
  3. mysql -u root -pabc123 -e 'show databases;'

6.5断点恢复

6.5.1基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作 可以基于精准的位置跳过错误的操作

在数据库中插入数据并查看

在表t1中插入数据 

  1. insert into t1 values(3,'kcy','ych');
  2. insert into t1 values(4,'zq','ls');
  3. insert into t1 values(5,'wp','hj');

刷新生成日志000004 

  1. mysqladmin -u root -pabc123 flush-logs #刷新日志文件
  2. mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000006 #查看日志文件内容(新生成日志的前一个日志)

查看000003日志信息

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

1、只恢复数据4,不恢复数据5

 

  1. use school;
  2. delete from t1 where id='4';
  3. delete from t1 where id='5';
  4. mysqlbinlog --no-defaults --stop-position='5571' /usr/local/mysql/data/mysql-bin.000003|mysql -u root -pabc123
  5. ####数据恢复
  6. mysql -u root -pabc123 -e 'use school;select * from t1;'

2、只恢复数据5,其他不管

先删除刚刚恢复的数据4,以免看不到恢复效果

  1. mysql -u root -pabc123 -e 'use school;delete from t1 where id=4'
  2. #####删除数据4
  3. mysql -u root -pabc123 -e 'use school;select * from t1;'

恢复数据5

  1. mysqlbinlog --no-defaults --start-position='5571' /usr/local/mysql/data/mysql-bin.000003|mysql -u root -pabc123
  2. #####恢复数据5
  3. mysql -u root -pabc123 -e 'use school;select * from t1'

3、只恢复数据3和数据5,不要数据4

先删除数据3、4、5

  1. mysql -u root -pabc123 -e 'use school;delete from t1 where id=3'
  2. mysql -u root -pabc123 -e 'use school;delete from t1 where id=4'
  3. mysql -u root -pabc123 -e 'use school;delete from t1 where id=5'
  4. ######删除数据345
  5. mysql -u root -pabc123 -e 'use school;select * from t1'

恢复数据3

  1. mysqlbinlog --no-defaults --stop-position='5363' /usr/local/mysql/data/mysql-bin.000003|mysql -u root -pabc123
  2. #######恢复数据3
  3. mysql -u root -pabc123 -e 'use school;select * from t1'

恢复数据5

  1. mysqlbinlog --no-defaults --start-position='5654' /usr/local/mysql/data/mysql-bin.000003|mysql -u root -pabc123
  2. ######恢复数据5
  3. mysql -u root -pabc123 -e 'use school;select * from t1'

6.6基于时间节点

  1. mysqlbinlog --no-defaults --start-datetime='2024-03-25 17:28:11' /usr/local/mysql/data/mysql-bin.000003|mysql -u root -pabc123
  2. ######恢复数据4
  3. mysql -u root -pabc123 -e 'use school;select * from t1'

声明:本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号