mysql 基础¶

数据库和表的创建¶

创建数据库

CREATE DATABASE test_db;

创建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

数据类型¶

常见数据类型：

1. 整数类型¶

数据类型	存储大小	有符号的范围	无符号的范围
`TINYINT`	1 字节	-128 到 127	0 到 255
`SMALLINT`	2 字节	-32,768 到 32,767	0 到 65,535
`MEDIUMINT`	3 字节	-8,388,608 到 8,388,607	0 到 16,777,215
`INT` / `INTEGER`	4 字节	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295
`BIGINT`	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	0 到 18,446,744,073,709,551,615

2. 字符串类型¶

以下是 MySQL 中常见的字符串类型及其区别，以表格的形式展示：

数据类型	存储大小	最大长度	描述
`CHAR(n)`	定长，n 字节	255 个字符	定长字符串，长度不足时会用空格填充
`VARCHAR(n)`	变长，实际长度 + ½ 字节	65,535 个字符	变长字符串，适用于存储可变长度的文本
`TINYTEXT`	变长，实际长度 + 1 字节	255 个字符	小文本字段，用于存储短文本
`TEXT`	变长，实际长度 + 2 字节	65,535 个字符	标准文本字段，用于存储较大文本
`MEDIUMTEXT`	变长，实际长度 + 3 字节	16,777,215 个字符	中等大小的文本字段
`LONGTEXT`	变长，实际长度 + 4 字节	4,294,967,295 个字符	超大文本字段，用于存储超大文本
`BINARY(n)`	定长，n 字节	255 字节	定长二进制字符串，用于存储二进制数据
`VARBINARY(n)`	变长，实际长度 + ½ 字节	65,535 字节	变长二进制字符串
`ENUM`	1 到 2 字节	最多 65,535 个值	枚举类型，字符串列表中的一个值
`SET`	1 到 8 字节	最多 64 个成员	字符串集合，可以包含多个值

说明 - CHAR(n)：适用于存储长度固定的字符串，例如国家代码、状态码等。长度不足时会自动用空格填充。 - VARCHAR(n)：适用于存储可变长度的字符串。只存储实际使用的字符数，并附加长度信息。 - TEXT 系列：用于存储大量文本数据，TINYTEXT 用于较小文本，TEXT 用于中等文本，MEDIUMTEXT 和 LONGTEXT 用于更大规模的文本。 - BINARY 和 VARBINARY：类似于 CHAR 和 VARCHAR，但用于存储二进制数据，如文件、图像等。 - ENUM：用于存储预定义的字符串值，适合用于状态、类型等有限集合的值。 - SET：用于存储多个预定义的字符串值组合，适合用于标签、权限等多选项的值。

3. 日期时间类型¶

以下是 MySQL 中常见的日期和时间类型及其区别，以表格的形式展示：

数据类型	存储大小	范围	格式	描述
`DATE`	3 字节	1000-01-01 到 9999-12-31	`YYYY-MM-DD`	只存储日期，不包含时间部分
`TIME`	3 字节	'-838:59:59' 到 '838:59:59'	`HH:MM:SS`	只存储时间，不包含日期部分
`DATETIME`	5 字节	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 03:14:07 UTC	`YYYY-MM-DD HH:MM:SS`	存储从 1970 年 1 月 1 日以来的秒数，受时区影响
`YEAR`	1 字节	1901 到 2155，或 70 到 99	`YYYY` 或 `YY`	只存储年份

说明 - DATE：用于存储日期信息，格式为 YYYY-MM-DD，只包含年、月、日部分。 - TIME：用于存储时间信息，格式为 HH:MM:SS，只包含时、分、秒部分。 - DATETIME：用于存储日期和时间信息，格式为 YYYY-MM-DD HH:MM:SS，不考虑时区。 - TIMESTAMP：用于存储 Unix 时间戳，从 1970 年 1 月 1 日以来的秒数。会自动根据时区转换。 - YEAR：用于存储年份信息，可以用 4 位或 2 位表示。2 位的 YEAR 类型会自动将 70-99 映射为 1970-1999，将 00-69 映射为 2000-2069。

4. 浮点数类型¶

以下是 MySQL 中常见的浮点数类型及其区别，以表格的形式展示：

数据类型	存储大小	范围（有符号）	范围（无符号）	描述
`FLOAT(p)`	4 字节	-3.402823466E+38 到 -1.175494351E-38, 0, 1.175494351E-38 到 3.402823466E+38	0 到 1.175494351E-38 到 3.402823466E+38	单精度浮点数，`p` 表示精度（可选）
`DOUBLE(p)`	8 字节	-1.7976931348623157E+308 到 -2.2250738585072014E-308, 0, 2.2250738585072014E-308 到 1.7976931348623157E+308	0 到 2.2250738585072014E-308 到 1.7976931348623157E+308	双精度浮点数，`p` 表示精度（可选）
`DECIMAL(m, d)`	变长	取决于 `m` 和 `d`	取决于 `m` 和 `d`	精确的小数，`m` 为总位数，`d` 为小数位数

说明 - FLOAT(p)：单精度浮点数类型，p 表示精度（位数），默认精度为 24 位。如果需要存储较小范围的浮点数，并且对精度要求不高，可以使用 FLOAT。 - DOUBLE(p)：双精度浮点数类型，p 表示精度（位数），默认精度为 53 位。如果需要存储较大范围的浮点数，或者对精度要求较高，可以使用 DOUBLE。 - DECIMAL(m, d)：精确的定点数类型，用于存储精确的小数值，m 表示总位数（包括小数点和小数位），d 表示小数位数。适用于对数值精度要求较高的场景，如货币金额的存储。

MySQL查询与操作¶

基本查询¶

SELECT 语句：

SELECT * FROM users;

带条件查询：

SELECT * FROM users WHERE age > 18;

数据插入、更新、删除¶

插入数据：

INSERT INTO users (name, age) VALUES ('Alice', 25);

更新数据：

UPDATE users SET age = 26 WHERE name = 'Alice';

删除数据：

DELETE FROM users WHERE name = 'Alice';

排序和分页¶

排序：

SELECT * FROM users ORDER BY age DESC;

分页：

SELECT * FROM users LIMIT 10 OFFSET 20;

MySQL索引¶

索引类型¶

普通索引：

CREATE INDEX idx_name ON users(name);

唯一索引：

CREATE UNIQUE INDEX idx_unique_name ON users(name);

主键索引：

ALTER TABLE users ADD PRIMARY KEY (id);

全文索引（InnoDB 需要 MySQL 5.6 及以上版本支持）：

CREATE FULLTEXT INDEX idx_fulltext_name ON users(name);

索引的优缺点¶

优点： 加快查询速度 缺点： 增加写操作的时间（插入、更新、删除时需要更新索引），占用更多的存储空间

MySQL事务¶

事务特性¶

ACID¶

原子性 (Atomicity)

定义：原子性确保事务中的所有操作要么全部执行成功，要么全部不执行。事务是不可分割的最小工作单元。

例子：假设有一个银行转账操作，从账户 A 转账到账户 B。该操作包括两个步骤：从账户 A 中扣款和向账户 B 中存款。原子性确保如果其中任何一个步骤失败，整个事务都会回滚（即不执行），从而保证不会出现只扣款却未存款的情况。

一致性 (Consistency)

定义：一致性保证事务执行前后，数据库都处于一个合法的状态。事务完成时，所有数据必须保持一致。

例子：如果银行规定账户余额不能为负数，那么在转账操作中，即使某个账户余额变更，也必须保证在整个操作过程中账户余额的约束条件得到满足。

隔离性 (Isolation)

定义：隔离性保证多个事务并发执行时，一个事务的操作不会对其他事务产生干扰。每个事务都认为自己是独立运行的。

隔离级别：MySQL 提供了四种隔离级别，用来控制事务的隔离程度：

•   读未提交 (Read Uncommitted)：事务可以读取其他未提交事务的数据，可能会出现脏读问题。

•   读已提交 (Read Committed)：事务只能读取其他已提交事务的数据，防止脏读，但可能会出现不可重复读问题。

•   可重复读 (Repeatable Read)：事务在开始时确定了一个一致的快照，在整个事务过程中看到的数据是一致的，防止脏读和不可重复读。InnoDB 的默认隔离级别。

•   可串行化 (Serializable)：最高的隔离级别，事务完全串行化执行，避免了幻读，但可能导致性能下降。

Note

脏读：

脏读指一个事务读取了另一个事务尚未提交的修改数据。由于读到的是未提交的数据，如果该修改最终被回滚，那么第一个事务所读取的数据就会变得不正确或“脏”。

脏读通常可以通过提高事务的隔离级别来避免，如使用“读已提交”（Read Committed）或更高的隔离级别。

幻读：

幻读指一个事务在执行两次相同的查询时，第二次查询结果包含了第一次查询时不存在的“幻影”记录。这通常是由于另一事务在第一次查询后插入了符合查询条件的记录。

幻读通常通过使用更高级别的事务隔离（如“可串行化”（Serializable）或“可重复读”（Repeatable Read））来防止。在“可重复读”隔离级别下，事务在第一次读取数据时就会锁定读取范围，防止其他事务插入或删除数据，从而避免幻读。

持久性 (Durability)

定义：持久性确保一旦事务提交，数据将被永久保存，即使系统崩溃也不会丢失数据。持久性通过将数据写入持久存储设备（如磁盘）来实现。

例子：在银行转账操作中，一旦事务提交，账户 A 的扣款和账户 B 的存款记录都将被永久保存，即使数据库服务器崩溃，数据依然可以恢复。

ACID 在 MySQL 中的实现

事务支持：MySQL 支持事务的表引擎主要是 InnoDB。InnoDB 提供了完整的 ACID 支持，并通过使用事务日志、锁机制和崩溃恢复技术来保证数据的一致性和持久性。

日志机制：MySQL 使用重做日志（Redo Log）和撤销日志（Undo Log）来保证事务的持久性和原子性。

锁机制：MySQL 通过使用锁（如行锁和表锁）来实现事务的隔离性，避免数据的并发冲突。

事务控制¶

开启事务：

START TRANSACTION;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

隔离级别¶

MySQL 中的事务隔离级别决定了一个事务在何种程度上与其他并发事务隔离，以确保数据一致性和防止并发问题。不同的隔离级别可以防止不同类型的并发问题，如脏读、不可重复读和幻读。MySQL 支持四种标准的事务隔离级别：

1. 读未提交（Read Uncommitted）¶

特点

最低的隔离级别，允许事务读取其他事务尚未提交的数据。
问题：可能会导致脏读（Dirty Read），即一个事务可以读取到另一个事务尚未提交的修改。如果该修改在后续回滚，这会导致数据不一致。

示例

假设事务 A 修改了一条记录但尚未提交，事务 B 在事务 A 提交之前读取了这条修改后的记录。如果事务 A 最后回滚，事务 B 读取到的数据就是脏数据。

用途

这种隔离级别通常用于不要求严格数据一致性的场景，以提高并发性能。

2. 读已提交（Read Committed）¶

特点

默认的隔离级别 在许多数据库系统中（但不是 MySQL，MySQL 默认使用可重复读）。
问题：防止了脏读，但仍然可能会出现不可重复读（Non-repeatable Read）。在同一个事务中，如果执行相同的查询多次，可能会得到不同的结果，因为其他事务可能在期间提交了更改。

示例

假设事务 A 读取了一条记录的值，随后事务 B 修改并提交了这条记录。事务 A 再次读取时会发现数据发生了变化。

用途

适用于要求防止脏读但不介意不可重复读的场景，例如读操作较多的系统。

3. 可重复读（Repeatable Read）¶

特点

MySQL 的默认隔离级别，保证在同一个事务中多次读取同一数据时，结果是一样的，即防止了不可重复读。
问题：防止了脏读和不可重复读，但可能会出现幻读（Phantom Read）。即在事务过程中，如果另一事务插入了新的记录，前一个事务可能在后续查询中发现新的“幻影”记录。

示例

假设事务 A 读取了一组满足某个条件的记录，随后事务 B 插入了一条新记录，满足事务 A 的查询条件。事务 A 在再次执行相同查询时，会发现多了一条记录。

用途

适用于要求防止脏读和不可重复读，且对幻读容忍度较高的场景，例如电商订单系统。

4. 可串行化（Serializable）¶

特点

最高的隔离级别，通过对所有读取的数据行加锁，确保事务完全串行化执行，即每个事务依次执行，防止了脏读、不可重复读和幻读。
问题：由于事务串行化执行，可能导致大量锁竞争，从而显著降低并发性能。

示例

在可串行化隔离级别下，如果一个事务正在读取数据，其他事务在该事务完成前不能对这些数据进行插入、更新或删除操作。

用途

适用于数据一致性要求极高的场景，如银行系统、金融交易系统等。

隔离级别与并发问题的对应关系

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
可串行化	不可能	不可能	不可能

设置隔离级别

MySQL 可以通过以下命令设置全局或会话级的隔离级别：

设置全局隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

设置会话隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

总结

读未提交：允许读取未提交的数据，存在脏读问题。
读已提交：只能读取已提交的数据，避免脏读，但可能有不可重复读问题。
可重复读：默认隔离级别，防止脏读和不可重复读，但可能有幻读问题。
可串行化：最高隔离级别，防止所有并发问题，但性能可能会受到影响。

MySQL存储引擎¶

常见存储引擎¶

InnoDB：支持事务，行级锁，外键，默认存储引擎。

MyISAM：不支持事务，表级锁，读取速度快。

Memory：数据存储在内存中，速度快，适合临时数据处理。

MySQL性能优化¶

查询优化¶

EXPLAIN分析查询：

EXPLAIN SELECT * FROM users WHERE age > 18;

使用索引：确保查询中使用了索引

减少查询的扫描行数：使用 LIMIT、适当的索引、避免 SELECT *

数据库设计优化¶

范式化与反范式化：根据需求选择适当的数据库设计模式

适当的字段类型：选择合适的数据类型，减少空间浪费

配置优化¶

调整缓存大小：如 innodb_buffer_pool_size

调整连接数：如 max_connections

MySQL主从复制与集群¶

MySQL 主从复制（Master-Slave Replication）和 MySQL 集群（MySQL Cluster）是两种用于提高数据库可用性、扩展性和容错能力的重要技术。它们在设计和用途上有所不同，适用于不同的应用场景。

MySQL 主从复制（Master-Slave Replication）¶

1. 概述

MySQL 主从复制是一种将数据从一个数据库服务器（主服务器）复制到一个或多个其他服务器（从服务器）的过程。这种方法主要用于读写分离、数据备份、负载均衡等场景。

2. 工作原理

主服务器（Master）：处理所有的写操作（INSERT、UPDATE、DELETE 等），并将这些操作记录到二进制日志（Binary Log）中。

从服务器（Slave）：读取主服务器的二进制日志，并在自己的数据库中执行相同的操作，以保持与主服务器的数据一致。

复制过程分为三个步骤：

主服务器记录事件：在主服务器上执行的每个写操作（如插入、更新、删除）都会记录到二进制日志中。
从服务器获取日志：从服务器通过 I/O 线程将主服务器的二进制日志复制到本地的中继日志（Relay Log）。
从服务器执行日志：从服务器通过 SQL 线程读取中继日志，并在本地数据库中执行这些操作，使数据保持同步。

3. 优点

读写分离：主服务器负责写操作，从服务器负责读操作，从而减轻主服务器的压力。
数据备份：从服务器可以作为主服务器的数据备份，提供数据冗余。
负载均衡：通过增加从服务器，可以实现读操作的负载均衡，提高系统的并发处理能力。

4. 缺点

延迟问题：由于从服务器需要时间获取和执行主服务器的操作日志，可能会导致数据同步延迟。
单点故障：如果主服务器出现故障，整个复制系统可能中断，除非使用主主复制或半同步复制来提高可用性。

5. 常见模式

异步复制：默认模式，主服务器不等待从服务器确认操作已经复制成功。
半同步复制：主服务器等待至少一个从服务器确认操作已复制到中继日志中后，才返回客户端成功消息。
主主复制（Multi-Master Replication）：两个服务器互为主从，双方都可以执行写操作。

6. 原理

MySQL主从复制的原理

MySQL 集群（MySQL Cluster）¶

1. 概述

MySQL 集群是一种分布式数据库解决方案，旨在提供高可用性、可扩展性和低延迟的数据存储服务。MySQL 集群将数据分布在多个节点上，并通过冗余和分片技术实现数据的高可用性和容错性。

2. 架构组成

MySQL 集群由以下几个关键组件组成：

管理节点（Management Node, ndb_mgmd）：负责集群的管理和配置。
数据节点（Data Node, ndbd 或 ndbmtd）：存储实际的数据，每个数据节点存储数据的一部分。
SQL 节点（SQL Node）：运行 MySQL Server，应用程序通过 SQL 节点访问集群中的数据。
API 节点（API Node）：可以直接与数据节点交互，用于高性能数据访问。

3. 工作原理

MySQL 集群采用的是共享无结构架构（Shared-Nothing Architecture），即每个节点独立运行，不共享存储。数据通过分片（Sharding）存储在多个数据节点上，并通过复制（Replication）实现冗余。集群自动管理数据的分布、负载均衡和故障转移。

4. 优点

高可用性：数据冗余和自动故障转移确保了集群的高可用性。
扩展性：可以通过增加数据节点和 SQL 节点来扩展集群的存储和处理能力。
低延迟：MySQL 集群通常用于需要低延迟、高并发的应用场景，如电信系统、实时分析等。

5. 缺点

复杂性：MySQL 集群的架构和管理相对复杂，需要专门的配置和维护。
存储开销：由于数据冗余机制，需要更多的存储空间来确保高可用性。
性能开销：对于写操作，特别是涉及到大量数据节点的事务，性能可能受到影响。

6. 应用场景

MySQL 集群适用于需要高可用性、高扩展性和低延迟的场景，如在线游戏、实时交易系统、内容分发网络等。

MySQL 安全管理¶

用户管理¶

创建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

赋予权限：

GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

撤销权限：

REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';

数据备份与恢复¶

备份数据库：

mysqldump -u root -p database_name > backup.sql

恢复数据库：

mysql -u root -p database_name < backup.sql

mysql日志¶

MySQL 中的日志是数据库系统中非常重要的一部分，它用于记录数据库运行时的各种操作和事件。这些日志不仅有助于数据恢复、故障排查，还在性能分析、审计跟踪等方面起到重要作用。MySQL 主要有以下几种类型的日志：

1. 二进制日志（Binary Log）¶

概述：

二进制日志是 MySQL 中最重要的日志类型之一，记录了所有导致数据库发生更改的语句，如 INSERT、UPDATE、DELETE 等。二进制日志用于数据恢复和主从复制。

功能：

数据恢复：在发生故障或意外中断时，可以通过重放二进制日志中的记录来恢复数据库到故障前的状态。
主从复制：在主从复制环境中，主服务器将二进制日志发送给从服务器，从服务器通过执行这些日志来保持与主服务器的数据同步。

配置

二进制日志可以通过以下配置启用：

[mysqld]
log-bin = /var/log/mysql/mysql-bin.log

Note

binlog_format：设置二进制日志的格式，有三种模式：

STATEMENT：记录 SQL 语句（默认值）。

ROW：记录每一行数据的更改，较为详细。

MIXED：混合模式，根据情况在 STATEMENT 和 ROW 之间切换。

使用

查看二进制日志文件：

SHOW BINARY LOGS;

恢复数据：

mysqlbinlog mysql-bin.000001 | mysql -u root -p

2. 错误日志（Error Log）¶

概述

错误日志记录 MySQL 服务器启动和关闭过程中发生的错误、警告信息，以及系统在运行过程中遇到的严重问题。

功能

故障排查：通过检查错误日志，可以发现并解决 MySQL 启动失败、运行时异常等问题。
启动和关闭信息：记录服务器的启动和关闭时间。

配置

错误日志通常默认启用，可以通过以下配置来指定错误日志的位置和文件名：

[mysqld]
log-error = /var/log/mysql/error.log

3. 查询日志（General Query Log）¶

概述

查询日志记录 MySQL 服务器接收到的所有 SQL 查询语句。由于记录的内容非常详尽，查询日志对性能有较大影响，因此在生产环境中通常不会启用。

功能

审计：可以用来审计和分析数据库中的所有 SQL 操作。
调试：帮助开发者调试和优化 SQL 查询。

配置

查询日志默认关闭，可以通过以下配置启用：

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

4. 慢查询日志（Slow Query Log）¶

概述

慢查询日志记录了执行时间超过指定阈值的所有 SQL 语句。通过分析慢查询日志，可以发现和优化执行速度慢的查询。

功能

性能优化：帮助识别和优化慢查询，从而提高数据库的性能。

配置

慢查询日志默认关闭，可以通过以下配置启用：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

long_query_time：设置查询执行时间的阈值（单位为秒），超过该值的查询将被记录到慢查询日志中。

使用

查看慢查询日志：可以直接查看日志文件，或者通过以下命令获取慢查询日志中的记录：

SHOW GLOBAL STATUS LIKE 'Slow_queries';

分析慢查询日志：可以使用 mysqldumpslow 工具来总结和分析慢查询日志中的信息。

5. 中继日志（Relay Log）¶

概述

中继日志是 MySQL 主从复制中的一种日志，存在于从服务器上，用于记录从主服务器接收到的二进制日志事件。

功能

数据同步：从服务器读取中继日志并执行其中的操作，以保持与主服务器的数据同步。

配置

中继日志通常由 MySQL 自动管理，无需手动配置，但可以通过以下配置选项进行调整：

[mysqld]
relay-log = /var/log/mysql/relay-bin

使用查看中继日志状态：

SHOW SLAVE STATUS\G;

6. InnoDB 存储引擎日志¶

概述

InnoDB 日志包括重做日志（Redo Log）和撤销日志（Undo Log），用于实现崩溃恢复和事务管理。

功能

重做日志（Redo Log）：记录已提交事务的所有更改，用于崩溃后数据恢复。
撤销日志（Undo Log）：用于回滚未提交的事务，确保事务的原子性。

配置

可以通过以下参数配置 InnoDB 日志：

[mysqld]
innodb_log_file_size = 50M
innodb_log_files_in_group = 2

使用

查看 InnoDB 日志：InnoDB 日志文件位于数据目录中，通常命名为 ib_logfile0 和 ib_logfile1。

总结

日志类型	功能	配置参数	默认状态
二进制日志	记录所有更改数据库的操作，用于复制和恢复	`log-bin`	关闭
错误日志	记录启动、关闭和运行时的错误信息	`log-error`	开启
查询日志	记录所有 SQL 查询语句，用于调试和审计	`general_log`	关闭
慢查询日志	记录执行时间超过阈值的查询，用于性能优化	`slow_query_log`	关闭
中继日志	从服务器使用的日志，记录主服务器的二进制日志	`relay-log`	自动
InnoDB 日志	包括重做日志和撤销日志，用于崩溃恢复和事务管理	`innodb_log_file_size` 等	自动

MySQL 日志为数据库的管理和维护提供了丰富的支持，合理使用这些日志可以显著提高系统的稳定性和性能。

7. 三大日志¶

MySQL三大日志(binlog、redo log和undo log)概述详解

MySQL 常用工具¶

mysqldump¶

mysqldump 是 MySQL 提供的一个实用工具，用于将 MySQL 数据库或表导出为 SQL 脚本文件，便于备份或迁移。这个工具可以导出表结构、数据，以及用于重建数据库的 SQL 语句。

导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

导出所有数据库

mysqldump -u 用户名 -p --all-databases > all_databases_backup.sql

导出单个表

mysqldump -u 用户名 -p 数据库名 表名 > 表备份文件.sql

恢复数据库

mysql -u 用户名 -p 数据库名 < 备份文件.sql

mysqlbinlog¶

mysqlbinlog 是一个命令行工具，用于读取和处理 MySQL 的二进制日志文件（binary log）。二进制日志记录了所有对数据库进行修改的操作，主要用于数据恢复、主从复制和审计。

基本概念

二进制日志：二进制日志记录了数据库中的所有数据更改操作，包括 INSERT、UPDATE、DELETE、CREATE 等语句。它不记录 SELECT 查询。二进制日志可以用于数据恢复、数据库同步（主从复制）、审计操作等。
日志文件：二进制日志由一系列日志文件组成，通常以 mysql-bin.xxxxx 命名，其中 xxxxx 是递增的编号。

基本用法

查看二进制日志内容

mysqlbinlog mysql-bin.000001 
# mysql-bin.000001 是要查看的二进制日志文件的名称。

导出二进制日志到 SQL 文件

mysqlbinlog mysql-bin.000001 > binlog.sql

指定时间范围提取日志

mysqlbinlog --start-datetime="2024-08-01 10:00:00" --stop-datetime="2024-08-01 12:00:00" mysql-bin.000001 > binlog_part.sql

指定位置范围提取日志

mysqlbinlog --start-position=120 --stop-position=500 mysql-bin.000001 > binlog_part.sql

显示原始 SQL 语句

mysqlbinlog --verbose mysql-bin.000001

恢复数据

mysqlbinlog mysql-bin.000001 | mysql -u 用户名 -p 数据库名

mysqldumpslow¶

mysqldumpslow 是 MySQL 提供的一个实用工具，用于分析和汇总 MySQL 慢查询日志（slow query log）。该工具能够帮助数据库管理员快速识别数据库中执行时间较长的 SQL 查询，以便进行性能调优。mysqldumpslow 通过对慢查询日志进行排序、分组和统计，提供了对数据库性能问题的深入分析。

运行 mysqldumpslow 并指定慢查询日志文件的路径：

mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log
# -t 10 表示显示最频繁的 10 个查询。

Note

• -s：指定排序方式，可以使用以下值：

• c：按次数排序（count）。

• t：按查询时间排序（time）。

• l：按查询锁定时间排序（lock time）。

• r：按返回记录数排序（rows）。

• a：按平均查询时间排序（average time）。

例如，按查询时间排序并显示前 10 个查询：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

-r：逆序显示结果（reverse order）。配合 -s 选项使用。例如，按查询时间排序并逆序显示：

mysqldumpslow -s t -r -t 10 /var/log/mysql/mysql-slow.log

-a：不抽象出查询中的数字和字符串。默认情况下，mysqldumpslow 会将 SQL 查询中的某些变量（如数字和字符串）抽象化，以便于统计和汇总。使用 -a 选项可以禁用这种抽象化：

mysqldumpslow -a -t 10 /var/log/mysql/mysql-slow.log

-g：仅匹配包含指定字符串的查询。支持正则表达式。例如，查找所有包含 “SELECT” 的查询：

mysqldumpslow -g "SELECT" /var/log/mysql/mysql-slow.log

-t：显示前 N 个查询。例如，显示出现次数最多的前 5 个查询：

mysqldumpslow -t 5 /var/log/mysql/mysql-slow.log