摘星小筑

DBA MySQL基础学习

MySQL简介

​ MySQL是由瑞典MySQL AB公司开发，目前属于 Oracle （甲骨文）公司旗下，是当下最流行的关系型数据库管理系统软件，并且由于SQL语句具有通用的特性，在学习完MySQL后学习其他RDBMS过程中也同样能使用相同的语法进行操作。
​ MySQL是一个C/S架构的软件，可以在本地通过socket文件进行登录，也可在远端使用TCP/IP协议进行登录。

MySQL版本

MySQL版本丰富，目前主流版本是5.6或5.7，以下是MySQL各个版本的发行日期，我将使用Linux平台与MySQL5.7.28进行演示：

​ GA代表稳定版

Feature MySQL Series
First release 3.11.1（1996） MySQL AB
Query Cache, Unions, Full-text, InnoDB 4.0 （2003-03）
Subqueries, R-trees 4.1 （2004-10）
Stored Routies, Views, Cursors, Triggers, XA Transactions, I_S 5.0 （2005-10）
Event scheduler, Patitioning, Plugin API, RBR, InnoDB Plugin, MySQL Cluster 5.1 （2008-11） SUN
Dtrace support, Semisync-replication, P_S, Supplementary Unicode characters 5.5 GA版本5.5.8 （2010-12-03） Oracle
Online-DDL, GTID, Parallel Replication, ICP, MRR ...etc... 5.6 GA版本5.6.10（2013-02-05）
XX 5.7 （2013-02） GA版本5.7.10 （2015-12-07）
XX 8.0.0 （2016-09-12）
XX 8.0.11（2018-04-19 ）GA版本

安装启动

软件下载

​ 打开Chorme浏览器，进入官网找到需要下载的版本，在这里将演示直接安装tar.gz格式Binary版本免编译的包。

​ 点我跳转

image-20210302114642770

​ 复制出链接，使用wget命令对其进行下载。

T > cd ~
T > wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

配置目录

​ 我们需要三个目录来存放mysqld.service服务的不同的数据。

T > mkdir -p /usr/local/application/mysql57/	  # 存放MySQL软件服务，即安装包路径
T > mkdir -p /usr/local/application/mysql57/data  # 存放MySQL服务产生的数据，库、表等信息
T > mkdir -p /usr/local/application/mysql57/logs  # 存放MySQL服务产生的日志等信息

​

tar.gz安装

​ 下载完成之后，执行以下命令进行解压操作：

T > tar zxvf ./mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /usr/local/application/mysql57
T > mv /usr/local/application/mysql57/mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/application/mysql57/mysql

环境变量

​ 接下来是MySQL环境变量的配置，打开下面文件：

T > vim /etc/profile

​ 写入以下内容：

export PATH=/usr/local/application/mysql57/mysql/bin:$PATH

​ 记得刷新配置：

T > source /etc/profile

​ 输入以下命令，显示版本信息则代表配置成功：

T > mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

用户与授权

​ MySQL应当由独立的用户进行管理，所以我们需要创建一个名为mysql的用户组与用户。

​ 在创建之前，先查看是否默认创建了该用户，如果没有创建再进行手动创建也不迟。

T > id mysql
# 没有该用户，手动创建
T > groupadd mysql
T > useradd -g mysql mysql

​ 我们需要对该用户进行目录进行授权操作，命令如下：

T > chown -R mysql:mysql /usr/local/application/mysql57

配置文件

​ 使用配置文件可以对mysql.server服务启动后的初始化参数进行配置，首先我们需要输入以下命令打开配置文件：

T > vim /etc/my.cnf

​ 写入的内容如下：

[mysqld]
user=mysql                                                  # mysql用户配置
server_id=3306                                              # 当前mysql服务的ID号，适用于多实例mysql服务
port=3306                                                   # 当前mysql服务的端口号
basedir=/usr/local/application/mysql57/mysql                # 当前mysql服务软件目录
datadir=/usr/local/application/mysql57/data                 # 当前mysql服务的数据存放目录
log_error=/usr/local/application/mysql57/logs/mysqld.log    # 当前mysql服务中运行日志存放目录及日志名称
socket=/tmp/mysql.sock                                      # socket连接时的文件，本地登录使用

[mysql]
default-character-set=utf8mb4            # 设置mysql客户端链接当前mysql服务时所使用的字符集为utf8mb4
socket=/tmp/mysql.sock                   # socket连接时的文件，本地登录使用

[client]
port=3306                                # 设置mysql客户端连接当前mysql服务时默认使用的端口

初始化

​ 初始化的命令有两条：

—-initialize			# 常规初始化
--initialize-insecure		# 免密初始化

​ 如果你使用常规初始化，它会生成一个随机密码，可以在配置的日志目录中进行查看，文件名为：mysqld.log。

​ 推荐使用免密初始化：

T > mysqld --initialize-insecure

​ 所有的初始化工作完成后都在内部生成自带库与表，并且，常规初始化还会其他额外的功能：

  1. 为root@localhost用户设置临时密码
  2. 对密码复杂度进行定制，规定其root的登录密码必须为12位，必须具有4种格式（必须拥有大写字符，小写字符，数字，特殊字符）
  3. 临时密码过期时间为180s

sys服务

​ 现在只是初始化完成，但是还没有将mysqld加入systemctl系统服务管控中，也就无法开启服务。

​ 所以现在我们需要对mysqld制作成系统服务，输入以下命令

T > cat >/etc/systemd/system/mysqld.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/application/mysql57/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

​ 然后就可以开启mysqld.service服务：

T > systemctl start mysqld.service

​ 尝试登陆：

T > mysql -uroot

​ 将mysqld.service服务进行开机启动：

systemctl enable mysqld.service

常见错误

​ 例举一些常见的错误：

​ 错误1：

.. ERROR! The server quit without updating PID file (...).

​ 这个错误可能是因为你的系统上已经有mysqld.service服务启动了，导致了端口占用，你需要kill掉旧的mysqld.service服务：

T > ps aux | grep 3306
# 找到相关的的服务PID号
T > kill PID号

​ 错误2：

Can 't connect to local MySQL server through socket /tmp/mysql.sock

​ 这个错误可能是因为你的系统上用于root用户本地登录的sock文件被删除，一般来说重启mysql.server服务即可。

​ 操作步骤：

T > pkill mysql*
T > systemctl start mysqld.service

​ 除此之外，你也可以使用TCP/IP协议进行登录：

T > mysql -uroot -p密码 --protocol tcp -hlocalhost

​ 其他的错误可能是由配置文件导致的，检查配置文件是否是/etc/my.cnf，不要把路径配置错了。

登录与密码

设置密码

​ 如果你使用免密初始化后想为root用户生成一个密码，可在终端中输入如下命令：

T > mysqladmin -uroot password "密码"

登录参数

​ MySQL支持两种形式的链接，即TCP/IP协议登录和本地mysql.sock文件登录：

# mysql.sock文件登录
T > mysql -u用户名 -p密码 -S /tmp/mysql.sock

# TCP/IP协议登录
T > mysql -u用户名 -p密码 -h地址 -P端口号

​ 在本地登录时，都是采用mysql.sock文件进行登录，如果是单一的MySQL服务实例，则后面-S的参数可以省略。

​ 在远端登录时，都是采用TCP/IP协议进行登录。

​ 以下例举一些常用的登录参数项：

-h：IP地址

-P：端口号，MySQL端口号默认为3306

-u：用户名

-p：密码

-e：一段执行命令

<：导入SQL语句文件

​ 使用参数e的案例，导出某些查询内容：

T > mysql -uroot -e"SHOW DATABASES;" > /tmp/data
T > cat /tmp/data

# 打印内容：
Database
information_schema
mysql
performance_schema
sys

​ 使用参数<的案例，导入并执行SQL语句：

T > mysql -uroot < /tmp/other.sql

用户查看

​ 登录MySQL后，使用以下命令可查看到自己登录的用户：

M > SELECT USER();

​ 使用以下命令可查看到当前链接该MySQL服务的所有用户与链接线程：

M > SHOW PROCESSLIST;

忘记密码

​ MySQL数据目录下默认会生成一些自带的数据库，其中user表就是做授权验证的。

​ 这使得MySQL客户端必须先经过授权登录后才能对服务端进行一系列的操作，但是我们也可以通过一些技术手段绕过这个授权。

T > ls /usr/local/application/mysql57/data/mysql | grep user

user.MYD
user.MYI
user.frm

​ 如何在忘记当前登录密码的情况下修改密码呢？以Linux平台为例：

# 1.关闭需要授权登录的MySQL服务端
T > systemctl stop mysqld.service

# 2.开启mysql_safe免授权登录的服务端，并且暂时的禁止远端登录
T > mysqld_safe --skip-grant-tables --skip-networking 

# 3.开启新终端，使用root用户进行登录，并且修改密码
T > mysql -uroot -S /tmp/mysql.sock
M > UPDATE MYSQL.USER SET authentication_string=password('123') WHERE user = 'root' AND host="localhost";

# 4.将内存中的数据刷写到磁盘并退出
T > FLUSH PRIVILEGES;
M > exit;

# 5.关闭免授权、关闭禁止远端访问的mysqld_safe服务进程
T > ps -ef | grep mysql
T > kill -9 mysql_safe进程PID

# 6.开启正常服务，尝试登录
T > systemctl start mysqld.service
T > mysql -uroot -p123 -S /tmp/mysql.sock

用户与权限

基础概念

​ 在MySQL中，我们可以使用root用户创建出一些新的用户并为他们分配一些权限，如可编辑那些数据库，可使用那些SQL语句等等。

​ 打个比方，一个开发部门可能公用一个数据库，而各个开发小组的组长包括成员只能查看或编辑自身业务范围之内的记录，这种需求下就需要使用到用户管理与权限管理。

系统库表

​ 用户与权限相关的信息存储在内置库mysql中，共有四张表，如下所示：

表名 描述
user 针对所有数据库，所有库下所有表，以及表下的所有字段
db 针对某一数据库，该数据库下的所有表，以及表下的所有字段
tables_priv 针对某一张表，以及该表下的所有字段
columns_priv 针对某一个字段

用户管理

​ 用户管理需要使用root账户，且总体来说都是对mysql.user做操作，除了查看用户的命令外，其他的操作都需要将数据刷写到磁盘。

​ 以下是一些创建用户的示例演示：

# 创建用户名为Yunya的用户，允许该用户从本地进行登录
M > CREATE USER "Yunya"@"localhost" IDENTIFIED BY "123";

# 创建用户名为Yunya的用户，允许该用户从192.168.31.10进行登录
M > CREATE USER "Yunya"@"192.168.31.10" IDENTIFIED BY "123";

# 创建用户名为Yunya的用户，允许该用户从192.168.31.xxx的网段进行登录
M > CREATE USER "Yunya"@"192.168.31.%" IDENTIFIED BY "123"; 

# 创建用户名为Yunya的用户，允许该用户从任意ip地址的网络进行登录
M > CREATE USER "Yunya"@"%" IDENTIFIED BY "123"; 

# 将内存中的数据刷写到磁盘
M > FLUSH PRIVILEGES;

# 在MySQL8版本以前，允许创建用户的同时进行授权操作
M > GRANT ALL ON *.* TO Yunya@"%" IDENTIFIED BY "123";

# %代表通配符，与RegExp中的.类似

​ 查询所有用户，则可以使用以下的命令：

M > SELECT user,host FROM mysql.user;

​ 修改用户密码的方式有两种：

# 方式一，非root用户：
M > ALERT USER Yunya@"localhost" IDENTIFIED BY "123";

# 方式二，修改root用户的密码：
M > UPDATE MYSQL.USER SET authentication_string=password('123') WHERE user = 'Yunya' AND host = "localhost";

# 将内存中的数据刷写到磁盘
M > FLUSH PRIVILEGES;

​ 删除用户的方式有两种：

# 方式一，非root用户：
M > DROP USER Yunya@"localhost";

# 方式二：
M > DELETE FROM mysql.user WHERE user = "Yunya" AND host = "localhost";

# 将内存中的数据刷写到磁盘
M > FLUSH PRIVILEGES;

权限管理

​ 权限相关操作必须由root进行，除了查看命令外，其他的操作都需要将数据刷写到磁盘。

​ 分配权限的相关操作：

# 为Yunya分配所有权限
M > GRANT all ON *.* TO "Yunya"@"%";

# 为Yunya分配db1数据库下的所有数据表的查看权限
M > GRANT select ON db1.* TO "Yunya"@"%";

# 为Yunya分配db1数据库下的t1数据表的查看id，name字段与更新age字段的权限
M > GRANT select(id,name),update(age) ON db1.t1 TO "Yunya"@"%";

# 将内存中的数据刷写到磁盘
M > FLUSH PRIVILEGES;

​ 查看用户权限的命令：

M > SHOW GRANTS FOR root@'localhost';

​ 释放用户权限的命令：

# 释放掉Yunya的所有权限
M > REVOKE all ON *.* FROM "Yunya"@"%";

# 将内存中的数据刷写到磁盘
M > FLUSH PRIVILEGES;

三层架构

基础概念

​ 我们知道MySQL是通过SQL语句进行操作的，其实当一个Client端链接到Server端后并提交命令这一过程可分为三个环节。

图片描述

图片描述

链接层

​ 链接层的主要作用有以下三点：

  1. 提供链接协议，socket与TCP/IP
  2. 验证用户名即密码的合法性，会对登录用户匹配专门的授权表
  3. 派生出一个专用的链接线程，用于接收SQL命令并返回结果

SQL层

​ SQL层主要作用于调优方面的工作，也是DBA及其需要关注的一层，主要作用如下所示：

  1. 验证SQL语法与sql_mode
  2. 验证语义
  3. 验证权限
  4. 进入解析器进行语句解析，生成多个执行计划（解析树）
  5. 进入优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划（代价：CPU IO MEM）
  6. 执行器按照优化器选择的执行计划，执行SQL语句，得出获取数据的方法
  7. 提供查询缓存（默认关闭）
  8. 记录操作日志binlog（默认关闭）

存储层

​ 存储层依靠存储引擎与磁盘打交道，与Linux上文件系统的概念相似，其功能是对磁盘上的数据进行存取。

​ 根据SQL层所提供的取数据的方法，拿到数据，返回给SQL层将其结构化成表，再由链接层线程返回给用户。

​ 在后续章节存储引擎中，将会详细介绍这一过程。

存储结构

逻辑存储结构

​ MySQL逻辑存储结构是按照库表进行存储，并且每个表中还具有字段（列），字段属性，数据行（记录），表属性（元数据，如字符编码校对规则等）等信息。

image-20210213215535089

物理存储结构

​ 对于MySQL中的一个库来说，实际上就是一个文件夹。

​ 如下所示，我创建一个db1的库：

M > CREATE DATABASE db1;

​ 在数据根目录中就能看到这样的一个以库名命名的目录：

T > ll /usr/local/application/mysql57/data/ | grep db1
drwxr-x---    3 _mysql  _mysql       102  2 13 21:57 db1

​ 对于MySQL中的一个表来说，就需要涉及到存储引擎，因为不同的存储引擎对表的物理存储结构也有所不同。

​ 在MyISAM存储引擎中，表的物理结构如下：

使用tableName.frm文件存储表结构，如字段，字段属性等
使用tableName.MYD存储表的记录
使用tableName.MYI存储表的索引

​ 在InnoDB存储引擎中，表的物理结构如下：

使用tableName.frm文件存储单张表的表结构，如字段，字段属性等
使用tableName.ibd存储单张表的表的记录以及索引信息等
使用ibdata1文件存储每一张表的元信息等属性，如字符编码、校对规则、统计信息等
posted @ 2021-02-13 23:04  云崖君  阅读(186)  评论(0)  编辑  收藏  举报