mqsql-02自用

  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
mysql> select user,host from mysql.user;

1.3.2 连接层

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

（3） 派生一个专用连接线程（接收SQL，返回结果）
      mysql> show processlist;
思考： 
忘记密码的参数在哪做的手脚？
--skip-grant-tables  
--skip-networking

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  ---->数据行和索引

面试题： 
说明MyISAM和InnoDB在存储方式上的异同？

1.5.2 微观

段，区，页
一个表就是一个段，mysql分配空间时至少分配一个区
每个区默认是1M（64 个 pages），MySQL最小的IO单元是PAGE(16K)

文件句柄，恢复rm的数据。
  1. 基础管理
    2.1 用户管理
    2.1.1 作用
登录
管理数据库对象

2.1.2 用户的定义

用户名@'白名单'
白名单？
oldguo@'10.0.0.51'
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'%'
oldguo@'oldguo.com'
oldguo@'localhost'
oldguo@'db01'

常用的：
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'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 oldboy@'10.0.0.%';
2. grant delete on wordpress.* to oldboy@'10.0.0.%';
3. grant insert on wordpress.t1 to oldboy@'10.0.0.%';
问，oldboy@'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用户是乱用，学会保护自己。
可以提意见，生成流程。
  1. 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 -p -h10.0.0.51 
[root@db01 ~]# mysql -uoldguo -p123  -h10.0.0.51 -e "show databases;"
[root@db01 ~]# mysql -uroot -p <world.sql

3.2 远程的客户端工具

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