第二篇：数据库应用配置、多实例构建、版本升级、服务体系结构

数据库服务初始化配置（应用配置）

服务进行配置能够启动服务、实现某些功能

利用编译安装过程实现配置

# 下载数据库服务软件程序（源码程序）
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.12.tar.gz
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.12.tar.gz

# 处理数据库服务软件依赖
yum -y install wget cmake gcc gcc-c++ ncurses ncurses-devel libaio-devel openssl openssl-devel

# 解压数据库服务软件程序
tar xf mysql-8.0.12.tar.gz
tar zxf mysql-boost-8.0.12.tar.gz -C /usr/local/

# 创建数据库服务管理用户
useradd mysql -s /sbin/nologin

# 创建数据库服务所需目录
mkdir -p /usr/local/mysql8.0  -- 数据库服务程序数据存储目录
mkdir -p /data/mysql8.0/log  -- 数据库服务日志存储目录
chown -R mysql:mysql /data/mysql8.0/
chown -R mysql:mysql /usr/local/mysql8.0

# 数据库服务编译安装配置过程（初始化过程）
cd /usr/local/src/mysql-8.0.12
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql8.0 -DMYSQL_DATADIR=/usr/local/mysql/data \
-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql-8.0.12/ -DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DTRACE=0 -DENABLED_LOCAL_INFILE=1 -DDEFAULT_CHARSET=utf8mb4

# 数据库服务编译安装部署过程
make && make install

编译安装mysql-8.0.12

更详细数据库服务源码部署安装过程参考链接：http://www.hzhcontrols.com/new-641636.html

编写配置文件信息实现配置⭐⭐

数据库配置文件加载顺序⭐

/etc/my.cnf  默认配置文件

# 通过数据库服务启动命令获取加载文件顺序（mysqld --help --verbose|grep my.cnf）
/etc/my.cnf -->  /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf 

# 手工指定加载配置文件的路径位置信息（数据库启动时进行指定）
mysqld --defaults-file=/opt/my.cnf &
mysqld_safe --defaults-file=/opt/my.cnf &

配置文件信息结构说明⭐

服务端常见标签类型：[mysqld] [mysql_safe] [server]

客户端常用标签类型：[mysql] [mysqldump] [client]

序号	配置部分	配置信息说明
01	带有[]部分	表示配置标签部分，用于区分不同程序运行的参数，包含服务器端和客户端标签信息
	`[服务端标签]`	负责数据库服务端运行参数信息设定
	`[客户端标签]`	影响客户端与服务连接参数信息设定（只影响本地客户端连接，不影响远程客户端连接）
02	不带[]部分	表示配置参数部分

配置文件信息内容说明⭐⭐⭐⭐

获取全部配置参数 mysql --verbose --help

序号	配置信息	解释说明
	[mysqld]	服务端配置标签
01	user=mysql	表示数据库服务管理用户信息
02	basedir=/usr/local/mysql	表示数据库服务程序安装路径
03	datadir=/data/3306/data	表示数据库服务数据存储路径
04	server_id=6	表述数据库服务标识节点编号（主从复制的唯一编号）
05	port=3306	表示数据库服务启动端口设定
06	socket=/tmp/mysql.sock	表示数据库服务本地连接文件（套接字文件）
07	default_authentication_plugin=mysql_native_password	表示数据库服务加密插件设定，用于向下兼容
	[mysql]	客户端配置标签（本地连接数据库生效）
01	socket=/tmp/mysql.sock	表示数据库服务本地连接文件（套接字文件）

利用启动命令参数启动进行配置

# 利用数据库服务命令启动服务加参数信息，完成初始化定制操作
mysqld_safe --skip-grant-tables --skip-networking &

数据库服务多实例创建⭐⭐⭐

多实例：在一个系统环境中，可以运行多个相同的服务程序信息，并且产生不同的进程和网络端口信息

1666720261445

步骤一：多实例部署环境规划

实例信息编号	实例服务端口	实例存储路径	实例配置文件	套接字文件
mysql-01	端口信息：3307	/data/3307/data	/data/3307/data/my.cnf	/tmp/mysql3307.sock
mysql-02	端口信息：3308	/data/3308/data	/data/3308/data/my.cnf	/tmp/mysql3308.sock
mysql-03	端口信息：3309	/data/3309/data	/data/3309/data/my.cnf	/tmp/mysql3309.sock

步骤二：多实例部署环境准备

# 创建数据库多实例所需目录
[root@db01 ~]# mkdir -p /data/330{7..9}/data

# 授权数据库多实例所需权限
[root@db01 ~]# chown -R mysql. /data/*

步骤三：多实例初始化操作

# 多实例初始化操作过程
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data
-- 初始化完毕后可以检查数据库目录中，是否已经存在了数据库初始化产生的数据文件信息；

步骤四：多实例配置文件编写

[root@db01 ~]# cat /data/3307/my.cnf 
[mysql]
socket=/tmp/mysql3307.sock
[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock

/data/3307/my.cnf

[root@db01 ~]# cat /data/3308/my.cnf 
[mysql]
socket=/tmp/mysql3308.sock
[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock

/data/3308/my.cnf

[root@db01 ~]# cat /data/3309/my.cnf 
[mysql]
socket=/tmp/mysql3309.sock
[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock

/data/3309/my.cnf

步骤五：多实例服务运行启动

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

[root@db01 ~]# ss -lntup |grep mysqld
tcp LISTEN 0 128 [::]:3307 [::]:* users:(("mysqld",pid=2206,fd=31))
tcp LISTEN 0 128 [::]:3308 [::]:* users:(("mysqld",pid=2374,fd=31))
tcp LISTEN 0 128 [::]:3309 [::]:* users:(("mysqld",pid=2542,fd=31))

启动是否指定配置文件⭐⭐⭐

数据库服务版本升级⭐⭐⭐

数据库服务的升级不同于其他程序升级，因为数据库服务中保存着企业重要的数据信息，一旦升级失败就会造成巨大损失

方式	升级说明	备注说明
单台服务器升级（`Inplace-就地`）	数据库服务小版本升级	比如：5.7.20 ->5.7.22
（风险较大）	数据库服务大版本升级	比如：5.7.20 ->5.8.20
迁移服务器升级（Mergeing-迁移）	数据库服务小版本升级	安装新版本数据库服务（备份迁移/主从迁移）
（需要迁移数据）	数据库服务大版本升级	安装新版本数据库服务（备份迁移/主从迁移）

数据库服务升级注意事项

数据库服务版本升级时，只支持在GA（General Availability）版本之间进行升级
数据库服务版本升级时，支持从数据库5.6到5.7再到8.0，跨版本升级，但是需要先将5.6升级到最新小版本，在进行跨版本升级
数据库服务版本升级时，需要提前考虑好版本回退的方案，最好升级前做好数据备份（特别是向8.0版本升级）
数据库服务版本升级时，制定的升级方案和升级步骤，需要尽可能降低数据库服务停机的时间

数据库服务官方参考资料：https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

数据库服务升级过程规划（Inplace）⭐⭐⭐

数据库服务数据备份保存（可以采用热备和冷备两种方案，冷备是需要停止业务后备份，热备是无需停止业务备份）
数据库服务最新程序安装（最新版本数据库服务安装过程时，无需停止原有数据库旧版服务）
数据库服务原有程序关闭（网站显示维护页面）
数据库服务最新程序启动（加载原有程序数据实现挂库升级，并采用跳过授权表和跳过网络方式启动）
数据库服务升级数据结构（数据库服务升级程序后，还需要升级数据系统结构信息，因此升级时间和数据量无关）
数据库服务可以正常重启（数据库服务升级完毕后，确认数据库服务是可以正常完成重启操作）
数据库服务功能测试验证（反复核实验证与数据库服务相关的各项功能是否正常）
数据库服务升级工作完毕（取消网站维护页面，恢复正常网站线上运营业务）

5.6.48 -> 5.7.30 本地升级⭐⭐⭐⭐⭐

先在5.6中创建数据信息模拟数据库中的数据

数据库服务最新程序安装

# 数据库服务具体安装过程省略，通过查看服务程序目录信息确认是否安装完毕
[root@db01 local]# ll -d mysql*
lrwxrwxrwx  1 root root        35 Aug  5 17:59 mysql56 -> mysql-5.6.48-linux-glibc2.12-x86_64
drwxr-xr-x 13 root root       205 Aug  5 18:04 mysql-5.6.48-linux-glibc2.12-x86_64
lrwxrwxrwx  1 root root        35 Aug  5 18:29 mysql57 -> mysql-5.7.30-linux-glibc2.12-x86_64
drwxr-xr-x  9 root root       129 Aug  5 18:28 mysql-5.7.30-linux-glibc2.12-x86_64
# 通过服务运行端口信息，确认旧版本与新版本服务运行情况
[root@db01 ~]# ss -lntup |grep mysqld
tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=3223,fd=15))

数据库服务原有程序关闭

# 关闭原有旧版本数据库服务（模拟操作）
[root@xiaoQ-01 local]# systemctl stop mysqld56.service

# 关闭原有就版本数据库服务（企业实战）平滑关闭
[root@xiaoQ-01 local]# vim /data/3356/my.cnf
innodb_fast_shutdown=0 
-- 企业实战环境，需要添加此配置参数信息在配置文件中，实现优雅关闭数据库服务
[root@xiaoQ-01 local]# systemctl stop mysqld56.service
-- 配置文件加入以上参数后，此时关闭数据库服务，会采用优雅关闭服务，而不是采用快速关闭数据库服务方式

数据库服务最新程序启动

# 修改旧版本数据库服务配置文件，使用新版本程序加载原有数据库中数据目录
[root@db01 ~]# cat /etc/my57.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3356/data
socket=/tmp/mysql.sock
innodb_fast_shutdown=0

# 利用命令脚本文件测试启动新版本数据库程序服务
/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my57.cnf --skip-grant-tables --skip-networking &

# 查看数据库服务启动进程信息
[root@db01 ~]# ps -ef |grep mysql
root       3669   3609  0 16:54 pts/0    00:00:00 /bin/sh /usr/local/mysql57/bin/mysqld_safe \
--defaults-file=/etc/my56.cnf --skip-grant-tables --skip-networking
mysql      3823   3669  0 16:54 pts/0    00:00:01 /usr/local/mysql57/bin/mysqld \
--defaults-file=/etc/my56.cnf --basedir=/usr/local/mysql57 --datadir=/data/3356/data \
--plugin-dir=/usr/local/mysql57/lib/plugin --user=mysql --skip-grant-tables --skip-networking \
--log-error=db01.err --pid-file=db01.pid --socket=/tmp/mysql.sock
root       3927   3609  0 16:57 pts/0    00:00:00 grep --color=auto mysql
[root@db01 ~]# ss -lntup |grep mysql  -- 此时数据库服务启动成功后，只会显示进程信息，不会显示网络端口信息

数据库服务升级数据结构

# 实现对原有数据库中数据信息的挂库升级操作
[root@db01 ~]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql.sock --force
Upgrade process completed successfully.
Checking if update is needed.
-- 显示以上信息内容，表述数据库中数据结构信息升级成功

# 验证数据库中数据结构是否升级成功
[root@db01 ~]# /usr/local/mysql57/bin/mysql -S /tmp/mysql.sock 
mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| my_import_data         |
| mysql                  |
| performance_schema     |
| sys                    |
| test                   |
+------------------------+
--- 升级完毕后，本地连接新版本数据库服务，查看数据库信息会显示sys库，表示数据结构信息升级成功

说明：在数据库版本从5.7升级到8.0时，可以省略mysql_upgrade升级改变数据结构信息操作，因为8.0版本会自动完成此步骤

数据库升级回滚⭐⭐⭐⭐⭐

企业数据库实战练习一：跨大版本回滚

数据库版本升级步骤一：数据库服务编写回滚文件

[root@xiaoQ-01 local]# vim /data/3356/my_rollback.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql56
datadir=/data/3356_back/data  -- 升级前做的旧版本冷备数据
socket=/tmp/mysql.sock
innodb_fast_shutdown=0

数据库版本升级步骤二：数据库服务重新回退启动

[root@xiaoQ-01 local]# /usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/3356/my_rollback.cnf &

# 查看数据库服务启动进程信息
[root@xiaoQ-01 local]# ps -ef|grep mysqld
-- 此时数据库服务启动成功后，只会显示进程信息，不会显示网络端口信息

企业数据库实战练习二：跨小版本回滚

数据库服务官方参考链接：https://dev.mysql.com/doc/refman/5.7/en/downgrading.html

说明：对于数据库8.0版本是不支持小版本间与大版本间的回滚的，只能采取恢复升级前数据方案。

数据库服务升级版本确认⭐⭐⭐⭐⭐

数据库服务8.0之后，利用mysqlshell中的函数，可以实现数据库服务升级的预检查功能

# 现在检测是否可以升级的mysql上创建并授权测试用户
mysql> create user root@'172.16.1.%' identified by 'root123';
mysql> grant all on *.* to root@'172.16.1.%';

# 上传解压8.0版本数据库mysql shell功能程序
[root@db01 local]# tar xf mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz 
[root@db01 local]# ln -s mysql-shell-8.0.26-linux-glibc2.12-x86-64bit mysqlsh

# 确认当前5.7版本信息是否可以升级到相应的8.0版本
[root@db01 local]# cd mysqlsh/bin/
[root@db01 bin]# ll
-rwxr-xr-x 1 7161 31415  7868984 Jun 11  2021 mysql-secret-store-login-path
-rwxr-xr-x 1 7161 31415 33241904 Jun 11  2021 mysqlsh

# 指定连接的旧版服务的用户名与密码，并根据网络白名单列表和端口建立连接，实现测试
[root@db01 bin]# ./mysqlsh root@172.16.1.51 -e "util.checkForServerUpgrade()"
Errors:   0
Warnings: 1
Notices:  1

数据库服务体系结构 ⭐⭐⭐⭐⭐

mysql可以分为Server层和存储引擎层

数据库服务实例

数据库服务实例就是程序运行的一种方式，会占用一定的内存资源和CPU资源，派生出多个线程完成不同的任务需求

MySQL实例 = mysqld(进程) + master thread监控管理 + 具体干活的thread + 预分配的内存结构

简单来说：mysql实例就是占用内存资源的统称，利用mysql实例可以对数据进行处理；

1667103513248

sql语句在MySQL各个功能模块中的执行过程

1680019373792

服务层（Server）：处理用户请求

Server服务层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能；以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

连接器（数据库服务连接器）

连接阶段的权限验证：全局性权限（能否连接到MySQL实例）

连接器负责与客户端建立连接、获取权限、维持和管理连接

mysql -h$ip -P$port -u$user -p

用户密码验证失败错误提示：Access denied for user

一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完成后，只有重新新建的连接才有新的权限

数据库里网络连接问题

数据库连接全部使用长连接，会出现MySQL占用内存涨的特别快

MySQL在执行过程中临时使用的内存是管理在连接对象里面，这些资源在连接断开时才释放

长连接累积下来，可能导致内存占用太大，被系统强行杀掉(OOM)，从现象上看就是MySQL异常重启

解决方案：

# 方案一
    定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
# 方案二
    如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后；
    通过执行 mysql_reset_connection 来重新初始化连接资；
    这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态；

查询数据库服务并发连接数量

mysql> select @@max_connections;

# 临时调整最大并发连接数

mysql> set global max_connections=5；

查询缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。

之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。

key 是查询的语句，value 是查询的结果；

如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空；

MySQL 提供了一种“按需使用”的方式：

可以将参数 query_cache_type 设置成 DEMAND（my.cnf配置文件中配置），这样对于默认的 SQL 语句都不使用查询缓存。

而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；明确使用查询缓存

mysql> select SQL_NO_CACHE * from T where ID=10；明确不使用查询缓存

需要注意的是：MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

分析器

MySQL的分析器负责解析SQL语句的语法和语义，验证语句结构是否正确，并生成对应的解析树供优化器使用

优化器

数据库优化器的作用是分析SQL语句和表结构，从多种可能的执行方案中选择最高效的查询路径（如索引使用、连接顺序等），以最小化资源消耗并最大化查询速度

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器

SQL执行前的权限验证：库/表/列级权限（能否操作具体数据）

数据库执行器的作用是将优化器生成的执行计划转化为实际的操作指令，驱动存储引擎读写数据，并返回查询结果

引擎层（Engine）：逻辑性的负责管理磁盘，实现数据调取和存储

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

不同存储引擎的表数据存取方式不同，支持的功能也不同

结构信息	解释说明
数据存储段概念（segment）	主要由多个区/簇构成
数据存储区/簇概念（extent）	主要由多个页组成，默认大小为：64page（连续的）等价于1M大小
数据存储页概念（page）	主要用于存储数据库表中的数据行信息，默认大小为16kB

解释说明：数据库服务存储的页区段概念

从系统的磁盘管理概念可以得知，磁盘物理组成是由磁头，盘片，存储颗粒，并利用正负极磁化存储颗粒形成正负磁化的存储颗粒；

由于希望能连续的读取和存储数据，随即便有了磁道的概念，为便于划分磁道的区域空间，便有磁盘扇区概念（磁道上的存储空间）；

磁盘出现扇区后，便有了数据存储空间大小的概念；

在使用磁盘时，会进行分区-格式化（inode/block 4kB）-挂载后才能使用，mysql服务会使用挂载后的存储目录；

所以，数据库服务存储数据时，会使用磁盘上block进行数据存储，所以存储page=4个连续的block（8个扇区 512字节）

1667115780477

posted on 2025-08-05 22:48 猿小姜阅读(16) 评论(0) 收藏举报