MySQL 体系结构管理


1. MySQL体系结构与管理

1.1 MySQL C/S结构介绍 

两种连接方式：

TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306

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

1.2 MySQL实例的构成 

公司： 老板  +  经理  + 员工 + 办公区

实例： mysqld +  master thread  + 干活的Thread  + 预分配的内存

1.3 MySQL中mysqld服务器进程结构

1.3.1 SQL语句引入

结构化的查询语言

DQL

DDL

DML

DCL

1.3.2 连接层

（1） 提供连接协议

Socket 

TCPIP

（2） 验证用户名（root@localhost）密码合法性，进行匹配专门的授权表。

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

  mysql> show processlist;

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

（1）验证SQL语法和SQL_MODE

（2）验证语义

（3）验证权限

（4）解析器进行语句解析，生成执行计划（解析树）

（5）优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。

代价：CPU  IO  MEM

（6）执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法。

（7）提供query cache(默认不开)，一般不开，会用redis

（8）记录操作日志（binlog），默认没开

1.3.4 存储引擎层

真正和磁盘打交道的一个层次

根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再又连接层线程返回给用户。

1.4 逻辑结构

库  =====> 表 ====> 数据行（记录），字段（列），属性（列属性，列约束，列的数据类型等，表属性，权限）

表=====>   数据行+列+元数据

1.5 物理结构（引入）

1.5.1 宏观

库，存储在操作系统的目录中

表：

user表： MyISAM

user.frm    -----> 列的定义信息

user.MYD -----> 数据行

user.MYI    -----> 索引信息

time_zone表：InnoDB

time_zone.frm  ---->列的定义信息

time_zone.ibd  ---->数据行和索引

1.5.2 微观

段，区，页

一个表就是一个段，mysql分配空间时至少分配一个区

每个区默认是1M（64 个 pages），MySQL最小的IO单元是PAGE(16K)

文件句柄，恢复rm的数据。

2. 基础管理

2.1 用户管理

2.1.1 作用

登录

管理数据库对象

2.1.2 用户的定义

用户名@'白名单'

白名单:

xiang@'10.0.0.51'

xiang@'10.0.0.%'

xiang@'10.0.0.5%'

xiang@'10.0.0.0/255.255.254.0'

xiang@'%'

xiang@'oldguo.com'

xiang@'localhost'

xiang@'db01'

常用的：

xiang@'10.0.0.%'

xiang@'10.0.0.5%'

xiang@'10.0.0.0/255.255.254.0'

xiang@'localhost'

2.1.3 用户管理

创建用户

mysql> create user oldguo@'10.0.0.%' identified by '123';

查询用户

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

小练习：

1. 创建一个oldboy用户，密码oldboy123 能够通过172.16.1网段登录数据库，并且查看用户

删除用户

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

修改用户

mysql> alter user root@'localhost' identified by '123';

2.2 权限管理

2.2.1 权限作用

控制用户登录之后能对MySQL对象做哪些命令。

2.2.2 权限的定义

MySQL的权限定义就是SQL语句。

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

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

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

8.0 版本新特性（了解）

加入了role的概念。

2.2.3 授权管理

（1）语法：

grant ALL    on    wordpress.* to    wordpress@'10.0.0.%'  identified by '123';

grant 权限  on  范围  to  用户    identified by '密码'  

grant select,update,insert,delete    on  范围  to  用户    identified by '密码'

范围：

*.*

wordpress.*

wordpress.t1

（2）例子：

1. 从windows中的navicat软件使用root管理mysql数据库

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

2. 创建 zhihu业务用户能够对zhihu业务库进行业务操作

grant select,update,delete ,insert on zhihu.* to zhihu@'10.0.0.%' identified by '123';

（3）思考一个问题 ：

1. grant select,update on *.* to xiang@'10.0.0.%';

2. grant delete on wordpress.* to xiang@'10.0.0.%';

3. grant insert on wordpress.t1 to xiang@'10.0.0.%';

问，xiang@'10.0.0.%' 能对t1表具备什么权限？

MySQL中的权限是可以继承，多次授权是叠加的。

所以，想要取消某个权限，必须通过回收的方式实现，而不能多次重复授权。

（4）查看用户权限

mysql> show grants for root@'localhost';

（5）回收权限

mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';

面试题：

1.开发人员找DBA开用户，需要DBA和开发人员沟通什么？

（1）你要做哪些权限的操作

（2）你要从什么地址来登数据库

（3）要对什么对象进行管理操作？

2.开发人员找DBA要管理员root用户的密码，作为DBA你怎么处理？（金融公司）

情况一：

（1）不给

（2）在金融公司严令禁止，举报！！！

情况二（小公司）

root用户是乱用，学会保护自己。

可以提意见，生成流程。

3. MySQL 连接管理

3.1 自带的客户端工具

mysql 参数

-u

-p

-S

-h

-P

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

[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51 -P3306

问题：

怎么证明你的数据库是可用的？

1. 证明进程和端口存在

ps -ef |grep mysqld

netstat -lnp|grep mysqld

2. 验证用户远程连接性

[root@db01 ~]# mysql -uoldguo -p123  -h10.0.0.51 -e "show databases;"

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

3.2 远程的客户端工具

navicat  sqlyog

略。

4. MySQL 启动方式介绍

辅助脚本方式（普通的启动关闭）：

sys-v  : /etc/init.d/mysqld 

/etc/init.d/mysqld  start

----> /application/mysql/bin/mysqld_safe

----> /application/mysql/bin/mysqld

systemd : /etc/systemd/system/mysqld.service

systemctl start mysqld 

---->/application/mysql/bin/mysqld

维护性的启动方式：

/application/mysql/bin/mysqld_safe  --skip-grant-tables --skip-networking &

/application/mysql/bin/mysqld &

5. MySQL 初始化配置

5.1 预编译时进行设置（略）

只能在编译安装中实现

硬编码配置到程序中

5.2 初始化配置文件(my.cnf)

5.2.1 初始化配置文件默认读取位置

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

/etc/my.cnf --》 /etc/mysql/my.cnf --》 /usr/local/mysql/etc/my.cnf  --》 ~/.my.cnf      ###读取顺序

--defaults-file=/opt/my.cnf

mysqld_safe

mysqld 

5.3 命令行模式

[root@db01 ~]# mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/asdad &

5.4 初始化配置文件应用

5.4.1 作用

数据库的启动：mysqld  mysqld_safe   

客户端的连接：mysql  mysqldump  mysqladmin

5.4.2 配置文件书写格式

[程序名]

配置项=xxx

配置项=xxx

配置项=xxx

配置项=xxx

服务端

[server]

[mysqld]

[mysqld_safe]

客户端

[client]

[mysql]

[mysqldump]

5.4.3 mysql初始化配置常用参数(通用模板)

[mysqld]

user=mysql

basedir=/application/mysql

datadir=/data/mysql/data

socket=/tmp/mysql.sock

server_id=6

port=3306

log_error=/data/mysql/data/mysql.log

log_bin=/data/mysql/data/mysql-bin   

[mysql]

socket=/tmp/mysql.sock

5.5 MySQL 多实例

5.5.1 创建目录

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

5.5.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

5.5.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

5.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=/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

5.5.5 授权

chown -R mysql.mysql /data/*

5.5.6 启动

systemctl start mysqld3307.service

systemctl start mysqld3308.service

systemctl start mysqld3309.service

5.5.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"

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,033评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,725评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,473评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,846评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,848评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,691评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,053评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,700评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,856评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,430评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,034评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2赞 343

推荐阅读更多精彩内容

  • 2019-06-14
    1. MySQL体系结构与管理 1.1 MySQL C/S结构介绍 *** 两种连接方式： TCP/IP：mysq...
    山有木兮_8adb阅读 383评论 0赞 0
  • 6-14MySQL
    1.4 逻辑结构 库 ---> 表 ----》数据行（记录），字段（列），属性（列属性，列约束，列的数据类型等，表...
    张鑫泽_2109阅读 494评论 0赞 0
  • mysql 基础讲解
    1. 体系结构 1.1 C/S(客户端/服务端)模型介绍 TCP/IP方式（远程、本地）： mysql -uroo...
    极光01阅读 555评论 0赞 0
  • 妈妈的孩子气
    情绪化的麻麻要努力学习冷静的带娃，道阻且长。。。 因为每次出门玩就不能顺利回家这边事情已经一而...
    myZing阅读 179评论 0赞 0