MySQL 基础

1. 常用连接参数

mysql -u 用户名 -p 密码 -h 主机地址 -P 端口号 -S socket文件

2. 非交互模式执行 SQL

mysql -u root -p -e ‘SHOW DATABASES;

`

| 参数 | 说明 |

|------|------|

| -u / --user | 指定登录用户名 |

| -p / --password | 指定登录密码（注意小写 p，建议放到最后） |

| -h / --host | 指定数据库主机地址 |

| -P / --port | 指定数据库端口号（大写 P） |

| -S / --socket | 指定 socket 文件 |

| -e / --execute | 非交互模式执行 SQL |

### 2. 常用管理命令

RELOAD; — 刷新授权表

SHUTDOWN; — 停止 MySQL 服务

STATUS; — 简查看数据库状态

START SLAVE; — 启动从库复制

STOP SLAVE; — 停止从库复制

SHOW VARIABLES; — 查看可用变量

SELECT VERSION(); — 查看 MySQL 版本信息

### 3. 什么是 SQL

**SQL**（Structured Query Language）—：结构化查询语言，是使用关系模型的数据库应用语言。

### 4. SQL 语句分类

| 分类 | 全称 | 作用 | 常见语句 |

|------|------|------|---------|

| **DDL** | Data Definition Language | 数据定义语言，定义数据库、表、索引等 | CREATE、ALTER、DROP |

| **DML** | Data Manipulation Language | 数据操纵语言，增删改查记录 | INSERT、UPDATE、DELETE |

| **DQL** | Data Query Language | 数据查询语言，检索信息 | SELECT |

| **DCL** | Data Control Language | 数据控制语言，管理权限和安全级别 | GRANT、REVOKE |

### 5. 数据库操作

**创建数据库：**

CREATE DATABASE 数据库名称;

**查询数据库：**

— 显示所有数据库

SHOW DATABASES;

— 显示某个数据库的创建语句

SHOW CREATE DATABASE db1;

**修改数据库信息：**

ALTER DATABASE 数据库名称 DEFAULT CHARSET=编码格式;

**删除数据库（慎用）：**

DROP DATABASE 数据库名称;

### 6. 数据表操作

**创建数据表：**

CREATE TABLE 数据表名称 (

字段1 字段类型 [字段约束],

字段2 字段类型 [字段约束],

...

);

**查询数据表：**

— 选择数据库

USE 数据库名称;

— 显示所有数据表

SHOW TABLES;

— 显示表的创建语句

SHOW CREATE TABLE 数据表名称;

— 显示表结构

DESC 数据表名称;

**修改数据表：**

**Ⅰ. 添加字段：**

ALTER TABLE 数据表名称 ADD 新字段 字段类型 FIRST|AFTER 其他字段;

— FIRST: 将新字段放在第一位

— AFTER 字段名: 将新字段放在指定字段之后

**Ⅱ. 修改字段名称或类型：**

ALTER TABLE 表名称 CHANGE 旧字段名 新字段名 字段类型;

**Ⅲ. 修改字段类型或约束：**

ALTER TABLE 表名称 MODIFY 字段名称 字段类型;

**Ⅳ. 删除字段：**

ALTER TABLE 表名称 DROP 字段名称;

**Ⅴ. 修改表名：**

ALTER TABLE 旧表名 RENAME TO 新表名;

**Ⅵ. 修改表的字符集：**

ALTER TABLE 表名称 DEFAULT CHARSET=编码格式;

**删除数据表（慎用）：**

DROP TABLE 数据表名称;

---

## 3. 数据类型与运算符

### 1. 数据类型

**整型：**

| 类型 | 占用空间 | 取值范围（有符号） |

|------|---------|-----------------|

| TINYINT | 1 字节 | -128 ~ 127 |

| SMALLINT | 2 字节 | -32768 ~ 32767 |

| MEDIUMINT | 3 字节 | -8388608 ~ 8388607 |

| INT / INTEGER | 4 字节 | -2147483648 ~ 2147483647 |

| BIGINT | 8 字节 | ±9.22×10¹⁸ |

**浮点与定点数：**

| 类型 | 说明 |

|------|------|

| FLOAT(M,D) | 单精度浮点数，M 为总位数，D 为小数位 |

| DOUBLE(M,D) | 双精度浮点数 |

| DECIMAL(M,D) | 定点数（精确存储），适合金额等对精度敏感的场景 |

**字符串类型：**

| 类型 | 说明 | 最大长度 |

|------|------|---------|

| CHAR(N) | 定长字符串 | 255 字符 |

| VARCHAR(N) | 变长字符串 | 65535 字符 |

| TINYTEXT | 微型文本 | 255 字节 |

| TEXT | 文本 | 65535 字节 |

| MEDIUMTEXT | 中型文本 | 16MB |

| LONGTEXT | 大型文本 | 4GB |

| ENUM | 枚举类型 | 65535 个值 |

| SET | 集合类型 | 64 个值 |

**日期/时间类型：**

| 类型 | 格式 | 说明 |

|------|------|------|

| DATE | YYYY-MM-DD | 日期 |

| TIME | HH:MM:SS | 时间 |

| DATETIME | YYYY-MM-DD HH:MM:SS | 日期时间 |

| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 时间戳（受时区影响） |

| YEAR | YYYY | 年份 |

**二进制类型：**

| 类型 | 说明 |

|------|------|

| BINARY(N) | 定长二进制 |

| VARBINARY(N) | 变长二进制 |

| BLOB | 二进制大对象 |

**其他类型：**

| 类型 | 说明 |

|------|------|

| JSON | MySQL 5.7+ 原生 JSON 支持 |

| GEOMETRY | 空间数据类型 |

### 2. 运算符

**算术运算符：** +、-、*、/、%（取模）

**比较运算符：**

| 运算符 | 说明 |

|--------|------|

| = | 等于 |

| <> / != | 不等于 |

| < / <= / > / >= | 大小比较 |

| BETWEEN ... AND ... | 在指定范围之间 |

| IN (值1, 值2, ...) | 在指定集合中 |

| LIKE | 模糊匹配（% 匹配任意多个字符，_ 匹配单个字符） |

| IS NULL / IS NOT NULL | 判断是否为空 |

**逻辑运算符：** AND（与）、OR（或）、NOT（非）、XOR（异或）

**位运算符：** &（位与）、|（位或）、^（位异或）、~（位取反）、>>（右移）、<<（左移）

---

## 4. 存储引擎

### 1. 存储引擎概述

MySQL 使用**可插拔存储引擎**架构，支持多种存储引擎以满足不同场景需求。

### 2. MyISAM 与 InnoDB 对比

| 特性 | MyISAM | InnoDB |

|------|--------|--------|

| **事务** | 不支持 | ✅ 支持 |

| **外键** | 不支持 | ✅ 支持 |

| **锁粒度** | 表级锁 | 行级锁 + 表级锁 |

| **并发性能** | 低（锁定整张表） | 高（只锁定涉及的行） |

| **全文索引** | ✅ 支持 | MySQL 5.6+ 支持 |

| **缓存** | 只缓存索引 | 缓存索引 + 数据 |

| **默认使用** | MySQL 5.5 之前 | MySQL 5.5+ 默认引擎 |

| **适用场景** | 以查询为主、不需要事务的表 | 需要事务支持、高并发读写 |

### 3. 查看存储引擎

— 查看当前 MySQL 支持的存储引擎

SHOW ENGINES;

— 查看表的存储引擎

SHOW CREATE TABLE 表名称;

— 查看数据库默认存储引擎

SHOW VARIABLES LIKE ‘default_storage_engine’;

---

## 5. 索引

### 1. 索引概述

索引是帮助 MySQL **高效获取数据**的数据结构。索引可以大大提高查询速度，但会降低写入性能并占用额外空间。

### 2. 索引分类

| 索引类型 | 说明 |

|----------|------|

| **普通索引**（INDEX） | 最基本的索引，没有唯一性限制 |

| **唯一索引**（UNIQUE） | 索引列的值必须唯一，允许 NULL |

| **主键索引**（PRIMARY KEY） | 特殊的唯一索引，不允许 NULL，一个表只能有一个 |

| **复合索引** | 多列组合索引，遵循最左前缀原则 |

| **全文索引**（FULLTEXT） | 用于全文搜索 |

| **空间索引**（SPATIAL） | 用于空间数据类型 |

### 3. 索引操作

— 创建索引

CREATE INDEX 索引名 ON 表名(列名);

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

— 查看索引

SHOW INDEX FROM 表名;

— 删除索引

DROP INDEX 索引名 ON 表名;

### 4. 索引使用原则

- **最左前缀原则**：复合索引从最左侧开始匹配，跳过中间列会导致后续列无法使用索引

- **高选择性列优先**：区分度高的列放在复合索引左侧

- **避免过度索引**：索引不是越多越好，每增加一个索引都会降低写入性能

- **覆盖索引**：查询的列全部在索引中时，无需回表查询，性能极高

---

## 6. 主从复制

### 1. MySQL 主从复制概述

MySQL 主从复制（Master-Slave Replication）是指将主数据库（Master）的 DDL 和 DML 操作通过二进制日志（Binlog）传输到从数据库（Slave）上，并在从库上重新执行，从而保持主从数据一致。

### 2. 主从复制架构

| 架构模式 | 说明 |

|----------|------|

| **一主一从** | 一个 Master 同步到一个 Slave |

| **一主多从** | 一个 Master 同步到多个 Slave，分担读压力 |

| **主主复制**（Master-Master） | 两个 Master 互相同步，同一时刻只有一台接收写请求 |

| **级联复制** | Slave 同时作为下一级 Slave 的 Master |

### 3. 主从复制核心思路

> ⚠️ **图片缺失**：原文档引用的主从复制架构图为本地临时文件。

**配置要点：**

1. Slave 必须安装**相同版本**的 MySQL 数据库软件

2. Master 端必须**开启二进制日志**（Binlog）；Slave 端必须开启 Relay Log

3. Master 端和 Slave 端的 server-id 不能一致

4. Master 端必须创建复制用户

5. 保证 Master 和 Slave 初始数据一致

**配置步骤：**

— Master 端 my.cnf 配置

[mysqld]

log-bin=mysql-bin

server-id=1

— Slave 端 my.cnf 配置

[mysqld]

server-id=2

relay-log=mysql-relay-bin

— Master 端创建复制用户

CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘密码’;

GRANT REPLICATION SLAVE ON . TO ‘repl’@’%’;

— Slave 端配置主从

CHANGE MASTER TO

MASTER_HOST='主库IP',

MASTER_USER='repl',

MASTER_PASSWORD='密码',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=位置号;

START SLAVE;

— 查看从库状态

SHOW SLAVE STATUS\G;

---

## 7. 基于 GTID 的复制

### 1. GTID 概述

**GTID**（Global Transaction Identifiers）—：全局事务标识符，是 MySQL 5.6 引入的新特性。

**特点：**

- 每个事务都可以被唯一识别和跟踪

- 添加新 Slave 或 Master 故障切换时，无需指定 Binlog 文件名和 Position 值

- 完全基于事务，**不支持 MyISAM** 存储引擎

**GTID 组成：** source_id:transaction_id

- source_id：来自 server_uuid，可在 auto.cnf 中查看

- transaction_id：自动生成的序列数字

**使用限制：**

1. ❌ 不支持非事务引擎（MyISAM），可能导致多个 GTID 分配给同一事务

2. ❌ 不支持 CREATE TABLE ... SELECT 语句（主库语法报错）

3. ❌ 不支持 CREATE/DROP TEMPORARY TABLE 语句

4. ⚠️ 必须设置 enforce-gtid-consistency 参数

5. ⚠️ sql-slave-skip-counter 不适用（传统跳过错误方式失效）

6. ⚠️ GTID 环境中所有节点必须统一开启或关闭 GTID

---

## 8. 半同步复制

### 1. 概念

**半同步复制**（Semi-Synchronous Replication）保证 Master 在每次 COMMIT 事务时，必须等待至少一个 Slave 确认已收到并写入 Relay Log 后，才向客户端返回成功。

`

Master COMMIT → 发送 Binlog → Slave 接收并写入 Relay Log → Slave 回复 ACK → Master 返回客户端成功

`

**优点：** 主从数据一致性更高

**缺点：** 牺牲 Master 性能（需等待 Slave 确认）

等待超时时间可配置：

`sql

-- 设置半同步复制等待超时时间（默认 10 秒）

SET GLOBAL rpl_semi_sync_master_timeout = 10000;

`

---

## 九、MHA 高可用架构

### 1. 什么是 MHA

**MHA**（Master High Availability）是一套成熟的 MySQL 高可用故障切换方案，由日本 DeNA 公司开发。

- **故障切换时间**：0~30 秒内自动完成

- **数据一致性**：在故障切换过程中最大程度保证数据不丢失

- **适用场景**：生产环境高可用需求

### 2. 工作原理

> ⚠️ **图片缺失**：原文档引用的 MHA 架构图为本地临时文件。

`

Master 故障 → 对比 Slave Binlog 位置 → 选取最新 Slave → 生成差异中继日志 → 应用日志 → 提升为新 Master

1. Master 出现故障

2. 对比各 Slave 的 I/O 线程读取 Binlog 的位置，选取最接近 Master 的 Slave 作为 Latest Slave

3. 其他 Slave 与 Latest Slave 对比生成差异中继日志并应用

4. 在 Latest Slave 上应用从 Master 保存的 Binlog，提升为新的 Master

5. 其他 Slave 应用差异中继日志并开始从新 Master 复制

`

### 3. MHA 组件

| 组件 | 部署位置 | 功能 |

|------|---------|------|

| **MHA Manager** | 独立机器或 Slave 节点 | 监控 Master、自动故障切换、管理多个集群 |

| **MHA Node** | 所有运行 MySQL 的服务器 | 保存 Binlog、应用差异中继日志、清除中继日志 |

**MHA Node 三大作用：**

1. **保存二进制日志**：如果可访问故障 Master，拷贝其 Binlog

2. **应用差异中继日志**：从拥有最新数据的 Slave 生成并应用中继日志

3. **清除中继日志**：在不停止 SQL 线程的情况下安全删除中继日志

---

> [!info] 关联文档
> - [[MySQL 复制原理与配置]]：主从复制原理详解
> - [[MySQL binlog 保留时间与清理]]：Binlog 管理与维护
> - [[MySQL 高可用方案]]：高可用架构对比
> - [[GTID 集合详解]]：GTID 深入原理
> - [[MySQL 基础知识面试题]]：配套面试题