DB
MySQL

MySQL内部组件结构

MySQL大体来说可以分为Server层和存储引擎层两部分

Server层

主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖大多数核心服务功能，以及所有的内置函数，如日期、时间、数学和加密函数等，所有跨存储引擎的功能都在这一层实现，如存储过程、触发器、视图等。

连接器

MySQl有navicat、mysql front、jdbc、SQLyog等非常丰富的客户端，客户端要发起通信都必须先跟Server端建立通信连接，而建立连接的工作由连接器来完成。连接器负责跟客户端建立连接、获取权限、维持和管理连接。 

1
mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306

连接器会到权限表里面查出拥有的权限，连接里面的权限判断逻辑，将依赖于此时读到的权限。一个用户成功建立连接后，即使管理员账号对该用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，新建的连接才会使用新的权限设置。用户的权限表在系统表空间的mysql的user表中。 

1
2
3
4
5
6
7
8
9
10
-- 创建新用户
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
-- 赋权限,%表示所有(host)
grant all privileges on *.* to 'test'@'%';
-- 刷新数据库
flush privileges; 
-- (设置用户名密码)
set password for 'test'@'localhost' = password('test');
-- 查看当前用户的权限
show grants for 'test'@'%';

连接完成后，若没有后续动作，连接将处于空闲状态，可在show processlist命令中看到它。其中的Command列显示为Sleep表示空闲连接。

1
2
3
4
-- 查询连接列表
show processlist;
-- 关闭具体的连接
kill 3;

客户端如果长时间不发送command到Server端，连接器就会自动将它断开。该时间由参数wait_timeout控制，默认8小时。 

1
2
3
4
-- 查看wait_timeout
show global variables like 'wait_timeout';
-- 设置全局服务器关闭非交互连接之前等待活动的秒数
set global wait_timeout=28800;

长连接指连接成功后，若客户端持续有请求，则一直使用同一个连接。短连接则是每次执行完很少的几次查询就断开连接。大多数用的都是长连接，把连接放在Pool内进行管理，但长连接有些时候会导致MySQL占用内存涨得特别快，因为在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放。

若长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。可以定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后断开连接，要查询再重连。若用的是MySQL 5.7或更新版本，可在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。该过程不需要重连和重新做权限验证，但会将连接恢复到刚刚创建完时的状态。

Store层

存储引擎层负责数据的存储和提取。插件式的架构模式，支持InnoDB、MyISAM、Memory等多个存储引擎。InnoDB从MySQL5.5.5版本开始成为默认存储引擎，是最常用的存储引擎，若在create table时不指定表的存储引擎类型，默认会设置存储引擎为InnoDB。

bin-log归档

binlog是Server层实现的二进制日志，会记录cud操作。Binlog在MySQL的Server层实现是引擎共用的，是逻辑日志，记录的是一条语句的原始逻辑，不限大小，追加写入，不会覆盖以前的日志；若误删了数据库,可使用binlog进行归档，要使用binlog归档，首先得记录binlog，因此需要先开启MySQL的binlog功能。

binlog格式有3种statement，row，mixed；

从bin‐log恢复数据

1
2
3
4
5
6
# 恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001|mysql -uroot -p eleven
# 恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731" /usr/local/mysql/data/binlog/mysql-bin.000001|mysql -uroot -p eleven
# 恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date="2018-03-02 12:00:00" --start-date="2019-03-02 11:55:00"|mysql -uroot -p eleven