【经验分享】MySQL线上使用经验

一、基本介绍

二、线上使用经验

1、MySQL慢查询日志

连接数据库，使用SHOW VARIABLES LIKE '%slow_query_log%';命令查询慢查询日志开关，输出如下：

slow_query_log的值OFF表示慢查询日志记录功能是关闭的，需要设置成ON才能打开；
slow_query_log_file的值为慢查询日志的文件路径；

执行SET GLOBAL slow_query_log = ON;打开慢查询日志开关，线上慎用。
执行命令SHOW VARIABLES LIKE '%long_query_time%';输出慢查询的拦截时间，默认时间是10s：

可通过SET long_query_time = 1;命令修改慢查询的拦截时间，比如说设置成1s。

测试一下：
另起一个窗口，执行tail -f /usr/local/mysql-8/data/localhost-slow.log监控慢查询日志内容，然后这边执行一个超过1秒的查询，如下图所示：


END

2、生产环境大数据表如何添加索引

生产环境单个数据库的前提下，如果单表数据量很大达上千万数据了，如何不影响主业务运行的前提下给大表某个字段添加索引呢，答案有很多种，下面介绍以下两种方式：
（1）使用MySQL自带的Oline DDL功能：MySQL提供了ALGORITHM=INPLACE选项，允许在不锁定表的情况下添加索引，这就意味着即使在添加索引时，应用程序仍然可以访问该表的其他数据，其中INPLACE算法可以有效减少对业务的影响，创建索引的SQL语句如下所示：

ALTERTABLEyour_tableALGORITHM=INPLACE,LOCK=NONE,ADDINDEXindex_name(column1,column2);

• ALGORITHM=INPLACE:指定使用就地算法进行表的修改，这样MySQL就不会复制表数据，而是直接修改原始表的数据结构；
• LOCK=NONE:在索引创建过程中不会对表进行锁定，其他操作可以继续进行，这样可以最大程度减少对业务的影响；

此法最适用InnoDB存储引擎，需要MySQL版本再5.6及以上。

（2）使用 pt-online-schema-change 工具：如果表非常大，或者不支持在线索引创建，另一种常用的方案是使用 Percona Toolkit 中的 pt-online-schema-change 工具。该工具的工作原理是创建一个新的表，然后逐渐将数据从原表迁移到新表中，完成后将表切换过来，整个过程不会对业务造成大的影响。

# 以下是使用 pt-online-schema-change 工具添加索引的示例：pt-online-schema-change--alter "ADD INDEX index_name (column1, column2)" D=your_database,t=your_table --execute

3、MySQL在线修改表结构

其实和第二点是一样的，使用Online DDL或者pt-online-schema-change 工具，这里不做过多介绍，直接上操作：
（1）使用MySQL自带的Oline DDL

ALTERTABLEyour_tableALGORITHM=INPLACE,LOCK=NONE,ADDCOLUMN字段名varchar(20)default''comment'这是字段描述';

此法最适用InnoDB存储引擎，需要MySQL版本再5.6及以上。

（2）使用 pt-online-schema-change 工具：
执行命令：pt-online-schema-change --socket=/tmp/mysql.sock --user=用户名 --password=密码 --alter "add column 字段名 varchar(20) default '' comment '这是字段描述' " D=数据库名, t=表名 --print --execute；

关于socket的路径，大家可根据/etc/my.cnf配置文件中配置的路径选择填写。
这种方式是要录入数据库名、表名、账号、密码，然后还有字段名、类型、字段描述，这里我默认设置的varchar(20)，简单易懂，可以自行按照业务来设置，执行前请在测试环境先尝试一遍。

演示一下：

执行命令：

/usr/bin/pt-online-schema-change\--socket=/tmp/mysql.sock\--user=root\--password='dinglian123@'\--alter="ADD COLUMN description varchar(20) DEFAULT '' COMMENT '这是字段描述'"\D=web_app,t=user\--print\--execute

再去查询表结构：

4、MySQL备份/恢复工具mysqldump

MySQL常用的备份/恢复工具有mysqldump、mydumper、XtraBackup。

• mysqldump工具： 这是官方自带的工具；
• mydumper工具： 逻辑备份工具；
• XtraBackup工具：物理热备份工具；

本案例讲述的是使用mysqldump备份数据库，实现原理为通过协议链接到MySQL数据库，将需要备份的数据查询出来，然后再将查询出来的数据转化为insert语句，当我们需要还原数据时只需要执行这些insert语句就可以恢复数据。下面进行实战

mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert 语句，即可将对应的数据还原。

案例一：mysqldump备份单个数据库

（1）备份一个数据库

目前我们有web_app数据库下有一个user表，如下图所示：

备份该数据库，执行命令：mysqldump --user=root -p --databases web_app > web_app20251217.sql
将本机的web_app数据库备份到当前目录下的web_app20251217.sql中，其他参数如下所示：

-p:数据库密码 --host=name:数据库地址,本机可以不写 --user=name:数据库账号 --port=端口:数据库端口,如果默认是3306可以不写 --databases:导出的数据库名 --tables:导出指定表的数据和结构

执行结果：

（2）删除web_app数据库，然后使用备份文件恢复该数据库：

先删除数据库，然后在创建数据库web_app，此时的库下没有任何表或者数据，然后再使用source 文件路径命令进行恢复，如图所示：


END…

案例二：备份所有数据库

mysqldump -u root -p -A>all_$(date+'+%F').sql# 终端提示输入密码Enter password:# 执行完成后，当前目录生成文件：all_2025-12-17.sql

以 root 用户身份登录 MySQL，输入密码后，导出服务器上所有数据库的结构和数据，并将导出的 SQL 内容保存到当前目录下，以「all_年月日.sql」命名的文件中。

案例三：备份指定数据

备份单库或者多库

# 格式：mysqldump -u 用户名 -p 数据库名 表名 > 备份文件名.sqlmysqldump -u root -p -B 数据库名>数据目名_$(date+'+%F').sql# 备份db1和db2两个数据库，生成合并的备份文件mysqldump -u root -p -B db1 db2>db1_db2_$(date+'+%F').sql

以 root 用户身份登录 MySQL，输入密码后，备份指定的单个数据库（含该库下所有表、视图、存储过程等），并将备份的 SQL 文件以「数据库名_年月日.sql」的格式保存到当前目录。

恢复命令：

# 带 -B 备份的恢复（无需提前建库）mysql -u root -p<数据库名_2025-12-17.sql# 单表备份的恢复（需先建库并切换）mysql -u root -p mysql>CREATE DATABASE IF NOT EXISTS test_db;# 建库mysql>USE test_db;# 切换到目标库mysql>SOURCE user_2025-12-17.sql;# 恢复表

案例四：只导出数据库表结构，不包含数据

mysqldump -u root -p -B -d db_name>db_name_not_data_$(date+'+%F').sql

案例五：只导出数据，不添加建表语句

mysqldump -u root -p -B -t db_name>db_name_not_create_info_$(date+'+%F').sql

案例六：导出指定数据库的指定表

mysqldump -u root -p 库名 --tables 表名>db_name_表名_$(date+'+%F').sql

案例七：导出指定数据库的多个表

mysqldump -u root -p 库名 --tables 表名1 表名2 表3...>db_name_tables_$(date+'+%F').sql

案例八：压缩备份

# 压缩备份mysqldump -u root -p -B 库名 --tables 表名|gzip>db_name_table_$(date+'+%F').sql.gz## 解压恢复的两种方法# 方法1gzip<db_name_table_2025-12-17.sql.gz|mysql -u root -p mydatabase# 方法2zcat db_name_table_2025-12-17.sql.gz|mysql -u root -p mydatabase

5、MySQL主从数据不一致的数据修复

pt-table-sync 是 Percona 工具集里用于校验 / 同步 MySQL 主从数据不一致的工具

# 目标端(从库)在前，源端(主库)在参数中，--sync-to-source 表示以源端为基准同步目标端# 验证pt-table-sync --sync-to-source\-u从库用户名 -p从库密码\h=主库ip,D=数据库名,t=表名,u=主库用户名,p=主库密码\h=从库ip\--print# 执行pt-table-sync --sync-to-source\-u从库用户名 -p从库密码\h=主库ip,D=数据库名,t=表名,u=主库用户名,p=主库密码\h=从库ip\--execute

修复的时间比较长，如果是上千万的数据，大概需要半小时至一小时左右。

需要注意的点：–print会把差异数据打印出来，如果数据比较多的话，建议不要使用这个命令，直接使用pt-table-checksum 工具来校验。

6、pt-table-checksum 工具校验主从不一致的差异

pt-table-checksum 是 Percona 工具集里用于校验 MySQL 主从数据一致性的工具（仅检测不一致，不修复），会通过在主库执行校验和计算，对比从库的结果，输出具体哪些表 / 哪些行数据不一致；
pt-table-sync 则基于 pt-table-checksum 的校验结果，精准修复主从数据差异（以主库为基准同步从库）。

文末安装了 pt-online-schema-change 时会同步包含 pt-table-checksum 工具—— 因为这两个工具都属于 Percona Toolkit 核心组件，官方打包时会将整个工具集作为一个整体发布，而非单独拆分。

第一步：用 pt-table-checksum 校验数据不一致范围
在从库上执行以下命令：

pt-table-checksum\--nocheck-replication-filters\# 不检查复制过滤规则（避免漏检）--no-check-binlog-format\# 不检查binlog格式（兼容ROW/STATEMENT格式）--replicate=test.checksum_results\# 校验结果存入主库 test 库的 checksum_results 表（自动创建）h=主库IP,u=校验账号,p=密码,P=3306\# 主库信息（IP/账号/密码/端口）-d 数据库名\# 指定要校验的数据库（只校验该库，不写则校验所有库）-t 表名# 指定要校验的表（只校验该表，不写则校验库下所有表）

执行完命令后，终端会输出校验结果，核心字段解读：

DATABASE：数据库名
TABLE：表名
CHUNKS：表被拆分的校验块数 -
DIFFS：不一致的块数，大于 0 表示该表有数据不一致
ROWS：校验的总行数 对比主库 3000w、从库 2800w，看是否匹配
ERRORS： 校验错误数 大于 0 需排查（如权限、网络问题）

也可直接查询主库的校验结果表：

-- 主库执行，查询不一致的表SELECTdb,tbl,diffsFROMtest.checksum_resultsWHEREdiffs>0;

第二步：用 pt-table-sync 修复数据不一致

# 先打印修复 SQL（不执行，验证准确性）pt-table-sync\--sync-to-source\# 以主库（source）为基准，修复从库数据--print\# 仅打印修复SQL（不实际执行，先核对）--charset=utf8mb4\# 指定字符集（避免乱码）h=主库ip,D=数据库名,t=表名,u=主从同步的用户,p=你的密码\# 主库（源端）信息h=从库ip,u=主从同步的用户,p=你的密码\# 从库（目标端）信息# 执行修复（确认 SQL 无误后）pt-table-sync\--sync-to-source\--print\--charset=utf8mb4\h=主库ip,D=数据库名,t=表名,u=主从同步的用户,p=你的密码\h=从库ip,u=主从同步的用户,p=你的密码\

–sync-to-source：核心：以 h=192.168.0.1（主库）为基准，同步 h=192.168.0.2（从库）数据
–print：仅输出需要执行的 INSERT/UPDATE/DELETE 语句，用于验证修复逻辑
–execute：执行修复 SQL（务必先通过 --print 验证，再执行）

注意事项：

• 数据量较大（3000w）的优化：校验 / 修复时指定单表（-t 表名），避免全库操作耗时过久，同时业务低峰期再去执行；
• 修复前确认从库的 IO/SQL 线程是 Running 状态（show slave status\G）；
• 校验 / 修复账号必须有主从库的 SELECT、UPDATE、DELETE、INSERT、SUPER 权限；
• 修复完成后，重新执行 pt-table-checksum，确认 DIFFS=0（无不一致）；

三、如何安装pt-online-schema-change

方式一：通过percona-toolkit.tar.gz安装包解压安装

# 下载wgetpercona.com/get/percona-toolkit.tar.gz# 解压tar-zvxf percona-toolkit.tar.gzcdpercona-toolkit-3.7.0-2# 安装perl依赖yuminstallperl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl Makefile.PL# 编译安装makemakeinstall# 验证pt-online-schema-change

方式二：RPM 包方式安装

# 1. 从 Percona 官方镜像下载（推荐）wgethttps://downloads.percona.com/downloads/percona-toolkit/3.5.5/binary/redhat/7/x86_64/percona-toolkit-3.5.5-1.el7.x86_64.rpm# 备用链接（阿里云镜像）wgethttps://mirrors.aliyun.com/percona/yum/release/7/RPMS/x86_64/percona-toolkit-3.5.5-1.el7.x86_64.rpm# 2. 安装（加 --force --nodeps 忽略依赖警告，前提是已装完 Perl 依赖）rpm-ivh percona-toolkit-3.5.5-1.el7.x86_64.rpm --force --nodeps# 3. 查看版本，输出正常则安装完成pt-online-schema-change --version

注意：若 RPM 安装仍报错，可直接解压 RPM 包，将工具二进制文件放到系统路径（这是在上面步骤安装失败的前提下）：

# 1. 解压 RPM 包rpm2cpio../percona-toolkit-3.5.5-1.el7.x86_64.rpm|cpio -idmv# 2. 将工具拷贝到 /usr/bin（系统可执行路径）cp-r usr/bin/* /usr/bin/# 3. 赋予执行权限chmod+x /usr/bin/pt-online-schema-change# 4. 验证pt-online-schema-change --version

离线安装包下载地址

如果是离线的系统说缺少依赖，可以点击这个网址去下载：https://mirrors.163.com/centos-vault/7.9.2009/os/x86_64/Packages/

下载完成后，通过执行：

rpm-ivh perl-Digest-1.17-245.el7.noarch.rpmrpm-ivh perl-Digest-MD5-2.52-3.el7.x86_64.rpm

报错缺少perl-Digest-MD5包

如果报错说缺少perl-Digest-MD5包，那么就安装一下yum -y install perl-Digest-MD5

四、其他

其他的线上经验，后续再分享了，目前只想得起这么多，哈哈…

创作不易，不喜勿喷。