MySQL 入门
体系结构
网络连接层
核心服务层
核心服务层是整个数据库服务器的核心,主要包括了系统管理和控制工具、连接池、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
3general-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 | #先查找并卸载已经存在的软件包 |
配置文件
1 | vi /app/conf/mysql/my.cnf |
输入以下命令初始化数据库服务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
4echo "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,主从设置步骤:
- 主从服务器节点设置不同的 server-id
- 启用二进制日志和 relaylog
- 主节点创建一个拥有复制权限的用户账号
- 查询主节点 binlog 信息
- 设置从节点同步主节点
修改数据库配置文件,[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 = 01
2
3service mysql srart
mysql -uroot -p''
show master status;
然后每个节点都创建同步账号1
2mysql -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
2mysql -uroot -pkGXSu4fjF
show master status;
再启动 B节点的 MySQL,执行以下命令,指定主库信息并完成 B<C 主从1
2
3
4
5
6
7
8
9
10
11
12
13mysql -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 | mysql -uroot -pkGXSu4fjF |
再启动 A节点的 MySQL,执行以下命令,指定主库信息并完成 A<B 主从
1 | mysql -uroot -pkGXSu4fjF |
同步完成以后,建立 C<A 主从,先在 A 节点执行如下命令,查看主库信息:
1 | mysql -uroot -pkGXSu4fjF |
再从 C节点执行以下命令,指定主库信息并完成 C<A 主从
1 | mysql -uroot -pkGXSu4fjF |
同步完成以后,三节点(环形)主从就搭建成功了
数据库初始化
1 | #创建数据库 |
创建数据账户及其授权和密码修改等
1 | #创建用户 |
相关设置
密码强度
1 | 查看密码的规则:SHOW VARIABLES LIKE 'validate_password%'; |
建数据库
1 | create database demo; |
创建用户
1 | CREATE USER 'test'@'%' IDENTIFIED BY '12345678'; |
授权语句分析: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
6systemctl 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 | QPS:show global status where variable_name in ('Queries', 'uptime'); |
https://xie.infoq.cn/article/5e1aec4933a497f773d5406e6
工具命令
ngram
全文解析器
explain
查看执行计划,查询 SQL 走了哪些索引
show profile
查看 SQL 对系统资源的损耗情况
xbstream
1 | mkdir /data |
1 | 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 |