1 体系结构

1.1 C/S模型的体系结构

登录方式：

# TCP方式(远程本地)
mysql -u root -p 123 -h 192.168.1.1 -P 3306
# Socket方式（仅本地）
mysql -u root -p 123 -S /tmp/mysql.sock

默认不允许root用户远程登录数据库

1.2 实例介绍

实例=mysqld后台守护进程+Master Thread+干活的Thread+预分配的内存
公司=老板+经理+员工+办公室

1.3 mysqld程序运行原理

1.3.1 mysqld程序结构

1.3.2 一条SQL语句的执行过程

1.3.2.1 连接层

（1）提供连接协议：TCP/IP、SOCKET
（2）提供验证：用户、密码、IP、SOCKET
（3）提供专用连接线程：接收用户SQL，返回结果
通过以下语句可以查看到连接线程的基本情况

3306 [(none)]>show processlist ;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

1.3.2.2 SQL层

（1）接收上层传送的SQL语句
（2）语法验证模块：验证语句语法
（3）语义检查：判断SQL语句的类型
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言
（4）权限检查：用户对库表有没有权限
（5）解析器：语句执行前，进行预处理，生成解析树（执行计划，也就是多种执行方案）
（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划代价模型：资源（CPU IO MEM）的耗损评估性能好坏
（7）执行器：根据最优的执行计划，执行SQL语句，产生执行结果
执行结果：在磁盘XXX位置上
（8）提供查询缓存（默认没开启），会使用redis、tair替代查询缓存
（9）提供日志记录（默认没开启）：binlog

1.3.2.3 存储引擎层

类似于文件系统，根据SQL层的执行结果，从磁盘拿数据。将16进制的磁盘数据，交由SQL架构化成表，由连接层的专用线程返回给用户。

1.4 逻辑结构

1.4.1 库

库名，库属性(字符集和校对规则)

1.4.2 表（二维表）

表名
表属性
列：列名（字段），列属性（数据类型，约束等）
数据行（记录）

1.5 物理存储结构

1.5.1 库的物理存储结构

用文件系统的目录来存储

1.5.2 表的物理存储结构

MyISAM（一种引擎）的表：

-rw-r----- 1 mysql mysql   10816 Nov  9 21:30 user.frm    #列的相关信息
-rw-r----- 1 mysql mysql     396 Nov 10 20:27 user.MYD    #记录（数据行）的相关信息
-rw-r----- 1 mysql mysql    4096 Nov 10 22:54 user.MYI    #索引信息

InnoDB（默认的存储引擎）的表：

-rw-r----- 1 mysql mysql    8636 Nov  9 21:30 time_zone.frm    #列的相关信息
-rw-r----- 1 mysql mysql   98304 Nov  9 21:30 time_zone.ibd    #数据行+索引

1.5.3 InnoDB的存储结构

InnoDB 存储引擎存储数据行和索引的是.ibd文件，.ibd文件的最小IO单元是页（16KB），数据存储时分配一到多个连续的页，这些连续的页组成区，一张表就是一个段，其中包括多个区。

2 基础管理

2.1 用户、权限管理

2.1.1 用户

作用：登录，管理数据库的逻辑对象（库、表）
定义：用户名@'白名单'
白名单：允许哪些IP能够登录数据库，不写白名单默认是@'%'
支持的配置方式：

bigsky@'%' # 所有地址
bigsky@'10.0.0.%' # 10.0.0.0/24网段地址
bigsky@'localhost' # 本地主机
bigsky@'10.0.0.5%' # 10.0.0.50~10.0.0.59
bigsky@'10.0.0.0/255.255.254.0' 
bigsky@'node1'  # node1是域名

管理操作：

增：
create user bigsky000@'10.0.1.%' identified by '123' ;
查：
desc mysql.user；    #查询 authentication_string
select user ,host,authentication_string from mysql.user ;
改：
alter user bigsky000@'10.0.1.%' identified by '456' ;            #改密码
update mysql.user set host='localhost' where user='root';        #改用户的登录ip
flush privileges; 
删：
drop user bigsky000@'10.0.1.%' ;

2.1.2 权限

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：超级管理员具备的，给别的用户授权的功能
SELECT,INSERT, UPDATE, DELETE：一般授予应用用户

2.1.3 授权命令

grant 权限 on 范围 to 用户 (with grant option)
范围：
*.* ：管理员用户
app.*,app.table：开发和应用用户

1、授权管理员用户远程登录
grant all on *.* to root@'10.0.1.%' ;
# grant all on *.* to root@'10.0.1.%' identified by '123'; # 8.0版本后不可在授权时同时配置密码
2、授权应用用户远程登录，并能操作app库
grant select,insert,update, delete on app.* to app@'10.0.1.%' ;

2.1.4 查看授权

show grants for 用户

3306 [(none)]> show grants for bigsky@'10.0.1.%' ;
+-----------------------------------------------------------+
| Grants for bigsky@10.0.1.%                                |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bigsky'@'10.0.1.%'                 |
| GRANT ALL PRIVILEGES ON `bigsky`.* TO 'bigsky'@'10.0.1.%' |
+-----------------------------------------------------------+

2.1.5 回收权限

revoke 权限 on 范围 from 用户

revoke create on bigsky.* from bigsky@'10.0.1.%' ; 

2.1.6 本地管理员用户密码找回

必须停止数据库，使用安全模式mysqld_safe启动数据库，加上参数--skip-grant-tables使连接层不加载授权表，此时登录数据库不进行验证。推荐加上参数--skip-networking不让TCP/IP方式登录，只能本地socket登录，防止在此时遭受攻击。

[root@node1 ~]$ mysqld_safe --skip-grant-tables --skip-networking  &

此时可以用任意密码登录

[root@node1 ~]$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3306 [(none)]>

因启动数据库没有加载授权表，不能直接用alter命令修改密码，

3306 [(none)]> alter user root@'localhost' identified by '456' ;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

需要手工加载授权表flush privileges，再用alter修改密码

3306 [(none)]>flush privileges ;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>alter user root@'localhost' identified by '456' ; 
Query OK, 0 rows affected (0.00 sec)

之后再重启数据库就可以了

2.2 连接管理

使用mysql命令连接数据库，常见参数如下：

-u 用户
-p 密码
-h IP地址
-P 端口号
-S socket文件
-e 免交互执行命令
<  导入SQL脚本

mysql -u root -p123 -h 192.168.1.1 -P 3306
mysql -u root -p123 -S /tmp/mysql.sock
mysql -u root -p123 -e 'select user,host,authentication_string from mysql.user'

2.3 多种启动方式

mysqld_safe和mysqld一般是在临时维护时使用。
还可以编写systemd的service脚本，使用systemd来启动mysqld进程

2.4 初始化配置

2.4.1 初始化配置的方法

预编译
初始化配置文件（适用于所有的启动方式）
命令行参数(仅限于mysqld_safe和mysqld)

2.4.2 初始化配置文件

初始化配置文件的默认读取路径

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 

默认情况下，会依次读取以上配置文件，如果有重复选项，会以最后一个文件的设置为准。但是如果启动时加入了--defaults-file=xxxx时，以上所有文件都不会读取。

配置文件的书写方式：

[标签]
配置项=xxxx
标签类型：服务器端、客户端

服务器标签:
[mysqld]
[mysqld_safe]
[server]  #包含以上两种

客户端标签：
[mysql]
[mysqldump]
[client]  #包含以上两种

配置文件示例展示：
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
server_id=6  #多实例时用到，唯一标识一个实例，范围1-65535
port=3306
socket=/tmp/mysql.sock
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt= [\\d]>  #提示符格式

2.5 多实例的应用

以3个实例为例

2.5.1 准备多个数据库目录

创建3个数据库文件夹

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

2.5.2 准备配置文件

准备3个my.cnf文件放在不同实例的文件夹下

cat > /data/3307/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3307/data/
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3308/data/
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3309/data/
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
EOF

2.5.3 初始化3套数据库

需要将/etc/my.cnf 文件进行重命名，防止初始化时读取

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --user=mysql --basedir=/app/mysql --datadir=/data/3307/data --initialize-insecure 
mysqld --user=mysql --basedir=/app/mysql --datadir=/data/3308/data --initialize-insecure 
mysqld --user=mysql --basedir=/app/mysql --datadir=/data/3309/data --initialize-insecure 
mv /etc/my.cnf.bak /etc/my.cnf

2.5.4 systemd管理多实例

复制原先配置的service文件

cp /etc/systemd/system/mysqld.service  /etc/systemd/system/mysqld3307.service
cp /etc/systemd/system/mysqld.service  /etc/systemd/system/mysqld3308.service
cp /etc/systemd/system/mysqld.service  /etc/systemd/system/mysqld3309.service

修改其中的--defaults-file配置

sed -i 's/-file=.*/-file=\/data\/3307\/my.cnf/g' /etc/systemd/system/mysqld3307.service 
sed -i 's/-file=.*/-file=\/data\/3308\/my.cnf/g' /etc/systemd/system/mysqld3308.service
sed -i 's/-file=.*/-file=\/data\/3309\/my.cnf/g' /etc/systemd/system/mysqld3309.service

2.5.5 授权

chown -R mysql:mysql /data/*

2.5.6 启动多实例

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

2.5.7 验证多实例

ss -lnp | grep 330
mysql -u