0）环境
# MySQL版本
mysql-5.7.20
# IP地址
10.0.0.51 172.16.1.51
1）体系结构
CS模型介绍(客户端/服务端)
#TCP/IP方式（远程）
mysql -uroot -p123 -h 10.0.0.51 -P3306
#Socket方式（套接字、本地登录、localhost）
mysql -uroot -p123 -S /tmp/mysql.sock
实例介绍
实例=mysqld后台守护进程+Master Thread+干活的Thread+预分配的内存
公司=老板+经理+员工+办公室
mysqld程序运行原理
层次结构
2）一条SQL语句的执行过程
-
命令
#查看MySQL用户信息
>select user,host from mysql.user;
层次介绍
连接层
连接层
1）提供连接协议：TCP/IP、Socket
2）提供验证：用户、密码、IP、Socket
3）提供专业连接线程：接收用户SQL，返回结果
#查看连接线程语句
> show processlist;
SQL层（重点）
SQL层
1）接收上层传送SQL语句
2）语法验证模块：验证SQL语句语法
3）权限检查（用户对库表的权限）
4）语义检查（判断语句类型）
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言 #MySQL查询语句类型
5）解析器：进行SQL的预处理，产生执行计划
6）优化器：根据解析器执行计划进行判断，选择最优的执行计划
代价模型：根据资源耗损进行评估 (CPU IO 内存 )
7）执行器：根据最优执行计划，执行SQL语句，产生执行结果
执行结果：数据在磁盘的XXX位置
8）提供查询缓存，提高效率。（默认关闭，可使用"Redis"或"Tair"替代查询缓存）
9）提供日志记录（日志管理）：Binlog(默认关闭)
存储引擎层（类似于Linux文件系统）
负责根据SQL语句执行结果，调取磁盘数据。(16进制)
将16进制的磁盘数据，交由SQL层转化为表，由连接层的专用线程返回给用户。
MySQL出现故障或性能问题80%以上是人为的。
——《oldguo》
3）逻辑结构
数据库
存放数据表
库名、库权限、库数据类型
数据表（二维表）
列：字段
行：记录
MySQL为了使存入的数据准确、规范、有意义，增强了传统二维表的功能。
表属性：权限、字符集、存储引擎
列属性：数据类型、约束、其他（默认值、自增长、注释）
物理存储结构引入
库的物理存储结构
用文件系统的目录来存储
位置：/data/mysql/data
表的物理存储结构
MyISAM（默认存储引擎）表
user.frm：列的相关信息
user.MYD：数据行
user.MYI:索引信息
InnoDB（默认存储引擎）表
time_zone.frm ：存储列相关信息
time_zone.ibd：数据行+索引
表的页、区、段（了解）
- 页：最小的存储单元 16KB
- 区：1个或多个连续的页
- 段：1个或多个连续的区，一个表就是一个段
4）基础管理
用户、权限管理
- 用户
作用：登陆 管理数据库 - 定义
用户@'白名单'
inanhan@'localhost'
- 白名单：允许登陆的IP地址段
- 支持方式
inanhan@'%' #所有地址
inanhan@'10.0.0.%' #10.0.0.0/24网段
inanhan@'localhost' #本地登陆
inanhan@'10.0.0.5%' #10.0.0.50~10.0.0.59
inanhan@'10.0.0.0/255.255.254.0' #子网划分：10.0.0.0/23
基本用户管理操作
- 增
#'创建用户
>create user inanhan@'10.0.0.%' identified by '123';
- 查
#查MySQL用户名、登陆白名单、密码
>select user,host,authentication_string from mysql.user;
#查询表结构
>desc mysql.user;
- 改
#'更改用户密码
>alter user inanhan@'10.0.0.%' identified by '456';
- 删
#'删除用户
>drop user inanhan@'10.0.0.%';
权限
- 权限介绍
SELECT #允许从表中查看数据
INSERT #允许在表里插入数据
UPDATE #允许修改表中的数据的权限
DELETE #删除行数据
CREATE #允许创建新的数据库和表的权限
DROP #删除数据库与表
RELOAD #允许刷新权限(FLUSH命令)
SHUTDOWN #允许关闭数据库实例
PROCESS #允许查看数据库进程
FILE #允许用户在MySQL进行读写文件磁盘操作
REFERENCES #允许创建外键（5.7.6版本之后引入）
INDEX #允许创建和删除索引
ALTER #允许修改表结构的权限，但必须要求有CREATE和INSERT权限配合
SHOW DATABASES #查看所有的数据库名
SUPER #允许执行一系列数据库管理命令，包括kill强制关闭某个连接
CREATE TEMPORARY TABLES #允许创建临时表的权限
LOCK TABLES #允许对拥有select权限的表进行锁定，以防止其他链接对此表读或写
EXECUTE #允许执行存储过程和函数的权限
REPLICATION SLAVE #允许Slave主机通过此用户连接Master以便建立主从复制关系
REPLICATION CLIENT #允许执行show master status,show slave status,show binary logs命令
CREATE VIEW #查看视图创建的语句：mysqladmin processlist, show engine
SHOW VIEW #代表通过执行show create view命令查看视图创建的语句
CREATE ROUTINE #允许创建procedure,function
ALTER ROUTINE #允许修改或者删除存储过程、函数的权限
CREATE USER #允许创建用户
EVENT #允许查询，创建，修改，删除MySQL事件
TRIGGER #允许创建，删除，执行，显示触发器的权限
CREATE TABLESPACE #允许创建表空间
-------------------------------------------------------------------------------------
ALL #以上所有权限，普通管理员权限
with grant option #超级管理员功能，为其他用户授权
应用用户权限 #SELECT，INSERT，UPDATE，DELETE
- 授权对象
*.* --->chmod 755 -R / ---->针对管理员
inanhan.* --->chmod 755 -R /inanhan ---->应用用户
inanhan.t1 --->chmod 755 -R /inanhan/t1
基本权限管理操作
- 授权命令写法
grant 权限 on 对象 to 用户 identified '密码'；
- 需求01：Windows系统使用Navicat登录到Linux中的MySQL，管理员用户。
#'授权命令
> grant all on *.* to root@'10.0.0.%' identified by '123';
- 需求02：创建一个应用用户 app 用户，能从Windows上登录MySQL，并且可以操作app库。
#'授权命令
> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
- 开发人员用户授权流程
权限
操作对象（库与表）
登陆IP地址
密码要求
- 8.0版本grant命令新特性
#用户创建与授权分开
#不支持自动创建用户与更改密码
- 查看授权信息
>show grants for app@'10.0.0.%'; #查看app用户授权信息
- 回收权限
>revoke delete on app.* from app@'10.0.0.%'; #回收app用户delete权限
5）连接管理
登陆方式
- 登陆方式01：TCP/IP
#连接10.0.0.51数据库（密码隐藏输入）
mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:
- 登陆方式02：Socket
#通过本地socket登陆MySQL
mysql -uroot -p -S /tmp/mysql.sock
Enter password:
#查看登陆socket路径
> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
mysql命令常用参数
-u #用户
-p #密码
-h #IP
-P #端口
-S #socket文件路径
-e #免交互执行命令
< #导入SQL脚本
#免交互查看MySQL数据库用户信息
mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
#导入SQL语句到MySQL数据库
mysql -uroot -p <world.sql
Enter password:
多种启动方式介绍
启动方式
-
提示
以上多种方式，都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
从CentOS 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库。
6）初始化配置
初始化配置文件
#查看初始化配置文件
mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
初始化配置文件读取顺序
#由左到右
/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
- 默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
- 若加入--defaults-file=xxxx时，则直接读取指定文件。
初始化配置书写格式
[标签]
配置项=XXX
...
#标签类型：服务端、客户端
服务端
[mysqld]
[mysqld_safe]
客户端
[mysql]
[mysqldump]
[client]
#实例
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/error.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>
6）多实例的应用
准备环境
#创建目录
mkdir -p /data/330{7,8,9}/data
#准备配置文件
#-----------------------实例01（3307）
vim /data/3307/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
#-----------------------实例02（3308）
vim /data/3308/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
#-----------------------实例03（3309）
vim /data/3309/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
初始化配置
#更改初始数据库配置文件名
mv /etc/my.cnf /etc/my.cnf.bak
#-------------------------------实例01
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/data/mysql
#-------------------------------实例02
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/data/mysql
#-------------------------------实例03
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/data/mysql
systemd管理多实例
#进入system目录
cd /etc/systemd/system
=====================================
#拷贝服务启动脚本
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
=====================================
#更改配置文件倒数第二行内容
#---------------------------实例01
vim mysqld3307.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
#---------------------------实例02
vim mysqld3308.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
#---------------------------实例03
vim mysqld3309.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
授权
#授权MySQL文件目录
chown -R mysql.mysql /data/*
启动实例
# 如有必要，可设置开机自启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
验证多实例
- 端口检测
#检测端口（3306配置文件已改名，所以未启动）
netstat -lnp|grep 330*
tcp6 0 0 :::3307 :::* LISTEN 2932/mysqld
tcp6 0 0 :::3308 :::* LISTEN 2939/mysqld
tcp6 0 0 :::3309 :::* LISTEN 2946/mysqld
检测SERVER ID
#免交互查看SERVER ID
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
登陆数据库
mysql -S /data/3307/mysql.sock #3307
mysql -S /data/3308/mysql.sock #3308
mysql -S /data/3309/mysql.sock #3309
0.0)数据库忘记root密码
#关闭数据库
systemctl stop mysqld
#跳过授权启动
mysql_safe --skip-grant-tables --skip-networking &
--skip-grant-tables : 连接层关闭验证模块，所有验证表不加载。
--skip-networking ：连接层关闭TCP/IP协议，禁止远程访问。
#无密码登陆
mysql -uroot -p 回车
> flush privileges;
> alter user root@'localhost' identified by '456';
#杀掉MySQL进程
pkill mysqld
#启动MySQL
systemctl start mysqld
