体系结构

网络连接层

核心服务层

核心服务层是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、SQL 接口、解析器、查询优化器和缓存等部分
系统管理和控制工具：提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等
连接池：主要负责存储和管理客户端与数据库的连接信息，连接池里的一个线程负责管理一个客户端到数据库的连接信息
SQL 接口：主要负责接收客户端发送过来的各种 SQL 命令，并将 SQL 命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端
解析器：主要负责对请求的 SQL 解析成一棵“解析树”，然后根据 MySQL 中的一些规则对“解析树”做进一步的语法验证，确认其是否合法
查询优化器：在 MySQL 中，如果“解析树”通过了解析器的语法检查，此时就会由优化器将其转化（如索引的选择，表的读取顺序等）为执行计划，然后与存储引擎进行交互，通过存储引擎与底层的数据文件进行交互
缓存：MySQL 的缓存是由一系列的小缓存组成的。例如：MySQL 的表缓存，记录缓存，MySQL 中的权限缓存，引擎缓存，Key 缓存等等，如果查询的结果能够命中缓存，则 MySQL 会直接返回缓存中的结果信息，能够提高数据的查询性能

存储引擎层

MySQL 中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互；MySQL 中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异；MySQL 最常用的存储引擎有 InnoDB 和 MyISAM，开发者可以选择合适的存储引擎，也可以定制化开发存储引擎，存储引擎是基于表的，不是数据库

系统文件层

系统文件层主要包括 MySQL 中存储数据的持久化（物理）文件，与上层的存储引擎进行交互，其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行PID 文件和 Socket 文件等

日志文件

主要包括：错误日志、通用查询日志、二进制日志、慢查询日志等

错误日志

主要存储的是 MySQL 运行过程中产生的错误信息。可以使用 show variables like '%log_error%'; 语句来查看 MySQL 中的错误日志

1
log_error=自定义路径

通用查询日志

主要记录 MySQL 运行过程中的一般查询信息，可以使用语句 show variables like '%general%'; 来查看 MySQL 中的通用查询日志文件

1
2
3
general-log=0|1（禁用|开启）
general_log_file=/路径/文件名
log-output=FILE|TABLE|NONE（文件|表|不存放，默认 FILE）

二进制日志

主要记录对 MySQL 数据库执行的插入、修改和删除操作，并且也会记录 SQL 语句执行的时间、执行的时长，但是二进制日志不记录 SELECT、SHOW 等不修改数据库的 SQL；主要用于恢复数据库的数据和实现 MySQL 主从复制

1
2
3
4
5
6
#查看二进制日志是否开启
show variables like '%log_bin%';
#查看二进制日志的参数
show variables like '%binlog%'
#查看日志文件
show binary logs;

慢查询日志

慢查询主要记录的是执行时间超过指定时间的 SQL 语句，这个时间默认是 10 秒

1
2
3
4
#查看是否开启慢查询日志
show variables like '%slow_query%';
#查看慢查询设置的时长
show variables like '%long_query_time%'

数据文件

主要包括了：db.opt 文件、frm 文件、MYD 文件、MYI 文件、ibd 文件、ibdata 文件、ibdata1 文件、ib_logfile0 和 ib_logfile1 文件等

db.opt

主要记录当前数据库使用的字符集和检验规则等信息

frm

存储数据表的结构信息，主要是数据表相关的元数据信息，包括数据表的表结构定义信息，每张表都会有一个 frm 文件
注意：MySQL8 版本中的 innodb 存储引擎的表没有 frm 文件

MYD

MyISAM 存储引擎专用的文件格式，主要存放 MyISAM 存储引擎数据表中的数据，每张 MyISAM 存储引擎表对应一个 MYD 文件

MYI

MyISAM 存储引擎专用的文件格式，主要存放与 MyISAM 存储引擎数据表相关的索引信息，每张 MyISAM 存储引擎表对应一个 MYI 文件

ibd

存放 Innodb 存储引擎的数据文件和索引文件，主要存放的是独享表空间的数据和索引，每张表对应一个 ibd 文件

ibdata

存放 Innodb 存储引擎的数据文件和索引文件，主要存放的是共享表空间的数据和索引，所有表共用一个（或者多个）ibdata 文件，可以根据配置来指定共用的 ibdata 文件个数

ibdata1

MySQL 的系统表空间数据文件，主要存储 MySQL 的数据表元数据、Undo 日志等信息

ib_logfile0 和 ib_logfile1

MySQL 数据库中的 Redo log 文件，主要用于 MySQL 实现事务的持久性；如果在某个时间点 MySQL 发生了故障，此时如果有脏页没有写入到数据库的 ibd 文件中，在重启 MySQL 的时候，MySQL 会根据 Redo Log 信息进行重做，将写入 Redo Log 并且尚未写入数据表的数据进行持久化操作

配置文件

用于存储 MySQL 所有的配置信息，在 Unix/Linux 环境中是 my.cnf 文件，在 Windows 环境中是 my.ini 文件

PID

pid 文件是存放 MySQL 进程运行时的进程号的文件，主要存在于 Unix/Linux 环境中，具体的存储目录可以在 my.cnf 文件中进行配置

Socket

socket 文件和 pid 文件一样，都是 MySQL 在 Unix/Linux 环境中运行才会有的文件，客户端可以直接通过 Socket 来连接 MySQL

解压安装

1
2
3
4
5
6
7
#先查找并卸载已经存在的软件包
rpm -qa mysql mysql-server mariadb mariadb-server
rpm -e 包名
xz -d mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.21-linux-glibc2.12-x86_64.tar -C /app/service
mv /app/service/mysql-8.0.21-linux-glibc2.12-x86_64 /app/service/mysql
mkdir -pv /app/logs/mysql && mkdir -pv /app/conf/mysql

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
vi /app/conf/mysql/my.cnf

[mysqld]
user = app
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

basedir = /app/service/mysql
datadir = /app/data/mysql
port = 3306
socket = /tmp/mysql.sock
#设置 utf8mb4 编码
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
#修改加密方式
default_authentication_plugin = mysql_native_password
#不区分大小写
lower_case_table_names = 1
#跳过 DNS 反查询
skip-name-resolve
#允许最大连接数
max_connections = 2048
open_files_limit = 65535
max-connect-errors = 100000
#错误日志
log-error = /app/logs/mysql/mysql-error.log
#慢查询
slow_query_log = 1
long_query_time = 10
#min_examined_row_limit = 1000
slow_query_log_file = /app/logs/mysql/mysql_slow_query.log

[mysql]
default-character-set = utf8mb4
socket = /tmp/mysql.sock

[client]
default-character-set = utf8mb4
socket = /tmp/mysql.sock

[mysqldump]
socket = /tmp/mysql.sock
  
[mysqladmin]
socket = /tmp/mysql.sock

输入以下命令初始化数据库服务

1
2
3
/app/service/mysql/bin/mysqld --defaults-file=/app/conf/mysql/my.cnf --initialize --user=app --basedir=/app/service/mysql --datadir=/app/data/mysql
#初始化后从日志中查询初始化密码，记录下来方便后续使用或者修改密码
grep 'temporary password' /app/logs/mysql/mysql-error.log

相关命令

使用以下命令添加环境变量：

1
2
3
4
echo "export MYSQL_HOME=/app/service/mysql" >> ~/bashrc
echo "export PATH=\$PATH:\$MYSQL_HOME/bin" >> ~/bashrc
#加载环境变量，使其生效
source ~/bashrc

或者创建软连接
1
2
#添加相关命令的软连接（root 权限运行）
ln -s /app/service/mysql/bin/mysql* /usr/bin/

使用自带的启动脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#拷贝命令（root 权限运行）
cp /app/service/mysql/support-files/mysql.server /app/service/mysql_service
#编辑脚本（root 权限运行）
vi /app/service/mysql_service

basedir=/app/service/mysql
datadir=/app/data/mysql
conf=/app/service/mysql/my.cnf

#启动数据库
/app/service/mysql_service start
#重启数据库
/app/service/mysql_service restart
#停止数据库
/app/service/mysql_service stop

其他命令
1
2
3
4
#本地登录
mysql -uroot -p'fjphty:BI96t'
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'kGXSu4fjF' PASSWORD EXPIRE NEVER;

集群部署

设置之前关闭所有数据库

环形主从

A、B、C三个节点分别按上述步骤部署好数据库，配置好时间同步；然后两两设置主从，如：A<B、B<C、C<A，主从设置步骤：

  1. 主从服务器节点设置不同的 server-id
  2. 启用二进制日志和 relaylog
  3. 主节点创建一个拥有复制权限的用户账号
  4. 查询主节点 binlog 信息
  5. 设置从节点同步主节点

修改数据库配置文件，[mysqld] 下添加如下内容（选中部分可以使用 IP 地址最后字段作区分）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#可以使用地址最后作为 id
serverid server-id = 175
#开启 MySQ 二进制日志系统
log-bin = /app/logs/mysql/175-master-bin
#要同步的库，用逗号分隔
replicate-do-db = wb,wb_gateway,wb_etl 
#从其他节点同步的操作不会记录日志，如果要做主库需要开启这个选项
log-slave-updates
#要同步的表，用逗号分隔，% 表示所有
replicate-wild-do-table = wb.%,wb_gateway.%,wb_etl.%
#忽略的库，每个库一行
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
slave-skip-errors = 1032,1062,1053,1146,2003
#relay log
max_relay_log_size = 0
relay_log = /app/logs/mysql/175-relay-bin
relay_log_purge = 1
relay_log_recovery = 1
sync_relay_log = 0
sync_relay_log_info = 0

1
2
3
service mysql srart
mysql -uroot -p''
show master status;

然后每个节点都创建同步账号
1
2
mysql -A -uroot -pkGXSu4fjF -e "CREATE USER 'rep'@'%' IDENTIFIED BY 'WB45678';GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';flush privileges;"
mysql -A -urep -pWB45678 -e "select 'ok';"

然后采用逆序启动，C-B-A，即先设置 B<C 主从，启动 C节点的 MySQL，执行以下命令，查看主库信息：
1
2
mysql -uroot -pkGXSu4fjF
show master status;


再启动 B节点的 MySQL，执行以下命令，指定主库信息并完成 B<C 主从
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql -uroot -pkGXSu4fjF
#指定主库信息
CHANGE MASTER TO
MASTER_HOST='节点 C 的 ip 地址',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='WB45678',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=2855257;
#开始同步
start slave;
#查看 slave 进程状态，Slave_IO_Running、Slave_SQL_Running 两个参数 Yes 表示状态正常，注意等待主库复制的延迟秒数变为 0 Seconds_Behind_Master: 0
show slave status \G

同步完成以后，建立 A<B 主从，先在 B 节点执行如下命令，查看主库信息：

1
2
mysql -uroot -pkGXSu4fjF
show master status;

再启动 A节点的 MySQL，执行以下命令，指定主库信息并完成 A<B 主从

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql -uroot -pkGXSu4fjF
#指定主库信息
CHANGE MASTER TO
MASTER_HOST='节点 B 的 ip 地址',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='WB45678',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=2855257;
#开始同步
start slave;
#查看 slave 进程状态，Slave_IO_Running、Slave_SQL_Running 两个参数 Yes 表示状态正常，注意等待主库复制的延迟秒数变为 0 Seconds_Behind_Master: 0
show slave status \G

同步完成以后，建立 C<A 主从，先在 A 节点执行如下命令，查看主库信息：

1
2
mysql -uroot -pkGXSu4fjF
show master status;

再从 C节点执行以下命令，指定主库信息并完成 C<A 主从

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql -uroot -pkGXSu4fjF
#指定主库信息
CHANGE MASTER TO
MASTER_HOST='节点 C 的 ip 地址',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='WB45678',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=2855257;
#开始同步
start slave;
#查看 slave 进程状态，Slave_IO_Running、Slave_SQL_Running 两个参数 Yes 表示状态正常，注意等待主库复制的延迟秒数变为 0 Seconds_Behind_Master: 0
show slave status \G

同步完成以后，三节点（环形）主从就搭建成功了

数据库初始化

1
2
3
4
#创建数据库
CREATE DATABASE wb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE wb_gateway DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE wb_etl DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

创建数据账户及其授权和密码修改等

1
2
3
4
5
6
7
8
9
10
11
#创建用户
USE mysql
CREATE USER 'wb'@'%' IDENTIFIED BY 'WB@45678';
#修改密码
ALTER USER 'wb'@'%' IDENTIFIED BY 'WB@45678' PASSWORD EXPIRE NEVER;
#赋予权限
GRANT ALL PRIVILEGES ON wb.* TO 'wb'@'%';
#取消权限
REVOKE ALL PRIVILEGES ON  *.* FROM 'wb'@'%';
#刷新生效
FLUSH PRIVILEGES;

相关设置

密码强度

1
2
3
4
查看密码的规则：SHOW VARIABLES LIKE 'validate_password%';
PS：validate_password_length=8，表示长度最少为8位；validate_password_policy=MEDIUM，表示密码的安全等级为中。
临时将密码安全等级设置为 low：set global validate_password_policy=0;，长度不变。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Cy#8J&s4';

建数据库

1
2
3
4
5
6
create database demo;
查看默认的数据库：show databases;
进入到mysql库：use mysql;
查看数据库的用户信息：select host,user from user;
创建用户并允许在任意主机上连接管理 demo 数据库：
GRANT ALL PRIVILEGES ON demo.* TO 'test'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION;

创建用户

1
2
CREATE USER 'test'@'%' IDENTIFIED BY '12345678'; 
GRANT SELECT ON demo.* TO 'test'@'%' IDENTIFIED BY '87654321';#这里只给了查询权限

授权语句分析：GRANT 权限 ON 数据库.数据表 TO '用户'@'主机' IDENTIFIED BY '密码';

权限 ALL PRIVILEGES、select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file
数据库 指定数据库，没有特别规定用 * 表示全部
数据表 指定数据表，没有特别规定用 * 表示全部
用户 创建的用户
主机 指定主机 IP，没有特别规定用 % 表示全部
密码 可以跟创建命令不一样

忘记密码

vim /etc/my.cnf 在 [mysqld] 底下添加语句：skip-grant-tables，然后重启 mysqld 服务；

1
2
3
4
5
6
systemctl restart mysqld 
mysql -u root -p #直接回车
mysql> use mysql;
mysql> update user set authentication_string=password("123456") where user="root";
mysql> flush privileges;
mysql> exit

注释或者删除：skip-grant-tables，重启 mysqld 服务。

监控指标

相应指标如下：
| 名称 | 说明 | 类别 |
| —- | —- | —- |
| QPS | 数据库每秒处理的请求数量 | 性能类 |
| TPS | 数据库每秒处理的事务数量 | 性能类 |
| 并发数 | 数据库实例当前并行处理的会话数量 | 性能类 |
| 连接数 | 连接到数据库会话的数量 | 性能类 |
| 缓存命中率 | 查询命中缓存的比例 | 性能类 |
| 可用性 | 数据库是否可以正常对外服务 | 高可用 |
| 阻塞 | 当前阻塞的会话数 | 高可用 |
| 慢查询 | 慢查询情况 | 高可用 |
| 主从延迟 | 主从延迟时间 | 高可用 |
| 主从状态 | 主从链路是否正常 | 高可用 |
| 死锁 | 查看死锁信息 | 高可用 |

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
QPS：show global status where variable_name in ('Queries', 'uptime');
TPS：show global status where variable_name in ('com_insert' , 'com_delete' , 'com_update', 'uptime');
并发数：show global status like 'Threads_running';
连接数 - 生产中配置报警阈值：Threads_connected /  max_connections > 0.8
当前连接数：show global status like 'Threads_connected';
最大连接数：show global status like 'max_connections';
innodb 缓冲池查询总数：show global status like 'innodb_buffer_pool_read_requests'; 
innodb 从磁盘查询数：show global status like 'innodb_buffer_pool_reads';
缓存命中率 - 生产中配置报警阈值：(innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests > 0.95 

慢查询
my.cnf 文件添加一下配置：
long_query_time=1
slow_query_log=ON
slow_query_log_file=/var/log/mysqld-slow.log
实时生效使用命令 set global + 上述配置项
select * from information_schema.'processlist';

主从延迟/状态：show slave status;
死锁：show engine innodb status;

https://xie.infoq.cn/article/5e1aec4933a497f773d5406e6

工具命令

ngram

全文解析器

explain

查看执行计划，查询 SQL 走了哪些索引

show profile

查看 SQL 对系统资源的损耗情况

xbstream

1
2
3
4
5
6
7
8
9
mkdir /data
cat /root/28.xb | xbstream -x -C /data

xtrabackup --decompress --remove-original --target-dir=/data
xtrabackup --prepare  --use-memory=1GB --target-dir=/data

chown -R mysql:mysql /data
xtrabackup --defaults-file=/etc/my.cnf --move-back --target-dir=/data
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data
1
2
yum install https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
yum install https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm