第1章 数据库产品介绍

1.1 数据库种类

(DBMS) database management service

常用种类

RDBNS 关系类数据库

1. MySQL

2. Oracle

3. MSSQL

4. PG

NoSQL 非关系型数据库

1. MongoDB

2. ES

3. Redis

4. Memcached

NewSQL 分布式数据库

1. RDS DRDS (阿里产品) AliSQL

2. PolarDB (阿里新代产品) OB

3. PinCAP TiDB

1.2 MySQL 产品分支

Oracle MySQL 同一家公司 收购

MariaDB 原作者新开发版本 (免费)

Percona MuSQL 和MySQL差不多 (第三方公司二次开发)

RDS 云数据库 MySQL阿里云二次开发

1.3 MySQL 版本选择

常见

1. 5.6 5.6.34 5.6.36 5.6.38

2. 5.7 5.7.18 5.7.20 .5.7.22 5.7.24

3. 8.0 8.0.11

GA时间: 5.6.38 和5.7.20 都是2017-09-13

MySQL 官网能查看具体帮助 地址: https://dev.mysql.com/

官网 下载地址: https://downloads.mysql.com/archives/community/

能查看具体下载版本等信息 下载历史版本

image.png

第2章 MySQL 5.7.26 二进制版本安装

软件 版本
Linux OS Centuos7.6
MySQL 5.7.26

安装:

1. 下载对应版本MySQL 拖拽到相应目录 官方推荐:/usr/local

2. 原始环境检查

shell> rpm -qa|grep mariadb

删除命令 yum remove 软件名

3. 解压后 做软连接 添加环境变量 创建mysql数据库目录

shell>ln -s mysql.xxxx mysql# 创建软连接

vim /etc/profile # 编辑环境变量

export PATH=/usr/local/mysql/bin:${PATH}

shell> sourue /etc/profile# 刷新一下环境变量

shell> mkdir -p /data/mysql/data # 创建目录

推荐数据库目录放到硬盘下 既可以方便数据转移 又方便维护

4. 准备用户 授权

shell> useradd -M -s /sbin/nologin mysql

shell>chowd -R mysql.mysql /usr/local/mysql

shell> chowd -R mysql.mysql /app/mysql/data

5. 下载mysql依赖 初始化数据库

shell>yum -y install libaio-devel # 安装依赖

初始化安装mysql 5.7以上版本

shell>mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysql/data

初始化安装mysql 5.7以下版本

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

说明:

--initialize-insecure # 不安全按照数据库  即为不创建root默认的密码

--user= # 指定管理用户

--basedir= # 指定服务主体目录

--datadir= # 指定数据存放目录

6. 编写配置文件准备启动

shell> vim /etc/my.cnf

[mysqld]

user=mysql

basedir=/usr/local/mysql

datadir=/data/mysql/data

socket=/tmp/mysql.sock

[mysql]

socket=/tmp/mysql.sock

第3章 数据库升级 升级到8.0

3.1 安装多实例

1. 上传8.0压缩包解压

2. 创建目录 修改权限

3. 将 /etc/my.cnf 配置文件暂时移动走

4. 初始化MySQL

5. 编写新的配置文件 注意名称不同

6. 用特殊方法启动mysql 8.0版本

/usr/local/mysql80/bin/mysql_safe –defaults-file=/etc/my80.cof &

7. 进入mysql不同版本 需要指定不同sock文件 或者端口

3.2 版本升级

升级MySQL版本需要升级两方面 软件 数据

升级注意事项:

1. 不要跨多版本升级 需要注意5.7之前的升级版本需要使用 mysql_upgrade命令

2. 注意备份旧版本数据 方便出问题回退 cp 或者 tar

升级步骤

1. 安装新版本

2. 修该软连接或者配置文件basedir

3. 用5.7的软件启动5.6的数据

升级数据字典mysql_upgrade -uroot -p123 -S /tmp/mysql.sock

注意备份

第4章 MySQL多实例

有时服务器性能优异 一个MySQL不足以沾满服务器性能 这时就会很浪费硬件资源

多实例 一台服务器中安装使用多个MySQL

官方参考资料: mysql_multi多实例搭建

https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html

4.1 规划

1. 端口 : 3307 3308 3309

2. 数据 : /data/330{7..9}/data

3. 配置文件: /data330{7..9}/my.cnf

4. 配置文件内容修改

4.2 操作

1. 创建多实例目录 授权

mkdir -p mkdir -p /data/330{7..9}/data

chown -R mysql. /data/

2. 初始化

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data  

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data 

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

3. 准备不同的配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
port=3307
server_id=7
socket=/tmp/mysql3307.sock
EOF 

cat > /data/3308/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
server_id=8
socket=/tmp/mysql3308.sock
EOF 

cat > /data/3309/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3309/data
port=3309
server_id=9
socket=/tmp/mysql3309.sock
EOF

4. 准备启动脚本 来启动不同的mysql

cat > /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=[图片上传失败...(image-9d05bb-1573896778674)] http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF 

cat > /etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=[图片上传失败...(image-82be6d-1573896778674)] http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF 

cat > /etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=[图片上传失败...(image-e9f41f-1573896778674)] http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

5. 启动多实例

systemctl start mysqld3307

systemctl start mysqld3308

systemctl start mysqld3309

6. 验证MySQL 看端口

ss -lntup查看是否有指定的端口

第5章 MySQL体系结构(工作模式/原理)

MySQL的C/S结构 传统模式 server端和clint端

server端: mysqld

clint端: mysql(命令) 第三方程序软件(开发用) mysql dump

5.1.1 MySQL实例

实例: 运行起来的mysql服务称之为一个实例

实例是 mysqld 启动后 开启Mater Thread (进程) + Thread(线程) 在专用内存中运行

第6章 mysqld的核心程序结构 (体系结构)

用问题解释 　面试题: 一条SQL语句在MySQL中如何处理的

一条命令的执行过程

连接层连接MySQL 验证连接协议 验证用户 通过后开启一条线程 接收到语句后 传输到后面的SQL 层

SQL 层接收到后 进行语法/ 语义/ 权限检查 解析后通过优化器判断出代价最低的执行计划 执行器执行优化器选择 交给存储引擎层

存储引擎层 取出16 进制数据

返回给SQL 层转换成表,

通过连接层( 连接线程) 返回结果

image.png

6.1 程序结构

1. 连接层：

a) 验证连接协议

**TCP／IP**（网络Socket）：＝IP＋Port

本地**Socket**：scoket文件

b) 验证用户名密码

c) 提供连接线程

负载SQL语句接收和结果反馈

2. SQL层

a) 语法检查 检擦语法是否正确 SQL语句通过;号进行分隔

b) 语义检查 检查SQL分类 对什么进行操作

c) 权限检查

d) 解析器 预处理 生成多种执行计划树 / 构造表结构

e) 优化器 判断那种执行计划代价最低 代价: CPU IO MEM 消耗最低的

f) 执行器: 执行优化器选择后的代价最低的策略 得出SQL****语句所需数据的存储位置

3. 存储引擎层 (文件系统) 和”磁盘”打交道的层次 取出16进制数据

a) 返回给SQL层转换成表,

b) 通过连接层(连接线程)返回结果

4. redis查询缓存(第三方工具) 我们查询过的SQL语句将他们缓存到redis中 省去了查询过程 加快速度

redis (第三方工具)是SQL层功能

SQL语句：　指的是MySQL结构化的查询语言（MySQL的专用语句）

分类：DDL（数据定义语言）　DCL（数据控制语言）　DML（数据操作语言）．．　按照功能进行分类

6.2 物理存储结构

宏观角度: 在MySQL存储目录中 我们能看到的存储引擎

存储引擎分为

My I SAm存储引擎 特点三个文件

[root@db ~]# ll /app/mysql/data/mysql/

-rw-r----- 1 mysql mysql 8820 Nov 11 12:04 columns_priv.frm

-rw-r----- 1 mysql mysql 0 Nov 11 12:04 columns_priv.MYD

-rw-r----- 1 mysql mysql 4096 Nov 11 12:04 columns_priv.MYI

其中

columns_priv.frm 用于存储表(列)结构

columns_priv.MYD 用于存储数据行

columns_priv.MYI 用于存储索引信息

In no DB存储引擎表 (5.5以上版本中 全是InnoDB存储引擎) 特点两个文件

-rw-r----- 1 mysql mysql 8780 Nov 11 12:04 engine_cost.frm

-rw-r----- 1 mysql mysql 98304 Nov 11 12:04 engine_cost.ibd

engine_cost.frm 用于存储表结构

engine_cost.ibd 用于存储 数据 + 索引

微观角度:

segment ( 段) 属于逻辑结构 一个( 非分区表) 表就是一个段 , 由一个或者多个区构成

extent ( 区) 连续的64 个page , 默认1M 5.7 之后根据页的大小可分为2-4M

page ( 页) MySQL 的最小物理IO 单元 默认IO 最小为16KB = 连续的4 个os block

为的是减少非连续block

image.png

6.3 MySQL逻辑结构

1. 库 可理解为Linux的目录 用于存储表信息

2. 表 理解为Linux的文件/表格 用于存储具体数据信息

结构为二维表 包含
列: 由两部分组成 列名 + 列属性
行: 记录
属性: 数据行,权限等