MySQL高级
1. MySQL架构组成
目录：
1.MySQL架构组成
2.MySQL的备份与恢复
3.MySQL查询和慢查询日志分析
4.MySQL存储引擎
5.MySQL索引优化
6.MySQL锁机制
7.MySQL高可用设计
8.MySQL集群
1.2 MySQL逻辑架构
学习 MySQL 就好比盖房子，如果想把房子盖的特别高，地基一定要稳，基础一定要牢固。学习MySQL 数据库前要先了解它的体系结构，这是学好 MySQL 数据库的前提。
1.2.1 MySQL架构体系介绍
MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，
可以分为四层：
- 连接层：最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。
- 服务层：中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。
- 引擎层：存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同 的功能,我们可以根据实际需求选择使用对应的存储引擎
- 存储层（文件系统层）：数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互
官方文档中 MySQL 的基础架构图
1.2.2 SQL查询流程
我们用一条 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程，如下图所示
- 通过客户端/服务器通信协议与 MySQL 建立连接
- 查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
- 预处理器生成新的解析树。
- 查询优化器生成执行计划。
- 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 QueryCache 中，以后若有相同的 SQL 语句执行则直接返回结果。
1.3 MySQL物理文件
物理文件包括：日志文件，数据文件，配置文件
1.3.1 日志文件
日志文件包括
- error log 错误日志 排错 /var/log/mysqld.log【默认开启】
- bin log 二进制日志 备份 增量备份 DDL DML DCL
- Relay log 中继日志 复制 接收 replication master
- slow log 慢查询日志 调优 查询时间超过指定值
-- 查看错误日志文件路径
show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+
-- 慢查询日志文件路径
show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
-- bin log 日志文件 需要在 my.cnf 中配置：
log-bin=/var/log/mysql-bin/bin.log
server-id=2
-- 查看 relay log 相关参数
show variables like '%relay%';
1.3.2 配置文件
配置文件 my.cnf
在 my.cnf 文件中可以进行一些参数设置, 对数据库进行调优。
[client] #客户端设置，即客户端默认的连接参数
port = 3307 #默认连接端口
socket = /data/mysqldata/3307/mysql.sock #用于本地连接的socket套接字
default-character-set = utf8mb4 #编码
[mysqld] #服务端基本设置
port = 3307 MySQL监听端口
socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录
basedir = /usr/local/mysql-5.7.11 #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp #MySQL存放临时文件的目录
character_set_server = utf8mb4 #服务端默认编码（数据库级别）
1.3.3 数据文件
-- 查看数据文件的位置
show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------+
-
.frm文件：不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在此文件中，包括表结构的定义信息等。
-
.MYD文件：myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的目录下
-
.MYI文件：也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样
-
.ibd文件：存放innoDB的数据文件（包括索引）。
-
db.opt文件：此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规则
2.MySQL的备份与恢复
2.1 为什么要进行数据备份
我们试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢 对于一些网站、系统来说，数据库就是一切，所以做好数据库的备份是至关重要的！
2.1.1 数据库备份的应用场景
数据丢失应用场景：
- 系统硬件或软件故障
- 自然灾害,比如水灾 火灾 地震等
- 黑客攻击,非法访问者故意破坏
- 误操作 , 人为的误操作占比最大
非数据丢失应用场景：
- 开发测试环境数据库搭建
- 数据库或者数据迁移
2.2 数据备份的类型
2.2.1 按照业务方式分
- 完全备份：将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份文件的存储位置以及数据库中的全部对象和相关信息。
- 差异备份：备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件以及数据库中其他被修改的内容。
- 增量备份：增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增 加或者被修改的文件。
| 完全备份 | 差异备份 | 增量备份 | |
|---|---|---|---|
| 备份方法 | 备份所有文件 | 一次全备份后,备份与全备份差异的部分 | 一次全备份后,备份与上次备份的差异部分 |
| 备份速度 | 最慢 | 较快 | 最快 |
| 恢复速度 | 最快 | 较快 | 最慢 |
| 恢复速度 | 最多 | 较多 | 最少 |
| 优 势 | 最快的恢复速度, 只需要上 一次完全备份就能恢复 | 相比增量,更快也更简单。并且只需要最近一次的完全备份和最后一次的差异备份就能恢复 | 备份速度快,较少的空间需求,没有重复的备份文件 |
| 劣势 | 最多的空间需求，大量重复的备份 | 较慢的备份速度,仍然会 存在许多的备份文件 | 最慢的恢复速度，恢复需要最近一次完全备份 和全部增量备份 |
2.2.2 备份的组合方式
- 完全备份与差异备份
以每周数据备份为例，可以在星期一进行完全备份，在星期二至星期六进行差异备份。
如果在星期 六数据被破坏了，则只需要还原星期一完全的备份和星期五的差异备份。
这种策略备份数据需要较多的时间，但还原数据使用较少的时间。
- 完全备份与增量备份
以每周数据备份为例，在星期一进行完全备份，在星期二至星期六进行增量备份。
如果在星期六数 据被破坏了，则需要还原星期一正常的备份和从星期二至星期五的所有增量备份。
这种策略备份数据需要较少的时间，但还原数据使用较长的时间。
2.3 MySQL冷备份和热备份
冷备份和热备份是按数据库的运行状态分类的
2.3.1 冷备份
冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
优点：
- 是操作比较方便的备份方法（只需拷贝文件）
- 低度维护，高度安全。
缺点:
- 在实施备份的全过程中，数据库必须要作备份而不能作其它工作。
- 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢慢。
- 不能按表或按用户恢复。
2.3.2 热备份
热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句。
优点:
- 可在表空间或数据文件级备份，备份时间短。
- 备份时数据库仍可使用。
- 可达到秒级恢复（恢复到某一时间点上）。
缺点:
- 不能出错，否则后果严重。
- 因难维护，所以要特别仔细小心，不允许“以失败而告终”。
2.4 实战演练
2.4.1 冷备份实战
# 1) 关闭SELinux
# 修改 selinux 配置文件,将SELINUX=enforcing改为SELINUX=disabled，保存后退出
vim /etc/selinux/config
# SELINUX=disabled
reboot # 修改后需要重启
# 2) 使用MySQL，找到MySQL数据文件位置,停止MySQL服务
SHOW VARIABLES LIKE '%dir%';
# 结果显示, 数据目录就是datadir的所在位置，即 /var/lib/mysql/
service mysqld stop -- 停止mysql
# 3) 进入到 /mysql 目录, 执行打包命令 将数据文件打包备份
cd /var/lib/ # 进入其上级目录
tar jcvf /root/backup.tar.bz2 mysql/ # 打包压缩到 root目录下
# 4) 删除掉数据目录下的所有数据
rm -rf /var/lib/mysql/
# 5) 恢复数据 (使用tar命令)
# 解压
tar jxvf backup.tar.bz2 mysql/
# 把备份的文件移动到/var/lib/里面去替代原来的mysql
mv /root/mysql/ /var/lib/
# 6) 启动MySQL, 然后登陆MySQL,查看数据是否丢失, 如果数据正常代表冷备成功
service mysqld start
2.4.2 热备份实战
-
mysqldump 备份工具：是MySQL数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G）可以用于备份
-
热备可以对多个库进行备份，可以对单张表或者某几张表进行备份。
备份单个数据库
# 1) 创建文件夹 , 备份数据
mkdir databackup
cd databackup
mysqldump -uroot -p lagou_edu > lagou_edu.sql
# 2) 使用MySQL，模拟数据丢失，删除数据库,然后重新创建一个新的库
DROP DATABASE lagou_edu;
CREATE DATABASE lagou_edu CHARACTER SET 'utf8';
# 3) 恢复数据
cd databackup
mysql -uroot -p lagou_edu < lagou_edu.sql
备份数据库的某些表
# 1) 备份 表数据
mysqldump -uroot -p lagou_edu course course_lesson > backupTable.sql
# 2) 使用MySQL，模拟数据丢失，删除数据表
DROP TABLE course;
DROP TABLE course_lesson;
# 3) 恢复数据
mysql -uroot -p lagou_edu < backupTable.sql
直接将MySQL数据库压缩备份
# 1) 备份数据
mysqldump -uroot -p lagou_edu | gzip > lagou_edu.sql.gz
# 2) 使用MySQL，模拟数据丢失，删除数据库
DROP DATABASE lagou_edu;
CREATE DATABASE lagou_edu CHARACTER SET 'utf8'</
最低0.47元/天 解锁文章
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
