day01-02-MYSQL体系结构和基础管理

第二章 MySQL体系结构与管理

1 MySQL C/S结构介绍 *** ****

两种连接方式：
TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket：mysql -uroot -poldboy123 -S /tmp/mysql.sock

2 MySQL实例的构成 ***
公司：老板 + 经理 + 员工 + 办公区
实例： mysqld + master thread + 干活的Thread + 预分配的内存

3 MySQL中mysqld服务器进程结构

3.1 SQL语句引入
结构化的查询语言
DQL 数据查询语言
DDL 数据定义语言
DML 数据操作语言
DCL 数据控制语言

mysql> select user,host from mysql.user;

3.2 连接层
（1）提供连接协议
Socket
TCPIP
（2）验证用户名（root@localhost）密码合法性，进行匹配专门的授权表。

（3）派生一个专用连接线程（接收SQL，返回结果）

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.01 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost | NULL | Sleep   |    3 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql>

思考：
忘记密码的参数在哪做的手脚？
--skip-grant-tables
--skip-networking

3.3 SQL层（优化方面至关重要的）

首先接收上层传送的SQL语句

（1）验证SQL语法和SQL_MODE
（2）语义检查：判断SQL语句的类型

DDL ：数据定义语言 (create,drop,alter)

DCL ：数据控制语言（grant,revoke）

DML ：数据操作语言 (insert,delete,update)

DQL：数据查询语言（select,show）

（3）权限检查：用户对库表有没有权限
（4）解析器进行语句解析，生成执行计划（解析树）
（5）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划

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

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

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

（7）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
（8）提供日志记录（日志管理章节）：binlog，默认是没开启的。

3.4 存储引擎层（类似于Linux中的文件系统）
真正和磁盘打交道的一个层次，有点类似系统的FS
根据SQL的执行结果，找到磁盘上16进制的数据，返回给SQL，结构化成二维表的方式，再又连接层线程返回给用户，最终展现出来

3.5 MySQL逻辑存储结构
库 ----》Linux目录
create database wordpress charset utf8mb4; ----》mkdir /wordpress
show databases; ----》ls /
use wordpress; ----》cd /wordpress

表 ----》Linux的文件
列（字段）无
列属性无
数据行（记录） ----》Linux数据行
表属性（元数据） ----》Linux 文件属性

3.6 MySQL物理存储结构
库：使用FS上的目录来表示
表：
MyISAM(ext2)
user.frm ：存储的表结构（列，列属性）
user.MYD : 存储的数据记录
user.MYI ：存储索引

InnoDB(XFS)
time_zone.frm ：存储的表结构（列，列属性）
time_zone.ibd ：存储的数据记录和索引
ibdata1 : 数据字典信息

3.7 innodb 段区页
一般情况下（非分区表）
一个表就是一个段（存储空间）.
一个段（存储空间）由多个区（块）构成
一个区（块）在（16k），64个连续的页，1M大小

数据行存储：每次默认分配64个连续的page，也就是1Mb大小。连续的64个page，称之为一个区

用户和权限管理

4.1 作用
登录MySQL
管理MySQL

4.2 用户的定义
用户名@'白名单'

wordpress@'%'
wordpress@'localhost'
wordpress@'127.0.0.1'
wordpress@'10.0.0.%'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'
wordpress@'10.0.%'

4.3 用户的操作
4.3.1 建用户

mysql> create user oldboy@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

说明：
8.0以前，可以自动创建用户并授权

mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';

4.3.2 查询用户

mysql> select user,host from mysql.user;

4.3.3 修改用户密码

mysql> alter user oldguo@'10.0.0.%' identified by '123456';

4.3.4 删除用户

mysql> drop user oldguo@'10.0.0.%' ;

4.4 权限管理
4.4.1 权限列表

# ALL 包括：
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

4.4.2 授权命令

with grant option  # 给别人授权的权限

grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option;

grant 权限  on 作用目标  to 用户  identified by 密码 with grant option;

grant SELECT,INSERT, UPDATE, DELETE, CREATE on wordpress.* to

作用目标:
*.*
wordpress.*
worpress.t1

4.4.3 授权需求

创建一个管理员用户root，可以通过10网段，管理数据库.

grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;

创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.

grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';

4.4.4 回收权限
show grants for wordpress@'10.0.0.%';

mysql> revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';
mysql> show  grants for wordpress@'10.0.0.%';

4.4.5 关于生产中开用户
（1）如何沟通开用户

是否有邮件批复
对哪些库和表做操作
做什么操作
从什么地址来登录
（2）开发人员找你要root用户密码？
走流程拒绝他
如果是金融类的公司

（1）原则上是不允许任何非DBA人员持有或申请root
（2）如果有人私下索要root密码，即使举报。

MySQL基础管理-2

MySQL的启动和关闭
1.1 日常启停
mysql.server start ---> mysqld_safe ---> mysqld
mysql.service ---> mysqld
需要依赖于 /etc/my.cnf
1.2 维护性的任务
mysqld_safe --skip-grant-tables --skip-networking &
我们一般会将我们需要的参数临时加到命令行.
也会读取/etc/my.cnf的内容,但是如果冲突,命令行优先级最高

[root@db01 ~]# mysqld_safe &
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

初始化配置
2.1 作用
(1)影响数据库的启动
(2)影响到客户端的功能
2.2 初始化配置的方法
(1)初始化配置文件(例如/etc/my.cnf)
(2)启动命令行上进行设置(例如:mysqld_safe mysqld)
(3)预编译时设置(仅限于编译安装时设置)
2.3 初始化配置文件的书写格式
[标签]
xxx=xxx
[标签]
xxx=xxx

2.4 配置文件标签的归类
服务器端:
[mysqld]
[mysqld_safe]
[server]

客户端:
[mysql]
[mysqladmin]
[mysqldump]
[client]

2.5 配置文件设置样板(5.7)

# 服务器端配置
[mysqld]
# 用户
user=mysql   
# 软件安装目录                
basedir=/application/mysql  
# 数据路径
datadir=/data/mysql/data 
# socket文件位置
socket=/tmp/mysql.sock
# 服务器id号
server_id=6
# 端口号
port=3306
#客户端配置
[mysql]
# socket文件位置，必须和服务端一致
socket=/tmp/mysql.sock

2.6 配置文件读取顺序

[root@mysql-node01 support-files]# mysqld --help --verbose|grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
[root@mysql-node01 support-files]#

2.7 强制使用自定义配置文件

--defautls-file 
[root@db01 tmp]# mysqld_safe --defaults-file=/tmp/aa.txt &

MySQL的连接管理

3.1 mysql 命令
注意:提前应该将用户授权做好

mysql> grant all on *.* to root@'10.0.0.%' identified by '123';

TCPIP :

mysql -uroot -p -h 10.0.0.51 -P3306

-P -h 参数必须成对出现。

Socket

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

3.2 客户端工具
sqlyog
navicat

多实例管理
4.1 准备多个目录
```
mkdir -p /data/330{7,8,9}/data
```

4.2 准备配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

4.3 初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql

4.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=/application/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf

vim mysqld3308.service
修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf


vim mysqld3309.service
修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

[root@db01 system]# grep "ExecStart" mysqld3309.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
[root@db01 system]# grep "ExecStart" mysqld3308.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
[root@db01 system]# grep "ExecStart" mysqld3307.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
[root@db01 system]#

4.5 授权

chown -R mysql.mysql /data/*

4.6 启动

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

4.7 验证多实例

netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"