1.00 课程知识章节说明
目前在互联网的实际应用中，各个企业都会比较关注自身网站的数据信息，既要保证数据信息的安全性，同时也要保证数据存储读取效率
并且在特殊的场景下，还要对存储的数据信息进行检索和分析；因此数据库服务业务已经在各行各业应用非常的广泛
对于互联网领域的技术人员，对于数据库服务知识的掌握，也将是在求职时必备的技能，有些时候还会绝对入职的定级和薪资水平。
1.01 数据库存储服务概述
今时今⽇，数据库系统已经成为各个动态⽹站上 web 应⽤程序的重要组成部分。
由于⾮常敏感和机密的数据有可能保存在数据库中，所以对数据库实施保护就显得尤为重要了。
要从数据库中提取或者存⼊数据，就必须经过连接数据库、发送⼀条合法查询、获取结果、关闭连接等步骤。
⽬前，能完成这⼀系列动作的最常⽤的查询语⾔是结构化查询语⾔ Structured Query Language (SQL)。
并且在对数据库进行管理与维护的过程中，需要对数据库的相关知识进行充分的掌握，最终才能保证企业核心数据的安全性。
1.1.1 数据库知识引入说明
数据信息到底什么？
| 序号 | 识别数据方式 | 数据信息 |
|---|---|---|
| 01 | 人类识别数据 | 账号 密码 图片 视频 数字 文字 特殊符号 地址链接… |
| 02 | 电脑识别数据 | 二进制数值信息 |
数据如何进行存储？
可以利用专业的软件程序，将相关的人类识别的数据，转换为电脑可以识别的二进制信息进行存储
其中一些重要的且核心的数据信息，比如网站中的账号和密码数据信息，需要保证数据存储的安全性，提供更大的存储空间，便于管理；
因此，专门管理企业核心数据信息软件应运而生，数据库管理系统程序（软件-DBMS）
- 最核心数据信息存储：RDBMS-关系型数据库进行存储
- 次核心数据信息存储：NoSQL-非关系型数据库进行存储
- 分布型数据信息存储：DDBMS-分布式数据库进行存储（NewSQL）
常见数据库管理系统程序排名：https://db-engines.com/en/ranking
1.1.2 数据库服务概念介绍
数据库（database DB）,是一种存储数据的仓库，在实际应用过程具有如下特征：
- 数据库是根据数据结构组织、存储和管理数据的；
- 数据库能够长期、高效的管理和存储数据；
- 数据库的目的就是能够存储（写）和提供（读）数据
1.1.3 数据库服务分类说明
数据库分为三个大的类型：(随着互联网发展的产品类型)
- 关系型数据库：（属于数据整合时代）
把复杂的数据结构归结为简单的二元关系（RDBMS），即二维表格形式（二维表）；会注重数据存储的持久性，但存储效率低；
此类型数据库服务，类似于excel表格的存储数据方式，多采用SQL语言方式进行操作管理；
关系型数据库四层结构信息：
| 序号 | 数据库结构 | 描述说明 |
|---|---|---|
| 01 | 数据库管理系统（DBMS） | 进行数据存储应用管理的操作环境或命令 |
| 02 | 数据库（DB） | 数据存储的承载环境 |
| 03 | 数据表（Table） | 数据关系的构建环境 |
| 04 | 数据字段（Field） | 实际数据信息展现形式 |
常见的关系数据库应用程序：
| 序号 | 数据库程序 | 应用说明 |
|---|---|---|
| 01 | MySQL | 互联网公司应用最广泛 |
| 02 | Mariadb | 企业场景应用较少（20%），主要用于教学环境较多 |
| 03 | Oracle | 传统企业和部分国企应用较多，但也逐步被国产数据库替代 |
| 04 | SQLserver | 适合windows server系统环境部署的数据库服务，属于微软公司发布的数据库服务 |
| 05 | PostgreSQL | 适合于海量数据信息存储，对于金融行业数据信息分析能力将强 |
> 说明：关系型数据库的极致追求：数据存储的安全性，但是在某种程度会损失数据存储和读取的性能。
- 非关系型数据库：（属于数据拆分时代）
没有具体模型的数据结构，英文简称NoSQL（Not Only SQL），意为”不仅仅是SQL”，比较注重数据读取的效率；
利用NoSQL数据库主要处理高并发应用的场景，以及海量数据存储应用的场景
常见的非关系数据库应用程序：
| 序号 | 数据库程序 | 应用说明 |
|---|---|---|
| 01 | Redis | 可以利用内存存储数据，也可以采用磁盘存储数据，数据常见展示形式为key-value形式 |
| 02 | Memcache | 可以利用内存存储数据，也可以采用磁盘存储数据，数据常见展示形式为key-value形式 |
| 03 | Mongodb | 属于面向文档数据存储的数据库 |
| 04 | ES | 主要用于做日志数据的收集与检索的数据库（ELK ELFK） |
> 说明：非关系型数据库的极致追求：数据存储的高效性，但是在某种程序会牺牲数据存储的安全性。
- 企业新型数据库：（属于业务整合时代）
属于近些年，由国人研发设计出的数据库服务，可以满足很多国内高并发量网站数据存储和读取业务的需求；
常见的新型数据库应用程序：
| 序号 | 数据库程序 | 应用说明 |
|---|---|---|
| 01 | TiDB | 开源分布式关系型数据库，是一款同时支持在线事务处理与在线分析处理的融合型分布式数据库产品 |
| 02 | OceanBase | 是由蚂蚁集团完全自主研发的国产原生分布式数据库，兼顾分布式架构的扩展性与集中式架构的性能优势 |
| 03 | PolarXDB | 是由阿里巴巴自主研发的云原生分布式数据库，是一款基于云架构理念分布式数据库产品，专注海量数据处理 |
| 04 | RDS/TDSQL | 阿里云/腾讯云平台基于SaaS云计算服务环境构建的数据库产品（PolarXDB TDSQL TiDB） |
数据信息来源参考：https://www.modb.pro/dbRank
数据信息来源参考：https://db-engines.com/en/ranking
数据信息来源参考：https://www.itdks.com/
1.1.4 数据库服务版本应用
根据本课程安排，主要会介绍讲解关系型数据库中的 MySQL 数据库服务，也是业界比较知名和流行的开源数据库产品；
因此，需要关注下MySQL软件程序的版本应用（产品线），以及同类型相关的数据库服务产品。
| 序号 | 厂商信息 | 数据库服务 |
|---|---|---|
| 01 | Oracle（官方） | Oracle/ MySQL（5.6 5.7* 8.0） |
| 02 | MariaDB | MariaDB Server（从5.5+之后，10.0+版本有自己的一些架构特性） |
| 03 | Percona | Percona Server |
MySQL企业应用的版本分析：
| 序号 | 大版本应用 | 小版本应用 |
|---|---|---|
| 01 | MySQL 5.6 | 5.6.36 5.6.38 5.6.40 5.6.46 （GA 6-12月）<br>2020 10月 常规/扩展服务全部停止 |
| 02 | MySQL 5.7 （应用更加广泛） | 5.7.20 5.7.22 5.7.24 5.7.26 ..5.7.40（GA最新双数版本）<br/>2021 01月 常规服务停止<br>2022 12月 常规/扩展服务全部停止 |
| 03 | MySQL 8.0 （属于最新版本） | 8.0.11 8.0.17+ 8.0.18 8.0.26 8.0.32 （GA最新双数版本） |
MySQL企业应用的发布版本：
- C：表示为社区版本，属于开源免费版本
- E：表示为企业版本，属于开源盈利版本
1.1.5 数据库服务优势特点
- MySQL数据库服务性能卓越，服务稳定，很少出现异常宕机的情况；
- MySQL数据库服务是开放源代码且无版权制约，自主性强，且使用成本低；
- MySQL数据库服务使用历史悠久，社区及用户非常活跃，遇到问题可以获取大量帮助；
- MySQL数据库服务软件体积小，安装使用简单，并且易于维护管理，安装及维护成本低；
- MySQL数据库服务业界口碑好，使得企业无需考虑就能直接使用；
- MySQL数据库服务架构应用广泛，可以用于构建LAMP LNMP LNMT等流行web架构；
- MySQL数据库服务支持多种操作系统，提供多种API接口，支持多种开发语言利用驱动接口调用；
1.02 数据库服务安装部署
1.2.1 数据库服务程序下载
利用官方网址获取数据库软件程序：https://www.mysql.com/
选择官网网站的 DOWNLOADS 进行数据库软件程序下载选择；
在数据库官方下载页面中，选择 MySQL Community (GPL) Downloads 链接，进行数据库程序社区版下载；
进入数据库程序的社区版下载页面后，会看到社区版数据库服务的多种安装场景，本课程选择MySQL Community Server方式安装
官方最新版下载页面：
官方历史版下载页面：（本次课程讲解选择历史相对稳定版本进行安装部署）
进入到社区版服务器安装场景界面，需要选择数据库服务的安装部署环境和需要下载的程序具体版本信息；
查看Linux系统的glibc运行使用的C语言库版本信息：
[root@master ~]# getconf GNU_LIBC_VERSION
glibc 2.17
[root@master ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。
1.2.2 数据库服务安装方式
- 不同系统环境数据库服务安装方式-Linux
在Linux系统中常见的部署安装方式：
| 序号 | 安装方式 | 特征说明 |
|---|---|---|
| 01 | 采用二进制方式安装 | 直接解压缩二进制程序包，进行简单的配置和初始化操作即可 |
| 02 | 采用rpm包方式安装 | 需要从官方网站下载rpm程序包，并且需要考虑系统环境的兼容性，解决软件程序包依赖 |
| 03 | 采用yum包方式安装 | 需要根据官方网站说明，配置yum下载的仓库源信息，在联网情况下进行安装部署 |
| 04 | 采用源码包方式安装 | 需要从官方网站下载源码程序包，并且需要解决程序包依赖问题，以及需要采用编译安装 |
> 说明：本次课程主要围绕数据库服务的应用和管理进行讲解，所以选择二进制包安装方式，使安装过程更加简单和规范。
部署安装MySQL数据库服务程序：
# 确认操作系统数据库服务安装环境
[root@xiaoQ-01 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
-- 查看操作系统的版本信息
[root@xiaoQ-01 ~]# ip address show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:96:c4:f5 brd ff:ff:ff:ff:ff:ff
inet 192.168.30.101/24 brd 192.168.30.255 scope global noprefixroute eth0
-- 查看操作系统的网卡地址
[root@xiaoQ-01 ~]# cat /etc/hosts
...省略部分信息...
192.168.30.101 xiaoQ-01
-- 查看系统本地域名解析信息
[root@xiaoQ-01 ~]# systemctl is-active firewalld
unknown
[root@xiaoQ-01 ~]# systemctl is-enabled firewalld
disabled
-- 查看防火墙服务是否关闭
[root@xiaoQ-01 ~]# getenforce
Disabled
-- 查看selinux安全策略是否关闭
[root@xiaoQ-01 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@xiaoQ-01 ~]# yum remove -y mariadb-libs
-- 清楚系统自带的mariadb数据库服务相关的程序包
[root@xiaoQ-01 ~]# yum install -y libaio-devel
-- 安装数据库服务程序所需的依赖软件包
[root@xiaoQ-01 ~]# ln -s /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5
或者
[root@xiaoQ-01 ~]# yum install ncurses-compat-libs
-- 需要修改链接库信息（只有centos 8 系统才需要进行操作修改）
# 数据库服务程序详细安装过程
[root@xiaoQ-01 ~]# cd /usr/local/
[root@xiaoQ-01 local]# rz -y
-- 上传数据库程序软件包
[root@xiaoQ-01 local]# tar xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
-- 解压数据库服务软件程序压缩包
[root@xiaoQ-01 local]# ln -s mysql-8.0.26-linux-glibc2.12-x86_64 mysql
-- 创建数据库服务程序目录的软链接
[root@xiaoQ-01 local]# tail -1 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
[root@xiaoQ-01 local]# source /etc/profile
-- 配置数据库服务程序环境变量
[root@xiaoQ-01 local]# mysql -V
mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
-- 可以获取数据库服务版本信息表示环境变量配置生效
[root@xiaoQ-01 local]# useradd mysql
-- 创建数据库服务管理用户信息
[root@xiaoQ-01 local]# mkdir -p /data/3306/data /data/3306/logs
[root@xiaoQ-01 local]# chown -R mysql. /data/
-- 创建数据库服务相关目录并进行目录信息进行授权
> 说明：以上操作步骤只是保证了数据库服务的软件程序部分安装完毕，在系统中可以识别数据库程序。
进行初始MySQL数据库服务操作：（8.0版本数据库）
数据库初始化命令应用解读：
| 核心程序命令 | 初始化核心参数 | 数据管理用户 | 数据存储路径 | 数据存放目录 |
|---|---|---|---|---|
| mysqld | –initialize-insecure | –user=mysql | –basedir=/usr/local/mysql | –datadir=/data/3306/data |
数据库初始化命令实践操作：
# 数据库服务初始化操作过程
[root@xiaoQ-01 local]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
2022-10-11T16:33:20.057586Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 33820
2022-10-11T16:33:20.099560Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-11T16:33:20.490688Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-11T16:33:21.202672Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-10-11T16:33:21.203068Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-10-11T16:33:21.292047Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
-- 数据库初始化过程，就是将数据库默认的系统数据信息创建出来，以及根据配置文件激活数据库的特定功能；
# 数据库服务初始化经典报错信息
报错情况-01：
[报错信息]：
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
[解决方法]：
yum install -y libaio-devel
报错情况-02：
[报错信息]：
initialize specified but the data directory has files in it. Aborting.
The designated data directory /data/3306/data/ is unusable. You can remove all files that the server added to it.
[解决方法]：
rm -rf /data/3306/data/*
> 说明：数据库服务初始化的过程，是用于让数据库服务创建出一些默认的数据信息，以及识别存储数据目录信息
编写生成MySQL数据库配置文件：
[root@xiaoQ-01 ~]# cat > /etc/my.cnf <<eof
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock eof — 创建数据库服务运行需要加载的my.cnf配置模板文件
启动运行MySQL数据库服务脚本：
# 查看数据库服务默认启动脚本文件
[root@xiaoQ-01 ~]# ls -l /usr/local/mysql/support-files/mysql.server
-rwxr-xr-x 1 7161 31415 10576 7月 1 2021 /usr/local/mysql/support-files/mysql.server
-- 数据库服务默认的启动脚本文件
# 设置数据库服务程序启动运行文件
[root@xiaoQ-01 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# 启动数据库服务程序利用脚本文件
[root@xiaoQ-01 ~]# /etc/init.d/mysqld start
[root@xiaoQ-01 ~]# service mysqld start
Starting MySQL.Logging to '/data/3306/data/xiaoQ-01.edu.err'.
SUCCESS!
[root@xiaoQ-01 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
-- 启动和停止数据库服务均成功
[root@xiaoQ-01 ~]# chkconfig --add mysqld
-- 设置数据库服务开机自动运行
# 启动数据库服务程序利用脚本文件 (systemd方式)
[root@xiaoQ-01 ~]# systemctl enable mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on
-- 将service管理服务方式转换为systemd管理服务方式
[root@xiaoQ-01 ~]# systemctl start mysqld
[root@xiaoQ-01 ~]# systemctl is-active mysqld
active
# 检查确认数据库服务是否启动成功
[root@xiaoQ-01 ~]# netstat -lntup|grep 3306
tcp6 0 0 :::33060 :::* LISTEN 67809/mysqld
tcp6 0 0 :::3306 :::* LISTEN 67809/mysqld
[root@xiaoQ-01 ~]# ps -ef|grep mysql
root 67663 1 0 01:04 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/xiaoQ-01.edu.pid
mysql 67809 67663 0 01:04 pts/0 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xiaoQ-01.edu.err --pid-file=/data/3306/data/xiaoQ-01.edu.pid --socket=/tmp/mysql.sock
特殊说明：在启动mysql数据库服务时，可能看到33060端口信息，此端口信息主要实现mysqlx协议的通讯过程；
利用mysqlx协议可以实现利用mysql-shell功能组件，对数据进行key-value操作，即识别json文件信息，进行远程管理；
> Port 3306 is the default port for the MySQL Protocol, which is used by the mysql client, MySQL Connectors, and utilities such as mysqldump and mysqlpump. > > Port 33060 is the default port for the MySQL Database Extended Interface (the MySQL X Protocol). > > The X Plugin can be disabled at startup by either setting mysqlx=0 in your MySQL configuration file, or by passing in either –mysqlx=0 or –skip-mysqlx when starting the MySQL server. > > Alternatively, use the -DWITH_MYSQLX=OFF CMake option to compile MySQL Server without X Plugin. > 参考链接：https://blog.csdn.net/michaelwoshi/article/details/107955539
登录管理MySQL数据库服务程序
[root@xiaoQ-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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数据库部署参考链接：https://www.cnblogs.com/oldboy666/p/15559963.html
不同数据库服务版本安装初始化差异说明：
| 版本信息 | 命令信息 | 初始化操作参数 | 备注说明 |
|---|---|---|---|
| 5.6 | mysql_install_db | 初始化的命令不一致，参数相同（无管理员密码） | |
| 5.7 8.0 | mysqld | –initialize-insecure | 表示不安全方式初始化（无管理员密码） |
| –initialize | 表示安全方式初始化（有随机默认管理员密码） |
> 安全方式初始化特性： > > 将会生成临时密码（12位密码 需要满足4种复杂度），必须在首次登录数据库时改掉，才能正常管理数据库；
使用安全模式初始化数据库后，需要利用临时密码登录数据库服务，并设置管理员用户密码信息
mysql > alter user root@'localhost' identified by '123456';
-- 如果不修改设置管理员用户密码，使用临时密码只是能登录数据库，但是无法管理数据库
- 不同系统环境数据库服务安装方式-windows
数据库服务软件程序下载：
MySQL软件下载地址：https://dev.mysql.com/downloads/mysql/
程序包下载好后，进行傻瓜式安装部署即可。
数据库服务环境变量配置：
# 在PATH信息后添加以下信息：
程序所在路径信息\bin
-- 设置数据库服务中bin目录路径信息
1.2.3 数据库服务启动关闭
在企业实际环境中，数据库服务的运行状态一般是不会进行随意调整的，在特殊场景下需要提前审批后，才能进行调整；
在企业实际环境中，在进行数据库服务关闭前，可以将业务先切换到备库（从库），再停止原有主库服务；
数据服务运行启动的进程信息查看：
[root@xiaoQ-01 ~]# ps -ef|grep mysqld
root 6352 1 0 00:01 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/xiaoQ-01.edu.pid
-- 利用mysqld_safe脚本信息，可以满足定制修改的需求，设置一些灵活的变量信息，支持一些高级的启动功能
mysql 6505 6352 17 00:01 pts/1 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xiaoQ-01.edu.err --pid-file=/data/3306/data/xiaoQ-01.edu.pid --socket=/tmp/mysql.sock
-- 利用mysqld命令可以启动数据库服务，并显示启动的命令进程信息，但不能负责完成数据库服务的停止和重启操作
-- 利用mysqld命令可以启动数据库服务，有些服务功能是锁定死的，不能编辑改动（比如日志功能）
-- 利用mysqld_safe命令启动数据库服务，可以进行特殊功能信息配置，并且可以实现数据库维护性操作，还可以设置启停重启；
> 说明：通过以上进程信息，可以看出数据库服务可以更灵活的启动，使用mysqld和mysqld_safe命令来完成，主要用于维护操作。
数据库服务运行启动与关闭操作逻辑：（ps -ef|grep mysqld –> bin/mysqld ）
数据库服务除了应用脚本文件或命令进行关闭数据库服务之外，还可以进入到数据库服务中进行关闭服务：
mysql> shutdown;
或者
mysqladmin -uroot -p123456 shutdown
> 说明：当利用脚本或命令无法正常关闭服务时，也可以使用以上操作方式关闭数据库服务，但不建议利用kill -9方式杀进程停数据库
数据库服务启动和关闭方式总结：
| 序号 | 启动方式 | 关闭方式 |
|---|---|---|
| 01 | mysqld & | kill/pkill/killall (不建议) kill -9 禁用 |
| 02 | mysqld_safe & | kill/pkill/killall (不建议) |
| 03 | mysql.server start | mysql.server stop |
| 04 | service mysqld start (建议) | service mysqld stop (建议) |
| 05 | systemctl start mysqld | systemctl stop mysqld |
| 06 | > shutdown | |
| 07 | mysqladmin -uroot -poldboy123 shutdown |
1.2.4 数据库服务密码设置
给mysql配置登陆密码，并使用新密码进行登录数据库
# 设置root密码:
[root@oldboy ~]# mysqladmin password 'oldboy123'
或者
[root@oldboy ~]# mysqladmin -uroot -p password 'oldboy123'
-- 表示第一次登录设置密码信息
# 进行登录测试：
[root@oldboy ~]# mysql -uroot -p
Enter password:
mysql> quit
给mysql修改登陆密码，并使用新密码进行登录数据库
# 利用数据库服务命令修改密码信息
[root@oldboy ~]# mysqladmin -uroot -poldboy123 password 'oldboy'
-- 将原有oldboy123密码 改为oldboy
# 进行测试登录
[root@oldboy ~]# mysql -uroot -poldboy
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
# 利用数据库服务SQL语句修改密码信息
mysql> alter user root@'localhost' identified by 'oldboy123';
mysql> flush privileges;
-- 适合于mysql 8.0
mysql> update mysql.user set authentication_string=PASSWORD('oldboy123') where user='root' and host='localhost';
mysql> flush privileges;
-- 适合于mysql 5.7
mysql> set password for 'oldboy'@'localhost'=PASSWORD('oldboy123');
mysql> flush privileges;
-- 适合于mysql 5.6
# 进行测试登录
[root@oldboy ~]# mysql -uroot -poldboy123
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
1.03 数据库服务基础管理
1.3.1 数据库服务用户管理
在数据库服务中，也是有用户知识概念的，类似系统中的用户管理应用，主要利用用户管理知识，可以实现：
- 用户登录数据库服务系统中（实现数据库服务登录）
- 用户管理数据库服务中对象（实现数据库对象管理）
01 创建数据库服务用户信息
# 数据库用户创建：'用户名称信息'@'白名单信息'
-- 用户名称信息：创建的用户字符串信息
-- 白名单信息：网段IP地址信息或者名称信息
用户信息构成：用户名称@主机名称/主机地址范围
主机范围就是允许哪些机器连接。连接的机器的iP地址，必须再允许的主机范围，否则不允许连接
主机名称或主机地址范围表示可以以什么样的方式，来使用数据库管理用户登录数据库系统；
常见的有两种方式：
- 第一种方式：以本地登录方式进行登录数据库系统；
- 第二种方式：以远程登录方式进行登录数据库系统；
进入到数据库环境中，执行以下SQL语句进行创建：
MySQL> create user blog@'192.168.56.%' identified by 'oldboy123';
> 说明：使用create创建数据库服务用户信息，创建的用户默认是没有数据库的管理权限的。
在进行数据库服务对应管理用户创始时，建议一个数据库对应一个管理人员，以免多个管理员操作管理相同数据库产生冲突；
在进行用户信息创建时，具有多种用户主机域的设置方法：
方法一：设置数据服务管理用户只能本地登录数据库系统，主机域设置方法：
root@localhost
-- localhost：表示只能通过本地主机登录数据库系统
方法二：设置数据服务管理用户可以远程登录数据库系统，主机域设置方法：
root@192.168.30.101
-- 192.168.30.101：表示可以在192.168.30.101主机上远程登录管理数据库系统
在企业实际网站架构中，会有大量的集群服务器，大量的应用服务器都会连接后端的数据库服务，因此设置远程连接主机域方法有：
- 方法一：授权单个主机进行访问：
root@192.168.30.101
- 方法二：授权一个网段进行访问：
# 方式一：书写方法（企业常用）
root@'192.168.30.0/24'
root@'192.168.30.0/255.255.255.0'
-- 授权一个网段主机均可远程登录数据库服务，表示这个网段中192.168.30.1~192.168.30.254均可访问
-- 在数据库中识别默认掩码就是/24，其他掩码信息需要具体写明，需要注意特殊VLSM子网掩码需要长格式指明，不能简写***
# 方式二：书写方法（企业常用）
root@'192.168.30.%'
-- 授权一个网段主机均可远程登录数据库服务，表示这个网段中192.168.30.1~192.168.30.254均可访问
# 方式三：书写方法
root@'172.16.%'
-- 授权一个更大网段主机均可远程登录数据库服务，表示这个网段中192.168.0.1~192.168.255.254均可访问
# 方式四：书写方法
root@'%'
-- 授权任意主机都可以远程登录数据库服务
# 方式五：书写方法
root@'192.168.30.51' 1-254
-- 授权一个具体的主机可以远程登录数据库服务，表示这个主机192.168.30.51可以访问
root@'192.168.30.5%'
-- 授权多个具体的主机可以远程登录数据库服务，表示多个主机192.168.30.50~192.168.30.59均可以访问
02 查询数据库服务用户信息
进行到数据库环境中，执行以下SQL语句进行查询：
MySQL> select user,host,authentication_string from mysql.user;
查看当前登录系统的用户信息：
MySQL> select user(); -- whoami
+--------------------+
| user() |
+--------------------+
| root@localhost |
+--------------------+
1 row in set (0.00 sec)
03 修改数据库服务用户密码
进入到数据库环境中，执行以下SQL语句进行修改；
MySQL> alter user blog@'192.168.56.%' identified by '123456';
04 删除数据库服务用户信息
进入到数据库环境中，执行以下SQL语句进行删除：（在生产环境慎用，不用的用户信息可以先锁定）
MySQL> drop user 'user'@'主机域'
# 扩展：锁定用户信息
MySQL> ALTER USER 'demo'@'localhost' ACCOUNT LOCK
测试练习：删除以下用户信息
''@'localhost'
''@'xiaoq'
'root'@'xiaoq'
实现最终只保留两个管理员用户：
MariaDB> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1|
| root | localhost|
+------+-----------+
05 锁定数据库服务用户信息
在数据库服务进行用户管理时，也可以将特定数据库管理用户进行锁定，从而实现禁止某些特定用户再次登录数据库服务进行管理；
锁定数据库用户操作：
# 进行数据库用户锁定操作
mysql> alter user oldboy@'localhost' account lock;
Query OK, 0 rows affected (0.02 sec)
# 查看数据库用户锁定状态
mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+----------------------+
| user | host | account_locked |
+------------------+-----------+----------------------+
| oldboy | localhost | Y |
| root | localhost | N |
+------------------+-----------+----------------------+
5 rows in set (0.00 sec)
[root@docker-server ~]# mysql -uoldboy -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'oldboy'@'localhost'. Account is locked.
-- 再次登录数据库服务，显示指定用户已经被锁定
锁定数据库用户操作：
# 进行数据库用户锁定操作
mysql> alter user oldboy@'localhost' account unlock;
Query OK, 0 rows affected (0.01 sec)
# 查看数据库用户锁定状态
mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+---------------------+
| user | host | account_locked |
+------------------+-----------+---------------------+
| oldboy | localhost | N |
| root | localhost | N |
+------------------+-----------+---------------------+
5 rows in set (0.00 sec)
[root@docker-server ~]# mysql -uoldboy -p
Enter password:
Welcome to the MySQL monitor.
-- 再次登录数据库服务，显示指定用户已经被解锁可以登录
06 数据库服务用户管理特性（5.7与8.0用户管理区别）
用户管理区别-01：创建用户授权区别
之前版本，只需要grant命令即可完成用户创建操作+用户密码设置+用户授权操作
mysql> grant all on *.* to xiaoQ@'192.168.30.%' identified by 'oldboy123';
之后版本，所有系统表都是InnoDB管理了，需要先建立用户再进行授权，并且grant命令不再支持indentified by语句
mysql> create user xiaoQ@'192.168.30.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to xiaoQ@'192.168.30.%';
Query OK, 0 rows affected (0.00 sec)
用户管理区别-02：用户密码插件区别
之前版本，用户密码加密插件使用的早期版本时，可以保证客户端程序正常登陆，保证主从架构数据正常，保证集群通讯不受到影响；
# 查看早期数据库服务版本的密码加密插件信息
mysql> show variables like '%auth%';
+-------------------------------+-------------------------------------------+
| Variable_name | Value |
+-------------------------------+-------------------------------------------+
| default_authentication_plugin | mysql_native_password |
| secure_auth | ON |
+-------------------------------+-------------------------------------------+
2 rows in set (0.00 sec)
-- 早期数据库服务的密码加密插件为：mysql_native_password
之后版本，用户密码加密插件使用的后期版本时，将会导致客户端程序无法登录，导致主从架构数据正常，影响集群正常通讯过程；
# 查看后期数据库服务版本的密码加密插件信息
mysql> show variables like '%auth%';
+-------------------------------+----------------------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+----------------------------------------+
1 row in set (0.01 sec)
-- 后期数据库服务的密码加密插件为：caching_sha2_password
为了解决数据库服务升级后，用户密码加密插件影响连接建立问题，可以采取以下两种方案
- 方案一：替换原有默认密码加密插件，更换为历史版本使用的加密插件（mysql_native_passwordl）
运维职责 - 方案二：替换客户端连接数据库服务端的驱动程序软件，使之兼容新版本加密插件功能
开发职责
在新版数据库中替换原有密码加密插件具体操作方法：
方法一：创建用户过程修改加密插件信息
mysql> create user oldboy@'localhost' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+--------------+-----------------------------------------------------------------+-----------------------------+
| user | host | authentication_string | plugin
+------------------+--------------+-----------------------------------------------------------------+-----------------------------+
| oldboy | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password
+------------------+--------------+-----------------------------------------------------------------+-----------------------------+
6 rows in set (0.00 sec)
方法二：修改已经创建用户加密插件信息
mysql> alter user 'xiaoQ'@'192.168.30.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,authentication_string,plugin from mysql.user;
+-----------+-----------------+----------------------------------------------------------------+-------------------------------+
| user | host | authentication_string | plugin |
+----------+------------------+----------------------------------------------------------------+-------------------------------+
| xiaoQ | 192.168.30.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
方法三：修改数据库服务配置文件信息（全局修改）
# 修改数据库服务配置文件信息
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password — 修改数据库服务配置文件，并在mysqld中区域进行修改 # 修改数据库服务配置后重启服务程序 [root@xiaoQ-01 ~]# service mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL. SUCCESS! # 登录数据库服务查看加密插件默认配置： mysql> show variables like '%auth%'; +—————————————-+——————————+ | Variable_name | Value | +—————————————-+——————————+ | default_authentication_plugin | mysql_native_password | +—————————————-+——————————+ 1 row in set (0.01 sec)
企业面试实战案例分享：若数据库服务本地管理员密码遗忘如何处理
解决处理方法：将数据库重启并进入数据库安全模式（不加载验证授权表模式）
停止数据库服务，并检查数据库服务是否停止：
mysql> shutdown;
或者
service mysql stop
-- 在系统命令行关闭
重新启动数据库服务，并设置启动方式为忽略授权表启动方式：
[root@oldboy ~]# mysqld_safe --skip-grant-tables --skip-networking &
-- skip-grant-tables 表示忽略授权表启动
-- skip-networking 忽略网络通讯方式启动
# 进行测试服务是否启动
[root@oldboy ~]# ps -ef|grep mysql
采用免密码方式登录数据库系统中，并重置root用户密码信息：
[root@oldboy ~]# mysql -uroot
mysql> flush privileges;
--表示先将数据库服务授权表加载到内存中，也可以将内存中修改的授权信息存入到磁盘中
mysql> alter user root@'localhost' identified by 'oldboy123';
--修改数据库服务用户密码信息
重新启动数据库服务程序：
# 杀死已经运行的数据库服务进程信息
[root@oldboy ~]# pkill mysqld
[root@oldboy ~]# ps -ef|grep mysql
# 重新启动数据库服务
[root@oldboy ~]# systemctl start mysqld
[root@oldboy ~]# netstat -lntup|grep 3306
# 重新登录进行测试
[root@oldboy ~]# mysql -uroot -poldboy123
1.3.2 数据库服务权限管理
在数据库服务中，权限管理可以用于针对不同的用户，限制登录管理数据库服务用户的操作行为，也可以区分不同用户的操作能力；
> 思考：系统权限和数据库权限作用在哪？ > > 系统权限是作用于文件上的； > > 数据库权限是作用在用户上的，或者角色（相当于系统的用户组）上的；
数据库服务常见的权限管理定义设置：（采用具有的字符参数信息进行定义,并且权限是赋予数据库管理用户的）
数据库服务中可以授权的所有权限信息查看：
MySQL> show privileges;
输出展示的信息表示内容：
| 序号 | 输出列 | 解释说明 |
|---|---|---|
| 01 | Privilege | 表示可以授权的所有权限信息展示 |
| 02 | Context | 表示设置的权限可以对数据库服务哪些资源进行操作 |
| 03 | Comment | 对相应的权限功能进行简单解释说明 |
核心重要授权权限总结：
| 序号 | 权限 | 授权资源 | 解释说明 |
|---|---|---|---|
| 01 | Select | Tables | 可以对表进行操作，查询表中数据信息 |
| 02 | Insert | Tables | 可以对表进行操作，插入表中数据信息 |
| 03 | Update | Tables | 可以对表进行操作，修改表中数据信息 |
| 04 | Delete | Tables | 可以对表进行操作，删除表中数据信息 |
| 05 | Alter | Tables | 可以对表进行操作，修改表中结构信息（元数据-属性） |
| 06 | Index | Tables | 可以对表进行操作，删除或创建表中的索引信息 |
| 07 | Create | Databases,Tables | 可以对表和库进行操作，用于创建数据库和表信息 |
| 08 | Drop | Databases,Tables | 可以对表和库进行操作，用于删除数据库和表信息 |
> 说明：all privileges（all）包含查看的所有权限信息，但是唯独缺了Grant option，不能授权用户，此权限只能给root@local用户
数据库服务中用户授权信息的存放位置：授权表 oldboy@% — select update
# 切换进入到默认mysql数据库中
mysql> use mysql;
# 查看数据库中的信息
mysql> show tables；
-- 查看mysql数据库中的表信息
常见的授权表信息说明：
| 序号 | 授权表名称 | 解释说明 |
|---|---|---|
| 01 | user | 主要用于存储用户、主机域、密码、加密插件等信息，同时还存储了*.*级别的权限(全局权限) |
| 02 | db | 主要用于存储用户、主机域等信息，同时还存储了单库级别的权限(局部权限) |
| 03 | tables_priv | 主要用于存储用户、主机域等信息，同时还存储了单表级别的权限(局部权限) |
> 说明：以上授权表信息了解作用即可，不建议采用手工方式对授权表中的数据进行修改调整
01 设置数据库服务用户授权
通过理解数据库内部结构，可以知晓，数据库是一个很大的仓库，里面包含多个库，每个库中有多个表，每个表中有多个行和列；
当然作为数据库服务管理用户，要管理的不仅仅是库和表，其实还有视图、触发器、存储过程等和数据库有关的信息；
因此，在某些时候创建完用户信息，还需要对创建的用户进行授权，就是让不同用户拥有管理库和表对象的权限。
进入到数据库环境中，执行以下SQL语句进行设置：
# 授权语法格式
MySQL> grant all on 授权对象范围 to 权限赋予用户信息
MySQL> grant all on * . * to blog@'192.168.30.%';
授权 权限 库信息 表信息 用户 主机域
# 对数据库和表同时进行授权方法
MySQL> grant all on *.*
-- 表示对所有的数据库和所有的表进行授权
MySQL> grant all on oldboy.*
-- 表示对指定的数据库中所有的表进行授权
MySQL> grant all on oldboy.t1
-- 表示对指定的数据库中指定的表进行授权
具体授权权限操作步骤：
MySQL> grant all on * . * to oldgirl@'192.168.30.%';
MySQL> grant Insert,Delete,Update,Select on * . * to oldboy@'192.168.30.%';
# 创建远程特权管理用户（练习）
MySQL > create user root@'10.0.0.%' identified by '123456';
MySQL > grant all on *.* to root@'10.0.0.%'
具体回收权限操作步骤：
mysql> grant insert,update,delete,select on * . * to oldbaby@'192.168.30.%';
Query OK, 0 rows affected (0.00 sec)
-- 数据库服务用户授权操作
mysql> show grants for oldbaby@'192.168.30.%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for oldbaby@192.168.30.% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `oldbaby`@`192.168.30.%` |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 数据库服务用户权限授权成功
mysql> revoke delete on *.* from oldbaby@'192.168.30.%';
Query OK, 0 rows affected (0.00 sec)
-- 数据库服务用户回收权限
mysql> show grants for oldbaby@'192.168.30.%';
+-----------------------------------------------------------------+
| Grants for oldbaby@192.168.30.% |
+-----------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `oldbaby`@`192.168.30.%` |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
-- 数据库服务用户权限回收成功
02 查看数据库服务用户权限
当用户授权操作完毕后，可以对授权的用户进行查看确认：
mysql> show grants for xiaoQ@'192.168.30.%';
授权操作实践过程：
# 数据库用户权限设置实践
[root@oldboy ~]# mysql -uroot -poldboy123
MySQL> grant all on *.* to blog@'192.168.30.%';
-- 授权blog作为管理员，但是不具有创建用户权利
MySQL> flush privileges;
-- blog@'192.168.30.%' 权限，就相当于管理员，但是缺少一个创建用户权限
# 数据库用户权限设置查看
MySQL> show grants for blog@'192.168.30.%';
--再查看发现权限变化ALL PRIVILEGES，重新登录数据库可以对数据库操作。
企业生产情况授权思路：
企业生产库表授权一定要更小，最小的授权最大的安全，给业务用户常规授权，包含对内容的增删改查；
| 序号 | 权限 | 互联网应用 |
|---|---|---|
| 01 | SELECT 查询数据 | 访问网站页面时，表示浏览查看数据信息； |
| 02 | INSERT 插入数据 | 访问网站页面时，表示发表文章数据信息； |
| 03 | UPDATE 修改数据 | 访问网站页面时。表示修改文章数据信息； |
| 04 | DELETE 删除数据 | 访问网站页面时，表示删除文章数据信息； |
# 生产常规授权实践
MySQL> create database bbs;
-- 创建数据库操作
MySQL> create user bbs@'192.168.56.%' identified by '123';
-- 创建管理数据库用户
MySQL> grant insert,select,update,delete on bbs.* to bbs@'192.168.56.%';
-- 进行管理用户授权
# 授权等价于root管理用户
MySQL> grant all on *.* to oldboy@'localhost' identified by 'oldboy123' with grant option;
MySQL> flush privileges;
03 数据库服务权限管理特性（5.7与8.0权限管理区别）
之后版本，出现了数据库服务中对用户的role支持，表示可以将企业中常用的权限汇总打包为一个特定的role信息，在赋予给指定用户；
具体操作演示过程：
# 指定用户操作特定数据库时，具有共性相同的权限：分为运维权限（rw） 分为开发权限（ro）
mysql>help create role;
-- 查看role创建的方法
# 创建指定的role信息
CREATE ROLE 'admin', 'developer';
CREATE ROLE 'webapp'@'localhost';
-- 创建role信息的语句结构信息，不加@信息表示角色白名单为%方式，实际还要根据具体用户定义
mysql> create role oldboy_rw,oldboy_r;
-- 创建两个不同的role信息
-- oldboy_rw: oldboy.* rw(select,update,insert,delete)
-- oldboy_r: oldboy.* r(select)
# 进行role权限信息设定
mysql> grant select on oldboy.* to oldboy_r;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,update,insert,delete on oldboy.* to oldboy_rw;
Query OK, 0 rows affected (0.00 sec)
# 将指定role信息与相应的用户信息进行捆绑
mysql> create user user01@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create user user02@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
-- 创建需要捆绑role权限的用户信息
mysql> grant oldboy_r to user01@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant oldboy_rw to user02@'%';
Query OK, 0 rows affected (0.00 sec)
-- 实现不同的role信息与不同的用户进行捆绑
# 进行角色信息激活
# 方式一：手动激活角色
mysql> SET DEFAULT ROLE ALL TO 'lei_test'@'localhost';
mysql> set default role all to user01@'%';
mysql> set default role all to user02@'%';
-- 利用数据库管理员账号手工激活角色
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `oldboy_r`@`%` |
+----------------+
1 row in set (0.00 sec)
-- user01登录数据库，会显示对应激活的角色信息
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `oldboy_rw`@`%` |
+----------------+
1 row in set (0.00 sec)
-- user02登录数据库，会显示对应激活的角色信息
#方式二：实现自动激活用户角色信息
mysql > set global activate_all_roles_on_login=on;
[root@db-01 ~]# mysql -uuser01 -p123 -h10.0.0.51
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `oldboy_r`@`%` |
+----------------+
1 row in set (0.00 sec)
-- user01登录数据库，会显示对应激活的角色信息
[root@db-01 ~]# mysql -uuser02 -p123 -h10.0.0.51
mysql> select current_role();
+-----------------+
| current_role() |
+-----------------+
| `oldboy_rw`@`%` |
+-----------------+
1 row in set (0.01 sec)
-- user02登录数据库，会显示对应激活的角色信息
# 进行用户操作验证
mysql> select * from oldboy.t1;
+----------+
| name |
+----------+
| zhangsan |
| lisi |
+----------+
2 rows in set (0.04 sec)
mysql> insert into oldboy.t1 values ('xiaoQ');
ERROR 1142 (42000): INSERT command denied to user 'user01'@'10.0.0.51' for table 't1'
-- user01只读用户登录数据库，只能查看操作，不能进行其他操作
mysql> select * from oldboy.t1;
+--------+
| name |
+--------+
| oldboy |
| lisi |
+--------+
2 rows in set (0.00 sec)
mysql> insert into oldboy.t1 values ('xiaoQ');
-- user02读写用户登录数据库，可以进行其他操作任务
1.3.3 数据库服务连接管理
当数据服务中用户创建设置完毕后，就可以实现利用指定用户身份连接登录管理数据服务，具体连接操作如下：
- 执行命令操作连接登录数据库服务：（内置客户端工具连接）
# 本机连接数据库服务：
[root@oldboy ~]# mysql -ublog -poldboy123
[root@oldboy ~]# mysql -uroot -poldboy123
-- 以上连接方式等价于，以下长格式命令登录方式
[root@oldboy ~]# mysql -uroot -poldboy123 -hlocalhost -P3306 -S /var/lib/mysql/mysql.sock
--- 进行本地连接时，hlocalhost -P3306 -S /var/lib/mysql/mysql.sock可以省略
# 远程连接数据库服务：
[root@oldboy ~]# mysql -uroot -poldboy123 -h192.168.30.101 -P3306 -S /var/lib/mysql/mysql.sock
数据库服务连接方式特殊说明：
在对数据库服务进行连接管理操作时，是可以基于两种网络连接方式：
方式一：利用socket建立远程连接（unix 套接字文件连接）
利用此方法建立数据库服务的连接，只能用于建立本地数据库服务的连接；
# 套接字文件信息定义：
[root@xiaoQ-01 ~]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock — 指定数据库服务加载的套接字文件路径信息
[mysqld]
socket=/tmp/mysql.sock — 指定数据库服务加载的套接字文件路径信息 # 利用套接字文件建立数据库服务连接 [root@oldboy ~]# mysql -uroot -poldboy123 -S /tmp/mysql.sock — -S指定socket的信息可以省略，因为在数据库服务配置文件的客户端已经配置过了
> 说明：需要在数据库服务中授权localhost用户，才能实现采用socket方式登录操控数据库服务
方式二：利用TCP/IP建立远程连接（网络地址与端口）
利用此方法建立数据库服务的连接，可以用于建立远程数据库服务的连接；
# 利用TCP/IP建立数据库服务连接
[root@oldboy ~]# mysql -uroot -poldboy123 -h 192.168.30.101 -P3306 -S /var/lib/mysql/mysql.sock
> 说明：需要在数据库服务中授权网络白名单用户，才能实现采用TCP/IP方式登录操控数据库服务
- 软件终端操作连接登录数据库服务：
利用Navicat数据库客户端远程软件，实现远程连接管理数据库服务；
数据库远程连接客户端下载链接：
软件激活步骤参考：https://zhuanlan.zhihu.com/p/404086701
Navicat软件基础使用：
01 需要建立远程连接数据库服务；
> 说明：在连接数据库服务时，选择好指定的数据库服务类型，例如：mysql mariadb oracle等。
02 连接建立后进行基本操作管理；
可以实现创建新的数据库；
可以实现创建新的数据表；
可以实现创建新的数据项；
- 程序代码操作连接登录数据库服务：
| 序号 | 开发语言 | 连接数据库驱动程序 |
|---|---|---|
| 01 | python | 连接数据库驱动-pymysql |
| 02 | golang | 连接数据库驱动-gomysql |
| 03 | java | 连接数据库驱动-jar |
| 04 | php | 连接数据库驱动-phpmysql |
不同语言程序代码连接数据库服务驱动：https://dev.mysql.com/downloads/
数据库服务连接管理方式逻辑图：
1.3.4 数据库服务初始配置
数据库服务的初始化配置操作的目的，主要是在某些场景下，想让数据库服务程序启动后，具备一些定制化的功能应用；
初始化配置方式一：源码安装数据库服务
数据库服务源码安装准备阶段：
# 下载数据库服务软件程序（源码程序）
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
-- 下载链接地址：https://dev.mysql.com/downloads/mysql/
# 处理数据库服务软件依赖
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
数据库服务源码程序启动配置：
vim /etc/my.cnf
... 部分信息省略 ...
更详细数据库服务源码部署安装过程参考链接：http://www.hzhcontrols.com/new-641636.html
初始化配置方式二：修改数据库配置文件
# 修改数据库配置文件参数，定义数据库服务功能
vim /etc/my.cnf
配置文件加载应用读取顺序：
# 通过数据库服务启动命令获取加载文件顺序
mysqld --help --verbose|grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
-- 数据库服务加载配置文件顺序：
-- /etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
# 数据库服务加载特定路径中的配置文件信息
配置文件特定路径举例：/opt/my.cnf, /data/3306/my.cnf, /data/3307/my.cnf, /data/3308/my.cnf
mysqld --defaults-file=/opt/my.cnf &
mysqld_safe --defaults-file=/opt/my.cnf &
-- 利用启动脚本加载参数信息，手工指定加载配置文件的路径位置信息
配置文件书写格式规范说明：
# 配置文件模板信息参考
cat /etc/my.cnf -- 获取全部配置参数 mysql --verbose --help
[mysql]
socket=/tmp/mysql.sock [mysqld] — 获取全部配置参数 mysqld –verbose –help port=3306 server_id=6 user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock — 服务端常见标签类型：[mysqld] [mysql_safe] [server] — 客户端常用标签类型：[mysql] [mysqldump] [client]
配置文件信息结构说明：
| 序号 | 配置部分 | 配置信息说明 |
|---|---|---|
| 01 | 带有[]部分 | 表示配置标签部分，用于区分不同程序运行的参数，包含服务器端和客户端标签信息 |
[服务端标签] | 负责数据库服务端运行参数信息设定 | |
[客户端标签] | 影响客户端与服务连接参数信息设定（只影响本地客户端连接，不影响远程客户端连接） | |
| 02 | 不带[]部分 | 表示配置参数部分 |
配置文件信息内容说明：
| 序号 | 配置信息 | 解释说明 |
|---|---|---|
| [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 &
> 说明：在命令加参数信息完成的初始化过程，是会优于配置文件中加载的配置参数信息
1.3.5 数据库服务实例创建
在数据库服务运行过程中，是可以存在多实例运行概念的，什么是数据库服务的多实例呢？
一般在一个系统环境中，可以运行多个相同的服务程序信息，并且产生不同的进程和网络端口信息，就可以称为多实例概念；
在数据库服务运行过程中，也可以启动多个数据库服务程序，产生多个数据库服务进程和不同的服务端口，形成多实例；
多个数据库服务实例信息中存储的数据库信息是相互隔离和独立的，并且利用数据库服务多实例可以实现测试与分布式架构需求；
企业数据库服务多实例应用架构设计：（主要用于支持多套业务场景）
数据库服务多实例配置实现过程：
步骤一：多实例部署环境规划
| 实例信息编号 | 实例服务端口 | 实例存储路径 | 实例配置文件 | 套接字文件 |
|---|---|---|---|---|
| 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@xiaoQ-01 ~]# mkdir -p /data/330{7..9}/data
# 授权数据库多实例所需权限
[root@xiaoQ-01 ~]# chown -R mysql. /data/*
步骤三：多实例初始数据信息
# 多实例初始化配置前调整操作
[root@xiaoQ-01 ~]# mv /etc/my.cnf /tmp
# 多实例初始化操作过程
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@xiaoQ-01 ~]# vim /data/3307/data/my.cnf
[mysql]
socket=/tmp/mysql3307.sock
[mysqld]
user=mysql port=3307 basedir=/usr/local/mysql datadir=/data/3307/data socket=/tmp/mysql3307.sock [root@xiaoQ-01 ~]# vim /data/3308/data/my.cnf
[mysqld]
user=mysql port=3308 basedir=/usr/local/mysql datadir=/data/3308/data socket=/tmp/mysql3308.sock [root@xiaoQ-01 ~]# vim /data/3309/data/my.cnf
[mysqld]
user=mysql port=3309 basedir=/usr/local/mysql datadir=/data/3309/data socket=/tmp/mysql3309.sock
步骤五：多实例服务运行启动
[root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3307/data/my.cnf &
[root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3308/data/my.cnf &
[root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3309/data/my.cnf &
步骤六：多实例服务端口查看
[root@xiaoQ-01 ~]# ss -lntup|grep mysqld
1.3.6 数据库服务版本升级
随着互联网技术的不断发展，很多新的应用都会随着企业的需求日新月异，同样数据库服务也会不断的升级更新；
所以早期很多企业的网站架构中，所部署安装的历史版本的数据库服务程序就需要升级迭代，来满足新的场景应用需求；
但是对于数据库服务的升级不同于其他程序升级，因为数据库服务中保存着企业重要的数据信息，一旦升级失败就会造成巨大损失；
因此，数据库程序的版本升级一定要采用比较规范正确的方式升级，即保证程序升级后的应用，同时保证数据的稳定性；
常见的数据库服务程序升级方式：
| 方式 | 升级说明 | 备注说明 |
|---|---|---|
单台服务器升级（Inplace-就地） | 数据库服务小版本升级 | 比如：5.7.20 ->5.7.22 |
| （风险较大） | 数据库服务大版本升级 | 比如：5.7.20 ->5.8.20 |
| 迁移服务器升级（Mergeing-迁移） | 数据库服务小版本升级 | 安装新版本数据库服务（备份迁移/主从迁移） |
| （需要迁移数据） | 数据库服务大版本升级 | 安装新版本数据库服务（备份迁移/主从迁移） |
> 说明：数据库服务升级时，不管哪个方式升级，都应该先做好数据备份，方便升级失败的回退。
01 数据库服务升级注意事项
- 数据库服务版本升级时，只支持在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
02 数据库服务升级过程规划（Inplace）
- 数据库服务数据备份保存（可以采用热备和冷备两种方案，冷备是需要停止业务后备份，热备是无需停止业务备份）
- 数据库服务最新程序安装（最新版本数据库服务安装过程时，无需停止原有数据库旧版服务）
- 数据库服务原有程序关闭（网站显示维护页面）
- 数据库服务最新程序启动（加载原有程序数据实现挂库升级，并采用跳过授权表和跳过网络方式启动）
- 数据库服务升级数据结构（数据库服务升级程序后，还需要升级数据系统结构信息，因此升级时间和数据量无关）
- 数据库服务可以正常重启（数据库服务升级完毕后，确认数据库服务是可以正常完成重启操作）
- 数据库服务功能测试验证（反复核实验证与数据库服务相关的各项功能是否正常）
- 数据库服务升级工作完毕（取消网站维护页面，恢复正常网站线上运营业务）
03 数据库服务升级过程实战
企业数据库实战练习一：演示5.6.48 ->5.7.30 本地升级
数据库版本升级步骤一：数据库服务最新程序安装
# 数据库服务具体安装过程省略，通过查看服务程序目录信息确认是否安装完毕
[root@xiaoQ-01 local]# ll -d mysql*
lrwxrwxrwx 1 root root 35 10月 27 23:28 mysql56 -> mysql-5.6.48-linux-glibc2.12-x86_64
drwxr-xr-x 13 root root 205 10月 27 23:34 mysql-5.6.48-linux-glibc2.12-x86_64
-r-------- 1 root root 343082156 10月 27 23:00 mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
lrwxrwxrwx 1 root root 35 10月 27 23:29 mysql57 -> mysql-5.7.30-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 10月 27 23:28 mysql-5.7.30-linux-glibc2.12-x86_64
-r-------- 1 root root 660017902 10月 27 23:02 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
# 通过服务运行端口信息，确认旧版本与新版本服务运行情况
[root@xiaoQ-01 local]# ss -lntp|grep mysql
LISTEN 0 80 [::]:3356 [::]:* users:(("mysqld",pid=65995,fd=10))
-- 确认只有旧版本服务在运行中，新版本服务并未运行
数据库版本升级步骤二：数据库服务原有程序关闭
# 关闭原有旧版本数据库服务（模拟操作）
[root@xiaoQ-01 local]# systemctl stop mysqld3356
# 关闭原有就版本数据库服务（企业实战）
[root@xiaoQ-01 local]# vim /data/3356/my.cnf
innodb_fast_shutdown=0
-- 企业实战环境，需要添加此配置参数信息在配置文件中，实现优雅关闭数据库服务
[root@xiaoQ-01 local]# systemctl stop mysqld3356
-- 配置文件加入以上参数后，此时关闭数据库服务，会采用优雅关闭服务，而不是采用快速关闭数据库服务方式
数据库版本升级步骤三：数据库服务最新程序启动
# 修改旧版本数据库服务配置文件，使用新版本程序加载原有数据库中数据目录
[root@xiaoQ-01 local]# vim /data/3356/my.cnf
[mysqld]
user=mysql basedir=/usr/local/mysql57 datadir=/data/3356/data socket=/tmp/mysql3356.sock port=3356 server_id=16 innodb_fast_shutdown=0 # 利用命令脚本文件测试启动新版本数据库程序服务 [root@xiaoQ-01 local]# /usr/local/mysql57/bin/mysqld_safe –defaults-file=/data/3356/my.cnf –skip-grant-tables –skip-networking & # 查看数据库服务启动进程信息 [root@xiaoQ-01 local]# ps -ef|grep mysqld root 97470 89853 0 00:05 pts/3 00:00:00 /bin/sh /usr/local/mysql57/bin/mysqld_safe –defaults-file=/data/3356/my.cnf –skip-grant-tables –skip-networking mysql 97638 97470 5 00:05 pts/3 00:00:00 /usr/local/mysql57/bin/mysqld –defaults-file=/data/3356/my.cnf –basedir=/usr/local/mysql56 –datadir=/data/3356/data –plugin-dir=/usr/local/mysql56/lib/plugin –user=mysql –skip-grant-tables –skip-networking –log-error=xiaoQ-01.edu.err –pid-file=xiaoQ-01.edu.pid –socket=/tmp/mysql3356.sock –port=3356 — 此时数据库服务启动成功后，只会显示进程信息，不会显示网络端口信息
数据库版本升级步骤四：数据库服务升级数据结构
# 实现对原有数据库中数据信息的挂库升级操作
[root@xiaoQ-01 local]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql3356.sock --force
...省略部分信息...
Upgrade process completed successfully.
Checking if update is needed.
-- 显示以上信息内容，表述数据库中数据结构信息升级成功
# 验证数据库中数据结构是否升级成功
[root@xiaoQ-01 local]# /usr/local/mysql57/bin/mysql -S /tmp/mysql3356.sock
mysql> show databases;
+------------------------------+
| Database |
+------------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+------------------------------+
5 rows in set (0.00 sec)
--- 升级完毕后，本地连接新版本数据库服务，查看数据库信息会显示sys库，表示数据结构信息升级成功
> 说明：在数据库版本从5.7升级到8.0时，可以省略mysql_upgrade升级改变数据结构信息操作，因为8.0版本会自动完成此步骤
数据库版本升级步骤五：数据库服务重启正常状态
# 关闭之前升级状态的数据库服务
[root@xiaoQ-01 local]# /usr/local/mysql57/bin/mysqladmin -S /tmp/mysql3356.sock shutdown
2022-10-27T16:41:07.726342Z mysqld_safe mysqld from pid file /data/3356/data/xiaoQ-01.edu.pid ended
[1]+ 完成 /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3356/my.cnf --skip-grant-tables --skip-networking
# 修改系统服务启动程序文件信息
[root@xiaoQ-01 local]# vim /etc/systemd/system/mysqld3356.service
ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3356/my.cnf
-- 修改此配置信息，将原有数据库服务启动文件中程序目录修改为新版本数据库服务程序目录
# 利用原有数据库启动命令进行服务启动
[root@xiaoQ-01 local]# systemctl daemon-reload
[root@xiaoQ-01 local]# systemctl start mysqld3356
# 检查数据服务升级完毕后是否正常启动
[root@xiaoQ-01 local]# ps -ef|grep mysql
mysql 16652 1 3 00:48 ? 00:00:00 /usr/local/mysql57/bin/mysqld --defaults-file=/data/3356/my.cnf
-- 检查核实数据库服务进程信息是否存在
[root@xiaoQ-01 local]# netstat -lntp|grep mysql
tcp6 0 0 :::3356 :::* LISTEN 16652/mysqld
-- 检查核心数据库服务端口信息是否存在
[root@xiaoQ-01 local]# /usr/local/mysql57/bin/mysql -S /tmp/mysql3356.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 MySQL Community Server (GPL)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.30 |
+-----------+
1 row in set (0.00 sec)
-- 连接进入数据库服务中，查看数据库服务版本信境
企业数据库实战练习二：演示5.7.30 ->8.0.20本地升级）
数据库版本升级步骤一：数据库服务最新程序安装
# 数据库服务具体安装过程省略，通过查看服务程序目录信息确认是否安装完毕
[root@xiaoQ-01 local]# ll -d mysql*
lrwxrwxrwx 1 root root 35 10月 27 23:29 mysql57 -> mysql-5.7.30-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 10月 27 23:28 mysql-5.7.30-linux-glibc2.12-x86_64
-r-------- 1 root root 660017902 10月 27 23:02 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
lrwxrwxrwx 1 root root 35 10月 27 23:28 mysql80 -> mysql-8.0.20-linux-glibc2.12-x86_64
drwxr-xr-x 13 root root 205 10月 27 23:34 mysql-8.0.20-linux-glibc2.12-x86_64
-r-------- 1 root root 343082156 10月 27 23:00 mysql-8.0.20-linux-glibc2.12-x86_64.tar.gz
# 通过服务运行端口信息，确认旧版本与新版本服务运行情况
[root@xiaoQ-01 local]# ss -lntp|grep mysql
LISTEN 0 80 [::]:3356 [::]:* users:(("mysqld",pid=65995,fd=10))
-- 确认只有旧版本服务在运行中，新版本服务并未运行
数据库版本升级步骤二：数据库服务原有程序关闭
# 关闭原有旧版本数据库服务（模拟操作）
[root@xiaoQ-01 local]# systemctl stop mysqld3357
# 关闭原有就版本数据库服务（企业实战）
[root@xiaoQ-01 local]# vim /data/3357/my.cnf
innodb_fast_shutdown=0
-- 企业实战环境，需要添加此配置参数信息在配置文件中，实现优雅关闭数据库服务
或者
> set global innodb_fast_shutdown=0
> select @@innodb_fast_shutdown
-- 连接进入旧版本数据库中，直接设置数据库服务功能参数信息，使之立即生效
[root@xiaoQ-01 local]# systemctl stop mysqld3357
-- 配置文件加入以上参数后，此时关闭数据库服务，会采用优雅关闭服务，而不是采用快速关闭数据库服务方式
数据库版本升级步骤三：数据库服务最新程序启动
# 修改旧版本数据库服务配置文件，使用新版本程序加载原有数据库中数据目录
[root@xiaoQ-01 local]# vim /data/3357/my.cnf
[mysqld]
user=mysql basedir=/usr/local/mysql80 datadir=/data/3357/data socket=/tmp/mysql3357.sock port=3357 innodb_fast_shutdown=0 # 利用命令脚本文件测试启动新版本数据库程序服务 [root@xiaoQ-01 local]# /usr/local/mysql80/bin/mysqld_safe –defaults-file=/data/3357/my.cnf –skip-grant-tables –skip-networking & # 查看数据库服务启动进程信息 [root@xiaoQ-01 local]# ps -ef|grep mysqld — 此时数据库服务启动成功后，只会显示进程信息，不会显示网络端口信息
数据库版本升级步骤四：数据库服务升级数据结构 （此步骤可省略）
# 验证数据库中数据结构是否升级成功
[root@xiaoQ-01 local]# /usr/local/mysql80/bin/mysql -S /tmp/mysql3357.sock
mysql> show databases;
+------------------------------+
| Database |
+------------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+------------------------------+
5 rows in set (0.00 sec)
--- 升级完毕后，本地连接新版本数据库服务，查看数据库信息会显示sys库，表示数据结构信息升级成功
> 说明：在数据库版本从5.7升级到8.0时，可以省略mysql_upgrade升级改变数据结构信息操作，因为8.0版本会自动完成此步骤
数据库版本升级步骤五：数据库服务重启正常状态
# 关闭之前升级状态的数据库服务
[root@xiaoQ-01 local]# /usr/local/mysql80/bin/mysqladmin -S /tmp/mysql3357.sock shutdown
# 修改系统服务启动程序文件信息
[root@xiaoQ-01 local]# vim /etc/systemd/system/mysqld3357.service
ExecStart=/usr/local/mysql80/bin/mysqld --defaults-file=/data/3357/my.cnf
-- 修改此配置信息，将原有数据库服务启动文件中程序目录修改为新版本数据库服务程序目录
# 利用原有数据库启动命令进行服务启动
[root@xiaoQ-01 local]# systemctl daemon-reload
[root@xiaoQ-01 local]# systemctl start mysqld3357
# 检查数据服务升级完毕后是否正常启动
[root@xiaoQ-01 local]# ps -ef|grep mysql
mysql 16652 1 3 00:48 ? 00:00:00 /usr/local/mysql80/bin/mysqld --defaults-file=/data/3357/my.cnf
-- 检查核实数据库服务进程信息是否存在
[root@xiaoQ-01 local]# netstat -lntp|grep mysql
tcp6 0 0 :::3356 :::* LISTEN 16652/mysqld
-- 检查核心数据库服务端口信息是否存在
[root@xiaoQ-01 local]# /usr/local/mysql80/bin/mysql -S /tmp/mysql3357.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.20 MySQL Community Server (GPL)
mysql> select @@default_authentication_plugin;
-- 查看数据库服务密码加密插件是否变为了8.0数据库服务的默认设置
mysql> select version();
+-----------+
| version() |
+-----------+
|8.0.20 |
+-----------+
1 row in set (0.00 sec)
-- 连接进入数据库服务中，查看数据库服务版本信息
04 数据库服务升级版本确认
在早期升级数据服务程序时，很多企业环境升级过程，都需要有前期的模拟测试环节，并且测试环境的版本升级兼容性需要盲猜；
根本无法通过当前环境的数据库服务版本，来核实确认是否可以进行升级的兼容性版本信息，从而有效避免升级后带来的问题；
而在数据库服务8.0之后，提供了一个mysqlshell命令功能，利用命令功能中的特殊函数，可以在升级前对当前环境版本进行验证；
简而言之：就是数据库服务8.0之后，利用mysqlshell中的函数，可以实现数据库服务升级的预检查功能
具体操作命令信息：
# 上传解压8.0版本数据库mysql shell功能程序
[root@xiaoQ-01 local]# tar xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz
[root@xiaoQ-01 local]# ln -s mysql-shell-8.0.20-linux-glibc2.12-x86-64bit mysqlsh
# 确认当前5.7版本信息是否可以升级到相应的8.0版本
[root@xiaoQ-01 local]# cd mysqlsh/bin/
[root@xiaoQ-01 bin]# ll
[root@xiaoQ-01 bin]# ll
总用量 41992
-rwxr-xr-x 1 7161 31415 8093728 3月 11 2020 mysql-secret-store-login-path
-rwxr-xr-x 1 7161 31415 34899760 3月 11 2020 mysqlsh
-- 可以将以上命令目录信息设置为环境变量，方便工作中操作mysqlsh命令
[root@xiaoQ-01 bin]# ./mysqlsh root:123456@10.0.0.101:3357 -e "util.checkForServerUpgrade()"
-- 指定连接的旧版服务的用户名与密码，并根据网络白名单列表和端口建立连接，实现测试
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at 192.168.30.100:3306, version 5.7.28 - MySQL Community
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.20...
1) Usage of old temporal type
No issues found
2) Usage of db objects with names conflicting with new reserved keywords
No issues found
3) Usage of utf8mb3 charset
No issues found
4) Table names in the mysql schema conflicting with new tables in 8.0
No issues found
5) Partitioned tables using engines with non native partitioning
No issues found
6) Foreign key constraint names longer than 64 characters
No issues found
7) Usage of obsolete MAXDB sql_mode flag
No issues found
8) Usage of obsolete sql_mode flags
No issues found
9) ENUM/SET column definitions containing elements longer than 255 characters
No issues found
10) Usage of partitioned tables in shared tablespaces
No issues found
11) Circular directory references in tablespace data file paths
No issues found
12) Usage of removed functions
No issues found
13) Usage of removed GROUP BY ASC/DESC syntax
No issues found
13) Usage of removed GROUP BY ASC/DESC syntax
No issues found
14) Removed system variables for error logging to the system log configuration
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging
15) Removed system variables
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
16) System variables with new default values
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
17) Zero Date, Datetime, and Timestamp values
No issues found
18) Schema inconsistencies resulting from file removal or corruption
No issues found
19) Tables recognized by InnoDB that belong to a different engine
No issues found
20) Issues reported by 'check table x for upgrade' command
No issues found
21) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:
[mysqld]
default_authentication_plugin=mysql_native_password However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security. If you are using replication please take time to understand how the authentication plugin changes may impact you. More information: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication Errors: 0 Warnings: 1 Notices: 0 — 只要关注结果信息中，是否存在errors信息 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
04 数据库服务升级回滚实践
企业数据库实战练习一：跨大版本回滚
数据库版本升级步骤一：数据库服务编写回滚文件
[root@xiaoQ-01 local]# vim /data/3356/my_rollback.cnf
[mysqld]
user=mysql basedir=/usr/local/mysql56 datadir=/data/3356_back/data — 升级前做的旧版本冷备数据 socket=/tmp/mysql3356.sock port=3356 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
-- 此时数据库服务启动成功后，只会显示进程信息，不会显示网络端口信息
数据库版本升级步骤三：数据库服务会推检查确认
[root@xiaoQ-01 local]# /usr/local/mysql56/bin/mysql -S /tmp/mysql3356.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.48 MySQL Community Server (GPL)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.48 |
+-----------+
1 row in set (0.00 sec)
-- 连接进入数据库服务中，查看数据库服务版本信息
企业数据库实战练习二：跨小版本回滚
数据库服务官方参考链接：https://dev.mysql.com/doc/refman/5.7/en/downgrading.html
> 说明：对于数据库8.0版本是不支持小版本间与大版本间的回滚的，只能采取恢复升级前数据方案。