前言

做运维或者DBA的同学肯定都遇到过这种情况：不小心执行了一个DROP TABLE语句，或者DELETE忘加WHERE条件，把数据删了。这时候怎么办？别急，MySQL的binlog日志可以帮你把数据找回来。

很多新手同学不知道binlog是什么，也不知道怎么用它来恢复数据。这篇文章就把binlog的知识点从头到尾讲清楚，包括binlog的三种格式、怎么开启、怎么查看、怎么用它恢复数据，看完之后你就彻底搞懂了。

一、binlog是什么

binlog的全称是二进制日志（Binary Log）。它记录了MySQL数据库里所有的写操作（INSERT、UPDATE、DELETE、CREATE TABLE这些），但是不记录SELECT查询。

binlog有什么用

1. 数据恢复：不小心删了数据，可以用binlog恢复
2. 主从复制：主库把binlog传给从库，从库重放binlog，实现主从同步
3. 数据审计：可以通过binlog追溯谁在什么时候做了什么操作

二、binlog的三种格式

binlog有三种格式，不同的格式记录的内容不一样。

1. STATEMENT（语句模式）

这种格式记录的是SQL语句本身。比如你执行了一条INSERT语句，binlog里就记录这条INSERT语句。

优点：

• 日志量小，占用空间少
• 同步的时候从库重放SQL就行

缺点：

• 有些函数（比如NOW()、UUID()）在主库和从库执行结果可能不一样，导致主从不一致
• 某些复杂的SQL可能在从库上执行结果不一样

2. ROW（行模式）

这种格式记录的是每一行数据的修改。比如你更新了100行数据，binlog里就记录这100行每一行改前是什么样，改后是什么样。

优点：

• 记录的是真实的数据修改，不会出现主从不一致的问题
• 能精确知道哪一行被改了

缺点：

• 日志量大，因为每一行修改都要记录
• 批量更新的时候，日志会特别大

3. MIXED（混合模式）

这种模式是前两种的混合。MySQL会自动判断：一般的SQL用STATEMENT模式，遇到那些可能出问题的SQL（比如用了NOW()、UUID()这些函数的），就自动切换成ROW模式。

总结： 现在一般推荐用ROW模式，虽然日志量大一点，但是数据一致性有保证，不会出主从不一致的问题。

三、怎么开启binlog

默认情况下，MySQL的binlog可能是没开的。我们先看看开没开：

SHOW VARIABLES LIKE 'log_bin';

如果Value是ON，说明开了；如果是OFF，说明没开。

开启binlog

编辑MySQL的配置文件 /etc/my.cnf，在[mysqld]下面添加：

[mysqld]
# 开启binlog
log_bin = mysql-bin

# binlog的格式，推荐用ROW
binlog_format = ROW

# 保存多少天的binlog，过期自动删除
expire_logs_days = 7

# 单个binlog文件最大多大
max_binlog_size = 100M

修改完之后重启MySQL服务：

systemctl restart mysqld

重启完之后再查一下，应该就开了。

四、怎么查看binlog

查看有哪些binlog文件

SHOW BINARY LOGS;

执行完之后会看到类似这样的结果：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     15264 |
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |       126 |
+------------------+-----------+

binlog文件是按编号来的，mysql-bin.000001满了就生成mysql-bin.000002，以此类推。

查看binlog里的内容

binlog是二进制文件，不能直接用cat看，要用mysqlbinlog工具。

比如要看mysql-bin.000001这个文件：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

如果是ROW格式的binlog，直接看是看不懂的，要加-vv参数：

mysqlbinlog -vv /var/lib/mysql/mysql-bin.000001

这样就能看到具体改了哪一行，改前是什么，改后是什么。

五、怎么用binlog恢复数据

这是大家最关心的：不小心删了数据，怎么用binlog恢复？

恢复的原理

binlog记录了所有的写操作。如果你不小心删了某段时间的数据，只要把这段时间之前的binlog重新执行一遍，数据就回来了。

举个例子

假设你在2026-10-05 10:00的时候，不小心执行了一条DELETE语句，把testdb库的users表全删了。现在要恢复数据。

步骤1：找到要恢复的binlog文件

先看看有哪些binlog文件，找到那个时间点对应的文件：

SHOW BINARY LOGS;

步骤2：找到删除操作的位置

用mysqlbinlog工具查看binlog，找到DELETE语句的位置：

mysqlbinlog --start-datetime="2026-10-05 09:00:00" --stop-datetime="2026-10-05 11:00:00" /var/lib/mysql/mysql-bin.000003

然后找到那条DELETE语句在binlog里的位置（position）。

步骤3：恢复数据

把DELETE语句之前的binlog重新执行一遍，数据就回来了：

mysqlbinlog --stop-position=刚才找到的位置 /var/lib/mysql/mysql-bin.000003 | mysql -u root -p

这样就把DELETE之前的数据恢复了。

注意： 恢复完之后，DELETE之后的操作也没了，所以还要把DELETE之后的操作也重新执行一遍，直到最新的位置。

六、binlog的其他常用操作

手动生成新的binlog文件

FLUSH LOGS;

这个命令会关闭当前的binlog文件，生成一个新的。一般备份完数据库之后执行这个，这样备份之后的操作都记录到新的binlog里，恢复的时候方便。

删除旧的binlog文件

-- 删除指定文件之前的所有binlog
PURGE BINARY LOGS TO 'mysql-bin.000003';

-- 删除指定日期之前的所有binlog
PURGE BINARY LOGS BEFORE '2026-10-01 00:00:00';

查看当前正在写的binlog

SHOW MASTER STATUS;

这个命令会显示当前正在写的binlog文件名和位置，主从复制的时候经常用到。

常见坑

坑1：binlog没开，删了数据找不回来

很多同学的MySQL默认没开binlog，结果不小心删了数据，才发现根本没有日志可以恢复，欲哭无泪。生产环境一定要开binlog！

坑2：binlog格式用了STATEMENT，主从不一致

很多同学用默认的STATEMENT格式，结果主从数据不一致。推荐用ROW格式，虽然日志大一点，但是数据一致性有保证。

坑3：binlog占满磁盘

很多同学开了binlog但是没设置过期时间，结果binlog文件越来越多，把磁盘占满了，MySQL直接挂了。一定要设置expire_logs_days，让旧的binlog自动删除。

坑4：恢复数据的时候把DELETE也执行了

很多同学恢复的时候，把DELETE语句也一起执行了，结果恢复完数据又被删了。一定要找到DELETE之前的位置，只恢复到那个位置之前。

坑5：恢复完没验证

很多同学恢复完数据就以为完事了，结果一查发现数据不对。恢复完一定要仔细核对数据，确保没问题了再上线。

总结

MySQL binlog的核心知识点：

1. binlog记录所有写操作，不记录查询
2. binlog的三个作用：数据恢复、主从复制、数据审计
3. 三种格式：
   • STATEMENT：记录SQL语句，日志小，但是可能主从不一致
   • ROW：记录每行数据修改，日志大，但是数据一致，推荐用
   • MIXED：混合模式，自动选择
4. 开启binlog：在my.cnf里配置log_bin和binlog_format
5. 查看binlog：用mysqlbinlog工具，ROW格式要加-vv
6. 恢复数据：找到误操作之前的位置，重新执行之前的binlog
7. 注意设置过期时间：不然binlog会把磁盘占满

binlog是MySQL最重要的日志之一，生产环境必须开。学会了用binlog恢复数据，以后不小心删数据的时候就不用慌了。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都听说过事务隔离级别，但是很多同学搞不清楚读未提交、读提交、可重复读、串行化这四个级别到底有什么区别，也不知道自己项目应该用哪个级别。

很多面试的时候也经常被问到：MySQL的默认隔离级别是什么？能解决什么问题？很多同学答不上来。

这篇文章就把MySQL的事务隔离级别从头到尾讲清楚，看完之后你就彻底搞懂了。

一、先复习一下事务的ACID特性

讲隔离级别之前，先复习一下事务的四个特性，也就是ACID：

1. 原子性（Atomicity）：事务里的操作要么全部成功，要么全部失败回滚
2. 一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏
3. 隔离性（Isolation）：多个事务之间互相隔离，不能互相干扰
4. 持久性（Durability）：事务提交之后，修改是永久的

我们今天讲的隔离级别，就是跟第三个特性"隔离性"有关的。隔离性的程度不同，就有了不同的隔离级别。

二、并发事务会带来什么问题

为什么需要隔离级别？因为多个事务同时操作数据库的时候，会出现各种问题。

常见的并发问题有三个：

1. 脏读

脏读就是：一个事务读到了另一个事务还没提交的数据。

比如：

• 事务A把某条数据从1改成了2，但是还没提交
• 事务B读了这条数据，读到的是2
• 然后事务A回滚了，数据又变回了1
• 事务B读的那个2就是脏数据，这就是脏读

2. 不可重复读

不可重复读就是：一个事务里，两次读同一条数据，结果不一样。

比如：

• 事务A第一次读某条数据，值是1
• 事务B把这条数据改成了2，并且提交了
• 事务A第二次再读这条数据，值变成了2
• 同一个事务里，两次读同一条数据结果不一样，这就是不可重复读

3. 幻读

幻读就是：一个事务里，两次查询的结果集不一样。

比如：

• 事务A查询id > 5的记录，查到了5、6两条
• 事务B插入了一条id=7的记录，并且提交了
• 事务A再查一次，发现多了一条id=7的
• 这就是幻读

注意：不可重复读和幻读的区别：

• 不可重复读：同一条数据，两次读结果不一样（修改导致的）
• 幻读：查询的结果集变多或者变少了（插入/删除导致的）

三、四个事务隔离级别

为了解决上面说的这三个问题，SQL标准定义了四个隔离级别，级别越高，越能解决问题，但是并发性能越差。

1. 读未提交（Read Uncommitted）

最低的隔离级别。一个事务可以读到另一个事务还没提交的数据。

能解决什么问题： 啥也解决不了
会出现什么问题： 脏读、不可重复读、幻读都可能出现

这个级别基本上没人用，所以就不多说了。

2. 读提交（Read Committed，RC）

一个事务只能读到另一个事务已经提交的数据。

能解决什么问题： 解决了脏读
还会出现什么问题： 不可重复读、幻读

Oracle默认就是这个隔离级别。

3. 可重复读（Repeatable Read，RR）

一个事务里，多次读同一条数据，结果是一样的。

能解决什么问题： 解决了脏读、不可重复读
还会出现什么问题： 幻读

注意：MySQL的InnoDB引擎在RR级别下，通过间隙锁解决了幻读问题，所以MySQL的RR级别实际上可以完全解决幻读。

MySQL默认就是这个隔离级别。

4. 串行化（Serializable）

最高的隔离级别。所有事务一个一个排队执行，完全串行。

能解决什么问题： 脏读、不可重复读、幻读全都解决了
缺点： 性能太差了，并发度最低，基本上没人用

四、四个隔离级别对比表

给大家整理了一个表格，一目了然：

五、MySQL默认的隔离级别

很多同学面试的时候被问到"MySQL默认的隔离级别是什么"，答不上来。

记住：MySQL InnoDB默认的隔离级别是可重复读（RR）。

注意和Oracle区分一下：Oracle默认的是读提交（RC）。

六、怎么查看当前的隔离级别

登录MySQL之后，执行下面的命令：

-- 查看全局的隔离级别
SELECT @@global.tx_isolation;

-- 查看当前会话的隔离级别
SELECT @@session.tx_isolation;

执行完之后会看到类似这样的结果：

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这就说明当前是可重复读级别。

七、怎么修改隔离级别

临时修改（当前会话）

-- 改成读提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 改成可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这种修改只对当前连接有效，断开连接之后就恢复了。

全局临时修改

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

这种修改对新连接有效，已经存在的连接不受影响，MySQL重启之后就恢复了。

永久修改

要永久修改的话，需要改配置文件。编辑my.cnf，在[mysqld]下面添加：

[mysqld]
transaction-isolation = READ-COMMITTED

修改完之后重启MySQL服务，就永久生效了。

八、实际项目中应该用哪个级别

很多同学问：实际项目中应该用哪个隔离级别？

一般来说：

• 如果对并发性能要求高：用读提交（RC）就够了，很多互联网公司都是用RC
• 如果对数据一致性要求高：用可重复读（RR），MySQL默认就是这个
• 串行化基本上没人用，性能太差了

其实大部分情况下，用MySQL默认的RR就够了，不用特意改。如果并发特别高，可以考虑改成RC，这样间隙锁就没了，并发性能会好很多。

常见坑

坑1：不知道MySQL默认的隔离级别是RR

很多同学以为MySQL默认是读提交，跟Oracle一样，结果面试答错了。记住：MySQL默认是可重复读（RR）。

坑2：把不可重复读和幻读搞混了

很多同学分不清不可重复读和幻读。记住：

• 不可重复读：同一条数据被修改了，两次读结果不一样
• 幻读：结果集的行数变多或者变少了，是插入/删除导致的

坑3：以为RR级别解决不了幻读

很多同学以为RR级别解决不了幻读，其实MySQL的InnoDB引擎在RR级别下，通过间隙锁是可以解决幻读的。这一点跟标准SQL不一样，MySQL做了增强。

坑4：修改了隔离级别但是没生效

很多同学用SET GLOBAL改了隔离级别，然后用当前连接查，发现还是原来的级别。这是因为SET GLOBAL只对新连接有效，当前连接还是原来的。要当前连接生效的话，用SET SESSION。

总结

MySQL事务隔离级别的核心知识点：

1. 并发事务的三个问题：脏读、不可重复读、幻读
2. 四个隔离级别：读未提交、读提交、可重复读、串行化，级别越高越安全，性能越差
3. 脏读：读到了别的事务还没提交的数据
4. 不可重复读：同一个事务里，两次读同一条数据结果不一样（修改导致的）
5. 幻读：同一个事务里，两次查询的结果集不一样（插入/删除导致的）
6. MySQL InnoDB默认是可重复读（RR），并且通过间隙锁解决了幻读
7. 查看隔离级别：SELECT @@tx_isolation
8. 修改隔离级别：SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL ...
9. 实际项目用RR或者RC就够了，串行化性能太差没人用

事务隔离级别是数据库的基础知识点，不管是面试还是实际开发，都是必须掌握的。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定都遇到过这种情况：两个人同时修改同一条数据，结果后提交的把先提交的覆盖了，数据就不对了。或者两个事务互相等对方释放锁，结果都卡住了，这就是死锁。

这些问题其实都是跟MySQL的锁机制有关的。很多新手同学对锁的概念很模糊，不知道什么是表锁、行锁、间隙锁，也不知道什么时候会加什么锁。这篇文章就把MySQL的锁机制从头到尾讲清楚，看完之后你就对锁有一个完整的认识了。

一、为什么需要锁

为什么数据库需要锁？很简单，因为要保证数据的一致性。

如果没有锁，两个事务同时修改同一条数据，就会出现问题。比如：

• A事务读了一条数据，值是100
• B事务也读了同一条数据，值也是100
• A事务给它加1，变成101，写回去
• B事务也给它加1，变成101，写回去

结果两个操作加起来，应该变成102，结果实际变成了101。这就出问题了。

锁的作用就是：当一个事务在操作数据的时候，其他事务不能操作，这样就保证了数据的一致性。

二、表锁

表锁就是锁整张表。当一个事务给表加了表锁，其他事务就不能操作这张表了。

表锁的特点

• 开销小，加锁快：因为不用锁每一行，直接锁整张表就行
• 不会出现死锁：因为一次就锁整张表，不存在互相等的情况
• 锁粒度大，并发度低：一张表同一时间只能有一个事务写，并发高了性能就差

什么时候用表锁

• 基本上我们用的InnoDB引擎很少用表锁，MyISAM引擎默认就是表锁
• InnoDB引擎在某些特殊情况下也会用表锁，比如：
  • 没有用到索引，导致行锁失效，升级成表锁
  • 要修改很多行数据，优化器觉得用表锁更划算

怎么手动加表锁

-- 加读锁（共享锁）
LOCK TABLES test READ;

-- 加写锁（排他锁）
LOCK TABLES test WRITE;

-- 释放锁
UNLOCK TABLES;

三、行锁

行锁就是锁某一行数据。当一个事务给某一行加了行锁，其他事务就不能操作这一行了，但是可以操作表里的其他行。

行锁的特点

• 开销大，加锁慢：要找到具体的行，然后加锁
• 可能会出现死锁：两个事务分别锁了不同的行，又互相等对方释放
• 锁粒度小，并发度高：不同的行可以同时被不同的事务操作，并发性能好

InnoDB的行锁是怎么实现的

很多同学以为InnoDB的行锁是真的锁数据行，其实不是。InnoDB的行锁是锁索引实现的。

也就是说，你WHERE条件用到了哪个索引，InnoDB就会给那个索引上加锁。如果你的WHERE条件没有用到索引，那InnoDB就没办法给行加锁了，这时候就会扫描全表，把所有行都锁一遍，相当于变成了表锁。

这就是为什么索引失效会导致性能差：不仅查询慢，还会把所有行都锁了，并发性能直接崩了。

行锁的两种模式

1. 共享锁（S锁）：读锁，加了S锁之后，其他事务也能加S锁，但是不能加X锁

   • 比如：SELECT ... LOCK IN SHARE MODE

2. 排他锁（X锁）：写锁，加了X锁之后，其他事务既不能加S锁，也不能加X锁

   • 比如：SELECT ... FOR UPDATE
   • UPDATE、DELETE语句默认都会加X锁

四、间隙锁

间隙锁是InnoDB里比较特殊的一种锁，很多同学都搞不懂。

简单来说，间隙锁就是锁两个值之间的间隙。比如你的表有1、3、5三个值，那间隙锁可以锁 (1,3)、(3,5) 这些区间，让你不能往这个区间里插数据。

间隙锁是干嘛的

间隙锁是为了防止幻读。

什么是幻读？比如：

• 事务A查询id > 5的记录，查到了5、6两条
• 事务B插入了一条id=7的记录
• 事务A再查一次，发现多了一条id=7的，这就是幻读

间隙锁就是为了解决这个问题：当你查询某个范围的数据时，InnoDB会给这个范围的间隙也加上锁，这样其他事务就不能往这个范围里插数据了，就不会出现幻读了。

间隙锁的注意点

1. 间隙锁只会在可重复读（RR）隔离级别下才有
2. 间隙锁和间隙锁之间不冲突，但是间隙锁和插入操作是冲突的
3. 间隙锁会导致加锁的范围变大，本来你只想锁一条记录，结果把周围的间隙也锁了，并发性能就差了

五、临键锁（Next-Key Lock）

临键锁就是行锁 + 间隙锁的组合。InnoDB在可重复读隔离级别下，默认用的就是临键锁。

比如你有一个索引，值是1、3、5、7。如果你查询id=3，那InnoDB加的临键锁就是 (1, 3]，也就是锁了1到3这个区间，包括3本身。

临键锁的作用就是：既锁了这条记录，又锁了它前面的间隙，这样就防止了幻读。

六、死锁

死锁就是两个或者多个事务互相等对方释放锁，结果谁也动不了，就卡住了。

比如：

• 事务A锁了id=1的行，想要锁id=2的行
• 事务B锁了id=2的行，想要锁id=1的行
• 这时候两个事务互相等，就死锁了

怎么解决死锁

MySQL有自动检测死锁的机制，发现死锁之后，会自动回滚其中一个事务，让另一个事务继续执行。

怎么避免死锁

1. 按相同的顺序访问表和行：比如所有事务都先锁id=1，再锁id=2，就不会死锁了
2. 大事务拆成小事务：事务越小，持锁时间越短，死锁概率越低
3. 尽量用索引访问数据：不用索引的话会锁更多的行，更容易死锁
4. 降低隔离级别：比如从可重复读降到读提交，间隙锁就没了，死锁概率也会低一点

七、怎么查看当前有哪些锁

MySQL8.0可以用下面的命令查看当前的锁：

SELECT * FROM performance_schema.data_locks;

或者看一下当前的事务：

SELECT * FROM information_schema.innodb_trx;

常见坑

坑1：没有用索引，行锁变成表锁

很多同学以为自己加的是行锁，结果发现整张表都被锁了。这是因为你的WHERE条件没有用到索引，InnoDB没办法定位到具体的行，只能扫描全表，把所有行都锁了，相当于表锁。

解决方法： 确保你的查询语句用到了索引，这样才会加行锁而不是表锁。

坑2：间隙锁导致并发性能差

很多同学发现自己的并发不高，但是锁冲突很严重。这就是因为间隙锁把范围锁大了。本来你只想锁一条记录，结果把周围的间隙也锁了，其他事务插数据就会被挡住。

解决方法： 如果不需要解决幻读，可以把隔离级别降到读提交（RC），这样就没有间隙锁了，并发性能会好很多。

坑3：事务太长，持锁时间久

很多同学写代码的时候，事务里包含了RPC调用、查Redis、做复杂计算这些耗时操作。结果这个事务持锁的时间特别长，其他事务都在等锁，性能就差了。

解决方法： 事务里只放数据库操作，其他耗时操作都放到事务外面。

坑4：死锁了不知道怎么排查

很多同学遇到死锁就慌了，不知道怎么查。其实MySQL默认会把死锁的信息记录到错误日志里。可以看看MySQL的错误日志，里面有详细的死锁信息。

也可以用这个命令查看最近的死锁：

SHOW ENGINE INNODB STATUS;

里面有个LATEST DETECTED DEADLOCK部分，就是最近一次死锁的信息。

总结

MySQL锁机制的核心知识点：

1. 表锁：锁整张表，开销小，并发低，MyISAM默认用
2. 行锁：锁某一行，开销大，并发高，InnoDB默认用
3. 行锁是锁索引实现的，没有用到索引的话会变成表锁
4. 共享锁（S锁）：读锁，多个事务可以同时加
5. 排他锁（X锁）：写锁，只能有一个事务加
6. 间隙锁：锁两个值之间的间隙，用来防止幻读，只有可重复读隔离级别才有
7. 临键锁：行锁 + 间隙锁，InnoDB默认的加锁方式
8. 死锁：两个事务互相等对方释放锁，MySQL会自动回滚其中一个
9. 避免死锁的方法：按相同顺序访问、小事务、用索引

锁是数据库里比较复杂的一个知识点，但是也是必须掌握的。搞懂了锁，你才能写出高并发的数据库代码。

遇到问题加QQ23979811 协助处理

前言

做网站的同学肯定都遇到过这种情况：网站用户越来越多，数据库压力越来越大，单台数据库扛不住了。这时候怎么办？很常见的方案就是做主从复制，主库写数据，从库读数据，把读写压力分开。

很多新手同学觉得主从复制很难，不敢尝试。其实只要按照步骤来，一步步配置，很快就能搭起来。这篇文章就把一主一从的主从复制搭建完整教程整理出来，从原理到实操，一步一步讲清楚。

一、主从复制是干嘛的

简单来说，主从复制就是：主数据库（Master）负责写数据，从数据库（Slave）负责读数据。主库的数据会自动同步到从库上。

主从复制的作用

1. 读写分离：写操作走主库，读操作走从库，减轻主库压力
2. 数据备份：从库相当于实时备份，主库挂了从库还能顶上
3. 做数据分析：可以在从库上跑复杂的统计查询，不影响主库的业务

主从复制的原理

主从复制的原理其实很简单，三步：

1. 主库把数据变更记录到二进制日志（binlog）里
2. 从库把主库的binlog拉过来，写到自己的中继日志（relay log）里
3. 从库重放中继日志里的SQL，这样数据就和主库一致了

二、准备工作

搭建主从复制之前，先准备好环境：

服务器准备

需要两台服务器：

• 主库服务器：IP假设是 192.168.1.10
• 从库服务器：IP假设是 192.168.1.11

软件要求

• 两台服务器都要安装MySQL，版本最好一样
• 主库和从库的server-id不能一样
• 两台服务器的MySQL端口要能互相通（一般是3306）

三、配置主库

首先配置主库，编辑主库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加这些配置：

[mysqld]
# 唯一的服务器ID，主库和从库不能一样
server-id = 1

# 开启二进制日志
log-bin = mysql-bin

# 需要同步的数据库，多个用逗号隔开
binlog-do-db = testdb

# 不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

修改完之后重启主库的MySQL：

systemctl restart mysqld

给从库创建一个复制账号

主库上要创建一个专门用来复制的用户，从库要用这个账号来连主库拉数据。

登录主库MySQL：

mysql -u root -p

然后执行：

CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'repl123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
FLUSH PRIVILEGES;

这个命令创建了一个叫repl的用户，密码是repl123456，只能从从库的IP（192.168.1.11）连接，并且有复制的权限。

查看主库的状态

接下来要记录主库的binlog文件名和位置，从库配置的时候要用：

SHOW MASTER STATUS;

执行完之后会看到类似这样的结果：

+------------------+----------+--------------+----------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000001 |      156 | testdb       | mysql,information_schema,...     |                   |
+------------------+----------+--------------+----------------------------------+-------------------+

记住File和Position的值，比如这里是 mysql-bin.000001 和 156。

注意：执行完这个命令之后，主库就不要再写数据了，不然Position会变，到时候从库同步就会出错。

四、配置从库

接下来配置从库。编辑从库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加：

[mysqld]
# 唯一的服务器ID，和主库不一样就行
server-id = 2

# 开启中继日志
relay-log = relay-bin

# 只读模式，从库默认只读，防止误写
read_only = 1

修改完之后重启从库的MySQL：

systemctl restart mysqld

配置从库连接主库

登录从库的MySQL：

mysql -u root -p

然后执行下面的命令，告诉从库主库在哪里，用什么账号连接：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=156;

这里的MASTER_LOG_FILE和MASTER_LOG_POS就是刚才在主库上用SHOW MASTER STATUS查出来的值。

启动从库复制

配置完之后，启动从库的复制线程：

START SLAVE;

查看从库状态

看看从库是不是正常运行：

SHOW SLAVE STATUS\G

执行完之后会看到很多信息，重点看这两个：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个都是Yes，那就说明主从复制已经搭好了！

如果是No，那就说明哪里出错了，下面的Last_Error字段会显示错误信息，根据错误信息去排查就行。

五、测试主从复制

现在来测试一下主从复制是不是正常工作。

在主库上创建一个表，插入一些数据：

USE testdb;
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO test (name) VALUES ('张三'), ('李四'), ('王五');

然后去从库上查一下：

USE testdb;
SELECT * FROM test;

如果能看到刚才插入的那三条数据，那就说明主从复制是正常工作的！

六、主从复制的常见问题

主从延迟

主从复制不是实时的，有时候主库写了数据，从库要等一会儿才能读到。如果主库压力很大，或者从库性能差，延迟可能会很大。

可以用这个命令查看从库延迟了多少秒：

SHOW SLAVE STATUS\G

看Seconds_Behind_Master这个值，就是延迟的秒数。如果是0，说明没有延迟。

从库误写数据

很多同学不知道从库是只读的，不小心在从库上写了数据，导致主从数据不一致，复制出错。

解决方法：一定要开启read_only=1，并且不要用root账号在从库上操作。

常见坑

坑1：主库和从库的server-id一样

很多同学配置的时候，忘了改从库的server-id，结果主从复制启动不起来。记住：主库和从库的server-id必须不一样。

坑2：主库防火墙没开

主库的防火墙没开3306端口，导致从库连不上主库。一定要检查防火墙是不是放行了3306端口。

坑3：配置完CHANGE MASTER之后忘了START SLAVE

很多同学配置完主库和从库之后，就以为完事了，结果发现数据根本没同步。这是因为忘了执行START SLAVE启动复制线程。

坑4：SHOW MASTER STATUS之后主库还在写数据

很多同学在主库执行了SHOW MASTER STATUS之后，还在往主库写数据，导致Position变了，从库配置的时候用的是旧的Position，结果同步出错。记住：执行SHOW MASTER STATUS之后，主库就不要再写数据了。

坑5：主库和从库数据一开始就不一致

很多同学的主库已经有很多数据了，直接配置主从复制，结果从库同步的时候出错。正确的做法应该是：先把主库的数据导出，导入到从库，然后再配置主从复制。

总结

MySQL主从复制搭建的步骤：

1. 准备两台服务器，都安装好MySQL
2. 配置主库：设置server-id，开启binlog
3. 主库创建复制账号
4. 主库执行SHOW MASTER STATUS，记录File和Position
5. 配置从库：设置server-id，开启relay log
6. 从库执行CHANGE MASTER TO，指向主库
7. 从库执行START SLAVE启动复制
8. 查看从库状态，确认IO和SQL线程都是Yes
9. 在主库写数据，从库查数据，测试同步

主从复制是数据库架构的基础，学会了之后才能搞读写分离、分库分表这些更高级的东西。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这个错误：用户访问网站的时候，突然报错了，说"MySQL连接失败：Too many connections"。这时候网站就打不开了，用户都在骂娘，你急得满头大汗不知道怎么办。

这个错误其实很常见，就是MySQL的最大连接数不够用了。很多新手同学遇到这个错误就慌了，不知道怎么处理。这篇文章就把这个问题的原因和解决方法讲清楚，以后再遇到就不用慌了。

一、为什么会出现Too many connections错误

MySQL默认的最大连接数是151，这个数在小项目里够用，但是如果项目用户多了，或者连接池设置得太大，就很容易把连接数占满。

当所有连接都被占满的时候，新的请求就进不来了，这时候MySQL就会报"Too many connections"的错误。

常见原因

1. 应用程序连接池设置太大：比如你的应用配置了500个连接，但是MySQL最大连接数才151，那肯定不够用
2. 慢SQL太多：很多SQL执行得很慢，连接迟迟不释放，占着茅坑不拉屎
3. 连接没有及时释放：应用程序写完代码忘了关闭连接，导致连接泄漏
4. 突发流量：突然来一波大流量，把连接数占满了

二、查看当前连接数和最大连接数

先看看你的MySQL现在最大连接数是多少：

SHOW VARIABLES LIKE 'max_connections';

执行完之后会看到类似这样的结果：

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

再看看当前有多少个连接：

SHOW STATUS LIKE 'Threads_connected';

结果类似：

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 120   |
+-------------------+-------+

你看，当前已经用了120个连接，最大才151，再涨一点就满了。

三、临时调整最大连接数

如果现在已经出现Too many connections错误了，可以先临时把最大连接数调大一点，救个急：

SET GLOBAL max_connections = 500;

这个命令执行完之后立刻生效，不用重启MySQL。但是注意：这个修改是临时的，MySQL重启之后就会恢复成原来的值。

四、永久修改最大连接数

要永久修改的话，需要改配置文件。找到MySQL的配置文件 /etc/my.cnf，在 [mysqld] 下面添加：

[mysqld]
max_connections = 1000

修改完之后重启MySQL服务：

systemctl restart mysqld

这样以后MySQL重启之后最大连接数还是1000，不会变回去。

注意：不要设得太大

很多同学觉得既然不够用，那就设大点，直接设个10000。这是不对的。

每个连接MySQL都要分配内存，如果连接数设得太大，每个连接占的内存加起来就很多了，可能会把服务器内存吃满。

一般来说，最大连接数设到500~1000就够了，要看你的服务器内存有多大。内存大就设大点，内存小就设小点。

五、查看连接是从哪来的

如果连接数突然涨得很猛，你得看看这些连接都是从哪来的，是不是有哪个程序疯了一直在创建连接。

SELECT 
    host, 
    COUNT(*) AS count
FROM information_schema.processlist
GROUP BY host
ORDER BY count DESC;

这个命令会把每个IP的连接数统计出来，你看看是不是某个IP连接特别多，如果是的话，就去查那个程序是不是有问题。

六、查看正在执行的SQL

如果很多连接都在执行SQL，那可能是慢SQL太多导致的。看看现在都在执行什么SQL：

SHOW PROCESSLIST;

执行完之后会列出所有正在运行的连接和SQL语句。你看看有没有执行了很久还没跑完的SQL，如果有的话，那就是罪魁祸首。

七、杀掉空闲连接

如果有很多连接都是空闲的（Sleep状态），占着连接不干活，那可以把它们杀掉，释放连接。

先看看有多少个Sleep状态的连接：

SELECT COUNT(*) FROM information_schema.processlist WHERE command = 'Sleep';

如果Sleep状态的连接很多，可以把它们杀掉。不过手动一个个杀太麻烦了，可以设置一个自动断开空闲连接的参数：

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

这两个参数的意思是：连接如果空闲超过60秒，MySQL就自动把它断开，释放连接。

同样，这个修改是临时的，要永久生效的话需要写到配置文件里：

[mysqld]
wait_timeout = 60
interactive_timeout = 60

八、从根本上解决问题

光调大最大连接数只是治标不治本，要从根本上解决问题，还得从应用层面下手：

1. 检查应用程序是不是有连接泄漏：用完连接记得释放，不要一直占着
2. 优化慢SQL：把执行慢的SQL优化一下，减少连接占用时间
3. 合理设置连接池大小：连接池不要设得太大，比最大连接数小一点就行
4. 使用连接池中间件：比如MyCat、ProxySQL这些数据库中间件，可以统一管理连接

常见坑

坑1：改完max_connections之后重启又变回去了

很多同学用SET GLOBAL改完最大连接数之后，以为就完事了，结果MySQL一重启又变回原来的值。这是因为SET GLOBAL是临时修改，要永久生效必须改配置文件。

坑2：max_connections设得太大

很多同学一遇到连接数不够，就直接把max_connections改成10000，结果MySQL直接起不来了，或者服务器内存被吃满了。每个连接都要占内存，连接数越大，内存消耗越多，要根据服务器内存来设，不要盲目设大。

坑3：只改了max_connections，没改表连接数

其实MySQL还有一个参数叫max_user_connections，是限制单个用户的最大连接数的。如果你给某个用户限制了最大连接数，就算全局max_connections很大，那个用户的连接数到上限了还是会报错。

可以用这个命令查看：

SHOW VARIABLES LIKE 'max_user_connections';

坑4：杀掉了正在执行重要SQL的连接

很多同学一看到连接数满了，就不管三七二十一把所有连接都杀掉。结果把正在执行重要业务SQL的连接也杀了，导致业务出问题。杀连接之前一定要看清楚，不要乱杀。

总结

MySQL Too many connections错误的解决方法：

1. 先查看当前最大连接数和已用连接数
2. 临时救急：用SET GLOBAL调大max_connections
3. 永久解决：改my.cnf配置文件，设置max_connections
4. 看看连接都是从哪来的，有没有异常
5. 优化慢SQL，减少连接占用时间
6. 设置wait_timeout，自动断开空闲连接
7. 从应用层面优化，减少连接泄漏

记住：调大最大连接数只是治标，优化SQL和代码才是治本。不要一遇到连接数不够就盲目调大max_connections，先找找根本原因。

遇到问题加QQ23979811 协助处理

前言

做网站开发或者运维的同学，肯定会遇到这种情况：需要给开发或者运营同学一个数据库账号，但是又不能给root权限，怕他们误操作删库。这时候就需要用到MySQL的用户和权限管理功能了。

很多新手同学只会用root账号登录数据库，不知道怎么创建新用户、怎么分配权限。这篇文章就把MySQL用户创建和权限管理的完整教程整理出来，从创建用户、授权、改密码到删除用户，一步一步讲清楚。

一、创建用户

首先我们来创建一个新用户。MySQL创建用户有两种方式：CREATE USER 和 GRANT。

方式1：用CREATE USER创建用户

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456';

这个命令创建了一个叫testuser的用户，密码是123456，只能从本机（localhost）连接MySQL。

如果你想让用户可以从任何地方连接，可以把localhost改成%：

CREATE USER 'testuser'@'%' IDENTIFIED BY '123456';

注意：生产环境不建议用%，最好指定具体的IP地址，更安全。

方式2：用GRANT创建用户并直接授权

其实更常用的是创建用户的同时直接授权，一步到位：

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost' IDENTIFIED BY '123456';

这个命令会自动创建testuser用户，并且把testdb数据库的所有权限都给它。

二、给用户授权

创建完用户之后，我们来给用户分配权限。MySQL的权限粒度很细，可以精确到某张表、某个字段。

常用权限说明

• ALL PRIVILEGES：所有权限
• SELECT：查询权限
• INSERT：插入权限
• UPDATE：更新权限
• DELETE：删除权限
• CREATE：创建表/数据库权限
• DROP：删除表/数据库权限
• ALTER：修改表结构权限
• INDEX：创建索引权限
• CREATE VIEW：创建视图权限
• PROCESS：查看进程权限
• RELOAD：执行flush权限
• SHOW DATABASES：查看所有数据库权限

给用户所有权限

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

这个命令的意思是：把testdb数据库里所有表的所有权限都给testuser用户。

只给用户查询和修改权限

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';

这样用户就只能查询、插入、修改、删除数据，不能修改表结构，也不能删表。

只给用户某张表的权限

GRANT SELECT ON testdb.users TO 'testuser'@'localhost';

这样用户只能查询testdb库里的users表，其他表都访问不了。

给用户所有数据库的权限

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

.的意思是所有数据库的所有表。这个权限基本相当于root了，要谨慎使用。

授权之后一定要刷新权限

每次给用户授权之后，一定要执行下面这个命令刷新权限，不然授权可能不生效：

FLUSH PRIVILEGES;

三、查看用户权限

想知道某个用户有什么权限，可以用下面这个命令：

SHOW GRANTS FOR 'testuser'@'localhost';

执行完之后会显示这个用户的所有权限。

查看所有用户

如果想看看MySQL里有哪些用户，可以查询mysql.user表：

SELECT user, host FROM mysql.user;

四、修改用户密码

如果要修改某个用户的密码，可以用下面的命令：

ALTER USER 'testuser'@'localhost' IDENTIFIED BY '新密码';

修改完之后也要刷新权限：

FLUSH PRIVILEGES;

五、回收用户权限

如果用户权限给多了，想收回某些权限，可以用REVOKE命令：

收回所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';

收回某个具体权限

REVOKE DELETE ON testdb.* FROM 'testuser'@'localhost';

这样用户就没有删除数据的权限了。

回收完权限之后也要刷新：

FLUSH PRIVILEGES;

六、删除用户

如果某个用户不需要了，可以删除：

DROP USER 'testuser'@'localhost';

注意：删除用户的时候，用户已有的权限也会一起被删掉。

七、创建用户的安全建议

1. 不要随便给用户ALL PRIVILEGES：按需授权，只给需要的权限就行
2. 不要用%允许所有IP连接：生产环境最好指定具体的IP
3. 不要用root账号给应用用：单独创建一个应用账号，只给需要的权限
4. 密码要复杂一点：不要用123456这种简单密码
5. 定期清理不用的用户：不用的用户及时删掉，减少安全隐患

常见坑

坑1：授权之后忘了FLUSH PRIVILEGES

很多同学授权完之后直接就用了，结果发现权限没生效。这就是因为忘了执行FLUSH PRIVILEGES。每次授权或者改密码之后，一定要执行一下这个命令刷新权限。

坑2：用户创建了但是连不上

很多同学创建完用户之后，用这个用户去连接数据库，结果连接失败。这一般是因为：

1. 你创建用户的时候指定的是localhost，但是你从其他IP连接
2. 防火墙没放行MySQL端口
3. 用户密码输错了

坑3：%和localhost搞混了

很多同学不知道'localhost'@'%'和'%'@'localhost'的区别。记住：用户名是前面的，后面的是允许连接的IP。

坑4：给了权限但是还是操作不了

有时候给了用户权限，但是用户还是操作不了。这一般是因为：

1. 忘了FLUSH PRIVILEGES
2. 表或者字段的权限没给
3. 用户根本就没有这个数据库的权限

总结

MySQL用户创建和权限管理的核心内容：

1. 创建用户：CREATE USER 或者 GRANT 直接创建并授权
2. 给用户授权：GRANT 权限 ON 数据库.表 TO 用户@IP
3. 查看权限：SHOW GRANTS FOR 用户
4. 改密码：ALTER USER 用户 IDENTIFIED BY 新密码
5. 回收权限：REVOKE 权限 ON 数据库.表 FROM 用户
6. 删除用户：DROP USER 用户
7. 所有操作完之后都要 FLUSH PRIVILEGES 刷新权限

记住这些命令，以后给应用或者同事分配数据库账号的时候就不用慌了。安全第一，不要随便给用户root权限。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定都遇到过中文乱码的问题。本来好好的中文，存到数据库里就变成问号或者乱码了。这其实很大概率就是字符集设置不对导致的。

很多新手同学不知道utf8和utf8mb4有什么区别，建库的时候直接用默认的utf8，结果存个emoji表情就报错了，或者存进去之后变成乱码。

这篇文章就详细讲讲MySQL字符集的设置方法，以及utf8和utf8mb4到底有什么区别，应该怎么选。

一、utf8和utf8mb4的区别

首先大家要搞清楚一个坑：MySQL里的utf8并不是真正的UTF-8。

MySQL里的utf8字符集，每个字符最多只能存3个字节。而真正的UTF-8编码，有些字符是占4个字节的，比如emoji表情（😀😂👍这些），还有一些生僻字。

所以如果你用utf8字符集，存emoji表情的时候就会报错，或者存进去之后变成乱码。

而utf8mb4才是真正的UTF-8，每个字符最多可以存4个字节，emoji表情、生僻字都能正常存。

总结一下：

• utf8：每个字符最多3字节，存不了emoji表情
• utf8mb4：每个字符最多4字节，完全兼容UTF-8，推荐使用

现在做新项目，直接用utf8mb4就对了，不要再用utf8了。

二、查看当前MySQL字符集设置

先看看你的MySQL现在用的是什么字符集：

SHOW VARIABLES LIKE '%character%';

执行完之后会看到类似下面的输出：

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

重点看这几个：

• character_set_server：服务器默认字符集
• character_set_database：当前数据库的字符集
• character_set_client：客户端字符集
• character_set_connection：连接字符集
• character_set_results：查询结果字符集

最好这几个都统一成utf8mb4，这样就不会有乱码问题了。

三、新建数据库的时候指定utf8mb4

如果你要新建一个数据库，直接在建库的时候指定字符集：

CREATE DATABASE testdb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

这样建出来的数据库默认就是utf8mb4字符集了。

新建表的时候也可以指定：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

四、修改MySQL默认字符集

如果你想把MySQL的默认字符集改成utf8mb4，可以修改my.cnf配置文件。

找到MySQL的配置文件 /etc/my.cnf，在 [mysqld] 下面添加：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

修改完之后重启MySQL服务：

systemctl restart mysqld

这样以后新建的数据库和表就默认都是utf8mb4字符集了。

五、修改已有数据库的字符集

如果你的数据库已经建好了，想改成utf8mb4，可以执行：

ALTER DATABASE testdb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

注意：这个只会修改数据库的默认字符集，已经建好的表的字符集不会变。

六、修改已有表的字符集

如果要修改某一张表的字符集，可以执行：

ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

这样整张表的所有字段都会改成utf8mb4字符集。

如果要批量修改整个数据库里所有表的字符集，可以生成一个SQL脚本：

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.tables
WHERE table_schema = 'testdb';

执行完之后会生成一堆ALTER TABLE语句，把这些语句复制出来执行就行。

七、连接的时候指定字符集

除了数据库和表的字符集，连接的时候也要指定字符集，不然还是可能会乱码。

用命令行连接的时候：

mysql -u root -p --default-character-set=utf8mb4

或者登录之后执行：

SET NAMES utf8mb4;

常见坑

坑1：用了utf8字符集，存emoji表情报错

很多同学建库的时候用的是utf8，结果存个emoji表情就报错了，或者存进去之后变成问号。这就是因为utf8最多只能存3个字节，emoji表情是4个字节的。

解决方法就是把字符集改成utf8mb4。

坑2：数据库字符集改了，但是表的字符集没改

很多同学改完数据库的字符集之后，以为就完事了，结果还是乱码。这是因为数据库的默认字符集改了，但是已经建好的表的字符集还是旧的。

一定要把表的字符集也一起改了，最好是整张表转成utf8mb4。

坑3：连接字符集不对，导致中文乱码

有时候数据库和表都是utf8mb4，但是查出来还是乱码。这一般是连接字符集不对。可以执行 SET NAMES utf8mb4 试一下，或者连接的时候加上 --default-character-set=utf8mb4 参数。

坑4：utf8mb4_general_ci和utf8mb4_unicode_ci选哪个

很多同学不知道这两个排序规则有什么区别。简单来说：

• utf8mb4_general_ci：速度快，但是对一些特殊字符的排序可能不太准确
• utf8mb4_unicode_ci：排序更准确，但是速度稍微慢一点

一般网站用utf8mb4_general_ci就够了，不用太纠结。

总结

MySQL字符集和utf8mb4的核心知识点：

1. MySQL的utf8不是真正的UTF-8，最多只能存3字节
2. utf8mb4才是真正的UTF-8，能存emoji表情，推荐使用
3. 新建数据库和表的时候直接用utf8mb4
4. 老的数据库如果要改，要同时改数据库和表的字符集
5. 连接的时候也要指定utf8mb4，不然还是会乱码

现在做新项目，直接全程用utf8mb4就对了，省得以后再改。

遇到问题加QQ23979811 协助处理

前言

做运维的同学经常会遇到这种情况：服务器磁盘突然满了，不知道是哪个数据库或者哪张表占了这么多空间。或者想知道自己的数据库现在有多大了，好提前规划扩容。

这时候就需要用SQL命令来查看数据库和表的大小了。很多新手同学不知道怎么查，只能傻乎乎的用du命令看整个data目录的大小，看不到具体是哪个数据库或哪张表占了空间。

这篇文章就把MySQL查看数据库大小和表大小的常用SQL命令都整理出来，以后遇到磁盘满了的问题就能快速定位了。

一、查看所有数据库的大小

如果你想知道MySQL里每个数据库分别占了多少空间，可以执行下面这个SQL：

SELECT 
    table_schema AS '数据库名',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

执行完之后会列出所有数据库的大小，单位是MB，从大到小排序。这样你一眼就能看出来哪个数据库最占空间了。

二、查看指定数据库的大小

如果你只想看某个数据库的大小，比如testdb数据库，可以执行：

SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '数据库大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb';

把testdb换成你要查询的数据库名就行。

三、查看数据库里所有表的大小

知道了哪个数据库大，接下来就要看这个数据库里哪张表最大了。执行下面这个SQL：

SELECT 
    table_name AS '表名',
    table_rows AS '行数',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY (data_length + index_length) DESC;

这样就能列出testdb数据库里所有表的大小，从大到小排序。你一眼就能看出来哪张表最占空间了。

四、查看单张表的大小

如果你只想看某一张表的大小，比如users表，可以执行：

SELECT 
    table_name AS '表名',
    table_rows AS '行数',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb' 
AND table_name = 'users';

五、查看表的详细大小（数据+索引分开）

有时候你想知道一张表里数据占了多少，索引占了多少，方便优化。可以执行：

SELECT 
    table_name AS '表名',
    ROUND(data_length / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY (data_length + index_length) DESC;

六、查看数据库里所有表的行数

有时候你只想知道每张表有多少行数据，不需要看大小。可以执行：

SELECT 
    table_name AS '表名',
    table_rows AS '行数'
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY table_rows DESC;

七、查看MySQL整体占了多少磁盘空间

如果你想知道整个MySQL一共占了多少磁盘空间，可以执行：

SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'MySQL总大小(GB)'
FROM information_schema.tables;

常见坑

坑1：table_rows显示的行数不准确

很多同学发现，用 information_schema.tables 查出来的 table_rows 和实际 count(*) 出来的行数对不上。

这是因为 information_schema 里的 table_rows 是估算值，不是精确值。InnoDB引擎的话，这个值大概有10%左右的误差。如果需要精确的行数，还是得用 SELECT COUNT(*) FROM 表名。

坑2：查出来的大小和实际磁盘占用对不上

有时候你用SQL查出来的数据库大小，和用 du 命令查出来的 data 目录大小对不上，差了不少。

这是因为MySQL的data目录里除了表数据和索引，还有：

1. redo log、undo log这些日志文件
2. ibdata1这些系统表空间文件
3. 二进制日志文件

这些都不算在表数据和索引大小里。所以用SQL查出来的大小一般会比实际磁盘占用小一点。

坑3：查完之后发现有个库特别大，不知道是什么

有时候你查完发现有个库特别大，但是你根本不知道这个库是干嘛的。一般可能是：

1. mysql系统库
2. information_schema、performance_schema这些系统库
3. 之前测试的时候建的库，忘了删了

坑4：磁盘满了但是查出来数据库没多大

如果磁盘满了，但是查出来数据库没多大，那大概率不是数据库的问题，可能是：

1. 日志文件太大（比如nginx日志、MySQL的binlog日志）
2. 网站上传的文件太大
3. 系统临时文件太多

这时候需要去查一下具体是哪个目录占了空间。

总结

MySQL查看数据库和表大小的常用SQL命令总结一下：

1. 查看所有数据库大小：查 information_schema.tables，按 table_schema 分组
2. 查看指定数据库大小：加 WHERE table_schema = '数据库名'
3. 查看数据库里所有表的大小：按 table_name 分组
4. 查看单张表的大小：加 AND table_name = '表名'
5. 数据和索引分开看：data_length 和 index_length
6. 查看行数：table_rows 字段

记住这些SQL命令，以后遇到磁盘满了的问题，就能快速定位是哪个数据库、哪张表占了空间，不用再瞎找了。

遇到问题加QQ23979811 协助处理

前言

做运维或者开发的同学都知道，数据库是整个网站最核心的资产。一旦数据库出了问题，数据丢了，那损失可就大了。所以做好数据库备份是非常重要的一件事。

很多新手同学可能从来没做过数据库备份，等真的出问题了才后悔莫及。这篇文章就详细讲讲MySQL数据库怎么备份和恢复，包括常用的备份命令、恢复方法，还有自动定时备份的脚本。

一、备份方法

MySQL备份最常用的工具就是mysqldump，它是MySQL自带的备份工具，不需要额外安装。

1. 备份整个数据库

备份单个数据库是最常用的场景。比如你要把本地的testdb数据库备份成一个sql文件：

mysqldump -u root -p testdb > /root/testdb.sql

执行完之后输入MySQL的密码，就开始备份了。备份完之后会在/root/目录下生成一个testdb.sql文件。

2. 备份单张表

有时候你只需要备份数据库里的某一张表，比如只备份users表：

mysqldump -u root -p testdb users > /root/users.sql

3. 备份所有数据库

如果你想把MySQL里所有的数据库都备份下来，可以用 --all-databases 参数：

mysqldump -u root -p --all-databases > /root/all.sql

4. 只备份表结构，不备份数据

有时候你只需要备份表结构，不需要备份数据，这时候可以用 --no-data 参数：

mysqldump -u root -p --no-data testdb > /root/testdb_schema.sql

5. 备份的时候加锁保证数据一致性

备份大表的时候，为了保证备份过程中数据不被修改，可以加 --lock-tables 参数：

mysqldump -u root -p --lock-tables testdb > /root/testdb.sql

6. 备份的时候压缩文件

如果数据库很大，备份出来的sql文件也会很大，这时候可以用gzip压缩一下：

mysqldump -u root -p testdb | gzip > /root/testdb.sql.gz

二、恢复方法

备份完了，怎么恢复呢？

1. 恢复整个数据库

恢复整个数据库的命令：

mysql -u root -p testdb < /root/testdb.sql

注意：恢复之前一定要确保testdb这个数据库已经存在了。如果不存在，需要先创建数据库：

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. 恢复单张表

恢复单张表的话，先登录MySQL，然后选择对应的数据库，再用source命令恢复：

mysql -u root -p

USE testdb;
source /root/users.sql;

3. 恢复压缩的备份文件

如果你的备份文件是用gzip压缩过的，恢复的时候需要先解压：

gunzip < /root/testdb.sql.gz | mysql -u root -p testdb

三、自动定时备份脚本

手动备份太麻烦了，我们可以写个脚本，每天自动备份数据库。

备份脚本内容

创建一个备份脚本 /root/mysql_backup.sh：

#!/bin/bash

# 数据库配置
DB_USER="root"
DB_PASSWORD="你的MySQL密码"
DB_NAME="testdb"
BACKUP_DIR="/root/backup"

# 备份文件名，用日期命名
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/testdb_$DATE.sql"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE

# 压缩备份文件
gzip $BACKUP_FILE

# 删除7天前的备份文件
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "备份完成：$BACKUP_FILE.gz"

给脚本加执行权限

chmod +x /root/mysql_backup.sh

添加到定时任务

用crontab添加定时任务，每天凌晨3点自动备份：

crontab -e

添加下面这行：

0 3 * * * /root/mysql_backup.sh

这样每天凌晨3点就会自动备份数据库了，而且只保留最近7天的备份，旧的会自动删除。

常见坑

坑1：备份的时候忘了加密码，执行的时候卡住了

很多同学写备份命令的时候，直接写 mysqldump -u root -p testdb > xxx.sql，然后执行的时候就会停下来让你输入密码。如果是在脚本里用的话，就会卡住。

解决方法是把密码直接写在命令里：mysqldump -u root -p你的密码 testdb > xxx.sql。注意-p和密码之间没有空格。

坑2：恢复的时候数据库不存在

很多同学恢复的时候直接执行 mysql -u root -p testdb < xxx.sql，结果报错说数据库不存在。记住恢复之前一定要先创建好对应的数据库。

坑3：备份文件太大，恢复的时候超时了

大文件恢复的时候很容易超时，或者因为各种原因中断。这时候可以用source命令在MySQL内部恢复，会稳定很多。

坑4：备份文件存在本地，服务器挂了备份也没了

很多同学备份文件直接放在服务器本地，觉得这样就安全了。其实不对，如果服务器磁盘坏了，或者被黑客删了，备份文件也没了。

正确的做法是：

1. 每天备份完之后，把备份文件下载到本地电脑
2. 或者上传到云存储（比如阿里云OSS、腾讯云COS）
3. 重要的备份最好异地多存几份

总结

MySQL数据库备份和恢复的核心内容：

1. 备份命令：mysqldump，常用参数有 --all-databases、--no-data、--lock-tables
2. 恢复命令：mysql < 备份文件，或者用source命令
3. 自动备份：写个shell脚本，配合crontab定时执行
4. 备份文件一定要异地存储，不能只放在服务器本地

数据库备份是个大事，千万不要等出问题了才想起来备份。养成每天自动备份的好习惯，出问题的时候才能从容应对。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这种情况：数据库里有一张几百万行甚至上千万行的大表，做分页查询的时候，第一页第二页查得很快，但是翻到第100页、第1000页的时候，查询就变得特别慢，甚至直接卡死。

这是为什么呢？怎么优化才能让大表分页查询不卡呢？这篇文章就详细讲讲MySQL大表分页越查越慢的原因，以及对应的优化方案。

为什么分页会越查越慢

先看一下最常见的分页查询写法：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

这个SQL的意思是：从 orders 表里，跳过100000条数据，取后面10条。

很多同学以为这个查询很快就能出来，其实不是的。MySQL执行这个SQL的时候，会先扫描前100010条数据，然后把前面的100000条扔掉，只返回最后10条。

也就是说，你翻到第1000页的时候，MySQL要先扫描100000多条数据，然后再扔掉。翻的页数越深，MySQL要扫描和扔掉的数据就越多，所以查询就会越来越慢。

优化方案

方案1：子查询优化（延迟关联）

这是最常用的优化方案。思路就是：先用索引把需要的id查出来，然后再根据id去查完整的数据。

原来的慢查询：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

优化后的写法：

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) t ON o.id = t.id;

这样优化的好处是：子查询里的 SELECT id FROM orders ORDER BY id LIMIT 100000, 10 只需要扫描索引就能完成，不需要回表查完整数据，速度会快很多。然后再根据查出来的10个id，去关联完整的表数据，只需要10次回表操作就行。

这种优化方式也叫"延迟关联"，是大表分页最常用的优化手段。

方案2：书签分页（记住上一页最后一条id）

如果你的业务场景允许（比如只允许"上一页"和"下一页"，不允许直接跳页），那用书签分页是最快的。

思路就是：记住上一页最后一条数据的id，下一页直接从这个id后面开始查。

第一页：

SELECT * FROM orders ORDER BY id LIMIT 10;

假设第一页最后一条数据的id是100。

第二页：

SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

这样不管你翻到第几页，查询速度都是一样快的，因为每次都是从某个id开始往后查10条，MySQL不需要扫描前面的几十万条数据。

这种分页方式的缺点是不支持直接跳页，只能一页一页往后翻。很多信息流产品（比如朋友圈、抖音）都是用这种分页方式。

方案3：覆盖索引

如果你查询的字段刚好都在索引里，那MySQL就不需要回表查完整数据了，速度会快很多。

比如你只需要查 id, title, create_time 这几个字段，那你可以建一个联合索引：

CREATE INDEX idx_title_time ON orders(title, create_time);

然后查询的时候只查这几个字段：

SELECT id, title, create_time FROM orders ORDER BY create_time LIMIT 100000, 10;

这样MySQL直接从索引里就能拿到所有需要的数据，不需要回表，查询速度会快很多。

方案4：禁止跳页查询

如果你的业务场景允许，可以考虑禁止用户直接跳页。比如只允许用户看前100页，或者只允许"上一页"和"下一页"。

因为翻到第1000页、第10000页的用户其实很少，大部分用户都是看前几页。禁止跳页可以避免大量深分页查询拖慢数据库。

常见坑

坑1：用LIMIT offset的时候，offset越大越慢

很多同学不知道LIMIT offset的原理，以为MySQL直接就跳过了前面的offset条数据。其实不是的，MySQL是先把前面的offset条数据都查出来，然后再扔掉。所以offset越大，查询越慢。

坑2：优化了子查询，但是子查询里没有用到索引

如果你的ORDER BY字段没有建索引，那就算用了子查询优化，还是会很慢。一定要确保ORDER BY的字段有索引。

坑3：书签分页的时候，id不是连续的

书签分页的前提是你的id是连续的、递增的。如果你的业务场景里id可能会被删除，导致id不连续，那书签分页可能会漏数据。这时候需要根据实际情况调整分页策略。

坑4：用count(*)查总页数很慢

大表查总页数也很慢，比如 SELECT COUNT(*) FROM orders，几百万行的表可能要查好几秒。

这个优化方案是：

1. 如果业务不需要精确的总页数，可以用"大约XX万条"来代替
2. 或者单独建一张表存总条数，定期更新
3. 或者用explain估算一下大概的行数

总结

MySQL大表分页越查越慢的问题，核心原因就是LIMIT offset会先扫描offset条数据然后扔掉，offset越大越慢。

常用的优化方案：

1. 子查询优化（延迟关联）：先查id，再关联完整数据
2. 书签分页：记住上一页最后一条id，下一页从这个id开始查
3. 覆盖索引：只查索引里有的字段，避免回表
4. 禁止跳页：不允许直接翻到第1000页

根据你的业务场景选择合适的优化方案，大表分页查询慢的问题就能很好地解决了。

遇到问题加QQ23979811 协助处理

前言

做网站开发或者运维的同学肯定经常遇到这种情况：把本地写好的项目传到服务器上，需要把本地的数据库sql文件导入到服务器的MySQL里。但是导入的时候经常报错，各种问题层出不穷，折腾半天都导不进去。

这篇文章就把MySQL导入sql文件时最常见的报错和解决方法都整理出来，帮你快速搞定导入问题。

常用导入命令

首先先讲一下最常用的导入sql文件的命令：

mysql -u root -p 数据库名 < /路径/你的sql文件.sql

比如你要把 test.sql 导入到 testdb 数据库里，命令就是：

mysql -u root -p testdb < /root/test.sql

执行完之后输入密码，就开始导入了。

常见报错及解决方法

1. 报错：Unknown database 'xxx'

这个报错的意思是你要导入的数据库不存在。很多同学直接就执行导入命令了，结果数据库还没建，肯定报错。

解决方法：

先登录MySQL，创建对应的数据库：

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

创建完数据库之后再执行导入命令。

2. 报错：File 'xxx.sql' not found

这个报错的意思是找不到sql文件。一般是因为你写的文件路径不对。

解决方法：

先确认一下sql文件的实际路径：

pwd
ls -l

确认文件确实在那个路径下，然后用绝对路径来导入，不要用相对路径。

3. 报错：Access denied for user 'root'@'localhost'

这个报错的意思是权限不足，没有权限往这个数据库里导入数据。

解决方法：

确认你登录MySQL用的用户有没有这个数据库的权限。如果是root用户，一般不会有这个问题。如果是其他用户，需要给用户授权：

GRANT ALL PRIVILEGES ON testdb.* TO '用户名'@'localhost';
FLUSH PRIVILEGES;

4. 报错：Packet larger than max_allowed_packet bytes

这个报错一般是因为你的sql文件太大了，超过了MySQL允许的最大数据包大小。

解决方法：

先查看当前的 max_allowed_packet 大小：

SHOW VARIABLES LIKE 'max_allowed_packet';

然后临时调大一点，比如调到100M：

SET GLOBAL max_allowed_packet = 104857600;

或者修改my.cnf配置文件，在 [mysqld] 下面加上：

[mysqld]
max_allowed_packet = 100M

然后重启MySQL服务再导入。

5. 报错：You have an error in your SQL syntax

这个报错的意思是SQL语法错误。一般是因为你的sql文件里有某些SQL语句在当前MySQL版本不兼容。

解决方法：

看看报错信息里说的是哪一行的语法错误，然后打开sql文件找到那一行，把不兼容的语法改一下。

比如从MySQL 8.0导出的sql文件导入到MySQL 5.7里，经常会有一些语法不兼容的问题，需要手动改一下。

6. 导入到一半就卡住了，或者超时了

如果你的sql文件特别大（几百M甚至几个G），导入的时候可能会卡住，或者因为超时而中断。

解决方法：

1. 先把 sql_mode 调整一下，去掉一些严格模式：

SET GLOBAL sql_mode = '';

2. 关闭自动提交，提升导入速度：

SET autocommit = 0;

3. 如果还是太慢，可以考虑用 source 命令导入：

USE testdb;
source /root/test.sql;

7. 中文乱码问题

导入完数据之后发现中文都变成乱码了。这一般是因为字符集不匹配。

解决方法：

导入之前先设置一下字符集：

SET NAMES utf8mb4;

然后再导入数据。或者导入的时候加上 --default-character-set 参数：

mysql -u root -p --default-character-set=utf8mb4 testdb < /root/test.sql

常见坑

坑1：忘记先创建数据库就直接导入

很多同学导入的时候直接就执行命令了，结果数据库还没建，肯定报错。一定要先确认数据库存在，再导入。

坑2：用相对路径导入找不到文件

很多同学写的是 mysql -u root -p testdb < test.sql，结果提示找不到文件。这是因为你当前所在的目录和sql文件所在的目录不一样，相对路径找不到。最好用绝对路径来导入。

坑3：导入完数据之后中文乱码

导入完数据发现中文都变成问号或者乱码了，这一般是字符集的问题。导入的时候一定要指定 utf8mb4 字符集，不然中文很容易乱码。

坑4：大文件导入到一半就中断了

大文件导入的时候很容易因为超时而中断，特别是几百M以上的sql文件。这时候可以调整一下超时时间，或者用 source 命令在MySQL内部导入，会稳定很多。

总结

MySQL导入sql文件的常见报错和解决方法总结一下：

1. 数据库不存在 → 先创建数据库
2. 文件找不到 → 用绝对路径
3. 权限不足 → 给用户授权
4. 数据包太大 → 调大 max_allowed_packet
5. 语法错误 → 检查SQL语句兼容性
6. 导入慢/卡住 → 关闭自动提交，用source命令
7. 中文乱码 → 指定utf8mb4字符集

记住这些常见问题和解决方法，以后导入sql文件的时候就不会再手忙脚乱了。

遇到问题加QQ23979811 协助处理

前言

做运维或者开发的同学肯定遇到过这种情况：服务器上的MySQL数据库用了很久，突然有一天忘记了root密码，登录不进去了。这时候项目可能正在运行，不能随便重装数据库，那怎么办呢？

别慌，MySQL提供了一种跳过权限验证的启动方式，我们可以用这种方式登录进去，然后重置root密码。这篇文章就详细讲讲在Linux环境下怎么重置MySQL的root密码。

操作步骤

1. 先停止MySQL服务

首先我们需要把正在运行的MySQL服务停掉：

systemctl stop mysqld

如果你用的是MariaDB，命令是：

systemctl stop mariadb

执行完之后可以检查一下MySQL是不是真的停了：

ps aux | grep mysql

如果没有看到mysql进程，说明已经停掉了。

2. 跳过权限验证启动MySQL

接下来我们要用 --skip-grant-tables 参数启动MySQL，这样启动之后就可以不用密码直接登录了。

mysqld_safe --skip-grant-tables &

注意最后面的 & 符号，这个是让MySQL在后台运行。

执行完这个命令之后，等个几秒钟，让MySQL完全启动起来。

3. 无密码登录MySQL

现在我们就可以不用密码直接登录MySQL了：

mysql -u root

执行完之后直接就进入MySQL命令行界面了，不需要输入密码。

4. 修改root密码

登录进去之后，我们就可以修改root密码了。

首先选择mysql数据库：

USE mysql;

然后修改root密码。注意不同版本的MySQL，修改密码的语句不一样：

MySQL 5.7版本：

UPDATE user SET authentication_string=PASSWORD('你的新密码') WHERE User='root';

MySQL 8.0版本：

ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';

修改完密码之后，一定要执行一下刷新权限的命令：

FLUSH PRIVILEGES;

然后退出MySQL：

EXIT;

5. 关闭跳过权限的MySQL进程，正常重启MySQL

现在我们需要把刚才用跳过权限方式启动的MySQL进程关掉，然后用正常方式重启。

先找到MySQL的进程ID：

ps aux | grep mysqld_safe

然后杀掉这个进程：

kill -9 进程ID

等个几秒钟，让MySQL完全关闭。然后用正常方式启动MySQL：

systemctl start mysqld

6. 验证新密码能不能登录

最后我们来验证一下新密码能不能正常登录：

mysql -u root -p

输入你刚才设置的新密码，如果能正常登录进去，说明密码重置成功了。

常见坑

坑1：修改密码之后忘了执行FLUSH PRIVILEGES

很多同学修改完密码之后直接就退出了，结果发现重启MySQL之后密码还是不对。这是因为修改完密码之后一定要执行 FLUSH PRIVILEGES 刷新权限，不然修改不会生效。

坑2：MySQL版本不一样，修改密码的语句不一样

很多同学抄网上的教程，结果修改密码的语句不对，报错了。这是因为MySQL 5.7和MySQL 8.0修改密码的方式不一样。5.7用的是 authentication_string 字段，8.0用的是 ALTER USER 语句。

你可以先执行一下这个命令看看你的MySQL版本：

SELECT VERSION();

然后用对应的语句修改密码。

坑3：跳过权限启动的时候没加 & 符号

如果执行 mysqld_safe --skip-grant-tables 的时候没加 & 符号，那这个命令就会一直占着你的终端窗口，你没法继续操作了。所以一定要记得在最后面加个 & 符号，让它在后台运行。

坑4：杀进程的时候杀错了

杀进程的时候一定要看清楚，不要把其他无关的进程杀掉了。最好先 ps aux | grep mysqld 看一下，确认哪个是我们刚才启动的跳过权限的MySQL进程。

总结

Linux环境下重置MySQL root密码的步骤总结一下：

1. 停止MySQL服务
2. 用 --skip-grant-tables 参数跳过权限启动
3. 无密码登录MySQL
4. 修改root密码
5. 刷新权限
6. 关闭跳过权限的进程，正常重启MySQL
7. 用新密码验证登录

记住这几个步骤，以后忘记root密码的时候就不用慌了，按照这个流程操作就能搞定。

遇到问题加QQ23979811 协助处理

前言

做数据库开发的同学都知道，索引是提升查询性能最有效的手段。很多时候一张几百万行的大表，加个合适的索引查询速度就能从几秒变成几毫秒。

但是很多同学不知道的是，就算你加了索引，如果SQL语句写得不对，索引也可能会失效，结果还是全表扫描，查询慢得要命。这篇文章就把MySQL索引失效的常见场景都整理出来，帮你避免踩坑。

常见索引失效场景

1. 对索引列使用函数或运算

这是最常见的索引失效场景。如果你在索引列上用了函数或者做了运算，MySQL就没法用到这个索引了。

失效的写法：

-- 对索引列使用函数
SELECT * FROM users WHERE DATE(create_time) = '2026-10-05';

-- 对索引列做运算
SELECT * FROM orders WHERE amount + 100 > 500;

正确的写法：

-- 改成范围查询
SELECT * FROM users 
WHERE create_time >= '2026-10-05 00:00:00' 
AND create_time < '2026-10-06 00:00:00';

-- 把运算移到右边
SELECT * FROM orders WHERE amount > 400;

2. 不满足最左前缀原则

如果你建的是联合索引（比如 index(a, b, c)），查询的时候必须从最左边的列开始用，否则索引就会失效。

联合索引：index(name, age, city)

失效的写法：

-- 没有用到最左列name
SELECT * FROM users WHERE age = 25;

-- 跳过了中间的列
SELECT * FROM users WHERE name = '张三' AND city = '北京';

正确的写法：

-- 从最左列开始
SELECT * FROM users WHERE name = '张三';

-- 按顺序用
SELECT * FROM users WHERE name = '张三' AND age = 25;

3. 使用like以%开头

如果你的查询条件是 like '%xxx' 或者 like '%xxx%'，那索引就会失效，因为MySQL不知道从哪里开始找。

失效的写法：

-- %在前面
SELECT * FROM users WHERE name LIKE '%张%';

-- %在前后都有
SELECT * FROM users WHERE name LIKE '%张';

正确的写法：

-- %在后面，这样可以用到索引
SELECT * FROM users WHERE name LIKE '张%';

如果一定要用前后模糊匹配的话，可以考虑全文索引或者ES搜索引擎。

4. 索引列使用 != 或 <>

如果查询条件用了 != 或者 <>，MySQL优化器会觉得大部分数据都要查，还不如直接全表扫描，所以索引就失效了。

失效的写法：

SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status <> 1;

正确的写法：

-- 改成in或者or
SELECT * FROM users WHERE status IN (0, 2, 3);

5. 使用or连接条件

如果or两边的列不是都有索引，那整个查询就会全表扫描。

失效的写法：

-- name有索引，但是age没有索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

正确的写法：

-- 用union all代替or
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25;

或者给age也加上索引，这样两边都能用索引。

6. 隐式类型转换

这是最坑的一个场景。如果你的索引列是字符串类型，但是查询的时候传了数字，MySQL会自动做类型转换，结果索引就失效了。

失效的写法：

-- 索引列phone是varchar类型，但是传了数字
SELECT * FROM users WHERE phone = 13800138000;

正确的写法：

-- 加引号，传字符串
SELECT * FROM users WHERE phone = '13800138000';

7. 使用not in、not exists

not in 和 not exists 也很容易导致索引失效。因为MySQL优化器对这类反查询的优化做得不太好。

失效的写法：

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

正确的写法：

-- 改成left join
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

8. 优化器认为全表扫描更快

还有一种情况是，虽然你写的SQL完全符合索引使用规则，但是MySQL优化器自己判断说"这张表数据太少了，全表扫描比走索引还快"，那它就会选择全表扫描。

这种情况是正常的，不用纠结。一般小表全表扫描确实更快。

如何检查索引是否生效

那怎么知道自己写的SQL有没有用到索引呢？很简单，用EXPLAIN关键字看执行计划就行。

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看这几个字段：

• type：如果是 ALL 就是全表扫描，说明索引失效了；如果是 ref、eq_ref、const 就说明用到索引了
• key：这里显示实际用到的索引名，如果是 NULL 说明没用到索引
• rows：扫描的行数，越大说明效率越低

常见坑

坑1：加了索引但是没生效，以为索引没用

很多同学加了索引之后发现查询还是慢，就觉得索引没用。其实大概率是你的SQL写法有问题，导致索引失效了。一定要先用EXPLAIN看一下执行计划，确认索引有没有被用到。

坑2：联合索引建了不用最左列

很多同学建了联合索引，但是查询的时候没用到最左边的列，结果索引白建了。一定要记住最左前缀原则。

坑3：隐式类型转换导致索引失效

这个最坑，因为写SQL的时候完全看不出来。比如手机号、身份证号这些字符串类型的字段，查询的时候一定要加引号，不然索引就失效了。

总结

MySQL索引失效的常见场景总结一下：

1. 对索引列使用函数或运算
2. 不满足最左前缀原则
3. like以%开头
4. 使用 != 或 <>
5. 使用or连接条件
6. 隐式类型转换
7. 使用not in、not exists
8. 表太小，优化器选择全表扫描

写SQL的时候一定要注意避开这些坑，写完之后用EXPLAIN检查一下索引有没有生效。这样你的数据库查询性能才能真正提上去。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这种情况：网站打开越来越慢，数据库CPU占用很高，但就是不知道哪条SQL语句拖慢了整个系统。这时候就需要用到MySQL的慢查询日志功能，把执行慢的SQL语句记录下来，然后逐个优化。

这篇文章就详细讲讲如何开启MySQL慢查询日志，以及怎么用它来定位和优化慢SQL语句。

操作步骤

1. 先查看当前慢查询日志的状态

登录MySQL之后，先看看慢查询日志有没有开启：

SHOW VARIABLES LIKE '%slow_query_log%';

执行完之后会看到类似下面的输出：

+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /var/lib/mysql/izuf6w7x1x2x3x4x5x6x-slow.log  |
+---------------------+-----------------------------------------------+

从这里可以看到，slow_query_log 是 OFF 状态，说明慢查询日志没有开启。

2. 查看当前慢查询时间阈值

慢查询的默认时间阈值是10秒，也就是说执行时间超过10秒的SQL才会被记录。这个阈值对生产环境来说太大了，一般我们会改成1秒或者2秒。

查看当前阈值：

SHOW VARIABLES LIKE 'long_query_time';

3. 临时开启慢查询日志（重启后失效）

如果只是想临时排查问题，可以直接在MySQL命令行里开启，不需要重启服务：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值为1秒
SET GLOBAL long_query_time = 1;

这样设置之后，执行时间超过1秒的SQL语句就会被记录到慢查询日志文件里了。

4. 永久开启慢查询日志（修改配置文件）

临时开启的方式在MySQL重启之后就会失效，如果想长期开启，需要修改MySQL的配置文件。

找到MySQL的配置文件，一般是 /etc/my.cnf 或者 /etc/mysql/my.cnf，在 [mysqld] 下面添加以下配置：

[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件位置
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值，单位秒
long_query_time = 1
# 没有用到索引的SQL也记录下来
log_queries_not_using_indexes = 1

修改完配置文件之后，重启MySQL服务：

systemctl restart mysqld

5. 查看慢查询日志内容

慢查询日志开启之后，执行慢的SQL语句就会被记录到日志文件里。我们可以直接查看日志文件：

cat /var/log/mysql/slow.log

不过慢查询日志的格式比较复杂，直接看日志文件不太直观。我们可以用 mysqldumpslow 工具来分析：

# 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

6. 使用EXPLAIN分析慢SQL

找到慢SQL之后，下一步就是分析这条SQL为什么慢。我们可以用 EXPLAIN 关键字来查看SQL的执行计划：

EXPLAIN SELECT * FROM users WHERE name = '张三';

执行完之后会输出很多字段，我们重点关注这几个：

• type：访问类型，最好是 const、eq_ref，最差是 ALL（全表扫描）
• key：实际用到的索引，如果是 NULL 说明没用到索引
• rows：扫描的行数，这个值越大越慢
• Extra：额外信息，如果看到 Using filesort 或者 Using temporary 就说明需要优化了

常见坑

坑1：开启了慢查询但日志文件里什么都没有

很多同学开启了慢查询日志，但是去看日志文件却发现什么都没有。这通常是因为以下几个原因：

1. 慢查询时间阈值设置得太大了，你的SQL执行时间还没超过阈值
2. 日志文件的路径不对，你看的不是正确的日志文件
3. MySQL没有权限写入日志文件，需要检查文件权限

坑2：long_query_time设置了但不生效

有时候你设置了 long_query_time = 1，但是发现执行时间0.5秒的SQL也被记录下来了。这是因为当前已经建立的MySQL连接还是用的旧的阈值，需要重新连接MySQL才能生效。

坑3：慢查询日志文件太大占满磁盘

如果你的网站访问量很大，慢查询日志可能会增长得非常快，几天就把磁盘占满了。所以一定要配置日志切割，或者定期清理慢查询日志文件。

总结

MySQL慢查询日志是定位数据库性能问题的必备工具，核心就三步：

1. 开启慢查询日志，设置合理的时间阈值（一般1秒）
2. 用mysqldumpslow工具分析慢查询日志，找到最慢的SQL
3. 用EXPLAIN分析慢SQL的执行计划，针对性优化（加索引、改写SQL等）

掌握了慢查询日志的使用方法，网站数据库性能问题就能快速定位和解决了。

遇到问题加QQ23979811 协助处理

前言

CentOS 7 开始，系统默认的防火墙管理工具从原来的 iptables 换成了 firewalld。很多刚接触 CentOS 7 的运维同学，部署完 Nginx 或者 Apache 之后发现网站访问不了，最后排查半天发现是防火墙没放行 80 和 443 端口。

这篇文章就详细讲讲 CentOS 7 下用 firewalld 开放 80、443 端口，并且确保重启后依然生效的完整操作步骤。

操作步骤

1. 先确认 firewalld 服务状态

在配置端口之前，先确认防火墙服务是否正在运行：

systemctl status firewalld

如果看到 active (running) 就说明防火墙正在运行。如果是 inactive 状态，说明防火墙没启动，可以先启动它：

systemctl start firewalld
systemctl enable firewalld

2. 查看当前已经开放的端口和服务

firewall-cmd --list-all

执行完之后会看到类似下面的输出：

public (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0
  sources:
  services: ssh dhcpv6-client
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

从这里可以看到，默认只开放了 ssh 和 dhcpv6-client 两个服务，80 和 443 端口都没开。

3. 永久开放 80 端口

这里要注意，必须加上 --permanent 参数，否则重启服务器之后配置就丢了。

firewall-cmd --permanent --add-port=80/tcp

执行成功之后会返回 success。

4. 永久开放 443 端口

同样的方式开放 443 端口，这个是 HTTPS 默认端口：

firewall-cmd --permanent --add-port=443/tcp

5. 重载防火墙使配置生效

很多同学加完端口就以为完事了，其实不对。firewalld 有运行时配置和永久配置之分，加了 --permanent 的配置写入了永久配置，但还需要重载一下才能在当前运行时生效：

firewall-cmd --reload

6. 验证端口是否真的开放了

最后再检查一下：

firewall-cmd --list-ports

如果看到 80/tcp 和 443/tcp 都在列表里，就说明配置成功了。

常见坑

坑 1：忘记加 --permanent 参数

这是最常见的错误。如果不加 --permanent，配置只在当前运行时生效，服务器一重启就没了。很多同学配置完当时测试没问题，第二天服务器重启网站又打不开了，就是这个原因。

正确写法：firewall-cmd --permanent --add-port=80/tcp

坑 2：加完端口忘记 reload

加了 --permanent 之后，必须执行 firewall-cmd --reload 才能生效。不然你会发现配置写进去了，但端口还是不通。

坑 3：想一次性开放多个端口怎么办

如果要开放一批连续的端口，比如 1000 到 2000，可以用这个写法：

firewall-cmd --permanent --add-port=1000-2000/tcp

如果要开放多个不连续的端口，就执行多次 add-port 命令就行。

坑 4：怎么移除已经开放的端口

如果不小心开错了端口，要移除的话用 remove-port：

firewall-cmd --permanent --remove-port=8080/tcp
firewall-cmd --reload

总结

CentOS 7 下 firewalld 开放端口的核心就三步：

1. 用 --permanent --add-port=端口号/tcp 添加永久规则
2. 执行 firewall-cmd --reload 重载生效
3. 用 firewall-cmd --list-ports 确认结果

记住这三个关键点，基本不会踩坑。Web 服务常用的 80 和 443 端口开放之后，外部才能正常访问你的网站。

遇到问题加QQ23979811 协助处理

前言

网站加载慢，很大原因是没开压缩。CSS、JS 文件动辄几百 KB，开了 gzip 之后能压缩到原来的 1/3，加载速度提升明显。本文就讲一下怎么在 Nginx 里配置 gzip 压缩。

为什么要开 gzip

举个例子：

• 一个 CSS 文件原始大小 100KB
• 开了 gzip 之后变成 20KB
• 传输体积减少 80%

浏览器收到压缩内容后自动解压，用户无感知。

基础配置

在 nginx.conf 的 http 块里加上：

http {
    gzip on;
    gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript image/svg+xml;
    gzip_min_length 1k;
    gzip_comp_level 6;
    gzip_vary on;
}

配置项说明

gzip on：
开启 gzip 压缩。

gzip_types：
哪些类型的文件需要压缩。

• text/plain：文本
• text/css：CSS
• application/json：JSON
• application/javascript：JS
• image/svg+xml：SVG 图片

gzip_min_length：
小于 1KB 的文件不压缩（压缩本身也占 CPU，小文件不值得压）。

gzip_comp_level：
压缩级别，1 到 9，6 比较合适（越大压得越狠，但越占 CPU）。

gzip_vary on：
给响应头加 Vary: Accept-Encoding，告诉 CDN 和缓存，这个内容是压缩过的。

完整示例

http {
    gzip on;
    gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript image/svg+xml;
    gzip_min_length 1k;
    gzip_comp_level 6;
    gzip_vary on;
    gzip_buffers 16 8k;
    gzip_http_version 1.1;
}

检查并重载

nginx -t
systemctl reload nginx

验证 gzip 是否生效

用 curl 测试

curl -H "Accept-Encoding: gzip" -I https://www.example.com

如果返回头里有：

Content-Encoding: gzip

说明 gzip 生效了。

用浏览器 F12 测试

打开浏览器 F12 → Network → 点一个 JS/CSS 文件 → Response Headers，看有没有 Content-Encoding: gzip。

注意事项

不要压缩图片

JPG、PNG 这些图片本身已经是压缩过的，再压 gzip 没什么效果，还浪费 CPU。所以 gzip_types 里不要加 image/jpeg、image/png 这些。

不要压缩太小的文件

太小的文件（比如小于 1KB）压缩完反而可能变大，所以用 gzip_min_length 过滤掉。

压缩级别不要太高

gzip_comp_level 设到 9 没意义，压不了多少，CPU 占用还高。6 就够了。

常见坑

坑1：开了 gzip 但没生效

配置写了，但浏览器看到的还是没压缩的。

排查：

1. 看文件大小是不是小于 gzip_min_length
2. 看 gzip_types 里有没有包含这种类型
3. 看浏览器请求头里有没有 Accept-Encoding: gzip

坑2：HTML 没压缩

只压缩了 CSS 和 JS，HTML 没压缩。

原因： gzip_types 里没写 text/html。

注意： text/html 是默认就压缩的，不用写在 gzip_types 里。

坑3：CDN 缓存了没压缩的版本

开了 gzip，但 CDN 上缓存的还是旧的没压缩的文件。

解决： 清一下 CDN 缓存，或者等 CDN 自动更新。

总结

gzip 配置很简单，就几行：

开了 gzip 网站加载速度立马快一截，零成本优化，一定要开。

遇到问题加QQ23979811 协助处理

前言

Nginx 默认就会记录访问日志，放在 /var/log/nginx/ 目录下。但日志每天都在涨，时间长了占满磁盘，还不好查。本文就讲一下 Nginx 日志配置、切割和分析方法。

默认日志位置

Nginx 默认日志文件：

/var/log/nginx/
├── access.log    # 访问日志
└── error.log     # 错误日志

日志格式

Nginx 默认的日志格式叫 combined，记录这些信息：

• 客户端 IP
• 访问时间
• 请求方法和路径
• 状态码
• 响应大小
• Referer
• User-Agent

自定义日志格式

在 nginx.conf 的 http 块里可以自定义日志格式：

http {
    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;
}

常用变量

按站点分日志

不同站点的日志分开存，方便排查问题：

server {
    listen 80;
    server_name www.example.com;

    access_log  /var/log/nginx/example.com.access.log;
    error_log   /var/log/nginx/example.com.error.log;
}

日志切割

用 logrotate 切割

CentOS7 装完 Nginx 就自动配了 logrotate，不用管。配置文件在：

/etc/logrotate.d/nginx

内容大概是：

/var/log/nginx/*.log {
    daily
    missingok
    rotate 14
    compress
    notifempty
    create 0640 nginx adm
    sharedscripts
    postrotate
        if [ -f /var/run/nginx.pid ]; then
            kill -USR1 `cat /var/run/nginx.pid`
        fi
    endscript
}

意思是：

• 每天切割一次
• 保留 14 天
• 压缩旧日志
• 空日志不切割

手动测试切割

logrotate -f /etc/logrotate.d/nginx

日志分析常用命令

查看访问量前 10 的 IP

awk '{print $1}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -10

查看访问量前 10 的页面

awk '{print $7}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -10

查看状态码统计

awk '{print $9}' /var/log/nginx/access.log | sort | uniq -c | sort -nr

查看爬虫访问

grep 'Googlebot' /var/log/nginx/access.log

查看某个 IP 的访问

grep '192.168.1.1' /var/log/nginx/access.log

常见坑

坑1：日志越来越大占满磁盘

日志不切割，时间长了把磁盘占满。

解决： 确认 logrotate 在运行：

cat /var/lib/logrotate.status

或者手动执行一次：

logrotate -f /etc/logrotate.d/nginx

坑2：日志里 IP 不对

日志里全是 127.0.0.1，不是真实用户 IP。

原因： Nginx 前面还有一层代理（比如 CDN），或者配了反向代理。

解决： 用 $http_x_forwarded_for 代替 $remote_addr：

log_format main '$http_x_forwarded_for - $remote_user [$time_local] "$request" '
                '$status $body_bytes_sent "$http_referer" "$http_user_agent"';

坑3：日志级别太高

错误日志里全是 notice，没用还占空间。

解决： 把错误日志级别调高一点：

error_log /var/log/nginx/error.log warn;

可选级别：debug、info、notice、warn、error、crit。

总结

Nginx 日志的常用操作：

日志是排查问题的第一手资料，学会看日志比什么都重要。

遇到问题加QQ23979811 协助处理

前言

前端项目写完了，怎么部署到服务器上让别人访问？现在前端都是 Vue、React 这种单页应用，打包出来一堆静态文件，扔到 Nginx 上就能跑。本文就讲一下怎么用 Nginx 部署前端静态网站。

第一步：打包前端项目

前端项目一般用 npm 或 yarn 打包：

npm run build

打包完会生成一个 dist 目录，里面就是所有静态文件（HTML、CSS、JS）。

第二步：上传到服务器

把 dist 目录上传到服务器上，比如放到 /var/www/html/my-app：

# 用 scp 上传
scp -r dist/ root@你的服务器IP:/var/www/html/my-app

或者用 FTP 工具上传。

第三步：配置 Nginx

最简单的配置

server {
    listen 80;
    server_name www.example.com;

    root /var/www/html/my-app;
    index index.html;

    location / {
        try_files $uri $uri/ /index.html;
    }
}

关键配置说明

root 网站根目录：

root /var/www/html/my-app;

就是你上传静态文件的目录。

index 默认首页：

index index.html;

用户访问网站根目录的时候，默认打开 index.html。

try_files 路由支持：

try_files $uri $uri/ /index.html;

这个很重要！Vue/React 这种单页应用，前端路由是靠 H5 History API 实现的。如果用户直接访问 /about 这种路径，Nginx 找不到对应的文件，就会 404。

加了 try_files 之后，Nginx 找不到文件就返回 index.html，交给前端路由处理。

检查并重载

nginx -t
systemctl reload nginx

测试

浏览器访问 www.example.com，应该能看到你的前端页面了。

加上 HTTPS

如果要配 HTTPS，加上 SSL 配置：

server {
    listen 80;
    server_name www.example.com;
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl;
    server_name www.example.com;

    ssl_certificate /etc/letsencrypt/live/www.example.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/www.example.com/privkey.pem;

    root /var/www/html/my-app;
    index index.html;

    location / {
        try_files $uri $uri/ /index.html;
    }
}

常见优化配置

开启 gzip 压缩

server {
    # ... 其他配置

    gzip on;
    gzip_types text/css application/javascript application/json image/svg+xml;
    gzip_min_length 1k;
    gzip_comp_level 6;
}

开启 gzip 之后，文件传输体积小很多，加载更快。

静态资源缓存

location ~* \.(js|css|png|jpg|jpeg|gif|ico|svg|woff|woff2|ttf|eot)$ {
    expires 30d;
    add_header Cache-Control "public, immutable";
}

静态资源缓存 30 天，不用每次都重新下载。

隐藏 Nginx 版本号

http {
    server_tokens off;
}

在 nginx.conf 的 http 块里加上这行，安全一点。

常见坑

坑1：刷新页面 404

路由是 /about，刷新就 404。

原因： Nginx 找不到 /about 对应的文件。

解决： 加 try_files：

location / {
    try_files $uri $uri/ /index.html;
}

坑2：JS/CSS 404

打包完访问页面，JS 和 CSS 全 404。

原因： 打包的时候 publicPath 没配对。

解决： Vue 项目在 vue.config.js 里配：

module.exports = {
  publicPath: '/'
}

React 项目在 .env 里配：

PUBLIC_URL=/

坑3：跨域问题

前端调后端接口，浏览器报跨域错误。

解决： 用 Nginx 反向代理把接口请求转发到后端，不要让前端直接请求后端地址。

location /api/ {
    proxy_pass http://后端地址;
}

坑4：权限不够

访问报 403 Forbidden。

原因： Nginx 没有读取文件的权限。

解决：

chown -R nginx:nginx /var/www/html/my-app
chmod -R 755 /var/www/html/my-app

总结

前端项目部署到 Nginx 的完整流程：

1. 打包：npm run build
2. 上传：把 dist 目录传到服务器
3. 配置 Nginx：root 指向静态文件目录，加 try_files
4. 重载：nginx -t && systemctl reload nginx
5. 测试：浏览器访问域名

部署静态网站是前端开发的必备技能，记住 try_files 那行就不会踩刷新 404 的坑了。

遇到问题加QQ23979811 协助处理

前言

现在做后端开发，后端服务一般跑在 3000、8080 这种端口上，总不能让用户直接访问 IP:端口 吧？这时候就需要 Nginx 反向代理，把 80 端口的请求转发到后端服务。本文就讲一下 Nginx 反向代理的配置方法。

什么是反向代理

简单说，反向代理就是：

• 用户访问 Nginx（80 端口）
• Nginx 把请求转发给后端服务（比如 3000 端口）
• 后端服务返回结果给 Nginx
• Nginx 再返回给用户

用户不知道后端服务的存在，只跟 Nginx 打交道。

基本配置

最简单的反向代理

server {
    listen 80;
    server_name www.example.com;

    location / {
        proxy_pass http://127.0.0.1:3000;
    }
}

就这么简单，所有访问 www.example.com 的请求都会被转发到 http://127.0.0.1:3000。

检查并重载

nginx -t
systemctl reload nginx

常用配置项

proxy_pass 后端地址

proxy_pass http://127.0.0.1:3000;

后端服务的地址，可以是 IP 加端口，也可以是域名。

传递真实客户端 IP

默认情况下，后端服务看到的是 Nginx 的 IP，不是真实用户的 IP。需要加上几个请求头：

location / {
    proxy_pass http://127.0.0.1:3000;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}

这样后端服务就能拿到真实用户的 IP 了。

代理 WebSocket

如果后端服务用了 WebSocket，需要额外配置：

location /ws {
    proxy_pass http://127.0.0.1:3000;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
}

超时设置

location / {
    proxy_pass http://127.0.0.1:3000;
    proxy_connect_timeout 60s;
    proxy_read_timeout 60s;
    proxy_send_timeout 60s;
}

如果后端服务响应慢，可以把超时时间调大一点。

按路径分流

不同路径转发到不同后端服务：

server {
    listen 80;
    server_name www.example.com;

    location /api/ {
        proxy_pass http://127.0.0.1:3000;
    }

    location /admin/ {
        proxy_pass http://127.0.0.1:8080;
    }

    location / {
        root /var/www/html;
        index index.html;
    }
}

这样：

• /api/ 开头的请求 → 3000 端口
• /admin/ 开头的请求 → 8080 端口
• 其他请求 → 静态文件

常见坑

坑1：502 Bad Gateway

访问报 502，说明 Nginx 连不上后端服务。

排查：

1. 看后端服务有没有启动：curl http://127.0.0.1:3000
2. 看后端服务监听的地址对不对，是不是只监听了 127.0.0.1
3. 看防火墙有没有挡住

坑2：后端拿不到真实 IP

日志里全是 Nginx 的 IP，不是用户真实 IP。

解决： 加上那几个请求头：

proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

坑3：静态资源 404

前端是 Vue/React 这种单页应用，刷新页面就 404。

原因： Nginx 找不到对应的静态文件。

解决： 加个 try_files：

location / {
    root /var/www/html;
    index index.html;
    try_files $uri $uri/ /index.html;
}

所有找不到的路径都返回 index.html，交给前端路由处理。

坑4：HTTPS 代理后端 HTTP

前端是 HTTPS，后端是 HTTP，浏览器报混合内容错误。

解决： 确保 Nginx 和后端之间是 HTTP 没问题，但要让后端知道原始请求是 HTTPS：

proxy_set_header X-Forwarded-Proto $scheme;

总结

Nginx 反向代理的核心就是 proxy_pass，把请求转发到后端服务。常用配置：

反向代理是 Nginx 最常用的功能之一，做 Web 开发必备。

遇到问题加QQ23979811 协助处理