MySQL 8.0 这套东西我前前后后用下来也有五六年了。从 5.7 升级过来的第一天就发现好多网上搜到的老命令根本跑不通比如 GRANT 之后还要不要 FLUSH PRIVILEGES、密码插件到底用哪个、为什么新建用户必须先 CREATE USER。后来带了几个人发现这些问题几乎每个人都要问一遍所以我干脆把平时查的东西沉淀成一份“使用者查阅笔记”跟着项目走项目用到哪就记到哪。这份笔记覆盖版本选型、安装配置、常用 SQL、用户权限、连接池、锁和死锁、备份恢复、慢查询优化、常见报错和面试考点全部是在 MySQL 8.0 真实环境验证过的。无论是刚接触数据库的研发还是接手数据库维护的同学都可以直接拿这份笔记里的命令去查、去用。1. 版本选择与环境准备1.1 8.0 和 8.4 LTS 的选型问题先说选型因为网上关于“mysql 8.4.11 lts数据库服务器的下载、解压及配置”的讨论热度很高。MySQL 8.0 从 2018 年 GA 到现在补丁版本一直在迭代8.0.36、8.0.40 这些版本至今仍是很多公司的生产主力。8.4 是 Oracle 后来推出的 LTS 版本比 8.0 更新更新周期和边界也重新规划了。很多人一看 8.4 是 LTS 就想直接冲但实际项目里要冷静看你现有业务跑在 8.0 上升级 8.4 属于跨大版本工具链、驱动、监控插件都可能需要一并验证不是改个版本号那么简单。我的建议是新项目并且愿意做完整兼容验证可以尝试 8.4但绝大多数存量 8.0 用户尤其是 5.7 刚升上来的继续用 8.0.x 是风险最小的选择。一份笔记不可能覆盖两个版本的每个细节后面我都以 8.0 为例遇到 8.4 的明显差异会单独说明。1.2 Windows 下二进制包安装的正确姿势Windows 上我一般不用图形化安装向导而是用官方 ZIP 二进制包。去 MySQL 官网选 MySQL Community Server 8.0.xx下载 Windows (x86, 64-bit) ZIP Archive。解压到比如D:\mysql-8.0.40-winx64注意别放 C 盘后面数据文件和日志增长会挤爆系统盘。关键步骤是初始化。用管理员身份打开 CMD进入bin目录先执行mysqld --initialize-insecure--initialize-insecure会生成本地 root 空密码适合本机调试生产环境建议用mysqld --initialize它会生成一个随机 root 密码输出到日志中。初始化完成后可以在前台跑mysqld --console试试能不能启动或者用mysqld --install把 MySQL 注册成 Windows 服务然后net start mysql启动。如果之前机器上装过旧版本并留有旧数据目录一定要先删掉 datadir 再初始化否则会因数据字典版本不一致直接报错退出。初装后第一件事是登录改密码。如果初始化时用了随机密码在data目录或错误日志里找temporary password字样。登录命令mysql -uroot -p改密码ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;1.3 Linux 下安装与初始化要点Linux 上不同发行版差别比较大。红帽系建议用官方 yum 仓库安装yum localinstall mysql80-community-release-el9-*.rpm yum install mysql-community-server systemctl start mysqld初始密码会写到/var/log/mysqld.log用grep temporary password /var/log/mysqld.log可以找到。Debian/Ubuntu 可以用 apt 仓库也可以直接用通用 tar 包装tar -xvf mysql-8.0.xx-linux-glibc2.17-x86_64.tar.xz groupadd mysql useradd -r -g mysql mysql chown -R mysql:mysql /usr/local/mysql然后创建/etc/my.cnf再以 mysql 用户初始化mysqld --initialize --usermysql mysqld_safe --usermysql Linux 上要注意 SELinux 和防火墙很多刚上手的朋友初始化都通过了结果远程连不上十有八九是防火墙没放行 3306 端口。安装完成后用systemctl list-unit-files | grep mysql确认服务状态如果使用通用包需要自己写 systemd 服务文件建议参考官方模板别省这一步。1.4 我常用的 my.ini 参数模板我习惯把核心参数显式写在my.iniLinux 是/etc/my.cnf不依赖默认值。以下是一份 4 核 8G 内存服务器的模板[mysqld] basedirD:/mysql-8.0.40-winx64 datadirD:/mysql-8.0.40-winx64/data port3306 character-set-serverutf8mb4 collation-serverutf8mb4_0900_ai_ci default_authentication_plugincaching_sha2_password max_connections300 innodb_buffer_pool_size2G innodb_log_file_size256M slow_query_log1 slow_query_log_fileD:/mysql-8.0.40-winx64/slow.log long_query_time1几个关键参数解释一下。character-set-server和collation-server必须写否则建表默认字符集可能是 latin1中文存进去就是乱码。max_connections默认只有 151如果业务用了连接池再加手工查询很容易打满。innodb_buffer_pool_size是 InnoDB 用来缓存表数据和索引的内存建议设置为物理内存的 50%~70%设太小会导致磁盘 IO 频繁设太大会挤占操作系统内存。slow_query_log开起来long_query_time设 1 秒后面做性能排查全靠它。2. 日常 SQL 与结构修改速查2.1 建库建表与增删改查常用写法“数据库增删改查”是搜索量很高的一组词这些操作虽然基础但 8.0 里有一些细节值得留意。创建数据库时明确指定字符集CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;建表推荐模板CREATE TABLE user ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;增删改查四件套没必要全列但有几个开发高频写法我想强调。批量插入不要循环单条 INSERT尽量用多个 VALUES 或LOAD DATA。更新时要注意加条件没加 WHERE 的 UPDATE 一旦误操作就是事故。INSERT ... ON DUPLICATE KEY UPDATE在 8.0 里很好用它会在唯一键冲突时执行更新很多“存在则更新不存在则插入”的逻辑可以一条 SQL 搞定。另一个容易踩坑的是REPLACE INTO它遇到冲突会先删掉旧行再插入新行副作用是自增 id 会变如果业务里 id 被其他表引用就别用这个。查询方面需要快速看表结构用DESC user;想确认建表语句用SHOW CREATE TABLE user\G。8.0 的SHOW CREATE TABLE输出比 5.7 更完整可以看到分区、表空间等信息。2.2 ALTER TABLE 高频修改结构操作“mysql数据库修改结构”这个词搜的人很多说明改表结构是真实高频需求。我整理一张速查表操作示例添加列ALTER TABLE user ADD COLUMN phone VARCHAR(20) NULL AFTER email;修改列类型ALTER TABLE user MODIFY COLUMN phone VARCHAR(30) NOT NULL DEFAULT ;重命名列ALTER TABLE user CHANGE COLUMN phone mobile VARCHAR(20);重命名表RENAME TABLE user TO users;添加索引ALTER TABLE user ADD INDEX idx_username (username);删除列ALTER TABLE user DROP COLUMN mobile;8.0 对 ALTER 做了不少优化很多操作是 INSTANT 或 INPLACE 算法不会长时间锁表。比如在表末尾添加一列8.0 可以 INSTANT 完成瞬间返回但如果在某个列之后插入新列可能就不是 INSTANT需要重新建表复制数据。大表操作前最好先用ALTER TABLE ... ALGORITHMINSTANT测试是否支持不支持就退化为ALGORITHMINPLACE。如果业务完全不能接受锁表可以考虑 Percona Toolkit 里的pt-online-schema-change8.0 支持得不错但工具本身不是官方生产使用前要在测试环境完整演练。2.3 添加唯一约束前清理重复数据这个问题我在论坛上回答过很多次表里已经有一堆重复数据执行ALTER TABLE user ADD UNIQUE KEY uk_username (username);时报Duplicate entry怎么处理。正确流程是查重复组SELECT username, COUNT(*) AS cnt FROM user GROUP BY username HAVING cnt 1;删除重复行保留每组最小的 idDELETE FROM user WHERE id NOT IN ( SELECT keep_id FROM ( SELECT MIN(id) AS keep_id FROM user GROUP BY username ) AS tmp );注意 MySQL 不允许在同一语句中直接从目标表读取子查询所以必须包一层临时表。如果数据量很大DELETE 一条条删除会很慢更高效的做法是创建新表、INSERT ... SELECT去重后的数据然后RENAME TABLE切换。去重逻辑按“保留最小 id”还是“保留最新数据”需要根据业务决定不要想当然。数据清理完成后再执行添加唯一约束就不会报错了。3. 用户、权限与连接管理笔记3.1 8.0 用户创建与授权差异8.0 在用户管理上跟 5.7 最大的区别是GRANT不再自动创建用户。一个常见报错就是直接写GRANT ALL ON *.* TO app%;发现用户不存在必须先CREATE USERCREATE USER app% IDENTIFIED BY strong_password; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO app%;关于FLUSH PRIVILEGES网上很多老教程说执行完GRANT要刷新实际上 8.0 里GRANT语句已经写入权限表权限立即生效不需要FLUSH PRIVILEGES。只有直接操作mysql.user表比如手工 UPDATE之后才需要刷新。另外默认认证插件是caching_sha2_password老客户端连接时会报Authentication plugin caching_sha2_password cannot be loaded。处理办法有两个升级 JDBC/客户端驱动到支持新插件或者把用户改成旧插件ALTER USER app% IDENTIFIED WITH mysql_native_password BY strong_password;我的建议是优先升级驱动老插件mysql_native_password在 8.0 里虽然兼容但安全性和密码哈希机制明显不如新的没必要为了省事拖累整体安全基线。3.2 密码有效期查看与设置“怎么查数据库密码有效期是多久”这个问题答案其实在一堆参数里。全局默认密码有效期由default_password_lifetime控制默认值是 0表示不过期。查看SHOW VARIABLES LIKE default_password_lifetime;查单个用户的密码过期状态可以用SHOW CREATE USER app%;输出里能看到PASSWORD EXPIRE NEVER或者PASSWORD EXPIRE INTERVAL 90 DAY之类的标记。也可以直接查mysql.user表SELECT user, host, password_expired, account_locked FROM mysql.user;设置全局策略让新用户默认每 180 天改一次密码SET GLOBAL default_password_lifetime 180;只对单个用户设置 90 天过期ALTER USER app% IDENTIFIED BY new_strong_password PASSWORD EXPIRE INTERVAL 90 DAY;注意改策略不会把已登录会话踢下线下一次登录才会要求改密码。如果业务中大量使用程序账号不要把程序账号设成定期过期否则某天 3 点钟连接池突然全部失联都是密码过期惹的祸。程序账号建议PASSWORD EXPIRE NEVER靠严格管理和密钥扫描来防护。3.3 连接数、连接池与主库不可达排查连接数问题是生产事故的重灾区。查看当前连接状态SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;如果Threads_connected已经逼近max_connections说明连接池配置、慢 SQL 或者连接泄漏至少有一个出了问题。应用连接池这里多说一句以 Java 的 HikariCP 为例maximumPoolSize不要拍脑袋写 200要结合数据库max_connections和应用实例数计算比如数据库允许 300 连接你有 5 个应用实例每个实例最大连接数应该控制在 50 左右留出一定余量给手工查询和运维工具。同时要配置validateConnection或者testWhileIdle避免数据库重启后连接池里全是死连接应用请求全部报错。关于“访问数据库时发生错误。主数据库无法访问使用主数据库的功能将不可用”这类报错常见于主从架构里应用连不上主库。我一般按这个顺序排查主库进程是否活着Linux 执行netstat -an | grep 3306Windows 执行netstat -an | findstr 3306网络是否通从应用机器telnet 主库IP 3306主从状态登录从库执行SHOW REPLICA STATUS\G看Replica_IO_Running和Replica_SQL_Running是否都是 Yes主库资源磁盘是否满、线程数是否打满、错误日志有没有新的记录。不要一看见报错就重启数据库重启很可能掩盖真实原因下次还会再犯。4. 事务、锁与死锁排查笔记4.1 隔离级别与 InnoDB 锁类型数据库并发场景绕不开锁。8.0 InnoDB 默认隔离级别是REPEATABLE READ查看当前值SELECT transaction_isolation;InnoDB 锁的类型不要死记硬背关键是理解行锁、间隙锁和 Next-Key Lock。REPEATABLE READ下为了防止幻读事务在范围内扫描时会给不存在的记录加间隙锁这就是死锁的常见来源之一。如果把隔离级别降到READ COMMITTED间隙锁会减少很多死锁概率也下降但应用要能接受不可重复读。生产环境是否切换需要业务方确认不能只看数据库。现在的 8.0 能直接通过performance_schema查锁信息SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;这个比 5.7 时代靠INFORMATION_SCHEMA.INNODB_LOCKS容易理解多了能看到锁事务、锁类型、锁模式和锁住的表/索引。平时遇到“数据库并发锁”问题我第一反应就是开这两个视图排查。4.2 死锁现场分析与业务侧重试死锁本身不可怕可怕的是应用没做重试。MySQL 检测到死锁会自动回滚其中一个事务报错编号是 1213提示Deadlock found when trying to get lock; try restarting transaction。业务代码必须捕获这个异常做重试比如批量任务里更新失败后等几百毫秒再跑一次否则整个事务直接失败用户看到报错。分析死锁现场最经典的方法是执行SHOW ENGINE INNODB STATUS\G看输出里的LATEST DETECTED DEADLOCK段里面会记录两个事务的等待链和最后执行的 SQL。红色或KEY编码其实没有固定格式要人工解读。常见死锁原因不外乎两种事务顺序不一致比如 A 事务先更新表 1 再更新表 2B 事务先更新表 2 再更新表 1或者一个事务锁了主键另一个事务锁了二级索引两边又交叉等待同一行。我的建议是业务开发时把“统一更新顺序”列到规范里如果需要更新多张表始终按相同顺序执行事务体量要小别在事务里做远程调用或复杂计算如果死锁发生频率高优先优化 SQL 减少锁范围而不是强行提升隔离级别或者关闭死锁检测那都是掩耳盗铃。5. 备份、binlog 与同步方案笔记5.1 mysqldump 逻辑备份与恢复备份是数据库的保命符必须定期做并且演练恢复。官方逻辑备份工具mysqldump最常见的用法mysqldump -uroot -p --single-transaction --routines --triggers --set-gtid-purgedOFF shop shop_20250601.sql--single-transaction很关键它利用 InnoDB 的一致性快照备份期间不会锁业务表。--routines和--triggers是备份存储过程、函数和触发器默认不备份忘记加的话恢复后应用可能要报错。--set-gtid-purgedOFF是为了避免把源库的 GTID 信息写到 dump 文件里否则同步到目标库时会干扰复制关系。恢复命令很简单mysql -uroot -p shop shop_20250601.sql但要注意两个点如果 dump 文件里没有CREATE DATABASE shop恢复前要自己提前建库如果目标库是复制架构中的从库导入逻辑备份后可能需要重新配置复制位点。大库全量 dump 文件动辄几十 GB恢复时间很长速度慢时可以尝试mysql --max-allowed-packet1G dump.sql调大包限制。5.2 binlog 解析与主从同步binlog 是 MySQL 实现复制和数据恢复的基础。查看当前二进制日志列表SHOW BINARY LOGS; SHOW MASTER STATUS;解析 binlog 成可读 SQLmysqlbinlog --base64-outputDECODE-ROWS -v mysql-bin.000001 binlog.sql8.0 里CHANGE MASTER TO已经逐渐被CHANGE REPLICATION SOURCE TO取代。配置主从的大致流程主库my.cnf设置server-id1和log_binmysql-bin从库server-id2然后主库创建同步账号并授权REPLICATION SLAVE, REPLICATION CLIENT。从库上先CHANGE REPLICATION SOURCE TO SOURCE_HOST..., SOURCE_USERrepl, SOURCE_PASSWORD..., SOURCE_LOG_FILEmysql-bin.000001, SOURCE_LOG_POSxxx;再执行START REPLICA;。网上搜“数据库同步软件”能看到很多工具比如必须部署同步组件或者消息队列消费 binlog。如果你只是想搭建主从原生复制就够了如果你的场景是异构同步比如 MySQL 到 Elasticsearch那才需要考虑 canal 这类组件。先分清需求别被关键词带偏。5.3 不要直接搬运 ibd 文件“数据库idb文件”这个话题经常有人搜索我必须泼一盆冷水直接从一台机器拷贝.ibd文件到另一台机器大多数情况下是行不通的。8.0 已经移除了.frm文件表定义全部放在数据字典里.ibd文件里的表空间 ID 和源实例绑定直接复制过去目标实例无法识别可能直接报表不存在或者表空间不匹配。如果确实要迁移单个表可以使用官方可传输表空间先ALTER TABLE t DISCARD TABLESPACE;把.ibd文件复制进去再ALTER TABLE t IMPORT TABLESPACE;。但这个操作要求表结构必须原样存在还要处理表空间加密、行格式等一堆细节生产环境风险很高。我自己的经验是普通场景优先 mysqldump 或物理备份方式比如 XtraBackup可传输表空间只在严格测试过的大表搬迁中考虑。6. 慢查询、压测与优化清单6.1 慢查询日志与 EXPLAIN 优化性能问题里“SQL 慢”占了大头。开启慢查询日志slow_query_logON long_query_time1 log_queries_not_using_indexesON长期开着slow_query_log对性能影响很小但可以抓到大部分隐患。日志多了以后用mysqldumpslow汇总mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log拿到慢 SQL 后用EXPLAIN分析8.0 支持EXPLAIN ANALYZE会真实执行 SQL 并给出实际耗时和读取行数比传统EXPLAIN更能暴露问题EXPLAIN ANALYZE SELECT * FROM orders WHERE order_no SO20240001;重点看type字段const/ref是理想情况range可以接受index和ALL就说明没走索引要优化了。看到Using filesort或者Using temporary时优先考虑排序字段是否和索引顺序一致或者调整ORDER BY和GROUP BY的字段。加了索引也不一定快如果对索引列使用函数比如WHERE DATE(create_time) CURDATE()索引也会失效改成范围查询WHERE create_time 2025-06-01 00:00:00 AND create_time 2025-06-02 00:00:00才有效。6.2 JMeter 压测 MySQL 的配置要点“jmeter数据库压测脚本”是很多人做容量评估时搜的词。JMeter 压 MySQL 前先把 MySQL 的 JDBC 驱动 jar 放到 JMeter 的lib目录。然后在测试计划里添加 JDBC Connection Configuration关键配置Variable Namemysql_poolDatabase URLjdbc:mysql://localhost:3306/shop?useSSLfalseserverTimezoneAsia/ShanghaiJDBC Driver Classcom.mysql.cj.jdbc.DriverUsername/Password数据库账号再加 JDBC Request把要压测的 SQL 写在Query输入框。线程组不要一上来拉到 1000否则往往先挂的是网络或者连接池。正确做法是阶梯加压500、1000、2000观察Threads_connected、CPU、磁盘 IO 和慢日志找到拐点。压测结果里的 TPS 和 RT 只能作为相对参考因为测试环境、数据量和线上差异很大。压测前先跑EXPLAIN确保压测的 SQL 都是合理走索引的不然压出来的数据没有意义。6.3 常用优化参数与索引实践经验数据库层常见优化参数我对 8.0 的维护经验如下参数建议值说明innodb_buffer_pool_size物理内存 60%核心缓存innodb_log_file_size256M~1Glogfile 太小会频繁刷盘max_connections300~800按业务实际sort_buffer_size2M不要盲目加大tmp_table_size64M临时表阈值max_heap_table_size64M内存临时表上限索引实践方面几个高频经验联合索引要遵守最左前缀查询条件里第一个字段必须命中索引最左字段区分度很低的列比如性别、状态单独建索引往往没用一个表别建太多索引写入性能会下降尽量用覆盖索引让 SELECT 的列都在索引里可以跳过回表。8.0 支持索引降序扫描比如INDEX ON user(create_time DESC)如果业务经常ORDER BY create_time DESC可以设计降序索引但加不加要看执行计划别盲加。7. 常见报错与面试高频考点7.1 Navicat 连接失败与 Too many connections“安装2025sqlserve安装成功了 navichat连接不了数据库”这类组合热点很常见虽然标题里混着 SQL Server但连接不上 MySQL 的场景我们可以单独说。Navicat 连不上 8.0常见五个原因MySQL 服务没启动检查进程和 3306 端口用户 host 不允许远程登录比如用户是applocalhost却用 Navicat 从 192.168.x.x 连肯定失败防火墙拦了 3306认证插件还是caching_sha2_passwordNavicat 版本太老不支持连接数满了出现Too many connections。其中 host 问题是新手重灾区。程序账号尽量按业务网段创建比如CREATE USER app192.168.1.% IDENTIFIED BY ...;不要图省事用%表示所有 IP。如果连接数满临时救急可以先在 SQL 里执行SET GLOBAL max_connections 2000;让服务先恢复再回来查慢 SQL 和应用连接泄漏原因不解决调再大也会二次爆炸。网络上搜索“该数据库不可以执行非日志模式的大容量复制请联系数据库所有者(dbo)”这个报错其实是 SQL Server 的提示不是 MySQL。遇到问题时先确认你的数据库产品和版本别拿 A 数据库的报错经验硬套 B 数据库这是排查问题的基本素养。7.2 数据库面试核心知识点速记“数据库面试题”通常围绕几个固定核心我顺手整理成速记版InnoDB 与 MyISAMInnoDB 支持事务、行锁、崩溃恢复MyISAM 只支持表锁且没有事务现在默认都是 InnoDB索引结构InnoDB 用 B 树叶子节点存数据支持顺序扫描哈希索引不适合范围查询事务隔离读未提交、读已提交、可重复读、串行化MySQL 默认可重复读通过 MVCC 和间隙锁解决幻读redo log 和 binlogredo log 是 Innodb 的物理日志保证崩溃恢复binlog 是服务层逻辑日志用于复制和时间点恢复两者配合两阶段提交保证一致性慢 SQL 优化先 EXPLAIN再决定加索引还是改写 SQL死锁两边事务锁资源顺序冲突数据库回滚代价小的事务应用侧要有重试机制。这些知识不是背下来就能拿 Offer 的面试官一般会追问“你们项目里真的遇到过死锁吗”“你怎么定位的”所以实际操作经验比概念更重要。8. 写在实际维护之后最后说一点自己的体会。数据库维护这行真正值钱的不是会敲几个命令而是对“操作后果”的判断力。我见过太多线上事故不是因为命令不会而是因为没想清楚就执行了ALTER TABLE、DELETE或者GRANT。MySQL 8.0 给了我很多便利比如数据字典更清晰、EXPLAIN ANALYZE更直观、锁信息更透明但版本越新越应该保持对底层机制的理解。这份笔记写到这里我建议你把它当成一份工具手册遇到问题先查SHOW和EXPLAIN再改参数做完操作要验证。如果以后我遇到新的坑会继续往这份查阅笔记里补充。你也可以从自己的项目出发把每次排查记录整理成自己的版本那才是最适合你的数据库笔记。