一、MySQL 基础架构简介

1.1 MySQL CS架构简介

MySQL是典型的C/S架构，服务器端程序使用的 mysqld，客户端使用 mysql程序 或 各语言的MySQL api库；

不论采用哪种类型的客户端 与 服务器进程进行通信（交互），最后实现的效果都是：客户端进程与服务器端建立连接后向服务器进程发送一段文本（SQL语句），服务器进程处理后再向客户端进程发送一段文本(处理结果)。

下图是 MySQL 的一个简要架构图，从下图可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看 SQL 语句要干嘛，再检查 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

设计一个数据库,首先得有两部分:

1、存储（文件系统）：用于存储数据到硬盘中；

2、程序实例：通知程序把数据存储到文件系统中，并对存储的数据进行管理，程序实例由大致的8个模块组成：

• 存储管理
• 索引管理
• 权限划分
• SQL解析
• 缓存机制
• 日志管理
• 容灾机制
• 锁管理

1.2 MySQl是怎么运行工作的

1、启动 MySQL

• MySQL 初始化整个系统，启动各个存储引擎，当整个系统初始化结束后，连接管理模块接手，准备好接受客户端请求。

2、接受处理服务请求

• 当连接管理模块监听到客户端的连接请求,会将连接请求转发给线程管理模块去请求一个连接线程;
• 线程管理模块又将控制权交给连接线程模块，告诉连接线程模块：现在有连接请求过来，需要建立连接，连接线程池中有空闲连接线程，就取出一个和客户端请求连接上，如果没有，则建立一个新的连接线程与客户端请求连接。

1.3 MySQL的历史发展简介

1、MySQL历史概况

MySQL的历史可以追溯到20世纪90年代初，下面是MySQL的主要里程碑和演变过程的详细介绍：

1. 1979年，瑞典的Monty Widenius在Tcx DataKonsult公司工作，它开发了一款名为Unireg工具，它是一个面向报表的存储引擎，利用索引顺序来读取数据，这也是ISAM存储引擎的算法的前身。
2. 1985年，Monty和David Axmart等几个小伙子成立了一家公司（MySQL AB前身），研发出了ISAM（Indexed Sequential Access Method）存储引擎工具。
3. 1990年，客户要求ISAM工具提供SQL接口，于是Monty找到了David Hughes（mQL的发明人）商讨合作事宜，后来发现mSQL的速度无法满足需求，于是Monty决心自己重写一个SQL支持，由此着手MySQL设计和研发。
4. 1995年，Monty与David Axmart一起协作，开发出了MySQL的第一个版本1.0，仅供内部使用。
5. 1996年1月，MySQL 3.1 发布了，没有2.x版本，最开始只提供了Solaris下的二级制版本，同年11月发布了Lunix版本。
6. 1999年，Monty、Allan 和 David 三人在瑞典创建了MySQL AB公司。同年，发布MySQL 3.23，该版本集成了Berkeley DB存储引擎。该引擎由Sleepycat公司开发，支持事务。在集成该引擎的过程中，对源码进行了改造，为后续可插拔式存储引擎架构奠定了基础。
7. 2000年，MySQL对旧的ISAM进行整理，并命名为 MyISAM存储引擎。同年，MySQL基于GPL协议开放源码。
8. 2002年，MySQL 4.0发布，集成了后来大名鼎鼎的 InnoDB存储引擎，该引擎由Innobase公司开发，支持事务，支持行级锁，适用于OLTP等高并发场景。支持新的语法，比如union和多表的delete语法，引入了查询缓存，同时还支持通过SSL进行连接。重写了复制，在备库使用了两个线程来实现复制，避免了之前一个线程做所有复制工作的模式下的任务切换导致的问题。
9. 2005年，MySQL 4.1，引入了更多新的语法，比如子查询和Insert on duplicate key update。开始支持UTF-8字符集。支持新的二进制协议和prepared语句。
10. 2006年，MySQL 5.0发布，开始支持游标，存储过程，触发器，视图，XA事务等企业级特性。老的ISAM引擎代码被彻底移除。同时引入了新的Federated等引擎。同年，Oracle收购Innobase公司。
11. 2008年，Sun以10亿美金收购MySQL AB。同年，发布MySQL 5.1，其开始支持定时器（Event scheduler），分区，基于行的复制等特性，以及plugin API（包括可插拔存储引擎的API）。
12. 2009年，Oracle以74亿美金收购Sun公司。而其第三方的存储引擎InnoDB早在2006年就被Oracle公司收购。
13. 2010年，MySQL 5.5发布，Oracle对MySQL版本进行了重新划分，分成了社区版和企业版，默认的引擎更换为InnoDB，增加了表分区、增加了Performace_schema库，包含了一些可测量的性能指标的增强。MySQL 5.5可以说是MySQL历史上质量最高的版本。
14. 2013年，MySQL 5.6发布，对InnoDB进行了改造，提供全文索引功能，使InnoDB适合各种场景。
15. 2015年，MySQL 5.7发布，其包括增加了JSON支持、在线DDL和更强大的查询优化功能等重要特性及更新。
16. 2016年，MySQL 8.0首个开发版发布，增加了数据字典，账号权限角色表，InnoDB增强，Jason增强等。
17. 2018年，MySQL 8.0发布。

Tips:

• MySQL的早期版本（1995-1996年）：最早的MySQL版本是基于瑞典公司TcX DataKonsult开发的mSQL数据库系统。MySQL AB的创始人对mSQL进行了改进和优化，发布了MySQL 1.0版，成为第一个商业化的MySQL版本。这个版本具备了关系型数据库的基本功能，并提供了简单的SQL查询语言支持。
• 开源发布（2000年）：为了扩大MySQL的用户群和推广，MySQL AB在2000年将MySQL的核心代码开源，并使用了GNU通用公共许可证（GPL）作为开源许可证。这一举动加速了MySQL的发展，并吸引了全球开发者的关注。开源使得更多人可以参与MySQL的改进和扩展，并为MySQL的普及打下了坚实的基础。
• MySQL AB的发展（2000-2008年）：MySQL AB公司在开源数据库市场上取得了成功，发布了一系列稳定和功能强大的MySQL版本。MySQL逐渐成为流行的数据库选择，尤其在Web应用开发中得到广泛应用。在这段时间里，MySQL不断改进其性能、可靠性和扩展性，并引入了更多的高级功能，如存储过程、触发器和视图等。

2、MySQL版本线

以下是MySQL所有重要版本的发布时间的简要描述：

• MySQL 1.0：最早的商业化版本；发布于1995年。
• MySQL 3.23：引入了许多重要功能，如存储过程、触发器和视图；发布于2001年。
• MySQL 4.0：增加了更多的功能和性能优化，如子查询和InnoDB存储引擎；发布于2003年。
• MySQL 4.1：引入了更多的高级功能和改进，如表分区和行级复制；发布于2004年。
• MySQL 5.0：引入了一系列重要的功能，包括视图、存储过程、触发器、事件调度和InnoDB存储引擎的默认支持；发布于2005年。
• MySQL 5.1：增加了性能和可靠性方面的改进，并引入了复制增强功能；发布于2008年。
• MySQL 5.5：进一步提升了性能和可靠性，引入了半同步复制和InnoDB存储引擎的改进；发布于2010年。
• MySQL 5.6：引入了更多的性能和扩展性优化，包括多线程复制和全文索引改进；发布于2013年。
• MySQL 5.7：增加了JSON支持、在线DDL和更强大的查询优化功能；发布于2015年。
• MySQL 8.0：引入了许多重要的新特性和改进，包括事务性数据字典、窗口函数、公共表表达式、JSON扩展等；发布于2018年。

二、MySQL安装、登录

2.1 Linux 安装环境

1、查看是否安装过MySQL及卸载MySQl

查看是否安装过MySQL：

1
2
3
4
5

# 如果你是用rpm安装, 检查一下RPM PACKAGE：
rpm -qa | grep -i mysql # -i 忽略大小写

# yum 安装方式
yum list installed | grep mysql

卸载上述命令查询出的已安装程序 ：

1

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

务必卸载干净，反复执行`rpm -qa | grep -i mysql 确认是否有卸载残留

卸载系统自带mariadb

1

yum remove mariadb*

删除mysql相关文件:

• 查找相关文件

1

find / -name mysql

• 删除上述命令查找出的相关文件

1

rm -rf xxx

• 删除 my.cnf

1

rm -rf /etc/my.cnf

2、MySQL服务的初始化

• 查看MySQL版本

1
2

mysql --version 
mysqladmin --version

• 服务的初始化

为了保证数据库目录与文件的所有者为 mysql 登录用户，如果是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：

1

mysqld --initialize --user=mysql

Tips： --initialize 选项默认以“安全”模式来初始化，会为 root 用户生成一个密码并将 该密码标记为过期，登录后你需要设置一个新的密码，生成的 临时密码 会记录在 MySQl日志中。

查找默认密码(安装启动后第一次登录密码)：

1
2
3
4

cat /var/log/mysqld.log 
root@localhost: 后面就是初始化的密码
# 或者执行下面命令进行查找
grep 'temporary password' $(grep "log-error" /etc/my.cnf | awk -F'=' '{print $2}')

3、启停MySQL及查看状态

• 启动：systemctl start mysqld.service
• 关闭：systemctl stop mysqld.service
• 重启：systemctl restart mysqld.service
• 查看状态：systemctl status mysqld.service

Tips: 加不加.service后缀都可以

1
2
3
4
5
6
7
8

查看MySQL服务是否自启动 
systemctl list-unit-files|grep mysqld.service

# 如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld.service

# 如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service

2.2 MySQL登录

1、通过mysql命令来登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：

1

mysql -h ${hostname|hostIP} -P ${port} -u ${username} -p ${DatabaseName} -e "SQL语句"

• -h参数 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP，不使用该参数时默认连接本机 MySQl服务器；
• -P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口，MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号；
• -u参数 后面接用户名，username为用户名；
• -p参数 会提示输入密码；
• DatabaseName参数 指明登录到哪一个数据库中，如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用 USE 命令来选择数据库；
• -e参数 后面可以直接加SQL语句，登录MySQL服务器以后执行这个SQL语句，然后退出MySQL服务器；

示例：

1

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

2、修改密码

1

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

3、设置远程登录

• 确认网络:

  • 在远程机器上使用ping ip地址保证网络畅通
  • 在远程机器上使用telnet命令保证端口号开放访问

• 关闭防火墙或开放端口:

  方式一：关闭防火墙

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# CentOS7：开启防火墙
systemctl start firewalld.service
# 查看防火墙状态
systemctl status firewalld.service
# 关闭防火墙
systemctl stop firewalld.service
# 设置开机启用防火墙 
systemctl enable firewalld.service 
# 设置开机禁用防火墙 
systemctl disable firewalld.service

    方式二：开放端口

1
2
3
4
5
6
7

# 查看开放的端口号
firewall-cmd --list-all
- 设置开放的端口号
firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent
- 重启防火墙
firewall-cmd --reload

• MySQL 设置允许远程登陆

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  # 修改允许远程登陆 use mysql; select Host,User from user; update user set host = '%' where user ='root'; flush privileges; # % 是个 通配符 ，如果 host='192.168.1.%'，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果 host='%'，表示所有IP都有连接权限。 # 注意：在生产环境下不能为了省事将host设置为 %，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置。 # 配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。 # 解决方法一： 升级远程连接工具版本 # 解决方法二： ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

三、字符集的相关操作

3.1 各级别的字符集

MySQL 命令行查看各级别的字符集：

1

show variables like 'character%';

• character_set_server：服务器级别的字符集
• character_set_database：当前数据库的字符集
• character_set_client：服务器解码请求时使用的字符集
• character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results：服务器向客户端返回数据时使用的字符集

字符集应用规则：

• 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则；
• 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则；
• 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则；

3.2 请求到响应过程中字符集的变化

四、MySQL的目录

4.1 MySQL的相关目录

查找MySQl安装目录（命令及配置文件等）

1

find / -name mysql

数据库文件的存放路径：

1

show variables like 'datadir'; # /var/lib/mysql/

配置文件目录：

1
2

ls -l /usr/share/mysql  #（命令及配置文件）
ls -l /etc/mysql        #（如my.cnf） 

4.2 数据库和文件系统的关系

MySQL 在数据库文件的存放路径 (show variables like 'datadir';) 下创建与 database名相同的子目录（一个database名对应一个同名子目录）用于存储 该数据库下的 所有表结构、索引 及 表数据 等数据。不同类型的存储引擎下，表的存储方式（文件名及结构等）有着一些相似和不相同之处。

1、InnoDB存储引擎模式

表结构：为了保存表结构，InnoDB 在 数据目录 下对应的数据库（数据库名）子目录下创建了一个专门用于 描述表结构的文件 —— 表名.frm

表中数据和索引:

• 系统表空间（system tablespace）

默认情况下，InnoDB 会在数据目录下创建一个名为 ibdata1、大小为 12M 的 自拓展 文件，这个文件就是对应的 系统表空间 在文件系统上的表示。

• 独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为 每一个表建立一个独立表空间，也就是说在 database 中创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同 —— 表名.ibd

Tips： MySQL8.0 中不再单独提供 系统表空间（system tablespace）—— 表名.frm ，而是合并在 表名.ibd 文件中。

• 系统表空间与独立表空间的设置 可以指定使用 系统表空间 还是 独立表空间 来存储数据，这个功能由启动参数 innodb_file_per_table 控制

1
2
3

cat my.conf
[server] 
	innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

• 其它类型的表空间 随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。

2、MyISAM存储引擎模式

表结构：在存储表结构方面， MyISAM 和 InnoDB 一样，也是在 数据目录 下对应的数据库子目录下创建了一个专门用于描述表结构的文件 —— 表名.frm

表中数据和索引: 在 MyISAM 中的索引全部都是 二级索引，该存储引擎的 数据和索引是分开存放 的，所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下， 例如 test表的 文件存储方式如下：

• test.frm： 存储表结构（MySQL8.0 改为了 b.xxx.sdi）
• test.MYD： 存储数据 (MYData)
• test.MYI： 存储索引 (MYIndex)

五、MySQL用户与权限管理

5.1 MySQL用户管理

1、创建用户

1

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

示例：

1
2

CREATE USER xiaomu IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'xiaomu'@'localhost' IDENTIFIED BY '123456';

2、查看用户列表

MySQl 用户列表存放在 mysql库下的 user表中，可使用SQL查询：

1

SELECT user, host, password from mysql.user;

3、修改用户

1
2

UPDATE mysql.user SET USER='xiaomu' WHERE USER='xiaomu123'; 
FLUSH PRIVILEGES;

4、删除用户

方式一：使用DROP方式删除（推荐）

1

DROP USER user[,user]…;

示例：

1
2

DROP USER xiaomu ; # 默认删除host为%的用户
DROP USER 'xiaomu'@'localhost';

方式二：使用DELETE方式删除（不推荐，有残留信息）

1
2

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
FLUSH PRIVILEGES;

5、设置当前用户密码

方式一：使用ALTER USER命令来修改当前用户密码

1

ALTER USER USER() IDENTIFIED BY 'new_password';

方式二：使用SET语句来修改当前用户密码

1

SET PASSWORD='new_password';

6、修改其它用户密码

方式一：使用ALTER语句来修改普通用户的密码

1

ALTER USER username [IDENTIFIED BY '新密码'] [,username[IDENTIFIED BY '新密码']]…;

式二：使用SET命令来修改普通用户的密码

1

SET PASSWORD FOR 'username'@'hostname'='new_password';

5.2 MySQL权限管理

1、权限列表

登录服务器后执行以下命令可查询 MySQl 支持的权限列表：

1

show privileges\G

• CREATE 和 DROP权限：可以创建新的数据库和表，或删除（移掉）已有的数据库和表；如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库；
• SELECT、INSERT、UPDATE 和 DELETE权限：允许在一个数据库现有的表上实施操作；
• SELECT权限：只有在它们真正从一个表中检索行时才被用到；
• INDEX权限：允许创建或删除索引，INDEX适用于已有的表；如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义；
• ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表；
• CREATE ROUTINE权限：用来创建保存的程序（函数和程序）；
• ALTER ROUTINE权限：用来更改和删除保存的程序；
• EXECUTE权限：用来执行保存的程序；
• GRANT权限：允许授权给其它用户，可用于数据库、表和保存的程序；
• FILE权限：使用户可以使用 LOAD DATA INFILE 和 SELECT ... INTO OUTFILE 语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）;

2、授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

• 只授予能满足需要的最小权限，防止用户干坏事；比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限；
• 创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段；
• 为每个用户设置满足密码复杂度的密码；
• 定期清理不需要的用户，回收权限或者删除用户；

3、授予权限

1

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

• 该权限如果发现没有该用户，则会直接新建一个用户。

给xiaomu用户用本地命令行方式，授予 databasesname库下的所有表的插删改查的权限：

1

GRANT SELECT,INSERT,DELETE,UPDATE ON databasename.* TO xiaomu@localhost;

授予通过网络方式登录的 xiaomu用户，对所有库所有表的全部权限，密码设为123；注意这里唯独不包括grant的权限：

1

GRANT ALL PRIVILEGES ON *.* TO xiaomu@'%' IDENTIFIED BY '123';

4、查看权限

• 查看当前用户权限：

1

SHOW GRANTS; 

或

1

SHOW GRANTS FOR CURRENT_USER; 

或

1

SHOW GRANTS FOR CURRENT_USER();

• 查看某用户的全局权限

1

SHOW GRANTS FOR 'user'@'主机地址';

5、收回权限

Tips: 注意，在将用户账户从user表删除之前，应该收回相应用户的所有权限；

• 收回权限命令

1

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

• 示例

1
2
3
4

# 收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

Tips: 注意, 须用户重新登录后才能生效

5.3 角色管理

1、创建角色

1

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名称的命名规则和用户名类似，如果host_name省略，默认为%，role_name不可省略，不可为空；

2、给角色赋予权限

1

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述语句中 privileges 代表权限的名称，多个权限以逗号隔开，可使用 SHOW 语句查询权限名称:

1

SHOW PRIVILEGES;

3、查看角色的权限

1

SHOW GRANTS FOR 'role_name';

只要创建了一个角色，系统就会自动给角色一个 USAGE 权限，意思是 连接登录数据库的权限；

4、回收角色的权限

1

REVOKE privileges ON tablename FROM 'rolename';

5、删除角色

1

DROP ROLE role [,role2]...

注意，如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

6、给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用

1

GRANT role [,role2,...] TO user [,user2,...];

查询当前已激活的角色：

1

SELECT CURRENT_ROLE();

7、激活角色

方式一：使用set default role 命令激活角色

1

SET DEFAULT ROLE ALL TO 'xiaorole'@'localhost';

方式二：将activate_all_roles_on_login设置为 ON

1

SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对所有角色永久激活；

8、撤销用户的角色

1

REVOKE role FROM user;

9、设置强制角色(mandatory role)

• 方式一：服务启动前设置

1
2

[mysqld] 
mandatory_roles='role1,role2@localhost,xiaorole@%.example.com'

• 方式二：运行时设置

1
2

SET PERSIST mandatory_roles = 'role1,role2@localhost,xiaorole@%.example.com'; # 系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,xiaorole@%.example.com'; # 系统重启后失效