MYSQL系列-整体架构介绍

技术驿站
834 阅读6分钟

引言

MYSQL是日常开发中必不可少需要熟练掌握的关系型数据库，本系列主要根据日常工作和学习，对MYSQL使用以及遇到问题的总结。希望对MYSQL系列的总结，能够加深对MYSQL的理解和掌握。

整体架构

MySQL 是一种流行的开源关系型数据库管理系统，它由四个主要组件构成: 网络连接层，服务层，存储引擎层和物理层。

网络连接层

网络连接层是 MySQL 的最上层，它提供了客户端和服务器之间的通信协议。客户端可以使用不同的编程语言（C JAVB PYTHON）和协议（例如，TCP/IP、SSH 或 SSL）来连接到服务器，并发送 SQL 查询和命令。网络连接层还可以提供安全性和身份验证功能，以确保只有授权的用户可以访问数据库。

服务层

服务层是 MySQL 的中间层，它主要负责查询解析、查询优化和缓存管理。当客户端发送 SQL 查询时，服务层会将其解析为可执行的语句，并尝试优化查询以提高性能。服务层还可以将查询结果缓存在内存中，以便在下一次执行相同查询时能够更快地返回结果。 下图是SQL查询过程：

image.png

存储引擎层

存储引擎层是 MySQL 的核心组件之一，它负责管理数据的存储和检索。
MySQL 支持多个存储引擎，如 InnoDB、MyISAM、Memory 等。每个存储引擎都有不同的特性和适用场景。
例如，InnoDB 是一个支持事务和行级锁定的存储引擎，适用于需要高并发和数据一致性的应用程序；而 MyISAM 则适用于读取密集型应用程序，因为它可以更快地执行查询操作。

物理层

物理层是 MySQL 的最底层，它负责管理数据在硬盘上的存储和检索。MySQL 使用文件系统来存储数据和元数据，例如表结构、索引和日志文件。
物理层还包括数据缓存和磁盘 I/O 管理，以提高数据读取和写入的性能。

具体架构图如下： image.png

  • Connectors：指的是不同语言中与SQL的交互

  • Management Serveices & Utilities： 系统管理和控制工具

  • Connection Pool： 连接池。管理缓冲用户连接，线程处理等需要缓存的需求。

  • SQL Interface： SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

  • Parser：解析器。SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。主要功能：

    • 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
    • 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

  • Optimizer: 查询优化器

  • Cache和Buffer： 查询缓存，不建议使用，后续MYSQL8.0版本已经废弃

  • Engine ：存储引擎

安装MYSQL

下面以win10系统通过wsl安装ubuntu系统，然后基于ubuntu安装mysql 5.7.26版本

win10 通过wsl安装ubuntu

可以参考下文 Windows 10 下安装Ubuntu（Wsl）

安装MYSQL

1.选取MYSQL版本

在downloads.mysql.com/archives/co… 中选择MYSQL版本

image.png

复制下载链接，使用wget下载

mysql@TOBY-HYW:~$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
--2023-06-28 22:04:40--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.37.145.123, 2600:140b:2:8aa::2e31, 2600:140b:2:8b6::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.37.145.123|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz [following]
--2023-06-28 22:04:43--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 104.117.185.43
Connecting to cdn.mysql.com (cdn.mysql.com)|104.117.185.43|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 678018165 (647M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz’

mysql-5.7.41-linux-glibc2.12-x86_64.ta 100%[==========================================================================>] 646.61M  1.74MB/s    in 5m 43s

2023-06-28 22:10:28 (1.88 MB/s) - ‘mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz’ saved [678018165/678018165]

2.新增MYSQL用户

toby@TOBY-HYW:/home$ sudo useradd -m mysql
toby@TOBY-HYW:/home$ sudo passwd mysql
New password:
Retype new password:
passwd: password updated successfully
toby@TOBY-HYW:/home$ sudo usermod -aG sudo mysql
toby@TOBY-HYW:/home$ sudo su - mysql
Welcome to Ubuntu 22.04.2 LTS (GNU/Linux 5.15.90.1-microsoft-standard-WSL2 x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

 * Strictly confined Kubernetes makes edge and IoT secure. Learn how MicroK8s
   just raised the bar for easy, resilient and secure K8s cluster deployment.

   https://ubuntu.com/engage/secure-kubernetes-at-the-edge

 * Introducing Expanded Security Maintenance for Applications.
   Receive updates to over 25,000 software packages with your
   Ubuntu Pro subscription. Free for personal use.

     https://ubuntu.com/pro

This message is shown once a day. To disable it please create the
/home/mysql/.hushlogin file.

3.解压并安装MYSQL

tar -zvxf ../mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz mysql-5.7.41-linux-glibc2.12-x86_64/

image.png

修改配置文件my.conf

mysql@TOBY-HYW:~/mysql3306$ mkdir conf
mysql@TOBY-HYW:~/mysql3306$ cd conf/

my.conf配置如下

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
#
[client]
port = 3306
socket = /home/mysql/mysql3306/mysqld.sock

[mysqld_safe]
socket = /home/mysql/mysql3306/mysqld.sock
pid-file = /home/mysql/mysql3306/mysqld.pid

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = /home/mysql/mysql3306
datadir = /home/mysql/mysql3306/data
port = 3306
server_id = 11
socket = /home/mysql/mysql3306/mysqld.sock

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

初始化MYSQL


mysql@TOBY-HYW:~/mysql3306/conf$ sudo /home/mysql/mysql3306/bin/mysqld --defaults-file=/home/mysql/mysql3306/conf/my.cnf --initialize --user=mysql;
[sudo] password for mysql:
/home/mysql/mysql3306/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

libaio.so.1找不到，使用如下命令安装

sudo apt-get install libaio1

再次执行，安装成功,root密码如下PEgJwk;Og7&d

mysql@TOBY-HYW:~/mysql3306/lib/plugin$ sudo /home/mysql/mysql3306/bin/mysqld --defaults-file=/home/mysql/mysql3306/conf/my.cnf --initialize --user=mysql;
2023-06-28T14:25:00.124809Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-06-28T14:25:00.125672Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2023-06-28T14:25:00.125678Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2023-06-28T14:25:00.431835Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-06-28T14:25:00.464341Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-06-28T14:25:00.525486Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 90acf47b-15bf-11ee-aceb-00155d34f9dc.
2023-06-28T14:25:00.529186Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-06-28T14:25:00.786769Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-06-28T14:25:00.786797Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-06-28T14:25:00.787165Z 0 [Warning] CA certificate ca.pem is self signed.
2023-06-28T14:25:00.815033Z 1 [Note] A temporary password is generated for root@localhost: PEgJwk;Og7&d

启动MYSQL

mysql@TOBY-HYW:~/mysql3306/lib/plugin$ sudo /home/mysql/mysql3306/bin/mysqld_safe --user=mysql &
[1] 8851
mysql@TOBY-HYW:~/mysql3306/lib/plugin$ Logging to '/home/mysql/mysql3306/data/TOBY-HYW.err'.
2023-06-28T14:26:15.474686Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql3306/data

登录mysql

mysql@TOBY-HYW:~/mysql3306/lib/plugin$ /home/mysql/mysql3306/bin/mysql -uroot -p -S /home/mysql/mysql3306/mysqld.sock
/home/mysql/mysql3306/bin/mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

libncurses.so.5找不到，使用

sudo apt-get install libncurses5

最终启动成功

mysql@TOBY-HYW:~/mysql3306$ /home/mysql/mysql3306/bin/mysql -uroot -p
Enter password:

修改root密码

set password = password('123456');

4.安装第二个MYSQL

和第一个类似，复制一个3307的文件夹，然后修改my.conf,把里面的3306改为3307，再把server_id改为12

image.png

5.相关执行命令汇总

sudo /home/mysql/mysql3306/bin/mysqld --defaults-file=/home/mysql/mysql3306/conf/my.cnf --initialize --user=mysql;
/home/mysql/mysql3306/bin/mysqld_safe --defaults-file=/home/mysql/mysql3306/conf/my.cnf --user=mysql &
/home/mysql/mysql3306/bin/mysql -uroot -p -S /home/mysql/mysql3306/mysqld.sock

/home/mysql/mysql3306/bin/mysqladmin -S /home/mysql/mysql3306/mysqld.sock -uroot -p1Qaz1Qaz shutdown
set password = password('123456');

sudo /home/mysql/mysql3307/bin/mysqld --defaults-file=/home/mysql/mysql3307/conf/my.cnf --initialize --user=mysql;
/home/mysql/mysql3307/bin/mysqld_safe --defaults-file=/home/mysql/mysql3307/conf/my.cnf --user=mysql &
/home/mysql/mysql3307/bin/mysql -uroot -p -S /home/mysql/mysql3307/mysqld.sock

/home/mysql/mysql3307/bin/mysqladmin -S /home/mysql/mysql3307/mysqld.sock -uroot -p1Qaz1Qaz shutdown

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

系列文章

1.MYSQL系列-整体架构介绍
2.MYSQL系列-基本概念和SQL执行过程
3.MYSQL系列-索引介绍和优化
4.MYSQL系列-各种锁类型、如何加锁介绍和死锁研究
5.MYSQL系列-SQL查询
6.MYSQL系列-SQL查询之JOIN
7.MYSQL系列-高可用部署和异常恢复
8.MYSQL系列-常用命令一（启动、库、表）
9.MYSQL系列-常用命令二（索引、视图、存储过程、事务、数据类型）
10.MYSQL系列-常用命令三（导入导出命令、系统参数、系统函数）
11.MYSQL系列-日志文件、数据文件介绍
12.MYSQL系列-各种Buffer优化一~性能增强利器
13.MYSQL系列-各种Buffer优化二~性能增强利器
14.MYSQL系列-分库分表（一）：原理介绍
15.MYSQL系列-分库分表（二）：Spring动态数据源实现分库分表落地实践-上
16.MYSQL系列-分库分表（二）：Spring动态数据源实现分库分表落地实践-下
17.MYSQL系列-分库分表（三）：Sharding-JDBC实现分库分表落地实践-上
18.MYSQL系列-分库分表（三）：Sharding-JDBC实现分库分表落地实践-中
19.MYSQL系列-分库分表（三）：Sharding-JDBC实现分库分表落地实践-下
20.MYSQL系列-分库分表（四）：MYCAT实现分库分表落地实践-上
21.MYSQL系列-数据库连接池：HikariCP介绍和使用
22.MYSQL系列-数据库连接池：Druid介绍和使用

他山之石

面试爽文 ：怎么也想不到，一个 MySQL 锁能问出20多个问题（万字长文）

avatar
文章
阅读
粉丝