一、关系数据库相关概念总结
名称 | 概念 |
relational(关系) | 由行和列组成的二维表,表内行和列次序不重要 |
row(行) | 每一行为一条记录record |
column(列) | 每一列为属性,字段,field域 |
primary key(主键) | 由一个或多个字段组成,作为记录的唯一标识,每张表只有一个且不为空 |
unique key(唯一键) | 由一个或多个字段组成,作为记录的唯一标识,可有多个且可以为空 |
domain(域) | 属性(列)的取值范围 |
二、关联类型总结
一对一联系(1:1)--在表A和表B内,表A的一个字段最多与表B中一个字段有关系,如:一张银行卡只能有一个密码
一对多联系(1:n)--表A和B表至少有多个字段有关系,且表B的每一个字段最多与表A一个字段有关,如:存款人可以在多个银行申请银行卡
多对多联系(m:n)--表A的每一个字段至少与表B的m个字段有关,并且表B的每一个字段与表A的至少n个字段有关,如:存款人和各家银行
三、MySQL范式总结
第一范式1NF:
无重复的列,每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有 多个值或者不能有重复的属性,确保每一列的原子性。除去同类型的字段,就是无重复的列
第二范式2NF:
第二范式必须先满足第一范式,属性完全依赖于主键,要求表中的每个行必须可以被唯一地区分,通常 为表加上每行的唯一标识主键PK,非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键
第三范式3NF:
满足第三范式必须先满足第二范式属性,非主键属性不依赖于其它非主键属性。第三范式要求一个数据 表中不包含已在其它表中已包含的非主关键字信息,非PK的字段间不能有从属关系
四、MySQL安装方式总结
4.1,程序包安装
[root@rocky-240220 network-scripts]# yum -y install mysql-server.x86_64 #安装
[root@rocky-240220 network-scripts]# systemctl status mysqld #启动服务[root@Rocky-test init.d]# mysql_secure_installation #运行初始化脚本提高安全性
4.2,通用二进制安装
MySQL :: Download MySQL Community Server (Archived Versions)https://downloads.mysql.com/archives/community/根据上述地址下载需要的MySQL服务器安装包即可
#1,下载安装包至服务器
#2,安装数据库相关包
[root@Rocky-test ~]# yum -y install libaio numactl-libs ncurses-compat-libs.x86_64
#创建MySQL用户和组[root@Rocky-test ~]# groupadd mysql
[root@Rocky-test ~]# useradd -r -g mysql -s /bin/nologin mysql#解压软件包并准备安装目录
[root@Rocky-test ~]# tar xvf mysql-5.7.44-linux-glibc2.12-x86_64.tar -C /opt
mysql-test-5.7.44-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@Rocky-test ~]# cd /opt
[root@Rocky-test opt]# tar xvf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz -C /usr/local#此处的/usr/local路径不能修改,是提前编译的路径
[root@Rocky-test opt]# cd /usr/local/
[root@Rocky-test local]# ln -s mysql-5.7.44-linux-glibc2.12-x86_64/ mysql
[root@Rocky-test local]# chown -R root.root /usr/local/mysql
[root@Rocky-test local]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@Rocky-test local]# . /etc/profile.d/mysql.sh
[root@Rocky-test local]# mkdir -p /home/data/mysql
[root@Rocky-test local]# vim /etc/my.cnf[mysqld]
datadir=/home/data/mysql
skip_name_resolve=1
socket=/home/data/mysql/mysql.sock
log_error=/home/data/mysql/mysql.log
pid_file=home/data/mysql/mysql.pid
[client]
socket=/home/data/mysql/mysql.sock
[root@Rocky-test local]# mysqld --initialize --user=mysql --datadir=/home/data/mysql
[root@Rocky-test local]# grep password /home/data/mysql/mysql.log
2024-04-29T06:14:44.456332Z 1 [Note] A temporary password is generated for root@localhost: 9ab8eiiSp!ei #此处为查看随机密码[root@Rocky-test local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@Rocky-test local]# chkconfig --add mysqld
[root@Rocky-test local]# service mysqld start
#登录MySQL修改密码[root@Rocky-test local]# mysql -uroot -p'9ab8eiiSp!ei' -h localhost
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password for root@'localhost'='Wlm@123';
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
[root@Rocky-test local]# mysql -uroot -p'Wlm@123'mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.44, for linux-glibc2.12 (x86_64) using EditLine wrapperConnection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.44 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /home/data/mysql/mysql.sock
Uptime: 4 min 50 secThreads: 1 Questions: 10 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.034
五、完成添加testdb库
[root@Rocky-test ~]# vim /etc/my.cnf #配置默认字符集
[client]
default-character-set=utf8mb4
[mysql]
prompt=(\\u@\\h)[\\d]>\\
default-character-set=utf8mb4[mysqld]
[root@Rocky-test ~]# mysql -uroot -p'Wlm@123'
(root@localhost)[(none)]>show variables like "character%"; #查看字符集是否修改
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)
(root@localhost)[(none)]>show Databases; #显示已有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.05 sec)(root@localhost)[(none)]>create database testdb; #创建数据库
Query OK, 1 row affected (0.03 sec)(root@localhost)[(none)]>create user test@'10.0.0.%' identified by 'Wlm@123'; #创建新用户
Query OK, 0 rows affected (0.14 sec)(root@localhost)[(none)]>grant all on testdb.* to test@'10.0.0.%'; #授权新用户访问数据库
Query OK, 0 rows affected (0.02 sec)#在其他机器安装数据库客户端进行访问测试
[root@rocky-240220 ~]# yum -y install mysql #安装MySQL客户端
[root@rocky-240220 ~]# mysql -utest -p'Wlm@123' -h'10.0.0.152' #远程登录MySQLmysql> show Databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| testdb |
+--------------------+
3 rows in set (0.01 sec)mysql> use testdb; #进入数据库
Database changed
mysql> create table students (id int auto_increment primary key ,name char(20),age tinyint); #创建新表
Query OK, 0 rows affected (0.14 sec)mysql> desc students; #查看表格结构
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| age | tinyint | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)mysql> insert students (name,age)values('xiao ming',20); #插入一条表数据
Query OK, 1 row affected (0.13 sec)
mysql> select * from students; #查看表内容
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | xiao ming | 20 |
+----+-----------+------+
1 row in set (0.01 sec)mysql> \q #退出数据库
Bye
六、完成DDL、DML用法总结
6.1,DDL(Data Definition Language)--数据定义语言
DDL常用语法:CREATE,DROP,ALTER
(root@localhost)[testdb]>create table teachers (id int unsigned auto_increment primary key,name char(20),age tinyint unsigned, gender enum('M','F') default 'M'); #创建teacher表,列id的属性为整数型,自动步进,主键;列name为20个以内字符;列age整数型;列gender内容为M或F,默认为M
Query OK, 0 rows affected (0.07 sec)(root@localhost)[testdb]>show table status like 'teachers'\G;
*************************** 1. row ***************************
Name: teachers
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2024-05-10 17:34:53
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
(root@localhost)[testdb]>alter table teachers add phone char(11) after age; #在age后添加新列phone,为11个字符内的字符串
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost)[testdb]>desc teachers;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)(root@localhost)[testdb]>drop table students; #删除students表
Query OK, 0 rows affected (0.03 sec)(root@localhost)[testdb]>show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| teachers |
+------------------+
1 row in set (0.00 sec)
6.2,DML(Data Manipulation Language)--数据操纵语言
DML常用语法:INSERT,UPDATE,DELEATE
(root@localhost)[testdb]>create table students like teachers; #创建teachers格式的表
Query OK, 0 rows affected (0.02 sec)(root@localhost)[testdb]>insert students (name,age,phone) values ('xiao ming',8,10086);
Query OK, 1 row affected (0.01 sec)
(root@localhost)[testdb]>insert students (name,age,gender) values ('xiao hong',9,'F');
Query OK, 1 row affected (0.01 sec)
(root@localhost)[testdb]>select * from students; #查看表内容
+----+-----------+------+-------+--------+
| id | name | age | phone | gender |
+----+-----------+------+-------+--------+
| 1 | xiao ming | 8 | 10086 | M |
| 2 | xiao hong | 9 | NULL | F |
+----+-----------+------+-------+--------+
2 rows in set (0.00 sec)(root@localhost)[testdb]>update students set phone=12315 where id=1; #修改1行的phone
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0(root@localhost)[testdb]>select id,phone from students;
+----+-------+
| id | phone |
+----+-------+
| 1 | 12315 |
| 2 | NULL |
+----+-------+
2 rows in set (0.00 sec)(root@localhost)[testdb]>delete from students where id=1; #删除id为1的行
Query OK, 1 row affected (0.01 sec)(root@localhost)[testdb]>select * from students;
+----+-----------+------+-------+--------+
| id | name | age | phone | gender |
+----+-----------+------+-------+--------+
| 2 | xiao hong | 9 | NULL | F |
+----+-----------+------+-------+--------+
1 row in set (0.00 sec)(root@localhost)[testdb]>truncate table students; #清空表数据
Query OK, 0 rows affected (0.05 sec)(root@localhost)[testdb]>select * from students;
Empty set (0.00 sec)(root@localhost)[testdb]>show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| students |
| teachers |
+------------------+
2 rows in set (0.00 sec)
七、MySQL架构原理总结
连接层 | 服务于C/S程序或程序所需的连接处理,身份验证等 |
核心服务层 | MySQL的核心部分,负责大部分存储引擎提供的功能 |
存储引擎层 | 底层数据存取操作实现部分,负责存储和获取所有存储的数据 |
数据存储层 | 主要将数据存储在文件系统上,并完成与存储引擎的交互 |
八、myisam与innodb存储引擎区别介绍
MySQL5.5.5以前默认数据库引擎为myisam,后续更换为innodb。
myisam | innodb |
不支持事务日志 | 支持事务,适合处理大量短期事务 |
表级锁定 | 行级锁定 |
写入时不能读取,读取时不能写入 | 读写阻塞受事务隔离级别影响 |
只缓存索引 | 缓存索引和数据 |
不支持外键约束和聚簇索引 | 支持聚簇索引 |
不支持高并发 | 支持高并发 |
读取数据较快,占用资源较少 | 支持全文索引 |
崩溃恢复性较差 | 崩溃恢复性较好 |
九、MySQL索引总结
索引:是排序的快速查找的特殊数据结构,定义作为查找条件的字段上,又称为键key ,索引通过存储引 擎实现优点:索引可以降低服务需要扫描的数据量,减少了IO次数索引可以帮助服务器避免排序和使用临时表索引可以帮助将随机I/O转为顺序 I/O缺点:占用额外空间,影响插入速度常见索引类型:二叉树,B-Tree,B+Tree,聚簇索引和非聚簇索引(数据和索引是否存储在一起)
(root@localhost)[hellodb]> create index index_name on students (name); #创建索引
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0(root@localhost)[hellodb]> show index from students\G; # 显示索引
*************************** 1. row ***************************
Table: students
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
(root@localhost)[hellodb]> drop index index_name on students; #删除索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
十、事务ACID总结
事务 :一组原子性的 SQL 语句,或一个独立工作单元事务日志:记录事务信息,实现 undo,redo 等故障恢复功能ACID 特性:A: 原子性(atomicity);整个事务中的所有操作要么全部成功执行,要么全部失败后回滚C: 一致性(consistency);数据库总是从一个一致性状态转换为另一个一致性状态, 类似于能量守恒定律I: 隔离性(Isolation);一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,可以实现并发D :持久性(durability);一旦事务提交,其所做的修改会永久保存于数据库中显示事务:begin结束事务:commit 提交,执行操作rollback 回滚,不执行操作自动提交:set autocommit={1|0}MySQL默认为1,为0时关闭自动提交
隔离级别 | 缺点 |
读未提交(read uncommited) | 产生脏读(只有日志提交者可读到修改数据) |
读提交(read commited) | 产生不可重复读(多次读取提交数据,每次读取结果不一致) |
可重复读(repeatable read) | 幻读(读取数据时,即使事务已提交修改数据,读取结果为初始值) |
序列化(serializable) | 产生事务阻塞,并发性能差 |
十一、事务日志工作原理总结
事务日志:
redo log:记录某数据块被修改后的值,在数据更新前先记录该日志,可以用来恢复未写入data file的已成功事务更新的数据
undo log:保存与执行的操作相反的操作,即记录某数据被修改前的值,可以用来在事务失败时进行回滚
十二、MySQL日志类型及日志启动过程
事务日志:记录事务进行操作的过程
通用日志:记录对数据库的通用操作
错误日志:mysqld启动和关闭过程输出的事件,运行中产生的错误等
慢查询日志:记录执行查询时长超出指定时长的操作
二进制日志:记录数据库操作后的结果(作为备份)
#启用通用日志(root@localhost)[hellodb]>set global general_log=1; #开启通用日志
Query OK, 0 rows affected (0.01 sec)(root@localhost)[hellodb]>select @@general_log; #查看是否开启通用日志
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)(root@localhost)[hellodb]>select @@log_output; #查看通用日志格式
+--------------+
| @@log_output |
+--------------+
| FILE |
+--------------+
1 row in set (0.00 sec)
(root@localhost)[hellodb]>select @@general_log_file; #查看通用日志存储位置
+-------------------------------+
| @@general_log_file |
+-------------------------------+
| /var/lib/mysql/Rocky-test.log |
+-------------------------------+
1 row in set (0.00 sec)#启用慢日志(root@localhost)[hellodb]>set global slow_query_log=1; #开启慢日志
Query OK, 0 rows affected (0.01 sec)(root@localhost)[hellodb]>select @@slow_query_log; #查看是否开启慢日志
+------------------+
| @@slow_query_log |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)(root@localhost)[hellodb]>set long_query_time=3; #设置查询阈值为3s,默认为10s
Query OK, 0 rows affected (0.00 sec)(root@localhost)[hellodb]>select @@long_query_time; #查看慢日志阈值
+-------------------+
| @@long_query_time |
+-------------------+
| 3.000000 |
+-------------------+
1 row in set (0.00 sec)#启用二进制日志[root@Rocky-test ~]# vim /etc/my.cnf #指定log_bin路径
[mysqld]log_bin=/var/lib/mysql(root@localhost)[(none)]> select @@sql_log_bin; #必须同时开启该值才能打开二进制日志
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)(root@localhost)[(none)]> select @@binlog_format; #查看log_bin日志格式
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set, 1 warning (0.00 sec)(root@localhost)[(none)]> show master status; #查看二进制日志
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 570 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)(root@localhost)[(none)]> select @@binlog_expire_logs_seconds; #查看二进制自动删除时间,单位秒
+------------------------------+
| @@binlog_expire_logs_seconds |
+------------------------------+
| 2592000 |
+------------------------------+
1 row in set (0.00 sec)
二进制文件刷新方式:1,mysqld服务重启2,二进制数据写满1G后自动切换3,使用MySQL命令flush logs
十三、二进制日志不同格式使用场景总结
事务日志 | 二进制日志 |
在线日志,记录数据库实时使用过程,不能删除 | 离线日志,记录数据库结果,可删除 |
记录事务执行的过程,包括提交和未提交 | 只记录事务提交的过程 |
只支持innodb | 支持innodb和myisam |
二进制日志记录的三种格式:
statement基于"语句 " 记录:仅 记录语句, 日志量较少(部分语句代表的操作可能因时间或数据库状态产生不同的影响,因此该格式有失败的风险);row基于"行 " 记录: 记录数据,日志量较大(记录语句代表的操作,数据量更大但是更加安全)mixed混合模式: 让系统自行判定该基于哪种方式进行
十四、MySQL备份介绍
14.1,备份类型
完全备份:整个数据集部分备份:只备份数据子集,如部分库或表增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快, 还原复杂差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单冷备:读、写操作均不可进行,数据库停止服务温备:读操作可执行;但写操作不可执行热备:读、写操作均可执行MyISAM:温备,不支持热备InnoDB:都支持
14.2,mysqldump工具
mysqldump database #备份指定的数据库和指定的多表,但是数据库定义不备份
mysqldump -B database db_name #备份指定的数据库
mysqldump -A database #备份所有数据库
mysqldump -E --events #备份所有event scheduler
mysqldump -F --flush-log #备份前回滚日志
mysqldump --source-data #适用于主从复制多机使用
14.3,mysqldump备份实例
#开启数据库二进制日志
(root@localhost)[(none)]>select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)(root@localhost)[(none)]>select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)#备份数据库
[root@Rocky-test ~]# mysqldump -uroot -p'Wlm@123' -A -F --single-transaction --source-data=2 > /home/backup_`date +%F`.sql #完全备份数据库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@Rocky-test ~]# ll /home/
-rw-r--r--. 1 root root 1295364 May 15 17:30 backup_2024-05-15.sql#修改数据库数据
[root@Rocky-test ~]# mysql -uroot -p'Wlm@123';
(root@localhost)[(none)]>drop database testdb;
Query OK, 2 rows affected (0.09 sec)#关闭二进制进行还原
(root@localhost)[(none)]>set sql_log_bin=0; #暂时关闭二进制日志
Query OK, 0 rows affected (0.00 sec)(root@localhost)[(none)]>source /home/backup_2024-05-15.sql #还原二进制日志
(root@localhost)[testdb]>show databases; #验证已还原数据库
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.00 sec)(root@localhost)[testdb]>set sql_log_bin=1; #重新开启二进制日志
Query OK, 0 rows affected (0.01 sec)
十五、编写crontab备份MySQL数据
目标:基于xtrabackup备份工具,每周1,周5进行完全备份,周2到周4进行增量备份
#开启数据库二进制日志
(root@localhost)[(none)]>select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)(root@localhost)[(none)]>select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)#编写备份脚本
[root@Rocky-test ~]# mkdir /home/backup
[root@Rocky-test ~]# chown -R mysql.mysql /home/backup/[root@Rocky-test ~]# yum -y install percona-xtrabackup-83-8.3.0-1.1.el8.x86_64.rpm #下载xtrabackup安装包至本地进行安装
[root@Rocky-test ~]# vim xtra_backup.sh
#!/bin/bash
week=`date +%u`
dir=/home/backupmain(){
case $week in
1)
rm -rf $dir/*
function_ql
;;
2)
if [ -d $dir/base ];then
function_zl1
else
printf "the first backup is not exist\n"
fi
;;
4)
if [ -d $dir/base ];then
function_zl2
else
printf "the first backup is not exist\n"
fi
;;
5)
rm -rf $dir/*
function_ql
;;
*)
break
esac}function_ql()
{
xtrabackup -uroot -p'Wlm@123' --backup --target-dir=$dir/base
}function_zl1()
{
xtrabackup -uroot -p'Wlm@123' --backup --target-dir=$dir/inc1 --incremental-basedir=$dir/base
}function_zl2()
{
xtrabackup -uroot -p'Wlm@123' --backup --target-dir=$dir/inc2 --incremental-basedir=$dir/inc1
}main
[root@Rocky-test ~]# chmod +x xtra_backup.sh
#设置周期任务
[root@Rocky-test ~]# crontab -e #每天23点执行脚本
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
0 23 * * * /root/xtra_backup.sh