MySQL数据库-day-2 2019-06-14

第二章MySQL体系结构与管理

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

16956686-301b140a033d0dd2.png

两种连接方式：
     TCP/IP ：mysql -u -p123456 -h 10.0.0.51 -p3306
     Socket ： mysql -u -p123456 -S /tmp/mysql.sock

2、MySQL实例的构成 ***

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

3、MySQL中mysqld服务器进程结构

16956686-7824255ca53f23e5.png

#1.连接层
（1）提供连接协议
         Socket    TCP/IP
（2）验证用户名（root@localhost） 密码合法性，进行匹配专门的授权表   
（3）派生一个专用连接进程（接收SQL，返回结果）
通过以下语句可以查看连接线程的基本情况。
mysql> show processlist;
思考：
忘记密码的参数在哪里做的手脚？
--skip-grant-tables  #---> 跳过授权
--skip-networking    #---> 直接跳过TCP/IP 协议 本地连接
#2.SQL层 （优化方面至关重要的）
（1）验证SQL语法和SQL_MODE
（2）验证含义
     DDL ：数据定义语言
     DCL ：数据控制语言
     DML ：数据操作语言
     DQL： 数据查询语言
     ...
（3）验证权限
（4）解析器进行语句解析，生成执行计划（解析树）
（5）优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。
代价： CPU IO MEM
（6）执行器按照优化器选择执行计划，执行SQL语句，得出获取数据得方法。
（7）提供query cache （默认不开），一般不开，会用redis 
（8）记录操作日志（binlog），默认没开
#3. 储存引擎层
真正和磁盘打交道得一个层次
根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再又连接层线程返回给用户。

3、MySQL数据库逻辑结构

MySQL数据库逻辑结构.png

#1、库 
表、 数据行（记录） 、字段 、 属性（列属性、列约束、列数据类型、表属性、权限）
#2、表
字段（名称、属性、数据类型、约束）
记录（字段名称对应的数据）
#3、关系
库--->表--->（记录、字段）

4、MySQL数据物理结构

MySQL数据物理结构.png

#1、库的物理存储结构
用文件系统的目录来储存
#2、表的物理存储结构
（1）多个文件组成
（2）MyISAM（一种引擎）的表
    -rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm   #----> 列的相关信息
    -rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD  #----> 数据行
    -rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI   #----> 索引信息
（3）InnoDB(默认的存储引擎)的表：
    -rw-r----- 1 mysql mysql    8636 Apr 18 11:37time_zone.frm
    -rw-r----- 1 mysql mysql   98304 Apr 18 11:37time_zone.ibd
    time_zone.frm：存储列相关信息
    time_zone.ibd：数据行+索引
#2、关系 段 区 页
    段：一个表就是一个段，包含一个或多个区
    区： 64个连续的页，共1M
    页： 最小的存储单元，默认16k

5、用户管理

#1.登陆、管理数据库对象（逻辑结构）

#2.用户的定义
用户名@'白名单'
白名单？
允许哪些用户登陆
白名单支持的方式:
xiaozhi@'10.0.0.51'    #---->允许10.0.0.51IP地址登陆    
xiaohi@'10.0.0.%'      #----->允许10.0.0.0/24网段登陆
xiaozhi@'10.0.0.5%'  #----->允许10.0.0.5[1-9] 登陆
xiaozhi@'10.0.0.0/255.255.254.0'  #---->允许254网段登陆
xiaozhi@'%'   #----->允许全部IP地址登陆
xiaozhi@'xzg1818.cn'  #----->允许域名登陆
xiaozhi@'localhost'   #----->本地登陆
xiaozhi@'db01'   #------>主机名登陆

常用：
xiaohi@'10.0.0.%'      #----->允许10.0.0.0/24网段登陆
xiaozhi@'10.0.0.5%'  #----->允许10.0.0.5[1-9] 登陆
xiaozhi@'10.0.0.0/255.255.254.0'  #---->允许254网段登陆
xiaozhi@'localhost'   #----->本地登陆

#增：
mysql> create user xiaozhi@'10.0.0.%' identified by '123';  #---->创建用户并设置密码

#删：
mysql> drop user xiaozhi@'%' #---->删除用户

#改：
mysql> alter user root@'localhost'  identified by '123456';  #---->为root用户修改密码

#查：
mysql> select user,host ,authentication_string from mysql.user; #---->查看用户信息

6、权限的管理

#1、权限作用
控制用户登陆之后能对MySQL对象做哪谢命令。

#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概率。

#3、权限管理
mysql> 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

例子：
1、需求，从windows中的navicat软件管理myqsl数据库
mysql> grant all on *.* to root@'10.0.0.%' identified by '123456';

2、 创建 zhihu 业务用户能够对zhihu业务库进行业务操作
mysql> create database zhihu;
mysql> create user zhihu@'10.0.0.1' identified by '123';
mysql> grant select,update,insert,delete  on zhihu.* to zhihu@'10.0.0.1' identified by '123';

思考一个问题？
mysql中的权限是可以继承，多次授权是叠加的。
所以，想要取消某个权限，必须通过回收的方式实现，而不能多次重复授权。
=======================================================

#4、查看授权
mysql> show grants for oldboy1@'172.16.1%';       #查看用户具有哪些权限
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option;  #为oldguo用户设置成超级管理员
=======================================================
#5、回收权限
mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';

面试题：
1.开发人员找DBA开用，需要DBA和开发人员沟通什么？
（1）你要做哪些权限的操作
（2）你要从什么地址来登录数据库
（3）要对什么对象进行管理操作？

2.开发人员找DBA要管理员root用户的密码，作为DBA你怎样处理？（金融公司）
情况一：
（1）不给
（2）在金融公司严令禁止，举报！！！

情况二（小公司）：
（1）root用户是乱用，学会保护自己。
（2）可以提意见，生成流程。

7、MySQL 连接管理

#1、自带的客户端工具
mysql 参数：
-u                   用户
-p                   密码
-h                   IP
-P                   端口
-S                   socket文件

问题：
怎么证明你的数据库是可用的？
1、证明进程和端口存在
ps -es |grep '3306'
netstat  -lntup|grep 3306;
2、验证用户远程连接性
[root@mysql ~]# mysql -uroot -p123456 -h 10.0.0.51 

-e                   免交互执行命令
<                    导入SQL脚本
[root@mysql ~]# mysql -uroot -p123456 -h 10.0.0.51 -e "show databases;"
[root@mysql ~]# mysql -uroot -p </root/world.sql
=======================================================

#2、远程客户端工具
Navicat  sqlyog.
=======================================================

8、MySQL启动方式介绍

image.png

辅助脚本方式（普通启动关闭）：
sys-v：/etc/init.d/mysqld （c6）

/etc/init.d/mysqld start 
----> /application/mysql/bin/mysqld_safe
----> /application/mysql/bin/mysqld


systemd：/etc/systemd/system/mysqld.service （c7）

systemctl start mysqld
----> /application/mysql/bin/mysqld

维护性的启动方式：
/application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
/application/mysql/bin/mysqld &

9、MySQL 初始化配置

#1、预编译时进行设置（略）
只能在编译安装中实现
硬编码在配置到程序中
=======================================================

#2、初始化配置文件（my.cnf）
2.1 初始化默认读取位置
[root@mysql ~]# mysqld --help --verbose |grep "my.cnf" |awk NR==1
/etc/my.cnf   ---> /etc/mysql/my.cnf   ---> /usr/local/mysql/etc/my.cnf   ---> ~/.my.cnf 

--defaults-file=/opt/my.cnf           #人为指定读取方式
只能这两种方式
mysqld_safe
mysqld

#例子：
[root@mysql ~]# mysqld_safe  --defaults-file=/opt/my.cnf  & 
=======================================================

#3、命令行模式
[root@mysql ~]# mysqld_safe  --defaults-file=/opt/my.cnf  --socket=/tmp/assds & 
=======================================================

#4、初始化配置文件应用
4.1 作用
数据库的启动：mysqld    mysqld_safa
客户端的连接：mysql   mysqldump  mysqladmin

4.2 书写格式
[程序名]
配置项=xxx
配置项=xxx
配置项=xxx
配置项=xxx

服务端
[server]  
[mysqld]
[mysqld_safa]

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

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
=======================================================

10、配置MySQL多实例

#1、 创建目录
mkdir -p /data/330{7,8,9}/data
=======================================================

#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
=======================================================

#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、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、 授权
chown -R mysql.mysql /data/*
=======================================================

#6、 启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
=======================================================

#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"
=======================================================

#连接多实例（可以不做）
[root@mysql ~]# mysql -uroot -p -S /data/3308/mysql.sock
=======================================================

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

MySQL数据库-day-2 2019-06-14

第二章MySQL体系结构与管理

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

2、MySQL实例的构成 ***

3、MySQL中mysqld服务器进程结构

3、MySQL数据库逻辑结构

4、MySQL数据物理结构

5、用户管理

6、权限的管理

7、MySQL 连接管理

8、MySQL启动方式介绍

9、MySQL 初始化配置

10、配置MySQL多实例

推荐阅读更多精彩内容