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

关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要

行row：表中的每一行，又称为一条记录record

列column：表中的每一列，称为属性，字段，域field

主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主键, 主键字段不能为空NULL

唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而且UK字段可以为NULL

域domain：属性的取值范围，如，性别只能是'男'和'女'两个值，人类的年龄只能0-150

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

一对一联系(1:1): 在表A或表B中创建一个字段﹐存储另一个表的主键值 如: 一个人只有一个身份证

一对多联系(1:n)：外键, 如: 部门和员工

多对多联系(m:n)：增加第三张表, 如: 学生和课程

3. 总结mysql设计范式

第一范式：1NF

无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列

说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库

第二范式：2NF

第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键

第三范式：3NF

满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系

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

一、程序包管理器管理的程序包

[root@rocky8-2 ~]# yum -y install mysql-server

二、源代码编译安装

下载并解压缩源码包

tar xvf   mysql-5.6.51.tar.gz  -C /usr/local/src

#mariadb-10.2.18.tar.gz

源码编译安装 MySQL

cd mysql-5.6.51/

cmake . \

-DCMAKE_INSTALL_PREFIX=/apps/mysql \

-DMYSQL_DATADIR=/data/mysql/ \

-DSYSCONFDIR=/etc/ \

-DMYSQL_USER=mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

-DWITH_DEBUG=0 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

make -j 8 && make install

提示：如果出错，执行rm -f CMakeCache.txt

准备环境变量

cd   /apps/mysql/

scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql

准备配置文件

cp -b /apps/mysql/support-files/my-default.cnf /etc/my.cnf

#针对旧版本或mariadb-10.2.18.tar.gz

cp /apps/mysql/support-files/my-huge.cnf   /etc/my.cnf

准备启动脚本,并启动服务

cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld

chkconfig --add mysqld

service mysqld start

安全初始化

mysql_secure_installation

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

实战案例：通用二进制格式安装 MySQL 5.6

1.准备用户

groupadd -r -g 306 mysql

useradd -r -g 306 -u 306 -d /data/mysql mysql

2.准备数据目录，建议使用逻辑卷

#可选做，后面的脚本mysql_install_db可自动生成此目录

mkdir -pv /data/mysql

chown mysql:mysql /data/mysql

3.准备二进制程序

tar xf mysql-VERSION-linux-x86_64.tar.gz -C /usr/local

cd /usr/local

ln -sv mysql-VERSION mysql

chown -R root:root /usr/local/mysql/

4.准备配置文件

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

centos7版本需执行命令，8上没有这个文件：cp /etc/my.cnf{,.bak} 

vim /etc/my.cnf

[mysqld]

datadir = /data/mysql

skip_name_resolve=1

socket=/data/mysql/mysql.sock

log-error=/data/mysql/mysql.log

pid-file=/data/mysql/mysql.pid

[client]

socket=/data/mysql/mysql.sock

5.创建数据库文件并提取root密码

方式一：生成随机密码：

mysqld --initialize --user=mysql --datadir=/data/mysql

grep password /data/mysql/mysql.log

方式二：生成root空密码

mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

6.准备服务脚本，并启动服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

chkconfig --add mysqld

service mysqld start

#修改前面生成的随机密码为指定密码

mysqladmin -uroot -p'随机密码' password magedu

#修改前面生成的空密码为指定密码

mysqladmin -uroot password magedu

#如果有对应的service 文件可以执行下面

cp /usr/local/mysql/support-files/systemd/mariadb.service

/usr/lib/systemd/system/

systemctl daemon-reload

systemctl enable --now mariadb

#安全初始化

#/usr/local/mysql/bin/mysql_secure_installation

7.测试登录

mysql -uroot -pmagedu

四.基于 docker 容器创建 MySQL

[root@ubuntu1804 ~]#docker run --name mysql -d -p 3306:3306 -e

MYSQL_ROOT_PASSWORD=123456 mysql:5.7.30

[root@ubuntu1804 ~]#mysql -uroot -p123456 -h127.0.0.1

总结MySQL配置文件

/etc/my.cnf   #Global选项

/etc/mysql/my.cnf #Global选项

~/.my.cnf #User-specific 选项

客户端 mysql 的配置文件，修改提示符

[root@Rocky85 ~]# vi /etc/my.cnf 

[mysql]

prompt=(\u@\h) [\d]>\_

配置所有MySQL 客户端的自动登录

vim /etc/my.cnf.d/client.cnf

[client]

user=wang

password=123456

[mysql]

prompt=(\\u@\\h) [\\d]>\\_

安全加固

运行脚本：mysql_secure_installation

设置数据库管理员root口令

禁止root远程登录

删除anonymous用户帐号

删除test数据库

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

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

[mysql]

default-character-set=utf8mb4

[client]

default-character-set=utf8mb4

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

一.获取SQL命令的帮助

-    官方文档https://dev.mysql.com/doc/refman/8.0/en/create-database.html

-    (root@localhost) [(none)]> \h

-    (root@localhost) [(none)]> help contents;

二.基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin.创建host表，字段（id，host，ip，cname等）

(root@localhost) [(none)]> \h CREATE DATABASE

create database testdb2 DEFAULT CHARACTER set utf8

查看所有支持的字符集(root@localhost) [(none)]> SHOW CHARACTER SET;

查看默认字符集(root@localhost) [(none)]> show variables like 'char%';

修改字符集：[root@rocky8-1 ~]# vim /mysql/3306/etc/my.cnf

character-set-server=utf8mb4

[root@rocky8-1 ~]# /mysql/3306/bin/mysqld restart

查看排序规则：(root@localhost) [testdb]> SHOW VARIABLES LIKE 'collation%';

(root@localhost) [testdb]> create table host (

     -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, 

     -> host VARCHAR(15) NOT NULL,

     -> ip VARCHAR(15) NOT NULL,

     -> cname VARCHAR(20) NOT NULL

     -> )

     -> ;

Query OK, 0 rows affected (0.11 sec)

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

(root@localhost) [testdb]> insert host(id,host,ip,cname) values(1,'rocky8-1','10.0.0.18','rocky');

Query OK, 1 row affected (0.09 sec)

(root@localhost) [testdb]> insert host(id,host,ip,cname) values(2,'ubuntu20-1','10.0.0.201','ubuntu');

Query OK, 1 row affected (0.01 sec)

DDL：数据定义语言：CREATE,DROP,ALTER（修改）

创建数据库并修改默认字符集为latin1

(root@localhost) [testdb]> create database testdb3 DEFAULT CHARACTER set latin1;

Query OK, 1 row affected (0.10 sec)

删除数据库testdb3

(root@localhost) [testdb]> drop database testdb3;

Query OK, 0 rows affected (0.00 sec)

修改数据库testdb的默认字符集为utf8

(root@localhost) [testdb]> ALTER DATABASE testdb character set utf8;

Query OK, 1 row affected, 1 warning (0.00 sec)

DML：数据操纵语言：INSERT,DELETE,UPDATE （增删改）

(root@localhost) [testdb]> insert host(id,host,ip,cname) values(3,'rocky8-2','10.0.0.28','rocky');

Query OK, 1 row affected (0.01 sec)

(root@localhost) [testdb]> delete from host where id=1;

Query OK, 1 row affected (0.01 sec)

(root@localhost) [testdb]> ALTER TABLE host add deleted char(1);

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

(root@localhost) [testdb]> update host set deleted=1 where id=3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

7. 总结mysql架构原理

MySQL是C/S 架构的，connectors是连接器；可供Native C API、JDBC、ODBC、NET、PHP、Perl、

Python、Ruby、Cobol等连接mysql；ODBC叫开放数据库（系统）互联，open database

connection；JDBC是主要用于java语言利用较为底层的驱动连接数据库；以上这些，站在编程角度可以

理解为连入数据库管理系统的驱动，站在mysql角度称作专用语言对应的链接器.

任何链接器连入mysql以后，mysql是单进程多线程模型的，因此，每个用户连接，都会创建一个单独的

连接线程；其实mysql连接也有长短连接两种方式，使用mysql客户端连入数据库后，直到使用quit命令

才退出，可认为是长连接；使用mysql中的-e选项，在mysql客户端向服务器端申请运行一个命令后则立

即退出，也就意味着连接会立即断开；所以，mysql也支持长短连接类似于两种类型；所以，用户连入

mysql后，创建一个连接线程，完成之后，能够通过这个连接线程完成接收客户端发来的请求，为其处

理请求，构建响应报文并发给客户端；由于是单进程模型，就意味着必须要维持一个线程池，跟之前介

绍过的varnish很接近，需要一个线程池来管理这众多线程是如何对众多客户端的并发请求，完成并发响

应的，组件connection pool就是实现这样功能；connection pool对于mysql而言，它所实现的功能，

包括authentication认证，用户发来的账号密码是否正确要完成认证功能；thread reuse线程重用功

能，一般当一个用户连接进来以后要用一个线程来响应它，而后当用户退出这个线程有可能并非被销

毁，而是把它清理完以后，重新收归到线程池当中的空闲线程中去，以完成所谓的线程重用；

connection limit 线程池的大小决定了连接并发数量的上限，例如，最多容纳100线程，一旦到达此上限

后续到达的连接请求则只能排队或拒绝连接；check memory用来检测内存，caches实现线程缓存；整

个都属于线程池的功能.当用户请求之后，通过线程池建立一个用户连接，这个线程一直存在，然后用户

就通过这个会话，发送对应的SQL语句到服务器端.

服务器收到SQL语句后，要对语句完成执行，首先要能理解sql语句需要有sql解释器或叫sql接口sql

interface就可理解为是整个mysql的外壳，就像shell是linux操作系统的外壳一样；用户无论通过哪种链

接器发来的基本的SQL请求，当然，事实上通过native C API也有发过来的不是SQL 请求，而仅仅是对

API中的传递参数后的调用；不是SQL语句不过都统统理解为sql语句罢了；对SQL而言分为DDL 和DML

#先创建用户并授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu' WITH

GRANT OPTION;两种类型，但是无论哪种类型，提交以后必须交给内核，让内核来运行，在这之前必须要告诉内核哪个

是命令，哪个是选项，哪些是参数，是否存在语法错误等等；因此，这个整个SQL 接口就是一个完完整

整的sql命令的解释器，并且这个sql接口还有提供完整的sql接口应该具备的功能，比如支持所谓过程式

编程，支持代码块的实现像存储过程、存储函数，触发器、必要时还要实现部署一个关系型数据库应该

具备的基本组件例如视图等等，其实都在sql interface这个接口实现的；SQL接口做完词法分析、句法分

析后，要分析语句如何执行让parser解析器或分析器实现

parser是专门的分析器，这个分析器并不是分析语法问题的，语法问题在sql接口时就能发现是否有错误

了，一个语句没有问题，就要做执行分析，所谓叫查询翻译，把一个查询语句给它转换成对应的能够在

本地执行的特定操作；比如说看上去是语句而背后可能是执行的一段二进制指令，这个时候就完成对应

的指令，还要根据用户请求的对象，比如某一字段查询内容是否有对应数据的访问权限，或叫对象访问

权限；在数据库中库、表、字段、字段中的数据有时都称为object，叫一个数据库的对象，用户认证的

通过，并不意味着就能一定能访问数据库上的所有数据，所以说，mysql的认证大概分为两过程都要完

成，第一是连入时需要认证账号密码是否正确这是authentication，然后，验证成功后用户发来sql语句

还要验证用户是否有权限获取它期望请求获取的数据；这个称为object privilege，这一切都是由parser

分析器进行的

分析器分析完成之后，可能会生成多个执行树，这意味着为了能够达到访问期望访问到的目的，可能有

多条路径都可实现，就像文件系统一样可以使用相对路径也可使用绝对路径；它有多种方式，在多种路

径当中一定有一个是最优的，类似路由选择，因此，优化器就要去衡量多个访问路径中哪一个代价或开

销是最小的，这个开销的计算要依赖于索引等各种内部组件来进行评估；而且这个评估的只是近似值，

同时还要考虑到当前mysql内部在实现资源访问时统计数据，比如，根据判断认为是1号路径的开销最小

的，但是众多统计数据表明发往1号路径的访问的资源开销并不小，并且比3号路径大的多，因此，可能

会依据3号路径访问；这就是所谓的优化器它负责检查多条路径，每条路径的开销，然后评估开销，这个

评估根据内部的静态数据，索引，根域根据动态生成的统计数据来判定每条路径的开销大小，因此这里

还有statics；一旦优化完成之后，还要生成统计数据，这就是优化器的作用；如果没有优化器mysql执

行语句是最慢的，其实优化还包括一种功能，一旦选择完一条路径后，例如用户给的这个命令执行起

来，大概需要100个开销，如果通过改写语句能够达到同样目的可能只需要30个开销；于是，优化器还

要试图改写sql语句；所以优化本身还包括查询语句的改写；一旦优化完成，接下来就交给存储引擎完成.

mysql是插件式存储引擎，它就能够替换使用选择多种不同的引擎，MyISAM是MySQL 经典的存储引擎

之一，InnoDB是由Innobase Oy公司所开发，2006年五月由甲骨文公司并购提供给MySQL的，NDB主

要用于MySQL Cluster 分布式集群环境，archive做归档的等等，还有许多第三方开发的存储引擎；存储

引擎负责把具体分析的结果完成对磁盘上文件路径访问的转换，数据库中的行数据都是存储在磁盘块上

的，因此存储引擎要把数据库数据映射为磁盘块，并把磁盘块加载至内存中；进程实现数据处理时，是

不可能直接访问磁盘上的数据的，因为它没有权限，只有让内核来把它所访问的数据加载至内存中以

后，进程在内存中完成修改，由内核再负责把数据存回磁盘；对于文件系统而言，数据的存储都是以磁

盘块方式存储的，但是，mysql在实现数据组织时，不完全依赖于磁盘，而是把磁盘块再次组织成更大

一级的逻辑单位，类似于lvm中的PE或LE的形式；其实，MySQL的存储引擎在实现数据管理时，也是在

文件系统之上布设文件格式，对于文件而言在逻辑层上还会再次组织成一个逻辑单位，这个逻辑单位称

为mysql的数据块datablock 一般为16k ，对于关系型数据库，数据是按行存储的；一般一行数据都是存

储在一起的，因此，MySQL 在内部有一个datablock，在datablock可能存储一行数据，也可能存放了n

行数据；将来在查询加载一行数据时，内核会把整个一个数据数据块加载至内存中，而mysql存储引

擎，就从中挑出来某一行返回给查询者，是这样实现的；所以整个存储是以datablock在底层为其最终级

别的.

事实上，整个存取过程，尤其是访问比较热点的数据，也不可能每一次当用户访问时或当某SQL语句用

到时再临时从磁盘加载到内存中，因此，为了能够加上整个性能，mysql的有些存储引擎可以实现，把

频繁访问到的热点数据，统统装入内存，用户访问、修改时直接在内存中操作，只不过周期性的写入磁

盘上而已，比如像InnoDB，所以caches和buffers组件就是实现此功能的；MySQL为了执行加速，因为

它会不断访问数据，而随计算机来说io是最慢的一环，尤其是磁盘io，所以为了加速都载入内存中管

理；这就需要MySQL 维护cache和buffer缓存或缓冲；这是由MySQL 服务器自己维护的；有很多存储引

擎自己也有cache和buffer一个数据库提供了3种视图，物理视图就是看到的对应的文件系统存储为一个个的文件，MySQL的数据

文件类型，常见的有redo log重做日志，undo log撤销日志，data是真正的数据文件，index是索引文

件，binary log是二进制日志文件，error log错误日志，query log查询日志，slow query log慢查询日

志，在复制架构中还存在中继日志文件，跟二进制属于同种格式；这是mysql数据文件类型，也就是物

理视图；逻辑视图这是在mysql接口上通过存储引擎把mysql文件尤其是data文件，给它映射为一个个

关系型数据库应该具备组成部分，比如表，一张表在底层是一个数据文件而已，里面组织的就是

datablock，最终映射为磁盘上文件系统的block，然后再次映射为本地扇区的存储，但是整个mysql需

要把他们映射成一个二维关系表的形式，需要依赖sql接口以及存储引擎共同实现；所以，把底层数据文

件映射成关系型数据库的组件就是逻辑视图；DBA 就是关注内部组件是如何运作的，并且定义、配置其

运作模式，而链接器都是终端用户通过链接器的模式进入数据库来访问数据；数据集可能非常大，每一

类用户可能只有一部分数据的访问权限，这个时候，最终的终端用户所能访问到的数据集合称作用户视

图；

为了保证MySQL运作还提供了管理和服务工具，例如:备份恢复工具，安全工具，复制工具，集群服务，

管理、配置、迁移、元数据等工具

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

MyISAM和InnoDB是MySQL中两种常见的存储引擎，它们在数据存储、事务处理、锁定机制、外键支持、空间占用、数据一致性等方面存在显著区别。

数据存储和文件结构：

MyISAM将数据、索引分开存储，在磁盘上表现为三个文件：.frm（表结构定义）、.MYD（数据文件）、.MYI（索引文件）。

InnoDB则将数据和索引存储在一起，在磁盘上表现为两个文件：.frm（表结构定义）和.ibd（数据和索引文件）。

事务处理：

MyISAM不支持事务处理，因此无法保证数据的一致性和完整性。

InnoDB支持事务，通过ACID属性（原子性、一致性、隔离性、持久性）保证数据的一致性和完整性。

锁定机制：

MyISAM使用表级锁，锁定整个表，读写操作都会影响整个表，不支持行级特定记录的锁定，并发控制较差。

InnoDB支持行级锁和表级锁，可以实现对单行或小部分数据的锁定，提供更好的并发控制。

外键支持：

MyISAM不支持外键。

InnoDB支持外键，可以维护表之间的关系，保证数据的引用完整性。

空间占用：

MyISAM通常占用较少的磁盘空间，因为它不支持事务和行级锁定，且索引和数据分开存储。

InnoDB需要更多的磁盘空间，因为它需要维护事务日志、数据版本等信息，并且数据和索引存储在一起。

数据一致性：

MyISAM在某些情况下可能会出现数据不一致的情况。

InnoDB通过事务和行级锁定机制保证数据的一致性。

使用场景：

如果应用需要高并发、事务支持、外键等功能，InnoDB是更好的选择。

如果应用对事务要求不高，且对读写性能有较高要求，MyISAM可能更适合简单的读多写少的场景。

选择存储引擎时，应根据应用的具体需求和数据库的使用场景来决定，考虑到数据的完整性、并发控制、事务处理等因素12。

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

mysql索引作用

索引可以降低服务需要扫描的数据量，减少了IO次数

索引可以帮助服务器避免排序和使用临时表

索引可以帮助将随机I/O转为顺序 I/O

哪些查询不会使用到索引

B+Tree索引的限制：

如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾

不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

不适合使用hash索引的场景

不适用于顺序查询：索引存储顺序的不是值的顺序

不支持模糊匹配

不支持范围查询

不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

10. 总结事务ACID事务特性

ACID特性：

A：原子性；整个事务中所有操作要么全部成功执行，要么全部失败后回滚

C：一致性；数据库总是从一个一致性状态转换为另一个一致性状态，类似于能量守恒定律

I：隔离性；一个事务所做出的操作在提交之前，是不能为其他事务所见；隔离有多种隔离级别，实现并发

D：持久性，一旦事务提交，其所做的修改会永久保存于数据库中

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

事务日志：

redo log:记录某数据块被修改后的值，数据更新前先记录redo log(WAL)，可以用来恢复未写入data file的已成功事务更新的数据

undo log:保存与执行的操作相反的操作，即记录某数据被修改前的值，可以用来在事务失败时进行rollback

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

mysql日志类型

-    错误日志(Error Log)

-    二进制日志(Binary Log & Binary Log Index)

-    通用查询日志(query log)

-    慢查询日志(slow query log)

-    事务日志(innodb redo log)

-    中继日志(reley log)

要启动MySQL的日志，可以在my.cnf（Linux）配置文件中设置相应的选项。

例如，要启用慢查询日志和查询日志，可以在配置文件中添加以下内容：

[mysqld]

slow_query_log=1

slow_query_log_file= /var/log/mysql/mysql-slow.log

general_log=1

general_log_file= /var/log/mysql/mysql.log

启用二进制日志（binlog）通常是为了复制功能，但也可以用于数据恢复：

[mysqld]

log_bin= /var/log/mysql/mysql-bin.log

修改配置后，需要重启MySQL服务器以使更改生效。

查看日志状态及配置：SHOW VARIABLES LIKE 'log_%';

开启或关闭日志：

SETGLOBALslow_query_log='ON';

SETGLOBALgeneral_log='ON';

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

二进制日志的格式分为3种：STATEMENT、ROW、MIXED。可以在启动时通过参数-binlog_format 进行设置。

STATEMENT

MySOL5.1之前的版本都采用这种方式，顾名思义，日志中记录的都是语句(statement），每一条对数据造成修改的SOL 语句都会记录在日志中，通过mysqlbinlog工具、可以清晰地看到每条语句的文本。主从复制的时候，从库(slave)会将日志解析为原文本，并在从库重新执行一次。这种格式的优点是日志记录清晰易读、日志量少，对I/O 影响较小。缺点是在某些情况下slave 的日志复制会出错。

ROW

MySQL5.1.11少，出现了这种新的日志格式。它将每一行的变更记录到日志中，而不是SOL语句。比加一个简单的更新 SOL: update emp set name='abc'，如果是STATENENT格式，日志中会记录一行SQL文本；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，如果是一个100 万行的大表，则日志中会记录 100 万条记录的变化情况。日志量大大增加。这种格式的优点是会记录每一行数据的变化细节，不会出现某些情况下无法复制的问题。缺点是日志量大，对I/O影响较大。

MIXED

这是日前MySOL默认的日志格式，即混合了STATEMENT和ROW 两种日志。默认情况采用STATEMENT，但在一些特殊情况下采用ROW 来进行记录，比如采用 NDB存储引擎，此时对表的 DML语句全都采用ROW；客户端使用了临时表；客户端采用了不确定函数，current_user()等，因为这种不确定函数在主从中得到的值可能不同，导致主从数据产生不一致。MIXED格式能尽量利用两种模式的优点，而避开它们的缺点。

可以在global和session 级别对 binlog_format 进行日志格式的设置。

MySQL的二进制日志（Binary Log）是一种记录MySQL数据库中所有修改操作的日志文件，它包含了对数据库进行更改的所有语句。二进制日志具有以下作用和使用场景：

1.数据恢复和备份：

二进制日志可以用于数据恢复和备份。通过将二进制日志应用到备份的数据库上，可以将备份数据恢复到二进制日志记录的某个时间点。这对于避免数据损坏或误操作造成的数据丢失非常有用。

2. 主从复制：

MySQL的主从复制是一种将数据从一个数据库服务器复制到另一个数据库服务器的机制。主服务器将更改操作记录到二进制日志中，然后从服务器通过读取主服务器的二进制日志来同步数据。二进制日志在主从复制中起到了关键的作用。

3.数据库迁移和升级：

通过使用二进制日志，可以将旧版本的MySQL数据库迁移到新版本的MySQL数据库，并保持数据的一致性。将旧版本的二进制日志应用到新版本的数据库上，可以重新执行以前的操作，从而实现数据库的升级和迁移。

4. 审计和故障排除：

二进制日志可以用于数据库的审计和故障排除。通过分析二进制日志，可以了解数据库中的操作流程，查找错误或故障的原因，并进行性能优化。

在MySQL中，可以通过配置参数来开启和管理二进制日志。可以设置二进制日志的大小限制、日志的保留时间以及是否记录所有SQL语句等。同时，MySQL提供了一些工具和命令来管理和操作二进制日志，如mysqlbinlog命令用于查看和解析二进制日志内容。

需要注意的是，由于二进制日志记录了所有的修改操作，因此它会占用一定的磁盘空间，并对数据库的性能产生一定的影响。因此，在配置二进制日志时需要权衡好数据的恢复需求和性能开销。

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

完全备份，部分备份

完全备份：整个数据集

部分备份：只备份数据子集，如部分库或表

完全备份、增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

增量和差异备份的基础和前提是完全备份

注意：二进制日志文件不应该与数据文件放在同一磁盘

冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务

温备：读操作可执行；但写操作不可执行

热备：读、写操作均可执行

MyISAM：温备，不支持热备

InnoDB：都支持

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快

逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

mysqldump完成数据库备份与恢复验证

[root@rocky85 ~]# mkdir /data

[root@Rocky85 ~]# mysqldump -A > /data/all.sql 全部数据库备份

[root@rocky8 ~]# mkdir /data

[root@rocky85 ~]# scp /data/all.sql 10.0.0.8:/data

[root@rocky8 ~]# mysql < /data/all.sql      恢复数据库

-------------------------------------------------------------------------------------------------------------------------------

xtrabackup完成数据库备份与恢复验证 

[root@rocky8 ~]# yum -y install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm

[root@rocky8 ~]# mkdir /backup

[root@rocky8 ~]# xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base 备份

还原之前数据库一定要停止服务

[root@rocky8-1 ~]# yum -y install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm

[root@rocky8 ~]# scp -r /backup/ 10.0.0.18:/

[root@rocky8-1 ~]# du -sh /backup/base

70M /backup/base

[root@rocky8-1 ~]# xtrabackup --prepare --target-dir=/backup/base  预准备

[root@rocky8-1 ~]# du -sh /backup/base

186M /backup/base

再次确认下面目录是空的，mysql服务未启动

[root@rocky8-1 ~]# ls /var/lib/mysql

[root@rocky8-1 ~]# systemctl status mysqld

[root@rocky8-1 ~]# xtrabackup --copy-back --target-dir=/backup/base  实质就是把文件拷贝到data目录下

[root@rocky8-1 ~]# chown -R mysql.mysql /data/mysql/    改属性

[root@rocky8-1 ~]# systemctl start mysqld

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

MySQL版本：mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz

xtrabackup版本：percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm

一.编写脚本执行备份：

[root@rocky8-2 ~]# vim backup_mysql.sh

#!/bin/bash

#

#******************************************************************************************************

#Author: zhaoming

#QQ: 599031583

#Date: 2024-08-04

#FileName: backup_mysql.sh

#URL: http://www.zmlinux.cn

#Description: The backup_mysql script

#Copyright (c): 2024 All rights reserved

#******************************************************************************************************

. /etc/os-release

week=`date | cut -d ' ' -f1`

pack_dir=`pwd`/percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm

back_dir=/backup/`date +%F_%u`

account="root"

password="magedu"

[ ! -e $pack_dir ] && { echo -e "\E[1;31m缺少xtrabackup，请下载xtrabackup包\E[0m";exit 3;} || echo -e "\E[1;32m开始执行备份MySQL\E[0m"

function pack_install(){

    if [ $ID = rocky ];then

        yum -y install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm

    elif [ $ID = ubuntu ];then

        apt update && apt -y install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm

    else

        echo "不支持的操作系统"

        exit 10

    fi

}

function dump_allmysql(){

    mkdir $back_dir -p

    xtrabackup -u$account -p$password --backup --target-dir=$back_dir/base && echo -e "\E[1;32mMySQL备份完成,文件存放路径：$back_dir\E[0m" || echo -e "\E[1;31mMySQL备份失败\E[0m"

}

function dump_plusmysql(){

    mkdir $back_dir -p

    xtrabackup -u$account -p$password --backup --target-dir=$back_dir/inc1 --incremental-basedir=$back_dir/base && echo -e "\E[1;32mMySQL备份完成,文件存放路径：$back_dir\E[0m" || echo -e "\E[1;31mMySQL备份失败\E[0m"

}

function dump_plus2mysql(){

    mkdir $back_dir -p

    xtrabackup -u$account -p$password --backup --target-dir=$back_dir/inc2 --incremental-basedir=$back_dir/inc1 && echo -e "\E[1;32mMySQL备份完成,文件存放路径：$back_dir\E[0m" || echo -e "\E[1;31mMySQL备份失败\E[0m"

}

function dump_plus3mysql(){

    mkdir $back_dir -p

    xtrabackup -u$account -p$password --backup --target-dir=$back_dir/inc3 --incremental-basedir=$back_dir/inc2 && echo -e "\E[1;32mMySQL备份完成,文件存放路径：$back_dir\E[0m" || echo -e "\E[1;31mMySQL备份失败\E[0m"

}

function main(){

    pack_install

    if [[ $week =~ Mon|Fri ]];then

    dump_allmysql

    elif [ $week = Tue ];then

    dump_plusmysql

    elif [ $week = Wed ];then

    dump_plus2mysql

    elif [ $week = Thu ];then

    dump_plus3mysql

    else

        exit 2

    fi

}

main

测试rocky脚本备份正常：

[root@rocky8-2 ~]# chmod +x backup_mysql.sh

[root@rocky8-2 ~]# crontab -e        计划任务

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

30 2 * * 1,2,3,4,5 /root/backup_mysql.sh                                     每周一~周五的2:30备份，具体周一周五完全备份，周二~周四增量备份在脚本中实现

查看计划任务