MySQL

扶疏525

209人浏览 · 2026-06-21 00:52:02

扶疏525 · 2026-06-21 00:52:02 发布

MySQL

实验环境：CentOS 7/8, MySQL 5.7/8.0

1. 数据库原理

1.1 数据的分类

根据数据结构类型，数据分为三类：

类型	说明	特点	示例
结构化数据	可用二维表逻辑表达的数据	先有结构再有数据；固定格式和有限长度	关系型数据库中的表
半结构化数据	介于结构化和非结构化之间	先有数据再有结构；包含标记分隔语义元素	JSON, XML, HTML
非结构化数据	没有固定结构的数据	无固定格式；整体存储	二进制文件、音视频、位置信息

结构化数据示例（二维表）：

| no | name | age |
|----|------|-----|
| 1  | 张三 | 123 |
| 2  | 李四 | 456 |

半结构化数据示例（JSON）：

[
  {"id": 1, "name": "张三", "age": 123},
  {"id": 2, "name": "李四", "age": 456}
]

1.2 数据管理发展历史

三个阶段对比

阶段	时间	特点
人工管理阶段	20世纪50年代中期以前	主要用于科学计算；外部存储只有磁带、打孔纸带；无专业数据管理软件；批处理
文件系统阶段	20世纪50年代后期至60年代中期	出现磁盘、磁鼓；基于文件系统的数据管理软件；可长期保存；可批处理或联机实时处理
数据库系统阶段	20世纪60年代后期至今	大容量磁盘；硬件价格下降；复杂结构化数据模型；高独立性、低冗余度

文件管理系统的优缺点

优点：文件形式多样化、数据长期保存、具有一定独立性
缺点：无统一接口、不支持并发访问、无安全控制、数据冗余不可避免

1.3 数据库管理系统（DBMS）

核心概念

概念	英文	说明
数据库	Database	按数据结构组织、存储和管理数据的仓库
数据库管理系统	DBMS	操纵和管理数据库的大型软件
数据库管理员	DBA	管理和维护DBMS的人员
应用程序	Application	执行某种功能的软件程序

DBMS基本功能

数据定义（DDL）：定义数据库的三级模式结构、两级映像、完整性约束
数据操作（DML）：实现数据的增删改查
数据组织、存储与管理：分类组织、存储和管理各种数据
数据库的运行管理：并发控制、安全性检查、完整性检查、事务管理
数据库的维护：数据载入、转换、转储、重组和重构
数据库的保护：恢复、并发控制、完整性控制、安全性控制
通信：与操作系统联机处理、网络通信

数据库管理系统发展

类型	特点	代表
层次数据库	树状模型；结构简单但缺乏灵活性；一对多关系	IMS
网状数据库	网络结构；多对多联系	IDS
关系型数据库	二维表格模型；行和列存储	Oracle, MySQL, SQL Server, DB2

1.4 关系型数据库理论

1.4.1 E-R模型

E-R图组成：

实体（Entity）：矩形表示，数据的使用者，客观存在的实物
属性（Attribute）：椭圆形表示，实体的特性
联系（Relationship）：菱形表示，属性之间的关联规则

1.4.2 联系类型

联系类型	符号	示例
一对一（1:1）	A ↔ B	人 ↔ 身份证；丈夫 ↔ 妻子
一对多（1:n）	A → 多个B	班级 → 多个学生；老师 → 多个学生
多对多（m:n）	A ↔ B	学生 ↔ 课程；商品 ↔ 订单

1.4.3 数据操作（CRUD）

操作	英文	SQL关键字	作用
增	Create	INSERT	增加数据
查	Read	SELECT	读取数据
改	Update	UPDATE	更新数据
删	Delete	DELETE	删除数据

1.4.4 数据库规范化（范式）

范式	核心目标	关键规则	说明
1NF	原子性	字段不可再分	每列都是不可分解的原子值
2NF	消除部分依赖	非主属性必须完全依赖整个主键	针对复合主键
3NF	消除传递依赖	非主属性之间不能相互依赖	非主键字段之间不能有依赖

💡 实际开发：一般满足3NF即可。越高范式，冗余越少，表越多。

1NF示例：

❌ 反例（违反1NF）：
| 学生ID | 联系方式              |
|--------|----------------------|
| 001    | 13800138000, zhang@example.com |

✅ 正例：
| 学生ID | 手机号       | 邮箱              |
|--------|-------------|-------------------|
| 001    | 13800138000 | zhang@example.com |

2NF示例：

❌ 反例（订单明细表，主键=(订单ID,商品ID)）：
| 订单ID | 商品ID | 商品名称 | 数量 |
|--------|--------|----------|------|
| 1001   | P001   | 苹果     | 5    |
→ "商品名称"只依赖"商品ID"，违反2NF

✅ 解决方法：拆表
商品表(商品ID, 商品名称)
订单明细表(订单ID, 商品ID, 数量)

3NF示例：

❌ 反例：
| 学生ID | 学院ID | 学院地址   |
|--------|--------|------------|
| 2001   | CS01   | 北京中关村 |
→ 学生ID → 学院ID → 学院地址，传递依赖

✅ 解决方法：拆表
学生表(学生ID, 学院ID)
学院表(学院ID, 学院地址)

1.4.5 SQL结构化查询语言

SQL不区分大小写（建议大写）
默认以 ; 结尾
关键词不能跨多行或简写
标准注释：-- 单行，/* */ 多行
MySQL注释：#

2. MySQL安装和基本使用

2.1 MySQL多实例

什么是多实例

在一台服务器上运行多个MySQL服务端进程，每个进程监听不同端口（3306, 3307, 3308），维护独立的配置和数据。

优点	缺点
节约硬件资源	资源抢占
便于对比测试	存在单点风险
便于统一管理

2.2 MySQL组成和常用工具

服务端程序

程序	功能
`mysqld_safe`	安全启动脚本
`mysqld`	服务端核心程序
`mysqld_multi`	多实例工具

客户端程序

程序	功能
`mysql`	交互式CLI工具
`mysqldump`	备份工具
`mysqladmin`	服务端管理工具
`mysqlimport`	数据导入工具

常用客户端选项

mysql -V                    # 显示版本
mysql -u用户名 -p密码        # 指定用户名密码
mysql -h主机 -P端口         # 指定主机和端口
mysql -e "SQL语句"          # 非交互式执行
mysql --prompt=名称         # 修改提示符

查看所有客户端命令

mysql> ?

查看所有服务端命令

mysql> help contents

查看服务端命令具体项

#例如查看Administration命令的具体项
mysql> help Administration

mysqladmin工具常用命令

mysqladmin -V               # 版本
mysqladmin status           # 状态信息
mysqladmin ping             # 心跳检测
mysqladmin shutdown         # 关闭服务
mysqladmin create 数据库名   # 创建数据库
mysqladmin drop 数据库名     # 删除数据库
mysqladmin password 新密码   # 修改密码

3. SQL语言

3.1 关系型数据库的构成

组件	关键字	说明
数据库	database	表的集合，文件系统中是一个目录
表	table	二维表形式，有行和列
索引	index	加快查询速度
视图	view	虚拟表，逻辑关联多个表
存储过程	procedure	完成特定功能的SQL语句集合
存储函数	function	可使用参数的SQL语句集合
触发器	trigger	满足触发条件时调用
事件调度器	event scheduler	计划任务
用户	user	连接服务端时的用户名
权限	privilege	用户对数据库的操作权限

3.2 SQL语言介绍

SQL语言规范

在数据库系统中，SQL语句不区分大小写，建议用大写。
SQL语句可单行或多行书写，默认以 **；**结尾。
关键词不能跨多行或简写。
用空格和TAB缩进来提高语句的可读性。
子句通常位于独立行，便于编辑，提高可读性。

SQL语句分类

类型	全称	说明	具体语句
DDL	Data Definition Language	数据定义语言	CREATE, DROP, ALTER
DML	Data Manipulation Language	数据操纵语言	INSERT, DELETE, UPDATE
DQL	Data Query Language	数据查询语言	SELECT
DCL	Data Control Language	数据控制语言	GRANT, REVOKE
TCL	Transaction Control Language	事务控制语言	BEGIN, COMMIT, ROLLBACK

3.3 管理数据库

查看数据库

SHOW DATABASES;

范例：

#默认这四个系统库，不能删除
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name 
  [DEFAULT CHARACTER SET utf8mb4];

修改数据库

ALTER DATABASE db_name CHARACTER SET utf8mb4;

删除数据库

DROP DATABASE [IF EXISTS] db_name;

3.4 数据类型

数值类型

类型	大小	范围（有符号）	用途
TINYINT	1 Bytes	(-128, 127)	小整数值
SMALLINT	2 Bytes	(-32768, 32767)	大整数值
MEDIUMINT	3 Bytes	(-8388608, 8388607)	大整数值
INT/INTEGER	4 Bytes	(-2147483648, 2147483647)	大整数值
BIGINT	8 Bytes	(-9.22E18, 9.22E18)	极大整数值
FLOAT	4 Bytes		单精度浮点
DOUBLE	8 Bytes		双精度浮点
DECIMAL(M,D)	依赖M和D		小数值（精确）

日期时间类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 ~ 838:59:59	HH:MM:SS	时间值
YEAR	1	1901 ~ 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期时间
TIMESTAMP	4	1970-01-01 00:00:01 UTC ~ 2038-01-19	YYYY-MM-DD HH:MM:SS	时间戳

字符串类型

类型	大小	用途
CHAR(n)	0-255 bytes	定长字符串
VARCHAR(n)	0-65535 bytes	变长字符串
TINYTEXT	0-255 bytes	短文本
TEXT	0-65535 bytes	长文本
MEDIUMTEXT	0-16777215 bytes	中等长度文本
LONGTEXT	0-4294967295 bytes	极大文本

列属性

关键字	含义
NULL / NOT NULL	允许/不允许为空
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增
UNSIGNED	无符号
CHARACTER SET	指定字符集

3.5 DDL语句（表操作）

创建表

CREATE TABLE [IF NOT EXISTS] tbl_name (
    col1 type1 修饰符,
    col2 type2 修饰符,
    PRIMARY KEY (col1),
    INDEX (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例：

CREATE TABLE student (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED,
    gender ENUM('M','F') DEFAULT 'M'
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

查看表

SHOW TABLES;                    -- 查看所有表
SHOW CREATE TABLE tbl_name;      -- 查看建表语句
DESC tbl_name;                  -- 查看表结构
SHOW COLUMNS FROM tbl_name;    -- 查看列信息
SHOW TABLE STATUS LIKE 'tbl';  -- 查看表状态

示例：

#查看
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| student2 |
+---------------+
2 rows in set (0.00 sec)

#查看结构
mysql> desc student2;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表

ALTER TABLE tbl_name RENAME TO new_name;           -- 修改表名
ALTER TABLE tbl_name ADD col type [AFTER col2];   -- 添加字段
ALTER TABLE tbl_name MODIFY col new_type;          -- 修改字段类型
ALTER TABLE tbl_name CHANGE col new_col new_type;  -- 修改字段名和类型
ALTER TABLE tbl_name DROP COLUMN col;              -- 删除字段
ALTER TABLE tbl_name ADD PRIMARY KEY (col);        -- 添加主键
ALTER TABLE tbl_name DROP PRIMARY KEY;             -- 删除主键

修改和删除表

ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值>丨DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

DROP TABLE [IF EXISTS] tbl_name;

3.6 DML语句（数据操作）

插入数据

-- 插入单条
INSERT INTO tbl_name (col1, col2) VALUES (val1, val2);

-- 插入多条
INSERT INTO tbl_name (col1, col2) VALUES (v1, v2), (v3, v4), (v5, v6);

-- 插入查询结果
INSERT INTO tbl1 (col1, col2) SELECT col1, col2 FROM tbl2 WHERE condition;

-- 存在则更新，不存在则插入
INSERT INTO tbl_name (id, name) VALUES (12, 'zhangsan') 
ON DUPLICATE KEY UPDATE name='zhangsan';

更新数据

UPDATE tbl_name SET col1=val1, col2=val2 WHERE condition;

⚠️ 重要：更新一定要加WHERE条件，否则会更新所有记录！
mysql -U  # 启用safe-updates模式，强制要求WHERE中使用KEY

删除数据

DELETE FROM tbl_name WHERE condition;
TRUNCATE TABLE tbl_name;  -- DDL语句，效率更高，不支持事务

💡 生产环境建议：使用逻辑删除（is_del字段标记），而非物理删除。

3.7 DQL语句（数据查询）

基本查询

SELECT * FROM tbl_name;                           -- 查询所有
SELECT col1, col2 FROM tbl_name;                  -- 查询指定列
SELECT col1 AS 别名 FROM tbl_name;                 -- 别名
SELECT DISTINCT col FROM tbl_name;                -- 去重

条件查询（WHERE）

SELECT * FROM stu WHERE id = 11;
SELECT * FROM stu WHERE id IN (11, 15, 19);
SELECT * FROM stu WHERE id BETWEEN 12 AND 15;
SELECT * FROM stu WHERE name IS NULL;             -- NULL判断用IS
SELECT * FROM stu WHERE name IS NOT NULL;
SELECT * FROM stu WHERE name LIKE 'xiao%';        -- %任意长度
SELECT * FROM stu WHERE name LIKE '%ng';          -- _单个字符

逻辑运算符

-- AND (可写为 &&)
SELECT * FROM stu WHERE id >= 11 AND id < 14;

-- OR (可写为 |)
SELECT * FROM stu WHERE id <= 12 OR id > 19;

-- NOT
SELECT * FROM stu WHERE id NOT BETWEEN 12 AND 15;
SELECT * FROM stu WHERE id NOT IN (12, 15, 19);

聚合函数

SELECT COUNT(*) FROM stu;          -- 统计记录数
SELECT COUNT(name) FROM stu;       -- 忽略NULL
SELECT MAX(id), MIN(id), AVG(age) FROM stu;
SELECT SUM(age) FROM stu;

分组（GROUP BY）

SELECT gender, COUNT(*), AVG(age) FROM stu 
GROUP BY gender;

-- 分组后过滤用HAVING
SELECT gender, COUNT(*) AS total FROM stu 
GROUP BY gender 
HAVING total >= 3;

💡 规则：一旦使用GROUP BY，SELECT后只能跟分组字段和聚合函数。

排序（ORDER BY）

SELECT * FROM stu ORDER BY id;           -- 升序（默认）
SELECT * FROM stu ORDER BY id ASC;       -- 升序
SELECT * FROM stu ORDER BY id DESC;      -- 降序

分页（LIMIT）

SELECT * FROM stu LIMIT 0, 3;    -- 第1页，每页3条（从0开始）
SELECT * FROM stu LIMIT 3, 3;    -- 第2页
SELECT * FROM stu LIMIT 6, 3;    -- 第3页

3.7.2 多表查询

7种连接方式总结

连接类型	语法	结果说明
交叉连接	`CROSS JOIN`	笛卡尔积，所有行两两组合
内连接	`INNER JOIN ... ON`	只保留两边匹配的数据（交集）
左连接	`LEFT JOIN ... ON`	左表全部保留，右表匹配不上显示NULL
右连接	`RIGHT JOIN ... ON`	右表全部保留，左表匹配不上显示NULL
全连接	`LEFT JOIN UNION RIGHT JOIN`	左右都保留（并集），MySQL需模拟
自连接	`SELF JOIN`	自己连自己，处理层级关系
联合查询	`UNION / UNION ALL`	上下拼接结果集

内连接示例

SELECT wj.wanjia_nicheng, yx.yingxiong_ming, zj.shenglv
FROM wanjia wj
INNER JOIN zhanji zj ON wj.wanjia_id = zj.wanjia_id
INNER JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id;

左连接示例

SELECT wj.wanjia_nicheng, yx.yingxiong_ming, zj.shenglv
FROM wanjia wj
LEFT JOIN zhanji zj ON wj.wanjia_id = zj.wanjia_id
LEFT JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id;

自连接示例

SELECT 
    u.wanjia_nicheng AS 玩家,
    inviter.wanjia_nicheng AS 邀请人
FROM wanjia u
LEFT JOIN wanjia inviter ON u.yaoqingren_id = inviter.wanjia_id;

3.8 MySQL用户管理

用户组成

MySQL用户 = 用户名@主机，主机限制了可登录位置。

-- 创建用户
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

-- 示例
CREATE USER 'test'@'192.168.108.%' IDENTIFIED BY '123456';
CREATE USER 'test'@'%' IDENTIFIED BY '123456';  -- 任意主机

-- 重命名
RENAME USER 'old'@'host' TO 'new'@'host';

-- 删除用户
DROP USER '用户名'@'主机';

-- 修改密码
ALTER USER 'user'@'host' IDENTIFIED BY '新密码';
SET PASSWORD FOR 'user'@'host' = '新密码';  -- MySQL 8.0前可用

3.9 权限管理（DCL）

授权

GRANT 权限列表 ON 数据库.表 TO '用户'@'主机' [WITH GRANT OPTION];

-- 示例
GRANT SELECT, INSERT ON db1.student TO 'root'@'192.168.108.%';
GRANT ALL ON wordpress.* TO 'wordpresser'@'192.168.108.%';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

取消权限

REVOKE 权限 ON 数据库.表 FROM '用户'@'主机';

查看权限

SHOW GRANTS FOR '用户'@'主机';
SHOW GRANTS FOR CURRENT_USER();

刷新权限

FLUSH PRIVILEGES;

3.10 图形化管理工具

工具	特点
Navicat	功能强大，商业软件
SQLyog	轻量快速
DBeaver	开源免费，支持多种数据库

3.11 LAMP架构与WordPress

LAMP = Linux + Apache + MySQL + PHP

WordPress安装步骤

# 1. 安装Apache和PHP
yum -y install httpd php php-mysqlnd php-json php-gd php-xml php-mbstring php-zip

# 2. 下载WordPress
wget https://cn.wordpress.org/latest-zh_CN.zip
unzip latest-zh_CN.zip
chown -R apache.apache wordpress/

# 3. MySQL创建数据库和用户
CREATE DATABASE wordpress;
CREATE USER 'wordpresser'@'192.168.108.%' IDENTIFIED BY '123456';
GRANT ALL ON wordpress.* TO 'wordpresser'@'192.168.108.%';

# 4. 配置Apache虚拟主机
# /etc/httpd/conf.d/blog.gqd.cloud.conf

4. MySQL 5.x 源码安装

环境准备

# 安装依赖
[root@mysql01 ~]# yum -y install ncurses ncurses-devel bison cmake gcc gcc-c++

# 创建用户
[root@mysql01 ~]# useradd -s /sbin/nologin mysql

# 解压源码
[root@mysql01 ~]# tar zxvf mysql-5.7.17.tar.gz -C /opt/
[root@mysql01 ~]# tar zxvf boost_1_59_0.tar.gz -C /usr/local/
[root@mysql01 ~]# cd /usr/local/
[root@mysql01 ~]# mv boost_1_59_0 boost

CMake配置

#配置安装环境
[root@mysql01 ~]# cd /opt/mysql-5.7.17/
[root@mysql01 mysql-5.7.17]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql   
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock   
-DSYSCONFDIR=/etc   
-DSYSTEMD_PID_DIR=/usr/local/mysql   
-DDEFAULT_CHARSET=utf8   
-DDEFAULT_COLLATION=utf8_general_ci   
-DWITH_INNOBASE_STORAGE_ENGINE=1   
-DWITH_ARCHIVE_STORAGE_ENGINE=1   
-DWITH_BLACKHOLE_STORAGE_ENGINE=1   
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1   
-DMYSQL_DATADIR=/usr/local/mysql/data   
-DWITH_BOOST=/usr/local/boost   
-DWITH_SYSTEMD=1

#注释
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ #客户端连服务的通讯文件
-DSYSCONFDIR=/etc \ #mysql的配置文件
-DSYSTEMD_PID_DIR=/usr/local/mysql \ #pid进程文件存放位置
-DDEFAULT_CHARSET=utf8 \ #字符集格式
-DDEFAULT_COLLATION=utf8_general_ci \ #排序规则
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #开启支持的存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \ #数据存放位置
-DWITH_BOOST=/usr/local/boost \ #底层的c++支持库位置
-DWITH_SYSTEMD=1 #id号

编译安装

#编译和安装
[root@mysql01 mysql-5.7.17]# make && make install
#递归配置目录所属主和组
[root@mysql01 ~]# chown -R mysql.mysql /usr/local/mysql/

配置文件

#编辑mysql的配置文件，直接全替换
[root@mysql01 ~]# vim /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

初始化与启动

#配置文件所属主和组
[root@mysql01 ~]# chown mysql:mysql /etc/my.cnf
#配置环境变量
[root@mysql01 ~]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@mysql01 ~]# echo 'export PATH' >> /etc/profile

#加载修改过的配置
[root@mysql01 ~]# source /etc/profile

#数据库初始化
[root@mysql01 ~]# cd /usr/local/mysql/
[root@mysql01 mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

# 启动服务
[root@mysql01 mysql]# cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@mysql01 mysql]# systemctl daemon-reload
[root@mysql01 mysql]# systemctl start mysqld
[root@mysql01 mysql]# systemctl enable mysqld

#给root账号设置密码为huawei
[root@mysql01 mysql]# mysqladmin -uroot password "huawei"

#登录mysql
[root@mysql01 ~]# mysql -uroot -phuawei

#授权远程登录
#授权远程登录
mysql> grant all privileges on *.* to 'root'@'%' identified by 'huawei' with grant option;

5. MySQL的备份与恢复

5.1 备份分类

分类维度	类型	说明
物理/逻辑	物理备份	备份物理文件（数据文件、日志文件）
	逻辑备份	备份逻辑组件（表、SQL语句）
冷/热/温	冷备份	关闭数据库时备份
	热备份	数据库运行中备份，依赖日志
	温备份	锁定表格（可读不可写）时备份

5.2 冷备份

# 备份（推荐写法）
[root@mysql01 ~]# systemctl stop mysqld # 先停止服务
[root@mysql01 ~]# cd /usr/local/mysql/data
[root@mysql01 data]# mkdir /mysql_bak
[root@mysql01 data]# tar czf /mysql_bak/mysql-backup-$(date +%F).tar.gz *
[root@mysql01 data]# systemctl start mysqld # 备份完成后启动服务
#测试服务正常
# 删除数据
[root@mysql01 ~]# systemctl stop mysqld # 先停止服务
[root@mysql01 ~]# rm -rf /usr/local/mysql/data/* # 清空数据目录（谨慎操作！）
#再次开启测试，发现数据库坏了
[root@mysql01 ~]# systemctl start mysqld
#停止数据库，恢复数据库
[root@mysql01 ~]# systemctl stop mysqld
[root@mysql01 ~]# tar xzf /mysql_bak/mysql-backup-2025-10-15.tar.gz -C /usr/local/mysql/data/
[root@mysql01 ~]# chown -R mysql:mysql /usr/local/mysql/data # 恢复权限
[root@mysql01 ~]# systemctl start mysqld # 启动服务
#再次测试，发现数据库恢复了

5.3 逻辑备份（mysqldump）

#备份数据库
[root@mysql01 ~]# systemctl start mysqld
[root@mysql01 ~]# mysqldump -u root -p school > /mysql_bak/school.sql
Enter password:
[root@mysql01 ~]# ls /mysql_bak/school.sql
mysql_bak/school.sql
[root@mysql01 ~]# cat /mysql_bak/school.sql

#备份多个数据库
[root@mysql01 ~]# mysqldump -u root -p --databases school mysql >
/mysql_bak/school-mysql.sql
Enter password:
[root@mysql01 ~]# cat /mysql_bak/school-mysql.sql
#备份所有数据库
[root@mysql01 ~]# mysqldump -u root -p --opt --all-databases > /mysql_bak/all.sql
Enter password:
[root@mysql01 ~]# cat /mysql_bak/all.sql
#备份整个表
[root@mysql01 ~]# mysqldump -u root -p school info > /mysql_bak/info.sql
Enter password:
[root@mysql01 ~]# cat /mysql_bak/info.sql

5.4 恢复数据

-- 方法1：source命令
mysql> use school;
mysql> source /mysql_bak/school.sql;

-- 方法2：命令行导入
mysql -u root -p school < /mysql_bak/school.sql

5.5 增量备份（二进制日志）

#开启二进制日志
[root@mysql01 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #增加这行

[root@web-server ~]# systemctl restart mysqld.service
[root@mysql01 ~]# ls /usr/local/mysql/data/
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.index public_key.pem server-key.pem
ca-key.pem client-key.pem ib_logfile0 mysql performance_schema school sys
ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 private_key.pem server-cert.pem

#先进行完整性备份
[root@mysql01 ~]# mysqldump -uroot -p school > /opt/school.sql

#日志刷新生效
[root@mysql01 ~]# mysqladmin -uroot -p flush-logs

#新产生的mysql-bin.000002只记录上次刷新后的操作
[root@mysql01 ~]# ls /usr/local/mysql/data/
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000002 private_key.pem server-cert.pem
ca-key.pem client-key.pem ib_logfile0 mysql mysql-bin.index public_key.pem server-key.pem
ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema school sys

# 4. 查看binlog内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

# 5. 恢复（基于binlog）
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p

6. 主从复制和读写分离

6.1 主从复制核心原理

三个核心组件：

binlog（二进制日志）—— 主库
IO线程 —— 从库
SQL线程 —— 从库

完整流程：

主库执行增删改（INSERT/UPDATE/DELETE）
MySQL把操作记录到binlog
从库IO线程连接主库，请求binlog
主库dump线程把binlog发给从库
从库IO线程将内容写入relay log（中继日志）
从库SQL线程读取relay log，重放SQL语句
两个Yes代表正常：Slave_IO_Running: Yes，Slave_SQL_Running: Yes

使用目标：数据冗余和灾难恢复；提升并发能力、避免锁冲突。

在这里插入图片描述

6.2 主从配置

实验拓扑图

在这里插入图片描述

使用 Centos-7模板克隆产生应用客户端和amoeba

使用 mysql模板克隆产生mysql主服务器，mysql从节点1，mysql从节点2

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

主机名	IP地址	作用
mysql-master	192.168.108.101	mysql主服务器
mysql-slave01	192.168.108.102	mysql从节点1
mysql-slave02	192.168.108.103	mysql从节点2
amoeba	192.168.108.110	amoeba
mysql-client	192.168.108.111	应用客户端

时间同步

通过时间戳实现业务的一致性

# 所有节点
ntpdate ntp.aliyun.com
date -R
systemctl disable firewalld --now
setenforce 0

主服务器配置

[root@mysql-master ~]# vim /etc/my.cnf
server-id = 11
log-bin = master-bin #主服务器日志文件
log-slave-updates = true #从服务器更新二进制日志
[root@mysql-master ~]# systemctl restart mysqld

-- 授权从库同步用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.108.%' IDENTIFIED BY '123456';

mysql> FLUSH PRIVILEGES;

-- 查看主库状态
mysql> show master status;
-- 记录 File 和 Position

从服务器配置

mysql-slave01,mysql-slave02都要做如下操作

#主从是克隆的要做这个操作，否则UUID一致
[root@mysql-slave01 ~]# systemctl stop mysqld
[root@mysql-slave01 ~]# rm -f /usr/local/mysql/data/auto.cnf
[root@mysql-slave01 ~]# systemctl start mysqld

[root@mysql-slave01 ~]# vim /etc/my.cnf
server-id = 22 #另外一台为23
relay-log = relay-log-bin #从主服务器上同步日志文件记录到本地
relay-log-index = slave-relay-bin.index #定义relay-log的位置和名称
[root@mysql-slave01 ~]# systemctl restart mysqld

-- 配置主从同步
mysql> CHANGE MASTER TO
  master_host='192.168.108.101',
  master_user='myslave',
  master_password='123456',
  master_log_file='master-bin.000001',
  master_log_pos=604;#master_log_file，master_log_pos与前面查询的相同

START SLAVE;

-- 查看状态
SHOW SLAVE STATUS\G;
-- 确认 Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes

6.3 读写分离（Amoeba）

核心原理：

写操作（INSERT/UPDATE/DELETE）→ 走主库
读操作（SELECT）→ 走从库（负载均衡）

工作流程（读写分离）

应用连接 Amoeba 代理（默认端口 8066），而非直接连 MySQL。
Amoeba 解析 SQL：
- 写操作 → 转发到配置的 主库池（writePool）。
- 读操作 → 转发到配置的 从库池（readPool），并按轮询 / 权重做负载均衡。
主从复制保证从库数据与主库一致，应用无感知。

核心组件（配置文件）

Amoeba 核心配置在 $AMOEBA_HOME/conf/ 下，共 7 个，最关键 2 个：

配置文件	作用
amoeba.xml	代理核心配置：端口、认证、读写池、路由规则、线程池
dbServers.xm	定义后端 MySQL 节点（主 / 从）、连接信息、连接池

路由规则（读写分离核心）

在 amoeba.xml 中配置：

writePool ：指定写操作转发的主库节点池。
readPool ：指定读操作转发的从库节点池（多从库自动轮询）。
规则优先级： readPools/writePools > defaultPools 。

Amoeba配置要点：

安装JDK和Amoeba

[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin
[root@amoeba ~]# ./jdk-6u14-linux-x64.bin
[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6

# 配置环境变量
[root@amoeba ~]# vim /etc/profile
#最下面加一下内容
export JAVA_HOME=/usr/local/jdk1.6
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba ~]# source /etc/profile

在三台mysql上添加权限开放给amoeba访问

#amooba访问数据库的账号
#mysql-master
mysql> grant all on *.* to test@'192.168.108.%' identified by '123.com';
#mysql-slave01
mysql> grant all on *.* to test@'192.168.108.%' identified by '123.com';
#mysql-slave02
mysql> grant all on *.* to test@'192.168.108.%' identified by '123.com';

配置amoeba.xml（核心路由）

[root@amoeba ~]# cd /usr/local/amoeba/
[root@amoeba amoeba]# vim conf/amoeba.xml
<!-- 客户端连接账号 -->
<property name="user">amoeba</property>
<property name="password">123456</property>

<!-- 读写池配置 -->
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>

配置dbServers.xml（后端MySQL节点）

[root@amoeba amoeba]# vim conf/dbServers.xml
<!-- 主库 -->
<dbServer name="master" parent="abstractServer">
  <property name="ipAddress">192.168.108.101</property>
</dbServer>

<!-- 从库1 -->
<dbServer name="slave1" parent="abstractServer">
  <property name="ipAddress">192.168.108.102</property>
</dbServer>

<!-- 从库2 -->
<dbServer name="slave2" parent="abstractServer">
  <property name="ipAddress">192.168.108.103</property>
</dbServer>

<!-- 从库池 -->
<dbServer name="slaves" virtual="true">
  <property name="poolNames">slave1,slave2</property>
</dbServer>

启动Amoeba

[root@amoeba ~]# /usr/local/amoeba/bin/amoeba start&
[root@amoeba ~]# netstat -anpt | grep java  # 确认监听8066端口

客户端连接测试

[root@mysql-client ~]# mysql -u amoeba -p123456 -h 192.168.108.110 -P 8066

7. MHA高可用

7.1 MHA概述

全称：Master High Availability
开发者：Yoshinori Matsunobu（日本）
实现语言：Perl
核心功能：自动故障转移（0-30秒），解决主库单点故障
组成：
- MHA Manager（管理节点）
- MHA Node（数据节点）

7.2 实验环境

使用centos7模板克隆产生mha节点

使用mysql模板克隆产生mysql-master，mysql-slave01，mysql-slave02

在这里插入图片描述

主机名	IP地址	作用
mysql-master	192.168.108.131	主服务器，安装node
mysql-slave01	192.168.108.132	从节点1，安装node
mysql-slave02	192.168.108.133	从节点2，安装node
mha	192.168.108.130	管理节点，安装node+manager

7.3 安装步骤

1. 前置环境（所有节点）

yum install epel-release --nogpgcheck -y
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch   perl-Parallel-ForkManager perl-ExtUtils-CBuilder   perl-ExtUtils-MakeMaker perl-CPAN

2. 安装MHA Node（所有节点）

tar zxvf mha4mysql-node-0.57.tar.gz
cd mha4mysql-node-0.57
perl Makefile.PL
make && make install

3. 安装MHA Manager（仅mha节点）

tar zxvf mha4mysql-manager-0.57.tar.gz
cd mha4mysql-manager-0.57
perl Makefile.PL
make && make install

！注意：一定要先安装node 组件才能安装manager 组件

4. 配置无密码SSH认证

# mha节点
ssh-keygen -t rsa
ssh-copy-id 192.168.108.131
ssh-copy-id 192.168.108.132
ssh-copy-id 192.168.108.133

# 各MySQL节点之间也互相配置

5. MySQL配置

在三台 MySQL 节点上分别操作

yum -y install perl-Module-Install
systemctl stop mysqld
rm -f /usr/local/mysql/data/auto.cnf
systemctl start mysqld
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
ln -s /usr/local/mysql/bin/mysql /usr/bin/
systemctl restart mysqld

主库：

[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
server-id = 1 #三台服务器的 server-id 不能一样
log_bin = master-bin
log-slave-updates = true

从库1：

[root@mysql-slave01 ~]# vim /etc/my.cnf
#default-character-set=utf8
[mysqld]
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

从库2：

[root@mysql-slave02 ~]# vim /etc/my.cnf
#default-character-set=utf8
[mysqld]
server-id = 3
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

mysql5.7注意

请注释/etc/my.cnf 中【client】下 #default-character-set=utf8

一定要注释，否则报错

6. 授权用户（所有MySQL节点）

-- 从库同步用户
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.108.%' IDENTIFIED BY '123';

-- MHA管理用户
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'192.168.108.%' IDENTIFIED BY 'manager';

-- 主机名授权（避免MHA检查报错）
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'mysql-master' IDENTIFIED BY 'manager';
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'mysql-slave01' IDENTIFIED BY 'manager';
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'mysql-slave02' IDENTIFIED BY 'manager';

FLUSH PRIVILEGES;

7. 配置主从同步

-- 在主库查看状态
SHOW MASTER STATUS;
-- 记录 File 和 Position

-- 在从库执行
CHANGE MASTER TO
  master_host='192.168.108.131',
  master_user='myslave',
  master_password='123',
  master_log_file='master-bin.000001',
  master_log_pos=1897;

START SLAVE;

-- 设置只读
SET GLOBAL read_only=1;

8. 配置MHA

在 manager 节点上复制相关脚本到/usr/local/bin 目录。

[root@mha ~]# cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
#拷贝后会有四个执行文件
[root@mha ~]# ll /usr/local/bin/scripts/
总用量 32
-rwxr-xr-x 1 mysql mysql 3648 5 月 31 2015 master_ip_failover #自动切换时 VIP 管理的脚本
-rwxr-xr-x 1 mysql mysql 9872 5 月 25 09:07 master_ip_online_change #在线切换时 vip的管理
-rwxr-xr-x 1 mysql mysql 11867 5 月 31 2015 power_manager #故障发生后关闭主机的脚本
-rwxr-xr-x 1 mysql mysql 1360 5 月 31 2015 send_report #因故障切换后发送报警的脚本

配置VIP漂移脚本 /usr/local/bin/master_ip_failover：

复制上述的自动切换时 VIP 管理的脚本到/usr/local/bin 目录，这里使用脚本管理 VIP

[root@mha ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin

修改内容如下：（删除原有内容，替换成以下内容）

[root@mha ~]# vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);

#############################VIP配置区##############################
my $vip = '192.168.108.200';
my $netmask = '24';
my $brdc = '192.168.108.255';
my $ifdev = 'ens33';
my $key = '1';
# 现代ip命令绑定VIP + 发送免费ARP刷新缓存
my $ssh_start_vip = "/usr/sbin/ip addr add $vip/$netmask brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 3 -I $ifdev $vip";
my $ssh_stop_vip  = "/usr/sbin/ip addr del $vip/$netmask dev $ifdev label $ifdev:$key";
###################################################################

GetOptions(
'command=s'        => \$command,
'ssh_user=s'       => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s'  => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s'  => \$new_master_host,
'new_master_ip=s'    => \$new_master_ip,
'new_master_port=i'  => \$new_master_port,
);

exit &main();

sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {
    my $exit_code = 1;
    eval {
        print "Disabling the VIP on old master: $orig_master_host \n";
        &stop_vip();
        $exit_code = 0;
    };
    if ($@) {
        warn "Got Error: $@\n";
        exit $exit_code;
    }
    exit $exit_code;
}
elsif ( $command eq "start" ) {
    my $exit_code = 10;
    eval {
        print "Enabling the VIP - $vip on the new master - $new_master_host \n";
        &start_vip();
        $exit_code = 0;
    };
    if ($@) {
        warn $@;
        exit $exit_code;
    }
    exit $exit_code;
}
elsif ( $command eq "status" ) {
    print "Checking the Status of the script.. OK \n";
    exit 0;
}
else {
    &usage();
    exit 1;
}
}

# 新主添加VIP
sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}

# 旧主删除VIP
sub stop_vip() {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

创建 MHA 软件目录并拷贝配置文件

[root@mha ~]# mkdir /etc/masterha
[root@mha ~]# cp /root/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
[root@mha ~]# vim /etc/masterha/app1.cnf
#全部删掉，替换
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=manager
ping_interval=1
remote_workdir=/tmp
repl_password=123
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.108.132 -s 192.168.108.133
shutdown_script=""
ssh_user=root
user=mha

[server1]
hostname=192.168.108.131
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.108.132
port=3306

[server3]
hostname=192.168.108.133
port=3306

9. 验证与启动

# 检测SSH
masterha_check_ssh --conf=/etc/masterha/app1.cnf

# 检测主从复制
masterha_check_repl --conf=/etc/masterha/app1.cnf

# 手动开启VIP（首次）
/sbin/ifconfig ens33:1 192.168.108.200

# 启动MHA
nohup masterha_manager --conf=/etc/masterha/app1.cnf   --remove_dead_master_conf --ignore_last_failover   </dev/null >/var/log/masterha/app1/manager.log 2>&1 &

# 查看状态
masterha_check_status --conf=/etc/masterha/app1.cnf

7.4 故障模拟与修复

故障模拟

# 停止主库MySQL
systemctl stop mysqld

# MHA会自动完成故障转移
# 查看日志确认切换结果

故障修复

# 1. 修复原主库
systemctl start mysqld

# 2. 将原主库配置为新主库的从库
mysql> CHANGE MASTER TO
  master_host='192.168.108.132',  -- 新主库
  master_user='myslave',
  master_password='123',
  master_log_file='master-bin.000002',
  master_log_pos=2210;

mysql> START SLAVE;

附录：常用命令速查

数据库操作

SHOW DATABASES;
CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name;

表操作

SHOW TABLES;
DESC tbl_name;
CREATE TABLE ...;
ALTER TABLE ...;
DROP TABLE tbl_name;
TRUNCATE TABLE tbl_name;

数据操作

INSERT INTO tbl_name (cols) VALUES (vals);
UPDATE tbl_name SET col=val WHERE condition;
DELETE FROM tbl_name WHERE condition;
SELECT * FROM tbl_name WHERE condition;

用户与权限

CREATE USER 'user'@'host' IDENTIFIED BY 'password';
GRANT ALL ON db.* TO 'user'@'host';
REVOKE ALL ON db.* FROM 'user'@'host';
SHOW GRANTS FOR 'user'@'host';
FLUSH PRIVILEGES;

主从复制

-- 主库
SHOW MASTER STATUS;

-- 从库
CHANGE MASTER TO ...;
START SLAVE;
STOP SLAVE;
SHOW SLAVE STATUS\G;

openEuler 社区

openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构

更多推荐

【Linux】磁盘物理结构与逻辑抽象——从CHS到LBA

openEuler 社区

第一次作业

免费开源的电脑系统，服务器、单片机都在用，比Windows稳定。在你电脑里虚拟一台独立小电脑，装Linux不会弄坏本机系统。浏览器搜清华镜像站，下载CentOS Linux镜像文件。2).新建虚拟机，导入下载好的Linux镜像。开机黑屏：电脑BIOS打开虚拟化功能。连不上网：虚拟机网卡改成NAT模式。1).装VMware虚拟机软件。4).设置用户名密码，完成安装。1.什么是Linux操作系统。3)

openEuler 社区

Linux操作系统

生活里很多东西的核心也都是Linux，像我们日常使用的安卓手机、家里的路由器、智能小家电，还有如今国内机关单位普及的银河麒麟、统信这类国产电脑系统，全部都是在它的基础上改造而成。该系统既可以像普通电脑一样靠桌面鼠标点击操作，也可以通过命令窗口输入代码快速完成各类管理设置。因为免费好用、占用运行内存小、抗压能力强的诸多优点，现如今不管是互联网企业后台、工业控制设备，还是各类小型智能硬件都大范围普及L