2、数据库总结

2022-10-27 08:56:27

1、定义

数据库：存放、组织、管理数据的一类软件。

分类：关系型数据库、非关系型数据库。

关系型数据库：一般通过SQL进行增删改查
非关系型数据库：Not only SQL
- 列式数据库——HBase
- key-value键值对数据库——Redis、MemCache、Etcd
- 文档型数据库——MongoDB
- 全文搜索引擎——Elasticsearch
- 时序数据库——Prometheus

2、SQL分类

2.1 SQL语句通常分为四类：

2.1.1 DDL

数据定义语句：语句定义了不同的数据段、数据库、表、列、索引、对象。

#常用关键字
create,drop,alter,rename,truncate

2.1.2 DML

数据操纵语句：用于添加、删除、更新、查询数据库记录，并检查数据完整。

#常用关键字
insert,delete,update

2.1.3 DCL

数据控制语句：用于控制不同数据段直接的许可和访问级别的语句

#常用关键字
grant,revoke

2.1.4 DQL

数据查询语句：用于从一个或多个表中检索信息

#常用关键字
select

3、数据分类

名称	说明
结构化数据	MySQL数据库里的表等
半结构化数据	XML、YAML、JSON等
非结构化数据	文档、图片、音频、视频等

4、MySQL的体系结构

客户端-->连接处理层-->存储引擎层-->存储层

4.1 客户端

可以是不通编程语言编写的应用程序，也可以是一些API接口

4.2 连接处理层：

服务层（连接池）管理和缓冲用户连接，为客户端请求做连接处理。
核心处理层（SQL处理）接收用户的SQL请求、查询、分析等

4.3 存储引擎层

一种底层存放、管理数据的模式。

两种存储引擎：MyISAM和InnoDB
- MyISAM：查询速度快，有较好的索引优化和数据压缩技术。不支持事务^[1]
- InnoDB：支持拥有ACID特性事务的存储引擎，处理大数据量设计
- NDB：用于MySQL Cluster的集群存储引擎，提供数据层面的高可用性
- MEMORY：内存储存数据，访问速度快，但安全性没有保障
- BLACKHOLE：黑洞引擎，写入的任何数据都消失（中继slave）

4.4 存储层

用来存储MySQL的一些数据文件，各种日志文件等物理文件，支持各种文件系统，如ext2、ext3、ext4、xfs、nfs等

5、常见日志文件

5.1 错误日志

5.1.1 内容

错误日志是最重要的日志之一，它记录了MariaDB/MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。

可以使用 --log-error=[file_name]来指定mysqld记录的错误日志文件，如果没有指定file_name，则默认的错误日志文件为datadir目录下的 hostname.err ，hostname表示当前的主机名。

也可以在MariaDB/MySQL配置文件中的mysqld配置部分，使用log-error指定错误日志的路径。

5.1.2 查看

如果不知道错误日志的位置，可以查看变量log_error来查看。

show variables like 'log_error';

在MySQL 5.5.7之前，刷新日志操作(如flush logs)会备份旧的错误日志(以_old结尾)，并创建一个新的错误日志文件并打开

在MySQL 5.5.7之后，执行刷新日志的操作时，错误日志会关闭并重新打开，如果错误日志不存在，则会先创建。

5.1.3 说明

在MariaDB/MySQL正在运行状态下删除错误日志后，不会自动创建错误日志，只有在刷新日志的时候才会创建一个新的错误日志文件。

5.2 一般查询日志

5.2.1 内容

记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。因此，Mysql默认是把General log关闭的。

5.2.2 查看

5.2.1 查看日志的存放方式：

show variables like 'log_output';

5.2.2 查看一般查询日志配置情况

设置日志结果会记录到名为gengera_log的表中，表的默认引擎都是CSV

set global log_output='table';

设置表数据到文件

set global log_output=file;

设置general log的日志文件路径：

set global general_log_file='/tmp/general.log';
#开启general log
set global general_log=on;
#关闭general log
set global general_log=off;

查看日志是否开启

show global variables like ‘general_log’;

5.3 二进制日志

5.3.1 作用

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
用于数据库的基于时间点的还原

5.3.2 内容

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

5.3.3 什么时候产生

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

5.3.4 什么时候释放

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

show variables like 'expire_logs_days';

5.3.5 对应的物理文件

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个binlog日志文件，通过一个统一的index文件来组织。

show variables like 'log_bin_basename';

5.3.6 其他

二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同.

5.3.6.1 作用不同

redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

5.3.6.2 内容不同

redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是SQL语句
两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的
恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog
关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

5.4 中继日志

5.4.1 内容

从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。
指定日志路径两种方法:

编辑my.cnf 写入 log-error=[path]
通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

5.4.2 查看

show variables like '%err%';

5.4.3 说明

max_relay_log_size

relay log 允许的最大值，如果该值为0，则默认值为 max_binlog_size (1G)；

如果不为0，则 max_relay_log_size 则为最大的relay_log文件大小；

relay_log
定义 relay_log 的位置和名称，如果值为空，则默认位置在数据文件的目录；

relay_log_index
定义 relay_log 索引的位置和名称，记录有几个 relay_log 文件，默认为2个

relay_log_info_file

定义 relay-log.info 的位置和名称

relay-log.info 记录 master 主库的 binary_log 的恢复位置和从库 relay_log 的位置；

relay_log_purge
是否自动清空中继日志，默认值为1(启用)；

relay_log_recovery
当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启；

sync_relay_log
当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O；

当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改；

sync_relay_log_info
这个参数和 sync_relay_log 参数一样

5.5 慢查询日志

5.5.1 内容

慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。

5.5.2 查看

5.5.1 查看慢查询时间

/*默认10s*/
show variables like 'long_query_time';

5.5.2 查看慢查询配置情况

show status like “%slow_queries%”;

5.5.3 查看慢查询日志路径

show variables like “%slow%”;

5.5.4 开启慢日志

set global slow_query_log=1;

5.6 DDL日志

5.6.1 内容

DDL日志或元数据日志记录由数据定义语句（如DROP TABLE和ALTER TABLE）生成的元数据操作。MySQL使用此日志从元数据操作中间发生的崩溃中恢复。当执行该语句

DROP TABLE $table_name;

我们需要确保两个t1 和t2被删除，并且每个表都被完全删除。此类SQL语句的另一个示例是

ALTER TABLE $table_name DROP PARTITION p2;

我们必须确保完全删除了该分区，并且从 table t3的分区列表中删除它的定义。

诸如刚刚描述的元数据操作的记录被写入MySQL数据目录中的文件ddl_log.log。这是一个二进制文件；它不是可读的，并且您不应尝试以任何方式修改其内容。

ddl_log.log只有记录元数据语句时，才会创建ddl_log.log，并在在以mysqld的方式启动后将被删除。因此，该文件可能不会出现在以完全正常方式运行的MySQL服务器上。

当前，ddl_log.log最多可容纳1048573个条目，相当于4 GB。超过此限制后，必须先重命名或删除文件，然后才能执行任何其他DDL语句。

没有与此文件关联的用户可配置服务器选项或变量。

5.7 元数据日志

5.7.1 内容

元数据，就是表示数据的数据，这些数据五花八门，总之，只要不是我们存储到数据库里的数据，大多都可以理解为元数据。描述数据库的任何数据—作为数据库内容的对立面—是元数据。因此，列名、数据库名、用户名、版本名以及从SHOW语句得到的结果中的大部分字符串是元数据。还包括INFORMATION_SCHEMA^[2]数据库中的表中的内容，因为定义的那些表存储关于数据库对象的信息。

5.7.2 查看

有几种show语句还可以带有一条like 'pattern'字句，用来限制语句的输出范围，其中'pattern'允许包含'%'和'_'通配符，比如下面这条语句返回domaininfo表中以s开头的所有列

show columns from domaininfo like 's%';

5.7.3 获取元数据的方法

show方法

show databases;  --列出所有数据库  
show create database db_name;  --查看数据库的DDL  
show tables; --列出默认数据库的所有表  
show tables from db_name;  --列出指定数据库的所有表  
show table status;  --查看表的描述性信息  
show table status from db_name;  
show create table tbl_name;  --查看表的DDL  
show columns from tbl_name;  --查看列信息  
show index from tbl_name;  --查看索引信息

select

像上面这张支持like字句的所有show都可以改写成一条where字句，如：

show columns ``from` `domaininfo ``where` `field=``'sysdomain'``;

注：desc tbl_name和explain tbl_name的效果和show columns from tbl_name一致。

从INFORMATION_SCHEMA数据库里查询相关表

从命令行获取元数据

前面两种方法都必须得在MySQL命令行里执行，而mysqlshow和mysqldump提供了从OS命令行获取元数据库的方法，如：

#列出所有数据库
mysqlshow
#列出给定数据库的所有表
mysqlshow db_name
#列出给定数据库表的所有列
mysqlshow db_name tbl_name
#列出索引信息
mysqlshow --keys db_name [$tbl_name]
#列出数据库的描述性信息
mysqlshow --status db_name
#mysqldump可以让你看到create table语句（就像show create table语句一样），如：
mysqldump --no-data db_name [$tbl_name] ...

注意：在用mysqldump查看表结构时，一定要加上--no-data，否则你看到的将是数据库表里的数据

6、备份与恢复

6.1 MySQL逻辑备份

本质上是导出SQL语句文件（逻辑导入导出）——命令：mysqldunp

任何存储引擎都可以用mysqldump备份或SQL语句
速度较慢，导入时可能发生格式不兼容，无法直接增量备份，提供三种级别备份——表、库、全库

6.2 MySQL物理备份

数据文件的备份——命令：xtrabackup

备份过程快速、可靠；支持增量备份，更灵活；备份过程不会打断正执行事务；可压缩；自动实现备份检验；还原快
只能对InnoDB表增量备份MyISM表增量备份是全备；备份MyISAM表前要对全库进行加READ LOCK，阻塞写操作

7、主从复制架构

7.1 复制原理

7.1.1 MySQL复制原理

master将数据库的改变写入二进制文件，slave同步这些二进制日志，并根据这些二进制日志进行数据重演操作实现数据同步。这种同步默认是单向的、异步的。

7.1.2 MySQL复制过程

slave的线程连到master，获取复制位置，并将binlog依次写到slave的relay_log。
salve的SQL监视relay_log的变动，达到数据一致。

7.2 复制架构

AB复制——一主一从
并联复制——一主多从
级联复制——主从从
双主复制——互为主从

7.3 三种复制方式

7.3.1 同步复制

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。

这样，显然不可取，也不是MYSQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间

7.3.2 异步复制

master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。

7.3.3 半同步复制

master只保证slaves中的一个操作成功，就返回，其他slave不管。

<5.6版本后>

基于GTID^[3]的AB复制——全局事务标识符

完全基于事务，不支持MyISAM引擎，不需要指定position。

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

<5.5版本后>

上面的架构默认异步，可能发生数据不一致甚至丢失。

半同步复制：

master每commit一个事务都要确保slave接受完master发送的binlog日志文件并写入到自己的中继日志relay_log中，再给master信号才算成功commit。所以会牺牲master的性能。

用于处理操作量大，复杂度高的数据：事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行，用来管理 insert,update,delete 语句 ↩︎
INFORMATION_SCHEMA是MySQL自带的一个系统数据库，它里面存储了所有的元数据，通过select里面的相关表就可以获取你想要的元数据。和show语句相比，它比较麻烦，但它的好处是标准的SQL语句，更具有可移植性，且更灵活，可以通过各种表达式获取你真正需要的信息。 ↩︎
GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主 ↩︎

码农公寓