数据库

1.数据库原理

1.1.E-R模型

E-R图模型的组成是由实体，属性和联系三部份组成。

实体：( Entity )
    实体是数据的使用者，代表软件系统中客观存在的生活中的实物，同一类实体构成实体集。
    在ER图中，实体用矩形表示。
属性：( Attribute )
    实体中的所有特性称为属性，每个属性描述的是实体的单个特性。在ER图中，属性用椭圆形表示。
联系：( Relationship )
    描述了实体的属性之间的关联规则。在ER图中，联系用菱形表示。

实体间的联系有三种类型：

一对一联系 ( 1:1 )：例如,一个学号只能分配给一个同学,每个同学都有一个学号，则学号与同学的联系是一对一。
一对多联系 ( 1:n )：例如,一个老师可以教授多门课程,每门课程只能有一个老师教授,则老师与课程的联系是一对多。
多对多联系 ( m:n )：例如,一个同学可以报名多门课程，每门课程下有多个同学，则同学与课程的联系是多对多。

1.2.设计数据库的基本原则--三大范式

第一范式 1NF (确保每列保持原子性)
第二范式 2NF (确保表中的每列都和主键相关)
第三范式 3NF (确保每列都和主键列直接相关，而不是间接相关)

1.3.MySQL执行查询语句执行流程

MySQL数据库中的SQL查询语句执行流程

1 接收查询语句：
    用户通过客户端或编程语言框架里面的驱动模块，经由专门的通信协议，向MySQL服务器发送SQL查询语句。
        如果是mysql服务端，这里的通信协议端口是 3306
    数据库服务端内部的连接器接收查询请求后，首先到本地的缓存记录中检索信息
        如果有信息，则直接返回查询结果
        如果没有信息，则将信息直接传递给后端的数据库解析器来进行处理

2 词法分析和语法分析：
    MySQL服务器首先会对接收到的查询语句进行词法分析，将其分解成一系列的词法单元。
    接着，进行语法分析，检查这些词法单元是否符合SQL语法规则。如果语法错误，MySQL会立即返回错误信息。

3 语义分析：
    在语法分析之后，MySQL会进行语义分析，检查查询语句中的表、列、函数等是否存在，以及用户是否有足够的权限执行该查询。
    语义分析还会检查查询语句中的数据类型是否匹配，以及是否存在违反数据库完整性的约束。

4 查询优化：
    MySQL的查询优化器会对查询语句进行优化，决定最优的执行查询计划 -- 也就是怎么走会效率更快。
    优化过程包括选择最佳的访问路径、索引、连接顺序等，以最小化查询的执行时间和资源消耗。
    优化器还会考虑统计信息，如表的行数、索引的分布等，以做出更明智的决策。

5 查询执行：
    优化后的查询计划被传递给执行器进行执行。执行器根据查询计划访问存储引擎，获取数据。
        存储引擎将文件系统里面的数据库文件数据，加载到内存，然后在内存中做一次聚合
        然后，临时放到查询缓存中，便于下次遇到相同的查询语句重复过来。
    在执行过程中，MySQL可能会使用缓存来加速查询的执行。从MySQL 8.0开始，查询缓存已被移除。

6 结果集返回：
    执行器将查询结果集返回给客户端。结果集可以包含零行或多行数据，具体取决于查询的内容。
    如果查询涉及聚合函数（如SUM、AVG等）或分组操作（如GROUP BY），执行器还会在返回结果之前对这些数据进行相应的处理。

7 错误处理和日志记录：
    如果在执行过程中遇到任何错误（如权限不足、表不存在等），MySQL会返回相应的错误信息给客户端。
    同时，MySQL还会记录查询的执行日志，以便进行性能分析和故障排查。

主要区别：MySQL 5.7 及之前版本查询缓存常被使用，但存在一些问题（如命中率低、维护开销大）。
MySQL 8.0 弃用查询缓存，避免相关开销。

-- 查询2023年每个城市中，购买了“电子产品”类别商品，且订单总金额大于1000元的客户信息
-- 以及他们对应的订单信息和购买的商品明细
SELECT 
    c.customer_name,
    c.customer_city,
    o.order_id,
    o.order_date,
    o.total_amount,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM 
    customers c
-- 连接orders表，获取客户的订单信息
JOIN 
    orders o ON c.customer_id = o.customer_id
-- 连接order_items表，获取订单中的商品明细信息
JOIN 
    order_items oi ON o.order_id = oi.order_id
-- 连接products表，获取商品的名称等信息
JOIN 
    products p ON oi.product_id = p.product_id
-- 连接categories表，获取商品所属类别信息
JOIN 
    categories cat ON p.category_id = cat.category_id
WHERE 
    -- 筛选出2023年的订单
    YEAR(o.order_date) = 2023 
    AND 
    -- 筛选出商品类别为“电子产品”的记录
    cat.category_name = '电子产品' 
    AND 
    -- 筛选出订单总金额大于1000元的订单
    o.total_amount > 1000
GROUP BY 
    -- 按照客户姓名、城市、订单ID、订单日期、订单总金额、商品名称进行分组
    c.customer_name,
    c.customer_city,
    o.order_id,
    o.order_date,
    o.total_amount,
    p.product_name
ORDER BY 
    -- 按照客户所在城市升序、订单总金额降序排序
    c.customer_city ASC,
    o.total_amount DESC;

对于这个大的流程来说，专用协议之后的功能，全部由数据库软件来实现，而我们需要做的是前面的各种sql语句的编写。

2.Mysql安装方式

mysql主流的四种安装方式

3.关于软件mysql和mariadb安装文件前后对比

注意：两个环境安装不同的mysql和mariadb
    配置文件的名称和位置都不一样
        但是，配置内容，基本上都是一样。
        mysql 是 多线程工作模式

4.包管理进行安装

4.1.光盘镜像

Rocky9 系列光盘镜像自带 mysql-server 8.0 和 mariadb-server 10.3

挂载镜像
[root@Rocky9-12 ~]# mount /dev/cdrom /opt/
mount: /opt: WARNING: source write-protected, mounted read-only.
[root@Rocky9-12 ~]# ls /opt/
AppStream  BaseOS  EFI  images  isolinux  LICENSE  media.repo


查看数据库软件
[root@Rocky9-12 ~]# ls /opt/AppStream/Packages/m/mysql-ser*
/opt/AppStream/Packages/m/mysql-server-8.0.36-1.el9_3.x86_64.rpm

[root@Rocky9-12 ~]# ls /opt/AppStream/Packages/m/mariadb-server-1*
/opt/AppStream/Packages/m/mariadb-server-10.11.6-1.module+el9.4.0+20012+a68bdff7.x86_64.rpm
/opt/AppStream/Packages/m/mariadb-server-10.5.22-1.el9_2.x86_64.rpm

其他信息：

CentOS7 系列光盘镜像自带 mariadb-server 5.5
CentOS6 系列光盘镜像自带 mysql-server 5.1

[21:37:13 root@centos7 ~]# mount /dev/cdrom /opt
mount: /dev/sr0 is write-protected, mounting read-only
[21:37:27 root@centos7 ~]# ls /opt/
CentOS_BuildTag  EULA  images    LiveOS    repodata    RPM-GPG-KEY-CentOS-Testing-7
EFI              GPL   isolinux  Packages  RPM-GPG-KEY-CentOS-7  TRANS.TBL


查看数据库软件
[21:38:19 root@centos7 ~]# ls /opt/Packages/mariadb-server-5.5.68-1.el7.x86_64.rpm
/opt/Packages/mariadb-server-5.5.68-1.el7.x86_64.rpm

4.2.官方yum源

[root@Rocky9-12 ~]# yum list "mysql"
mysql.x86_64                        8.0.41-2.el9_5               


[root@Rocky9-12 ~]# yum list "mariadb"
mariadb.x86_64                      3:10.5.27-1.el9_5 

4.3.mariadb源

如何获取最新的mariadb软件源，参考mariadb 官方源配置页面，选择版本和系统发行版本
注意：目前版本最新为 11.8

官网：https://mariadb.org/download/

4.4.mysql源

如何获取最新的mysql软件源，参考mysql官方源配置页面

官网：http://dev.mysql.com/downloads/

点击需要使用功能的源类型，然后下载对应的包就可以了
MySQL Community Downloads 即 MySQL 社区版下载

General Availability (GA) Releases：意思是 “通用版本发布”
通常指软件经过全面测试、达到稳定状态，可供广大用户在生产环境等正式场景使用的版本，按钮为橙色文字。

Archives：意为 “存档”
这里一般指软件过往旧版本的集合，供有特定需求（如兼容性需求）的用户下载使用，按钮为蓝色文字。

apt源中版本最新版本是8.34-1

yum源版本最新版本是8.0.4x版本

结果显示：
    社区版，在 APT 源中，目前主流的是8版本
    在其他的下载页面，虽然存在9.1.0版本，但是LTS依然是8版本

5.Rocky9中安装mysql8.0

5.1.软件信息

获取mysql软件源
[root@Rocky9-12 ~]# yum list mysql mysql-server
mysql.x86_64                  8.0.41-2.el9_5           appstream
mysql-server.x86_64           8.0.41-2.el9_5           appstream

获取mariadb的软件源
[root@Rocky9-15 ~]# yum list mariadb mariadb-server
mariadb.x86_64                3:10.5.27-1.el9_5       appstream
mariadb-server.x86_64         3:10.5.27-1.el9_5       appstream

5.2.安装软件

安装 mysql-server，会自动安装客户端包【安装客户端不会安装服务端软件】

[root@Rocky9-12 ~]# yum install -y mysql-server

服务状态
[root@Rocky9-12 ~]# systemctl status mysqld.service
○ mysqld.service - MySQL 8.0 database server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disabled)
     Active: inactive (dead)

启动服务
[root@Rocky9-12 ~]# systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

再次查看
[root@Rocky9-12 ~]# systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
     Active: active (running) 

5.3.环境查看

检测mysql的进程状态

多线程模式
[root@Rocky9-12 ~]# pstree | grep mysql
        |-mysqld---36*[{mysqld}]

检测其他信息

自动创建的账户
[root@Rocky9-12 ~]# getent passwd mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin

查看mysql真正的家目录
[root@Rocky9-12 ~]# ll /var/lib/mysql
total 91604
-rw-r----- 1 mysql mysql       56 May  7 21:58  auto.cnf
-rw-r----- 1 mysql mysql      157 May  7 21:58  binlog.000001
-rw-r----- 1 mysql mysql       16 May  7 21:58  binlog.index
-rw------- 1 mysql mysql     1705 May  7 21:58  ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 May  7 21:58  ca.pem
-rw-r--r-- 1 mysql mysql     1112 May  7 21:58  client-cert.pem
-rw------- 1 mysql mysql     1705 May  7 21:58  client-key.pem
-rw-r----- 1 mysql mysql   196608 May  7 22:00 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql  8585216 May  7 21:58 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql     6238 May  7 21:58  ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 May  7 21:58  ibdata1
-rw-r----- 1 mysql mysql 12582912 May  7 21:58  ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  7 21:58 '#innodb_redo'
drwxr-x--- 2 mysql mysql      187 May  7 21:58 '#innodb_temp'
drwxr-x--- 2 mysql mysql      143 May  7 21:58  mysql
-rw-r----- 1 mysql mysql 26214400 May  7 21:58  mysql.ibd
srwxrwxrwx 1 mysql mysql        0 May  7 21:58  mysql.sock
-rw------- 1 mysql mysql        5 May  7 21:58  mysql.sock.lock
-rw-r--r-- 1 mysql mysql        7 May  7 21:58  mysql_upgrade_info
srwxrwxrwx 1 mysql mysql        0 May  7 21:58  mysqlx.sock
-rw------- 1 mysql mysql        5 May  7 21:58  mysqlx.sock.lock
drwxr-x--- 2 mysql mysql     8192 May  7 21:58  performance_schema
-rw------- 1 mysql mysql     1705 May  7 21:58  private_key.pem
-rw-r--r-- 1 mysql mysql      452 May  7 21:58  public_key.pem
-rw-r--r-- 1 mysql mysql     1112 May  7 21:58  server-cert.pem
-rw------- 1 mysql mysql     1705 May  7 21:58  server-key.pem
drwxr-x--- 2 mysql mysql       28 May  7 21:58  sys
-rw-r----- 1 mysql mysql 16777216 May  7 22:00  undo_001
-rw-r----- 1 mysql mysql 16777216 May  7 22:00  undo_002

查看数据库的数据文件
[root@Rocky9-12 ~]# ls /var/lib/mysql/mysql
general_log_213.sdi  general_log.CSM  general_log.CSV  
slow_log_214.sdi  slow_log.CSM  slow_log.CSV

注意：因为这些文件，不是普通的文件系统，它是数据库专用的文件，只能通过数据库的统一接口到这些文件中，进行数据的查找。

查看监听端口，默认3306，33060 是8.0版本中的特性
[root@Rocky9-12 ~]# ss -tnulp | grep mysql
tcp   LISTEN 0      151     *:3306    *:*    users:(("mysqld",pid=2520,fd=24))
tcp   LISTEN 0      70      *:33060   *:*    users:(("mysqld",pid=2520,fd=21))

注意：X Protocol 是一种新的通信协议，旨在提供更高效、灵活的数据交互方式，让客户端与服务器通信更顺畅。
在 MySQL 8.0 中，33060 端口是 X Protocol 协议的端口 。

5.4.登录测试

客户端连接: 默认用户名 root，默认密码为空

连接到数据库里面
[root@Rocky9-12 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.41 Source distribution
mysql>

查看当前数据库的版本信息
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41    |
+-----------+
1 row in set (0.00 sec)

mysql> exit; # 退出数据库的连接

客户端连接: 直接使用用户和密码
[root@Rocky9-12 ~]# mysql -uroot -p -P3306
Enter password:                # 因为没有密码，所以这里直接Enter即可
Server version: 8.0.41 Source distribution
mysql>

mysql> \s        # 显示当前 MySQL 服务器会话状态的快捷命令
--------------
mysql  Ver 8.0.41 for Linux on x86_64 (Source distribution)

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.41 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 8 min 35 sec

Threads: 2  Questions: 8  Slow queries: 0  Opens: 120  Flush tables: 3  Open tables: 36  Queries per second avg: 0.015
--------------

6.在Rocky9中安装mariadb

6.1.软件安装

查看mariadb软件
[root@Rocky9-15 ~]# yum list mariadb
mariadb.x86_64          3:10.5.27-1.el9_5          appstream

安装软件 -- 会自动安装客户端
[root@Rocky9-15 ~]# yum -y install mariadb-server

启动服务
[root@Rocky9-15 ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

查看状态
[root@Rocky9-15 ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.5 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: disabled)
     Active: active (running)

监听端口
[root@Rocky9-15 ~]# ss -tunlp | grep 3306
tcp   LISTEN 0      80    *:3306      *:*    users:(("mariadbd",pid=3169,fd=19))

# 服务名字变成了 mariadb

注意：
mariadb里面没有33060的端口
MariaDB 的设计初衷是保持与 MySQL 高度兼容，主要继承 MySQL 5.5 及之前版本的特性。
它专注于提供稳定、性能良好且与旧版 MySQL 兼容的数据库服务，面向大多数传统数据库应用场景，这些场景不需
要通过 33060 端口使用 X Protocol。

多线程状态
[root@Rocky9-15 ~]# pstree | grep maria
        |-mariadbd---7*[{mariadbd}]

查看家目录文件结构
[root@Rocky9-15 ~]# ll /var/lib/mysql/
total 122920
-rw-rw---- 1 mysql mysql     24576 May  7 22:10 aria_log.00000001
-rw-rw---- 1 mysql mysql        52 May  7 22:10 aria_log_control
-rw-rw---- 1 mysql mysql       972 May  7 22:10 ib_buffer_pool
-rw-rw---- 1 mysql mysql  12582912 May  7 22:10 ibdata1
-rw-rw---- 1 mysql mysql 100663296 May  7 22:10 ib_logfile0
-rw-rw---- 1 mysql mysql  12582912 May  7 22:10 ibtmp1
-rw-rw---- 1 mysql mysql         0 May  7 22:10 multi-master.info
drwx------ 2 mysql mysql      4096 May  7 22:10 mysql
srwxrwxrwx 1 mysql mysql         0 May  7 22:10 mysql.sock
-rw-rw---- 1 mysql mysql        16 May  7 22:10 mysql_upgrade_info
drwx------ 2 mysql mysql        20 May  7 22:10 performance_schema

检查客户端连接命令 -- 本质上是 mariadb文件
[root@Rocky9-15 ~]# ll /usr/sbin/mysqld
lrwxrwxrwx 1 root root 19 Feb  4 19:21 /usr/sbin/mysqld -> /usr/libexec/mysqld
[root@Rocky9-15 ~]# ll /usr/libexec/mysqld
lrwxrwxrwx 1 root root 8 Feb  4 19:21 /usr/libexec/mysqld -> mariadbd

6.2.连接登录

客户端连接
[root@Rocky9-15 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.27-MariaDB MariaDB Server

MariaDB [(none)]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.5.27-MariaDB |
+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> exit
Bye

7.Ubuntu24 安装mysql8.0

7.1.安装软件

安装 mysql-server，会自动安装客户端包【安装客户端不会安装服务端软件】
[root@ubuntu24-13:~]# apt install mysql-server -y

再次查看状态信息
[root@ubuntu24-13:~]# systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) 

注意：如果是第一次安装mysql-server出现服务启动不起来，而且没有提前做任何的配置，那一般情况下，是本地主机环境已经被历史残留的数据信息破坏了。

检测mysql的进程状态

多线程模式
[root@ubuntu24-13:~]# pstree | grep mysql
        |-mysqld---36*[{mysqld}]


自己创建的账户
[root@ubuntu24-13:~]# getent passwd mysql
mysql:x:110:110:MySQL Server,,,:/nonexistent:/bin/false

查看mysql真正的家目录
[root@ubuntu24-13:~]# ll /var/lib/mysql
total 91612
-rw-r-----  1 mysql mysql   196608 May  7 22:19 '#ib_16384_0.dblwr'
-rw-r-----  1 mysql mysql  8585216 May  7 22:17 '#ib_16384_1.dblwr'
drwxr-x---  2 mysql mysql     4096 May  7 22:17 '#innodb_redo'/
drwxr-x---  2 mysql mysql     4096 May  7 22:17 '#innodb_temp'/
drwx------  7 mysql mysql     4096 May  7 22:17  ./
drwxr-xr-x 50 root  root      4096 May  7 22:17  ../
-rw-r-----  1 mysql mysql       56 May  7 22:17  auto.cnf
-rw-r-----  1 mysql mysql      180 May  7 22:17  binlog.000001
-rw-r-----  1 mysql mysql      404 May  7 22:17  binlog.000002
-rw-r-----  1 mysql mysql      157 May  7 22:17  binlog.000003
-rw-r-----  1 mysql mysql       48 May  7 22:17  binlog.index
-rw-------  1 mysql mysql     1705 May  7 22:17  ca-key.pem
-rw-r--r--  1 mysql mysql     1112 May  7 22:17  ca.pem
-rw-r--r--  1 mysql mysql     1112 May  7 22:17  client-cert.pem
-rw-------  1 mysql mysql     1705 May  7 22:17  client-key.pem
-rw-r--r--  1 root  root         0 May  7 22:17  debian-5.7.flag
-rw-r-----  1 mysql mysql     3425 May  7 22:17  ib_buffer_pool
-rw-r-----  1 mysql mysql 12582912 May  7 22:17  ibdata1
-rw-r-----  1 mysql mysql 12582912 May  7 22:17  ibtmp1
drwxr-x---  2 mysql mysql     4096 May  7 22:17  mysql/
-rw-r-----  1 mysql mysql 26214400 May  7 22:17  mysql.ibd
drwxr-x---  2 mysql mysql     4096 May  7 22:17  performance_schema/
-rw-------  1 mysql mysql     1709 May  7 22:17  private_key.pem
-rw-r--r--  1 mysql mysql      452 May  7 22:17  public_key.pem
-rw-r--r--  1 mysql mysql     1112 May  7 22:17  server-cert.pem
-rw-------  1 mysql mysql     1705 May  7 22:17  server-key.pem
drwxr-x---  2 mysql mysql     4096 May  7 22:17  sys/
-rw-r-----  1 mysql mysql        5 May  7 22:17  ubuntu24-13.pid
-rw-r-----  1 mysql mysql 16777216 May  7 22:19  undo_001
-rw-r-----  1 mysql mysql 16777216 May  7 22:19  undo_002

查看数据库的数据文件
[root@ubuntu24-13:~]# ls /var/lib/mysql/mysql
general_log.CSM  general_log.CSV  general_log_213.sdi  
slow_log.CSM  slow_log.CSV  slow_log_214.sdi

注意：因为这些文件，不是普通的文件系统，它是数据库专用的文件，只能通过数据库的统一接口到这些文件中，进行数据的查找。

查看监听端口，默认3306，33060 是8.0版本中的特性
[root@ubuntu24-13:~]# netstat -tunlp | grep mysql
tcp        0      0 127.0.0.1:3306   0.0.0.0:*     LISTEN      8524/mysqld
tcp        0      0 127.0.0.1:33060  0.0.0.0:*     LISTEN      8524/mysqld

结果显示：默认监听的是本地主机的IP地址

7.2.登录连接

客户端连接: 默认用户名 root，默认密码为空

连接到数据库里面
[root@ubuntu24-13:~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu)

# 查看当前数据库的版本信息
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.42-0ubuntu0.24.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> exit        # 退出数据库的连接
Bye

客户端连接: 直接使用用户和密码
[root@ubuntu24-13:~]# mysql -uroot -p
Enter password:                # 因为没有密码，所以这里直接Enter即可，也可以随便乱写密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu)

Copyright (c) 2000, 2025, 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> \s            # 显示当前 MySQL 服务器会话状态的快捷命令
--------------
mysql  Ver 8.0.42-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.42-0ubuntu0.24.04.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 7 min 11 sec

Threads: 2  Questions: 8  Slow queries: 0  Opens: 119  Flush tables: 3  Open tables: 38  Queries per second avg: 0.018
--------------

8.Ubuntu24中安装mysql8.0---二进制包安装

8.1.如何获取二进制包文件

传统的二进制包安装需要进行三步：configure --- make --- make install
而mysql的二进制包是指己经编译完成【也就是说，make已经做过了】，以压缩包提供下载的文件，下载到本
地之后释放到自定义目录，再进行配置即可。

二进制包的下载位置 -- Download Archives

官网：https://dev.mysql.com/downloads

选择 Download Archives

选择第一个 Mysql Community Server

选择第一个，然后点击右侧的下载就可以了
https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

以同样的逻辑下载mysql的最新版本9.2.0

选择第一个，然后点击右侧的下载就可以了
https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz

8.2.基础环境

安装依赖
[root@ubuntu24-16:~]# apt -y install libaio-dev numactl libnuma-dev libncurses-dev

注意：ubuntu24系统没有libaio1的包，需要单独去下载安装
curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb
dpkg -i libaio1_0.3.113-4_amd64.deb

8.3.用户环境

[root@ubuntu24-16:~]# groupadd -r mysql
[root@ubuntu24-16:~]# useradd -r -g mysql -s /sbin/nologin mysql

8.4.软件安装

获取软件
[root@ubuntu24-16:~]# mkdir -p /data/softs
[root@ubuntu24-16:~]# cd /data/softs
[root@ubuntu24-16:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

解压至指定目录，这个目录只能写 /usr/local/
[root@ubuntu24-16:softs]# mkdir /usr/local/mysql
[root@ubuntu24-16:softs]# tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
[root@ubuntu24-16:softs]# mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql

查看文件
[root@ubuntu24-16:softs]# ls /usr/local/mysql
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@ubuntu24-16:softs]# ls /usr/local/mysql/bin/
ibd2sdi                 myisamchk     mysql_config               
mysql_tzinfo_to_sql  mysqld        mysqldump         mysqlslap
innochecksum         myisamlog     mysql_config_editor        
mysqladmin           mysqld-debug  mysqldumpslow     perror
my_print_defaults    myisampack    mysql_migrate_keyring      
mysqlbinlog          mysqld_multi  mysqlimport
myisam_ftdump        mysql         mysql_secure_installation  
mysqlcheck           mysqld_safe   mysqlshow

8.5.环境变量设定-可选

创建环境变量
[root@ubuntu24-16:softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@ubuntu24-16:softs]# source /etc/profile.d/mysql.sh
[root@ubuntu24-16:softs]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin

8.6.环境配置

创建主配置文件
[root@ubuntu24-16:~]# mkdir /usr/local/mysql/etc
[root@ubuntu24-16:~]# vim /usr/local/mysql/etc/my.cnf
[root@ubuntu24-16:~]# cat /usr/local/mysql/etc/my.cnf
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log

注意：
    大家在互联网上经常看到的认证属性已经被移除了，但是，该密码插件选项依然很管用。
    甚至默认的密码插件在集群操作的时候，不能用。
    default-authentication-plugin = mysql_native_password

mysqlx：
    传统的 MySQL 客户端 - 服务器通信主要基于经典的 SQL 接口。随着现代应用开发需求的变化，如对JSON 数据类型的更好支持、更高效的文档存储和检索、以及更适合现代编程语言的 API 等，MySQL 引入了X DevAPI。而 mysqlx 就是与这个新 API 相关的重要部分，它提供了新的通信协议和客户端库，使得开发
者可以更方便地使用 MySQL 数据库。
    使用 mysqlx 命令行客户端来测试和执行基于 X DevAPI 的操作。例如，通过该客户端可以使用新的语法进行数据库查询和操作。

注意：配置文件中涉及到的配置目录，必须存在，否则无法运行

创建数据目录

创建依赖目录
[root@ubuntu24-16:~]# mkdir /usr/local/mysql/{data,log}

更改文件属性
[root@ubuntu24-16:~]# chown -R mysql:mysql /usr/local/mysql

如果我们采用的是软连接的方式，上面赋权的时候，命令应该是：
chown -R mysql:mysql /usr/local/mysql*
注意：mysql后面有一个 *，如果不加，会导致很多的权限问题，比如
2024-12-08T06:32:09.439274Z 0 [ERROR] [MY-010187] [Server] Could not open file
'/data/server/mysql/logs/mysql.log' for error logging: Permission denied
2024-12-08T06:32:09.439380Z 0 [ERROR] [MY-013236] [Server] The designated data
directory /data/server/mysql/data/ is unusable. You can remove all files that
the server added to it.
2024-12-08T06:32:09.439412Z 0 [ERROR] [MY-010119] [Server] Aborting

8.7.环境初始化

8.7.1.密码初始化

初始化,本地root用户 - 使用密码

如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看
如果使用 --initialize-insecure -选项会生成空密码

[root@ubuntu24-16:~]# cd /usr/local/mysql/

[root@ubuntu24-16:mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@ubuntu24-16:mysql]# grep 'temporary password' /usr/local/mysql/log/error.log
2025-05-07T14:55:15.598967Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: god&ia6haBrC

8.7.2.空密码初始化

初始化,本地root用户 - 使用空密码

清理历史文件
[root@ubuntu24-16:~]# rm -rf /usr/local/mysql/data/*
[root@ubuntu24-16:~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@ubuntu24-16:~]# tail -f /usr/local/mysql/log/error.log
......
2025-05-07T14:58:05.616373Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2025-05-07T14:58:07.889872Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.

结果提示：使用的是空密码

确认文件
[root@ubuntu24-16:~]# ls /usr/local/mysql/data/
'#ib_16384_0.dblwr'     auto.cnf          client-key.pem   
mysql.ibd               public_key.pem    undo_001
'#ib_16384_1.dblwr'     ca-key.pem        ib_buffer_pool   
mysql_upgrade_history   server-cert.pem   undo_002
'#innodb_redo'          ca.pem            ibdata1          
performance_schema      server-key.pem
'#innodb_temp'          client-cert.pem   mysql            
private_key.pem         sys

8.8.服务脚本

定制启动脚本

该脚本不是systemd风格的脚本，但是可以被 systemd兼容
[root@ubuntu24-16:~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

重载配置文件
[root@ubuntu24-16:~]# systemctl daemon-reload

启动mysqld服务
[root@ubuntu24-16:~]# /etc/init.d/mysqld start
Starting mysqld (via systemctl): mysqld.service

查看自动生成的服务管理文件
[root@ubuntu24-16:~]# systemctl cat mysqld.service
# /run/systemd/generator.late/mysqld.service
# Automatically generated by systemd-sysv-generator

[Unit]
Documentation=man:systemd-sysv-generator(8)
SourcePath=/etc/init.d/mysqld
Description=LSB: start and stop MySQL
After=network-online.target
After=remote-fs.target
After=ypbind.service
After=nscd.service
After=ldap.service
After=ntpd.service
After=xntpd.service
Wants=network-online.target

[Service]
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
SuccessExitStatus=5 6
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d/mysqld stop
ExecReload=/etc/init.d/mysqld reload

检测服务状态
[root@ubuntu24-16:~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
     Loaded: loaded (/etc/init.d/mysqld; generated)
     Active: active (running) 

查看端口连接
[root@ubuntu24-16:~]# netstat -tunlp | grep mysql
tcp6       0      0 :::33060                :::*        LISTEN      7349/mysqld
tcp6       0      0 :::3306                 :::*        LISTEN      7349/mysqld

8.9.连接测试

客户端连接

[root@ubuntu24-16:~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL

查看当前的数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

修改密码
mysql> alter user root@'localhost' identified by 'Magedu';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

mysql用户账号由两部分组成：
    'USERNAME'@'HOST'
    dange@'10.0.0.100'
    dange@'10.0.0.%'
    dange@'%'
    说明：HOST限制此用户可通过哪些远程主机连接mysql服务器

确认修改密码效果

默认情况下，无法登录了
[root@ubuntu24-16:~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

使用用户名和密码，可以正常登录
[root@ubuntu24-16:~]# mysql -uroot -pMagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.4.0 MySQL Community Server - GPL

8.10.定制服务管理文件

cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=mysqld.service
After=network.target
After=syslog.target
 
[Install]
WantedBy=multi-user.target
 
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
LimitNOFILE = 10000
EOF

重载配置
[root@ubuntu24-16:~]# systemctl daemon-reload

启动服务
[root@ubuntu24-16:~]# systemctl start mysqld

检测效果
[root@ubuntu24-16:~]# systemctl start mysqld.service
[root@ubuntu24-16:~]# netstat -tunlp | grep 3306
tcp6       0      0 :::33060                :::*                    LISTEN      8503/mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      8503/mysqld
[root@ubuntu24-16:~]# systemctl status mysqld
● mysqld.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: enabled)
     Active: active (running) since Thu 2025-05-08 10:07:35 CST; 21s ago
   Main PID: 8503 (mysqld)
      Tasks: 36 (limit: 4552)
     Memory: 429.7M (peak: 443.2M)
        CPU: 2.465s
     CGroup: /system.slice/mysqld.service
             └─8503 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf

May 08 10:07:35 ubuntu24-16 systemd[1]: Started mysqld.service - MySQL Community Server.

9.Ubuntu中安装mysql9.2---二进制包安装

9.1.基础环境

安装依赖
[root@ubuntu24-13:~]# apt -y install libaio-dev numactl libnuma-dev libncurses-dev

注意：ubuntu24系统没有libaio1的包，需要单独去下载安装
[root@ubuntu24-13:~]# curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb
[root@ubuntu24-13:~]# dpkg -i libaio1_0.3.113-4_amd64.deb


否则环境初始化的时候，会发生如下报错
root@ubuntu24:mysql# bin/mysqld --initialize --user=mysql --
basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open
shared object file: No such file or directory

9.2.用户环境

创建组和用户
[root@ubuntu24-13:~]# groupadd -r mysql
[root@ubuntu24-13:~]# useradd -r -g mysql -s /sbin/nologin mysql

9.3.软件环境

获取软件
[root@ubuntu24-13:~]# mkdir -p /data/softs
[root@ubuntu24-13:~]# cd /data/softs
[root@ubuntu24-13:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz

解压至指定目录，这个目录只能写 /usr/local/
[root@ubuntu24-13:softs]# ls
mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz
[root@ubuntu24-13:softs]# tar xf mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz
[root@ubuntu24-13:softs]# mv mysql-9.2.0-linux-glibc2.28-x86_64 /usr/local/mysql

查看文件
[root@ubuntu24-13:softs]# ls /usr/local/mysql
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@ubuntu24-13:softs]# ls /usr/local/mysql/bin/
ibd2sdi            myisamchk      mysql_config                mysql_tzinfo_to_sql  
mysqld             mysqldump      mysqlslap
innochecksum       myisamlog      mysql_config_editor         mysqladmin           
mysqld-debug       mysqldumpslow  perror
my_print_defaults  myisampack     mysql_migrate_keyring       mysqlbinlog          
mysqld_multi       mysqlimport
myisam_ftdump      mysql           mysql_secure_installation  mysqlcheck           
mysqld_safe        mysqlshow

9.4.环境变量设定-可选

创建环境变量
[root@ubuntu24-13:~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@ubuntu24-13:~]# source /etc/profile.d/mysql.sh
[root@ubuntu24-13:~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin

9.5.环境配置

创建主配置文件
[root@ubuntu24-13:~]# mkdir /usr/local/mysql/etc
[root@ubuntu24-13:~]# vim /usr/local/mysql/etc/my.cnf
[root@ubuntu24-13:~]# cat /usr/local/mysql/etc/my.cnf
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log

注意：
    大家在互联网上经常看到的认证属性已经被移除了，但是，该密码插件选项依然很管用。
    甚至默认的密码插件在集群操作的时候，不能用。
    default-authentication-plugin = mysql_native_password

注意：配置文件中涉及到的配置目录，必须存在，否则无法运行

创建数据目录

创建依赖目录
[root@ubuntu24-13:~]# mkdir /usr/local/mysql/{data,log}

更改文件属性
[root@ubuntu24-13:~]# chown -R mysql:mysql /usr/local/mysql/

如果我们采用的是软连接的方式，上面赋权的时候，命令应该是：
chown -R mysql:mysql /usr/local/mysql*
注意：mysql后面有一个 *，如果不加，会导致很多的权限问题，比如
2025-05-08T06:32:09.439274Z 0 [ERROR] [MY-010187] [Server] Could not open file
'/data/server/mysql/logs/mysql.log' for error logging: Permission denied
2025-05-08T06:32:09.439380Z 0 [ERROR] [MY-013236] [Server] The designated data
directory /data/server/mysql/data/ is unusable. You can remove all files that
the server added to it.
2025-05-08T06:32:09.439412Z 0 [ERROR] [MY-010119] [Server] Aborting

9.6.环境初始化

9.6.1.密码初始化

初始化,本地root用户 - 使用密码

如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看
如果使用 --initialize-insecure -选项会生成空密码

[root@ubuntu24-13:~]# cd /usr/local/mysql/
[root@ubuntu24-13:mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@ubuntu24-13:mysql]# grep 'temporary password' /usr/local/mysql/log/error.log
2025-05-08T02:27:47.810610Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: msgQ:*jwk1Kb

9.6.2.空密码初始化

初始化,本地root用户 - 使用空密码

清理历史文件
[root@ubuntu24-13:mysql]# rm -rf /usr/local/mysql/data/*
[root@ubuntu24-13:mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@ubuntu24-13:mysql]# tail -f /usr/local/mysql/log/error.log
......
2025-05-08T02:30:16.497848Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2025-05-08T02:30:18.762028Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.

结果提示：使用的是空密码

确认文件
[root@ubuntu24-13:~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n"
#ib_16384_0.dblwr              #ib_16384_1.dblwr              #innodb_redo
#innodb_temp                   auto.cnf                       ca-key.pem
ca.pem                         client-cert.pem                client-key.pem
ib_buffer_pool                 ibdata1                        mysql
mysql.ibd                      mysql_upgrade_history          performance_schema
private_key.pem                public_key.pem                 server-cert.pem
server-key.pem                 sys                            undo_001
undo_002

9.7.服务脚本

定制启动脚本

该脚本不是systemd风格的脚本，但是可以被 systemd兼容
[root@ubuntu24-13:~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

重载配置文件
[root@ubuntu24-13:~]# systemctl daemon-reload

启动mysqld服务
[root@ubuntu24-13:~]# /etc/init.d/mysqld start
Starting mysqld (via systemctl): mysqld.service.

查看自动生成的服务管理文件
[root@ubuntu24-13:~]# systemctl cat mysqld.service
# /run/systemd/generator.late/mysqld.service
# Automatically generated by systemd-sysv-generator

[Unit]
Documentation=man:systemd-sysv-generator(8)
SourcePath=/etc/init.d/mysqld
Description=LSB: start and stop MySQL
After=network-online.target
After=remote-fs.target
After=ypbind.service
After=nscd.service
After=ldap.service
After=ntpd.service
After=xntpd.service
Wants=network-online.target

[Service]
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
SuccessExitStatus=5 6
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d/mysqld stop
ExecReload=/etc/init.d/mysqld reload

检测服务状态
[root@ubuntu24-13:~]# systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
     Loaded: loaded (/etc/init.d/mysqld; generated)
     Active: active (running) 

查看端口连接
[root@ubuntu24-13:~]# netstat -tunlp | grep mysql
tcp6       0      0 :::33060    :::*           LISTEN      8114/mysqld
tcp6       0      0 :::3306     :::*           LISTEN      8114/mysqld

9.8.连接测试

客户端连接
[root@ubuntu24-13:~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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>

查看当前的数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.2.0     |
+-----------+
1 row in set (0.00 sec)

修改密码
mysql> alter user root@'localhost' identified by 'Magedu';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

确认修改密码效果

默认情况下，无法登录了
[root@ubuntu24-13:~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)



使用用户名和密码，可以正常登录
[root@ubuntu24-13:~]# mysql -uroot -pMagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 9.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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>

9.9.定制服务管理文件

cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=mysqld.service
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
LimitNOFILE = 10000
EOF

重载配置
[root@ubuntu24-13:~]# systemctl daemon-reload

启动服务
[root@ubuntu24-13:~]# systemctl start mysqld

查看端口情况
[root@ubuntu24-13:~]# netstat -tunlp | grep 3306
tcp6       0      0 :::33060       :::*          LISTEN      8114/mysqld
tcp6       0      0 :::3306        :::*          LISTEN      8114/mysqld

查看服务状态信息
[root@ubuntu24-13:~]# systemctl status mysqld.service
● mysqld.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: enabled)
     Active: active (running) 

10.Rocky9中安装mysql8.0---二进制包安装

10.1.基础环境

安装依赖
[root@Rocky9-12 ~]# yum -y install libaio numactl-libs ncurses-compat-libs

10.2.用户环境

创建组和用户
[root@Rocky9-12 ~]# groupadd -r mysql
[root@Rocky9-12 ~]# useradd -r -g mysql -s /sbin/nologin mysql

10.3.软件环境

获取软件
[root@Rocky9-12 ~]# mkdir -p /data/softs
[root@Rocky9-12 ~]# cd /data/softs
[root@Rocky9-12 softs]#wget https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xzz

解压至指定目录，这个目录只能写 /usr/local/
[root@Rocky9-12 softs]# ls
mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz
[root@Rocky9-12 softs]# tar xf mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz
[root@Rocky9-12 softs]# mv mysql-9.2.0-linux-glibc2.28-x86_64 /usr/local/mysql

[root@Rocky9-12 softs]# ls /usr/local/mysql
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@Rocky9-12 softs]# ls /usr/local/mysql/bin/ | xargs -n 3 printf "%-30s %-30s %-30s\n"
ibd2sdi                        innochecksum                   myisamchk
myisam_ftdump                  myisamlog                      myisampack
my_print_defaults              mysql                          mysqladmin
mysqlbinlog                    mysqlcheck                     mysql_config
mysql_config_editor            mysqld                         mysqld-debug
mysqld_multi                   mysqld_safe                    mysqldump
mysqldumpslow                  mysqlimport                    mysql_migrate_keyring
mysql_secure_installation      mysqlshow                      mysqlslap

10.4.环境变量设定--可选

创建环境变量
[root@Rocky9-12 ~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@Rocky9-12 ~]# source /etc/profile.d/mysql.sh
[root@Rocky9-12 ~]# echo $PATH
/usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin

10.5.环境配置

创建主配置文件
[root@Rocky9-12 ~]# mkdir /usr/local/mysql/etc
[root@Rocky9-12 ~]# vim /usr/local/mysql/etc/my.cnf
[root@Rocky9-12 ~]# cat /usr/local/mysql/etc/my.cnf
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log

创建数据目录

创建依赖目录
[root@Rocky9-12 ~]# mkdir /usr/local/mysql/{data,log}

更改文件属性
[root@Rocky9-12 ~]# chown -R mysql:mysql /usr/local/mysql/

10.6.环境初始化

10.6.1.密码初始化

[root@Rocky9-12 ~]# cd /usr/local/mysql/
[root@Rocky9-12 mysql]# bin/mysqld -initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@Rocky9-12 mysql]# grep 'temporary password' /usr/local/mysql/log/error.log
2025-05-08T02:59:08.664100Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: cZYk#4#T)2k7

10.6.2.空密码初始化

初始化,本地root用户 - 使用空密码

清理历史文件
[root@Rocky9-12 mysql]# rm -rf /usr/local/mysql/data/*
[root@Rocky9-12 mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@Rocky9-12 mysql]# tail -f /usr/local/mysql/log/error.log
......
2025-05-08T03:00:45.561141Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2025-05-08T03:00:47.872042Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.

结果显示：为空密码

确认文件
[root@Rocky9-12 ~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n"
auto.cnf                       ca-key.pem                     ca.pem
client-cert.pem                client-key.pem                 #ib_16384_0.dblwr
#ib_16384_1.dblwr              ib_buffer_pool                 ibdata1
#innodb_redo                   #innodb_temp                   mysql
mysql.ibd                      mysql_upgrade_history          performance_schema
private_key.pem                public_key.pem                 server-cert.pem
server-key.pem                 sys                            undo_001
undo_002

10.7.定制服务管理文件

cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=mysqld.service
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
LimitNOFILE = 10000
EOF

重载配置
[root@Rocky9-12 ~]# systemctl daemon-reload

启动mysql
[root@Rocky9-12 ~]# systemctl start mysqld.service

查看端口连接
[root@Rocky9-12 ~]# netstat -tunlp | grep 3306
tcp6       0      0 :::33060   :::*          LISTEN      2052/mysqld
tcp6       0      0 :::3306    :::*          LISTEN      2052/mysqld

10.8.连接测试

[root@Rocky9-12 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> select version();
+-----------+
| version() |
+-----------+
| 9.2.0     |
+-----------+
1 row in set (0.00 sec)

修改密码
mysql> alter user root@'localhost' identified by 'Magedu';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

默认情况下，无法登录了
[root@Rocky9-12 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

使用用户名和密码，可以正常登录
[root@Rocky9-12 ~]# mysql -uroot -pMagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 9.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> exit
Bye

11.Rocky9-12二进制格式安装 MySQL 5.7.44

11.1.基础环境

安装依赖
[root@Rocky9-12 ~]# yum -y install libaio numactl-libs ncurses-compat-libs

11.2.准备用户

创建组和用户
[root@Rocky9-12 softs]# groupadd -r mysql
[root@Rocky9-12 softs]# useradd -r -g mysql -s /sbin/nologin mysql

11.3.软件环境

获取软件
[root@Rocky9-12 ~]# mkdir -p /data/softs
[root@Rocky9-12 ~]# cd /data/softs
[root@Rocky9-12 softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

解压至指定目录，这个目录只能写 /usr/local/
[root@Rocky9-12 softs]# ls
mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@Rocky9-12 softs]# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@Rocky9-12 softs]# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql

11.4.环境变量设定-可选

[root@Rocky9-12 softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@Rocky9-12 softs]# source /etc/profile.d/mysql.sh
[root@Rocky9-12 softs]# echo $PATH
/usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin

11.5.环境配置

创建主配置文件
[root@Rocky9-12 softs]# mkdir /usr/local/mysql/etc
[root@Rocky9-12 softs]# vim /usr/local/mysql/etc/my.cnf
[root@Rocky9-12 softs]# cat /usr/local/mysql/etc/my.cnf
[mysqld]
port=3306
socket = /usr/local/mysql/data/mysql.sock
user=mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
skip_name_resolve = 1
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log

[client]
socket = /usr/local/mysql/data/mysql.sock

11.6.创建数据目录，建议使用逻辑卷

创建依赖目录
[root@Rocky9-12 softs]# mkdir /usr/local/mysql/{data,log}

更改文件属性
[root@Rocky9-12 softs]# chown -R mysql:mysql /usr/local/mysql/

11.7.环境初始化

11.7.1.密码初始化

[root@Rocky9-12 ~]# cd /usr/local/mysql/bin
[root@Rocky9-12 bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@Rocky9-12 bin]# grep 'temporary password' /usr/local/mysql/log/error.log
2025-05-08T11:36:50.323892Z 1 [Note] A temporary password is generated for root@localhost: p>(D,oCOv1;U

11.7.2.空密码初始化

初始化,本地root用户 - 使用空密码

清理历史文件
[root@Rocky9-12 bin]# rm -rf /usr/local/mysql/data/*
[root@Rocky9-12 bin]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@Rocky9-12 bin]# tail /usr/local/mysql/log/error.log
......
2025-05-08T11:38:56.196531Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

结果显示：为空密码

确认文件
[root@Rocky9-12 bin]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n"
auto.cnf                       ca-key.pem                     ca.pem
client-cert.pem                client-key.pem                 ib_buffer_pool
ibdata1                        ib_logfile0                    ib_logfile1
mysql                          performance_schema             private_key.pem
public_key.pem                 server-cert.pem                server-key.pem
sys

11.8.定制服务管理文件

cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=mysqld.service
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
LimitNOFILE = 10000
EOF

重载配置
[root@Rocky9-12 bin]# systemctl daemon-reload

启动mysql
[root@Rocky9-12 bin]# systemctl start mysqld.service

查看端口连接
[root@Rocky9-12 bin]# netstat -tunlp | grep 3306
tcp6       0      0 :::3306    :::*      LISTEN      1935/mysqld

11.9.连接测试

[root@Rocky9-12 bin]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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>

查看当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

修改密码
mysql> alter user root@'localhost' identified by 'Magedu';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

使用用户名和密码进行登录
[root@Rocky9-12 bin]# mysql -uroot -pMagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> exit
Bye

12.ubuntu24-13二进制格式安装 MySQL 5.7.44

12.1.基础环境

安装依赖
[root@ubuntu24-13:~]# apt -y install libaio-dev

12.2.准备用户

创建组和用户
[root@ubuntu24-13:~]# groupadd -r mysql
[root@ubuntu24-13:~]# useradd -r -g mysql -s /sbin/nologin mysql

12.3.软件环境

获取软件
[root@ubuntu24-13:~]# mkdir -p /data/softs
[root@ubuntu24-13:~]# cd /data/softs
[root@ubuntu24-13:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

解压至指定目录，这个目录只能写 /usr/local/
[root@ubuntu24-13:softs]# ls
mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@ubuntu24-13:softs]# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@ubuntu24-13:softs]# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql

12.4.环境变量设定-可选

[root@ubuntu24-13:softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@ubuntu24-13:softs]# source /etc/profile.d/mysql.sh
[root@ubuntu24-13:softs]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin

12.5.环境配置

创建主配置文件
[root@ubuntu24-13:softs]# mkdir /usr/local/mysql/etc
[root@ubuntu24-13:softs]# vim /usr/local/mysql/etc/my.cnf
[root@ubuntu24-13:softs]# cat /usr/local/mysql/etc/my.cnf
[mysqld]
port=3306
user=mysql

character-set-server=utf8
collation-server=utf8_general_ci
default-storage-engine=INNODB

# Only allow connections from localhost  
bind-address = 0.0.0.0

#sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100
#skip-name-resolve

socket = /usr/local/mysql/data/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data

#日志
#日志时间,默认是UTC
log_timestamps=SYSTEM  
#错误日志
log-error = /usr/local/mysql/log/error.log
#开启二进制日志
log-bin=/usr/local/mysql/mysql-bin
server-id=1
#开启通用查询日志
#general_log=ON
#general_log_file=/usr/local/mysql/mysql-query.log
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/var/log/mysql/mysql-slow.log
long_query_time=1
#记录不使用索引查询的语句
log_queries_not_using_indexes=ON

#修改密码校验等级
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_policy=0
validate_password_length=4

#设置导入导出的安全目录
secure_file_priv=""

skip_name_resolve = 1
pid-file = /usr/local/mysql/data/mysqld.pid

[mysql]
default-character-set=utf8
auto-rehash

[client]
port=3306
socket = /usr/local/mysql/data/mysql.sock

其中log_error文件需要事先创建，同时注意文件权限为mysql

12.6.创建数据目录，建议使用逻辑卷

创建依赖目录
[root@ubuntu24-13:softs]# mkdir /usr/local/mysql/{data,log}

更改文件属性
[root@ubuntu24-13:softs]# chown -R mysql:mysql /usr/local/mysql/

12.7.环境初始化

密码初始化

[root@ubuntu24-13:~]# cd /usr/local/mysql/bin
[root@ubuntu24-13:~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

确认登录密码
[root@ubuntu24-13:~]# grep 'temporary password' /usr/local/mysql/log/error.log
2025-05-09T13:33:21.997935+08:00 1 [Note] A temporary password is generated for root@localhost: ,?ssSMZ)z419

报错:
[root@ubuntu24-13:~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决问题：
这是由于系统安装的版本过高。通过建立软连接方式解决
查看自己系统下对应的版本，建立软连接
[root@ubuntu24-13:~]# ldd /usr/local/mysql/bin/mysqld | grep libaio
        libaio.so.1 => not found
[root@ubuntu24-13:~]# find / -name libaio.so*
/usr/lib/x86_64-linux-gnu/libaio.so.1t64
/usr/lib/x86_64-linux-gnu/libaio.so.1t64.0.2
/usr/lib/x86_64-linux-gnu/libaio.so

# 创建软链接
[root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libaio.so /usr/lib/x86_64-linux-gnu/libaio.so.1
# 检查库是否存在
[root@ubuntu24-13:~]# ll /lib/x86_64-linux-gnu/libaio.so.1
lrwxrwxrwx 1 root root 35 May  9 13:25 /lib/x86_64-linux-gnu/libaio.so.1 -> /usr/lib/x86_64-linux-gnu/libaio.so
# 验证动态链接器能否找到库
[root@ubuntu24-13:~]# ldd /usr/local/mysql/bin/mysqld | grep libaio
        libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007c61e38ec000)

报错：
[root@ubuntu24-13:~]# mysql
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

[root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libncurses.so.6.4 /usr/lib/x86_64-linux-gnu/libncurses.so.5
[root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libtinfo.so.6.4 /usr/lib/x86_64-linux-gnu/libtinfo.so.5

确认文件
[root@ubuntu24-13:~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n"
auto.cnf                       ca-key.pem                     ca.pem
client-cert.pem                client-key.pem                 ib_buffer_pool
ib_logfile0                    ib_logfile1                    ibdata1
mysql                          performance_schema             private_key.pem
public_key.pem                 server-cert.pem                server-key.pem
sys

12.8.定制服务管理文件

cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=mysqld.service
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
LimitNOFILE = 10000
EOF

重载配置
[root@ubuntu24-13:~]# systemctl daemon-reload

启动mysql
[root@ubuntu24-13:~]# systemctl start mysqld.service

查看端口连接
[root@ubuntu24-13:~]# netstat -tunlp | grep 3306
tcp6       0      0 :::3306    :::*      LISTEN      1935/mysqld

12.9.连接测试

[root@ubuntu24-13:~]# mysql -uroot -p",?ssSMZ)z419"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log

Copyright (c) 2000, 2023, 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>

查看当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

修改密码并授予权限
mysql> alter user root@'localhost' identified by 'Mysql.123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to root@'%' identified by 'Mysql.123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[root@ubuntu24-13:~]# mysql -uroot -p'Mysql.123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> exit
Bye

13.通用二进制格式安装 MySQL 5.6.50

13.1.基础环境

安装依赖
[root@Rocky9-12 ~]# yum install -y perl-Data-Dumper autoconf libaio  perl-Sys-Hostname ncurses-compat-libs

常见错误：

缺少安装依赖，会报如下错误
[root@Rocky9-12 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:
Sys::Hostname

[root@Rocky9-12 ~]# mysql
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@Rocky9-12 ~]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

在 Rocky Linux 9 系统中，可以使用 dnf 命令安装 libncurses5 相关的包。不过 Rocky Linux 9 中可能没有直接提供 libncurses.so.5 版本，你可以尝试安装兼容的 ncurses 包：
sudo dnf install ncurses-compat-libs
这个包通常会包含 libncurses.so.5 或者提供其兼容的版本。

13.2.准备用户

创建组和用户
[root@Rocky9-12 ~]# groupadd -r mysql
[root@Rocky9-12 ~]# useradd -r -g mysql -s /sbin/nologin mysql

13.3.软件环境

获取软件
[root@Rocky9-12 ~]# mkdir -p /data/softs
[root@Rocky9-12 ~]# cd /data/softs
[root@Rocky9-12 softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

解压至指定目录，这个目录只能写 /usr/local/
[root@Rocky9-12 softs]# ls
mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
[root@Rocky9-12 softs]# tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
[root@Rocky9-12 softs]# mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql

13.4.环境变量设定--可选

[root@Rocky9-12 softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@Rocky9-12 softs]# source /etc/profile.d/mysql.sh
[root@Rocky9-12 softs]# echo $PATH
/usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin

13.5.环境配置

创建主配置文件
[root@Rocky9-12 softs]# mkdir /usr/local/mysql/etc
[root@Rocky9-12 softs]# vim /usr/local/mysql/etc/my.cnf
[root@Rocky9-12 softs]# cat /usr/local/mysql/etc/my.cnf
[mysqld]
port=3306
socket = /usr/local/mysql/data/mysql.sock
user=mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
innodb_file_per_table=on
skip_name_resolve = on
symbolic-links=0

[client]
socket = /usr/local/mysql/data/mysql.sock

[mysqld_safe]
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log

13.6.创建数据目录，建议使用逻辑卷

创建依赖目录
[root@Rocky9-12 softs]# mkdir /usr/local/mysql/{data,log}

更改文件属性
[root@Rocky9-12 softs]# chown -R mysql:mysql /usr/local/mysql/

13.7.环境初始化

[root@Rocky9-12 softs]# cd /usr/local/mysql
[root@Rocky9-12 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:
Sys::Hostname

确认文件
[root@Rocky9-12 mysql]# ll /usr/local/mysql/data/
total 110600
-rw-rw---- 1 mysql mysql 12582912 May  8 20:06 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May  8 20:06 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 May  8 20:06 ib_logfile1
drwx------ 2 mysql mysql     4096 May  8 20:06 mysql
drwx------ 2 mysql mysql     4096 May  8 20:06 performance_schema
drwxr-xr-x 2 mysql mysql       20 May  8 19:56 test

13.8.定制服务管理文件

cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=mysqld.service
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
LimitNOFILE = 10000
EOF

重载配置
[root@Rocky9-12 bin]# systemctl daemon-reload

启动mysql
[root@Rocky9-12 bin]# systemctl start mysqld.service

查看端口连接
[root@Rocky9-12 bin]# netstat -tunlp | grep 3306
tcp6       0      0 :::3306    :::*      LISTEN      1935/mysqld

13.9.连接测试

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

Copyright (c) 2000, 2020, 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>

查看当前版本信息
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.50    |
+-----------+
1 row in set (0.01 sec)

修改密码
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

-- 刷新权限表
FLUSH PRIVILEGES;
-- 修改 root 用户在 localhost 上的密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

使用用户名和密码进行登录
[root@Rocky9-12 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.50 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> exit
Bye

13.10.初始化脚本提高安全性

• 范例: 针对MySQL5.6前版本进行安全加固

运行脚本：mysql_secure_installation
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库

[root@Rocky9-12 ~]# mysql -uroot -pMagedu
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.50 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
|      | rocky9-12 |
| root | rocky9-12 |
+------+-----------+
6 rows in set (0.00 sec)

mysql> exit
Bye

[root@Rocky9-12 ~]# file `which mysql_secure_installation`
/usr/local/mysql/bin/mysql_secure_installation: Perl script text executable

做个软链接，默认文件为/tmp/mysql.sock，否则会报如下错误
下面直接指定socket文件路径，也不行
[root@Rocky9-12 ~]# ln -s /usr/local/mysql/data/mysql.sock /tmp/mysql.sock
[root@Rocky9-12 ~]# ll /tmp/mysql.sock
lrwxrwxrwx 1 root root 32 May  8 21:08 /tmp/mysql.sock -> /usr/local/mysql/data/mysql.sock

错误：

[root@Rocky9-12 ~]# /usr/local/mysql/bin/mysql_secure_installation --socket=/usr/local/mysql/data/mysql.sock                        


NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Enter current password for root (enter for none):
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Enter current password for root (enter for none):
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Unable to connect to the server as root user, giving up.
Cleaning up...

[root@Rocky9-12 ~]# /usr/local/mysql/bin/mysql_secure_installation



NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!




All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Cleaning up...

14.常见的报错信息

14.1.报错信息

[root@Rocky9-12 ~]#echo $PATH
/root/.local/bin:/root/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
[root@Rocky9-12 ~]# whereis mysqld
mysqld: /usr/local/mysql/bin/mysqld
[root@Rocky9-12 ~]# ll /usr/local/mysql/bin/mysqld
-rwxr-xr-x 1 mysql mysql 211661577 Oct 11  2023 /usr/local/mysql/bin/mysqld
[root@Rocky9-12 ~]# mysqld
-bash: /usr/local/mysql/bin/mysqld: No such file or directory

文件存在，但提示文件一直提示找不到

14.2.分析问题

当你使用 whereis 命令能找到 mysqld 的路径，并且通过 ll 命令能看到该文件确实存在，但执行 mysqld 时却提示 “No such file or directory”，这通常不是文件本身不存在，而是因为缺少该程序运行所需的依赖库或者动态链接库路径配置有误。下面为你详细分析可能的原因及解决办法：

1. 缺少 32 位兼容库
    如果 mysqld 是 32 位程序，而你的系统是 64 位的，可能会因为缺少 32 位兼容库而导致找不到依赖的动态链接器。

解决办法：
    在 Rocky Linux 9 系统中，你可以使用以下命令安装 32 位兼容库：
    dnf install -y glibc.i686

2. 动态链接库路径配置问题
    mysqld 程序可能依赖于某些特定的动态链接库，而这些库的路径没有被正确配置到系统的动态链接库搜索路径中。

解决办法：
    临时解决：使用 LD_LIBRARY_PATH 环境变量来临时指定动态链接库的搜索路径。例如，如果 mysqld 依赖的库在 /usr/local/mysql/lib 目录下，可以执行以下命令：
    export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH
    mysqld
    
    永久解决：创建一个新的.conf文件，将动态链接库的路径添加到该文件中，然后更新动态链接库缓存。
    echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
    ldconfig

3. 文件损坏
    mysqld 文件本身可能已经损坏，导致系统无法正确执行。

4. SELinux 或防火墙限制
    SELinux 或者防火墙可能会阻止 mysqld 程序的执行。
解决办法
    SELinux：可以临时禁用 SELinux 来进行测试：    
    setenforce 0
    如果禁用 SELinux 后 mysqld 能够正常执行，你可以修改 SELinux 的策略来允许 mysqld 运行。

防火墙：确保防火墙没有阻止 mysqld 所需的端口（默认是 3306）。可以使用以下命令开放端口：
    firewall-cmd --zone=public --add-port=3306/tcp --permanent
    firewall-cmd --reload

14.3.解决问题

按照以上步骤操作，应该能够解决 mysqld 执行时提示 “No such file or directory” 的问题。

最后发现自己下载的第一个32位软件
自己下错版本信息了
最后下载64位安装就成功了，再也没有提示错误了

endl