mysql数据库基础

mysql数据库基础

mysql简介

一、MySQL 概述：开源数据库的标杆

MySQL 是由瑞典公司 MySQL AB 开发（2008 年被 Sun 收购，后随 Sun 并入 Oracle）的开源关系型数据库管理系统（RDBMS），基于 SQL（结构化查询语言） 进行数据操作。因其高性能、可靠性和易用性，成为 Web 应用、大数据分析、云计算等场景的核心组件，全球市场份额长期位居前列。

二、核心特点：为什么选择 MySQL？

1. 开源免费：遵循 GPL（GNU 通用公共许可证），企业可免费使用、修改和分发，降低成本。
2. 跨平台支持：兼容 Windows、Linux、macOS 等主流操作系统，适配性强。
3. 高性能与扩展性
   • 支持事务（InnoDB 存储引擎）、索引优化（B+ 树索引）和查询缓存，处理百万级数据效率高。
   • 通过主从复制、集群（如 MySQL Cluster）实现读写分离和高可用。
4. 丰富的生态系统
   • 与 PHP、Python、Java 等编程语言无缝集成，支持主流框架（如 Django、Spring）。
   • 工具链完善：phpMyAdmin（可视化管理）、MySQL Workbench（设计与运维）、Navicat（跨平台客户端）。

三、架构与存储引擎：灵活的底层设计

1. 逻辑架构分层：

   • 连接层：处理客户端连接、认证和权限控制。
   • 服务层：解析 SQL、优化查询计划、管理缓存（如查询缓存、表缓存）。
   • 存储引擎层：核心组件，负责数据的存储与检索（如 InnoDB、MyISAM）。
   • 系统文件层：操作磁盘数据文件、日志文件等。

2. 主流存储引擎对比：

   引擎	事务支持	锁级别	适用场景
   InnoDB	支持	行级锁	高并发事务（如电商订单系统）
   MyISAM	不支持	表级锁	读多写少场景（如静态数据）
   Memory	不支持	表级锁	临时缓存（数据存储在内存）

四、应用场景：从小型网站到大型企业

1. Web 应用后端
   • 典型案例：WordPress、Drupal 等 CMS 系统默认使用 MySQL，Facebook、Twitter 早期也基于 MySQL 构建。
2. 电商与交易系统
   • 支持高并发事务（如订单处理、库存管理），通过 InnoDB 引擎保证数据一致性。
3. 大数据与分析
   • 与 Hadoop、Spark 等大数据框架集成，或通过 MySQL Cluster 处理海量数据。
4. 嵌入式系统
   • 轻量级部署（如物联网设备数据存储），占用资源少。

五、关键功能与技术

1. 事务与 ACID 特性
   • 支持原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），确保数据安全。
2. 索引与查询优化
   • 支持 B+ 树、哈希索引，通过 EXPLAIN 命令分析查询性能，优化 SQL 语句。
3. 复制与高可用
   • 主从复制：主库写入，从库读取，分担负载；
   • MHA（Master High Availability）：自动检测主库故障并切换，减少服务中断。
4. 安全机制
   • 基于用户权限的访问控制（GRANT/REVOKE）、SSL 加密传输、审计日志（MySQL Enterprise Edition）。

六、版本演进与分支

• 官方版本：由 Oracle 维护，当前稳定版为 8.0（新增窗口函数、CTE 公用表表达式、JSON 原生支持等）。
• 社区分支
  • MariaDB：MySQL 创始人主导的分支，兼容 MySQL 语法，新增 ColumnStore 存储引擎，适合分析场景。
  • Percona Server：优化性能与监控，支持更大并发和 InnoDB 增强特性。

安装mysql

二进制安装

准备环境

[root@localhost ~]# dnf -y install gcc libaio  numactl  openssl ncurses-compat-libs

解释：

各软件包的作用

1. gcc
   • 全称：GNU Compiler Collection（GNU 编译器集合）。
   • 作用：C、C++ 等编程语言的编译器，是开发和编译源码的基础工具。
   • 场景：编译 MySQL、Python 等源码包时必备。
2. libaio
   • 全称：Linux Native Asynchronous I/O Library（Linux 原生异步 I/O 库）。
   • 作用：提供异步 I/O 支持，允许程序在等待 I/O 操作完成时继续执行其他任务，提升性能。
   • 场景：高并发数据库（如 MySQL、PostgreSQL）依赖此库优化磁盘 I/O。
3. numactl
   • 全称：NUMA Control（非统一内存访问控制工具）。
   • 作用：优化多核服务器的内存访问，允许程序指定内存和 CPU 的亲和性（affinity）。
   • 场景：大型数据库或高性能计算（HPC）应用在 NUMA 架构服务器上的部署。
4. openssl
   • 作用：开源的加密库，提供 SSL/TLS 协议实现，用于数据传输加密和数字证书管理。
   • 场景：为 MySQL、Web 服务器（如 Nginx）等提供 HTTPS 加密功能。
5. ncurses-compat-libs
   • 全称：NCurses Compatibility Libraries（NCurses 兼容库）。
   • 作用：提供对旧版 NCurses 库的兼容支持，确保依赖旧库的程序能正常运行。
   • 场景：某些旧版应用或工具（如部分数据库客户端）可能依赖此库。

典型应用场景

1. 编译安装 MySQL
   MySQL 源码编译需要 gcc，运行时依赖 libaio 和 numactl 优化性能，openssl 用于加密连接。
2. 部署高性能服务器
   为数据库服务器（如 MySQL、PostgreSQL）或应用服务器（如 Nginx）安装必要的依赖库。
3. 开发环境配置
   搭建 C/C++ 开发环境，或安装依赖这些基础库的编程语言（如 Python、Ruby）。

注意事项

• 权限要求：必须以 root 用户执行，否则会提示权限不足。

• 系统兼容性

  ：

  • 适用于 Fedora、CentOS 8+、RHEL 8+ 等使用 dnf 的系统。
  • CentOS 7 及以下版本需使用 yum 替代 dnf。

• 网络连接：需确保服务器能访问官方软件源（如 CentOS 的 BaseOS 源）。

• 版本依赖

  • 若安装特定版本的软件（如 MySQL 8.0），可能需要额外的依赖（如 libaio-devel）。
  • 可通过 dnf info <package> 查看包详情

安装完可以使用 echo $?

[root@localhost ~]# echo $?
0
###参看上次返回状态0是正常1-255为有误

创建系统用户

[root@localhost ~]# useradd -M -s /sbin/nologin mysql
###-M不创建家目录   -s /sbin/nologin：设置用户的登录 Shell为/sbin/nologin，表示该用户无法交互式登录系统（即不能通过 SSH 或终端直接登录）。

把二进制mysql包上传到服务器

[root@localhost ~]# ls
anaconda-ks.cfg  mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz
[root@localhost ~]# tar xf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz 
[root@localhost ~]# mv mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local/mysql
[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost mysql]# mkdir data

###解压缩 并把解压缩好的文件移动到/usr/local/mysql下面别再当前再创建一个名为data的子目录

把文件所属主改为我们上面所创建的mysql用户

[root@localhost mysql]# chown -R mysql:mysql /usr/local/mysql/
[root@localhost mysql]# ll
总计 312
drwxr-xr-x.  2 mysql mysql   4096 2023年12月13日 bin
drwxr-xr-x.  2 mysql mysql   4096  4月 7日 15:59 data
drwxr-xr-x.  2 mysql mysql   4096 2023年12月13日 docs
drwxr-xr-x.  3 mysql mysql   4096 2023年12月13日 include
drwxr-xr-x.  6 mysql mysql   4096 2023年12月13日 lib
-rw-r--r--.  1 mysql mysql 279356 2023年12月13日 LICENSE
drwxr-xr-x.  4 mysql mysql   4096 2023年12月13日 man
-rw-r--r--.  1 mysql mysql    666 2023年12月13日 README
drwxr-xr-x. 28 mysql mysql   4096 2023年12月13日 share
drwxr-xr-x.  2 mysql mysql   4096 2023年12月13日 support-files
###chown 更改所属主 所属组 -R 递归

进行初始化

[root@localhost bin]# ./mysqld --initialize --user=mysql  --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2025-04-07T08:14:46.514442Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.36) initializing of server in progress as process 9547
2025-04-07T08:14:46.520612Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-07T08:14:46.682012Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-07T08:14:47.463894Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 3iAqHaIsS))N
###最后一步是生成一个临时密码这个是首次登录凭证

解释

• --initialize：
  初始化 MySQL 数据目录，创建系统表和初始数据。这一步必须在空目录执行（你之前已正确清空了 /usr/local/mysql/data）。
• --user=mysql：
  以 mysql 用户身份运行初始化过程，符合安全最佳实践。
• --basedir 和 --datadir：
  明确指定 MySQL 的安装路径和数据存储路径。

创建mysql配置文件

[root@localhost ~]# vim /etc/my.cnf
[client]
socket=/usr/local/mysql/data/mysql.sock

[mysqld]
socket=/usr/local/mysql/data/mysql.sock
bind-address=0.0.0.0
skip-name-resolve
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=2048
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M

解释 my.cnf

[client] 客户端配置

socket：指定客户端连接本地 MySQL 时使用的 Unix 套接字文件路径。

• 作用：通过文件系统而非网络进行本地通信，提高连接效率

[mysqld] 服务****网络与连接配置

1. socket
   • 服务器端套接字文件路径，必须与客户端配置一致。
2. bind-address=0.0.0.0
   • 监听所有可用网络接口（包括公网和内网）。
   • 安全提示：生产环境建议改为 127.0.0.1（仅本地访问）或特定 IP。
3. skip-name-resolve
   • 禁用反向 DNS 解析，加速客户端连接认证。
4. prot=3306
   • 端口：port=3306（MySQL 默认端口）。

路径配置

1. basedir=/usr/local/mysql
   • MySQL 安装根目录，用于查找二进制文件和库。
2. datadir=/usr/local/mysql/data
   • 数据文件存储目录，必须与初始化时的 --datadir 一致。

性能与容量配置

1. max_connections=2048
   • 最大允许的并发连接数，需根据服务器内存调整。
   • 过高可能导致内存溢出，建议配合 innodb_buffer_pool_size 使用。
2. max_allowed_packet=16M
   • 单个 SQL 语句或数据包的最大大小，影响大文件导入（如 LOAD DATA）。

字符集与存储引擎

1. character-set-server=utf8
   • 服务器默认字符集，建议改为 utf8mb4 以支持 emoji 等特殊字符。
2. default-storage-engine=INNODB
   • 默认表存储引擎，InnoDB 支持事务和外键，是主流选择。

配置 MySQL 服务的 systemd 单元文件这样就可以使用systemctl命令来启动管理mysql

vim /lib/systemd/system/mysqld.service

[Unit]
Description=mysql
After=network.target


[service]
Type=forking
ExecStart=/usr/local/mysqld start 
ExecReload=/usr/local/mysqld restart
ExecStop=/etc/init.d/mysqld stop
PrivateTmp=true



[Install]
WantedBy=multi-user.target

设置完以后使用systemctl daemon-reload 命令来重新加载systemd

解释

[Unit] 部分

[Unit]
Description=mysql
After=network.target

• Description：服务的简短描述，用于 systemctl status mysql 等命令显示信息。
• After=network.target：指定 MySQL 服务在网络服务启动后再启动（依赖网络连接）。

[Service] 部分

[Service]
Type=forking
ExecStart=/usr/local/mysqld start 
ExecReload=/usr/local/mysqld restart
ExecStop=/etc/init.d/mysqld stop
PrivateTmp=true

• Type=forking：指定服务的启动类型为 “后台运行”（通过 fork 子进程实现）。这是传统 UNIX 服务的常见启动方式。
• ExecStart：启动服务的命令（这里使用 /usr/local/mysqld start）。
• ExecReload：重新加载配置的命令（使用 restart 可能会导致服务短暂中断，通常建议使用 reload）。
• ExecStop：停止服务的命令（指向 SysVinit 脚本 /etc/init.d/mysqld）。
• PrivateTmp=true：为服务创建独立的临时文件目录（/tmp 和 /var/tmp），增强安全性。

[Install] 部分

[Install]
WantedBy=multi-user.target

• WantedBy=multi-user.target：指定服务在多用户模式下自动启动（相当于传统的运行级别 3、4、5）。当系统进入该模式时，MySQL 会被自动激活。

使用mysql刚才初始化的密码登录mysql

mysql -uroot -p 密码

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'pwd123';

解释

1. 用户标识 'root'@'localhost'
   • root：这是 MySQL 的超级管理员账户
   • localhost：限定了该用户只能通过本地连接（如 Unix 套接字或localhost的 TCP 连接）访问
   • 如果需要远程访问数据库，应使用'root'@'%'，不过从安全角度考虑，不建议给 root 账户开放远程权限
2. 认证插件 mysql_native_password
   • 这是 MySQL 传统的密码认证方式
   • 生成的密码会以明文形式存储在 mysql.user 表的 authentication_string 字段中
   • 这种认证方式兼容性强，适合大多数应用程序和工具
3. 密码设置 BY 'pwd123'
   • 新密码为pwd123
   • 在实际生产环境中，建议使用更复杂的密码，例如包含大小写字母、数字和特殊字符的组合
   • 密码长度建议至少 8 个字符