拓冰网站定制 
写这份手册的念头其实是我自己刚接触MySQL那会儿踩了太多坑攒出来的。当时看文档看不进去记命令记不住一碰到服务启动失败或者字符集乱码就上网东翻西找时间全耗在搜索里。后来靠一套自己的常用命令清单把日常大部分操作都固定在几条命令上慢慢才觉得心里有底。这里整理的是我自己长期在用的MySQL常用命令按场景拆开讲覆盖从安装到日常操作再到问题排查的完整链路。不管你是刚装好MySQL正被服务无法启动卡住的新手还是在写SQL、搞事务、调索引时想要一份速查的开发者这份手册都能直接拿去用。每个命令我都尽可能说清楚背后的逻辑以及我实际使用中遇到的坑。1. 安装、启动与基础环境配置1.1 MySQL版本怎么选很多人在下载安装那一步就开始纠结尤其是看到MySQL 5.7和8.0两个大版本不知道选哪个。我个人的建议是新项目直接用8.0老项目或者对稳定性要求极高、团队又没人熟悉8.0新特性的场景继续用5.7也没问题。8.0相比5.7有几个肉眼可见的变化默认字符集从latin1改成了utf8mb4不用再每次建库都手动指定字符集新增了窗口函数和公共表表达式CTE写复杂统计SQL会舒服很多另外身份认证插件默认是caching_sha2_password这个会直接影响你连数据库的方式后面我会专门讲。在Linux上我常用的下载方式是直接到官网拿tar包解压或者用rpm安装。如果你走rpm路线命令大概是这样的# 下载mysql community rpm包 wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm rpm -Uvh mysql80-community-release-el7-3.noarch.rpm yum install mysql-community-server解压安装方式用的命令组是groupadd mysql useradd -r -g mysql mysql tar -zxvf mysql-8.0.xx-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.xx /usr/local/mysql cd /usr/local/mysql mkdir data chown -R mysql:mysql /usr/local/mysql bin/mysqld --initialize --usermysql bin/mysqld_safe --usermysql 这里有一个非常关键的细节初始化时一定要留意日志里的临时密码。MySQL 8.0在初始化时不会像5.7那样默认让你直接空密码登录而是在日志文件里打印一行[Note] A temporary password is generated for rootlocalhost: xxxxxxxx我第一次装8.0的时候没注意这个临时密码初始化完怎么登都登不进去后来重新初始化一遍才看到。拿到临时密码后登录进去第一件事就是改密码ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;1.2 服务启动失败排查net start mysql 服务无法启动这几个字应该是被搜索最多的MySQL报错之一。触发原因很多但常见就那么几类我按概率排了一下第一类my.ini或my.cnf配置文件有问题。路径写错、参数值不合法、basedir或datadir指向不对都会导致服务起不来。排查方法很简单去MySQL的data目录下看错误日志文件名叫hostname.err最后几十行通常直接告诉你原因。第二类data目录权限不对。Linux上用rpm安装后如果data目录属主不是mysql用户服务一样起不来。解决办法chown -R mysql:mysql /var/lib/mysql/第三类端口或socket被占用。如果你之前装过MySQL实例没停干净或者机器上有MariaDB3306端口被占新服务自然起不来。用netstat -tlnp | grep 3306看一眼就知道。Windows上还有一个很隐蔽的问题服务路径指向了旧版本或错误目录。我见过有人装了两个版本的MySQL旧服务残留结果服务管理器里的MySQL指向的其实是旧的bin路径。这种直接去服务属性里改可执行文件路径或者删掉旧服务重新注册mysqld --remove mysqld --install再强调一次MySQL服务起不来永远是先看日志别瞎猜。日志文件里每一行报错都有明确的Error Code把日志贴到搜索引擎里比你自己盲试命令高效得多。1.3 Docker环境下安装MySQL现在很多开发环境直接用Docker跑MySQL省去本地安装的折腾。我常用的运行命令是docker run -d \ --name mysql \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ -e MYSQL_DATABASEtestdb \ -v /opt/mysql-data:/var/lib/mysql \ mysql:8.0这里有几个点必须注意。一个是-v目录挂载数据目录一定要挂出来否则容器删了数据就全没了。另一个是字符集虽然8.0默认utf8mb4但5.7镜像默认还是latin1建议启动时加上参数docker run -d \ --name mysql \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_unicode_ci \ -v /opt/mysql-data:/var/lib/mysql \ mysql:5.7docker方式装MySQL如果启动失败不要只看docker logs这个只能看到容器层的日志数据库自身的错误日志还是要进容器里看文件docker logs mysql docker exec -it mysql bash cat /var/log/mysql/error.log还有一个大家容易忽略的问题容器重启策略。生产环境尽量加上--restartalways否则服务器重启后MySQL容器不会自动拉起应用层依赖数据库的服务就全断了。2. 连接数据库与日常登录管理2.1 登录与连接状态查看命令行登录MySQL是最基础的操作但里面的门道也不少。mysql -u root -p mysql -u root -p -h 127.0.0.1 -P 3306 mysql -u root -p -h 192.168.1.10 -P 3306 --default-character-setutf8mb4注意-h后面如果写localhost走的是socket连接写127.0.0.1或真实IP走的是TCP连接。两者在某些场景下行为不一样比如localhost可以跳过某些host限制127.0.0.1则受mysql.user表里host字段控制。遇到Access denied for user rootlocalhost这类报错多半就是host不匹配导致的。连接上之后几个最常用的查看命令SELECT VERSION(); SELECT NOW(); SELECT DATABASE(); SELECT USER(); SHOW PROCESSLIST; SHOW VARIABLES LIKE %character%;SHOW PROCESSLIST这个命令我要单独说一下。它的作用是看当前所有数据库连接的线程状态。当数据库变慢、卡死、连接数飙高的时候第一件事就是执行它看看哪些查询在跑、跑了多久、有没有State列显示Locked或Sending data卡住。信息字段里最关键的是Time列如果超过几十秒基本可以判定为慢查询或者存在锁问题可能需要kill掉KILL thread_id;比如SHOW PROCESSLIST里看到一个id为123的查询已经跑了200秒直接KILL 123;就能把它杀掉。这个操作要谨慎生产环境杀掉正在跑的查询可能造成事务回滚但总比让数据库一直锁着强。2.2 用户与权限管理除了root用户实际项目里通常要单独建业务账号。因为root权限太大误操作或者被入侵的后果都很严重。建用户和授权的完整流程CREATE USER app_user% IDENTIFIED BY 密码; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_user%; GRANT ALL PRIVILEGES ON mydb.* TO app_user%; FLUSH PRIVILEGES;权限粒度要把握好。只读账号就只给SELECT写入账号再给INSERT、UPDATE、DELETEDBA级别的操作永远不要用业务账号干。FLUSH PRIVILEGES是用来刷新权限表的通常在直接修改mysql.user表之后才需要执行用GRANT授权的场景其实可以不执行但养成习惯也无妨。查看某个用户的权限SHOW GRANTS FOR app_user%;修改密码的三种姿势ALTER USER rootlocalhost IDENTIFIED BY 新密码; SET PASSWORD FOR rootlocalhost 新密码; -- 5.7及以下版本 SET PASSWORD PASSWORD(新密码);8.0不建议用PASSWORD()函数它已经被废弃了。如果客户端连不上8.0报错Authentication plugin caching_sha2_password cannot be loaded有两个解决思路一是更新客户端驱动让驱动支持新认证协议二是在MySQL里把用户的认证方式改回mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 密码;第二种是临时的新项目最好还是用新驱动适配新认证方式别一直迁就旧协议。2.3 SSL连接错误处理mysql ssl连接错误也是高频搜索词。MySQL 8.0默认开启了SSL连接有时候客户端工具没配好证书或者协议不匹配会报SSL相关错误。我遇到最多的场景是Java应用连MySQL 8.0时报javax.net.ssl.SSLHandshakeException。处理思路一般是三步走先看数据库端SSL状态然后确认驱动版本最后决定是完整配置SSL还是先关闭SSL穿透测试。查看SSL状态SHOW VARIABLES LIKE %ssl%;如果返回have_ssl为YES说明服务端开了SSL。驱动版本太旧不支持新的SSL协议就会握手失败这种情况升级驱动最干净。如果测试阶段不想折腾SSL可以在连接串上加参数绕过jdbc:mysql://127.0.0.1:3306/testdb?useSSLfalseserverTimezoneAsia/Shanghai注意useSSLfalse只建议在开发或内网环境使用公网传输的数据链路一旦被截获明文数据就全裸奔了。生产环境该配证书还是得配别图省事把安全底线丢了。3. 数据库与表结构操作命令3.1 创建数据库与表创建库最完整的写法CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;utf8mb4能存emoji和生僻字而utf8在MySQL里实际上是utf8mb3根本存不了四字节字符。从5.5版本开始就有utf8mb4了但很多人还是惯性用utf8导致后面往表里插emoji时报错Incorrect string value。从5.7开始官方推荐utf8mb48.0更是直接默认所以建库建表时把字符集显式写清楚省得后续一堆乱码麻烦。查看字符集相关命令SHOW CREATE DATABASE mydb; SHOW VARIABLES LIKE character_set_database;建表命令的完整优点示例CREATE TABLE IF NOT EXISTS user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) NOT NULL COMMENT 用户名, age INT DEFAULT 0 COMMENT 年龄, email VARCHAR(128) DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, UNIQUE KEY uk_username (username), KEY idx_age (age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户信息表;这几个字段设计上的细节值得说道说道。id用BIGINT AUTO_INCREMENT做主键是因为在实际业务里INT的范围只有21亿左右看似很大但流水表很快就可能不够用。created_at和updated_at直接让数据库维护时间戳应用层不用每次插入都传时间。ON UPDATE CURRENT_TIMESTAMP会在记录更新时自动刷新时间这个特性在做数据审计、排查脏数据时特别好用能知道这条记录最后是什么时候被动过的。查看表结构DESC user_info; SHOW CREATE TABLE user_info;DESC给出的是精简的字段列表SHOW CREATE TABLE给出的是完整的建表语句包括索引、约束、字符集等细节。修改字段或者排查建表问题时后者更有用。3.2 修改表结构的常用命令开发迭代过程中修改表结构是最频繁的操作之一。这一组命令我基本每周都会用到-- 添加字段 ALTER TABLE user_info ADD COLUMN address VARCHAR(255) DEFAULT NULL COMMENT 地址 AFTER email; -- 修改字段类型或属性 ALTER TABLE user_info MODIFY COLUMN age INT NOT NULL DEFAULT 0; -- 修改字段名和定义 ALTER TABLE user_info CHANGE COLUMN email contact_email VARCHAR(128) DEFAULT NULL; -- 删除字段 ALTER TABLE user_info DROP COLUMN address; -- 修改表名 RENAME TABLE user_info TO user_detail;MODIFY和CHANGE的区别很多人分不清。MODIFY只改字段的属性类型、默认值不改变字段名CHANGE可以同时改字段名和属性所以CHANGE后面要写两遍字段名——一遍旧名、一遍新名。如果你只是想改类型却用CHANGE第二个名字重复写一遍旧名也能实现但多了一个出错的可能所以规则是改类型用MODIFY改字段名用CHANGE。“mysql设置默认值为0”这个搜索词对应的就是这类操作的需求。比如新增一个状态字段默认值要设为0ALTER TABLE user_info ADD COLUMN status TINYINT NOT NULL DEFAULT 0 COMMENT 状态 0-正常 1-禁用;有些同学写SQL时忘了NOT NULL和DEFAULT结果字段允许NULL、默认值也是NULL业务代码拿到数据后还要多做一层空值判断凭空增加很多麻烦。设计字段时把NULL和空串区分开通常业务状态字段用NOT NULL DEFAULT 0不需要有意义的字段才允许NULL。还有一个容易忽略的细节大表加字段时要注意锁表问题。MySQL 5.6之后ALTER TABLE里的很多操作比如加字段使用了Online DDL但不是所有操作都完全在线有些场景还是会短暂锁表。在几百万行的大表上做结构变更最好安排在业务低峰期并且先用一个小表测试一下耗时心里有数再操作。3.3 存储过程的简单使用“mysql存储过程”也是经常搜的词。存储过程就是把一段SQL逻辑存到数据库里让应用层调用。我不建议把复杂业务逻辑都写进存储过程因为后期维护是真的痛苦但有些固定批处理任务比如按月清理历史数据用存储过程还是可以的。一个简单的存储过程示例DELIMITER $$ CREATE PROCEDURE sp_cleanup_old_logs(IN days INT) BEGIN DELETE FROM operation_log WHERE created_at DATE_SUB(NOW(), INTERVAL days DAY); END$$ DELIMITER ; CALL sp_cleanup_old_logs(30); DROP PROCEDURE IF EXISTS sp_cleanup_old_logs;注意DELIMITER这个命令。MySQL默认用分号作为语句结束符而存储过程内部有多个分号如果不用DELIMITER改掉结束符客户端会在写第一行的时候就判定语句结束导致整个存储过程无法完整提交。写完存储过程再用DELIMITER ;把它改回来这一步千万别忘。4. 数据操作增删改查与排序分页4.1 INSERT、UPDATE、DELETE的正确姿势数据操作算是DBA和开发的高频动作了但很多线上事故恰恰出在最基础的增删改查上。插入数据的常见写法INSERT INTO user_info (username, age, email) VALUES (zhangsan, 25, zsexample.com); INSERT INTO user_info (username, age, email) VALUES (lisi, 30, lisiexample.com), (wangwu, 28, wangwuexample.com); INSERT INTO user_info (username, age, email) SELECT username, age, email FROM temp_user WHERE age 18;还有一种在数据迁移时很好用的写法是INSERT ... ON DUPLICATE KEY UPDATE遇到主键或唯一键冲突就更新不冲突就插入INSERT INTO user_info (id, username, age) VALUES (1, zhangsan, 26) ON DUPLICATE KEY UPDATE username VALUES(username), age VALUES(age);MySQL 8.0.20之后VALUES()在ON DUPLICATE KEY UPDATE里有更新写法了建议用AS语法。但常规版本里上面这种写法还能用新版本里推荐INSERT INTO t1 (a, b) VALUES (1, 2) AS new ON DUPLICATE KEY UPDATE b new.b;UPDATE的一个大坑是忘记带WHERE条件。执行前一定要自问我是不是要更新全表如果不是必须写WHEREUPDATE user_info SET age 26 WHERE username zhangsan;DELETE同理DELETE FROM user_info WHERE id 100;在生产环境修改或删除前有条件的我会先跑一遍SELECT确认影响行数SELECT COUNT(*) FROM user_info WHERE username zhangsan;确认只有一条或预期条数再执行UPDATE或DELETE。这条习惯帮我挡了好几次出大事的机会强烈建议你也养成。再补一个“mysql update 还原”相关的场景。有时UPDATE操作失误把字段值改错了想还原。如果你的数据库有备份用备份恢复即可如果没有备份MySQL本身有binlog可以通过binlog把操作反转但操作难度不小不是一条命令就能解决的。日常我建议用两个土办法兜底一个是执行UPDATE前先把受影响的行备份到一张临时表CREATE TABLE user_info_bak_20250101 AS SELECT * FROM user_info WHERE id 100;另一个是开启事务错了直接回滚START TRANSACTION; UPDATE user_info SET age 26 WHERE id 100; -- 检查结果 SELECT * FROM user_info WHERE id 100; -- 不对就回滚 ROLLBACK; -- 对了就提交 COMMIT;4.2 SELECT查询、排序、分组与分页查询语句是MySQL使用频率最高的命令门类看起来人人会写但很多细节值得打磨。基础查询SELECT id, username, age FROM user_info WHERE age 18 AND age 30; SELECT id, username, age FROM user_info WHERE age BETWEEN 18 AND 30; SELECT id, username, age FROM user_info WHERE username IN (zhangsan, lisi); SELECT id, username, age FROM user_info WHERE username LIKE zhang%;LIKE查询有一个性能注意点%写在开头比如%zhang会导致索引失效因为无法利用B树的顺序查找特性。数据量大时这种查询就是全表扫描尽量改写。“mysql排序”是个热门搜索排序的完整语法SELECT id, username, age FROM user_info ORDER BY age DESC, id ASC;ORDER BY可以按多个字段级联排序第一个字段相同才按第二个字段排。默认是ASC升序。注意排序字段最好有索引否则大数据量下ORDER BY会走filesort性能会明显下降。在联合索引的场景下排序字段要匹配索引字段顺序否则优化器也不会用索引排序。分组统计SELECT age, COUNT(*) AS cnt FROM user_info GROUP BY age HAVING cnt 1;WHERE和HAVING的分工要记清楚WHERE是分组前过滤行HAVING是分组后过滤组。先按条件把行缩小范围再分组效率更高能写进WHERE的条件尽量别放HAVING。分页查询SELECT * FROM user_info ORDER BY id LIMIT 20, 20; SELECT * FROM user_info ORDER BY id LIMIT 20 OFFSET 20;两种写法等价LIMIT 20, 20表示跳过20条取20条LIMIT 20 OFFSET 20表示偏移20条取20条。对于深分页比如跳过了1万条MySQL需要扫描前面所有行才能定位到目标偏移量性能很差。碰到这种场景可以用游标式分页SELECT * FROM user_info WHERE id 10000 ORDER BY id LIMIT 20;记住上次最大id下次查询直接带上就能让每次查询都命中索引范围扫描这是大表分页的通用优化思路。去重查询用DISTINCTSELECT DISTINCT age FROM user_info;排查重复数据用的分组聚合SELECT age, COUNT(*) FROM user_info GROUP BY age HAVING COUNT(*) 1;4.3 事务的开启与管理事务是MySQL保证数据一致性的核心机制。InnoDB支持事务MyISAM不支持这也是为什么现在都默认用InnoDB。事务的基本流程START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果第二步更新失败可以整体回滚ROLLBACK;事务的四个隔离级别默认是REPEATABLE READ可重复读-- 查看当前隔离级别 SELECT transaction_isolation; -- 8.0之前的版本查这个 SELECT tx_isolation; -- 设置隔离级别会话级 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;讲个实际场景。你在事务里先查询了一条记录然后别的连接修改并提交了这条记录你再次查询时在REPEATABLE READ级别下看到的数据还是原来的——这就是可重复读的语义。如果业务上需要能看到别人刚提交的数据可能要降级到READ COMMITTED。但是这只是一个权衡因为降低隔离级别会引入不可重复读和幻读问题。正常情况下保持默认就好非必要不改。事务里还有一个隐患是“长事务”。一个事务长时间不提交会持有锁影响其他连接还会导致undo log膨胀。所以事务里面别做耗时太长的外部调用比如HTTP请求开事务前先想清楚处理完立刻提交。5. 索引、锁与性能优化命令5.1 索引的创建与查看“mysql创建索引”、“mysql锁的分类”、“mysql面试题”这些热搜词说明了索引有多重要面试会问实际开发中也经常要调。创建索引的命令-- 普通索引 CREATE INDEX idx_age ON user_info(age); -- 唯一索引 CREATE UNIQUE INDEX uk_email ON user_info(email); -- 联合索引 CREATE INDEX idx_age_name ON user_info(age, username); -- 在创建表时指定索引 -- 见前文的建表示例中的 KEY idx_age (age) -- 删除索引 DROP INDEX idx_age ON user_info;查看表的索引SHOW INDEX FROM user_info;建立联合索引时最核心的原则是把区分度高的字段放前面。比如idx_age_name(age, username)如果age字段只有几个取值比如年龄段就那么几个区分度不高放前面可能不如把username放前面。但也要看实际查询条件最准确的方法是结合查询语句看WHERE里哪些字段能等值匹配然后通过EXPLAIN验证。索引不是越多越好。每多一个索引写入时就要多维护一颗B树插入删除的速度都会受影响。我见过有人为了性能给表加了十几个索引结果写入慢到卡顿完全得不偿失。索引建在查询频繁的字段上而不是所有字段上这句话值得抄在工位上。还有一个容易被忽略的点TEXT、BLOB等大字段没法直接加完整索引只能建前缀索引CREATE INDEX idx_email_prefix ON user_info(email(20));意思是取email字段前20个字符建索引。前缀长度要平衡索引大小和区分度取太短可能区分度不够取太长索引就失去意义了一般不算太难的判断。5.2 EXPLAIN查看执行计划分析SQL跑得慢的利器是EXPLAIN这也是我排查慢查询的第一板斧。EXPLAIN SELECT id, username FROM user_info WHERE age 25 ORDER BY id;执行后输出一张表核心列含义如下type访问类型从上到下性能从好到坏依次是systemconsteq_refrefrangeindexALL。看到ALL基本意味着全表扫描要警惕。key实际用到的索引。rows预估扫描的行数数值越小越好。Extra如果出现Using filesort或Using temporary说明查询可能需要优化比如排序字段无索引、或者分组操作无法用索引完成。一个实际例子。查询语句SELECT * FROM user_info WHERE age 25 ORDER BY created_at;在只有idx_age的情况下EXPLAIN结果里可能会出现Using filesort因为排序字段created_at没在索引中。优化方案是把索引改成联合索引CREATE INDEX idx_age_created ON user_info(age, created_at);这样既能用索引定位age 25的行又能按索引顺序直接读取created_atfilesort就消失了。这种优化思路在面试和日常调优里都很常见。5.3 锁的分类与处理mysql锁的分类也是搜索高频词。MySQL的锁可以简单按粒度分表级锁和行级锁。表级锁用LOCK TABLES显式加锁LOCK TABLES user_info READ; -- 此时其他会话可以读但不能写 UNLOCK TABLES;行级锁由InnoDB在事务中自动管理。共享锁S锁用LOCK IN SHARE MODE排他锁用FOR UPDATESTART TRANSACTION; SELECT * FROM user_info WHERE id 100 LOCK IN SHARE MODE; -- 其他事务可以读同一行但不能修改 START TRANSACTION; SELECT * FROM user_info WHERE id 100 FOR UPDATE; -- 其他事务对该行的读写都会被阻塞FOR UPDATE常用于乐观锁/悲观锁场景。比如电商秒杀扣库存时先SELECT ... FOR UPDATE锁定库存行再进行扣减操作能避免超卖。如果两个事务互相持有对方需要的锁就可能产生死锁InnoDB会自动检测并回滚其中一个事务让另一个执行成功。生产环境遇到死锁报错Deadlock found when trying to get lock处理方式是先把事务拆短减少锁的持有时间多个事务访问多张表时尽量用一致的顺序访问降低隔离级别也能减少某些锁竞争。锁问题排查手段-- 查看当前事务和锁等待状态 SELECT * FROM information_schema.INNODB_TRX; SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 或者新版使用 SELECT * FROM performance_schema.data_locks;查出事务ID后如果确定某条事务卡死了可以杀掉它KILL trx_mysql_thread_id;八成线上“数据库卡住”都是长事务持锁不释放导致的按上面三步基本能定位到元凶。6. 数据备份、导入导出与迁移6.1 mysqldump备份备份这一步千万别省。我用得最顺手的备份工具就是mysqldump。# 备份单个数据库 mysqldump -u root -p mydb mydb.sql # 备份多个数据库 mysqldump -u root -p --databases mydb1 mydb2 multi_db.sql # 备份所有数据库 mysqldump -u root -p --all-databases all_db.sql # 只备份表结构不备份数据 mysqldump -u root -p --no-data mydb schema.sql # 只备份数据不备份表结构 mysqldump -u root -p --no-create-info mydb data.sql实际操作中备份大库时网络中断会导致备份文件损坏所以我的备份脚本里会加上--single-transaction和--quick参数这两个对InnoDB尤为重要可以在不锁表的情况下完成一致性备份mysqldump -u root -p --single-transaction --quick mydb mydb.sql--single-transaction利用InnoDB的事务特性在快照读的基础上备份不影响业务读写。5.7和8.0下直接用。如果备份的是MyISAM表得另行加锁但现在基本没有这个需求了。恢复数据的操作mysql -u root -p mydb mydb.sql如果SQL文件比较大比如上G直接在终端里恢复容易中途断掉也看不到进度。可以先进入mysql命令行然后SOURCE执行USE mydb; SOURCE /data/backup/mydb.sql;SOURCE的好处是用一个会话连续执行出错能看到具体行号方便定位问题。但SOURCE不会自动跳过错误默认遇到错误就继续往下执行所以如果备份文件里有几条没导入干净建议导完以后统计一下表行数和备份前对一下。6.2 导入导出CSV与其他迁移方式很多场景需要把MySQL数据导出给其他系统或者从文件导入数据。CSV是最通用的格式。导出CSVSELECT id, username, age INTO OUTFILE /tmp/user_info.csv FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n FROM user_info;注意MySQL 8.0默认禁止SELECT ... INTO OUTFILE导出到任意路径会报错The MySQL server is running with the --secure-file-priv option。解决办法是查看允许的导出目录SHOW VARIABLES LIKE secure_file_priv;把导出文件放到该目录下即可。或者也可以不经过CSV直接通过客户端工具导出到本地。从CSV导入MySQLLOAD DATA INFILE /tmp/user_info.csv INTO TABLE user_info FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS;IGNORE 1 ROWS是跳过第一行表头。如果CSV文件里某些字段是空值可以用SET子句转换LOAD DATA INFILE /tmp/user_info.csv INTO TABLE user_info FIELDS TERMINATED BY , IGNORE 1 ROWS (age, email, created) SET created_at IF(created , NOW(), created);注意导入大文件之前先看看目标表和CSV字段的数量、顺序是否完全对得上。字段差一位整个表的数据就可能错位。我的做法是先导入小样本比如前100行验证再导全量。还有一条经常被搜索的“使用flink实现mysql同步到clickhouse”属于数据实时同步体系。核心原理是开启MySQL的binlog通过Flink CDC组件捕获binlog变更事件写入ClickHouse。命令层面需要确认MySQL开启了binlogSHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE binlog_format;binlog_format建议是ROW格式因为Flink CDC解析的就是行级变更。这个体系搭起来比导CSV复杂一个数量级适合实时数仓场景但如果只是定期同步简单粗暴的mysqldump 导入反而更稳。7. 高频报错与排查方法汇总7.1 升级与初始化报错my-014060热搜词里有[error] [my-014060] [server] invalid mysql server upgrade。这个报错通常出现在MySQL进行版本升级时服务器检测到数据目录的版本信息异常。我遇到过的情况是从5.7直接跳到8.0或者数据目录拷贝后没清理干净导致系统认为需要升级但数据字典又不匹配直接崩了。解决办法分两步第一步确认数据目录下有一个叫mysql.ibd或ibdata1的文件这是系统表空间别动它。然后查看数据目录的版本信息SHOW VARIABLES LIKE innodb_version;第二步严格按照官方升级路径来5.7要先升级到5.7最新版再升8.0不能直接跨大版本跳。升级前先备份整个数据目录然后用mysqld --initialize重新初始化一个干净数据目录加载备份而不是把旧目录硬指过去。我个人的建议是升级数据库这种事情不做最好要做就必须有完整预案。先在一个测试环境模拟整个升级过程确认所有存储过程、函数、SQL语法在8.0下都能正常跑再动生产。7.2 字符集乱码问题乱码问题在MySQL里几乎人人遇到。本质原因就是客户端、连接、服务端、表、字段的字符集不一致。系统性排查字符集的方法SHOW VARIABLES LIKE character_set_client; SHOW VARIABLES LIKE character_set_connection; SHOW VARIABLES LIKE character_set_results;修改当前会话的字符集SET NAMES utf8mb4;SET NAMES utf8mb4等价于同时把character_set_client、character_set_connection、character_set_results三个变量都设成utf8mb4是最快的临时解决办法。永久解决靠配置文件。Linux下是/etc/my.cnfWindows下是my.ini加这一段[client] default-character-setutf8mb4 [mysql] default-character-setutf8mb4 [mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci改完之后重启MySQL再用SHOW VARIABLES LIKE character%确认全部变成utf8mb4。如果已经建好的表是latin1或有乱码历史数据需要转换表字符集ALTER TABLE user_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这条命令会重写整张表数据量大时耗时长且可能锁表建议低峰期执行。7.3 连接数耗尽与慢查询连接数耗尽的一个常见报错是Too many connections排查和解决思路-- 查看最大连接数和当前连接数 SHOW VARIABLES LIKE max_connections; SHOW STATUS LIKE Threads_connected;如果当前连接数接近max_connections说明连接池配置过大或者有慢查询占住连接不释放。临时调大连接数SET GLOBAL max_connections 500;但临时调大是治标不治本。更根本的思路是找到占用连接的会话看它们都在干什么SELECT id, user, host, db, time, state, info FROM information_schema.processlist WHERE command ! Sleep;把time很大的查询逐条分析加索引或者改SQL。如果应用服务的连接池配置过大比如Spring Boot的hikari最大连接数设置成100但数据库最大连接数只有150也需要调小应用连接池。慢查询日志的开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;这些配置线上最好通过my.cnf持久化slow_query_log 1 long_query_time 1 slow_query_log_file /var/log/mysql/slow.log开启后会记录所有超过1秒的SQL定期看这个文件就能把潜在的慢SQL揪出来逐一优化。注意long_query_time单位是秒设置为1意味着执行1秒以上都记录下来。对于大部分业务系统建议设成0.5甚至更小抓得更准。7.4 其他我见过的高频错误数据库字符处理错误Incorrect string value: \xF0\x9F... for column原因几乎都是utf8mb3无法存储emoji。核对一下库表字符集是不是utf8mb4。You are not allowed to use the\xF0\x9F...literal.如下是把上述报错转述成普通说法原因是字符集不对建表时用了utf8而不是utf8mb4。索引长度超限Specified key was too long; max key length is 3072 bytesInnoDB索引最大长度是3072字节utf8mb4一个字符最多占4个字节也就是说索引字段最多768个字符。处理方案是缩短字段长度或者改用前缀索引。数据包超过大小限制Packet for query is too large调整参数SET GLOBAL max_allowed_packet 67108864;max_allowed_packet默认是4MB或64MB版本不同有差异批量插入或大字段时很容易超。持久化同样写到my.cnf的[mysqld]段里。8. 实用工具组合与工作流建议8.1 各场景命令速查整合一下日常最常用的一套操作流程方便复制。环境准备阶段CentOS rpmrpm -Uvh mysql80-community-release-el7-3.noarch.rpm yum install mysql-community-server systemctl start mysqld grep temporary password /var/log/mysqld.log mysql -u root -p ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;连接登录阶段mysql -u root -p -h 127.0.0.1 -P 3306日常维护阶段SHOW PROCESSLIST; SHOW VARIABLES LIKE %character%; SHOW INDEX FROM user_info; EXPLAIN SELECT ...; SELECT * FROM information_schema.processlist WHERE command ! Sleep;备份阶段mysqldump -u root -p -h 127.0.0.1 --single-transaction mydb mydb_$(date %Y%m%d).sql8.2 我的三条经验和建议第一数据库命令不是背出来的是查出来的。真正重要的不是记住每条命令的语法而是知道遇到一类问题该去哪找解决方案。比如看到锁相关的错误优先打开SHOW PROCESSLIST和information_schema里的锁表看到慢查询先看slow log再对SQL做EXPLAIN。第二生产环境的所有变更操作都要有回退方案。改表结构前先备份表做数据更新前先跑一遍SELECT确认影响范围。我见过太多线上事故都是从一条没带WHERE的UPDATE、一次忘加索引的ALTER开始的。第三Linux命令行功底是真的重要。很多MySQL排查都依赖Linux基础命令比如用grep从错误日志里搜关键字、用netstat查端口占用、用df -h看磁盘空间——空间满了MySQL也会拒绝写入这类问题的排查速度快慢很看基础能力扎不扎实。mysql的常用命令手册写到这儿基本上把我日常用的、能想到的高频场景全覆盖了一遍。从安装启动、连接管理、库表操作、数据增删改查到索引锁事务、备份恢复和报错排查每一类都是实打实基于我用过的场景总结出来的。如果你正在搭建一套自己的数据库操作规范照着每一条命令去敲一遍、去试一遍跑通了之后把这些命令沉淀成自己的速查表以后遇到问题直接翻自己的笔记比从头搜索快得多。 MySQL常用命令速查手册：从安装、索引到事务优化全攻略
写这份手册的念头其实是我自己刚接触MySQL那会儿踩了太多坑攒出来的。当时看文档看不进去记命令记不住一碰到服务启动失败或者字符集乱码就上网东翻西找时间全耗在搜索里。后来靠一套自己的常用命令清单把日常大部分操作都固定在几条命令上慢慢才觉得心里有底。这里整理的是我自己长期在用的MySQL常用命令按场景拆开讲覆盖从安装到日常操作再到问题排查的完整链路。不管你是刚装好MySQL正被服务无法启动卡住的新手还是在写SQL、搞事务、调索引时想要一份速查的开发者这份手册都能直接拿去用。每个命令我都尽可能说清楚背后的逻辑以及我实际使用中遇到的坑。1. 安装、启动与基础环境配置1.1 MySQL版本怎么选很多人在下载安装那一步就开始纠结尤其是看到MySQL 5.7和8.0两个大版本不知道选哪个。我个人的建议是新项目直接用8.0老项目或者对稳定性要求极高、团队又没人熟悉8.0新特性的场景继续用5.7也没问题。8.0相比5.7有几个肉眼可见的变化默认字符集从latin1改成了utf8mb4不用再每次建库都手动指定字符集新增了窗口函数和公共表表达式CTE写复杂统计SQL会舒服很多另外身份认证插件默认是caching_sha2_password这个会直接影响你连数据库的方式后面我会专门讲。在Linux上我常用的下载方式是直接到官网拿tar包解压或者用rpm安装。如果你走rpm路线命令大概是这样的# 下载mysql community rpm包 wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm rpm -Uvh mysql80-community-release-el7-3.noarch.rpm yum install mysql-community-server解压安装方式用的命令组是groupadd mysql useradd -r -g mysql mysql tar -zxvf mysql-8.0.xx-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.xx /usr/local/mysql cd /usr/local/mysql mkdir data chown -R mysql:mysql /usr/local/mysql bin/mysqld --initialize --usermysql bin/mysqld_safe --usermysql 这里有一个非常关键的细节初始化时一定要留意日志里的临时密码。MySQL 8.0在初始化时不会像5.7那样默认让你直接空密码登录而是在日志文件里打印一行[Note] A temporary password is generated for rootlocalhost: xxxxxxxx我第一次装8.0的时候没注意这个临时密码初始化完怎么登都登不进去后来重新初始化一遍才看到。拿到临时密码后登录进去第一件事就是改密码ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;1.2 服务启动失败排查net start mysql 服务无法启动这几个字应该是被搜索最多的MySQL报错之一。触发原因很多但常见就那么几类我按概率排了一下第一类my.ini或my.cnf配置文件有问题。路径写错、参数值不合法、basedir或datadir指向不对都会导致服务起不来。排查方法很简单去MySQL的data目录下看错误日志文件名叫hostname.err最后几十行通常直接告诉你原因。第二类data目录权限不对。Linux上用rpm安装后如果data目录属主不是mysql用户服务一样起不来。解决办法chown -R mysql:mysql /var/lib/mysql/第三类端口或socket被占用。如果你之前装过MySQL实例没停干净或者机器上有MariaDB3306端口被占新服务自然起不来。用netstat -tlnp | grep 3306看一眼就知道。Windows上还有一个很隐蔽的问题服务路径指向了旧版本或错误目录。我见过有人装了两个版本的MySQL旧服务残留结果服务管理器里的MySQL指向的其实是旧的bin路径。这种直接去服务属性里改可执行文件路径或者删掉旧服务重新注册mysqld --remove mysqld --install再强调一次MySQL服务起不来永远是先看日志别瞎猜。日志文件里每一行报错都有明确的Error Code把日志贴到搜索引擎里比你自己盲试命令高效得多。1.3 Docker环境下安装MySQL现在很多开发环境直接用Docker跑MySQL省去本地安装的折腾。我常用的运行命令是docker run -d \ --name mysql \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ -e MYSQL_DATABASEtestdb \ -v /opt/mysql-data:/var/lib/mysql \ mysql:8.0这里有几个点必须注意。一个是-v目录挂载数据目录一定要挂出来否则容器删了数据就全没了。另一个是字符集虽然8.0默认utf8mb4但5.7镜像默认还是latin1建议启动时加上参数docker run -d \ --name mysql \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_unicode_ci \ -v /opt/mysql-data:/var/lib/mysql \ mysql:5.7docker方式装MySQL如果启动失败不要只看docker logs这个只能看到容器层的日志数据库自身的错误日志还是要进容器里看文件docker logs mysql docker exec -it mysql bash cat /var/log/mysql/error.log还有一个大家容易忽略的问题容器重启策略。生产环境尽量加上--restartalways否则服务器重启后MySQL容器不会自动拉起应用层依赖数据库的服务就全断了。2. 连接数据库与日常登录管理2.1 登录与连接状态查看命令行登录MySQL是最基础的操作但里面的门道也不少。mysql -u root -p mysql -u root -p -h 127.0.0.1 -P 3306 mysql -u root -p -h 192.168.1.10 -P 3306 --default-character-setutf8mb4注意-h后面如果写localhost走的是socket连接写127.0.0.1或真实IP走的是TCP连接。两者在某些场景下行为不一样比如localhost可以跳过某些host限制127.0.0.1则受mysql.user表里host字段控制。遇到Access denied for user rootlocalhost这类报错多半就是host不匹配导致的。连接上之后几个最常用的查看命令SELECT VERSION(); SELECT NOW(); SELECT DATABASE(); SELECT USER(); SHOW PROCESSLIST; SHOW VARIABLES LIKE %character%;SHOW PROCESSLIST这个命令我要单独说一下。它的作用是看当前所有数据库连接的线程状态。当数据库变慢、卡死、连接数飙高的时候第一件事就是执行它看看哪些查询在跑、跑了多久、有没有State列显示Locked或Sending data卡住。信息字段里最关键的是Time列如果超过几十秒基本可以判定为慢查询或者存在锁问题可能需要kill掉KILL thread_id;比如SHOW PROCESSLIST里看到一个id为123的查询已经跑了200秒直接KILL 123;就能把它杀掉。这个操作要谨慎生产环境杀掉正在跑的查询可能造成事务回滚但总比让数据库一直锁着强。2.2 用户与权限管理除了root用户实际项目里通常要单独建业务账号。因为root权限太大误操作或者被入侵的后果都很严重。建用户和授权的完整流程CREATE USER app_user% IDENTIFIED BY 密码; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_user%; GRANT ALL PRIVILEGES ON mydb.* TO app_user%; FLUSH PRIVILEGES;权限粒度要把握好。只读账号就只给SELECT写入账号再给INSERT、UPDATE、DELETEDBA级别的操作永远不要用业务账号干。FLUSH PRIVILEGES是用来刷新权限表的通常在直接修改mysql.user表之后才需要执行用GRANT授权的场景其实可以不执行但养成习惯也无妨。查看某个用户的权限SHOW GRANTS FOR app_user%;修改密码的三种姿势ALTER USER rootlocalhost IDENTIFIED BY 新密码; SET PASSWORD FOR rootlocalhost 新密码; -- 5.7及以下版本 SET PASSWORD PASSWORD(新密码);8.0不建议用PASSWORD()函数它已经被废弃了。如果客户端连不上8.0报错Authentication plugin caching_sha2_password cannot be loaded有两个解决思路一是更新客户端驱动让驱动支持新认证协议二是在MySQL里把用户的认证方式改回mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 密码;第二种是临时的新项目最好还是用新驱动适配新认证方式别一直迁就旧协议。2.3 SSL连接错误处理mysql ssl连接错误也是高频搜索词。MySQL 8.0默认开启了SSL连接有时候客户端工具没配好证书或者协议不匹配会报SSL相关错误。我遇到最多的场景是Java应用连MySQL 8.0时报javax.net.ssl.SSLHandshakeException。处理思路一般是三步走先看数据库端SSL状态然后确认驱动版本最后决定是完整配置SSL还是先关闭SSL穿透测试。查看SSL状态SHOW VARIABLES LIKE %ssl%;如果返回have_ssl为YES说明服务端开了SSL。驱动版本太旧不支持新的SSL协议就会握手失败这种情况升级驱动最干净。如果测试阶段不想折腾SSL可以在连接串上加参数绕过jdbc:mysql://127.0.0.1:3306/testdb?useSSLfalseserverTimezoneAsia/Shanghai注意useSSLfalse只建议在开发或内网环境使用公网传输的数据链路一旦被截获明文数据就全裸奔了。生产环境该配证书还是得配别图省事把安全底线丢了。3. 数据库与表结构操作命令3.1 创建数据库与表创建库最完整的写法CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;utf8mb4能存emoji和生僻字而utf8在MySQL里实际上是utf8mb3根本存不了四字节字符。从5.5版本开始就有utf8mb4了但很多人还是惯性用utf8导致后面往表里插emoji时报错Incorrect string value。从5.7开始官方推荐utf8mb48.0更是直接默认所以建库建表时把字符集显式写清楚省得后续一堆乱码麻烦。查看字符集相关命令SHOW CREATE DATABASE mydb; SHOW VARIABLES LIKE character_set_database;建表命令的完整优点示例CREATE TABLE IF NOT EXISTS user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) NOT NULL COMMENT 用户名, age INT DEFAULT 0 COMMENT 年龄, email VARCHAR(128) DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, UNIQUE KEY uk_username (username), KEY idx_age (age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户信息表;这几个字段设计上的细节值得说道说道。id用BIGINT AUTO_INCREMENT做主键是因为在实际业务里INT的范围只有21亿左右看似很大但流水表很快就可能不够用。created_at和updated_at直接让数据库维护时间戳应用层不用每次插入都传时间。ON UPDATE CURRENT_TIMESTAMP会在记录更新时自动刷新时间这个特性在做数据审计、排查脏数据时特别好用能知道这条记录最后是什么时候被动过的。查看表结构DESC user_info; SHOW CREATE TABLE user_info;DESC给出的是精简的字段列表SHOW CREATE TABLE给出的是完整的建表语句包括索引、约束、字符集等细节。修改字段或者排查建表问题时后者更有用。3.2 修改表结构的常用命令开发迭代过程中修改表结构是最频繁的操作之一。这一组命令我基本每周都会用到-- 添加字段 ALTER TABLE user_info ADD COLUMN address VARCHAR(255) DEFAULT NULL COMMENT 地址 AFTER email; -- 修改字段类型或属性 ALTER TABLE user_info MODIFY COLUMN age INT NOT NULL DEFAULT 0; -- 修改字段名和定义 ALTER TABLE user_info CHANGE COLUMN email contact_email VARCHAR(128) DEFAULT NULL; -- 删除字段 ALTER TABLE user_info DROP COLUMN address; -- 修改表名 RENAME TABLE user_info TO user_detail;MODIFY和CHANGE的区别很多人分不清。MODIFY只改字段的属性类型、默认值不改变字段名CHANGE可以同时改字段名和属性所以CHANGE后面要写两遍字段名——一遍旧名、一遍新名。如果你只是想改类型却用CHANGE第二个名字重复写一遍旧名也能实现但多了一个出错的可能所以规则是改类型用MODIFY改字段名用CHANGE。“mysql设置默认值为0”这个搜索词对应的就是这类操作的需求。比如新增一个状态字段默认值要设为0ALTER TABLE user_info ADD COLUMN status TINYINT NOT NULL DEFAULT 0 COMMENT 状态 0-正常 1-禁用;有些同学写SQL时忘了NOT NULL和DEFAULT结果字段允许NULL、默认值也是NULL业务代码拿到数据后还要多做一层空值判断凭空增加很多麻烦。设计字段时把NULL和空串区分开通常业务状态字段用NOT NULL DEFAULT 0不需要有意义的字段才允许NULL。还有一个容易忽略的细节大表加字段时要注意锁表问题。MySQL 5.6之后ALTER TABLE里的很多操作比如加字段使用了Online DDL但不是所有操作都完全在线有些场景还是会短暂锁表。在几百万行的大表上做结构变更最好安排在业务低峰期并且先用一个小表测试一下耗时心里有数再操作。3.3 存储过程的简单使用“mysql存储过程”也是经常搜的词。存储过程就是把一段SQL逻辑存到数据库里让应用层调用。我不建议把复杂业务逻辑都写进存储过程因为后期维护是真的痛苦但有些固定批处理任务比如按月清理历史数据用存储过程还是可以的。一个简单的存储过程示例DELIMITER $$ CREATE PROCEDURE sp_cleanup_old_logs(IN days INT) BEGIN DELETE FROM operation_log WHERE created_at DATE_SUB(NOW(), INTERVAL days DAY); END$$ DELIMITER ; CALL sp_cleanup_old_logs(30); DROP PROCEDURE IF EXISTS sp_cleanup_old_logs;注意DELIMITER这个命令。MySQL默认用分号作为语句结束符而存储过程内部有多个分号如果不用DELIMITER改掉结束符客户端会在写第一行的时候就判定语句结束导致整个存储过程无法完整提交。写完存储过程再用DELIMITER ;把它改回来这一步千万别忘。4. 数据操作增删改查与排序分页4.1 INSERT、UPDATE、DELETE的正确姿势数据操作算是DBA和开发的高频动作了但很多线上事故恰恰出在最基础的增删改查上。插入数据的常见写法INSERT INTO user_info (username, age, email) VALUES (zhangsan, 25, zsexample.com); INSERT INTO user_info (username, age, email) VALUES (lisi, 30, lisiexample.com), (wangwu, 28, wangwuexample.com); INSERT INTO user_info (username, age, email) SELECT username, age, email FROM temp_user WHERE age 18;还有一种在数据迁移时很好用的写法是INSERT ... ON DUPLICATE KEY UPDATE遇到主键或唯一键冲突就更新不冲突就插入INSERT INTO user_info (id, username, age) VALUES (1, zhangsan, 26) ON DUPLICATE KEY UPDATE username VALUES(username), age VALUES(age);MySQL 8.0.20之后VALUES()在ON DUPLICATE KEY UPDATE里有更新写法了建议用AS语法。但常规版本里上面这种写法还能用新版本里推荐INSERT INTO t1 (a, b) VALUES (1, 2) AS new ON DUPLICATE KEY UPDATE b new.b;UPDATE的一个大坑是忘记带WHERE条件。执行前一定要自问我是不是要更新全表如果不是必须写WHEREUPDATE user_info SET age 26 WHERE username zhangsan;DELETE同理DELETE FROM user_info WHERE id 100;在生产环境修改或删除前有条件的我会先跑一遍SELECT确认影响行数SELECT COUNT(*) FROM user_info WHERE username zhangsan;确认只有一条或预期条数再执行UPDATE或DELETE。这条习惯帮我挡了好几次出大事的机会强烈建议你也养成。再补一个“mysql update 还原”相关的场景。有时UPDATE操作失误把字段值改错了想还原。如果你的数据库有备份用备份恢复即可如果没有备份MySQL本身有binlog可以通过binlog把操作反转但操作难度不小不是一条命令就能解决的。日常我建议用两个土办法兜底一个是执行UPDATE前先把受影响的行备份到一张临时表CREATE TABLE user_info_bak_20250101 AS SELECT * FROM user_info WHERE id 100;另一个是开启事务错了直接回滚START TRANSACTION; UPDATE user_info SET age 26 WHERE id 100; -- 检查结果 SELECT * FROM user_info WHERE id 100; -- 不对就回滚 ROLLBACK; -- 对了就提交 COMMIT;4.2 SELECT查询、排序、分组与分页查询语句是MySQL使用频率最高的命令门类看起来人人会写但很多细节值得打磨。基础查询SELECT id, username, age FROM user_info WHERE age 18 AND age 30; SELECT id, username, age FROM user_info WHERE age BETWEEN 18 AND 30; SELECT id, username, age FROM user_info WHERE username IN (zhangsan, lisi); SELECT id, username, age FROM user_info WHERE username LIKE zhang%;LIKE查询有一个性能注意点%写在开头比如%zhang会导致索引失效因为无法利用B树的顺序查找特性。数据量大时这种查询就是全表扫描尽量改写。“mysql排序”是个热门搜索排序的完整语法SELECT id, username, age FROM user_info ORDER BY age DESC, id ASC;ORDER BY可以按多个字段级联排序第一个字段相同才按第二个字段排。默认是ASC升序。注意排序字段最好有索引否则大数据量下ORDER BY会走filesort性能会明显下降。在联合索引的场景下排序字段要匹配索引字段顺序否则优化器也不会用索引排序。分组统计SELECT age, COUNT(*) AS cnt FROM user_info GROUP BY age HAVING cnt 1;WHERE和HAVING的分工要记清楚WHERE是分组前过滤行HAVING是分组后过滤组。先按条件把行缩小范围再分组效率更高能写进WHERE的条件尽量别放HAVING。分页查询SELECT * FROM user_info ORDER BY id LIMIT 20, 20; SELECT * FROM user_info ORDER BY id LIMIT 20 OFFSET 20;两种写法等价LIMIT 20, 20表示跳过20条取20条LIMIT 20 OFFSET 20表示偏移20条取20条。对于深分页比如跳过了1万条MySQL需要扫描前面所有行才能定位到目标偏移量性能很差。碰到这种场景可以用游标式分页SELECT * FROM user_info WHERE id 10000 ORDER BY id LIMIT 20;记住上次最大id下次查询直接带上就能让每次查询都命中索引范围扫描这是大表分页的通用优化思路。去重查询用DISTINCTSELECT DISTINCT age FROM user_info;排查重复数据用的分组聚合SELECT age, COUNT(*) FROM user_info GROUP BY age HAVING COUNT(*) 1;4.3 事务的开启与管理事务是MySQL保证数据一致性的核心机制。InnoDB支持事务MyISAM不支持这也是为什么现在都默认用InnoDB。事务的基本流程START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果第二步更新失败可以整体回滚ROLLBACK;事务的四个隔离级别默认是REPEATABLE READ可重复读-- 查看当前隔离级别 SELECT transaction_isolation; -- 8.0之前的版本查这个 SELECT tx_isolation; -- 设置隔离级别会话级 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;讲个实际场景。你在事务里先查询了一条记录然后别的连接修改并提交了这条记录你再次查询时在REPEATABLE READ级别下看到的数据还是原来的——这就是可重复读的语义。如果业务上需要能看到别人刚提交的数据可能要降级到READ COMMITTED。但是这只是一个权衡因为降低隔离级别会引入不可重复读和幻读问题。正常情况下保持默认就好非必要不改。事务里还有一个隐患是“长事务”。一个事务长时间不提交会持有锁影响其他连接还会导致undo log膨胀。所以事务里面别做耗时太长的外部调用比如HTTP请求开事务前先想清楚处理完立刻提交。5. 索引、锁与性能优化命令5.1 索引的创建与查看“mysql创建索引”、“mysql锁的分类”、“mysql面试题”这些热搜词说明了索引有多重要面试会问实际开发中也经常要调。创建索引的命令-- 普通索引 CREATE INDEX idx_age ON user_info(age); -- 唯一索引 CREATE UNIQUE INDEX uk_email ON user_info(email); -- 联合索引 CREATE INDEX idx_age_name ON user_info(age, username); -- 在创建表时指定索引 -- 见前文的建表示例中的 KEY idx_age (age) -- 删除索引 DROP INDEX idx_age ON user_info;查看表的索引SHOW INDEX FROM user_info;建立联合索引时最核心的原则是把区分度高的字段放前面。比如idx_age_name(age, username)如果age字段只有几个取值比如年龄段就那么几个区分度不高放前面可能不如把username放前面。但也要看实际查询条件最准确的方法是结合查询语句看WHERE里哪些字段能等值匹配然后通过EXPLAIN验证。索引不是越多越好。每多一个索引写入时就要多维护一颗B树插入删除的速度都会受影响。我见过有人为了性能给表加了十几个索引结果写入慢到卡顿完全得不偿失。索引建在查询频繁的字段上而不是所有字段上这句话值得抄在工位上。还有一个容易被忽略的点TEXT、BLOB等大字段没法直接加完整索引只能建前缀索引CREATE INDEX idx_email_prefix ON user_info(email(20));意思是取email字段前20个字符建索引。前缀长度要平衡索引大小和区分度取太短可能区分度不够取太长索引就失去意义了一般不算太难的判断。5.2 EXPLAIN查看执行计划分析SQL跑得慢的利器是EXPLAIN这也是我排查慢查询的第一板斧。EXPLAIN SELECT id, username FROM user_info WHERE age 25 ORDER BY id;执行后输出一张表核心列含义如下type访问类型从上到下性能从好到坏依次是systemconsteq_refrefrangeindexALL。看到ALL基本意味着全表扫描要警惕。key实际用到的索引。rows预估扫描的行数数值越小越好。Extra如果出现Using filesort或Using temporary说明查询可能需要优化比如排序字段无索引、或者分组操作无法用索引完成。一个实际例子。查询语句SELECT * FROM user_info WHERE age 25 ORDER BY created_at;在只有idx_age的情况下EXPLAIN结果里可能会出现Using filesort因为排序字段created_at没在索引中。优化方案是把索引改成联合索引CREATE INDEX idx_age_created ON user_info(age, created_at);这样既能用索引定位age 25的行又能按索引顺序直接读取created_atfilesort就消失了。这种优化思路在面试和日常调优里都很常见。5.3 锁的分类与处理mysql锁的分类也是搜索高频词。MySQL的锁可以简单按粒度分表级锁和行级锁。表级锁用LOCK TABLES显式加锁LOCK TABLES user_info READ; -- 此时其他会话可以读但不能写 UNLOCK TABLES;行级锁由InnoDB在事务中自动管理。共享锁S锁用LOCK IN SHARE MODE排他锁用FOR UPDATESTART TRANSACTION; SELECT * FROM user_info WHERE id 100 LOCK IN SHARE MODE; -- 其他事务可以读同一行但不能修改 START TRANSACTION; SELECT * FROM user_info WHERE id 100 FOR UPDATE; -- 其他事务对该行的读写都会被阻塞FOR UPDATE常用于乐观锁/悲观锁场景。比如电商秒杀扣库存时先SELECT ... FOR UPDATE锁定库存行再进行扣减操作能避免超卖。如果两个事务互相持有对方需要的锁就可能产生死锁InnoDB会自动检测并回滚其中一个事务让另一个执行成功。生产环境遇到死锁报错Deadlock found when trying to get lock处理方式是先把事务拆短减少锁的持有时间多个事务访问多张表时尽量用一致的顺序访问降低隔离级别也能减少某些锁竞争。锁问题排查手段-- 查看当前事务和锁等待状态 SELECT * FROM information_schema.INNODB_TRX; SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 或者新版使用 SELECT * FROM performance_schema.data_locks;查出事务ID后如果确定某条事务卡死了可以杀掉它KILL trx_mysql_thread_id;八成线上“数据库卡住”都是长事务持锁不释放导致的按上面三步基本能定位到元凶。6. 数据备份、导入导出与迁移6.1 mysqldump备份备份这一步千万别省。我用得最顺手的备份工具就是mysqldump。# 备份单个数据库 mysqldump -u root -p mydb mydb.sql # 备份多个数据库 mysqldump -u root -p --databases mydb1 mydb2 multi_db.sql # 备份所有数据库 mysqldump -u root -p --all-databases all_db.sql # 只备份表结构不备份数据 mysqldump -u root -p --no-data mydb schema.sql # 只备份数据不备份表结构 mysqldump -u root -p --no-create-info mydb data.sql实际操作中备份大库时网络中断会导致备份文件损坏所以我的备份脚本里会加上--single-transaction和--quick参数这两个对InnoDB尤为重要可以在不锁表的情况下完成一致性备份mysqldump -u root -p --single-transaction --quick mydb mydb.sql--single-transaction利用InnoDB的事务特性在快照读的基础上备份不影响业务读写。5.7和8.0下直接用。如果备份的是MyISAM表得另行加锁但现在基本没有这个需求了。恢复数据的操作mysql -u root -p mydb mydb.sql如果SQL文件比较大比如上G直接在终端里恢复容易中途断掉也看不到进度。可以先进入mysql命令行然后SOURCE执行USE mydb; SOURCE /data/backup/mydb.sql;SOURCE的好处是用一个会话连续执行出错能看到具体行号方便定位问题。但SOURCE不会自动跳过错误默认遇到错误就继续往下执行所以如果备份文件里有几条没导入干净建议导完以后统计一下表行数和备份前对一下。6.2 导入导出CSV与其他迁移方式很多场景需要把MySQL数据导出给其他系统或者从文件导入数据。CSV是最通用的格式。导出CSVSELECT id, username, age INTO OUTFILE /tmp/user_info.csv FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n FROM user_info;注意MySQL 8.0默认禁止SELECT ... INTO OUTFILE导出到任意路径会报错The MySQL server is running with the --secure-file-priv option。解决办法是查看允许的导出目录SHOW VARIABLES LIKE secure_file_priv;把导出文件放到该目录下即可。或者也可以不经过CSV直接通过客户端工具导出到本地。从CSV导入MySQLLOAD DATA INFILE /tmp/user_info.csv INTO TABLE user_info FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS;IGNORE 1 ROWS是跳过第一行表头。如果CSV文件里某些字段是空值可以用SET子句转换LOAD DATA INFILE /tmp/user_info.csv INTO TABLE user_info FIELDS TERMINATED BY , IGNORE 1 ROWS (age, email, created) SET created_at IF(created , NOW(), created);注意导入大文件之前先看看目标表和CSV字段的数量、顺序是否完全对得上。字段差一位整个表的数据就可能错位。我的做法是先导入小样本比如前100行验证再导全量。还有一条经常被搜索的“使用flink实现mysql同步到clickhouse”属于数据实时同步体系。核心原理是开启MySQL的binlog通过Flink CDC组件捕获binlog变更事件写入ClickHouse。命令层面需要确认MySQL开启了binlogSHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE binlog_format;binlog_format建议是ROW格式因为Flink CDC解析的就是行级变更。这个体系搭起来比导CSV复杂一个数量级适合实时数仓场景但如果只是定期同步简单粗暴的mysqldump 导入反而更稳。7. 高频报错与排查方法汇总7.1 升级与初始化报错my-014060热搜词里有[error] [my-014060] [server] invalid mysql server upgrade。这个报错通常出现在MySQL进行版本升级时服务器检测到数据目录的版本信息异常。我遇到过的情况是从5.7直接跳到8.0或者数据目录拷贝后没清理干净导致系统认为需要升级但数据字典又不匹配直接崩了。解决办法分两步第一步确认数据目录下有一个叫mysql.ibd或ibdata1的文件这是系统表空间别动它。然后查看数据目录的版本信息SHOW VARIABLES LIKE innodb_version;第二步严格按照官方升级路径来5.7要先升级到5.7最新版再升8.0不能直接跨大版本跳。升级前先备份整个数据目录然后用mysqld --initialize重新初始化一个干净数据目录加载备份而不是把旧目录硬指过去。我个人的建议是升级数据库这种事情不做最好要做就必须有完整预案。先在一个测试环境模拟整个升级过程确认所有存储过程、函数、SQL语法在8.0下都能正常跑再动生产。7.2 字符集乱码问题乱码问题在MySQL里几乎人人遇到。本质原因就是客户端、连接、服务端、表、字段的字符集不一致。系统性排查字符集的方法SHOW VARIABLES LIKE character_set_client; SHOW VARIABLES LIKE character_set_connection; SHOW VARIABLES LIKE character_set_results;修改当前会话的字符集SET NAMES utf8mb4;SET NAMES utf8mb4等价于同时把character_set_client、character_set_connection、character_set_results三个变量都设成utf8mb4是最快的临时解决办法。永久解决靠配置文件。Linux下是/etc/my.cnfWindows下是my.ini加这一段[client] default-character-setutf8mb4 [mysql] default-character-setutf8mb4 [mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci改完之后重启MySQL再用SHOW VARIABLES LIKE character%确认全部变成utf8mb4。如果已经建好的表是latin1或有乱码历史数据需要转换表字符集ALTER TABLE user_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这条命令会重写整张表数据量大时耗时长且可能锁表建议低峰期执行。7.3 连接数耗尽与慢查询连接数耗尽的一个常见报错是Too many connections排查和解决思路-- 查看最大连接数和当前连接数 SHOW VARIABLES LIKE max_connections; SHOW STATUS LIKE Threads_connected;如果当前连接数接近max_connections说明连接池配置过大或者有慢查询占住连接不释放。临时调大连接数SET GLOBAL max_connections 500;但临时调大是治标不治本。更根本的思路是找到占用连接的会话看它们都在干什么SELECT id, user, host, db, time, state, info FROM information_schema.processlist WHERE command ! Sleep;把time很大的查询逐条分析加索引或者改SQL。如果应用服务的连接池配置过大比如Spring Boot的hikari最大连接数设置成100但数据库最大连接数只有150也需要调小应用连接池。慢查询日志的开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;这些配置线上最好通过my.cnf持久化slow_query_log 1 long_query_time 1 slow_query_log_file /var/log/mysql/slow.log开启后会记录所有超过1秒的SQL定期看这个文件就能把潜在的慢SQL揪出来逐一优化。注意long_query_time单位是秒设置为1意味着执行1秒以上都记录下来。对于大部分业务系统建议设成0.5甚至更小抓得更准。7.4 其他我见过的高频错误数据库字符处理错误Incorrect string value: \xF0\x9F... for column原因几乎都是utf8mb3无法存储emoji。核对一下库表字符集是不是utf8mb4。You are not allowed to use the\xF0\x9F...literal.如下是把上述报错转述成普通说法原因是字符集不对建表时用了utf8而不是utf8mb4。索引长度超限Specified key was too long; max key length is 3072 bytesInnoDB索引最大长度是3072字节utf8mb4一个字符最多占4个字节也就是说索引字段最多768个字符。处理方案是缩短字段长度或者改用前缀索引。数据包超过大小限制Packet for query is too large调整参数SET GLOBAL max_allowed_packet 67108864;max_allowed_packet默认是4MB或64MB版本不同有差异批量插入或大字段时很容易超。持久化同样写到my.cnf的[mysqld]段里。8. 实用工具组合与工作流建议8.1 各场景命令速查整合一下日常最常用的一套操作流程方便复制。环境准备阶段CentOS rpmrpm -Uvh mysql80-community-release-el7-3.noarch.rpm yum install mysql-community-server systemctl start mysqld grep temporary password /var/log/mysqld.log mysql -u root -p ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;连接登录阶段mysql -u root -p -h 127.0.0.1 -P 3306日常维护阶段SHOW PROCESSLIST; SHOW VARIABLES LIKE %character%; SHOW INDEX FROM user_info; EXPLAIN SELECT ...; SELECT * FROM information_schema.processlist WHERE command ! Sleep;备份阶段mysqldump -u root -p -h 127.0.0.1 --single-transaction mydb mydb_$(date %Y%m%d).sql8.2 我的三条经验和建议第一数据库命令不是背出来的是查出来的。真正重要的不是记住每条命令的语法而是知道遇到一类问题该去哪找解决方案。比如看到锁相关的错误优先打开SHOW PROCESSLIST和information_schema里的锁表看到慢查询先看slow log再对SQL做EXPLAIN。第二生产环境的所有变更操作都要有回退方案。改表结构前先备份表做数据更新前先跑一遍SELECT确认影响范围。我见过太多线上事故都是从一条没带WHERE的UPDATE、一次忘加索引的ALTER开始的。第三Linux命令行功底是真的重要。很多MySQL排查都依赖Linux基础命令比如用grep从错误日志里搜关键字、用netstat查端口占用、用df -h看磁盘空间——空间满了MySQL也会拒绝写入这类问题的排查速度快慢很看基础能力扎不扎实。mysql的常用命令手册写到这儿基本上把我日常用的、能想到的高频场景全覆盖了一遍。从安装启动、连接管理、库表操作、数据增删改查到索引锁事务、备份恢复和报错排查每一类都是实打实基于我用过的场景总结出来的。如果你正在搭建一套自己的数据库操作规范照着每一条命令去敲一遍、去试一遍跑通了之后把这些命令沉淀成自己的速查表以后遇到问题直接翻自己的笔记比从头搜索快得多。