第二章 MySQL的体系结构与基础管理

本课程，适合具备一定Linux运维或者开发基础的朋友，课程定级中、高级DBA。
只要掌握80%，轻松助力薪资15k-25K。
课程内容均来自与MySQL官网+MySQL源码。
配套精品视频(2021 5月全新录制，版权所有：郭加磊 oldguo。)，获取方法私聊。

1. MySQL C/S结构

Clinet : mysql mysqldump ,sqlyog,API
Server : mysqld守护进程

image.png

2. 实例(instance)

实例: mysqld + 线程(Master thread , IO ,SQL ,purge...) + 预分配内存(数据\日志\线程)
公司: boss + 员工(总经理+普通员工) + 办公室

3. MySQL服务的构成

image.png

3.1 Server层

a. 连接层

b. SQL 层

3.2 engine 存储引擎层

4. 对象存储结构和逻辑结构的对比

逻辑结构:

库 : 库名,库属性(字符集,校对规则)
表 : 列+行+表属性+表名

存储结构:

库---> 目录
表---> xx.ibd ---> 区(extents)---->页(pages)

5. 用户管理

5.1 作用

登陆数据库
管理数据库对象

5.2 长成啥样?

名字@'白名单'
白名单? ----> 在白名单中的IP才能连MySQL
oldguo@'localhost' --> 本地用户
oldguo@'10.0.0.2' --> 单一IP
oldguo@'10.0.0.%' --> 范围IP
oldguo@'10.0.0.5%' --> 范围IP
oldguo@'10.0.0.0/255.255.254.0' --> 范围IP
oldguo@'%' --> 范围IP

安全规范:

a. 白名单尽量小,最好细化到单一IP, %要不得.
b. 用户名有特点.
c. 无用的用户要删除或者锁定.
d. 密码超过三种复杂度,12位以上.

5.3 查\增\删\改

5.3.1 查询

mysql> desc mysql.user;
mysql> select user,host ,authentication_string ,plugin from mysql.user;
mysql> select user as "用户",host as "白名单" ,authentication_string as "密码",plugin as "插件" from mysql.user;
mysql> select user as "用户",host as "白名单" ,plugin as "插件" from mysql.user;

5.3.2 创建用户

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

命令不会,看帮助.
mysql> help create user;

5.5.3 彩蛋:

8.0 之后必须先建用户后授权,grant不再支持建用户功能和密码修改功能;

密码插件,8.0之前使用mysql_native_password,8.0 之后使用caching_sha2_password

导致的问题: 使用老的客户端程序,连接不了8.0版本
解决方法:
a. 建用户时,指定mysql_native_password插件进行密码加密.
b. 修改用户时,可以修改插件
c. 配置文件中指定默认加密插件为mysql_native_password

5.3.4 修改用户

mysql> alter user oldboy@'10.0.0.%'  identified with mysql_native_password by '123';
mysql> select user as "用户",host as "白名单" ,plugin as "插件" from mysql.user;

mysql> alter user oldboy@'10.0.0.%' ACCOUNT LOCK;
mysql> alter user oldboy@'10.0.0.%' ACCOUNT UNLOCK;

5.3.5 删除用户

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

6. 权限管理

6.1 作用

约束用户能够对数据库对象(库,表)干啥(SQL).

6.2 权限列表

mysql> SHOW PRIVILEGES;
ALL ?   除了Grant option所有权限.

2.3 授权

grant 权限 on 权限范围 to 用户;
权限范围?
*.* 全局范围,包含了所有库表 chmod 777 -R /
wordpress.* 单库范围 chmod 777 -R /wordpress
wordpress.t1 单表

mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant all on *.* to  root@'10.0.0.%';
mysql> create user wp_user@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant select,update,delete,insert  on wordpress.* to  wp_user@'10.0.0.%';

6.4 查询用户权限

6.4.1 专用命令

mysql> show grants for wp_user@'10.0.0.%';

6.4.2. 授权表查询

use mysql ;
user         :  user  host auth plugin  全局授权(*.*)的权限   
db           :  user  host              单库范围授权(wordpress.* )的用户权限
table_priv   :  user  host              单表范围授权(wordpress.t1)的用户权限

6.5 回收权限

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

6.6 角色 role MySQL 8.0 中的role支持

需求
oldguo.*  rw(insert,update,delete,select)     --->oldguo_rw
oldguo.*  r (select)                          --->oldguo_r

mysql> create role oldguo_rw,oldboy_r;
mysql> grant select on oldguo.* to oldboy_r;
mysql> grant select,update,insert,delete  on oldguo.* to oldguo_rw;
mysql> create user user1@'%' identified by '123';
mysql> create user user2@'%' identified by '123';
mysql> grant oldguo_r  to user1@'%';
mysql> grant oldguo_rw  to user2@'%';

6.7 彩蛋本地管理员root@'localhost',密码忘记(误删除\误修改)

a. 重启数据库到无验证模式 
[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
b. 登录改密码 
[root@db01 ~]# mysql
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123';
c. 正常启动数据库
[root@db01 ~]# /etc/init.d/mysqld restart

6.8 彩蛋：如何暴力破解弱口令

7. 连接管理

7.1 自带客户端程序

7.1.1 mysql

(1) 本地 socket文件连接 
条件: localhost用户需要提前创建.
[root@db01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock
(2) 网络连接串(TCP/IP)
条件: 远程连接用户必须出现在白名单.
[root@db01 ~]# mysql -uoldguo -p123 -h10.0.0.51 -P3306

7.2 开发工具

sqlyog 
navicat 
workbench

7.3 程序(驱动)连接

php 
python
go
java
等。

8. 配置文件

8.1 方式

a. 源码包,编译时配置一些参数(CMAKE)
b. 配置文件
c. 命令行指定

8.2 配置文件应用

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

注意:
a. 多个文件会依次从左到右读取,如果有重复,最后读取的生效.
b. 启动时,强制指定读取哪个配置文件(--defaults-file=/opt/aa.txt),mysqld mysqld_safe 程序能够调用

8.2.2 基本结构

[mysqld]
user=mysql
basedir=/usr/local/mysql 
datadir=/data/3306/data
port=3306 
socket=/tmp/mysql.sock 
[client]
socket=/tmp/mysql.sock

[标签] :
[服务端]:  [mysqld]  [mysqld_safe]  [server]
影响: 数据库启动,初始化.
[客户端]:  [mysql] [mysqldump]     [client]
影响:  只影响到本机客户端程序运行.

9. 启动关闭

9.1 启动方式

mysqld & 
mysqld_safe & 
mysql.server start
service  start    systemd   start

9.2 关闭方式

mysql.server stop 
sys-v  systemd  stop
mysqladmin -uroot -p123 shutdown  
shutdown

10. 多实例

10.1 同版本

10.1.1 多套目录

mkdir -p /data/330{7..9}/data 
chown -R mysql. /data

10.1.2 配置文件

cat >/data/3307/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql 
datadir=/data/3307/data
port=3307 
socket=/tmp/mysql3307.sock 
EOF

cat >/data/3308/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql 
datadir=/data/3308/data
port=3308 
socket=/tmp/mysql3308.sock 
EOF


cat >/data/3309/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql 
datadir=/data/3309/data
port=3309 
socket=/tmp/mysql3309.sock 
EOF

10.1.3 初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

10.1.4 启动数据库

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &


cat >/etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF


systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309

10.2 多版本多实例

10.2.1 5.6和5.7 解压和软连接

[root@db01 opt]# ln -s /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql57
[root@db01 opt]# ln -s /opt/mysql-5.6.46-linux-glibc2.12-x86_64  /usr/local/mysql56

10.2.2 目录及授权

mkdir -p /data/331{6..7}/data 
chown -R mysql. /data

10.2.3 配置文件

cat >/data/3316/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql56 
datadir=/data/3316/data
port=3316 
socket=/tmp/mysql3316.sock 
EOF

cat >/data/3317/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql57 
datadir=/data/3317/data
port=3317 
socket=/tmp/mysql3317.sock 
EOF

10.2.4 初始化数据

/usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/3317/data
/usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56 --datadir=/data/3316/data

10.2.5 启动

[root@db01 opt]# /usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &
[root@db01 opt]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

10.2.6 连接

[root@db01 opt]# mysql -S /tmp/mysql3307.sock
[root@db01 opt]# mysql -S /tmp/mysql3316.sock
[root@db01 opt]# mysql -S /tmp/mysql3317.sock

11. 升级

11.1 升级方式

a. inplace (就地升级)
适合于有主从环境.
b. merging (逻辑备份迁移升级)

11.2 升级注意事项（INPLACE）

来自于MySQL官网
Upgrade is only supported between General Availability (GA) releases.
Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.6 release before upgrading to MySQL 5.7.
Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.5 to 5.7 is not supported.
Upgrade within a release series is supported. For example, upgrading from MySQL 5.7.x to 5.7.y is supported. Skipping a release is also supported. For example, upgrading from MySQL 5.7.x to 5.7.z is supported.

翻译:
a. 支持GA版本之间升级
b. 5.6--> 5.7 ,先将5.6升级至最新版，再升级到5.7
c. 5.5 ---> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--->5.7 最新
d. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
e. 降低停机时间（停业务的时间）

11.3 INPLACE 升级过程原理

1.备份原数据库数据
2. 安装新版本软件
3. 关闭原数据库（挂维护页
4. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--skip-networking)
5. 升级：只是升级系统表。升级时间和数据量无关的。
6. 正常重启数据库。
7. 验证各项功能是否正常。
8. 业务恢复。

11.4 5.6.48 ----> 5.7.30 Inplace 升级演练

11.4.1 安装新版本软件 5.7.30

ok。

11.4.2 停原库 ,做冷备.

a. 快速关库功能关闭(优雅关闭)

连接到数据库中（5.6.48），执行以下语句:
[root@db01 ~]# mysql -S /tmp/mysql3316.sock -e "set global innodb_fast_shutdown=0 ;"
[root@db01 ~]# mysql -S /tmp/mysql3316.sock -e "select @@innodb_fast_shutdown ;"
[root@db01 ~]# /usr/local/mysql56/bin/mysqladmin -S /tmp/mysql3316.sock  shutdown

b. 冷备:

[root@db01 ~]# cp -r /data/3316/data/ /tmp/bak

c. 使用高版本软件（5.7.30）挂低版本（5.6.48）数据启动

[root@db01 data]# vim /data/3316/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3316/data
socket=/tmp/mysql3316.sock
port=3316
innodb_fast_shutdown=0

[root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking &

d. 升级（升级到8.0可以省略）

[root@db01 data]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql3316.sock --force

e. 重启数据库到正常状态

[root@db01 data]# /usr/local/mysql57/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
[root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &

11.5 Mysql 5.7.30 Inplace升级到MySQL 8.0.20

11.5.1 升级之前的预检查

a. 安装mysqlsh

[root@db01 opt]# tar xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz 
[root@db01 opt]# ln -s /opt/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
[root@db01 opt]# cd /usr/local/mysqlsh/
[root@db01 bin]# vim /etc/profile
export PATH=/usr/local/mysqlsh/bin:$PATH
[root@db01 bin]# source /etc/profile
[root@db01 bin]# mysqlsh --version
mysqlsh   Ver 8.0.20 for Linux on x86_64 - for MySQL 8.0.20 (MySQL Community Server (GPL))

b. 在5730数据库中创建链接用户

[root@db01 bin]# mysql -S /tmp/mysql3317.sock
mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant all on *.* to root@'10.0.0.%';

c. 升级前检测

[root@db01 ~]# mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()" >>/tmp/up.log

11.5.2 正式升级

a. 安装新版本软件 8.0.20

ok。

b. 停原库

# 1. 快速关库功能关闭(优雅关闭)  

连接到数据库中（5.7.30），执行以下语句。

mysql> set global innodb_fast_shutdown=0 ;
mysql> select @@innodb_fast_shutdown;
mysql> shutdown ;

c. 使用高版本软件（8.0.20）挂低版本（5.7.30）数据启动

[root@db01 data]# vim /data/3317/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3317/data
socket=/tmp/mysql3317.sock
port=3317

[root@db01 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &

d. 重启数据库到正常状态

[root@db01 data]# mysqladmin -S /tmp/mysql3317.sock shutdown
[root@db01 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

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