基于Linux操作系统中的MySQL数据库备份（三十三）

逻辑备份是以逻辑方式备份数据库中的数据，备份的是逻辑对象（如表、视图、存储过程等）的备份，表示为逻辑数据库结构（create database、 create table等语句）、内容（insert语句或分割文本文件）的信息的定义和数据。逻辑备份一般通过导出数据库的SQL语句或者使用备份工具来实现。逻辑备份的恢复过程相对较慢，但是可以选择性地恢复特定的数据。逻辑备份适用于跨数据库平台或者需要数据筛选的场景。对数据库逻辑组件（如表等数据库对象）

（二）数据库备份策略角度

1、完整备份

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

2、增量备份

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

五、常见的备份方法

（一）物理备份

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

（二）使用专用备份工具

mysqldump

mysqlhotcopy

（三）通过启用二进制日志增量备份

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

（四）第三方工具备份

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

（五）注意事项

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

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

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

六、备份

（一）完整备份

1、打包数据库文件备份

1.1、位置

源码包安装的MySQL的位置/usr/local/mysql/data/

rpm包或yum安装的的位置/var/lib/mysql

1.2、备份

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

create database emm;

use emm;

create table students(
编号 int not null,
姓名 varchar(20) not null,
性别 char(10) not null
)character set utf8mb4 collate utf8mb4_unicode_ci;

INSERT INTO students (编号, 姓名,性别) VALUES ('1','张三', '男'),
('2','李四', '女'),
('3','王五', '男'),
('4','赵六', '男'),
('5','小明', '男'),
('6','小帅', '男'),
('7','小美', '女'),
('8','小红', '女'),
('9','大明', '男'),
('10','大帅', '男'),
('11','大美', '女'),
('12','大红', '女');

select * from students;

停止数据库服务

systemctl stop mysqld

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

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

1.3、还原

创建还原目录

mkdir restore

模拟元数据丢失

创建备份目录

mkdir bak

将/var/lib/mysql/* 移动到bak目录下

mv /var/lib/mysql/* ~/bak/

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

tar xf mysql_all-2023-07-27.tar.gz -C restore/

cd /var/lib/mysql/
mv ~/restore/var/lib/mysql/* /var/lib/mysql/

启动服务

systemctl start mysqld

登录查看

2、备份工具备份

2.1、备份

工具

2.1.1、mysqldump

对单个库进行完全备份

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

mysqldump -uroot -p123123 --databases emm > /root/mysql-bak-$(date +%F).sql

警告的意思是：在命令行界面上使用明文密码可能存在安全隐患。

对多个库进行完全备份

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

mysqldump -uroot -p --databases emm aaa wjy > /root/mysql-bak-$(date +%Y-%m-%d_%H:%M).sql

对所有库进行完全备份

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

mysqldump -uroot -p --opt --all-databases > /root/mysql-bak-$(date +%Y-%m-%d_%H:%M).sql

对表进行完全备份

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

对表的结构进行备份

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

mysqldump -uroot -p -d emm students > /root/mysql-bak-$(date +%Y-%m-%d_%H:%M).sql

2.2、还原

工具

2.2.1、source

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

登录mysql查看数据库列表

mysql -uroot -p123123
show databases;

备份wjy数据库

mysqldump -uroot -p123123 --databases wjy > /root/wjy.sql

登录MySQL数据库，删除wjy库，查看

mysql]# mysql -uroot -p123123

drop database wjy;

show databases;

source /root/wjy.sql

show databases;

2.2.2、mysql

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

登录mysql查看数据库列表

mysql -uroot -p123123
show databases;

备份emm数据库

mysqldump -uroot -p123123 --databases emm > /root/mysql-bak-$(date +%F).sql

删除数据库并查看数据库

drop database emm;
show databases;

使用命令恢复备份

mysql -uroot -p < /root/mysql-bak-2023-07-27.sql

登录数据库查看验证

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

mysql -uroot -p emm < mysql-bak-2023-07-27_19:45.sql

（二）增量备份

1、使用完全备份时，备份与恢复的时间过长

2、增量备份就是备份自上一次备份之后增加的或改变的文件内容

3、特点：

3.1、没有重复数据，备份量不大，时间短

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

3.3、MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binary logs（binlog））间接实现增量备份

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

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

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

vim /etc/my.cnf

5、开启二进制日志备份功能

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

vim /etc/my.cnf

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

6、增量恢复

6.1、应用场景

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

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

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

6.2、方法

6.2.1、一般恢复

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

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

6.2.2、基于时间点的恢复

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

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

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

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

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

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

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

6.2.5、基于位置的恢复

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

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

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

（三）实验案例

1、要求：

北京****有限公司的用户信息数据库为client，用户资费数据表为user_info

请为该公司每周进行完全备份

每天为该公司进行增量备份

新增加的用户信息如表所示

创建数据库

create database client;

创建数据表

use 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'),
('000000007','李四','女','017','91'),
('000000008','王五','女','018','23');

查看数据表

select * from user_info;

2、进行一次完全备份

mkdir /mysql_bak

进行备份

mysqldump -uroot -p123123 client user_info > /mysql_bak/client_userinfo-$(date +%F).sql

查看验证

编辑配置文件添加内容

vim /etc/my.cnf

需添加的内容

server-id=1
log-bin=mysql-bin

重启服务

systemctl restart mysqld

3、进行一次日志回滚（生成新的二进制日志）

4、将当前日志刷新到磁盘中，并创建新的日志文件

mysqladmin -uroot -p flush-logs

5、添加新的数据

use client;

insert into user_info values
('000000009','赵六','男','019','37'),
('000000010','孙七','男','020','36');

查看表格信息

select * from user_info;

6、进行增量备份

mysqladmin -uroot -p123123 flush-logs

查看验证

7、查看新操作的日志记录

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

8、模拟误操作删除数据表

drop table client.user_info;

9、恢复完全备份

mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2023-07-27.sql

查看数据表

select * from client.user_info;

恢复增量备份

mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p123123

进入数据库查看

mysql -uroot -p123123

select * from client.user_info;

基于时间点的增量恢复

先删除数据表再进行查看

drop table client.user_info;

select * from client.user_info;

恢复初始完整备份

mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2023-07-27.sql

解析和显示二进制日志文件中

mysqlbinlog -v /mysql_bak/mysql-bin.000002

在这里可以看出日志生成事件编号起始为293，结束事件事件为454

恢复事件为454的日志

mysqlbinlog --no-defaults --start-datetime="2023-07-27 22:14:33" /mysql_bak/mysql-bin.000002 | mysql -uroot -p123123

mysqlbinlog是一个用于解析MySQL二进制日志文件的工具，它可以读取MySQL二进制日志文件的内容，并以可读的方式输出。

--no-defaults选项表示不使用默认的配置文件。

--start-datetime选项指定了要开始解析的时间点，这里是2023年7月27日22点14分33秒。

/mysql_bak/mysql-bin.000002是要解析的二进制日志文件的路径和文件名。

|是一个管道符号，将mysqlbinlog的输出作为输入传递给后面的mysql命令。

mysql命令用于执行解析后的MySQL语句，-uroot -p123123表示使用用户名root和密码123123登录MySQL数据库。

基于位置的恢复

mysql -uroot -p123123

drop table client.user_info;

恢复一下最初的完整备份

mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2023-07-27.sql

查看

mysqlbinlog -v /mysql_bak/mysql-bin.000002

mysqlbinlog --no-defaults --start-position='293' /mysql_bak/mysql-bin.000002 | mysql -uroot -p123123

mysqlbinlog --no-defaults --stop-position='485' /mysql_bak/mysql-bin.000002 | mysql -uroot -p123123

Wjy¹

关注

2
点赞
踩
16

收藏

觉得还不错? 一键收藏
打赏
1
评论
基于Linux操作系统中的MySQL数据库备份（三十三）

数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方，以便在数据库丢失或损坏时进行恢复。数据库备份是数据库管理中必不可少的一项工作，通过备份可以帮助恢复数据，减少数据丢失的风险。
复制链接

扫一扫