简述MySQL体系结构与MySQL的 安装

1. 简述MySQL体系结构

2. 安装部署MySQL（使用yum以及通用二进制方式）

1.简述MySQL体系结构

MySQL分为四层体系结构，分别是：

1. 连接层
2. SQL层
3. 插件式存储引擎层
4. 物理文件层

1.连接层：

由connection pool（连接池）构成
目的：解决资源频繁分配、释放所造成的问题，而连接池的作用相当于一个缓存池。
原理：预先存取一定数量连接在连接池中，如果需要建立数据库连接，会从连接池中取出一个，使用完毕后重新放回连接池。

2.SQL层

由系统管理和控制工具、SQL接口，解析器，优化器，缓存组成
Management Serveices & Utilities：系统管理和控制工具，用于管理数据库
SQL Interface：SQL接口，接受用户的SQL命令并返回对应查询结果，例如“select * from”
Parser：解析器，验证和解析收到的SQL命令
Optimizer：优化器，在SQL语句进行查询之前进行优化
Cache和Buffer：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接从查询缓存中提取数
据.（提高速度\效率）

3.插件式存储引擎层

Engine：存储引擎，存储引擎是MySql中具体与文件打交道的子系统
原理：插件式，可以理解为需要什么功能，就插入什么类型的存储引擎（方便工作）

4.物理文件层

用于存放数据文件、日志文件、配置文件以及其它文件。
 

yum方法下载RPM解压安装

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# 
[root@localhost ~]# setenforce 0
[root@localhost ~]# sed -i '/^SELINUX=/ c SELINUX=disabled' /etc/selinux/config
[root@localhost ~]# 
[root@localhost ~]# cd /etc/yum.repos.d/
[root@localhost yum.repos.d]# touch mysql.repo
[root@localhost yum.repos.d]# ll
total 40
-rw-r--r--. 1 root root 1664 Oct 23  2020 CentOS-Base.repo
-rw-r--r--. 1 root root 1309 Oct 23  2020 CentOS-CR.repo
-rw-r--r--. 1 root root  649 Oct 23  2020 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root  314 Oct 23  2020 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  630 Oct 23  2020 CentOS-Media.repo
-rw-r--r--. 1 root root 1331 Oct 23  2020 CentOS-Sources.repo
-rw-r--r--. 1 root root 8515 Oct 23  2020 CentOS-Vault.repo
-rw-r--r--. 1 root root  616 Oct 23  2020 CentOS-x86_64-kernel.repo
-rw-r--r--. 1 root root    0 Jul  5 13:40 mysql.repo

[root@localhost ~]# ll
total 570956
-rw-------. 1 root root      1419 Jul  5 08:54 anaconda-ks.cfg
-rw-r--r--. 1 root root 584652800 Jul  5 13:56 mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
[root@localhost ~]# mkdir mysql
[root@localhost ~]# ll
total 570956
-rw-------. 1 root root      1419 Jul  5 08:54 anaconda-ks.cfg
drwxr-xr-x. 2 root root         6 Jul  5 13:57 mysql
-rw-r--r--. 1 root root 584652800 Jul  5 13:56 mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
[root@localhost ~]# tar xf mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar -C mysql
[root@localhost ~]# cd mysql
[root@localhost mysql]# ll
total 570956
-rw-r--r--. 1 7155 31415  25079412 Mar 20  2017 mysql-community-client-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    277784 Mar 20  2017 mysql-community-common-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   3777808 Mar 20  2017 mysql-community-devel-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  45493312 Mar 20  2017 mysql-community-embedded-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  23924496 Mar 20  2017 mysql-community-embedded-compat-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 126060264 Mar 20  2017 mysql-community-embedded-devel-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   2237888 Mar 20  2017 mysql-community-libs-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   2113196 Mar 20  2017 mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  54661812 Mar 20  2017 mysql-community-minimal-debuginfo-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 169622200 Mar 20  2017 mysql-community-server-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  14501488 Mar 20  2017 mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 116887988 Mar 20  2017 mysql-community-test-5.7.18-1.el7.x86_64.rpm
[root@localhost mysql]# yum localinstall -y mysql-community-server-5.7.18-1.el7.x86_64.rpm mysql-community-client-5.7.18-1.el7.x86_64.rpm mysql-community-common-5.7.18-1.el7.x86_64.rpm mysql-community-libs-5.7.18-1.el7.x86_64.rpm

Complete!

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2023-07-05 14:01:18 CST; 11min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 9635 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 9562 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 9638 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─9638 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 05 14:01:15 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 05 14:01:18 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# 
[root@localhost ~]# systemctl enable mysqld
[root@localhost ~]# awk '/temporary password/{print $NF}' /var/log/mysqld.log
5Fa8f.rDiZ(l
[root@localhost ~]# 
[root@localhost ~]# mysql -uroot -p
Enter password: 
mysql>
mysql> alter user root@localhost identified by 'Root@1234'; //修改密码
Query OK, 0 rows affected (0.01 sec)

通用二进制方式--生产环境

[root@localhost local]# groupadd -r mysql
[root@localhost local]# useradd mysql -r -g mysql -c "MySQL Server" -s /bin/false
[root@localhost local]#  tar xf mysql-5.7.14-linux-glibc2.5-x86_64.tar -C /usr/local
[root@localhost local]# tar xf /usr/local/mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@localhost local]# ln -sv /usr/local/mysql-5.7.14-linux-glibc2.5-x86_64 /usr/local/mysql
‘/usr/local/mysql’ -> ‘/usr/local/mysql-5.7.14-linux-glibc2.5-x86_64’
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
2023-07-06T10:46:26.417353Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-07-06T10:46:27.184576Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-07-06T10:46:27.323742Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-07-06T10:46:27.391559Z 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: 5bf1813a-1bea-11ee-acf0-000c29092b5e.
2023-07-06T10:46:27.394010Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-07-06T10:46:27.397457Z 1 [Note] A temporary password is generated for root@localhost: q=Gfkw&tr3yw

[root@localhost local]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? n
[root@localhost local]# vim /etc/my.cnf
[root@localhost local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# chkconfig --add mysqld
[root@localhost local]# chkconfig mysqld on
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
[2] 12169
[root@localhost ~]# 2023-07-06T11:25:08.415226Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
2023-07-06T11:25:08.445133Z mysqld_safe A mysqld process already exists

mysql> alter user root@localhost identified by 'RedHat@123';
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>