3.MySQL体系结构和中间件

[toc]

MySQL结构概述

MySQL客户端与服务端模型

MySQL是一个C/S结构的服务

MySQL客户端：

• mysql
• mysqladmin
• mysqldump

第三方客户端：

• SQLyog
• Navicat

mysqld是mysql服务端的守护进程

MySQL客户端与服务端连接方式

• TCP/IP

mysql -uroot -p123 -h10.0.0.51

• Socket

mysql -uroot -p123 -S /tmp/mysql.sock

示例(mysql连接方式)：

mysql -uroot -p123                                  # 连接方式为：socket
mysql -uroot -p123 -h127.0.0.1                   	# 连接方式为：TCP/IP
mysql -uroot -p123 -hlocalhost                   	# 连接方式为：socket
mysql -uroot -p123 -h127.0.0.1 -S /tmp/mysql.sock	# 连接方式为：TCP/IP
mysql -uroot -p123 -S /tmp/mysql.sock -h127.0.0.1	# 连接方式为：TCP/IP

总结：

• MySQL默认连接方式：Socket，Socket连接性能高，不需要建立TCP/IP三次握手
• 不是 -h一定就是TCP连接
• 当-h和-S同时指定时，优先执行-h

MySQL服务器构成

示例

一个后台进程 + 多个线程 + 预分配的内存结构

MySQLd守护进程的程序结构

• 连接层
  • 验证用户的合法性
  • 验证黑名单、白名单
  • 提供两种连接方式
    • TCP/IP
    • Socket
  • 提供一个和SQL建立连接的专用线程

反向解析，IP解析成了db03

• SQL层
  • 验证语法
  • 验证语义
    • DDL
    • DML
    • DQL
    • DCL
    • DTL
  • 解析器：解析后面的SQL语句，将SQL语句生成多种执行方式
  • 优化器：将多种执行方式选择出最优的一种方式
  • 执行器：执行最优的这种方式的SQL语句
    • 提供一个和存储引擎层专用的线程
    • 接收存储引擎层返回的结构化成表的数据
  • 前面有缓存、写缓存
  • 记录日志（binlog）
• 存储引擎层
  • 和磁盘交互取数据
  • 将数据结构化成表格的形式
  • 提供一个和SQL交互的专用线程

连接线程和交互线程，返回线程...是同一个

MySQL的结构

结构

• 逻辑结构：MySQL的所有操作对象
  • 库
  • 表：元数据 + 真实数据
    • 元数据：列 + 其他属性（行数+占用空间大小+权限）
      • 列：列名 + 数据类型 +其他约束
    • 真实数据：表里面的内容
• 物理结构
  • 最底层的数据文件

MySQL逻辑结构与Linux系统对比

MYSQL	LINUX
库	目录
show databases;	ls-l /
use mysql	cd /mysql
表	文件
show tables;	ls
二维表=元数据+真实数据行	文件=文件名+文件属性

MySQL的两种存储引擎

ll
# myisam存储引擎
-rw-r----- 1 mysql mysql 8556 Mar 30 11:28 zls_tb2.frm
-rw-r----- 1 mysql mysql 0 Mar 30 11:28 zls_tb2.MYD
-rw-r----- 1 mysql mysql 1024 Mar 30 11:28 zls_tb2.MYI
# innodb存储引擎
-rw-r----- 1 mysql mysql 8556 Mar 30 10:21 zls_tb.frm
-rw-r----- 1 mysql mysql 98304 Mar 30 10:21 zls_tb.ibd

MySQL单位概念

• 段：段是由多个区组成，一个段就是一张表
• 区：区是由多个页组成的 64个页 （1M）
• 页（块）：MySQL中最小的存储单位 1个页=16K

MySQL清理所有用户

# 清理所有用户
mysql> drop user root@'::1';
Query OK, 0 rows affected (0.01 sec)

truncate mysql.user;

MySQL误删除root用户或忘记root密码

跳过授权表进入mysql

# 停止数据库
/etc/init.d/mysqld stop

# 跳过授权表启动mysql
## 方案一：编辑配置文件
vim /etc/my.cnf
skip-grant-tables
## 方案二：在命令行加入
mysqld_safe --skip-grant-tables --skip-networking &
skip-networking # 跳过任何网段，不允许远程连接，只能本地连接

# 登陆mysql
mysql

方案一：创建root用户并分配权限

# 无法直接创建，需要先刷新授权表
flush privileges;

# 创建root用户
## create命令创建
create user root@'localhost' identified by '123';
## grant命令创建
grant all on *.* to root@'localhost' identified by '123';

# 查看user表的表结构(获取表字段)
desc mysql.user;

# 插入数据
insert into
user(user,host,password,ssl_cipher,x509_issuer,x509_subject)
value('root','localhost',PASSWORD('123'),'','','');

# 查看数据库用户
select user,host from mysql.user;

# 重启数据库
/etc/init.d/mysqld restart

方案二：创建root用户并分配权限

# 无法直接创建，需要先刷新授权表
flush privileges;

# 创建root用户
grant all on *.* to root@'localhost' identified by '123';

# 更新用户权限信息
update mysql.user set grant_priv='Y' where user='root' and
host='localhost';

# 刷新授权表使更改的权限生效
flush privileges;

# 重启数据库
/etc/init.d/mysqld restart

# 查看数据库用户
select user,host from mysql.user;

方案三：创建root用户并分配权限

# 无法直接创建，需要先刷新授权表
flush privileges;

# 创建root用户
grant all on *.* to root@'localhost' identified by '123' with grant option;

# 重启数据库
/etc/init.d/mysqld restart

# 查看数据库用户
select user,host from mysql.user;

MySQL的中间件

Atlas

Atlas时 360 公司开发维护的一个基于MySQL协议的数据中间层项目。

atlas架构
Atlas是一个位于应用程序与MySQL之间中间件。在后端DB看来，Atlas相当于连接它的客户端，在前端应用看来，Atlas相当于一个DB。Atlas作为服务端与应用程序通讯，它实现了MySQL的客户端和服务端协议，同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节，同时为了降低MySQL负担，它还维护了连接池。

主要功能

# 读写分离
# 从库负载均衡
# IP过滤
# 自动分表
# DBA可平滑上下线DB
# 自动摘除宕机的DB

Mysql router

MySQL Router是mysql官方发布的数据库中间件，是处于应用client和dbserver之间的轻量级代理程序，它能检测，分析和转发查询到后端数据库实例，并把结果返回给client。是mysql-proxy的一个替代品

mysql router架构

Mycat

Mycat是基于开源cobar演变而来，对cobar的代码进行了彻底的重构，使用NIO重构了网络模块，并且优化了Buffer内核，增强了聚合，Join等基本特性，同时兼容绝大多数数据库成为通用的数据库中间件。1.4 版本以后 完全的脱离基本cobar内核，结合Mycat集群管理、自动扩容、智能优化，成为高性能的中间件。

• 一个彻底开源的，面向企业应用开发的大数据库集群
• 支持事务、ACID、可以替代MySQL的加强版数据库
• 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
• 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
• 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
• 一个新颖的数据库中间件产品

mycat架构

mycat主要功能

# 支持SQL92标准
# 遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。
# 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群。
# 支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster
# 基于Nio实现，有效管理线程，高并发问题。
# 支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数。
# 支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join。
# 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。
# 支持多租户方案。
# 支持分布式事务(弱xa)。
# 支持全局序列号，解决分布式下的主键生成问题。
# 分片规则丰富，插件化开发，易于扩展。
# 强大的web，命令行监控。
# 支持前端作为mysq通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉。
# 支持密码加密
# 支持服务降级
# 支持IP白名单
# 支持SQL黑名单、sql注入攻击拦截
# 支持分表(1.6)
# 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理(2.0开发版)。

Cobar

Cobar是提供关系型数据库(MySQL)分布式服务的中间件，它可以让传统的数据库得到良好的线性扩展，并看上去还是一个数据库,对应用保持透明。产品在阿里巴巴稳定运行3年以上。接管了3000+个MySQL数据库的schema。集群日处理在线SQL请求50亿次以上。集群日处理在线数据流量TB级别以上。

cobar架构

Amoeba

Amoeba(变形虫)项目,该开源框架于2008年 开始发布一款 Amoeba for Mysql软件。这个软件致力于MySQL的分布式数据库前端代理层，它主要在应用层访问MySQL的 时候充当SQL路由功能，专注于分布式数据库代理层(Database Proxy)开发。座落与 Client、DB Server(s)之间,对客户端透明。具有负载均衡、高可用性、SQL 过滤、读写分离、可路由相关的到目标数据库、可并发请求多台数据库合并结果。通过Amoeba你能够完成多数据源的高可用、负载均衡、数据切片的功能

amoeba架构

Mysql proxy

MySQL Proxy是一个处于你的client端和MySQL server端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载均衡，故障、查询分析，查询过滤和修改等等。MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。MySQL Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。