前言

用Docker跑MySQL，删了容器数据就没了？那是没挂数据卷。本文讲怎么用Docker跑MySQL并持久化数据。

一、基本运行

最简单的跑法

docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=123456 \
  mysql:5.7

问题： 删了容器，数据库里的数据全没了。

二、加数据卷持久化

完整命令

docker run -d \
  --name mysql \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -v /data/mysql:/var/lib/mysql \
  mysql:5.7

解释

• -p 3306:3306 端口映射
• -e MYSQL_ROOT_PASSWORD=123456 设置root密码
• -v /data/mysql:/var/lib/mysql 数据卷挂载
  • 宿主机 /data/mysql 目录
  • 容器里 /var/lib/mysql 目录

这样容器删了，数据还在宿主机上。

三、其他常用配置

1. 设置字符集

docker run -d \
  --name mysql \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -v /data/mysql:/var/lib/mysql \
  -v /data/mysql/my.cnf:/etc/mysql/conf.d/my.cnf \
  mysql:5.7 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

2. 创建数据库和用户

docker run -d \
  --name mysql \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -e MYSQL_DATABASE=mydb \
  -e MYSQL_USER=myuser \
  -e MYSQL_PASSWORD=123456 \
  -v /data/mysql:/var/lib/mysql \
  mysql:5.7

3. 挂载配置文件

先在宿主机创建my.cnf：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_connections=1000

然后挂载：

docker run -d \
  --name mysql \
  -v /data/mysql/my.cnf:/etc/mysql/conf.d/my.cnf \
  -v /data/mysql:/var/lib/mysql \
  mysql:5.7

四、Docker Compose方式

version: '3'
services:
  mysql:
    image: mysql:5.7
    container_name: mysql
    restart: always
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: mydb
    volumes:
      - /data/mysql:/var/lib/mysql
      - /data/mysql/my.cnf:/etc/mysql/conf.d/my.cnf

启动：

docker-compose up -d

五、连接MySQL

进入容器连接

docker exec -it mysql mysql -uroot -p123456

从宿主机连接

mysql -h127.0.0.1 -P3306 -uroot -p123456

用Navicat等工具

• 主机：服务器IP
• 端口：3306
• 用户名：root
• 密码：123456

六、备份和恢复

备份数据库

docker exec mysql mysqldump -uroot -p123456 mydb > /data/backup/mydb.sql

恢复数据库

docker exec -i mysql mysql -uroot -p123456 mydb < /data/backup/mydb.sql

七、常见坑

坑1：密码不对

报错：

Access denied for user 'root'@'localhost'

检查： 环境变量 MYSQL_ROOT_PASSWORD 设了没？

坑2：权限问题

宿主机 /data/mysql 目录权限不对：

chmod -R 777 /data/mysql

坑3：端口被占用

3306端口被宿主机的MySQL占了：

Bind for 0.0.0.0:3306 failed: port is already allocated

解决： 换个端口：

-p 3307:3306

坑4：数据没持久化

没加 -v 挂载，删了容器数据就没了。

检查： 有没有 -v /data/mysql:/var/lib/mysql？

坑5：第一次启动慢

MySQL第一次启动要初始化数据，要等个十几秒。

等不及就先别连，过一会再试。

坑6：字符集不对，中文乱码

加参数：

--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci

八、推荐配置

docker run -d \
  --name mysql \
  --restart=always \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=你的密码 \
  -v /data/mysql:/var/lib/mysql \
  -v /data/mysql/my.cnf:/etc/mysql/conf.d/my.cnf \
  mysql:5.7

• --restart=always 开机自动启动
• 数据挂载到 /data/mysql
• 配置文件挂载

总结

记住：

1. 一定要挂数据卷，不然删容器数据就没了
2. 用 -e MYSQL_ROOT_PASSWORD 设密码
3. 用 -p 3306:3306 映射端口
4. 配置文件也可以挂载进去

最常用：

docker run -d --name mysql -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -v /data/mysql:/var/lib/mysql \
  mysql:5.7

遇到问题加QQ23979811 协助处理

前言

手动装Nginx、PHP、MySQL太麻烦，用Docker Compose一键部署LNMP环境，几分钟搞定。

一、项目结构

lnmp/
├── docker-compose.yml
├── nginx/
│   ├── conf.d/
│   │   └── default.conf
│   └── nginx.conf
├── php/
│   └── php.ini
├── mysql/
│   └── my.cnf
└── www/
    └── index.php

二、docker-compose.yml

version: '3'

services:
  nginx:
    image: nginx:latest
    container_name: lnmp_nginx
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./nginx/conf.d:/etc/nginx/conf.d
      - ./nginx/nginx.conf:/etc/nginx/nginx.conf
      - ./www:/var/www/html
    depends_on:
      - php
    networks:
      - lnmp

  php:
    image: php:fpm
    container_name: lnmp_php
    volumes:
      - ./www:/var/www/html
      - ./php/php.ini:/usr/local/etc/php/php.ini
    networks:
      - lnmp

  mysql:
    image: mysql:5.7
    container_name: lnmp_mysql
    ports:
      - "3306:3306"
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: mydb
    networks:
      - lnmp

networks:
  lnmp:
    driver: bridge

三、Nginx配置

nginx/conf.d/default.conf

server {
    listen       80;
    server_name  localhost;
    root   /var/www/html;
    index  index.php index.html;

    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }

    location ~ \.php$ {
        fastcgi_pass   php:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  /var/www/html$fastcgi_script_name;
        include        fastcgi_params;
    }
}

四、PHP配置

php/php.ini

[PHP]
date.timezone = Asia/Shanghai
upload_max_filesize = 20M
post_max_size = 20M
memory_limit = 128M

五、MySQL配置

mysql/my.cnf

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

六、测试文件

www/index.php

<?php
phpinfo();
?>

七、启动

cd lnmp
docker-compose up -d

查看状态

docker-compose ps

访问

http://localhost

能看到phpinfo页面就是成功了。

八、常用命令

九、连接MySQL

从宿主机连接

mysql -h 127.0.0.1 -P 3306 -u root -p123456

从PHP连接

<?php
$pdo = new PDO('mysql:host=mysql;dbname=mydb', 'root', '123456');
echo "连接成功";
?>

注意：host填mysql，不是localhost，是docker-compose.yml里的服务名。

十、安装PHP扩展

进入PHP容器

docker-compose exec php bash

安装pdo_mysql

docker-php-ext-install pdo_mysql
docker-php-ext-install mysqli

重启PHP

docker-compose restart php

常见坑

坑1：Nginx报502

502 Bad Gateway，原因是PHP容器没启动，或者地址不对。

解决：确认PHP容器在运行，fastcgi_pass填服务名php:9000。

坑2：MySQL启动失败

原因：数据目录权限不对，或者密码没设。

解决：确认MYSQL_ROOT_PASSWORD设了，数据目录权限改成777。

坑3：文件修改不生效

改了www里的文件，浏览器刷新没变。

解决：确认volumes挂载了./www:/var/www/html。

坑4：端口被占用

port is already allocated，原因是宿主机80端口被占了。

解决：改端口，比如改成8080。

坑5：数据丢失

删了容器，MySQL数据没了。

解决：一定要挂载数据卷，./mysql/data:/var/lib/mysql。

总结

部署五步：建项目目录结构，写docker-compose.yml，写配置文件，docker-compose up -d启动，浏览器访问测试。

记住：数据一定要挂载，不然删容器数据就没了。

遇到问题加QQ23979811 协助处理

前言

网站突然卡死，查什么都慢，可能是表被锁住了。怎么查谁锁的表，怎么杀掉锁表进程？

一、查看正在执行的进程

SHOW FULL PROCESSLIST;

看State列，要是 Locked 就是锁表了。

二、查看锁表信息

查看当前锁

SELECT * FROM information_schema.INNODB_TRX;

这是InnoDB正在执行的事务。

查看锁等待

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

看谁在等谁的锁。

查看具体的锁

SELECT * FROM information_schema.INNODB_LOCKS;

MySQL 8.0改成了 performance_schema.data_locks。

三、找出锁表的SQL

查看长时间运行的事务

SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as run_seconds,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

run_seconds 越大，跑的越久，越可能是锁表的元凶。

查看锁等待链

SELECT 
    r.trx_id as waiting_trx_id,
    r.trx_mysql_thread_id as waiting_thread,
    r.trx_query as waiting_query,
    b.trx_id as blocking_trx_id,
    b.trx_mysql_thread_id as blocking_thread,
    b.trx_query as blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

四、杀掉锁表进程

找到进程ID

从上面的查询结果里，找到 blocking_thread，就是锁表的那个连接ID。

杀掉进程

KILL 进程ID;

批量杀掉

比如杀掉所有跑了超过60秒的事务：

SELECT CONCAT('KILL ', trx_mysql_thread_id, ';') 
FROM information_schema.INNODB_TRX 
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

把结果复制出来执行。

五、常用查询

查看谁在锁什么表

SELECT 
    t.table_name,
    t.table_rows,
    l.lock_type,
    l.lock_mode,
    l.lock_id,
    t2.trx_mysql_thread_id,
    t2.trx_query,
    t2.trx_started
FROM information_schema.INNODB_LOCKS l
JOIN information_schema.INNODB_TRX t2 ON l.lock_trx_id = t2.trx_id
JOIN information_schema.TABLES t ON l.lock_table = CONCAT(t.table_schema, '/', t.table_name);

查看行锁还是表锁

• RECORD 是行锁
• TABLE 是表锁

六、为什么会锁表

原因1：大事务没提交

开了个事务，执行了一大堆，一直没commit，锁就一直占着。

解决： 事务要短小，及时提交。

原因2：没有索引

更新/删除没走索引，会锁全表。

解决： 给where条件加索引。

原因3：DDL操作

ALTER TABLE、OPTIMIZE TABLE这些会锁表。

解决： 低峰期做，或者用pt-online-schema-change。

原因4：长查询

一条SQL跑几分钟，锁住相关行。

解决： 优化慢SQL。

七、MySQL 8.0的变化

MySQL 8.0把INNODB_LOCKS和INNODB_LOCK_WAITS废弃了，改成了performance_schema。

查看锁等待（8.0）

SELECT * FROM performance_schema.data_lock_waits;

查看锁（8.0）

SELECT * FROM performance_schema.data_locks;

常见坑

坑1：杀错进程

杀了正在正常执行的业务进程，导致业务报错。

解决： 先看清楚trx_query是什么，确认是僵尸事务再杀。

坑2：杀了又出来

杀了锁表进程，过一会儿又锁了。

原因： 程序里有死循环，或者连接池自动重连了。

解决： 找到源头，修代码。

坑3：KILL不掉

KILL 123;

进程还在，没杀掉。

原因： 事务在做rollback，需要时间。

解决： 等它回滚完，或者直接重启MySQL。

坑4：查不到锁信息

information_schema.INNODB_TRX是空的。

原因： 可能是MyISAM表，MyISAM的表锁不在InnoDB里。

MyISAM表锁查看：

SHOW OPEN TABLES WHERE In_use > 0;

坑5：锁和MDL搞混

MDL（元数据锁）不是行锁，是表结构的锁。

查看MDL锁：

SELECT * FROM performance_schema.metadata_locks;

总结

锁表排查三步：

1. SHOW FULL PROCESSLIST 看有没有Locked
2. information_schema.INNODB_TRX 找长事务
3. KILL 进程ID 杀掉锁表进程

记住： 杀之前先看清楚是什么SQL，别误杀了正常业务！

遇到问题加QQ23979811 协助处理

前言

数据库数据很重要，必须每天自动备份。写个备份脚本，自动备份+自动删旧的，不用手动管。

一、备份脚本

完整脚本 backup_mysql.sh

#!/bin/bash

# MySQL配置
DB_USER="root"
DB_PASS="your_password"
DB_NAME="mydb"

# 备份目录
BACKUP_DIR="/data/backup/mysql"

# 保留天数
KEEP_DAYS=7

# 日期
DATE=$(date +%Y%m%d_%H%M%S)

# 备份文件名
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

# 创建目录
mkdir -p $BACKUP_DIR

# 备份并压缩
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "[$(date)] 备份成功: $BACKUP_FILE"
else
    echo "[$(date)] 备份失败!"
    exit 1
fi

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

echo "[$(date)] 旧备份清理完成"

二、脚本说明

各部分解释

备份全部数据库

mysqldump -u$DB_USER -p$DB_PASS --all-databases | gzip > $BACKUP_FILE

备份多个数据库

mysqldump -u$DB_USER -p$DB_PASS --databases db1 db2 db3 | gzip > $BACKUP_FILE

三、设置执行权限

chmod +x /data/backup/backup_mysql.sh

四、测试脚本

/data/backup/backup_mysql.sh

看有没有备份文件生成：

ls -lh /data/backup/mysql/

五、设置定时任务

每天凌晨3点备份

crontab -e

添加：

0 3 * * * /data/backup/backup_mysql.sh >> /data/backup/backup.log 2>&1

说明：

• 0 3 * * * 每天凌晨3点
• >> backup.log 输出写到日志
• 2>&1 错误也写进去

查看定时任务

crontab -l

六、备份文件恢复

恢复步骤

# 解压
gunzip < mydb_20261008_030000.sql.gz | mysql -u root -p mydb

或者先解压再恢复

# 解压
gunzip mydb_20261008_030000.sql.gz

# 恢复
mysql -u root -p mydb < mydb_20261008_030000.sql

七、更完善的脚本

带日志和邮件通知的版本

#!/bin/bash

DB_USER="root"
DB_PASS="your_password"
DB_NAME="mydb"
BACKUP_DIR="/data/backup/mysql"
KEEP_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
LOG_FILE="$BACKUP_DIR/backup.log"

mkdir -p $BACKUP_DIR

echo "========== $(date) 开始备份 ==========" >> $LOG_FILE

# 备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME 2>>$LOG_FILE | gzip > $BACKUP_FILE

if [ $? -eq 0 ]; then
    FILE_SIZE=$(ls -lh $BACKUP_FILE | awk '{print $5}')
    echo "[$(date)] 备份成功，文件大小: $FILE_SIZE" >> $LOG_FILE
else
    echo "[$(date)] 备份失败!" >> $LOG_FILE
    # 这里可以加邮件通知
    exit 1
fi

# 删除旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
echo "[$(date)] 清理 $KEEP_DAYS 天前的旧备份完成" >> $LOG_FILE

echo "========== $(date) 备份结束 ==========" >> $LOG_FILE

常见坑

坑1：密码写在脚本里不安全

脚本里明文写了数据库密码。

解决： 用mysql配置文件：

# /root/.my.cnf
[client]
user=root
password=your_password

然后脚本里就不用写密码了：

mysqldump $DB_NAME | gzip > $BACKUP_FILE

设置权限：

chmod 600 /root/.my.cnf

坑2：备份文件太大

不压缩的话备份文件很大。

解决： 用gzip压缩，能压缩到原来的1/10。

坑3：定时任务不执行

cron里写了脚本，但没执行。

原因：

1. 脚本没加执行权限
2. 路径写错了
3. 环境变量不对

解决： 脚本里用全路径，比如 /usr/bin/mysqldump。

坑4：备份完没验证

备份文件损坏了都不知道。

解决： 定期找个测试库恢复一下，看备份能不能用。

坑5：备份文件存在同一台服务器

服务器挂了，备份也没了。

解决： 把备份传到远程服务器或OSS。

# 传到远程服务器
scp $BACKUP_FILE root@192.168.1.20:/data/backup/

总结

备份三步走：

1. 写备份脚本（mysqldump + gzip + find删旧）
2. 加执行权限
3. 加crontab定时任务

记住： 备份不是备份了就完了，要定期验证能不能恢复！

遇到问题加QQ23979811 协助处理

前言

删了几条数据，再插入新数据，id跳了一大截，不连续了。怎么让自增主键从1开始？

一、为什么自增主键会不连续

原因1：删除了数据

删了中间的几条，id就跳了。这是正常的，InnoDB的自增计数器不会回退。

原因2：事务回滚

插入一半回滚了，自增id已经用了，不会退回来。

原因3：批量插入部分失败

插100条，第50条失败了，前49条成功，后面的id就跳了。

原因4：TRUNCATE和DELETE的区别

TRUNCATE会重置自增计数器，DELETE不会。

二、查看当前自增值

SHOW TABLE STATUS LIKE 'users';

看 Auto_increment 字段，就是下一条要插入的id。

三、重置自增主键

方法1：修改自增值

ALTER TABLE users AUTO_INCREMENT = 1;

注意： 如果表里已有数据，会设为当前最大id+1。

方法2：清空表后重置

-- 清空表（重置自增）
TRUNCATE TABLE users;

TRUNCATE会把自增计数器重置为1。

方法3：DELETE后手动重置

-- 删除所有数据
DELETE FROM users;

-- 手动重置自增
ALTER TABLE users AUTO_INCREMENT = 1;

四、怎么让自增主键连续

问题：真的需要连续吗？

其实不需要。

自增主键只要唯一就行，不连续不影响业务。用户看到的id跳了也没关系。

如果非要连续

那只能：

1. 导出数据
2. 清空表
3. 重新导入

# 导出数据
mysqldump -u root -p mydb users > users.sql

# 清空表
mysql -u root -p mydb -e "TRUNCATE TABLE users"

# 重新导入
mysql -u root -p mydb < users.sql

注意： 重新导入后id会重新从1开始，但如果表之间有外键关联，会出问题。

五、InnoDB自增的坑

坑1：重启后自增值会变

MySQL 5.7之前，InnoDB把自增值存在内存里，重启后会从当前max(id)+1重新算。

如果删了最大id的那条，重启后自增值就变小了，可能重复。

MySQL 8.0修复了这个问题，把自增值持久化到redo log了。

坑2：批量插入会跳很多id

一次性插入很多数据，中间失败了，id跳了一大截。

解决： 不要批量插，一条条插。

坑3：自增步长

默认步长是1，可以改：

SHOW VARIABLES LIKE 'auto_increment_increment';

一般主从复制时会设不同步长，防止id冲突。

六、常见问题

问题1：自增主键突然跳到很大

突然从100跳到10000，怎么回事？

可能原因：

1. 批量插入失败了
2. 有人手动改了AUTO_INCREMENT
3. 主从同步导致的

问题2：自增主键重复了

ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

原因： 重启后自增值变小了，和已有数据冲突。

解决： 手动设大一点：

ALTER TABLE users AUTO_INCREMENT = 10000;

问题3：删除数据后id不连续

这是正常现象，不用管。

常见坑

坑1：TRUNCATE和DELETE搞混

DELETE FROM users 不会重置自增。
TRUNCATE TABLE users 会重置自增。

记住： 想重置自增用TRUNCATE。

坑2：外键约束导致TRUNCATE失败

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

解决： 先关外键检查：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

坑3：重置自增后插入报错

设AUTO_INCREMENT=1，但表里已经有id=1的数据了。

解决： 设成max(id)+1：

SELECT MAX(id) FROM users;
-- 假设max(id)=100
ALTER TABLE users AUTO_INCREMENT = 101;

总结

记住几点：

1. 自增主键不连续是正常的，不用纠结
2. TRUNCATE会重置自增，DELETE不会
3. 重置自增用 ALTER TABLE t AUTO_INCREMENT = n
4. MySQL 8.0之前重启会丢自增值，要注意

别追求id连续，没意义，唯一就行。

遇到问题加QQ23979811 协助处理

前言

表里有重复数据，同一个用户存了好几条。怎么删除重复数据，只保留一条？

一、先查有哪些重复数据

按字段查重复

SELECT name, COUNT(*) as cnt 
FROM users 
GROUP BY name 
HAVING cnt > 1;

查重复的完整记录

SELECT * FROM users 
WHERE name IN (
    SELECT name FROM users 
    GROUP BY name 
    HAVING COUNT(*) > 1
)
ORDER BY name;

二、删除重复数据，保留一条

方法1：用自连接（推荐）

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.name = u2.name;

意思： 同一个name，id大的删掉，保留id小的。

方法2：用NOT IN

DELETE FROM users 
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id 
        FROM users 
        GROUP BY name
    ) as tmp
);

方法3：用临时表

-- 第一步：建临时表存要保留的id
CREATE TABLE tmp AS
SELECT MIN(id) as min_id FROM users GROUP BY name;

-- 第二步：删除不在临时表里的
DELETE FROM users WHERE id NOT IN (SELECT min_id FROM tmp);

-- 第三步：删临时表
DROP TABLE tmp;

三、按多个字段去重

比如name和email都重复才算重复：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id 
AND u1.name = u2.name 
AND u1.email = u2.email;

四、保留最新的一条

不是保留id最小的，而是保留最新创建的：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.created_at < u2.created_at 
AND u1.name = u2.name;

五、批量去重前先备份

重要！ 删之前先备份：

CREATE TABLE users_bak AS SELECT * FROM users;

确认没问题再删。

六、怎么防止重复数据

加唯一索引

ALTER TABLE users ADD UNIQUE INDEX uk_name (name);

加了唯一索引，重复数据就插不进去了。

插入前先查

// PHP示例
$exists = $db->query("SELECT id FROM users WHERE name = '$name'");
if ($exists) {
    echo "已存在";
} else {
    $db->query("INSERT INTO users ...");
}

用INSERT IGNORE

INSERT IGNORE INTO users (name) VALUES ('张三');

重复了就忽略，不报错。

用ON DUPLICATE KEY UPDATE

INSERT INTO users (name, count) VALUES ('张三', 1)
ON DUPLICATE KEY UPDATE count = count + 1;

重复了就更新，不插入新的。

常见坑

坑1：删完发现数据没了

删重复的时候把正常数据也删了。

原因： 关联条件写错了。

解决： 先备份，再用SELECT确认要删哪些：

SELECT u1.* FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.name = u2.name;

确认没问题再把SELECT改成DELETE。

坑2：大表删太慢

几百万条数据，删半天删不完。

解决： 分批删，每次删1000条：

DELETE FROM users WHERE ... LIMIT 1000;

坑3：自连接删不干净

有的重复数据没删掉。

原因： 字段值有NULL，NULL=NULL是unknown。

解决： 加NULL判断：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id 
AND (u1.name = u2.name OR (u1.name IS NULL AND u2.name IS NULL));

坑4：报错"You can't specify target table for update in FROM clause"

MySQL不允许先查再删同一张表。

解决： 套一层临时表：

DELETE FROM users 
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id FROM users GROUP BY name
    ) as tmp
);

总结

去重三步：

1. 先查：GROUP BY ... HAVING COUNT(*) > 1
2. 再确认：SELECT出来看哪些要删
3. 最后删：自连接DELETE

记住： 删之前务必备份！

遇到问题加QQ23979811 协助处理

前言

网站突然变慢，不知道哪条SQL拖后腿。开慢查询日志，把跑得慢的SQL都抓出来优化。

一、什么是慢查询日志

慢查询日志（slow query log）记录执行时间超过阈值的SQL语句。

作用： 找到慢SQL，优化它们。

二、查看是否开启

SHOW VARIABLES LIKE 'slow_query_log';

OFF就是没开。

三、开启慢查询日志

方法1：临时开启（重启失效）

-- 开启
SET GLOBAL slow_query_log = ON;

-- 设置阈值（超过2秒算慢）
SET GLOBAL long_query_time = 2;

方法2：永久开启

改my.cnf：

[mysqld]
# 开启慢查询
slow_query_log = 1

# 慢查询阈值（秒）
long_query_time = 2

# 慢查询日志文件位置
slow_query_log_file = /var/log/mysql/slow.log

# 没走索引的SQL也记下来（可选）
log_queries_not_using_indexes = 1

重启MySQL：

systemctl restart mysqld

四、查看慢查询日志

直接看文件

tail -f /var/log/mysql/slow.log

用mysqldumpslow分析

MySQL自带的分析工具：

# 按查询时间排序，取前10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

常用参数：

示例：

# 按次数最多的排
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

# 只看select语句
mysqldumpslow -g "select" /var/log/mysql/slow.log

五、用pt-query-digest分析

更专业的分析工具（需要安装）：

pt-query-digest /var/log/mysql/slow.log

输出更详细的统计报告。

六、常用参数说明

七、优化慢SQL

找到慢SQL后怎么优化：

1. 加索引

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

看type是不是ALL，是ALL说明没走索引。

2. 避免select *

只查需要的字段：

-- 别这样
SELECT * FROM users;

-- 这样
SELECT id, name FROM users;

3. 避免大表全表扫描

加where条件，加limit。

4. 优化分页

大表分页越查越慢，用游标分页。

常见坑

坑1：long_query_time设太小

设成0.1秒，日志文件爆炸，全是慢SQL。

建议： 生产环境设1-2秒。

坑2：日志文件越来越大

慢查询日志一直写，磁盘满了。

解决： 用logrotate切割日志。

坑3：开启慢查询影响性能

开慢查询本身有性能损耗。

解决： 排查完就关掉，或者用低采样率。

坑4：long_query_time不生效

改了参数，但还是记录所有SQL。

原因： 连接没断开，用的还是旧参数。

解决： 重新连接MySQL。

坑5：log_output=TABLE占满系统表

把慢查询写到mysql.slow_log表，表越来越大。

解决： 定期清理：

TRUNCATE TABLE mysql.slow_log;

总结

开启三步：

1. SET GLOBAL slow_query_log = ON
2. SET GLOBAL long_query_time = 2
3. 看日志：mysqldumpslow -s t -t 10 slow.log

记住： 慢查询日志是优化数据库的第一步，先找到慢SQL再优化。

遇到问题加QQ23979811 协助处理

前言

存个emoji表情，MySQL报错" Incorrect string value"，乱码了。这就是字符集没设对。

一、utf8和utf8mb4区别

注意： MySQL的utf8是阉割版，真正的UTF-8是utf8mb4。

为什么要用utf8mb4

存emoji表情（😀🍔🚀），utf8存不了，会报错。

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content'

二、查看当前字符集

SHOW VARIABLES LIKE 'character_set%';

输出：

三、设置字符集

1. 改my.cnf

[mysqld]
# 服务器字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

重启MySQL：

systemctl restart mysqld

2. 建库时指定

CREATE DATABASE mydb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

3. 建表时指定

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

4. 改已有库字符集

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

5. 改已有表字符集

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

四、连接字符集

连接时也要设对，不然还是乱码。

PHP连接时设置

mysqli_set_charset($conn, 'utf8mb4');

连接后执行SQL

SET NAMES utf8mb4;

五、collation排序规则

推荐用utf8mb4_general_ci，快。

六、常见问题

问题1：存emoji报错

原因： 字段是utf8不是utf8mb4。

解决： 改成utf8mb4。

问题2：中文乱码

原因： 连接字符集不对。

解决： 连接后执行 SET NAMES utf8mb4。

问题3：like查询不区分大小写

原因： 用了_ci结尾的排序规则。

解决： 改用_bin。

常见坑

坑1：只改了表，没改库

库是latin1，表是utf8mb4，导数据还是乱码。

解决： 库、表、连接都要统一字符集。

坑2：改了表字符集，数据还是乱

改了表，但旧数据已经存成latin1了。

解决： 需要转码：

ALTER TABLE t MODIFY c BLOB;
ALTER TABLE t MODIFY c TEXT CHARACTER SET utf8mb4;

坑3：utf8和utf8mb4混用

有的表utf8，有的表utf8mb4，联表查询报错。

解决： 全库统一用utf8mb4。

坑4：mysqlclient不支持utf8mb4

老版本客户端不支持utf8mb4。

解决： 升级MySQL客户端。

总结

记住三点：

1. 数据库、表、连接全部用utf8mb4
2. 排序规则用utf8mb4_general_ci
3. 存emoji必须用utf8mb4

别用utf8了，直接上utf8mb4。

遇到问题加QQ23979811 协助处理

前言

一台数据库扛不住读写压力，怎么办？搭主从复制，主库写、从库读，分担压力。

一、主从复制原理

原理：

1. 主库把写操作写进binlog
2. 从库IO线程读主库binlog，写进中继日志（relay log）
3. 从库SQL线程读中继日志，重放SQL

作用：

1. 读写分离，分担主库压力
2. 数据备份，主库挂了从库顶上
3. 异地容灾

二、准备工作

环境：

要求：

1. MySQL版本一致
2. 主库开启binlog
3. 主从时间同步

三、配置主库

1. 改my.cnf

[mysqld]
# 服务ID（唯一）
server_id = 1

# 开启binlog
log_bin = mysql-bin

# binlog格式
binlog_format = ROW

# 只复制哪些库（可选）
# binlog_do_db = mydb

# 忽略哪些库（可选）
# binlog_ignore_db = mysql

2. 重启主库

systemctl restart mysqld

3. 创建复制用户

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

4. 查看主库状态

SHOW MASTER STATUS;

输出：

记住File和Position，后面从库要用。

5. 锁表导数据

FLUSH TABLES WITH READ LOCK;

开另一个终端导出数据：

mysqldump -u root -p --all-databases --master-data=2 > all.sql

解锁：

UNLOCK TABLES;

四、配置从库1

1. 改my.cnf

[mysqld]
# 服务ID（唯一，和主库不同）
server_id = 2

# 中继日志
relay_log = relay-bin

2. 重启从库

systemctl restart mysqld

3. 导入主库数据

mysql -u root -p < all.sql

4. 关联主库

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;

5. 开启从库

START SLAVE;

6. 查看状态

SHOW SLAVE STATUS\G

看两个值：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

都是Yes就成功了。

五、配置从库2

和从库1一样，只是server_id改成3：

[mysqld]
server_id = 3
relay_log = relay-bin

然后重复上面的步骤3-6。

六、验证主从同步

主库插入数据

CREATE TABLE test (id INT);
INSERT INTO test VALUES (1);

从库查

SELECT * FROM test;

能查到就同步成功了。

七、常用操作

查看从库状态

SHOW SLAVE STATUS\G

停止从库

STOP SLAVE;

重置从库

RESET SLAVE;

查看主库有哪些从库

SHOW SLAVE HOSTS;

常见坑

坑1：server_id重复

主库和从库server_id一样，复制失败。

解决： 每个MySQL实例server_id必须唯一。

坑2：防火墙挡了3306

从库连不上主库。

解决： 主库开放3306端口。

坑3：复制用户权限不够

解决： 复制用户需要REPLICATION SLAVE权限。

坑4：数据不一致

主从数据对不上。

原因：

1. 从库写了数据
2. 主库跳过了错误
3. 大事务导致延迟

解决： 从库只读：

[mysqld]
read_only = 1

坑5：从库延迟很大

主库写太快，从库追不上。

解决：

1. 用半同步复制
2. 从库硬件升级
3. 拆分更多从库

总结

搭建步骤：

1. 主库开启binlog，设server_id=1
2. 主库创建复制用户
3. 主库锁表导出数据
4. 从库改server_id，导入数据
5. 从库关联主库，START SLAVE
6. 验证两个Yes

记住： 一主多从配置简单，关键是server_id唯一、binlog格式一致。

遇到问题加QQ23979811 协助处理

前言

网站突然打不开，MySQL报"Too many connections"，连接数满了。本文讲怎么排查和解决。

报错长什么样

ERROR 1040 (00000): Too many connections

意思是MySQL连接数超过最大限制了。

一、先紧急处理

查看当前连接数

SHOW STATUS LIKE 'Threads_connected';

查看最大连接数

SHOW VARIABLES LIKE 'max_connections';

默认一般是151或者100。

临时调大连接数

SET GLOBAL max_connections = 1000;

马上生效，不用重启。

二、查看谁占了连接

查看所有连接

SHOW FULL PROCESSLIST;

按用户统计

SELECT user, COUNT(*) FROM information_schema.processlist GROUP BY user;

按IP统计

SELECT SUBSTRING_INDEX(host, ':', 1) AS ip, COUNT(*) 
FROM information_schema.processlist 
GROUP BY ip;

杀掉多余连接

-- 杀掉Sleep的连接
KILL 连接ID;

批量杀：

SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist 
WHERE user = 'www' AND command = 'Sleep';

三、永久修改最大连接数

改my.cnf：

[mysqld]
max_connections = 1000

重启MySQL：

systemctl restart mysqld

四、为什么连接数会满

原因1：程序没关连接

每查一次数据库就新开一个连接，用完不关，连接越积越多。

解决：

1. 用连接池
2. 用完及时关连接

原因2：慢SQL占着连接

一条SQL跑10秒，请求一多，连接就占满了。

解决： 优化慢SQL，加快查询速度。

原因3：max_connections太小

默认151，大流量网站不够用。

解决： 调大到500或1000。

原因4：Sleep连接太多

大量空闲连接占着位置。

解决： 调小wait_timeout：

[mysqld]
wait_timeout = 60
interactive_timeout = 60

空闲60秒自动断开。

五、排查步骤

第一步：确认是不是真的满了

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

第二步：看谁占了连接

SHOW FULL PROCESSLIST;

第三步：找原因

是慢SQL？还是程序泄漏？还是连接数太小？

第四步：紧急处理

SET GLOBAL max_connections = 1000;

或者杀掉Sleep连接。

常见坑

坑1：改了配置不生效

改了my.cnf，重启了，还是151。

原因： 改错配置文件了。

解决： 确认MySQL读的是哪个配置文件：

mysql --help | grep my.cnf

坑2：max_connections调太大

调到10000，结果MySQL启动不了。

原因： 每个连接都要占内存，太多会把内存吃满。

解决： 一般500-1000够用了，别调太大。

坑3：连接泄漏没解决

调大了max_connections，过几天又满了。

原因： 程序有连接泄漏，没根本解决。

解决： 查代码，修连接泄漏问题。

坑4：max_user_connections限制

单个用户连接数也有限制。

查看：

SHOW VARIABLES LIKE 'max_user_connections';

总结

紧急处理三步：

1. SHOW FULL PROCESSLIST 看谁占连接
2. 杀掉多余连接
3. 调大 max_connections

根本解决： 优化慢SQL + 修连接泄漏 + 用连接池。

遇到问题加QQ23979811 协助处理

前言

误删了数据，有备份但太老了，怎么办？开启binlog可以恢复到任意时间点的数据。

一、什么是binlog

binlog是MySQL的二进制日志，记录所有写操作（增删改）。

作用：

1. 数据恢复
2. 主从复制

二、开启binlog

查看是否开启

SHOW VARIABLES LIKE 'log_bin';

OFF就是没开。

开启方法

改my.cnf：

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

# 服务ID
server_id = 1

# binlog格式（推荐row）
binlog_format = ROW

# 保留7天
expire_logs_days = 7

# 单个文件最大1G
max_binlog_size = 1G

重启MySQL：

systemctl restart mysqld

验证开启成功

SHOW VARIABLES LIKE 'log_bin';

显示ON就是开了。

三、查看binlog文件

SHOW BINARY LOGS;

输出：

四、恢复数据

场景：误删了表，要恢复

第一步：找到要恢复的位置

先看binlog内容：

mysqlbinlog /var/log/mysql/mysql-bin.000002

找到误操作的位置点（position）。

第二步：恢复到备份点

先恢复最近的全量备份：

mysql -u root -p < backup.sql

第三步：恢复binlog

从备份点开始，恢复binlog到误操作前：

mysqlbinlog --start-position=备份点position --stop-position=误操作前position /var/log/mysql/mysql-bin.000002 | mysql -u root -p

按时间恢复

mysqlbinlog --start-datetime="2026-10-08 07:00:00" --stop-datetime="2026-10-08 07:30:00" /var/log/mysql/mysql-bin.000002 | mysql -u root -p

五、常用操作

刷新binlog（新开一个文件）

FLUSH LOGS;

删除旧binlog

-- 删除指定文件之前的
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 删除7天前的
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);

查看当前binlog位置

SHOW MASTER STATUS;

六、binlog格式

推荐用ROW，最安全。

常见坑

坑1：binlog没开就想恢复

原因： 事前没开启binlog。

解决： 现在就去开，不然出事就晚了。

坑2：binlog格式不对

用STATEMENT格式，恢复后数据不一致。

解决： 用ROW格式。

坑3：binlog占满磁盘

binlog一直写，磁盘满了。

解决： 设 expire_logs_days = 7，自动删旧的。

坑4：恢复顺序错了

先恢复binlog再恢复备份，数据更旧了。

正确顺序： 先恢复全量备份 → 再恢复binlog增量。

坑5：mysqlbinlog命令找不到

原因： MySQL环境变量没配。

解决： 用全路径：

/usr/local/mysql/bin/mysqlbinlog

总结

开启binlog三步：

1. 改my.cnf加 log_bin
2. 重启MySQL
3. 验证 SHOW VARIABLES LIKE 'log_bin'

记住： 生产环境一定要开binlog，不然出事就完蛋。

遇到问题加QQ23979811 协助处理

前言

磁盘快满了，不知道哪个库哪个表占空间大。本文教你查MySQL数据库和表的大小。

一、查看所有数据库大小

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;

输出：

二、查看某个数据库的所有表大小

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

把 mydb 换成你的数据库名。

三、查看单个表大小

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

四、查看所有表大小（按大小排序）

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

只看前20大的表。

五、用命令行查看

查看数据目录

du -sh /var/lib/mysql/

查看某个库的大小

du -sh /var/lib/mysql/mydb/

查看所有库的大小

du -sh /var/lib/mysql/*/

六、常用场景

场景1：磁盘满了，找大表

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

找出最大的10个表。

场景2：表很大，想知道数据和索引各占多少

SELECT 
    table_name AS '表名',
    ROUND(data_length / 1024 / 1024, 2) AS '数据(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引(MB)'
FROM information_schema.tables
WHERE table_schema = 'mydb';

场景3：清理无用数据，释放空间

删了很多数据，但磁盘空间没释放。

原因： InnoDB删数据后不会自动释放磁盘空间。

解决： 优化表：

OPTIMIZE TABLE users;

常见坑

坑1：table_rows不准

InnoDB的table_rows是估算值，不是精确值。

原因： InnoDB是多版本的，统计信息不精确。

解决： 要精确行数用 SELECT COUNT(*) FROM 表名;

坑2：删了数据空间没释放

DELETE删了很多行，但文件大小没变。

原因： InnoDB的表空间不会自动收缩。

解决： OPTIMIZE TABLE 表名; 或者重建表。

坑3：临时表占空间

performance_schema等系统库也占空间。

解决： 正常现象，不用管。

坑4：ibdata1文件很大

共享表空间文件很大，但不知道里面是什么。

原因： 所有InnoDB表都存在一个文件里。

解决： 开 innodb_file_per_table，每个表独立文件。

总结

常用查询：

1. 查所有库大小：GROUP BY table_schema
2. 查某库所有表：WHERE table_schema='库名'
3. 查某表大小：WHERE table_schema='库名' AND table_name='表名'

记住： 磁盘满了先查information_schema.tables找大表。

遇到问题加QQ23979811 协助处理

前言

清空表数据，用DELETE还是TRUNCATE？删表用DROP？很多人搞混，本文讲清楚区别。

三种操作

1. DELETE：删行

-- 删指定行
DELETE FROM users WHERE id = 1;

-- 删全表
DELETE FROM users;

特点：

• 一行一行删，慢
• 可以加WHERE条件
• 可以回滚（在事务里）
• 自增ID不重置，继续从之前的数开始
• 会写binlog

2. TRUNCATE：清空表

TRUNCATE TABLE users;

特点：

• 直接删表重建，快
• 不能加WHERE条件
• 不能回滚
• 自增ID重置，从1开始
• 比DELETE快很多

3. DROP：删表

DROP TABLE users;

特点：

• 表结构+数据全删
• 表都没了
• 最快

对比表

什么时候用哪个

用DELETE

• 只删部分数据（带WHERE）
• 需要回滚
• 数据量小

用TRUNCATE

• 清空全表数据
• 数据量大，要快
• 不需要回滚

用DROP

• 表不要了
• 表结构也删

常见坑

坑1：DELETE删全表很慢

DELETE FROM users;

100万行删半天。

解决： 清空全表用 TRUNCATE TABLE users;

坑2：TRUNCATE不能回滚

执行TRUNCATE后想后悔，回不去了。

解决： 执行前先备份，或者用DELETE（慢但能回滚）。

坑3：TRUNCATE自增ID重置

清空表后，自增ID从1开始了。

原因： TRUNCATE会重建表。

解决： 如果不想重置自增ID，用DELETE。

坑4：外键约束导致TRUNCATE失败

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

原因： 有其他表的外键关联到这个表。

解决： 先关外键检查：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

坑5：DELETE不写WHERE删全表

手滑了，没写WHERE，把全表删了。

解决： 执行DELETE前，先SELECT一下看条件对不对。

性能对比

TRUNCATE和DROP都是秒级，DELETE要逐行删。

总结

一句话总结：

• 删部分数据 → DELETE
• 清空全表 → TRUNCATE
• 表不要了 → DROP

记住： 清空表别用DELETE，太慢了。

遇到问题加QQ23979811 协助处理

前言

SQL查得慢，不知道为什么慢。用EXPLAIN看MySQL怎么执行这条SQL，就能找到问题在哪。

一、EXPLAIN怎么用

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

输出很多列，重点看这几个：id、select_type、table、type、key、rows、Extra。

二、重点列详解

1. type（访问类型）

最重要的一列，看SQL查得快不快。

从好到差：

目标： 至少达到range，最好是ref以上。

如果type是ALL，就是全表扫描，必须优化。

2. key（实际用到的索引）

key: NULL

说明没用索引。

key: idx_name

说明用到了idx_name索引。

3. rows（扫描行数）

MySQL预估要扫描多少行。

rows越大，越慢。

4. Extra（额外信息）

常见的：

• Using where：WHERE过滤
• Using index：覆盖索引，不用回表，好
• Using filesort：需要额外排序，不好
• Using temporary：用了临时表，不好

三、常见问题

问题1：type=ALL，全表扫描

原因： 没加索引，或者索引失效。

解决： 给WHERE条件的字段加索引。

问题2：key=NULL，没用索引

原因：

1. 没建索引
2. 索引失效了（函数、类型转换、前导模糊查询等）

解决： 建正确的索引，避免索引失效写法。

问题3：Using filesort

原因： ORDER BY的字段没走索引。

解决： 给ORDER BY的字段加索引。

问题4：Using temporary

原因： GROUP BY或DISTINCT用了临时表。

解决： 给GROUP BY的字段加索引。

四、实战案例

慢SQL

SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;

EXPLAIN结果：

问题：

1. type=ALL，全表扫描
2. key=NULL，没用索引
3. rows=100000，扫了10万行
4. Using filesort，还要额外排序

优化

加联合索引：

ALTER TABLE orders ADD INDEX idx_userid_createtime (user_id, create_time);

再EXPLAIN：

优化效果：

1. type变成ref
2. 用到了索引
3. rows从10万变成10
4. 没有filesort了

五、常用优化技巧

1. 避免SELECT *

只查需要的列，能用上覆盖索引。

2. WHERE条件加索引

经常查询的字段加索引。

3. 联合索引最左前缀

联合索引 (a,b,c)，查询条件要从最左列开始。

4. 避免索引失效

• 不要对索引字段用函数
• 不要隐式类型转换
• 不要前导模糊查询 like '%xxx'

总结

EXPLAIN看这三点：

1. type：是不是全表扫描
2. key：有没有用到索引
3. rows：扫描了多少行

记住： 慢SQL先EXPLAIN，找到问题再优化。

遇到问题加QQ23979811 协助处理

前言

本地能连MySQL，远程连接就报错。常见原因：用户没授权、防火墙没开、MySQL只监听本地。

报错长什么样

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx'

或者：

ERROR 1130 (HY000): Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

原因一：用户没授权远程登录

排查

SELECT user, host FROM mysql.user;

看root用户的host是 localhost 还是 %。

如果是 localhost，就只能本地登录。

解决

授权远程登录：

-- 方法1：授权root所有IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的密码';

-- 方法2：只允许某个IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '你的密码';

-- 刷新
FLUSH PRIVILEGES;

原因二：防火墙没开3306端口

排查

在本地服务器上测试：

netstat -tlnp | grep 3306

看MySQL监听的是 127.0.0.1:3306 还是 0.0.0.0:3306。

如果是 127.0.0.1:3306，说明只监听本地。

解决防火墙

CentOS7 firewalld

# 开3306端口
firewall-cmd --permanent --add-port=3306/tcp

# 重载
firewall-cmd --reload

Ubuntu ufw

ufw allow 3306/tcp

原因三：MySQL只监听本地

排查

netstat -tlnp | grep 3306

如果显示 127.0.0.1:3306，就是只监听本地。

解决

改my.cnf：

[mysqld]
bind-address = 0.0.0.0

注释掉 skip-networking（如果有的话）。

重启MySQL：

systemctl restart mysqld

原因四：云服务器安全组

阿里云、腾讯云等云服务器，除了系统防火墙，还有安全组。

解决

在云服务器控制台，找到安全组，放行3306端口。

原因五：MySQL版本问题

MySQL 8.0默认用 caching_sha2_password，老客户端不支持。

解决

创建用户时指定老的加密方式：

CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

排查步骤

第一步：本地能连吗

先在服务器上本地连一下：

mysql -u root -p

本地能连，说明MySQL本身没问题。

第二步：看MySQL监听地址

netstat -tlnp | grep 3306

第三步：防火墙开了吗

firewall-cmd --list-ports

看3306在不在。

第四步：远程telnet测试

在本地电脑上测试：

telnet 服务器IP 3306

如果连不上，就是网络/防火墙问题。

常见坑

坑1：改了授权忘了刷新

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

改完就退出，结果还是连不上。

解决： 执行 FLUSH PRIVILEGES;

坑2：bind-address没改

授权了，防火墙也开了，还是连不上。

原因： MySQL只监听本地。

解决： 改 bind-address = 0.0.0.0

坑3：安全组没开

系统防火墙开了，云服务器安全组没开。

解决： 去云控制台放行3306端口。

坑4：密码不对

报错不是"连接不上"，而是"Access denied"。

原因： 密码错了。

解决： 确认密码，注意大小写。

总结

远程连接失败排查顺序：

1. 先授权远程用户
2. 再开防火墙3306
3. 然后改bind-address
4. 最后查云安全组

记住： 90%的情况都是没授权或者防火墙没开。

遇到问题加QQ23979811 协助处理

前言

知道旧密码，想改个新密码，有好几种方法。本文汇总常用的改密码方法。

方法一：ALTER USER（推荐）

登录MySQL后：

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

然后刷新：

FLUSH PRIVILEGES;

优点： 官方推荐，语法标准，MySQL 5.7/8.0都支持。

方法二：SET PASSWORD

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('你的新密码');

刷新：

FLUSH PRIVILEGES;

注意： MySQL 8.0以后 PASSWORD() 函数被删了，不能用了。

方法三：UPDATE直接改

UPDATE mysql.user SET authentication_string=PASSWORD('你的新密码') 
WHERE User='root' AND Host='localhost';

刷新：

FLUSH PRIVILEGES;

注意： 同样，MySQL 8.0不能用PASSWORD()函数。

方法四：命令行修改

不用登录MySQL，直接命令行：

mysqladmin -u root -p password "你的新密码"

输入旧密码后，就改成新密码了。

MySQL 8.0改密码

MySQL 8.0改密码语法有变化：

方法一：ALTER USER（推荐）

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

方法二：SET PASSWORD

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

不同Host的root用户

root可能有多个：

SELECT user, host FROM mysql.user WHERE user='root';

输出可能是：

每个host的root密码是独立的，要分别改。

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY '新密码';
ALTER USER 'root'@'%' IDENTIFIED BY '新密码';

常见坑

坑1：改了密码还是旧密码能登

原因： 没执行 FLUSH PRIVILEGES;

解决： 改完密码一定要刷新权限。

坑2：MySQL 8.0报语法错误

ERROR 1064 (42000): You have an error in your SQL syntax

原因： 用了 PASSWORD() 函数，MySQL 8.0删了。

解决： 用 ALTER USER 语句。

坑3：改了密码远程登不上

本地能登，远程连不上。

原因： 只改了localhost的密码，远程用户的密码没改。

解决： 把所有host的root密码都改了。

坑4：密码太简单报错

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因： MySQL密码强度校验太严。

解决： 用复杂点的密码（大小写+数字+特殊字符），或者关掉密码校验：

SET GLOBAL validate_password_policy=LOW;

坑5：改完密码phpStudy/phpMyAdmin连不上

原因： 站点配置文件里的密码还是旧的。

解决： 改项目配置文件里的数据库密码。

总结

知道旧密码改密码：

记住： 改完密码一定要 FLUSH PRIVILEGES;

遇到问题加QQ23979811 协助处理

前言

MySQL连接数太多，报"Too many connections"，怎么看当前有多少连接？哪些连接在跑什么SQL？用show processlist。

一、查看当前连接

基本命令

SHOW PROCESSLIST;

输出：

每列含义：

• Id：连接ID，kill的时候用
• User：用户名
• Host：客户端IP和端口
• db：当前数据库
• Command：当前命令（Query/Sleep/Connect）
• Time：当前状态持续时间（秒）
• State：状态
• Info：正在执行的SQL

显示完整SQL

SHOW FULL PROCESSLIST;

默认Info列只显示前100个字符，加FULL显示完整SQL。

二、查看连接数

当前连接数

SHOW STATUS LIKE 'Threads_connected';

最大连接数

SHOW VARIABLES LIKE 'max_connections';

历史最大连接数

SHOW STATUS LIKE 'Max_used_connections';

三、杀掉连接

杀掉某个连接

KILL 连接ID;

比如杀掉ID为123的连接：

KILL 123;

杀掉所有Sleep连接

-- 先查出来
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist 
WHERE user = 'www' AND command = 'Sleep';

把结果复制出来执行，就能批量杀掉。

四、常用查询

按用户统计连接数

SELECT user, COUNT(*) FROM information_schema.processlist 
GROUP BY user;

按IP统计连接数

SELECT SUBSTRING_INDEX(host, ':', 1) AS ip, COUNT(*) 
FROM information_schema.processlist 
GROUP BY ip;

查看执行超过10秒的SQL

SELECT * FROM information_schema.processlist 
WHERE time > 10;

五、调整最大连接数

临时调整

SET GLOBAL max_connections = 1000;

永久调整

改my.cnf：

[mysqld]
max_connections = 1000

重启MySQL生效。

常见坑

坑1：Sleep连接太多

大量Sleep连接占着连接数，导致新连接进不来。

原因： 程序没关连接，连接池没配好。

解决：

1. 调小 wait_timeout，让空闲连接自动断开
2. 检查程序连接池配置

SET GLOBAL wait_timeout = 60;

坑2：看不到其他用户的连接

普通用户只能看到自己的连接。

解决： 用root用户登录，或者PROCESS权限。

坑3：kill了连接还在

kill了连接，但连接还在列表里。

原因： 连接正在回滚，需要等一会。

解决： 等几秒再看，或者重启MySQL。

坑4：连接数突然暴涨

突然一堆连接进来，数据库卡死。

原因： 可能是程序bug，或者被攻击了。

排查： 按IP统计，看哪个IP连接最多。

总结

常用命令：

记住： 连接数满了先看processlist，找出是谁占着连接。

遇到问题加QQ23979811 协助处理

前言

查询数据库时报错：Table 'xxx' doesn't exist，明明表就在那，为什么说不存在？本文排查常见原因。

报错长什么样

SELECT * FROM users;

报错：

ERROR 1146 (42S02): Table 'mydb.users' doesn't exist

常见原因

原因1：数据库选错了

当前在别的库里，所以找不到表。

排查： 先看当前在哪个库：

SELECT DATABASE();

如果显示的不是你要的库，就切换：

USE mydb;

再查一次。

原因2：表名写错了

拼错了，或者大小写不对。

排查： 看看库里到底有哪些表：

SHOW TABLES;

确认表名是不是和你写的一模一样。

注意： Linux下MySQL表名区分大小写，Users 和 users 是两个表。

原因3：表真的被删了

表确实不存在了，可能被误删了。

排查： 用SHOW TABLES确认。如果真没有，从备份恢复。

原因4：字符集导致的问题

表名里有特殊字符，编码不对导致识别不出来。

排查： 用 SHOW TABLES LIKE 'xxx%'; 模糊匹配一下。

原因5：MySQL升级后表损坏

升级MySQL后，某些表报错不存在，但文件还在。

原因： 表引擎损坏，或者frm文件丢了。

排查： 看数据目录下有没有对应文件：

ls /var/lib/mysql/mydb/

原因6：用户没有权限

虽然报错是"不存在"，但有时候权限不够也会这么报。

排查： 用root用户登录试试，如果root能看到，就是权限问题。

排查步骤

第一步：确认当前数据库

SELECT DATABASE();

第二步：查看所有表

SHOW TABLES;

第三步：模糊搜索表名

SHOW TABLES LIKE '%user%';

第四步：确认表文件是否存在

ls /var/lib/mysql/你的库名/ | grep 表名

常见坑

坑1：Linux下表名区分大小写

Windows开发没问题，上传到Linux服务器就报错。

解决： 表名统一用小写，或者在my.cnf加：

lower_case_table_names = 1

注意： 这个参数只能在初始化MySQL前设置，已经用了再改会出问题。

坑2：分表了忘了加表名

用了分表，代码里查的是 users，实际表名是 users_0、users_1。

解决： 确认分表规则，代码里拼对表名。

坑3：视图报错

查视图时报"表不存在"，可能是视图依赖的表被删了。

排查： 看视图定义：

SHOW CREATE VIEW 视图名;

坑4：临时表

会话结束临时表就没了，新开一个连接查就报错。

解决： 临时表只在当前会话有效，别跨连接用。

坑5：误删了库

整个库都没了，当然找不到表。

排查：

SHOW DATABASES;

看你的库还在不在。

总结

排查顺序：

1. 先看当前在哪个库
2. 再SHOW TABLES看表在不在
3. 确认表名大小写对不对
4. 最后看数据目录下文件在不在

记住： 90%的情况都是库选错了或者表名写错了。

遇到问题加QQ23979811 协助处理

前言

接手别人的项目，不知道表有哪些字段、什么类型、有没有索引，全靠DESC和SHOW CREATE TABLE。

一、查看表有哪些字段

方法1：DESC（最常用）

DESC users;

或者：

DESCRIBE users;

输出：

每列含义：

• Field：字段名
• Type：字段类型
• Null：能不能为NULL
• Key：有没有索引
• Default：默认值
• Extra：额外属性（自增等）

方法2：SHOW COLUMNS

SHOW COLUMNS FROM users;

和DESC效果一样，写法不同而已。

二、查看建表语句

SHOW CREATE TABLE

SHOW CREATE TABLE users;

输出：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

这个命令能看到：

• 完整的建表SQL
• 所有字段和类型
• 主键、索引
• 表引擎、字符集
• 默认值、自增等

三、查看表状态

SHOW TABLE STATUS LIKE 'users'\G

能看到：

• 表行数
• 数据大小
• 索引大小
• 更新时间
• 引擎类型
• 字符集

四、查看表的所有索引

SHOW INDEX FROM users;

或者：

SHOW KEYS FROM users;

五、查看数据库里所有表

SHOW TABLES;

六、实用组合

查看所有表的表名

SHOW TABLES;

查看某库的表

USE 数据库名;
SHOW TABLES;

或者：

SHOW TABLES FROM 数据库名;

七、从information_schema查

更灵活的方式，从系统表查：

查表的字段信息

SELECT * FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'mydb' 
AND TABLE_NAME = 'users';

查表的索引信息

SELECT * FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'mydb' 
AND TABLE_NAME = 'users';

常见坑

坑1：DESC看不到索引详细信息

DESC只告诉你Key列是啥，但不知道索引名。

解决： 用 SHOW INDEX FROM 表名;

坑2：SHOW CREATE TABLE输出太长

直接看会换行很乱。

解决： 加 \G 按行显示：

SHOW CREATE TABLE users\G

坑3：不知道当前在哪个库

查的时候报错说表不存在。

解决： 先 SELECT DATABASE(); 看当前库。

坑4：表名大小写问题

Linux下MySQL表名区分大小写，Windows不区分。

解决： 表名小写，开发时注意。

总结

常用命令总结：

记住： 看表结构首选DESC，要看完整建表语句用SHOW CREATE TABLE。

遇到问题加QQ23979811 协助处理

前言

数据库不备份，出事就完蛋。本文讲用mysqldump备份MySQL，再配crontab定时自动备份。

一、mysqldump基本用法

备份单个数据库

mysqldump -u root -p mydb > /backup/mydb.sql

输入密码后，就把mydb库备份到mydb.sql了。

备份多个数据库

mysqldump -u root -p --databases db1 db2 > /backup/mydbs.sql

备份所有数据库

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

备份单个表

mysqldump -u root -p mydb users > /backup/users.sql

二、常用参数

推荐完整命令

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  mydb > /backup/mydb_$(date +%Y%m%d).sql

三、压缩备份

备份完太大，直接压缩：

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

恢复的时候：

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

四、定时备份脚本

创建备份脚本：

vi /usr/local/bin/mysql_backup.sh

内容：

#!/bin/bash

# 备份目录
BACKUP_DIR=/backup/mysql

# 数据库配置
DB_USER=root
DB_PASS=你的密码
DB_NAME=mydb

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

# 备份文件名（带日期）
DATE=$(date +%Y%m%d_%H%M%S)
FILE=$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --default-character-set=utf8mb4 \
  $DB_NAME | gzip > $FILE

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

echo "备份完成：$FILE"

加执行权限：

chmod +x /usr/local/bin/mysql_backup.sh

五、配置crontab定时任务

编辑crontab：

crontab -e

加上：

# 每天凌晨2点备份
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

六、恢复备份

恢复普通备份

mysql -u root -p mydb < /backup/mydb.sql

恢复压缩备份

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

常见坑

坑1：密码写在命令行里不安全

mysqldump -u root -p123456 mydb > xxx.sql

这样密码会出现在进程列表里。

安全做法： 用配置文件存密码。

# /root/.my.cnf
[mysqldump]
user=root
password=你的密码

权限设600：

chmod 600 /root/.my.cnf

以后备份就不用输密码了：

mysqldump mydb > xxx.sql

坑2：备份时锁表

大库备份的时候，业务卡住了。

原因： 没加 --single-transaction。

解决： InnoDB库一定要加这个参数。

坑3：备份文件是空的

备份完一看，文件0字节。

原因： 密码错了，或者数据库名错了。

解决： 先手动执行一次，看报错。

坑4：备份没包含存储过程

备份后恢复，发现存储过程没了。

原因： mysqldump默认不备份存储过程。

解决： 加 --routines 参数。

总结

三步搞定自动备份：

1. 写备份脚本
2. 配crontab定时执行
3. 定期清理旧备份

记住： 备份了不算数，能恢复才算数。

遇到问题加QQ23979811 协助处理