体系结构及基础管理

1. 体系结构

1.1 C/S(客户端/服务端)模型介绍

TCP/IP方式（远程、本地）：

mysql -uroot -poldboy123 -h 10.0.0.51 -P3306

Socket方式(仅本地)：

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

1.2 实例介绍
实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存

公司=老板+经理+员工+办公室

1.3 mysqld程序运行原理
1.3.1 mysqld程序结构

mysql出现故障或性能问题80%以上是人为的。

                                                                    ---oldguo

1.3.2 一条SQL语句的执行过程

1.3.2.1 连接层

（1）提供连接协议：TCP/IP 、SOCKET

（2）提供验证：用户、密码，IP，SOCKET

（3）提供专用连接线程：接收用户SQL，返回结果

通过以下语句可以查看到连接线程基本情况

mysql> show processlist;

1.3.2.2 SQL层 （重点）

（1）接收上层传送的SQL语句

（2）语法验证模块：验证语句语法

（3）语义检查：判断SQL语句的类型

DDL ：数据定义语言

DCL ：数据控制语言

DML ：数据操作语言

DQL：数据查询语言

（4）权限检查：用户对库表有没有权限

（5）解析器：进行SQL的预处理，产生执行计划

（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划

    代价模型：资源（CPU IO MEM）的耗损评估性能好坏

（7）执行器：根据最优执行计划，执行SQL语句，产生执行结果

执行结果：在磁盘的xxxx位置上

（8）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能

（9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

1.3.2.3 存储引擎层（类似于Linux中的文件系统）

负责根据SQL层执行的结果，从磁盘上拿数据。将16进制的磁盘数据，交由SQL架构化成表，由连接层的专用线程返回给用户。

1.4 逻辑结构
1.4.1 库：库名，库权限，库数据类型等

1.4.2 表（二维表）

列：字段

行：记录

MySQL为了将来数据的准确、规范、有意义，对于传统认知的二维表添加了更多的功能

表属性：权限，存储引擎，字符集

列属性：数据类型、约束、其他属性（默认值，自增长，注释）

1.5 物理存储结构引入
1.5.1 库的物理存储结构
用文件系统的目录来存储

1.5.2 表的物理存储结构
MyISAM（一种引擎）的表：

user.frm：列的相关信息

user.MYD：数据行

user.MYI：索引信息

==========================

InnoDB(默认的存储引擎)的表：

time_zone.frm：存储列相关信息

time_zone.ibd：数据行+索引

1.5.3 表的段、区、页（16k）（了解）
页：最小的存储单元

区：1个或多个连续的页

段：一个表就是一个段，包含一个或多个区

==================================================

2. 基础管理

2.1 用户、权限管理

2.1.1 用户

作用：

登录，管理数据库对象

定义：

用户@‘白名单’

白名单？

允许哪些IP能够登录数据库

支持的方式？

oldboy@’%’ ：所有地址

oldboy@‘10.0.0.%’ :10.0.0.0/24 网段的地址

oldboy@‘localhost’ :本地主机

oldboy@‘10.0.0.5%’ ：10.0.0.50~10.0.0.59

oldboy@‘10.0.0.0/255.255.254.0’ ：子网掩码不是24位的

管理操作：

增：

mysql> create user oldboy@‘10.0.0.%’ identified by ‘123’;

查：

mysql> select user ,host ,authentication_string from mysql.user

改:

mysql> alter user oldboy@‘10.0.0.%’ identified by ‘456’;

删：

mysql> drop user oldboy@‘10.0.0.%’;

2.1.2 权限

介绍

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

ALL : 以上所有权限，一般是普通管理员拥有的

with grant option：超级管理员才具备的，给别的用户授权的功能

应用用户：SELECT,INSERT, UPDATE, DELETE

权限管理操作：

grant 权限 on 对象 to 用户 identified by ‘密码’；

需求例子：windows机器的navicat登录到linux中的MySQL，管理员用户。

grant all on 对象 to 用户 identified by ‘密码’；

对象：

. —>chmod 755 -R / ----》针对管理员

oldboy.* chmod 755 -R /oldboy ----->应用用户

oldboy.t1 chmod 755 -R /oldboy/t1

mysql> grant all on . to root@‘10.0.0.%’ identified by ‘123’;

需求：创建一个应用用户app用户，能从windows上登录mysql，并能操作app库。

mysql> grant select ,update,insert,delete on app.* to app@‘10.0.0.%’ identified by ‘123’;

开发人员用户授权流程：

1.权限

2.对谁操作

3.你从哪来

4.密码要求

提示：8.0在grant命令添加新特性

建用户和授权分开了

grant 不再支持自动创建用户了，不支持改密码

授权之前，必须要提前创建用户。

查看授权：

mysql> show grants for app@‘10.0.0.%’;

回收权限：

revoke delete on app.* from app@‘10.0.0.%’；

2.2 连接管理

2.2.1 自带客户端命令

mysql 常用参数：

-u 用户

-p 密码

-h IP

-P 端口

-S socket文件

-e 免交互执行命令

< 导入SQL脚本

登录方法一——远程登录：

[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306

Enter password:

登录方法一——远程登录：

mysql> select @@socket;

±----------------+

| @@socket |

±----------------+

| /tmp/mysql.sock |

[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock

Enter password:

[root@db01 ~]# mysql -uroot -p -e “select user,host from mysql.user;”

Enter password:

±--------------±----------+

| user | host |

±--------------±----------+

| abc | 10.0.0.% |

| app | 10.0.0.% |

| root | 10.0.0.% |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

±--------------±----------+

[root@db01 ~]#

[root@db01 ~]# mysql -uroot -p <world.sql

Enter password:

[root@db01 ~]#

2.3 多种启动方式介绍

提示：

以上多种方式，都可以单独启动MySQL服务

mysqld_safe和mysqld一般是在临时维护时使用。

另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库

2.4 初始化配置
2.4.1 初始化配置的方法
预编译

配置文件

命令行

2.4.2 初始配置文件

初始化配置文件的默认读取路径

[root@db01 ~]# mysqld --help --verbose |grep my.cnf

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

初始化配置文件读取顺序

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。

但是，如果启动时加入了–defaults-file=xxxx时，以上的所有文件都不会读取

配置文件的书写方式

[标签]

配置项=xxxx

标签类型：服务端、客户端

服务器端标签：

[mysqld]

[mysqld_safe]

[server]

客户端标签：

[mysql]

[mysqldump]

[client]

配置文件的示例展示：

[root@db01 ~]# cat /etc/my.cnf

[mysqld]

user=mysql

basedir=/data/mysql

datadir=/data/mysql/data

socket=/tmp/mysql.sock

server_id=6

port=3306

log_error=/data/mysql/mysql.log

[mysql]

socket=/tmp/mysql.sock

prompt=Master [\d]>

2.5 多实例的应用

2.5.1 准备多个目录

mkdir -p /data/330{7,8,9}/data

2.5.2 准备配置文件

vim /data/3307/my.cnf

[mysqld]

basedir=/data/mysql

datadir=/data/3307/data

socket=/data/3307/mysql.sock

log_error=/data/3307/mysql.log

port=3307

server_id=7

---

vim /data/3308/my.cnf

[mysqld]

basedir=/data/mysql

datadir=/data/3308/data

socket=/data/3308/mysql.sock

log_error=/data/3308/mysql.log

port=3308

server_id=8

---

vim /data/3309/my.cnf

[mysqld]

basedir=/data/mysql

datadir=/data/3309/data

socket=/data/3309/mysql.sock

log_error=/data/3309/mysql.log

port=3309

server_id=9

---

2.5.3 初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak

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

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

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

2.5.4 systemd管理多实例

cd /etc/systemd/system

cp mysqld.service mysqld3307.service

cp mysqld.service mysqld3308.service

cp mysqld.service mysqld3309.service

vim mysqld3307.service

ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

vim mysqld3308.service

ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

vim mysqld3309.service

ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

2.5.5 授权
chown -R mysql.mysql /data/*

2.5.6 启动

systemctl start mysqld3307.service

systemctl start mysqld3308.service

systemctl start mysqld3309.service

2.5.7 验证多实例

netstat -lnp|grep 330

mysql -S /data/3307/mysql.sock -e “show variables like ‘server_id’”

mysql -S /data/3308/mysql.sock -e “show variables like ‘server_id’”

mysql -S /data/3309/mysql.sock -e “show variables like ‘server_id’”