MySQL体系结构和管理学习笔记day02

1.MySQL C/S模型

Server : mysqld
Client :
socket : 仅本地连接使用
tcp/ip : 应用连接使用
TCP/IP方式（远程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(仅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

2. 实例 **

实例= mysqld ----> master thread ----> 干活的线程 + 预分配的内存结构
公司= boss 经理 员工 办公室

3.mysql的程序结构

3.1 MySQL的专用管理和操作命令SQL

DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言

3.2 SQL语句的执行过程

select user, host form mysql.user;
(1)mysql -u root -pxxxx

3.2.1 连接层

(1) 提供链接协议
socket
TCP/IP
(2) 验证 user password host port
(3) 提供连接线程
show [full] processlist;
功能: 接收SQL语句,返回结果.]

3.2.2 SQL层 *****

(1) 语法检查和SQL_MODE检查
(2) 语义检查和权限检查
(3) 解析预处理,生成解析树(执行计划树)
(4) 优化会根据自带算法,选择最优的方案(代价模型算法)
代价? cpu io mem
(5) 选择最优的方案进行执行SQL
(6) 提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
(7) 提供日志记录（日志管理章节）：binlog，默认是没开启的。

3.2.3 存储引擎层（简单介绍）

相当于文件系统,将数据取出,再交给SQL层结构化成表,返回给用户.

4. MySQL的逻辑结构 ***

4.1 库：

库名
库属性

4.2 表

表名
属性
列:列名(字段),列属性(数据类型,约束等)
数据行(记录)

5. MySQL的物理存储结构引入

5.1 库的物理存储结构

用文件系统的目录来存储

5.2 表的物理存储结构 ***

MyISAM（一种引擎）的表：
user.frm
user.MYD
user.MYI
InnoDB(默认的存储引擎)的表：
time_zone.frm：存储列相关信息
time_zone.ibd：数据行+索引

5.3 InnoDB表底层存储结构引入(扩展)*****

段 : 一个表就是一个的段(非分区表)
区 : 固定大小1M, 连续的64个页
页 : 最小的IO单元,默认16K

6. MySQL基础管理

6.1 用户的管理

6.1.1 用户的作用

6.1.2 用户的定义

6.1.3 用户管理操作

create user oldguo@'10.0.0.%';
drop user oldguo@'10.0.0.%';
alter user oldguo@'10.0.0.%' identified by '123';
select user,host from mysql.user;
create user oldboy@'10.0.0.%' identified by '123';
注:
(1)8.0以前,以上命令可以忽略,grant可以自动创建用户并授权.
(2)8.0以后必须先建用户后授权,grant只做授权功能.

6.2 权限管理

6.2.1 作用

6.2.2 权限定义

6.2.3 授权范围

6.2.4 授权管理命令

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

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 : 普通管理员
ALL+ with grant option 超级管理员 .

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

6.2.5 授权管理生产需求

(1) 授权一个管理员用户admin ,密码admin,能够通过10网段管理数据库
oldguo[(none)]>grant all on . to admin@'10.0.0.%' identified by 'admin';
(2) 开放一个wordpress应用的用户,密码123,nginx服务器使172.16.1.%网段.
应用用户需要权限?
select,insert,update,delete
grant select,insert,update,delete on wordpress.* to wordpress@'172.16.1.%' identified by '123';
(3) 中小公司,开发人员leader,需要开发用户dev通过10网段对wordpress进行开发和管理
grant SELECT,INSERT, UPDATE, DELETE,CREATE,ALTER,CREATE VIEW,SHOW VIEW,CREATE ROUTINE, ALTER ROUTINE,EVENT, TRIGGER on wordpress.* to dev@'10.0.0.%' identified by '123';

6.2.6 权限回收

oldguo[(none)]>show grants for dev@'10.0.0.%';
oldguo[(none)]>revoke delete on wordpress.* from dev@'10.0.0.%';

6.2.7 面试问题

作为开发人员开用户需要怎么沟通?
以邮件形式,进行开用户的申请
向谁申请?
DBA权限管理人员
分管部门领导
DBA主管
提供哪些信息?
需要哪些权限
操作对象
从哪来访问MySQL

最为DBA,开发人员找你开用户需要怎么沟通?
提供哪些信息?
需要哪些权限
操作对象
从哪来访问MySQL

开发人员找你私下要root用户权限?
不给!
金融类:
非本业务部分,不得提供超级管理员用户的权限

6.2.8 本地管理员用户密码忘记

(1) 关闭数据库

(2) 将数据库启动到无验证模式

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

(3) 修改密码

oldguo[(none)]>flush privileges;
oldguo[(none)]>alter user root@'localhost' identified by '123456';

(4)重启数据库为正常模式

[root@db01 ~]# /etc/init.d/mysqld restart

(5)测试新密码

[root@db01 ~]# mysql -uroot -p123456

6.3 MySQL连接管理

6.3.1 MySQL自带命令

mysql
-u
-p
-h
-P
-S
-e
-V
<
--help
socket :
mysql -uroot -p123 -S /tmp/mysql.sock
mysql -uoldguo -p -S /tmp/mysql.sock
注意:本地登录的用户,需要提前授权localhost相关用户
TCP/IP :
mysql -uoldguo -p -h10.0.0.51 -P3306

[root@db01 ~]# mysql -uroot -p123456 -e "show databases;"
[root@db01 ~]# mysql -uroot -p123456 <world.sql

6.3.2 客户端工具连接

oldguo[(none)]>grant all on . to root@'10.0.0.%' identified by '123';
sqlyog
navicat

6.4 MySQL的启动和关闭方式

6.5 MySQL的初始化配置

6.5.1 提供的方法

1. 预编译 **

2. 初始化配置文件*****

3. 命令行 ***

注意: 优先级 3>2>1

6.5.2 初始化配置文件的默认读取顺序

[root@db01 /tmp]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ----> /tmp/mysql.sock
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf ----> /tmp/a.sock

注意:一旦使用--defaults-file,以上的默认配置就不会生效了
[root@db01 ~]# mysqld_safe --defaults-file=/opt/oldguo.cnf &

6.5.3 初始化配置文件作用

数据库的启动
客户端的登录

6.5.4 初始化配置文件格式

[标签1]
xxxx=yyyy
[标签2]
xxxx=yyyy
[标签3]
xxxx=yyyy

标签 :
服务端
[mysqld]
[mysqld_safe]
[server]
客户端
[mysql]
[mysqldump]
[mysqladmin]
[client]

6.5.5 配置文件模板

[mysqld]
user=mysql
server_id=6
port=3306
basedir=/application/mysql
datadir=/data/3306/data
log_error=/data/3306/data/3306.log
socket=/data/3306/mysql.sock
[mysql]
socket=/data/3306/mysql.sock

6.6 MySQL的多实例管理

6.6.1 准备多个目录

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

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

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

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

6.6.5 授权

chown -R mysql.mysql /data/*

6.6.6 启动

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

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