结束低效学习，MySQL技术原理大总结
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
- 前言
-
- 🚀 1.企业版与社区版的区别
- 🚀 2.MySQL 安装及升级
-
- 🌈 2.1 安装部署
- 🌈 2.2 跨版本升级
- 🚀 3.SQL实训
-
- 🌈 3.1 基础查询
- 🌈 3.2 高阶查询
- 🚀 4.体系架构
-
- 🌈 4.1 参数文件
- 🌈 4.2 pid 文件
- 🌈 4.3 表结构文件
- 🌈 4.4 日志文件
- 🌈 4.5 错误日志
- 🌈 4.6 全查询日志
- 🌈 4.7 二进制日志Binlog
- 🌈 4.8 慢查询日志
- 🌈 4.9 Redo
- 🌈 4.10 undo
- 🌈 4.11 中继日志
- 🚀 5.备份恢复
-
- 🌈 5.1 备份方法
- 🌈 5.2 备份文件的种类
- 🌈 5.3 备份内容
- 🚀 6.高可用
- 🚀 6.如何体系拿下MySQL
前言
本文总结了MySQL的核心技术原理分享给大家
🚀 1.企业版与社区版的区别
企业版（收费版本）
社区版（开源版本）
Mysql 企业版（商业版）是由 mysql 公司内部发布，同时参考社区版的先进代码功能和算法，是 mysql 公司的赢利产品，需要付费才能使用及提供服务支持，稳定性和
可靠性无疑都是最好的,但不遵守 GPL 协议分类门户型网站有买商业版的.
Mysql 社区版则是由分散在世界各地的 mysql 开发者，爱好者以及用户参与开发与测试，并完成软件代码的管理，测试工作，社区也会设立 BUG 汇报机制，收集用户
遇到 BUG 问题情况，相比商业版，社区版的开发及测试环境没有那么严格.遵守 GPL 协议
社区版在技术方面会加入许多新的未经严格测试的特性，以从广大社区用户得到反馈和修正。社区版源码无规律，很多社区用户都可以补充和修正，社区
版未经各个专有系统平台的压力测试和性能测试，社区版在当今高速发展的软件和硬件体系的兼容性方面都可能存在技术风险。社区版可以看作是企业版的
“广泛体验版”，可靠性、稳定性不高、技术成熟度低。
企业版经过严格测试认证，更加稳定、安全、可靠，性能也比社区版好。企业版使用商业的编译器对代码进行编译和优化，源代码有规律且稳定而且执行
效率高，各版本平台绑定优化，同时包含企业级图型监控软件、服务和支持，可以监控软件运行状态，技术预警，出现问题后可根据源码编排规律和资深
MySQL数据库专家及时查找和修正，使技术风险降到最低，定期的升级支持包可以良好的解决软硬件兼容性问题。
🚀 2.MySQL 安装及升级
🌈 2.1 安装部署
Linux 之下 之下 MySQL 安装的三种方案的比较
在 Windows 下可以使用 NOINSTALL 包和图形化包来安装，在 Linux 下可以使用如下 3 种方式来安装：
RPM （Redhat Package Manage）：安装简单，适合初学者学习使用，一台服务器只能安装一个 MySQL，Red Hat Enterprise Linux / Oracle Linux
二进制（ Binary Package）：安装简单；可以安装到任何路径下，灵活性好；一台服务器可以安装多个 MySQL，Linux - Generic
源码（ Source Package）：在实际安装的操作系统进行可根据需，要定制编译，最灵活；性能最好；一台服务器可以安装多个 MySQL，Source Code
Linux系统静默安装MySQL,跨版本升级
🌈 2.2 跨版本升级
🚩为什么升级 MySQL
基于安全考虑
基于性能和稳定性考虑：mgr 复制 ，并行复制 writeset 等功能，性能提升
新的功能：Hash join ，窗口函数，DDL 即时，json 支持
原始环境中版本太多，统一版本
8.0 版本基本已到稳定期，可以大量投入生产环境中
🚩升级前注意事项
升级前注意事项
1.注意字符集设置:
为了避免新旧对象字符集不一致的情况，在配置文件将字符集和校验规则设置为旧版本的字符集和比较规则。
2.密码认证插件变更:
为了避免连接问题，可以仍采用 5.7 的 mysql_native_password 认证插件。*
3.sql_mode 支持问题:
8.0 版本 sql_mode 不支持 NO_AUTO_CREATE_USER，要避免配置的 sql_mode 中带有NO_AUTO_CREATE_USER。*
4.是否需要手动升级系统表:
在 MySQL 8.0.16 行 版本之前，需要手动的执行 mysql_upgrade 在 来完成该步骤的升级，
在 MySQL8.0.16 版本及之后是由 mysqld 来完成该步骤的升级。
5.高可用架构:
需要先升级从库，再逐步升级主库
🚩 MySQL 升级方法选择
跨版本升级：
—可行
5.5.57 --> 5.6.48
5.5.62 --> 5.7.30
5.6.37 --> 5.7.19
5.7.30 --> 8.0.19
– 不可行，需要使用中间版本过度
5.5.62 --> 8.0.19
5.6.37 --> 8.0.19
Lnux系统静默安装MySQL,跨版本升级：
https://jeames.blog.csdn.net/article/details/123781576
🚀 3.SQL实训
🌈 3.1 基础查询
1.查询 student 表的所有行
2.查询 student 表中的 name、sex 和 class 字段的所有行
3.查询 teacher 表中不重复的 department 列
4.查询 score 表中成绩在 50-80 之间的所有行
5.查询 score 表中成绩为 85, 86 或 88 的行
6.查询 student 表中 ‘95033’ 班或性别为 ‘女’ 的所有行
7.以 class 降序的方式查询 student 表的所有行
8.查询 “95031” 班的学生人数
9.查询 score 表中的最高分的学生学号和课程编号
10.以class为降序、生日为升序排序，查询student表
🌈 3.2 高阶查询
1.查询每门课的成绩平均成绩
2.查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数
3.查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree
4.查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department
5.查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree
6.查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列
7.查询95031班的学生每门课程的平均成绩
8.查询 “计算机系” 课程的成绩表
9.查询某选修课程多于 5 个同学的教师姓名
10.查询计算机系与电子工程系中的不同职称的教师
11.查询课程3-105且成绩至少高3-245的score信息，成绩降序
12.查询课程3-105且成绩高于3-345的score信息
13.查询某课程成绩比该课程平均成绩低的score信息
14.查询student表中至少有3名男生的class
15.查询studnet表中年龄最大的和年龄最小
16.查询student表中每个学生的姓名和年龄
17.scores_tb根据成绩从高到低排序，使用row_number或者dense_rank开窗函数
【MySQL8入门到精通】运维篇-SQL实战（100道题库）
https://jeames.blog.csdn.net/article/details/121688943
🚀 4.体系架构
🌈 4.1 参数文件
🌈 4.2 pid 文件
当 mysql 实例启动的时候，会将自己的进程 id 写入一个文件中，该文件即为 pid 文件，由参数 pid_file 控制，默认路径位于数据目录下，可以通过以下方式查看：mysql> show variables like '%datadir%';mysql> show variables like 'pid_file';
🌈 4.3 表结构文件
innodb 包括 ibd 和 frm,当你启用了 innodb_file_per_table，表被存储在他们自己的表空间里。mysql> show variables like 'innodb_file_per_table';
innodb_data_file_path 用来指定 innodb tablespace 文件，如果我们不在 My.cnf 文件中指定innodb_data_home_dir 和 innodb_data_file_path 那么默认会在 datadir 目录下创建 ibdata1 作为 innodb tablespace。当你启用了 innodb_file_per_table，表被存储在他们自己的表空间里，但是共享表空间仍然在存储其它的 InnoDB 内部数据：✓ 数据字典，也就是 InnoDB 表的元数据✓ 变更缓冲区✓ 双写缓冲区✓ 撤销日志🌈 4.4 日志文件
日志文件记录了影响 mysql 数据库的各种类型活动，常见的日志文件有错误日志、二进制日志、慢查询日志、全查询日志、redo 日志、undo 日志。🌈 4.5 错误日志
错误日志对 mysql的启动、运行、关闭过程进行了记录，mysql dba 在遇到问题时候，第一时间应该查看这个错误日志文件，该文件不但记录了出错信息，还记录了一些警告信息以及正确信息，这个 error 日志文件类似于 oracle 的 alert 文件，只不过默认情况下是以 error 结尾。可以通过 show variables like 'log_error';DBA 在遇到问题的时候，第一时间应该查看这个错误日志文件，该文件不但记录了出错信息，还记录了一些警告信息以及正确信息，这个 error 日志文件类似于 Oracle的 alert 文件，只不过默认情况下是以 err 结尾。可以通过“show variables like 'log_error';”命令查看错误日志的路径。如下所示：
🌈 4.6 全查询日志
全查询日志记录了所有对数据库请求的信息，正确的 SQL 才会被记录下来（错误写法的 SQL 语句不会记录），包括 show、查询 select 语句、权限不足的语句（ERROR 1044 (42000): Access denied for user）。默认位置在变量 datadir 下，默认文件名为：主机名.log。MySQL 的通用查询日志默认情况下是不开启的。当需要进行采样分析时手工开启：mysql> show variables like 'general_log';
##日志开启mysql> SET GLOBAL general_log=1;mysql> show variables like 'general_log';默认名称为：变量 datadir 下：主机名.log🌈 4.7 二进制日志Binlog
Binlog 是 MySQL 中一个很重要的日志，记录了对数据库进行变更的操作，但是不包括 select操作以及 show 操作，因为这类操作对数据库本身没有没有修改。如果想记录 select和 show 的话，那就需要开启全查询日志。另外 binlog 还包括了执行数据库更改操作时间和执行时间等信息。binlog 是 MySQL Server 层记录的二进制日志文件。mysql> show variables like ‘%log_bin%’;
mysql> flush logs; --滚动日志
mysql> show binary logs;
二进制的主要作用有如下 2 个：（1 ）恢复 recovery 。某些数据的恢复需要二进制日志，在全库文件恢复后，可以在此基础上通过二进制日志进行 point-to-time 的恢复（mysqldump 全量恢复+binlog增量恢复）。（2 ）复制（replication ）。其原理和恢复类似，通过复制和执行二进制日志使得一台远程的 mysql数据库（slave）于一台 mysql 数据库（master）进行实时同步。🌈 4.8 慢查询日志
当查询超过一定时间没有返回结果的时候，才会记录进慢查询日志。• 慢查询日志可以帮助 DBA 找出执行效率缓慢的 SQL 语句，为数据库优化工作提供帮助。• 慢查询日志默认是不开启的，建议开启慢查询日志。• 当需要进行采样分析时手工开启。mysql> show variables like ‘%long_query_time%’;
慢查询日志开启方法如下：
mysql> show variables like ‘%slow_%’;
mysql> set global slow_query_log=1;
mysql> show variables like ‘%slow_%’;
🌈 4.9 Redo
记录 InnoDB 等支持事务的存储引擎执行事务时产生的日志。事务型存储引擎用于保证原子性、一致性、隔离性和持久性。其变更数据不会立即写到数据文件中，而是写到事务日志中。事务日志文件名为“ib_logfile0”和“ib_logfile1”，默认存放在表空间所在目录。查看参数设置：show variables like ‘innodb%log%’;
🌈 4.10 undo
8.0 中的 Undo 日志
开始从 L SQL 理 层面非常方便的管理 Undo 表空间，MySQL 服务启动后，默认有两个 Undo 表空间：undo01,undo02。这两个默认产生的 Undo 表空间文件，不能在 SQL 层面来管理。直接删除会被 MySQL 阻止。mysql> select name from information_schema.innodb_tablespaces where space_type='Undo';
undo表空间是不能删除的mysql> drop undo tablespace innodb_undo_001;mysql> show errors;
🌈 4.11 中继日志
从主服务器的二进制日志文件中复制而来的事件，
并保存为二进制的日志文件。
中继日志也是二进制日志，用来给 slave 库恢复。
🚀 5.备份恢复
✨✨MySQL支持的备份类型如下图所示:
🌈 5.1 备份方法
备份可以分为如下 3种：
a.热备份（Hot Backup）：热备份也称为在线备份（Online Backup），是指在数据库运行的过程中进行备份，对生产环境中的数据库运行没有任何影响。
常见的热备方案是利用 mysqldumpXtraBackup 等工具进行备份。
b.冷备份（Cold Backup）：冷备份也称为离线备份（Offline Backup），是指在数据库关闭的情况下进行备份，这种备份非常简单，只需要关闭数据库，复制相关的物理文件即可。目前，线上数据库一般很少能够接受关闭数据库，所以该备份方式很少使用。
c.温备份（Warm Backup）：温备份也是在数据库运行的过程中进行备份，但是备份会对数据库操作有所影响。该备份利用锁表的原理备份数据库，由于影响了数据库的操作，故该备份方式也很少使用。
🌈 5.2 备份文件的种类
备份可以分为如下两种：Physical (Raw) Versus Logical Backups
a.物理备份（Physical Backup）：物理备份也称为裸文件备份（Raw Backup），是指复制数据库的物理文件。物理备份即可以在数据库运行的情况下进行备份（常见备份工具：MySQL Enterprise Backup（商业）、XtraBackup 等），也可以在数据库关闭的情况下进行备份。该备份方式不仅备份速度快，而且恢复速度也快，但是由于无法查看备份后的内容，所以只能等到恢复之后，才能检验备份出来的数据是否是正确的。
b.逻辑备份（Logical Backup）：逻辑备份是指备份文件的内容是可读的，该文本一般都是由一条条 SQL 语句或者表的实际数据组成。常见的逻辑备份方式有 mysqldump、
SELECT … INTO OUTFILE 等方法。这类备份方法的好处是可以观察备份后的文件内容，缺点是恢复时间往往都会很长。
逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；
而物理备份则不同，不同的存储引擎有着不同的备份方法。
因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。
🌈 5.3 备份内容
备份可以划分为如下 3 种：
a.全量备份（Full Backup）：全量备份（完全备份）是指对数据库进行一次完整的备份，备份所有的数据，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。这是一般常见的备份方式，可以使用该备份快速恢复数据库，或者搭建从库。恢复速度也是最快的，但是每次备份会消耗较多的磁盘空间，并且备份时间较长。所以，一般推荐一周做一次全量备份。
b.增量备份（Incremental Backup）：增量备份也叫差异备份，是指基于上次完整备份或增量备份，对数据库新增的修改进行备份。这种备份方式有利于减少备份时使用的磁盘空间，加快备份速度。但是恢复的时候速度较慢，并且操作相对复杂。推荐每天做一次增量备份。
c.日志备份（Binary Log Backup）：日志备份是指对数据库二进制日志的备份。二进制日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。该备份方式一般与上面的全量备份或增量备份结合使用，可以使数据库恢复到任意位置。所以，推荐每小时甚至更频繁的备份二进制日志。
在生产环境上，一般都会选择以物理备份为主，逻辑备份为辅，加上日志备份，来满足线上使用数据库的需求。
🚀 6.高可用
1 主 2 从+HAProxy–读负载均衡
双主+Keepalived–单点故障（故障自动切换）
双主+HAProxy 实现负载均衡（不建议）
1 主 2 从+MySQL Router—单点故障（故障自动切换）、
读负载均衡、读写分离（通过不同的端口号来实现的）
1 主 2 从+ProxySQL—单点故障（故障自动切换）、读负载均衡、读写分离
MHA --（搭建、主从自动切换、发送告警邮件）
MHA+ProxySQL 测试-- 读写分离
PXC+HAProxy
MGR（搭建、单主和多主切换、添加节点）、MGR+HAProxy、MGR+ProxySQL
Orchestrator 高可用管理工具（拓扑图、自动 failover）
MySQL Router 的主要用途是读写分离，主主故障自动切换，负载均衡
https://jeames.blog.csdn.net/article/details/122262184
1主2从GTID复制
https://jeames.blog.csdn.net/article/details/124514052
🚀 6.如何体系拿下MySQL
MySQL8入门到精通，每周更新2篇，欢迎订阅哦，福利多多
CSDN专栏地址：
https://blog.csdn.net/weixin_41645135/category_11595820.html
创作打卡挑战赛 
赢取流量/现金/CSDN周边激励大奖