Linux中MySQL的逻辑备份与恢复

发布于：2025-06-08 ⋅ 阅读:(23) ⋅ 点赞:(0)

一、数据库的介绍

1.1、数据库的组成

数据库是一堆物理文件的集合，主要包括：

  1. 数据文件 /var/lib/mysql

  2. 配置文件 => /etc/my.cnf

  3. 日志文件（主要是二进制日志文件）

1.2、存储引擎层

简单来说，就是数据的存储方式。在MySQL中，我们可以使用show engines查看当前数据库版本支持哪些引擎。

常见的数据存储引擎：InnoDB、MyISAM、NDB等。

1.3、MySQL体系结构

MySQL是由多个层次构成的DBMS软件：

 

1.4、MyISAM与InnoDB引擎的区别

  1. MyISAM引擎：

    • 擅长数据的查询，支持全文索引。

  2. InnoDB引擎：

    • 支持事务处理、行级锁、支持外键。==5.7也是支持全文索引==

1.5、存储层（数据文件与日志文件）

存储引擎的数据文件存储方式

首先创建一个数据库：

CREATE DATABASE db_it DEFAULT CHARSET=utf8;

当数据库创建完毕后，查看/mysql_3306/data文件夹：

[root@localhost data]# ll
总用量 110624
drwx------. 2 mysql mysql       77 4月  17 14:45 db_it
-rw-rw----. 1 mysql mysql 12582912 4月  17 14:26 ibdata1
drwx------. 2 mysql mysql     4096 4月  14 15:04 mysql
drwxr-xr-x. 2 mysql mysql       20 4月  14 14:53 test

MyISAM引擎的文件存储

mysql> USE db_it;
mysql> CREATE TABLE tb_user(id INT, name CHAR(1)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 创建完成后，查看db_it目录信息，发现产生了3个文件：

[root@localhost db_it]# ll
总用量 20
-rw-rw----. 1 mysql mysql   65 4月  17 14:44 db.opt
-rw-rw----. 1 mysql mysql 8586 4月  17 14:45 tb_user.frm
-rw-rw----. 1 mysql mysql    0 4月  17 14:45 tb_user.MYD
-rw-rw----. 1 mysql mysql 1024 4月  17 14:45 tb_user.MYI

文件说明：

  • .frm：框架文件，定义数据表结构

  • .MYI：INDEX索引，主要用于存放索引文件

  • .MYD：数据文件

 InnoDB引擎的文件存储

mysql> USE db_it;
mysql> CREATE TABLE tb_user(id INT, name CHAR(1)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 创建完成后，查看db_it目录信息，发现产生了2个文件：

[root@mysql ~]# ll /mysql_3306/data/db_it/
total 112
-rw-r----- 1 mysql mysql 61 Aug 28 15:24 db.opt
-rw-r----- 1 mysql mysql 8586 Aug 28 15:31 tb_user.frm
-rw-r----- 1 mysql mysql 98304 Aug 28 15:31 tb_user.ibd

文件说明：

  • .frm：框架文件，定义数据表结构

  • .ibd：索引文件+数据文件

其实InnoDB引擎不仅仅会产生以上两个文件，其在外部data目录中也会产生一个文件（确切来说不能叫做产生文件，而应该叫做共享文件）。

 所以由此可知，InnoDB引擎的数据备份不能简单的通过拷贝方式实现，必须使用专业的备份工具。

1.6、日志文件
日志类型 写入日志的信息
错误日志 (error log) 启动、运行或停止 mysqld 时遇到的问题
通用查询日志 (general log) 服务器收到的所有客户端连接和语句
二进制日志 (binary log) 数据更改语句 (更新、删除、更改表结构)
慢查询日志 (slow query log) 执行时间超过特定阈值的查询语句
DDL日志 (元数据日志) 由DDL语句执行的元数据操作
1.7、错误日志

作用：存放数据库的启动、停止或运行时的错误信息；

场景：用于数据库启动排错。

error错误日志的命令规则与存放的目录：/data目录下+主机名称.err

默认是开启的，可以通过修改 my.cnf 文件自定义，如：

# vim my.cnf

[mysqld]
...
log_error=data/数据目录/主机名称.err或mysql.err

 注意：默认存在 $datadir/hostname.err

1.8、二进制日志

作用：

二进制日志记录数据库的所有更改操作（DDL/DML/DCL），不包含select或者show这类语句。

场景：

  1. 用于主从复制：在主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作是的和主服务器的更改相同。

  2. 用于数据的恢复：用于数据的恢复操作。

默认binlog日志是关闭的，可以通过修改配置文件完成开启，如下：

# vim /usr/local/mysql/my.cnf

[mysqld]
log-bin=/usr/local/mysql/data/binlog
server-id=1

当我们更改了my.cnf配置文件，一定要记得重启MySQL服务器。service命令

二进制日志比较特殊，需要使用 mysqlbinlog 工具查看，如 mysqlbinlog 二进制日志文件。

二：备份类型和备份工具

2.1、备份类型

逻辑备份（mysqldump）

  • 备份的是建表、建库、插入等操作所执行SQL语句（DDL,DML,DCL）。

  • 适用于中小型数据库，效率相对较低一般。在数据库正常提供服务的前提下进行，如：mysqldump、mydumper等。

  • 备份实质：就是把要备份的数据导出成.sql或.txt文件

物理备份（tar、cp、xtrabackup）

  • 直接复制数据库文件

  • 适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

  • 一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份；

  • 如：tar、cp、xtrabackup（数据库可以正常提供服务lvm，snapshot、rsync等)

  • 备份的实质：对数据文件+配置文件+日志文件进行拷贝操作

在线热备（数据冗余、AB复制、主从复制）

  • MySQL的replication架构，如M-S|M-S-S|M-M-S等

  • 实时在线备份

2.2、备份工具

（1）社区版安装包中的备份工具

mysqldump（逻辑备份，只能全量备份)

1）企业版和社区版都包含

2）本质上使用SQL语句描述数据库及数据并导出

3）在MYISAM引|擎上锁表，Innodb引擎上锁行

4）数据量很大时不推荐使用

mysql hotcopy（物理备份工具）

1）企业版和社区版都包含

2）perl写的一个脚本，本质上是使用锁表语句后再拷贝数据

3)只支持MYISAM数据引擎

（2）企业版安装包中的备份工具

mysqlbackup

1）在线备份

2）增量备份

3）部分备份

4）在某个特定时间的一致性状态的备份

（3）第三方备份工具

XtraBackup

Xtrabackup是一个对InnoDB做数据备份的工具，支持寺在线热备份（备份时不影响数据读写），是商业备份工具InnoDBHotbackup的一个很好的替代品。

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup只能备份InnoDB和XtraDB3两种数据表，不能备份myisam类型的表。

innobackupex是将Xtrabackup进行封装的per脚本，所以能同时备份处理innodb和myisam的存储引擎，但在处理myisam时需要加一个读锁。

2.3、备份过程须考虑的因素

  • 必须制定详细的备份计划（策略）(备份频率、时间点、周期)

  • 备份数据应该放在非数据库本地并建议有多份副本

  • 必须做好数据恢复的演练（每隔一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据。）

  • 根据数据应用的场合、特点选择正确的备份工具

  • 对备份文件进行合理的归类和存放

  • 服务的可用性

2.4、mysqldump工具（逻辑备份工具）

本质：导出的是sql语句文件

优点：无论是什么存储引擎，都可以用mysqldump备成sql语句

缺点：速度较慢，导入时可能会出现格式不兼容的突发状况

无法直接做增量备提供三种级别的备份，表级，库级和全库级

（1）mysqldump基本语法
表级别备份
mysqldump [OPTIONS] database [tables] > b.sql
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

准备一些要备份的数据：

-- 创建数据库
mysql> CREATE DATABASE db_it DEFAULT CHARSET=utf8;

-- 使用数据库
mysql> USE db_it;

-- 创建表
CREATE TABLE tb_student (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    age TINYINT UNSIGNED DEFAULT 0,
    gender ENUM('male', 'female'),
    subject ENUM('ui', 'java', 'yunwei', 'python'),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO tb_student VALUES 
    (NULL, '刘备', 33, 'male', 'java'),
    (NULL, '关羽', 32, 'male', 'yunwei'),
    (NULL, '张飞', 30, 'male', 'python'),
    (NULL, '貂蝉', 18, 'female', 'ui'),
    (NULL, '大乔', 18, 'female', 'ui');
（2）mysqldump表级备份与还原

备份

案例：把db_it数据库中的tb_student数据表进行备份

# mkdir /bak
# mysqldump db_it tb_student > /tmp/sqlbak/tb_student.sql -p
Enter password:123

还原

# mysql 数据库名称 < sql文件位置 -p
Enter password:123
或
# mysql -u root -p
Enter password:123
mysql> use db_it
mysql> source sql文件的位置

2.5、mysqldump库级备份与还原

备份

案例：把db_it数据库进行备份

# mysqldump --databases db_it > /tmp/sqlbak/db_it.sql -p
Enter password:123

 还原

# mysql < sql文件位置 -p
Enter password:123
或
# mysql-uroot-p
Enter password:123
mysql> source.sql文件的位置

2.6、mysqldump全库级备份

在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志！！！  

 开启二进制日志

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=/mysql_3306/data/binlog
# mysqldump --all-databases --master-data --single-transaction > all.sql
选项 描述说明
--flush-logs, -F 开始备份前刷新日志（二进制日志）binlog.000001 => binlog.000002
--flush-privileges 备份包含mysql数据库时刷新授权表 => 刷新用户和授权信息
--lock-all-tables, -x MyISAM一致性，服务可用性（针对所有库所有表）
--lock-tables, -l 备份前锁表（针对要备份的库）
--single-transaction 适用InnoDB引擎，保证一致性，服务可用性
--master-data=2 表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行
--master-data=1 表示将二进制日志位置和文件名写入到备份文件，在dump文件中不注释这一行

三：实现增量备份（要点）

3.1、增量备份的核心思路

增量备份的关键：

  1. 要有全量备份作为基础

  2. 继续增删改数据

  3. 再次需要备份时，不需要进行全量备份，只需要备份binlog日志文件即可（因为binlog日志记录了增删改操作的所有SQL语句）

3.2、增量备份实验步骤

第一步：先准备数据
-- 创建数据库
CREATE DATABASE db_it DEFAULT CHARSET=utf8;

-- 使用数据库
USE db_it;

-- 创建表
CREATE TABLE tb_student (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    age TINYINT UNSIGNED DEFAULT 0,
    gender ENUM('male', 'female'),
    subject ENUM('ui', 'java', 'yunwei', 'python'),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO tb_student VALUES 
    (NULL, '刘备', 33, 'male', 'java'),
    (NULL, '关羽', 32, 'male', 'yunwei'),
    (NULL, '张飞', 30, 'male', 'python'),
    (NULL, '貂蝉', 18, 'female', 'ui'),
    (NULL, '大乔', 18, 'female', 'ui');
第二步：开启二进制日志，重启服务，然后进行全库备份
# 编辑 MySQL 配置文件
vim /etc/my.cnf

# 添加以下配置
[mysqld]
server-id=10
log-bin=/mysql_3306/data/binlog

# 重启 MySQL 服务
systemctl restart mysqld

# 清理临时备份文件
rm -rf /tmp/sqlbak/*

# 备份所有数据库
mysqldump \
--single-transaction \
--flush-logs \
--master-data=2 \
--all-databases \
> /bak/a.sql \
-p
第三步：继续对数据库进行增删改操作
-- 插入新记录
INSERT INTO tb_student VALUES (NULL, '小乔', 16, 'female', 'ui');
-- 删除记录
DELETE FROM tb_student WHERE id = 3;
第四步：突然发生了硬件故障，数据库丢失了
# 模拟故障情况，删除数据库
mysql -e "DROP DATABASE db_it;" -p
# 输入密码: 123
第五步：动员运维工程师开始进行数据恢复，马上把最新的二进制文件进行备份
# 复制二进制日志文件到备份目录
cp /usr/local/mysql/data/binlog.000003 /bak
第六步：先进行全库恢复
# 恢复全量备份
mysql < /tmp/sqlbak/all.sql -p
# 输入密码: 123
第七步：通过binlog增量备份还原数据到100%

学会读二进制日志文件，必须通过专业的工具

# 使用 mysqlbinlog 解析二进制日志文件，定位关键操作
/mysqlbinlog /bak/binlog.000003
# 重点关注事故临界点（如 DROP DATABASE）​
# 确认日志中操作位置的起始和结束偏移量
# 使用指定位置范围恢复数据
mysqlbinlog --start-position=201 --stop-position=629
 /bak/binlog.000003 | mysql -uroot -p

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布