homework 6

at528668686

已于 2024-05-10 21:56:39 修改

阅读量1k

点赞数 28

文章标签：数据库 sql 运维 linux vim

于 2024-05-09 23:51:12 首次发布

本文链接：https://blog.csdn.net/at528668686/article/details/138595766

版权

1. 总结关系型数据库相关概念，关系，行，列，主键，惟一键，域。

关系型数据库相关概念
- 关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
- 行row：表中的每一行，又称为一条记录record
- 列column：表中的每一列，称为属性，字段，域field
- 主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主键, 主键字段不能为空NULL
- 唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而且UK字段可以为NULL
- 域domain：属性的取值范围，如，性别只能是’男’和’女’两个值，人类的年龄只能0-150

2. 总结关联类型，1对1，1对多，多对多关系。可以自行设计表进行解释。

范例：

1对1：两个实体之间存在一对一的关联关系，即每个实体都只有一个与之关联的另一个实体。这种关联关系通常用于表示两个实体之间具有排他性的关系。对于上述示例中，学生id与该学生就是一对一的关联关系，该学生id只属于该学生对象。

1对多：在一个实体中，多个实体与之关联。这种关联关系通常用于表示一对多的关系。对于上述示例中，一个班级下有多名学生，班级对象可以和多名学生对象关联，属于一对多关系。

多对多：两个实体之间存在多对多的关联关系，即一个实体中有多个与另一个实体关联的实体，反之亦然。这种关联关系通常用于表示两个实体之间存在复杂的关系，上述示例中，一个学生可以选修多门课程，同时一门课程也可以被多个学生选修。通过第三张“学生选修课程记录表”让学生与被选课程关联起来。多位学生选修一门课程，一位学生选修多门课程，就属于多对多关系。

3. 总结mysql设计范式

第一范式：1NF
无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。
除去同类型的字段，就是无重复的列说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
第二范式：2NF
第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键。
第三范式：3NF
满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系。
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，不同的规范要求被称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般数据库只需满足第三范式(3NF）即可。掌握范式的目的是为了在合适的场景下违反范式。

4. 总结Mysql多种安装方式，及安全加固，并总结mysql配置文件。

1）总结Mysql安装方式

1/ 直接安装mysql服务器软件包

# rucky /centos ：yum -y install mysql-server

# ubuntu： apt -y install mysql-server

2/ 源代码编译安装

不建议，可使用二进制格式程序包，二进制程序包相比源码只是少了个编译过程。

3/ 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

访问 MySQL :: Download MySQL Community Server 下载对应程序包。

选择合适的glibc版本下载，可通过ldd --version -v得到系统glibc版本。

# 通用二进制安装MySQL 8.0.37
# 安装依赖
yum -y install libaio numactl-libs ncurses-compat-libs
# 添加用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 准备程序文件
mkdir /app
cd /app
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.37-linux-glibc2.28-x86_64.tar.xz
tar -xf mysql-8.0.37-linux-glibc2.28-x86_64.tar.xz
cd /usr/local/
ln -s /apps/mysql-8.0.37-linux-glibc2.28-x86_64 mysql
chown -R root.root /usr/local/mysql/

# 准备环境变量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

# 准备配置文件
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
log-error=/data/mysql/mysql.log
pid-file=/tmp/mysql.pid
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock

# 初始化数据库文件并提取root密码
mkdir -pv /data/mysql
mysqld --initialize --user=mysql --datadir=/data/mysql
awk '/temporary password/{print $NF}' /data/mysql/mysql.log #提取root密码

# 准备服务脚本和启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
systemctl start mysqld

# 修改生成的随机密码为指定密码
mysqladmin -uroot -p'i=HdIar15n5x' password 123456

#测试登录
mysql -uroot -p123456

2）安全加固

运行mysql_secure_installation

设置“验证密码”组件吗？
更改root的密码？
删除匿名用户？
不允许远程登录？
删除测试数据库并访问它?
现在重新加载权限表吗？

3）mysql配置文件

MySQL的配置文件通常被称为my.cnf，它是一个文本文件，包含了MySQL服务器的各种参数和设置。
MySQL配置文件的主要部分：

vim /etc/my.cnf
[client] #配置服务器
socket=/tmp/mysql.sock #指定MySQL服务器要使用的套接字文件的路径。
user=root # 指定默认连接用户名为root，仅做配置做测试用，不安全不建议使用。
password=123456 #指定默认密码为123456

[mysqld] #这部分的配置影响mysqld服务器，即MySQL服务器。
datadir=/data/mysql #指定MySQL数据文件的路径。
skip_name_resolve=1 #禁止主机名解析，建议使用
log-error=/data/mysql/mysql.log #指定MySQL服务器错误日志的路径
socket=/tmp/mysql.sock #指定MySQL服务器要使用的套接字文件的路径

1. 完成将server和client端的mysql配置默认字符集为utf8mb4;

vim /etc/my.cnf #编辑mysql配置文件
[mysqld] # 设置服务器默认字符集
character_set_server=utf8mb4

[mysql] # 针对mysql客户端设置默认字符集
default-character-set=utf8mb4

[client] # 针对所有客户端设置默认字符集
default-character-set=utf8mb4

5. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin.创建host表，字段（id，host，ip，cname等）

1）获取SQL命令的帮助

可以通过 HELP contents 命令查看帮助文档的目录列表，运行结果如下：

根据上面运行结果列出的目录，可以选择某一项进行查询。例如使用 HELP Data Types; 命令查看所支持的数据类型，运行结果如下：

如果还想进一步查看某一数据类型，如 INT 类型，可以使用 HELP INT;命令，运行结果如下：

另外，还可以查询某命令，例如使用 HELP CREATE database命令查询创建数据库的语法，运行结果如下所示：

2）添加testdb库字符集utf8, 排序集合utf8_bin.创建host表，字段等信息

CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
use `testdb`;
CREATE TABLE `host` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`host` varchar(30) NOT NULL COMMENT '主机名',
`ip` varchar(32) NOT NULL COMMENT 'ip地址',
`cname` varchar(30) COMMENT '真实名称',
PRIMARY KEY (`id`)
);

6. 根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

1）DDL: Data Defination Language 数据定义语言，用于定义或修改数据库结构，包括创建、修改、删除数据库、表、列等。 (常见的有：CREATE，DROP，ALTER)

2）DML: Data Manipulation Language 数据操纵语言，用于插入、更新、删除表中的数据。 (常见的有：INSERT，DELETE，UPDATE)

-- 插入两条记录
INSERT INTO `student` VALUES(null,'张明',20,'F');
INSERT INTO `student` VALUES(null,'李红',21,'M');
-- 修改一条记录
UPDATE `student` SET age=22 where id=1;
--删除李红学生信息
DELETE from student WHERE name='李红';

7. 总结mysql架构原理

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

# 网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API技术与MySQL建立连接。

# 服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。
1）连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
2）系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等
3）SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
4）解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。
5）查询优化器（Optimizer）：当"解析树"通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。
6）缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

# 存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

# 系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。
1）日志文件
——错误日志（Error log）
默认开启，可通过执行show variables like '%log_error%';查询
——通用查询日志（General query log）
记录一般查询语句，可通过执行show variables like '%general%';查询
——二进制日志（binary log）
记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。
show variables like '%log_bin%'; //是否开启
show variables like '%binlog%'; //参数查看
show binary logs;//查看日志文件
——慢查询日志（Slow query log）
记录所有执行时间超时的查询SQL，默认是10秒。
show variables like '%slow_query%'; //是否开启
show variables like '%long_query_time%'; //时长
2）配置文件: 用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
3）数据文件
db.opt 文件：记录这个库的默认使用的字符集和校验规则。
frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。
MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。
MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI文件。
ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。
ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等。
ib_logfile0、ib_logfile1 文件：Redo log 日志文件。
4）pid 文件
pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。
5）socket 文件
socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

8. 总结myisam和Innodb存储引擎的区别。

myisam和Innodb存储引擎的主要区别

事务支持
- InnoDB：支持ACID事务，可以执行提交、回滚以及具有崩溃恢复能力的操作。
- MyISAM：不支持事务处理，适合于读密集的表，或者那些不需要事务的应用程序
锁机制
- InnoDB：提供行级锁
- MyISAM：只提供表级锁
外键约束
- InnoDB：支持外键约束
- MyISAM：不支持
数据缓存和索引缓存
- InnoDB：拥有自己的缓存系统，既缓存数据也缓存索引。
- MyISAM：只缓存索引，不缓存数据。
存储限制
- InnoDB：最大64TB
- MyISAM：最大256TB
默认设置和使用
- InnoDB：从MySQL5.5.5版本开始被设置为默认的存储引擎。
- MyISAM：更早期版本的MySQL中是默认的存储引擎。

9. 总结mysql索引作用，同时总结哪些查询不会使用到索引。

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现。

作用
- 索引可以降低服务需要扫描的数据量，减少IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机IO转换为顺序IO
查询不会使用到索引的情况：
- 全表扫描：当需要返回大部分或全部数据行时，优化器可能选择直接进行全表扫描而不使用索引。
- 索引列上有计算或函数：在索引列上使用函数或运算符（例如 WHERE YEAR(column) = 2021）可能导致索引失效。
- 非最左前缀查询：在复合索引中，如果查询没有使用索引的最左边的列，则索引可能不会被使用。
- LIKE 语句以通配符开头：如 LIKE ‘%abc’ 会使得索引失效，而 LIKE ‘abc%’ 可以使用索引。
- 数据类型不一致：如果查询条件中的数据类型与索引列不匹配，MySQL可能不会使用索引。
- 隐式转换：由于数据类型的隐式转换，比如将字符串和数字比较，可能导致索引失效。
- 低基数列：如果索引列的值非常重复（低基数），优化器可能会认为全表扫描更有效。
- NULL 值查询：某些情况下，对含有NULL值的列进行查询可能不会使用索引。
- OR 条件：当使用 OR 连接多个条件，且不是所有涉及的列都有索引时，可能不会使用索引。
- 使用了索引合并策略：在某些情况下，MySQL会采取索引合并策略，即结合多个单列索引代替复合索引。

10. 总结事务ACID事务特性

ACID事务特性

A：原子性（Atomicity）
整个事务中的所有操作要么全部成功执行，要么有失败后全部回滚。
如果事务的一部分操作失败，整个事务将回滚到开始状态，之前的操作就像从未发生过一样。
C：一致性（Consistency）
数据库总是从一个一致性状态转换为另一个一致性状态。
一致性意味着数据库中的数据将会符合所有预定规则，在事务开始和结束时都保持数据的完整性。
I：隔离性（Isolation）
一个事务所作出的操作在提交之前，是不能被其他事务所见。隔离有多种隔离级别，实现并发。
隔离性可以通过“锁定”事务所用到的数据来实现。但在多数情况下，隔离性是通过“多版本并发控制”（MVCC，Multi-Version Concurrency Control）来实现的。
D：持久性（Durability）
一旦提交事务，其所做的修改会永远保存于数据库中。

11. 总结事务日志工作原理。

事务日志：transaction log
● redo log：记录某数据块被修改后的值，数据更新前先记录redo log（WALWrtite Ahead Log），可以用来恢复未写入data file的已成功事务更新的数据。（事务提交后数据还没来得及写到磁盘中发生系统崩溃，设备启动后会根据redo log恢复事务修改后的内容）
● undo log：保存与执行的操作相反的操作，即记录某数据被修改前的值，可用来在事务失败时进行rollback
事务型存储引擎自行管理和使用，建议和数据文件分开存放
redo log整体流程

事务日志工作原理：
1，先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
2，生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值。
3，当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式。
4，定期将内存中修改的数据刷新到磁盘中。

12. 总结mysql日志类型，并说明如何启动日志。

日志类型：

● 事务日志：transaction log

事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为预写式日志write ahead logging。事务日志文件：ib_logfile0,ib_logfile1，事务型存储引擎自行管理和使用，建议和数据文件分开存放

● 错误日志，error log

默认开启，可通过执行show variables like '%log_error%'; 查询当前状态。

vim /etc/my.cnf #修改配置文件systemctl restart mysqld #重启服务生效

● 通用日志：general log

记录一般查询语句，可通过执行show variables like '%general%';查询查询当前状态。

vim /etc/my.cnf #修改配置文件systemctl restart mysqld #重启服务生效

● 慢查询日志：slow query log

记录所有执行时间超时的查询SQL，默认是10秒。

show variables like '%slow_query%'; //是否开启
show variables like '%long_query_time%'; //时长

vim /etc/my.cnf #修改配置文件

systemctl restart mysqld #重启服务生效

● 二进制日志：binary log

记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。
show variables like '%log_bin%'; #是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
show variables like '%sql_log_bin%'; #指定文件位置；默认ON，表示启用二进制日志功能，上述两项都开启才可以

show binary logs; #查看日志文件

● 中继日志：relay log

在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

13. 总结二进制日志的不同格式的使用场景。

binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT

1）STATEMENT：只记录SQL语句本身，而不是记录对行的修改。

优点：日志文件体积较小，因为不需要为每一行改变记录详细信息。对于那些改变大量数据的操作来说，效率更高。
缺点：某些情况下可能无法提供确定性的复制结果，比如包含当前时间或自动递增的ID等非确定性函数的SQL语句。并不是所有的语句都能准确以这种方式复制（例如，涉及到用户定义的函数或存储程序）。
合适的使用场景：当所有更改都是确定性的，或者可以保证所有非确定性操作在所有服务器上产生相同的结果时。数据库负载主要由确定性语句组成，如没有包含当前时间或随机数生成器的查询。

2）ROW：记录每一行数据的更改

优点：提供了最高级别的数据改变记录，使复制具有确定性，避免了因执行环境差异导致的数据不一致问题。
缺点：日志文件可能会非常大，尤其是在大量数据变动时。日志文件中包含的数据可能导致复制性能降低。
合适的使用场景：当涉及到很多非确定性语句，或者依赖于特定执行环境的结果时使用。系统中使用了触发器、存储过程等，且这些对象内部逻辑依赖于当前的数据状态。需要确保主从服务器之间的数据一致性，不能容忍任何偏差。

3）MIXED：结合了STATEMENT和ROW两种模式的优点，MySQL自动选择使用哪种模式。

优点：大部分情况下使用STATEMENT格式，节省空间并提高效率。在必要的时候自动切换至ROW格式，保证复制的准确性。
缺点：由于自动选择机制，可能会使得复制行为难以预测。管理起来可能比单一格式更复杂。
合适的使用场景：如果不确定使用哪种格式比较好，MIXED可以作为一个折中的选择。希望同时获得STATEMENT格式的性能优势和ROW格式的数据一致性。当所有更改都是确定性的，或者对确定结果不敏感的时候使用。

14. 总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。

1）mysql备份类型

● 完全备份，部分备份
○ 完全备份：整个数据集
○ 部分备份：只备份数据子集，如部分库或表
● 完全备份、增量备份、差异备份
○ 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
○ 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
● 冷、温、热备份
○ 冷备：读写操作均不可进行，数据库停止服务
○ 温备：读操作可执行，写操作不能执行
○ 热备：读写操作均可执行。MyISAM支持温备，不支持热备；InnoDB都支持
● 物理和逻辑备份
○ 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多空间，速度快
○ 逻辑备份：从数据库导出数据另存，与存储引擎无关，占用空间少，速度慢

2）mysqldump数据库备份与恢复验证

# 备份所有数据库
mysqldump -uroot -p123456 -F -A --single-transaction > all.bak.sql
# 还原数据库
mysql -uroot -p123456 < all.bak.sql

3）xtrabackup数据库备份与恢复验证

可到https://www.percona.com/downloads下载合适版本，版本与MySQL版本要对应。如xtrabackup 8.0.28对应MySQL 8.0.28
参考文档：Downloaded RPM packages - Percona XtraBackup

——备份所有数据库
# 安装xtrabackup
yum localinstall percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm -y
mkdir /backup
xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
# 出现completed OK!字样时，备份完成。

——还原数据库
# 在目标主机上还原
# 注意：恢复主机MySQL服务停止，并且数据目录为空
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
# 出现completed OK!字样时，准备完成。
xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录，注意：数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
3）还原属性
chown -R mysql:mysql /data/mysql
4）启动服务
systemctl start mysqld

15. 编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

1）通过mysqldump每天备份所有mysql数据库

创建一个备份MySQL的shell脚本。本机的MySQL用户名为root，密码为123456，并且备份所有数据库：
vim mysqldump_backup.sh （加入一下内容）
#!/bin/bash
# MySQL 用户名、密码和主机
USER="root"
PASSWORD="123456"
HOST="10.0.0.6"
# 创建一个新目录，用于存储当天的备份文件
DAY_DIR=$(date +"backup_%Y-%m-%d")
mkdir -p $DAY_DIR
# 备份MySQL数据库
mysqldump -u $USER -p$PASSWORD -h $HOST --all-databases > $DAY_DIR/all_databases_$(date +"%Y-%m-%d_%H-%M-%S").sql
# 压缩备份文件
tar -czf $DAY_DIR.tar.gz $DAY_DIR
# 删除临时目录
rm -rf $DAY_DIR

# 脚本增加可执行权限
chmod +x mysqldump_backup.sh

# 增加cron任务计划
crontab -e （加入一下内容）
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

# 每天凌晨 2:30 AM 使用 mysqldump 备份所有 MySQL 数据表
30 2 * * * /root/mysqldump_backup.sh

2）基于xtrabackup进行备份

vim xtrabackup_script.sh
#!/bin/bash

#定义MySQL用户名和密码
MYSQL_USER="root"
MYSQL_PASSWORD="123456"

# 定义备份存放目录
BACKUP_DIR_ROOT="/mnt/DBbak/xtrabackup"
FULL_BACKUP_DIR="${BACKUP_DIR_ROOT}/full"
INCR_BACKUP_DIR="${BACKUP_DIR_ROOT}/incremental"

# 完全备份
do_full_backup() {
   if [ ! -d "${FULL_BACKUP_DIR}" ] ;then
       mkdir -p "${FULL_BACKUP_DIR}"
   fi
   CURRENT_FULL_DIR="${FULL_BACKUP_DIR}/$(date +%F)"
   xtrabackup --backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD --target-dir=$CURRENT_FULL_DIR
   #记录最后完全备份时间
   date "+%F %T" > "${FULL_BACKUP_DIR}/last_full_backup.txt"

}

基于上次完整备份做增量备份
do_incremental_backup() {
   if [ ! -d "${INCR_BACKUP_DIR}" ] ;then
       mkdir -p "$INCR_BACKUP_DIR"
   fi
   # 获取上次完全备份时间
   LAST_FULL_BACKUP_TIME=$(cat "${FULL_BACKUP_DIR}/last_full_backup.txt")
   # echo $LAST_FULL_BACKUP_TIME
   # 基于上次完整备份做增量备份
   INCR_BASE_DIR="${FULL_BACKUP_DIR}/$(date -d "${LAST_FULL_BACKUP_TIME}" +%F)"
   # echo $INCR_BASE_DIR
   CURRENT_INCR_DIR="${INCR_BACKUP_DIR}/$(date +%F_%H-%M-%S)"

   # 如果没有最后一次完全备份的记录，则执行完全备份
   if [ ! -d "${INCR_BASE_DIR}" ] || [ ! -f "${FULL_BACKUP_DIR}/last_full_backup.txt" ];then
       # echo "do_full_backup in do_incremental_backup"
       do_full_backup
       return
   fi

   # 创建增量备份目录并执行增量备份
   mkdir -p "$CURRENT_INCR_DIR"
   xtrabackup --backup --incremental-basedir=$INCR_BASE_DIR --target-dir=$CURRENT_INCR_DIR --user=$MYSQL_USER --password=$MYSQL_PASSWORD

}

# 选择备份类型
#[ "$1" = "full" ] 中的"$1" = "full"和"$1"="full"不一样，=号左右没有空格会判定为一个命令，有空格则被看做一个表达式
if [ "$1" = "full" ];then
   echo do_full_backup
   do_full_backup
elif [ "$1" = "incremental" ];then
   echo do_incremental_backup
   do_incremental_backup
else
   echo "Invalid backup type: $1. Use 'full' or 'incremental'."
fi

脚本增加可执行权限
chmod +x /mnt/xtrabackup_script.sh

增加cron任务计划
crontab -e （加入一下内容）
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
# 每周一（1）和周五（5）凌晨 2:30 AM 进行完全备份
30 2 * * 1 xtrabackup_script.sh full > /mnt/full_backup.log 2>&1
30 2 * * 5 xtrabackup_script.sh full > /mnt/full_backup.log 2>&1
# 每周二（2）、周三（3）和周四（4）凌晨 1:00 AM 进行增量备份
0 1 * * 2-4 /mnt/xtrabackup_script.sh incremental > /mnt/incremental_backup.log 2>&1