【高级篇】MySQL

• MySQL架构篇：涉及Linux平台安装及一些基本问题，基础不牢固者需要学习
• 索引及调优篇：面试和开发的重点，也是重灾区，需要全面细致的学习和掌握
• 事务篇：面试和开发的重点，需要全面细致的学习和掌握
• 日志与备份篇：根据实际开发需要，进行相应内容的学习

---

【MySQL架构篇】

1. Linux下MySQL的安装与使用

1.1 安装前说明

①.Linux系统及工具的准备

• 安装并启动好两台虚拟机：CentOS7，用于使用MySQL5和8
• 安装有Xshell和Xftp等访问CentOS系统的工具
• CentOS6和7在MySQL的使用中的区别
  • 防火墙：6是iptables，7是firewalld
  • 启动服务的命令：6是service，7是systemctl

②.查看是否安装过MySQL

如果是用rpm安装, 检查一下RPM PACKAGE：
rpm -qa | grep -i mysql  # -i 忽略大小写
检查mysql service：
systemctl status mysqld.service
如果存在mysql-libs的旧版本包，显示如下：
Last login: Wed Mar  1 15:18:47 2023
[root@redis100 ~]# rpm -qa | grep -i mysql

如果不存在mysql-lib的版本，显示如下：
Last login: Wed Mar  1 15:18:47 2023
[root@redis100 ~]# rpm -qa | grep -i mysql
[root@redis100 ~]# 

②.MySQL的卸载

1. 关闭mysql服务
systemctl stop mysqld.service
2. 查看当前mysql安装状况
rpm -qa | grep -i mysql
#或
yum list installed | grep mysql
3. 卸载上述命令查询出的已安装程序
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
务必卸载干净，反复执行 rpm -qa | grep -i mysql 确认是否有卸载残留
4. 删除mysql相关文件
查找相关文件
find / -name mysql
删除上述命令查找出的相关文件
rm -rf xxx
5. 删除my.cnf
rm -rf /etc/my.cnf

1.2 MySQL的linux版安装

①.MySQL的下载

 ②.MySQL的安装

1.CentOS下检查MySQL依赖
-检查/tmp临时目录权限（必不可少）
由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件
chmod -R 777 /tmp
-安装前，检查依赖
[root@redis100 ~]# rpm -qa | grep libaio
libaio-0.3.109-13.el7.x86_64
[root@redis100 ~]# rpm -qa | grep net-tools
net-tools-2.0-0.25.20131004git.el7.x86_64
[root@redis100 ~]# rpm -qa | grep net-tools
2.将安装程序拷贝到/opt目录下，在mysql的安装文件目录下，按照顺序执行
-i, --install 安装软件包
-v, --verbose 提供更多的详细信息输出
-h, --hash 软件包安装的时候列出哈希标记(和-v一起使用效果更好)，展示进度条
[root@redis100 mysql-8.0.26]# rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
[root@redis100 mysql-8.0.26]# rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm 
[root@redis100 mysql-8.0.26]# rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
如果出错，执行该命令：[root@redis100 mysql-8.0.26]# yum remove mysql-libs
[root@redis100 mysql-8.0.26]# rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm 
[root@redis100 mysql-8.0.26]# rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm 

③.查看MySQL的版本

mysqladmin-version或mysql-version或rpm -qa | grep -i mysql
[root@redis100 ~]# mysql --version
mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
[root@redis100 ~]# rpm -qa | grep -i mysql
mysql-community-libs-8.0.26-1.el7.x86_64
mysql-community-common-8.0.26-1.el7.x86_64
mysql-community-server-8.0.26-1.el7.x86_64
mysql-community-client-plugins-8.0.26-1.el7.x86_64
mysql-community-client-8.0.26-1.el7.x86_64

④.服务的初始化

为了保证数据库目录与文件的所有者为mysql登录用户
如果你是以root身份运行mysql服务，需要执行下面的命令初始化：
mysqld --initialize --user=mysql
说明：--initialize 选项默认以“安全”模式来初始化，则会为root用户生成一个密码并将该密码标记为过期
登录后你需要设置一个新的密码。生成的临时密码会往日志中记录一份。
查看密码：cat /var/log/mysqld.log
[root@redis100 ~]# cat /var/log/mysqld.log
2023-03-01T08:38:27.414516Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 6016
2023-03-01T08:38:27.455763Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-01T08:38:28.734034Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-03-01T08:38:30.808138Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2023-03-01T08:38:30.809367Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2023-03-01T08:38:31.019602Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Qjovixwp.9Xi
启动服务：[root@redis100 ~]# systemctl start mysqld

⑤.查看MySQL服务是否是自启动

systemctl list-unit-files|grep mysqld.service
[root@redis100 ~]# systemctl list-unit-files | grep mysqld.service
mysqld.service                                enabled 
默认是enabled。
如不是enabled可以运行如下命令设置自启动：
systemctl enable mysqld.service
如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service

1.3 MySQL登录和远程连接

1.首次登录：通过mysql -hlocalhost -P3306 -uroot -p进行登录
在Enter password：录入初始化密码
因为初始化密码默认是过期的，所以查看数据库会报错
2.修改密码：
ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc123';
3.设置远程登录：在windows下的sqlyog远程连接虚拟机数据库
在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到报错信息，这是由于Mysql配置了不支持远程连接引起的。
--在远程机器上使用ping ip地址 保证网络畅通，ping 192.168.56.100
--在远程机器上使用telnet命令保证端口号开放访问，telnet 192.168.56.100 3306
--关闭防火墙或开放端口号，二选一即可
方式1：关闭防火墙
CentOS7：
systemctl start firewalld.service
systemctl status firewalld.service
systemctl stop firewalld.service
#设置开机启用防火墙
systemctl enable firewalld.service
#设置开机禁用防火墙
systemctl disable firewalld.service
方式二：开放端口号
查看开放的端口号
firewall-cmd --list-all
设置开放的端口号
firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent
重启防火墙
firewall-cmd --reload
--Linux下修改配置：
在Linux系统MySQL下测试
use mysql;
select Host,User from user;
可以看到root用户的当前主机配置信息为localhost，不允许做远程连接
修改Host为通配符%，就可以进行远程连接了
update user set host = '%' where user = 'root';
或update user set host = '192.168.1.%' where user = 'root';
Host修改完成后记得执行flush privileges使配置立即生效
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

1.4 字符集的相关操作

• 在MySQL8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

查看默认使用的字符集
show variables like 'character%';
或
show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (2.14 sec)
character_set_server：服务器级别的字符集
character_set_database：当前数据库的字符集
character_set_client：服务器解码请求时使用的字符集
character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results：服务器向客户端返回数据时使用的字符集

2. MySQL的数据目录

2.1 MySQL8的主要目录结构

1. 数据库文件的存放路径
MySQL数据库文件的存放路径：/var/lib/mysql
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.04 sec)
从结果中可以看出，在我的计算机上MySQL的数据目录就是:/var/lib/mysql/
2. 相关命令目录:/usr/bin和/usr/sbin
mysqladmin、mysqlbinlog、mysqldump等命令
[root@redis100 ~]# cd /usr/bin
[root@redis100 bin]# find . -name "mysqladmin";
./mysqladmin
[root@redis100 bin]# find . -name "mysqldump";
./mysqldump
3. 配置文件目录:/usr/share/mysql-8.0(命令及配置文件)，/etc/mysql(如my.cnf)
[root@redis100 bin]# cd /usr/share/mysql-8.0
[root@redis100 mysql-8.0]# ll
总用量 992
drwxr-xr-x. 2 root root     24 3月   1 16:18 bulgarian
drwxr-xr-x. 2 root root   4096 3月   1 16:18 charsets
drwxr-xr-x. 2 root root     24 3月   1 16:18 czech
drwxr-xr-x. 2 root root     24 3月   1 16:18 danish
-rw-r--r--. 1 root root  25575 7月   1 2021 dictionary.txt
drwxr-xr-x. 2 root root     24 3月   1 16:18 dutch
drwxr-xr-x. 2 root root     24 3月   1 16:18 english
drwxr-xr-x. 2 root root     24 3月   1 16:18 estonian
drwxr-xr-x. 2 root root     24 3月   1 16:18 french
drwxr-xr-x. 2 root root     24 3月   1 16:18 german
drwxr-xr-x. 2 root root     24 3月   1 16:18 greek
drwxr-xr-x. 2 root root     24 3月   1 16:18 hungarian
-rw-r--r--. 1 root root   3999 7月   1 2021 innodb_memcached_config.sql
-rw-r--r--. 1 root root   2216 7月   1 2021 install_rewriter.sql
drwxr-xr-x. 2 root root     24 3月   1 16:18 italian
drwxr-xr-x. 2 root root     24 3月   1 16:18 japanese
drwxr-xr-x. 2 root root     24 3月   1 16:18 korean
-rw-r--r--. 1 root root 610894 7月   1 2021 messages_to_clients.txt
-rw-r--r--. 1 root root 348904 7月   1 2021 messages_to_error_log.txt
-rw-r--r--. 1 root root   1977 7月   1 2021 mysql-log-rotate
drwxr-xr-x. 2 root root     24 3月   1 16:18 norwegian
drwxr-xr-x. 2 root root     24 3月   1 16:18 norwegian-ny
drwxr-xr-x. 2 root root     24 3月   1 16:18 polish
drwxr-xr-x. 2 root root     24 3月   1 16:18 portuguese
drwxr-xr-x. 2 root root     24 3月   1 16:18 romanian
drwxr-xr-x. 2 root root     24 3月   1 16:18 russian
drwxr-xr-x. 2 root root     24 3月   1 16:18 serbian
drwxr-xr-x. 2 root root     24 3月   1 16:18 slovak
drwxr-xr-x. 2 root root     24 3月   1 16:18 spanish
drwxr-xr-x. 2 root root     24 3月   1 16:18 swedish
drwxr-xr-x. 2 root root     24 3月   1 16:18 ukrainian
-rw-r--r--. 1 root root   1248 7月   1 2021 uninstall_rewriter.sql
[root@redis100 mysql-8.0]# 

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

像InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上的，操作系统用来管理磁盘的结构被称为文件系统，所以用专业一点的话表述就是：像InnoDB、MyISAM这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写会文件系统。

1. 查看默认数据库
show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.05 sec)
有4个数据库是属于MySQL自带的系统数据库。
-mysql
    MySQL系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，
    一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，
    一些帮助信息以及时区信息等。
-information_schema
    MySQL系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息 ，
    比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。
    这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据 。
    在系统数据库information_schema中提供了一些以innodb_sys开头的表，用于表示内部系统表。
-performance_schema
    MySQL系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，
    可以用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，
    在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
-sys
    MySQL系统自带的数据库，这个数据库主要是通过视图的形式把information_schema
    和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能。

2.3 数据库在文件系统中的表示

• 使用CREATE DATABASE 数据库名语句创建一个数据库的时候，在文件系统上实际发生了什么呢？其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，每当新建一个数据库时，MySQL会帮助我们做两件事：
  • 在数据目录下创建一个和数据库名同名的目录
  • 在该数据库同名的子目录下创建一个名为db.opt的文件（仅限于MySQL5.7及之前版本），这个文件中包含了该数据库的各种性能，比如该数据库的字符集合比较规则
  • 查看数据目录的指令：cd /var/lib/mysql；ll

2.4 表在文件系统中的表示

2.4.1 InnoDB存储引擎模式

• xxx.frm：存储表结构，比如字段、类型、约束
• xxx.ibd：实际存放的数据的地方，也可以放在ibdata1中

1. 表结构
为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是：表名.frm
比如我们在atguigu数据库下创建一个名为test的表
mysql> use atguigu;
mysql> CREATE TABLE test(c1 int);
那在数据库atguigu对应的子目录下就会创建一个名为test.frm的用于描述表结构的文件。
.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以二进制格式存储的，我们直接打开是乱码的。
[root@redis100 ~]# cd /var/lib/mysql
[root@redis100 mysql]# cd ./atguigu
[root@redis100 atguigu]# ll
总用量 112
-rw-r-----. 1 mysql mysql 114688 3月   2 13:47 test.ibd
[root@redis100 atguigu]# 
2. 表中数据和索引
①.系统表空间(system tablespace)
    默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，
    这个文件就是对应的系统表空间在文件系统上的表示。怎么才12M？
    注意这个文件是自扩展文件，当不够用的时候它会自己增加文件大小。
    当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，
    那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下my.cnf配置文件
    [server]
    innodb_data_file_path=data1:512M;data2:512M:autoextend
②.独立表空间(file-per-table tablespace)
    在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，
    而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。
    使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，
    文件名和表名相同，只不过添加了一个.ibd的扩展名而已，所以完整的文件名称长这样：表名.ibd
    比如：我们使用了独立表空间去存储atguigu数据库下的test表的话，
    那么在该表所在数据库对应的atguigu目录下会为test表创建这两个文件：test.frm、test.ibd
    其中test.ibd文件就用来存储test表中的数据和索引
MySQL8提供的解析xxx.ibd文件的命令：
    [root@redis100 atguigu]# ibd2sdi --dump-file=test.txt test.ibd
    [root@redis100 atguigu]# ll
    总用量 124
    -rw-r-----. 1 mysql mysql 114688 3月   2 13:55 test.ibd
    -rw-------. 1 root  root    9392 3月   3 15:40 test.txt
    [root@redis100 atguigu]# vim ./test.txt
③.系统表空间与独立表空间的设置
    我们可以自己指定使用系统表空间还是独立表空间来存储数据，
    这个功能由启动参数innodb_file_per_table控制
    比如说我们想刻意将表数据都存储到系统表空间时，可以在启动MySQL服务器的时候这样配置：
    [server]
    innodb_file_per_table=0 #0：代表使用系统表空间；1：代表使用独立表空间
    默认情况：
    mysql> show variables like 'innodb_file_per_table';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_file_per_table |  ON   |
    +-----------------------+-------+
④.其他类型的表空间
    随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，
    比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。    

2.4.2 MyISAM存储引擎模式

1. 表结构
在存储表结构方面，MyISAM和InnoDB一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件：表名.frm
2. 表中数据和索引
在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。
所以在文件系统中也是使用不同的文件来存储索引文件和数据文件，同时表数据都存放在对应的数据库子目录下。
假如test表使用MyISAM存储引擎的话，那么在它所在数据库对应的atguigu目录下会为test表创建这三个文件
test.frm 存储表结构
test.MYD 存储数据(MYData)
test.MYI 存储索引(MYIndex)
举例：创建一个MyISAM表，使用ENGINE选项显示指定引擎。因为InnoDB是默认引擎
CREATE TABLE `student_myisam` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(64) DEFAULT NULL,
    `age` int DEFAULT NULL,
    `sex` varchar(2) DEFAULT NULL,
    PRIMARY KEY (`id`)
)ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;

2.5 小结

• 举例：数据库a，表b
• ①.如果表b采用InnoDB，data\a中会产生1个或者2个文件：
  • b.frm：描述表结构文件，字段长度、类型、约束等
  • 如果采用系统表空间模式的，数据信息和索引信息都存储在ibdata1中
  • 如果采用独立表空间存储模式，data\a中还会产生b.ibd文件
  • 此外：
    • MySQL5.7中会在data/a的目录下生成db.opt文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件。
    • MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中。
• ②.如果表b采用MyISAM，data\a中会产生3个文件：
  • MySQL5.7中：b.frm，描述表结构文件，字段长度等
  • MySQL8.0中：b_xxx.sdi，描述表结构文件，字段长度等
  • b.MYD(MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
  • b.MYI(MYIndex)：存放索引信息文件

3. 用户与权限管理

3.1 用户管理

• MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有的权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。

3.1.1 登录MySQL服务器

1. 启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
下面详细介绍命令中的参数：
-h参数：后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
-P参数：后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，
不使用该参数时自动连接到3306端口，port为连接的端口号。
-u参数：后面接用户名，username为用户名。
-p参数：会提示输入密码。
DatabaseName参数：指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库
中，然后可以使用USE命令来选择数据库。
-e参数：后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
举例：mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
2. 数据库自带的4个用户：
mysql> use mysql;
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.01 sec)

3.1.2 创建用户

• 不推荐使用Insert语句直接操作MySQL中的user表来增加用户。官方推荐使用CREATE USER语句创建新用户，此时必须拥有CREATE USER权限。每添加一个用户，CTEATE USER语句会在MySQL.user表中添加一条记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句就会返回一个错误

####################CREATE USER语句的基本语法形式如下：
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
--用户名参数表示新建用户的账户，由用户（User）和主机名（Host）构成；
--“[]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，
这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。
如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
--CREATE USER语句可以同时创建多个用户
举例：
CREATE USER wnanb IDENTIFIED BY 'abc123'; #默认host是%,建议写成'wnanb'@'%'
mysql> CREATE USER wnanb IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.10 sec)
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | wnanb            |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)
CREATE USER 'wnanb'@'localhost' IDENTIFIED BY 'abc123';
mysql> CREATE USER 'wnanb'@'localhost' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | wnanb            |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | wnanb            |
+-----------+------------------+
6 rows in set (0.00 sec)
###############################查看新用户的权限：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.02 sec)
mysql> show grants;
+-------------------------------------------+
| Grants for wnanb@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `wnanb`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

3.1.3 修改用户

修改用户名
UPDATE mysql.user SET USER = 'li4' WHERE USER = 'wang5';
FLUSH PRIVILEGES;

3.1.4 删除用户

方式1：使用DROP方式删除（推荐）
使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：
DROP USER user[,user]…;
举例：
DROP USER 'li4';  # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';
方式2：使用DELETE方式删除
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
FLUSH PRIVILEGES;
举例：
DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';
FLUSH PRIVILEGES;
注意：不推荐通过DELETE FROM USER WHERE USER='li4' 进行删除，系统会有残留信息保留。
而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

3.1.5 设置当前用户密码

1.使用ALTER USER命令来修改当前用户密码，用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下：
ALTER USER USER() IDENTIFIED BY 'new_password';
2.使用SET语句来修改当前用户密码，使用root用户登录MySQL后，可以使用SET语句来修改密码，具体SQL语句如下：
SET PASSWORD='new_password';
该语句会自动将密码加密后再赋给当前用户。

3.1.6 修改其他用户密码

1.使用ALTER语句来修改普通用户的密码，可以使用ALTER USER语句来修改普通用户的密码。基本语法形式如下：
ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc123';
2.使用SET命令来修改普通用户的密码，使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。SET语句的代码如下：
SET PASSWORD FOR 'username'@'hostname'='new_password';
3.使用UPDATE语句修改普通用户的密码（不推荐）
UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";

3.2 权限管理

3.2.1 权限列表：show privileges

• 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服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

3.2.2 授予权限的原则

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

3.2.3 授予权限

给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权和直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。

授权命令：
GRANT 权限1,权限2,...,权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY '密码口令'];
该权限如果发现没有该用户，则会直接新建一个用户
比如：
--给li4用户用本地命令行方式，授予atguigu这个库下的所有表的插删改查的权限
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigu.* TO li4@localhost;
--授予通过网络方式登录的joe用户，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组。
所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；
所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是
删除。

3.2.4 查看权限

查看当前用户权限：
-show grants;
-show grants for current_user;
-show grants for current_user();
查看某用户的全局权限：
-show grants for 'user'@'主机地址';

3.2.5 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍在user表中保存（删除user表中的账户记录使用DROP USER语句）

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限
1. 收回权限命令
REVOKE 权限1,权限2,...,权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
举例：
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

3.3 权限表

3.3.1 user表，是MySQL中最重要的一个权限表，记录用户账号和权限信息，有49个字段，如下图

这些字段可以分为4类，分别是范围列（或用户列）、权限列、安全列和资源控制列

①.返回列（用户列）

• host：表示连接类型
  • %，表示所有远程通过TCP方式的连接
  • IP，地址如 (192.168.1.2、127.0.0.1) 通过制定ip地址进行的TCP方式的连接
  • 机器名，通过制定网络中的机器名进行的TCP方式的连接
  • ::1，IPv6的本地ip地址，等同于IPv4的127.0.0.1
  • localhost，本地方式通过命令行方式的连接，比如mysql -u xxx -p xxx 方式的连接。
• user，表示用户名，同一用户通过不同方式链接的权限是不一样的。
• password，密码
  • 所有密码串通过password(明文字符串) 生成的密文字符串。MySQL 8.0在用户管理方面增加了角色管理，默认的密码加密方式也做了调整，由之前的SHA1改为SHA2，不可逆 。同时，加上 MySQL 5.7的禁用用户和用户过期的功能，MySQL在用户管理方面的功能和安全性都较之前版本大大的增强了。
  • mysql5.7及之后版本的密码保存到authentication_string字段中不再使用password字段。

②.权限列

• Grant_priv字段：表示是否拥有GRANT权限
• Shutdown_priv字段：表示是否拥有停止MySQL服务的权限
• Super_priv字段：表示是否拥有超级权限
• Execute_priv字段：表示是否拥有EXECUTE权限。拥有EXECUTE权限，可以执行存储过程和函数。
• Select_priv，Insert_priv等：为该用户所拥有的权限

③.安全列

• 安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于加密；
• 两个是x509相关的（x509_issuer、x509_subject），用于标识用户；
• 另外两个Plugin字段用于验证用户身份的插件，该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。

④.资源控制列， 资源控制列的字段用来限制用户使用的资源 ，包含4个字段，分别为：

• max_questions，用户每小时允许执行的查询操作次数；
• max_updates，用户每小时允许执行的更新操作次数；
• max_connections，用户每小时允许执行的连接操作次数；
• max_user_connections，用户允许同时建立的连接次数。

查看字段：
DESC mysql.user
查看用户,以列的方式显示数据：
select * from mysql.user \G;
查询特定字段：
SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv
FROM mysql.user;

3.3.2 db表

使用DESCRIBE查看db表的基本结构：
DESCRIBE mysql.db;
1.用户列
db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。
表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。
2.权限列
Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限。

3.3.3 tables_priv表和columns_priv表

tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限
desc mysql.tables_priv;
tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv
--Host、Db、User和Table_name四个字段分别表示主机名、数据库名、用户名和表名。
--Grantor表示修改该记录的用户。
--Timestamp表示修改该记录的时间。
--Table_priv表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。
--Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。

3.3.4 procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限，表结构如图：desc mysql.procs_priv

3.4 访问控制(了解)

3.4.1 连接核实阶段

• 当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接收或者拒绝连接。即客户端用户在连接请求中提供用户名、主机地址、用户密码、MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息。
• 服务器只有在user表记录的Host和User字段匹配客户端主机和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

3.4.2 请求核实阶段

• 一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表
• 确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。
• MySQL通过向下层级的顺序（从user表到columns_priv表）检查权限表，但并不是所有的权
  限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。

3.5 角色管理

3.5.1 角色的理解

• 引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

3.5.2 创建角色

创建角色使用CREATE ROLE语句，语法如下：
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']] ...
角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空
练习：我们现在需要创建一个经理的角色，就可以用下面的代码
CREATE ROLE 'manager'@'localhost';

3.5.3 给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权
给角色授权的语法结构是：
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用show语句查询权限名称
show privileges\G;
练习：现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码实现
GRANT SELECT ON demo.settlement TO 'manager';
GRANT SELECT ON demo.goodsmaster TO 'manager';
GRANT SELECT ON demo.invcount TO 'manager';
 

3.5.4 查看角色的权限

赋予角色权限之后，可以通过show grants语句，查看权限是否创建成功：
mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@%                                  |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`                   |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%`    |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%`  |
+-------------------------------------------------------+
只要你创建了一个角色，系统就会自动给你一个“USAGE”权限，意思是连接登录数据库的权限 。
代码的最后三行代表了我们给角色“manager”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权限。

3.5.5 回收角色的权限

• 角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句
• 修改了角色的权限，会影响拥有该角色的账户的权限

撤销角色权限的SQL语法如下：
REVOKE privileges ON tablename FROM 'rolename';
练习：撤销school_write角色的权限
REVOKE INSERT,UPDATE,DELETE ON school.* FROM 'school_write';
show grants for 'school_write';

3.5.6 删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。 
DROP ROLE role[,role1] ...
注意：如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限
练习：执行如下SQL删除角色school_read
DROP ROLE 'school_read';

3.5.7 给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下：
GRANT role[,role1,...] TO user [,user1,...]
上述语句中，role代表角色，user代表用户，可将多个角色同时赋予多个用户，逗号隔开即可
练习：给kangshifu用户添加角色school_read权限
GRANT school_read TO 'kangshifu'@'localhost';
SHOW GRANTS FOR 'kangshifu'@'localhost';
使用kangshifu用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE。SQL语句如下：
SELECT CURRENT_ROLE();

3.5.8 激活角色

方式1：使用set default role 命令激活角色
举例1：
SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';
举例2：使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下：
SET DEFAULT ROLE ALL TO
    'dev1'@'localhost',
    'read_user1'@'localhost',
    'read_user2'@'localhost',
    'rw_user1'@'localhost';
方式2：将activate_all_roles_on_login设置为ON
默认情况：
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login |  OFF  |
+-----------------------------+-------+
1 row in set (0.00 sec)
设置：
SET GLOBAL activate_all_roles_on_login=ON;
这条SQL语句的意思是，对所有角色永久激活。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

3.5.9 撤销用户的角色

撤销用户角色的SQL语法：
REVOKE role FROM user;
练习：撤销kangshifu用户的school_read角色
REVOKE school_read FROM 'kangshifu'@'localhost';
撤销后，执行如下查询语句，查看kangshifu用户的角色信息
show grants for 'kangshifu'@'localhost';

3.5.10 设置强制角色（mandatory role）

方式1：服务启动前设置：
[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'
方式2：运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';  #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com';  #系统重启后失效

4. 逻辑架构

4.1 逻辑架构剖析

4.1.1 服务器处理客户端请求

连接层

系统(客户端)访问MySQL服务前，做的第一件事就是建立TCP连接

经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取

用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行

用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销

服务层

SQL Interface：SQL接口

接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface

MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

Parser：解析器

在解析器中对SQL语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的

在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写

Optimizer：查询优化器

SQL语句在语法解析之后、查询之前会使用查询优化器确定SQL语句的执行路径，生成一个执行计划

4.2 SQL执行流程

4.3 数据库缓冲池

5. 存储引擎

【索引及调优篇】

6. 索引的数据结构

7. InnoDB数据存储结构

8. 索引的创建与设计原则

9. 性能分析工具的使用

10. 索引优化与查询优化

11. 数据库的设计规范

12. 数据库其他调优策略

【事务篇】

13. 事务基础知识

14. MySQL事务日志

15. 锁

16. 多版本并发控制（MVCC）

【日志与备份篇】

17. 其他数据库日志

18. 主从复制

19. 数据库备份与恢复