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

作者: MySQL_oldguo | 来源:发表于2021-04-28 17:38 被阅读0次

本课程，适合具备一定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 &