MySQL 相关设置 & 性能调优
# 相关设置
# 密码强度
查看密码的规则：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';
2
3
4
# 建数据库
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;
2
3
4
5
6
# 创建用户
CREATE USER 'test'@'%' IDENTIFIED BY '12345678';
GRANT SELECT ON demo.* TO 'test'@'%' IDENTIFIED BY '87654321';#这里只给了查询权限
2
授权语句分析：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 服务；
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
2
3
4
5
6
注释或者删除：skip-grant-tables，重启 mysqld 服务。
# 监控指标
相应指标如下：
| 名称 | 说明 | 类别 |
|---|---|---|
| QPS | 数据库每秒处理的请求数量 | 性能类 |
| TPS | 数据库每秒处理的事务数量 | 性能类 |
| 并发数 | 数据库实例当前并行处理的会话数量 | 性能类 |
| 连接数 | 连接到数据库会话的数量 | 性能类 |
| 缓存命中率 | 查询命中缓存的比例 | 性能类 |
| 可用性 | 数据库是否可以正常对外服务 | 高可用 |
| 阻塞 | 当前阻塞的会话数 | 高可用 |
| 慢查询 | 慢查询情况 | 高可用 |
| 主从延迟 | 主从延迟时间 | 高可用 |
| 主从状态 | 主从链路是否正常 | 高可用 |
| 死锁 | 查看死锁信息 | 高可用 |
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;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
https://xie.infoq.cn/article/5e1aec4933a497f773d5406e6 (opens new window) 
# 体系结构
# 网络连接层
# 核心服务层
核心服务层是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、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 中的错误日志
log_error=自定义路径
# 通用查询日志
主要记录 MySQL 运行过程中的一般查询信息，可以使用语句show variables like '%general%';来查看 MySQL 中的通用查询日志文件
general-log=0|1（禁用|开启）
general_log_file=/路径/文件名
log-output=FILE|TABLE|NONE（文件|表|不存放，默认 FILE）
2
3
# 二进制日志
主要记录对 MySQL 数据库执行的插入、修改和删除操作，并且也会记录 SQL 语句执行的时间、执行的时长，但是二进制日志不记录 SELECT、SHOW 等不修改数据库的 SQL；主要用于恢复数据库的数据和实现MySQL 主从复制
#查看二进制日志是否开启
show variables like '%log_bin%';
#查看二进制日志的参数
show variables like '%binlog%'
#查看日志文件
show binary logs;
2
3
4
5
6
# 慢查询日志
慢查询主要记录的是执行时间超过指定时间的 SQL 语句，这个时间默认是 10 秒
#查看是否开启慢查询日志
show variables like '%slow_query%';
#查看慢查询设置的时长
show variables like '%long_query_time%'
2
3
4
# 数据文件
主要包括了：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
# 工具命令
# ngram
全文解析器
# explain
查看执行计划，查询 SQL 走了哪些索引
# show profile
查看 SQL 对系统资源的损耗情况
# xbstream
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
2
3
4
5
6
7
8
9
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
2