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、数据分类

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复制过程

1. slave的线程连到master，获取复制位置，并将binlog依次写到slave的relay_log。
2. 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的性能。