Linux中的mysql逻辑备份与恢复

一、安装mysql社区服务

二、数据库的介绍

三、备份类型和备份工具

---

一、安装mysql社区服务

这是小编自己写的，没有安装的去看看

Linux换源以及yum安装nginx和mysql-CSDN博客

二、数据库的介绍

2.1 数据库的组成

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

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

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

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

2.2 存储引擎层

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

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

MyISAM与InnoDB引擎的区别

1. MyISAM引擎：

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

2. InnoDB引擎：

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

2.3 存储层（数据文件与日志文件 - InnoDB）

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

首先创建一个数据库：

create database lsh DEFAULT CHARSET=utf8;

MyISAM引擎的文件存储

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

InnoDB引擎的文件存储

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

2.4 日志文件

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

二进制日志

作用：

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

场景：

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

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

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

# vim /etc/my.cnf[mysqld]
log-bin=/usr/local/mysql/data/binlog
server-id=1

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

systemctl restart mysqld

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

mysqlbinlog 【文件名】

三、备份类型和备份工具

3.1 备份和冗余的区别

• 备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

• 冗余：数据有多份冗余，但不等于备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

3.2 备份类型

逻辑备份（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等

• 实时在线备份

3.3 备份工具

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

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

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

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

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

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

mysql hotcopy（物理备份工具）

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

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

3)只支持MYISAM数据引擎

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

mysqlbackup

1）在线备份

2）增量备份

3）部分备份

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

第三方备份工具

XtraBackup

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

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

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

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

3.3 mysqldump工具（逻辑备份工具）

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

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

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

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

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');

mysqldump表级备份与还原

备份

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

# mkdir /bak
# mysqldump -uroot -p db_it tb_student > /bak/tb_student.sql

还原

-- 还原之前先把表删了
drop table tb_studnet;# mysql -u root -pmysql> use db_it
mysql> source /bak/tb_student.sql

mysqldump库级备份与还原

备份

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

# mysqldump -uroot -p --databases db_it > /bak/db_it.sql

还原

drop database db_it;# mysql -uroot -pmysql> source /bak/db_it.sql

mysqldump全库级备份

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

开启二进制日志

vim /etc/my.cnfserver-id=1
log-bin=/mysql_3306/data/binlog

# mysqldump -uroot -p --all-databases --master-data --single-transaction > /bak/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.4 实现增量备份（重要）

增量备份的核心思路

增量备份的关键：

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

2. 继续增删改数据

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

增量备份实验步骤

第一步：先准备数据

-- 创建数据库
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