小白带你学习linux的mysql数据库备份(三十)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 小白带你学习linux的mysql数据库备份(三十)



一、概述

数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方,以数据库丢失或损坏时进行恢复。数据库备份是数据库管理中必不可少的一项工作,通过备份可以保护数据库中的数据和业务

二、数据备份的重要性

备份的主要目的是灾难恢复,备份还可以测试应用,回滚数据修改,查询历史数据,审计等。

三、造成数据丢失的原因

程序错误

人为错误

运算失败

磁盘故障

灾难(如火灾、地震)和盗窃

四、备份类型

1、物理与逻辑角度

1.1物理备份

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

1.2冷备份

是在关闭数据库的时候进行的

1.3热备份

数据库处于运行状态,这种备份方法依赖于数据库的日志文件

1.4逻辑备份

对数据库逻辑组件(如表等数据库对象)的备份,表时为逻辑数据库结构(create database、create table等语句)和内容(insert语句或分割文本文件)的信息

2、数据库备份策略角度

1.1完整备份

完整备份指的是备份整个数据库的所有信息,包括数据库文件中所有的数据、表、视图、存储过程、触发器等。一般来说,完整备份是备份最全面、可靠的方式,但备份文件体积较大,备份时间也相对较长

1.2增量备份

增量备份指的是在完整备份的基础上,每隔一定时间备份新增的数据或修改的数据。这种备份方式可以节省备份时间和备份文件的空间,但需要配合特定软件或命令进行

五、常见的备份方法

1、物理备份

物理冷备份时需要在数据库处于关闭状态下,能够较好的保证数据库的完整性。物理冷备份以用于非核心业务,这类业务都允许中断,物理冷备份的特点就是速度快,恢复时也是最为简单的,通过直接打包数据库文件夹(/usr/local/mysql/data)来实现备份。

2、使用专用备份工具

mysqldump

mysqlhotcopy

3、通过启用二进制日志增量备份

MySQL支持增量备份,进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制。对执行备份点后进行的数据库更改所需的信息进行备份。如果进行增量备份(包含上次完全备份或增量备份以来发生的数据修改),需要刷新二进制日志。

4、第三方工具备份

Percona XtraBackup是一个免费的MySQL热备份软件,支持在线备份innodb和XtraDB,也可以支持MySQL表备份,

5、注意事项

定期备份,指定备份计划,严格遵守

除了完全备份之外,开启binlog日志功能很重要

使用统一的、容易理解的备份名称,推荐使用库名或者表名加上时间的命名规则

六、备份

1、完整备份

1.1打包数据库文件备份

位置

源码包的位置/usr/local/mysql/data/

rpm包的位置/var/lib/mysql

备份

在数据库中创建数据表,并写入数据

停止数据库服务

创建备份目录

按照特定格式对数据库目录进行备份

tar  czf  mysql_all-$(date  +%F).tar.gz  /var/lib/mysql/*

先关闭数据库,在备份数据。

systemctl stop mysqld.service
tar -czf mysql_ljt_$(date +%F).tar.gz /var/lib/mysql/*

还原

创建还原目录

模拟元数据丢失

我们先把mysql打包成一个压缩包,然后创建两个文件夹,把mysql移动到其中的一个文件夹,查看原来的mysql里面为0

tar -czf mysql_ljt_$(date +%F).taz /var/lib/mysql/*
mkdir /bak
mkdir /restore
mv /var/lib/mysql/* /bak
ll /var/lib/mysql

将解压之后的数据还原值原目录

tar xf mysql_ljt_2023-07-27.taz -C /restore/
cd /restore/
cd /var/lib/mysql/

解压数据包到restore

MV移动数据到mysql重启一下mysql登录进入mysql

mv /restore/var/lib/mysql/* /var/lib/mysql/
systemctl restart mysqld
mysql -uroot -p123456

登录查看

1.2备份工具备份

备份

工具           mysqldump

对单个库进行完全备份

mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] > /备份路径/备份文件名

-p可以不加密码

把原来的库删除在恢复测试一下

对多个库进行完全备份

mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… > /备份路径/备份文件名

对所有库进行完全备份

mysqldump -u用户名 -p[密码] [选项] --opt --all-databases > /备份路径/备份文件名

对表进行完全备份

mysqldump -u用户名 -p[密码] [选项] 数据库名 表名 > /备份路径/备份文件名

对表的结构进行备份          

mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名

mysqldump -uroot -p  -d ljt class >/mnt/class.1.sql

这个命令是用来导出 MySQL 数据库中的指定表的数据结构(仅包含表结构,不包含数据)到指定路径下的备份文件中。

  • -u [用户名]:指定数据库的用户名。
  • -p[密码]:指定数据库的密码。请注意在-p后面没有空格。
  • -d:表示只导出数据表的结构,不包含数据。
  • [数据库名]:指定要备份的数据库的名称。
  • [表名]:指定要备份的表的名称。
  • > /备份路径/备份文件名:表示将备份的结果输出到指定路径和文件名中。

还原

工具

source            登录mysql 数据库执行source  备份sql脚本路径

mysql

mysql -u用户名 -p[密码] < 库备份脚本的路径

mysql -u用户名 -p[密码] 库名 < 表备份脚本的路径

2、增量备份

1.1使用完全备份时,备份与恢复的时间过长
1.2增量备份就是备份自上一次备份之后增加的或改变的文件内容
1.3特点

没有重复数据,备份量不大,时间短

恢复麻烦,需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复

MySQL没有提供直接的增量备份办法,可以通过MySQL提供的二进制日志(binary logsbinlog))间接实现增量备份

1.4MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作。

二进制日志在启动MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。

1.5开启二进制日志备份功能

MySQL的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀,如 log-bin=mysql-bin,然后重启mysqld服务。默认此配置存在。

vim /etc/my.cnf
systemctl restart mysqld
ll /var/lib/mysql

生成了一个二进制日志

mysql回滚了一次生成另一个记录

mysqladmin -uroot -p flush-logs
ll /var/lib/mysql

使用mysqld --log-bin=文件存放路径/文件前缀 重新启动mysqld服务每周选择服务器负载较轻的时间段,或者用户访问较少的时间段进行备份

1.6增量恢复

应用场景

人为的SQL语句破坏了数据库

在进行下一次全备之前发生系统故障导致数据库丢失

在主从架构中,主库数据发生了故障,保证从库数据一致性

方法

一般恢复

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

格式: mysqldbinlog [--no-defaults] 增量备份文件 | mysql -u用户名 -p密码

基于时间点的恢复

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

格式:从日志开头截止到某个时间点的恢复:

mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒‘ 二进制日志 | mysql -u用户名 -p密码

从某个时间点到日志结尾的恢复

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒‘ 二进制日志 | mysql -u用户名 -p密码

从某个时间点到某个时间点的恢复

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒‘ --stop-datetime=’年-月-日 小时:分钟:秒‘ 二进制日志 | mysql -u用户名 -p密码

基于位置的恢复

可能在同一时间点既有错误的操作也有正确的操作,基于位置进行恢复更加准确

mysqlbinlog --stop-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码

mysqlbinlog --start-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码

七、实验

让我们来一次完整的二进制内容的操作吧

安装mysql(yum 方式)

添加数据库、表,录入数据

create database client;
create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10)) DEFAULT CHARSET=utf8;
insert into user_info values('000000006','张三','男','016','10');
insert into user_info values('000000007','李四','女','017','91');
insert into user_info values('000000008','王五','女','018','23');
select * from user_info;

先进行一次完全备份

mkdir /mysql_bak
mysqldump -uroot -p123456 client user_info > /mysql_bak/client_userinfo-$(date +%F).sql
mysqldump -uroot -p123456 --databases client > /mysql_bak/client-$(date +%F).sql
ls /mysql_bak/

添加两条内容,重启一下服务

vim /etc/my.cnf
 systemctl restart mysqld

server-id=1

log-bin=mysql-bin

进行一次日志回滚(生成新的二进制日志)

mysqladmin -uroot -p123456 flush-logs

出现了两个日志分别为000001,000002

继续录入新的数据

mysql -uroot -p123456
 use client;
 insert into user_info values('000000009','赵六','男','019','37');
 insert into user_info values('000000010','孙七','男','020','36');
 select * from user_info;

进行增量备

mysqladmin -uroot -p123456 flush-logs
ll /var/lib/mysql

mv  /var/lib/mysql/mysql-bin.000002 /mysql_bak/
mysqlbinlog -v /mysql_bak/mysql-bin.000002

模拟误操作删除user_info表

drop table client.user_info;
select * from client.user_info;

恢复完全备份

mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2023-07-28.sql

查看一下数据库

mysql -uroot -p123456
select * from client.user_info;

恢复增量备份

mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456
select * from client.user_info;

基于时间点的增量备份恢复

drop table client.user_info;
select * from client.user_info;

mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2023-07-28.sql 
 mysqlbinlog --no-defaults /mysql_bak//mysql-bin.000002  -v

完整备份回数据库查看一下时间点的文件

查看一下数据库的身份信息

select * from client.user_info;

查看一下赵六的结束时间戳

再看一下孙七的结束时间戳

只恢复赵六,不恢复孙七:

mysqlbinlog --no-defaults --stop-datetime="2023-07-28  9:42:43" /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456

只恢复孙七,不恢复赵六:

drop table client.user_info;
mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2023-07-28.sql

这就要使用另一个命令了需要的是开始时间

mysqlbinlog --no-defaults --start-datetime="2023-07-28  9:42:43" /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456

查看数据库

基于位置的恢复

drop table client.user_info;
mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2023-07-28.sql

只恢复赵六,不恢复孙七:

mysqlbinlog --no-defaults --stop-position='520' /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456
select * from client.user_info;

只恢复孙七,不恢复赵六:

drop table client.user_info;
mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2023-07-28.sql 
mysqlbinlog --no-defaults --start-position='520' /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456
select * from client.user_info;

 

相关文章
|
9天前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
28 4
|
21天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤:启动软件后,通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后,选择“数据导出”,确认导出的数据库及格式(推荐SQL格式),设置存储路径,点击“开始导出”。完成后,可在指定路径找到备份文件,建议定期备份并存储于安全位置。
161 11
|
2月前
|
关系型数据库 MySQL 数据库
RDS MySQL灾备服务协同解决方案构建问题之数据库备份数据的云上云下迁移如何解决
RDS MySQL灾备服务协同解决方案构建问题之数据库备份数据的云上云下迁移如何解决
|
9天前
|
关系型数据库 MySQL Linux
Linux 安装 mysql 【使用 tar.gz | tar.xz安装包-离线安装】
在Linux系统中使用tar.xz压缩包安装MySQL数据库的详细步骤。包括下载MySQL压缩包,解压到指定目录,创建mysql用户和组,设置目录权限,初始化MySQL,配置my.cnf文件,启动服务,以及修改root用户密码。此外,还提供了如何设置Windows远程登录MySQL服务器的方法。
Linux 安装 mysql 【使用 tar.gz | tar.xz安装包-离线安装】
|
24天前
|
关系型数据库 MySQL 数据库
Navicat备份数据库
涵盖`Navicat`数据库备份、数据安全及备份策略等主题。文库采用精美主题,提升阅读体验。
16 1
Navicat备份数据库
|
9天前
|
关系型数据库 MySQL Linux
Linux 安装 mysql【使用yum源进行安装】
这篇文章介绍了在Linux系统中使用yum源安装MySQL数据库的步骤,包括配置yum源、安装MySQL服务、启动服务以及修改root用户的默认密码。
Linux 安装 mysql【使用yum源进行安装】
|
1月前
|
NoSQL 关系型数据库 Redis
mall在linux环境下的部署(基于Docker容器),Docker安装mysql、redis、nginx、rabbitmq、elasticsearch、logstash、kibana、mongo
mall在linux环境下的部署(基于Docker容器),docker安装mysql、redis、nginx、rabbitmq、elasticsearch、logstash、kibana、mongodb、minio详细教程,拉取镜像、运行容器
mall在linux环境下的部署(基于Docker容器),Docker安装mysql、redis、nginx、rabbitmq、elasticsearch、logstash、kibana、mongo
|
27天前
|
SQL 数据库 数据安全/隐私保护
如何手动备份数据库?
如何手动备份数据库?
48 1
|
2月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
119 3
|
2月前
|
关系型数据库 MySQL Linux
【Azure 应用服务】在创建Web App Service的时候,选Linux系统后无法使用Mysql in App
【Azure 应用服务】在创建Web App Service的时候,选Linux系统后无法使用Mysql in App
【Azure 应用服务】在创建Web App Service的时候,选Linux系统后无法使用Mysql in App
下一篇
无影云桌面