MySQL数据库

白佳宝

于 2024-05-25 16:19:42 发布

阅读量453

点赞数 5

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/cghcgyhbc/article/details/139100345

版权

一.数据库原理

1.相关概念

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

2.联系类型

一对一关系（1：1）：在表A或表B中创建一个字段﹐存储另一个表的主键值如: 一个人只有一个身份证
一对多联系（1：n）：外键, 如: 部门和员工
多对多联系（m：n）：增加第三张表, 如: 学生和课程

3.数据库的正规化分析

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF）,其余范式以次类推。一般数据库只需满足第三范式(3NF）即可

第一范式：1NF
无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列
说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据
库
第二范式：2NF
第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键
第三范式：3NF
满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系

二.MySQL安装和基本使用

1.MySQL安装

（1）centos8可直接yum安装

yum -y install mysql-server

（2）二进制安装MySQL8.0.27

#安装相关包
yum -y install libaio numactl-libs ncurses-compat-libs

#用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

#准备程序文件
wget https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
tar mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz -C /usr/local
cd /usr/local
ln -s mysql-8.0.27-linux-glibc2.12-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]
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_storage_engine=InnoDB  
character_set_server=utf8mb4

[client]
socket=/data/mysql/mysql.sock
character_set_client=utf8mb4

#初始化数据库文件并提取root密码 (在/data/mysql 会自动生成，但是/data/必须事先存在)
生成随机密码：
mysqld --initialize --user=mysql --datadir=/data/mysql

awk '/temporary password/{print $NF}' /data/mysql/mysql.log
LufavlMka6,!

生成空密码：
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

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

#修改口令
mysqladmin -uroot -p'LufavlMka6,!'   password magedu

mysqladmin -uroot   password magedu

#测试登录
mysql -uroot -pmagedu

2.MySQL基本使用

（1）MySQL安全加固

修改默认的管理员用户名和密码。
移除不必要的用户账户和数据库。
启用防火墙，限制对 MySQL 端口的访问。
定期备份数据库，以防止数据丢失。
定期更新 MySQL 服务器和相关组件的补丁和安全更新。

（2）MySQL配置文件说明

MySQL 的配置文件通常是 my.cnf文件，它包含了 MySQL 服务器的各种配置选项。您可以通过编辑这个文件来配置 MySQL 服务器的各种参数，包括字符集设置、缓冲区大小、日志文件路径等。

三.SQL语句

1.创建数据库

命令：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’
CHARACTER SET ‘character set name’
COLLATE ‘collate name’;

范例：创建testdb库，字符集utf8, 排序集合utf8_bin

mysql> CREATE DATABASE testdb
    -> CHARACTER SET utf8
    -> COLLATE utf8_bin;
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)

2.DDL语句

（1）创建表

命令：
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, …)
#字段信息
col type1
PRIMARY KEY(col1,…)
INDEX(col1, …)
UNIQUE KEY(col1, …)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

范例：.创建host表，字段（id，host，ip，cname等）
mysql> use testdb;
Database changed
mysql> CREATE TABLE host(
    -> id INT NOT NULL AUTO_INCREMENT,
    -> host VARCHAR(255) NOT NULL,
    -> ip VARCHAR(15) NOT NULL,
    -> cname VARCHAR(255),
    -> PRIMARY KEY(id)
    -> )CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| host             |
+------------------+
1 row in set (0.00 sec)

mysql> desc host;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| host  | varchar(255) | NO   |     | NULL    |                |
| ip    | varchar(15)  | NO   |     | NULL    |                |
| cname | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

（2）查看表

查看表：SHOW TABLES [FROM db_name]
查看表创建命令：SHOW CREATE TABLE tbl_name
查看表结构：DESC [db_name.]tb_name 或 SHOW COLUMNS FROM [db_name.]tb_name
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name
查看支持的engine类型：SHOW ENGINES;

范例：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)

mysql> show create table host\G;
*************************** 1. row ***************************
       Table: host
Create Table: CREATE TABLE `host` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host` varchar(255) COLLATE utf8mb3_bin NOT NULL,
  `ip` varchar(15) COLLATE utf8mb3_bin NOT NULL,
  `cname` varchar(255) COLLATE utf8mb3_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin
1 row in set (0.00 sec)

mysql> desc host;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| host  | varchar(255) | NO   |     | NULL    |                |
| ip    | varchar(15)  | NO   |     | NULL    |                |
| cname | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> show columns from host;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| host  | varchar(255) | NO   |     | NULL    |                |
| ip    | varchar(15)  | NO   |     | NULL    |                |
| cname | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show table status like 'host'\G;
*************************** 1. row ***************************
           Name: host
         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: NULL
    Create_time: 2024-05-25 10:46:21
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb3_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

（3）修改表

命令：
ALTER TABLE ‘tbl_name’
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

范例：增加列
mysql> ALTER TABLE host ADD gender VARCHAR(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc host;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| host   | varchar(255) | NO   |     | NULL    |                |
| ip     | varchar(15)  | NO   |     | NULL    |                |
| cname  | varchar(255) | YES  |     | NULL    |                |
| gender | varchar(10)  | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#修改字段名
mysql> ALTER TABLE host
    -> CHANGE host hostname VARCHAR(255);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE host CHANGE cname status VARCHAR(255);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc host;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| hostname | varchar(255) | YES  |     | NULL    |                |
| ip       | varchar(15)  | NO   |     | NULL    |                |
| status   | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#修改字段属性
mysql> ALTER TABLE host MODIFY status varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc host;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| hostname | varchar(255) | YES  |     | NULL    |                |
| ip       | varchar(15)  | NO   |     | NULL    |                |
| status   | varchar(50)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

（4）删除表

命令：drop table name

3.DML语句

(1)INSERT语句

命令：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
{VALUES | VALUE} ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE #如果重复更新之
col_name=expr
[, col_name=expr] … ]

范例：全值插入
mysql> desc host;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| hostname | varchar(255) | YES  |     | NULL    |                |
| ip       | varchar(15)  | NO   |     | NULL    |                |
| status   | varchar(50)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert host values(0,'rocky8','10.0.0.8',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from host;
+----+----------+----------+--------+
| id | hostname | ip       | status |
+----+----------+----------+--------+
|  1 | rocky8   | 10.0.0.8 | 1      |
+----+----------+----------+--------+
1 row in set (0.00 sec)

#部分插入
mysql> insert host (hostname,ip)values('centos8','10.0.0.18');
Query OK, 1 row affected (0.00 sec)

mysql> select * from host;
+----+----------+-----------+--------+
| id | hostname | ip        | status |
+----+----------+-----------+--------+
|  1 | rocky8   | 10.0.0.8  | 1      |
|  2 | centos8  | 10.0.0.18 | NULL   |
+----+----------+-----------+--------+
2 rows in set (0.00 sec)

(2)UPDATE语句

命令：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]

范例：
mysql> update host set status=0 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from host;
+----+----------+-----------+--------+
| id | hostname | ip        | status |
+----+----------+-----------+--------+
|  1 | rocky8   | 10.0.0.8  | 1      |
|  2 | centos8  | 10.0.0.18 | 0      |
+----+----------+-----------+--------+
2 rows in set (0.00 sec)

（3）DELETE语句

命令：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]

范例：
mysql> delete id=1 from host;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1 from host' at line 1
mysql> delete from host where status=0;
Query OK, 1 row affected (0.00 sec)

mysql> select * from host;
+----+----------+----------+--------+
| id | hostname | ip       | status |
+----+----------+----------+--------+
|  1 | rocky8   | 10.0.0.8 | 1      |
+----+----------+----------+--------+
1 row in set (0.00 sec)

四.MySQL架构和性能优化

1.MySQL架构

（1）MySQL架构原理

连接层：连接层负责处理客户端与服务器之间的连接和认证。当客户端发起连接请求时，连接层负责验证客户端的身份，并在验证通过后建立连接。
管理服务和工具：管理服务和工具包括了用于管理和监控 MySQL 服务器的各种工具和服务，例如 MySQL Workbench、MySQL Enterprise Monitor 等。
SQL 接口：SQL 接口层接收客户端发送的 SQL 查询，并将其传递给服务器端进行处理。这一层还包括了解析 SQL 查询、优化查询执行计划等功能。
查询优化器：查询优化器负责分析 SQL 查询，并生成最优的执行计划。它会考虑索引、表大小、连接方式等因素，以确定最有效的查询执行方式。
缓存和存储引擎：MySQL 的架构中包含了缓存层和存储引擎层。缓存层用于缓存查询结果，以提高查询性能。存储引擎层负责实际的数据存储和检索，MySQL 支持多种存储引擎，如 InnoDB、MyISAM 等。
日志模块：日志模块记录了数据库的变更操作，包括事务日志、错误日志、慢查询日志等。这些日志对于数据库的恢复、性能分析和故障排查非常重要。
并发控制：并发控制层负责处理多个客户端同时对数据库进行操作时的并发访问控制。它通过锁机制和事务隔离级别来确保数据的一致性和完整性。
数据存储：数据存储层实际上是存储引擎层，负责将数据存储在磁盘上，并提供数据的检索和更新功能。

2.存储引擎

（1）InnoDB引擎和MyISAM引擎

Feature	InnoDB	MyISAM
事务支持	支持事务处理，具有事务安全性	不支持事务处理
行级锁定	支持行级锁定，可以提供更好的并发性,多个事务可以同时访问同一表的不同行	不支持行级锁定（表级）
外键约束	支持外键约束，可以确保数据的完整性，实现数据表之间的关联	不支持外键约束
崩溃恢复	支持崩溃恢复功能能，具有更好的容错性，可以在数据库崩溃后进行恢复	对崩溃恢复支持较弱，容易出现数据损坏或丢失
全文搜索	不支持全文搜索索引，需要使用额外的全文搜索引擎来实现全文搜索功能	支持全文搜索索引
空间占用	占用空间相对较小	占用更多的磁盘空间
索引类型	使用聚簇索引	使用非聚簇索引
表级锁定	支持表级锁定和行级锁定，可以选择	支持表及锁定
并发性能	适用于高并发环境	适用于静态数据或只读数据的场景
数据缓存	使用缓冲池来缓存数据和索引，可以提高数据访问的速度	不使用缓冲池，数据和索引存储在文件中

（2）管理存储引擎
查看mysql支持的存储引擎

show engines;

查看当前默认的存储引擎

show variables like '%storage_endine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like 'tb_name';
show create table tb_name;

设置表的存储引擎：

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

3.服务器配置和状态

（1）服务器选项(不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效)
#查看myqld的可用选项列表

mysqld --verbose --help

#获取mysqld当前启动选项

mysqld --print-defaults

查看服务器选项

select variables like 'option_name';

修改服务器选项

vim /etc/my.cnf
[mysqld]
option_name='value'

（2）服务器系统变量（不能持久保存，服务器重启就失效了）

全局变量

查询全局变量

show global variables;    只查看所有全局变量
show variables like 'var_name';  #var_name=变量名
select @@'var_name'

修改全局变量（修改后重新进入数据库显示修改成功，或其他会话进入）

set global var_name='value'  #value=变量值（0 1 或 ON OFF 或 NULL FELAS）

会话变量

查询会话变量

show variables like 'var_name';
select @@'var_name';

修改会话变量（只修改一个会话）

set var_name='value'

状态变量（分为全局和会话，只读，不可更改）

查状态变量

show global status like 'var_name'; 查看全局状态变量
show status status like 'var_name'; 查看会话状态变量

4.INDEX索引

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

索引可以降低服务需要扫描的数据量，减少IO次数
索引可以帮助服务器避免排序和使用临时表
所以可以帮助将随机I/O转为顺序I/O

缺点：

占用额外空间，影响插入速度

（2）管理索引
创建索引

CREATE (UNIQUE)INDEX index_name ON table_name(column1 [ASC|DESC],column2[ASC|DESC],...);
#index_name  创建索引名,表中唯一 UNIQUE 创建唯一索引 table_name 指定在那个表上创建索引 （column1 [ASC|DESC],column2[ASC|DESC],...） 指定要索引的列表名，可以指定一列或多列 ASC|DESC 索引的排序顺序

ALTER TABLE table_name ADD INDEX index_name(column1[ASC|DESC],column2[ASC|DESC]);

删除索引

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

显示引索信息

SHOW INDEX FROM table_name\G

(3)EXPLAIN工具和profile工具

范例：索引的作用
mysql> select * from testlog where salary=49670;
+-------+-----------+--------+
| id    | name      | salary |
+-------+-----------+--------+
|    13 | wang31312 |  49670 |
| 46248 | wang25789 |  49670 |
+-------+-----------+--------+
2 rows in set (0.00 sec)

mysql> explain select * from testlog where salary=49670;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | testlog | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100309 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index idx_salary on testlog(salary);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from testlog
    -> ;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| testlog |          0 | PRIMARY    |            1 | id          | A         |       92196 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| testlog |          1 | idx_salary |            1 | salary      | A         |       92142 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

mysql> explain select * from testlog where salary=49670;
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | testlog | NULL       | ref  | idx_salary    | idx_salary | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show profiles;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|   100015 | 0.00008475 | creat index idx_salary on testlog(salary)        |
|   100016 | 0.11936000 | create index idx_salary on testlog(salary)       |
|   100017 | 0.00066550 | select * from testlog where salary=49670      
|   100029 | 0.01522175 | select * from testlog where name='wang38730'     |
+----------+------------+--------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

5.并发控制

（1）锁机制

读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务），多个读互不阻塞
写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外
事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，
但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况
也称为锁冲突

（2）事务

ACID特性：

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

事务管理

显示启动事务：

BEGIN
BEGIN WORK
START TRANSACTION

结束事务

COMMIT     #提交，相当于vi中的wq保存退出
ROLLBACK   #回滚，相当于vi中的wq！不保存退出

注意：只有事务型存储引擎中的DML语句（INSERT、UPDATE、DELETE）方能支持此类操作

自动提交（建议关闭自动提交）

set autocommit={0|1} #默认是 1 开启  建议 0 关闭

设置保存点

SAVEPOINT savepoint_name;

回滚到保存点

ROLLBACK TO SAVEPOINT savepoint_name;

查看事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

（3）事务隔离级别

READ UNCOMMITTED ：可读取到未提交数据，产生脏读
READ COMMITTED：可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次
读取数据不一致
REPEATABLE READ ：可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE ：可串行化，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务），或者未提交的写事务阻塞读和写事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

查看事务隔离级别

select @@transaction_isolation;

设置事务隔离级别

SET transaction_isolation='READ-UNCOMMITTED|READCOMMITTED|REPEATABLE-READ|SERIALIZABLE'
或
vim /etc/my.cnf
[mysqld]
transaction_isolation=SERIALIZABLE

6.日志管理

（1）事务日志（只支持innodb）

innodb事务日志相关配置

mysql> show variables like '%innodb_log%' ;
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_file_size               | 50331648 |    #每个日志文件大小
| innodb_log_files_in_group          | 2        |    #日志组成员个数
| innodb_log_group_home_dir          | ./       |    #事务文件路径
+------------------------------------+----------+

修改 innodb事务日志相关配置

[root@rocky8 ~]# vim /etc/my.cnf

[mysqld]
innodb_log_group_home_dir = /data/mysql
innodb_log_file_size=50331648 
innodb_log_files_in_group=3

事务日志优化

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
2比0更安全一点

mysql> select @@innodb_flush_log_at_trx_commit;  #默认值是1
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

[root@rocky8 ~]# vim /etc/my.cnf

[mysqld]
innodb_flush_log_at_trx_commit=0|1|2

（2）错误日志

错误日志路径

mysql> show global variables like 'log_error';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| log_error     | /data/mysql/mysql.log |
+---------------+-----------------------+
1 row in set (0.00 sec)

错误日志详细程度

mysql> show global variables like 'log_error_verbosity';  #默认是2
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_error_verbosity | 2     |
+---------------------+-------+
1 row in set (0.00 sec)

[root@rocky8 ~]# vim /etc/my.cnf

[mysqld]
log_error_verbosity=1|2|3

（3）通用日志

开启通用日志（默认不开启）

mysql> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global general_log=on;
Query OK, 0 rows affected (0.01 sec)

[root@rocky8 ~]# ll /data/mysql -t   （生成通用日志文件rocky8.log）
total 128652
-rw-r----- 1 mysql mysql      238 May 23 09:26  rocky8.log
-rw-r----- 1 mysql mysql 16777216 May 23 08:37  undo_001
-rw-r----- 1 mysql mysql 16777216 May 23 08:37  undo_002
-rw-r----- 1 mysql mysql 33554432 May 23 08:35  mysql.ibd

通用日志配置

mysql> show global variables like '%general_log%';
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| general_log      | ON                     |    #通用日志开启开关
| general_log_file | /data/mysql/rocky8.log |    #通用日志文件路径
+------------------+------------------------+
2 rows in set (0.01 sec)

mysql> show global variables like 'log_output';  #默认通用日志存放在文件中
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global log_output="table";            #修改通用日志存放到表中
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show table status like 'general_log'\G;
*************************** 1. row ***************************
           Name: general_log
         Engine: CSV
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-05-21 21:06:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb3_general_ci
       Checksum: NULL
 Create_options: 
        Comment: General log
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from mysql.general_log\G;
*************************** 1. row ***************************
  event_time: 2024-05-23 09:51:20.700260
   user_host: skip-grants user[root] @ localhost []
   thread_id: 7
   server_id: 1
command_type: Query
    argument: 0x73686F7720676C6F62616C207661726961626C6573206C696B6520276C6F675F6F757470757427
*************************** 2. row ***************************
  event_time: 2024-05-23 09:52:31.379793
   user_host: skip-grants user[root] @ localhost []
   thread_id: 7
   server_id: 1
command_type: Query
    argument: 0x73656C65637420404067656E6572616C5F6C6F675F66696C65
*************************** 3. row ***************************
  event_time: 2024-05-23 09:52:51.010980
   user_host: skip-grants user[root] @ localhost []
   thread_id: 7
   server_id: 1
command_type: Query
    argument: 0x73686F7720676C6F62616C207661726961626C6573206C696B6520272567656E6572616C5F6C6F672527

[root@rocky8 ~]# head /data/mysql/mysql/general_log.CSV
"2024-05-23 09:51:20.700260","skip-grants user[root] @ localhost []",7,1,"Query","show global variables like 'log_output'"
"2024-05-23 09:52:31.379793","skip-grants user[root] @ localhost []",7,1,"Query","select @@general_log_file"
"2024-05-23 09:52:51.010980","skip-grants user[root] @ localhost []",7,1,"Query","show global variables like '%general_log%'"

（4）慢查询日志

慢查询日志开启（默认不开启OFF）

mysql> show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

[root@rocky8 ~]# vim /etc/my.cnf  #开启慢查询日志

[mysqld]
slow_query_log=on

慢查询日志配置

mysql> select @@long_query_time;        #慢查询的阈值，默认是10秒
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'slow_query_log_file';  #慢查询日志文件
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log_file | /data/mysql/rocky8-slow.log |
+---------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'log_queries_not_using_indexes'; #记录不使用索引的记录，默认是OFF不记录的
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

慢查询分析工具mysqldumpslow

[root@rocky8 ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

-s 是表示按照何种方式排序，后面可以带如下排序方式
c: 访问次数
l: 锁定时间
r: 返回记录的行数
t: 查询时间
al:平均锁定时间
ar:平均返回记录的行数
at:平均查询时间
-t 是top n的意思，即为返回前面多少条的数据
-g 后边可以写一个正则匹配模式，大小写不敏感的

[root@rocky8 ~]# mysqldumpslow -s c -t 2 /data/mysql/rocky8-slow.log

Reading mysql slow query log from /data/mysql/rocky8-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # User@Host: skip-grants user[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use hellodb;
  SET timestamp=N;
  select * from testlog where name='S'

Died at /usr/local/mysql/bin/mysqldumpslow line 162, <> chunk 1.

（5）二进制日志

二进制日志三种格式

1.基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
2.基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
3.混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

修改二进制格式

mysql> show variables like 'binlog_format';    #MySQL默认使用ROW方式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

[root@rocky8 ~]# vim /etc/my.cnf               #修改二进制格式

[mysqld]
binlog_format=row|statement|mixed

启动二进制日志

mysql> select @@sql_log_bin;   #默认开启，支持动态修改，系统变量，而非服务器选项
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';    #默认是OFF，需要修改配置文件开启，上述两项都开启才可以
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
log_bin       #log_bin=on  不是开启的意思，是二进制文件名开头的on 
              #默认路径是mysql数据路径，可以指定log_bin=/PATH/bin_log_file   
              
[root@rocky8 ~]# ll /data/mysql/rocky*    #开启二进制文件后会生成配置文件
-rw-r----- 1 mysql mysql      179 May 21 21:02 /data/mysql/rocky8-bin.000001
-rw-r----- 1 mysql mysql      516 May 21 21:04 /data/mysql/rocky8-bin.000002

二进制日志配置

 mysql> show variables like 'max_binlog_size';  #单个二进制文件的最大体积，达到最大值会自动滚动，默认是1G
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> show variables like 'max_binlog_cache_size'; #限制用于缓存多事务查询的字节大小
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'binlog_cache_size';  #限制用于缓存多事务查询的字节大小
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'sync_binlog';  #设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'expire_logs_days'; #二进制日志可以自动删除的天数。 默认为0，即不自动删除
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

查看二进制日志

mysql> show master logs;   #查看二进制日志文件
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| rocky8-bin.000001 |       179 | No        |
| rocky8-bin.000002 |       516 | No        |
+-------------------+-----------+-----------+

mysql> show master status;  #查看使用中的二进制欸之文件
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| rocky8-bin.000021 |      157 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysqlbinlog [OPTIONS] log_file…
–start-position= # 指定开始位置
–stop-position= #
–start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss
–stop-datetime=
–base64-output[=name]
-v -v

范例:
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10"  --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

[root@rocky8 ~]# mysqlbinlog /data/mysql/rocky8-bin.000015
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240522 14:23:26 server id 1  end_log_pos 126 CRC32 0x1a1976a4 	Start: binlog v 4, server v 8.0.36 created 240522 14:23:26 at startup
ROLLBACK/*!*/;
BINLOG '
Xo9NZg8BAAAAegAAAH4AAAAAAAQAOC4wLjM2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABej01mEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAaR2GRo=
'/*!*/;
# at 126
#240522 14:23:26 server id 1  end_log_pos 157 CRC32 0xe9b4c0c6 	Previous-GTIDs
# [empty]
# at 157
#240522 14:51:49 server id 1  end_log_pos 236 CRC32 0x3e56f51a 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1716360709208535	immediate_commit_timestamp=1716360709208535    transaction_length=262
# original_commit_timestamp=1716360709208535 (2024-05-22 14:51:49.208535 CST)
# immediate_commit_timestamp=1716360709208535 (2024-05-22 14:51:49.208535 CST)
/*!80001 SET @@session.original_commit_timestamp=1716360709208535*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

删除二进制日志

#清楚指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
范例：
PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

#删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #

切换二进制日志文件（重启服务会自动生成新的二进制日志文件）

mysql> fllush logs; 
或
[root@rocky8 ~]#msyqladmin -uroot -p123456 flush-logs

五.备份恢复

1.备份还原

（1）备份类型
注意：增量备份和差异备份的基础和前提是完全备份，二进制文件不应该与数据文件放在同一磁盘

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

（2）备份工具

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition 组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

（3）还原要点

做还原测试，用于测试备份的可用性
还原演练，写成规范的技术文档

范例: 实现冷备和数据还原
[root@rocky8 ~]# systemctl stop mysqld
[root@rocky8 ~]# scp -r /data/mysql/* 10.0.0.58:/data/mysql/
root@10.0.0.58's password: 
auto.cnf                                                                              100%   56    44.4KB/s   00:00    
ca-key.pem                                                                            100% 1676     1.3MB/s   00:00    
ca.pem                                                                                100% 1112     1.8MB/s   00:00    
client-cert.pem                                                                       100% 1112     1.6MB/s   00:00    
client-key.pem                                                                        100% 1676     2.4MB/s   00:00    
classes.ibd                                                                           100%  112KB  69.1MB/s   00:00    
coc.ibd                                                                               100%  112KB  72.0MB/s   00:00    
courses.ibd                                                                           100%  112KB  94.4MB/s   00:00    
scores.ibd                                                                            100%  112KB 104.4MB/s   00:00    
students.ibd                                                                          100%  112KB 123.3MB/s   00:00    
teachers.ibd                                                                          100%  112KB 133.5MB/s   00:00  
rocky8-bin.000010                                                                     100%  156   406.0KB/s   00:00    
rocky8-bin.000011                                                                     100%  179   446.9KB/s   00:00    
rocky8-bin.000012                                                                     100%   12KB  22.4MB/s   00:00    
rocky8-bin.000013                                                                     100%   21KB  40.6MB/s   00:00    
rocky8-bin.000014                                                                     100%  661     1.6MB/s   00:00    
rocky8-bin.index                                                                      100%  280   761.8KB/s   00:00    
rocky8.log                                                                            100%  489   838.2KB/s   00:00    
server-cert.pem                                                                       100% 1112     3.0MB/s   00:00    
server-key.pem                                                                        100% 1680     3.5MB/s   00:00    
sys_config.ibd                                                                        100%  112KB  93.4MB/s   00:00    
undo_001                                                                              100%   16MB 210.4MB/s   00:00    
undo_002        
[root@centos8 ~]# mysql -uroot -pmagedu
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 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@centos8 ~]# cd /data/mysql
[root@centos8 mysql]# ll
total 192588
-rw-r----- 1 mysql mysql       56 May 23 16:24  auto.cnf
-rw------- 1 mysql mysql     1676 May 23 16:24  ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 May 23 16:24  ca.pem
-rw-r----- 1 mysql mysql      179 May 23 16:09  centos8-bin.000001
-rw-r----- 1 mysql mysql      515 May 23 16:11  centos8-bin.000002
-rw-r----- 1 mysql mysql      179 May 23 16:17  centos8-bin.000003
-rw-r----- 1 mysql mysql      179 May 23 16:27  centos8-bin.000004
-rw-r----- 1 mysql mysql      156 May 23 16:27  centos8-bin.000005
-rw-r----- 1 mysql mysql      105 May 23 16:27  centos8-bin.index
-rw-r--r-- 1 mysql mysql     1112 May 23 16:24  client-cert.pem
-rw------- 1 mysql mysql     1676 May 23 16:24  client-key.pem
drwxr-x--- 2 mysql mysql      132 May 23 16:24  hellodb
-rw-r----- 1 mysql mysql   196608 May 23 16:29 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql  8585216 May 23 16:24 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql     3338 May 23 16:27  ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 May 23 16:27  ibdata1
-rw-r----- 1 mysql mysql 50331648 May 23 16:29  ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May 23 16:24  ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May 23 16:27  ibtmp1
drwxr-x--- 2 mysql mysql      187 May 23 16:27 '#innodb_temp'
drwxr-x--- 2 mysql mysql      143 May 23 16:09  mysql
-rw-r----- 1 mysql mysql 28311552 May 23 16:27  mysql.ibd
-rw-r----- 1 mysql mysql   282022 May 23 16:29  mysql.log
-rw-r----- 1 mysql mysql        5 May 23 16:27  mysql.pid
srwxrwxrwx 1 mysql mysql        0 May 23 16:27  mysql.sock
-rw------- 1 mysql mysql        5 May 23 16:27  mysql.sock.lock
drwxr-x--- 2 mysql mysql     8192 May 23 16:09  performance_schema
-rw------- 1 mysql mysql     1676 May 23 16:24  private_key.pem
-rw-r--r-- 1 mysql mysql      452 May 23 16:24  public_key.pem
-rw-r----- 1 mysql mysql      179 May 23 16:24  rocky8-bin.000001
-rw-r----- 1 mysql mysql      493 May 23 16:24  rocky8-bin.000002
-rw-r----- 1 mysql mysql      179 May 23 16:24  rocky8-bin.000003
-rw-r----- 1 mysql mysql    11266 May 23 16:24  rocky8-bin.000004
-rw-r----- 1 mysql mysql      280 May 23 16:24  rocky8-bin.index
-rw-r----- 1 mysql mysql      489 May 23 16:24  rocky8.log
-rw-r--r-- 1 mysql mysql     1112 May 23 16:24  server-cert.pem
-rw------- 1 mysql mysql     1680 May 23 16:24  server-key.pem
drwxr-x--- 2 mysql mysql       28 May 23 16:09  sys
-rw-r----- 1 mysql mysql 16777216 May 23 16:29  undo_001
-rw-r----- 1 mysql mysql 16777216 May 23 16:29  undo_002
[root@centos8 ~]# service mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL. SUCCESS! 

[root@centos8 ~]# mysql -uroot -pmagedu
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 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

2.mysqldump备份工具

（1）mysqldumop说明

逻辑备份工具：
mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份
命令格式：
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3…] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库，包含数据库本身定义也会备份
mysqldump 常见通用选项：
-u, --user=name User for login if not current user
-p, --password[=name] Password to use when connecting to server
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name… #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
–triggers：#备份表相关触发器，默认启用,用–skip-triggers，不备份触发器
–default-character-set=utf8 #指定字符集
–master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为–source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复
制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭–lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启–single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–singletransaction或-x，–master-data 一起使用实现，此时只刷新一次二进制日志
–compact #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,–no-create-db #不备份create database，可被-A或-B覆盖
–flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
–hex-blob #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，
BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度

（2）实例：利用mysqldump备份恢复表（命令前题在配置文件中写入用户名密码）

[root@rocky8 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| testlog           |
| toc               |
+-------------------+
8 rows in set (0.01 sec)

[root@rocky8 ~]# mysqldump hellodb > /data/hellodb-bak.sql
[root@rocky8 ~]# ll /data/hellodb-bak.sql 
-rw-r--r-- 1 root root 2675193 May 23 17:26 /data/hellodb-bak.sql

[root@rocky8 ~]# mysql
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| testlog           |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

[root@rocky8 ~]# mysql hellodb < /data/hellodb-bak.sql 
[root@rocky8 ~]# mysql
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| testlog           |
| toc               |
+-------------------+
8 rows in set (0.00 sec)

（3）利用mysqldump备份和恢复数据库（mysqldump -A 备份所有数据库）

[root@rocky8 ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@rocky8 ~]# mysqldump -B hellodb > /data/hellodb_all-bak.sql
[root@rocky8 ~]# ll /data/hellodb_all-bak.sql 
-rw-r--r-- 1 root root 2675377 May 23 17:36 /data/hellodb_all-bak.sql

[root@rocky8 ~]# mysql
mysql> drop database hellodb;
Query OK, 8 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@rocky8 ~]# mysql < /data/hellodb_all-bak.sql
[root@rocky8 ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

（4）实战：mysqldump工具和二进制日志恢复误删除表

案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表
前提：在配置文件中加入用户名密码，如不加使用例：mysqldump -uroot -p123456 + 命令

#完全备份，要求开启二进制日志
[root@rocky8 ~]# mysqldump -A --source-data=2 > /backup/all.sql
[root@rocky8 ~]# ll /backup/all.sql 
-rw-r--r-- 1 root root 3960976 May 23 17:49 /backup/all.sql

#完全备份后数据更新
mysql> insert teacher (name,age)values('a',22);
ERROR 1146 (42S02): Table 'hellodb.teacher' doesn't exist
mysql> insert teachers(name,age) values('a',22);
Query OK, 1 row affected (0.01 sec)

mysql> insert teachers(name,age) values('b',23);
Query OK, 1 row affected (0.01 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | a             |  22 | NULL   |
|   6 | b             |  23 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

#10：00误删除一个重要的表teachers
mysql> drop table teachers;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| testlog           |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

#后续其他表继续更新
mysql> insert students(name,age) values('c',18);
Query OK, 1 row affected (0.01 sec)
mysql> insert students(name,age) values('d',19);
Query OK, 1 row affected (0.01 sec)

#10.10发现表删除，进行还原
#停止数据库访问

#从完全备份中，找到二进制位置

[root@rocky8 ~]# head /backup/all.sql -n 30

-- CHANGE MASTER TO MASTER_LOG_FILE='rocky8-bin.000021', MASTER_LOG_POS=3841262;

#备份从完全备份后的二进制日志
[root@rocky8 ~]# mysqlbinlog --start-position=3841262 /data/mysql/rocky8-bin.000021 > /backup/logbin.sql
[root@rocky8 ~]# ll /backup/logbin.sql 
-rw-r--r-- 1 root root 8057 May 23 17:58 /backup/logbin.sql

#找到误删除的语句，从备份中删除此语句
[root@rocky8 ~]# grep -ni drop /backup/logbin.sql 
90:DROP TABLE `teachers` /* generated by server */
[root@rocky8 ~]#sed -i '/DROP TABLE `teachers`/d' /backup/logbin.sql

#利用完全备份和修改过的二进制日志进行还原
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source all.sql

mysql> source logbin.sql;

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| testlog           |
| toc               |
+-------------------+
8 rows in set (0.00 sec)

mysql> select * from techers;
ERROR 1146 (42S02): Table 'hellodb.techers' doesn't exist
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | a             |  22 | NULL   |
|   6 | b             |  23 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

3.xtrabackup备份工具

（1）安装xtrbackup工具
点击下载percona-xtrabackup-83-8.3.0-1.1.el8.x86_64.rpm

[root@centos8 ~]# ll percona-xtrabackup-83-8.3.0-1.1.el8.x86_64.rpm 
-rw-r--r-- 1 root root 46279364 May 23 19:10 percona-xtrabackup-83-8.3.0-1.1.el8.x86_64.rpm

[root@centos8 ~]# yum -y install percona-xtrabackup-83-8.3.0-1.1.el8.x86_64.rpm
Last metadata expiration check: 2:29:02 ago on Thu 23 May 2024 04:42:00 PM CST.
Dependencies resolved.
========================================================================================================================
 Package                       Architecture   Version                                        Repository            Size
========================================================================================================================
Installing:
 percona-xtrabackup-83         x86_64         8.3.0-1.1.el8                                  @commandline          44 M
Installing dependencies:
 libev                         x86_64         4.24-6.el8                                     AppStream             52 k
 mariadb-connector-c           x86_64         3.1.11-2.el8_3                                 AppStream            200 k
 perl-DBD-MySQL                x86_64         4.046-3.module_el8.3.0+419+c2dec72b            AppStream            156 k
 perl-DBI                      x86_64         1.641-3.module_el8.3.0+413+9be2aeb5            AppStream            740 k
 perl-Math-BigInt              noarch         1:1.9998.11-7.el8                              BaseOS               196 k
 perl-Math-Complex             noarch         1.59-420.el8                                   BaseOS               108 k
 rsync                         x86_64         3.1.3-12.el8                                   BaseOS               405 k
 zstd                          x86_64         1.4.4-1.el8                                    AppStream            393 k
Enabling module streams:
 perl-DBD-MySQL                               4.046                                                                    
 perl-DBI                                     1.641                                                                    

Transaction Summary
========================================================================================================================
Install  9 Packages

Total size: 46 M
Total download size: 2.2 M
Installed size: 230 M
Downloading Packages:
(1/8): perl-Math-Complex-1.59-420.el8.noarch.rpm                                        269 kB/s | 108 kB     00:00    
(2/8): perl-Math-BigInt-1.9998.11-7.el8.noarch.rpm                                      366 kB/s | 196 kB     00:00    
(3/8): rsync-3.1.3-12.el8.x86_64.rpm                                                    709 kB/s | 405 kB     00:00    
(4/8): libev-4.24-6.el8.x86_64.rpm                                                      151 kB/s |  52 kB     00:00    
(5/8): perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64.rpm                    442 kB/s | 156 kB     00:00    
(6/8): mariadb-connector-c-3.1.11-2.el8_3.x86_64.rpm                                    379 kB/s | 200 kB     00:00    
(7/8): perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64.rpm                          1.0 MB/s | 740 kB     00:00    
(8/8): zstd-1.4.4-1.el8.x86_64.rpm                                                      679 kB/s | 393 kB     00:00    
------------------------------------------------------------------------------------------------------------------------
Total                                                                                   1.5 MB/s | 2.2 MB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                1/1 
  Installing       : zstd-1.4.4-1.el8.x86_64                                                                        1/9 
  Installing       : mariadb-connector-c-3.1.11-2.el8_3.x86_64                                                      2/9 
  Installing       : libev-4.24-6.el8.x86_64                                                                        3/9 
  Installing       : rsync-3.1.3-12.el8.x86_64                                                                      4/9 
  Installing       : perl-Math-Complex-1.59-420.el8.noarch                                                          5/9 
  Installing       : perl-Math-BigInt-1:1.9998.11-7.el8.noarch                                                      6/9 
  Installing       : perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64                                            7/9 
  Installing       : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64                                      8/9 
  Installing       : percona-xtrabackup-83-8.3.0-1.1.el8.x86_64                                                     9/9 
  Running scriptlet: percona-xtrabackup-83-8.3.0-1.1.el8.x86_64                                                     9/9 
  Verifying        : perl-Math-BigInt-1:1.9998.11-7.el8.noarch                                                      1/9 
  Verifying        : perl-Math-Complex-1.59-420.el8.noarch                                                          2/9 
  Verifying        : rsync-3.1.3-12.el8.x86_64                                                                      3/9 
  Verifying        : libev-4.24-6.el8.x86_64                                                                        4/9 
  Verifying        : mariadb-connector-c-3.1.11-2.el8_3.x86_64                                                      5/9 
  Verifying        : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64                                      6/9 
  Verifying        : perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64                                            7/9 
  Verifying        : zstd-1.4.4-1.el8.x86_64                                                                        8/9 
  Verifying        : percona-xtrabackup-83-8.3.0-1.1.el8.x86_64                                                     9/9 

Installed:
  libev-4.24-6.el8.x86_64                                 mariadb-connector-c-3.1.11-2.el8_3.x86_64                    
  percona-xtrabackup-83-8.3.0-1.1.el8.x86_64              perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64    
  perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64     perl-Math-BigInt-1:1.9998.11-7.el8.noarch                    
  perl-Math-Complex-1.59-420.el8.noarch                   rsync-3.1.3-12.el8.x86_64                                    
  zstd-1.4.4-1.el8.x86_64                                

Complete!

（2）xtrabackup用法
备份

命令： xtrabackupex [option] BACKUP-ROOT-DIR
选项：
–user：#该选项表示备份账号
–password：#该选项表示备份的密码
–host：#该选项表示备份数据库的地址
–databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
如：“xtra_test dba_test”，同时，在指定某数据库时，也可以只指定其中的某张表。
如：“mydatabase.mytable”。该选项对innodb引擎表无效，还是会备份所有innodb表
–defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
–incremental：#该选项表示创建一个增量备份，需要指定–incremental-basedir
–incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与–incremental同时使用
–incremental-dir：#该选项表示还原时增量备份的目录
–include=name：#指定表名，格式：databasename.tablename

预准备

命令：xtrabackupex --apply-log [option] BACKUP-DIR
选项：
–apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
–use-memory：#和–apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
–export：#表示开启可导出单独的表之后再导入其他Mysql中
–redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并

还原

命令：
xtrabackupex --copy-back [选项] BACKUP-DIR
xtrabackupex --move-back [选项] [–defaults-group=GROUP-NAME] BACKUP-DIR
选项：
–copy-back：#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
–move-back：#这个选项与–copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
–force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或–move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果–copy-back和–move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败

还原注意事项：

datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则–copy-back选项不会覆盖
在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成

范例：利用xtrabackup8.0 完全备份和还原MySQL8.0
1.安装xtrabackup包
[root@centos8 ~]# yum -y install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm 
...省略...
Installed:
  libev-4.24-6.el8.x86_64                                 mariadb-connector-c-3.1.11-2.el8_3.x86_64                    
  percona-xtrabackup-80-8.0.28-20.1.el8.x86_64            perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64    
  perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64     perl-Math-BigInt-1:1.9998.11-7.el8.noarch                    
  perl-Math-Complex-1.59-420.el8.noarch                   rsync-3.1.3-12.el8.x86_64                                    

Complete!


2.在原主机上做完全备份到 /backup
[root@centos8 ~]# mkdir /backup
[root@centos8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
...省略...
2024-05-25T15:34:50.032503+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/backup-my.cnf
2024-05-25T15:34:50.032573+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/backup-my.cnf
2024-05-25T15:34:50.032950+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/xtrabackup_info
2024-05-25T15:34:50.033025+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/xtrabackup_info
2024-05-25T15:34:51.033446+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (18280788) to (18280798) was copied.
2024-05-25T15:34:51.238733+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[root@centos8 ~]# ll /backup/base
total 72756
-rw-r----- 1 root root      475 May 25 15:34 backup-my.cnf
-rw-r----- 1 root root      156 May 25 15:34 binlog.000002
-rw-r----- 1 root root       16 May 25 15:34 binlog.index
drwxr-x--- 2 root root      132 May 25 15:34 hellodb
-rw-r----- 1 root root     6096 May 25 15:34 ib_buffer_pool
-rw-r----- 1 root root 12582912 May 25 15:34 ibdata1
drwxr-x--- 2 root root      143 May 25 15:34 mysql
-rw-r----- 1 root root 28311552 May 25 15:34 mysql.ibd
drwxr-x--- 2 root root     8192 May 25 15:34 performance_schema
drwxr-x--- 2 root root       28 May 25 15:34 sys
-rw-r----- 1 root root 16777216 May 25 15:34 undo_001
-rw-r----- 1 root root 16777216 May 25 15:34 undo_002
-rw-r----- 1 root root       18 May 25 15:34 xtrabackup_binlog_info
-rw-r----- 1 root root      102 May 25 15:34 xtrabackup_checkpoints
-rw-r----- 1 root root      465 May 25 15:34 xtrabackup_info
-rw-r----- 1 root root     2560 May 25 15:34 xtrabackup_logfile
-rw-r----- 1 root root       39 May 25 15:34 xtrabackup_tablespaces

[root@centos8 ~]# scp -r /backup/ 10.0.0.58:/
root@10.0.0.58's password: 
xtrabackup_logfile                                                                    100% 2560     2.4MB/s   00:00    
ibdata1                                                                               100%   12MB 242.3MB/s   00:00    
sys_config.ibd                                                                        100%  112KB  68.1MB/s   00:00    
...省略...

3.在目标主机上还原
注意：在目标主机上停止mysql服务（systemctl stop mysqld）,并且数据目录为空（rm -rf /var/lib/mysql/*）
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]# yum -y install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm 
...省略...
Installed:
  libev-4.24-6.el8.x86_64                                 mariadb-connector-c-3.1.11-2.el8_3.x86_64                    
  percona-xtrabackup-80-8.0.28-20.1.el8.x86_64            perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64    
  perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64     perl-Math-BigInt-1:1.9998.11-7.el8.noarch                    
  perl-Math-Complex-1.59-420.el8.noarch                   rsync-3.1.3-12.el8.x86_64                                    

Complete!
[root@centos8 ~]# xtrabackup --prepare --target-dir=/backup/base
...省略...
2024-05-25T15:40:25.174164+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.28 started; log sequence number 18280982
2024-05-25T15:40:25.174494+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2024-05-25T15:40:25.174532+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2024-05-25T15:40:26.174856+08:00 0 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 4294967294
2024-05-25T15:40:26.174912+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2024-05-25T15:40:26.178246+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-05-25T15:40:26.197927+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 18280982
2024-05-25T15:40:26.198323+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

2)复制到数据库目录
[root@centos8 ~]# xtrabackup --copy-back --target-dir=/backup/base
...省略...
mysql/xtrabackup_master_key_id
2024-05-25T15:40:46.611606+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
2024-05-25T15:40:46.625276+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
2024-05-25T15:40:46.626625+08:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_temp
2024-05-25T15:40:46.626655+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_temp
2024-05-25T15:40:46.725709+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

3)还原属性
[root@centos8 mysql]# chown -R mysql:mysql /var/lib/mysql
[root@centos8 mysql]# ll
total 183332
-rw-r----- 1 mysql mysql      156 May 25 15:40 binlog.000002
-rw-r----- 1 mysql mysql       14 May 25 15:40 binlog.index
drwxr-x--- 2 mysql mysql      132 May 25 15:40 hellodb
-rw-r----- 1 mysql mysql     6096 May 25 15:40 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 May 25 15:40 ibdata1
-rw-r----- 1 mysql mysql 50331648 May 25 15:40 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May 25 15:40 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May 25 15:40 ibtmp1
drwxr-x--- 2 mysql mysql      143 May 25 15:40 mysql
-rw-r----- 1 mysql mysql 28311552 May 25 15:40 mysql.ibd
drwxr-x--- 2 mysql mysql     8192 May 25 15:40 performance_schema
drwxr-x--- 2 mysql mysql       28 May 25 15:40 sys
-rw-r----- 1 mysql mysql 16777216 May 25 15:40 undo_001
-rw-r----- 1 mysql mysql 16777216 May 25 15:40 undo_002
-rw-r----- 1 mysql mysql      465 May 25 15:40 xtrabackup_info
-rw-r----- 1 mysql mysql        1 May 25 15:40 xtrabackup_master_key_id

4）启动服务
[root@centos8 ~]# systemctl start mysqld
[root@centos8 ~]# mysql -uroot -p123456
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 8
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

范例：编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份	
[root@centos8 ~]# crontab -e

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

0 0 * * 1,5 /usr/bin/xtrabackup --backup --target-dir=/backup/$(date +\%Y-\%m-\%d)/full
0 0 * * 2-4 /path/to/xtrabackup --backup --target-dir=/backup/$(date +\%Y-\%m-\%d)/incremental --incremental-basedir=/backup/$(date -d "yesterday" +\%Y-\%m-\%d)/full

白佳宝

关注

5
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库

apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键。无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。
复制链接

扫一扫