1. 库操作之前先把概念理清楚很多刚接触 MySQL 的朋友上来就敲CREATE DATABASE敲完之后一脸满足觉得自己会建库了。但真正在线上环境里跑过几年的人都知道库操作这块水其实挺深的。你建库时的字符集选错、排序规则选错后面表建好了才发现乱码、排序不对那时候再回头改库成本远比你想象中大得多。先把这个最基础的概念摆正MySQL 里的“库”到底是什么你可以把它理解成一个文件柜表是文件柜里的文件夹索引和数据是文件夹里的资料。建库本身并不是一个高频操作但它决定了后续所有表、索引、视图、存储过程的家安在哪里、用什么规则来运行。这个“规则”就是字符集和排序规则很多人忽略掉这一步后面全是坑。这篇文章不扯虚的直接围绕 MySQL 库操作里你一定会用到的那些命令和场景把原理、实操、坑点一次性讲透。无论你是刚入门的新手还是写过几年 SQL 的老手只要你需要在 MySQL 上建库、改库、删库、备份库这篇都值得花几分钟过一遍。需要说明的是我这里所有操作都以常用的 MySQL 5.7 和 8.0 版本为准个别命令在低版本上略有差异我会特别标注出来。2. 库的创建字符集和排序规则是第一道分水岭2.1 一条完整建库语句应该长什么样最基本的一句CREATE DATABASE mydb;这句在本地测试环境跑完全没问题但放到生产环境我几乎不会这么写。为什么因为这条语句完全使用了 MySQL 的默认配置而默认配置大概率不是你想要的。完整且推荐的写法是这样CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;注意8.0 版本默认字符集已经是 utf8mb4默认排序规则是utf8mb4_0900_ai_ci而 5.7 版本即使你写了 utf8mb4默认的排序规则往往是utf8mb4_general_ci。这两个排序规则有什么差别主要是对某些特殊字符、中文拼音排序的处理方式不一样0900_ai_ci是 8.0 引入的 Unicode 9.0 标准实现更强但只适用于 8.0。如果你用的是 5.7建议统一写成CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;2.2 字符集选错了会怎样我记得早年接手过一个老项目数据库用的是latin1表里存的中文全是问号。当时排查了半天最后发现是建库时图省事直接按了默认而默认字符集是latin1。中文存进去出来就是???再想转回来基本等于重写数据。所以这里给出一句话经验新库一律用 utf8mb4。别再用 utf8utf8 在 MySQL 里实际上是 utf8mb3最多存 3 个字节像某些 emoji 表情、生僻字根本存不进去存进去就是报错或变成乱码。utf8mb4 是完整的 4 字节 UTF-8 实现是当前唯一推荐的中文场景字符集方案。2.3 加一个 IF NOT EXISTS 减少报错你可能会在脚本里反复执行建库语句比如初始化脚本跑了两次第二次直接报ERROR 1007 (HY000): Cant create database mydb; database exists。这时候只要加上IF NOT EXISTS就稳了CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;这条语句在幂等性要求高的场景下非常好用脚本重复执行不会中断。3. 查看与切换库每个 DBA 的日常动作3.1 查看当前有哪些库SHOW DATABASES;输出结果会列出所有你有权限访问的库。注意这条命令显示的不只是你自己建的库还包括系统自带的库比如information_schema、performance_schema、mysql、sys8.0 新增。这些系统库千万别去动尤其是mysql库里面存的是用户权限信息手一抖删错表可能整个实例都连不上了。这里有个实用小技巧如果你只想看名字带某个关键词的库可以这样SHOW DATABASES LIKE %test%;用通配符过滤输出更清爽。3.2 查看当前所在的库SELECT DATABASE();输出当前会话默认使用的库名。如果返回NULL说明你当前还没有选定任何库。新手常常搞混USE和这个命令的关系USE是切换SELECT DATABASE()只是查看。切换库USE mydb;这一个命令执行后当前会话的默认库就变成mydb。但要注意USE只对当前会话生效你退出连接再进来默认库会回到空需要重新USE。如果你希望每次连接都自动进入某个库可以在连接时指定mysql -u root -p mydb这样连接后默认就在mydb里省一次USE。3.3 查看建库语句有时候你想知道某个库当初到底是怎么建的用了什么字符集、什么排序规则直接执行SHOW CREATE DATABASE mydb\G输出类似这样Database: mydb Create Database: CREATE DATABASE mydb /*!40100 DEFAULT CHARACTER SET utf8mb4 */ /*!80016 DEFAULT ENCRYPTIONN */这里两个/*!注释片段很有意思/*!40100表示 MySQL 4.01.00 及以上版本会执行其中的内容而/*!80016是 8.0.16 及以上版本才支持的加密选项。这类注释式语法在导出、迁移场景下很常见看到不要奇怪。4. 修改库属性字符集、排序规则与库名4.1 修改字符集和排序规则建库之后发现字符集选错了不用删库重建直接改属性即可ALTER DATABASE mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;这里有一个重要提示ALTER DATABASE 里的 DEFAULT 关键字只影响后续新建的表不影响库里已有的表。什么意思就是说你执行完这条语句后老表的字符集还是原来的新表才会用新的字符集。如果想让已有表也改过来必须对每张表单独执行ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里有个超级大的坑CONVERT TO会重写整张表的数据如果表有几百万甚至上千万行执行期间会有锁表业务会短暂不可写。我曾在线上吃过这个亏一张 500 万行的表执行转换跑了两分钟期间所有写请求全部阻塞。所以生产环境做这种操作一定要选业务低峰期并且提前评估表大小和时间成本。4.2 修改库名没有直接改名的命令这是 MySQL 一个比较反直觉的点它没有ALTER DATABASE ... RENAME TO ...这样的语法。想改库名通常的思路是用 mysqldump 导出整个库新建目标库名的库导入数据删除旧库。这三步听起来简单但当你库里有几十张表、数据量几十个 GB 时每一步都耗时很长。而且这个过程中如果业务还在写入先备份后删库的操作会有数据丢失风险。所以我的建议是库名一旦定下来尽量不要改。如果实在要改优先考虑用上层的连接配置或路由来兼容而不是直接改库名。在 5.7 以下的某些版本有RENAME DATABASE语法但在 MySQL 5.7 开始已经彻底移除。如果你看到网上老教程还在写RENAME DATABASE直接无视那是在 5.1 时代的写法早就废了。4.3 修改库的默认加密属性8.0 新增MySQL 8.0 引入了表空间加密功能建库时可以指定默认加密CREATE DATABASE mydb DEFAULT ENCRYPTIONY;如果你后来想改ALTER DATABASE mydb DEFAULT ENCRYPTIONN;注意同样只影响之后新建的表。生产环境如果要开启加密要先确认硬件性能可接受加密会引入一定 CPU 开销。5. 删除库一条命令引发的血案5.1 标准删库命令DROP DATABASE mydb;就这么简单执行完库没了里面的表、数据、存储过程、视图全都没了。没有任何回收站概念没有 undo 的机会。5.2 删库前三件事我见过不少人删库删得干净利落然后回头找我要数据。为了不让自己后悔删库前请强制自己按这个顺序走一遍确认这个库是否真的不再需要了跟业务方确认别自己拍板。至少做一次完整备份mysqldump或物理备份都行备份文件要放到其他机器或异地存储。写清楚删库时间和原因保留操作审计记录方便后续追溯。备份命令示例mysqldump -u root -p --single-transaction --routines --events --triggers mydb mydb_backup_$(date %Y%m%d).sql这个--single-transaction在 InnoDB 引擎下可以保证备份期间数据一致性不加的话备份过程中如果有写入备份出来的数据可能是某个中间状态恢复后会出现不一致。--routines、--events、--triggers分别对应存储过程、定时事件、触发器不加的话这些对象不会被备份出来恢复后你会发现少了很多东西。5.3 DROP DATABASE 与 DROP TABLE 的区别DROP DATABASE删除整个库及其所有对象。DROP TABLE只删一张表。如果你只想清理某个库里的所有表但不删库MySQL 没有内置的DROP ALL TABLES命令。你可以用脚本生成批量删除语句但更稳妥的做法是建一个新库把所有需要保留的表迁移过去然后把旧库整体删掉。另外执行DROP DATABASE不会自动删除磁盘上的 ibd 文件碎片实际上 InnoDB 在删除库时会释放相关表空间但如果是共享表空间模式物理文件大小不会立即缩小。所以你会发现删完库后磁盘占用没有立刻降下来这是正常现象不是没删干净。需要收缩的话得重建表空间或使用OPTIMIZE TABLE这又是另一个大工程了。6. 权限视角下的库操作建库不等于能用库6.1 为什么你建了库却看不到有时候你用 A 账号建了库切到 B 账号执行SHOW DATABASES发现根本看不到这个库。很多人第一反应是“没同步”“出 bug 了”其实不是这就是权限隔离。MySQL 的库操作与用户权限是绑定的。B 账号没有这个库的任何权限MySQL 就不会把它显示出来。你需要用 root 或具有全局权限的账号给 B 授权GRANT ALL PRIVILEGES ON mydb.* TO appuser%; FLUSH PRIVILEGES;这里的mydb.*表示库内的所有对象。授权后 B 账号才能看到并操作这个库。6.2 最小权限原则生产环境千万不要把ALL PRIVILEGES到处撒。常见的做法是只给业务账号它需要的那几个权限DML 权限SELECT、INSERT、UPDATE、DELETEDDL 权限CREATE、ALTER、DROP只在迁移或初始化阶段才给管理权限INDEX、REFERENCES、TRIGGER按需分配。一个比较稳妥的初始授权方案GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO appuser%;把 DDL 权限留给专门的维护账号避免业务代码里有人误执行 DROP 或 ALTER这种事我在实际运维中见过不止一次某个同事在业务代码里写错了一条 SQL把一张表给 ALTER 了差点导致线上事故。6.3 查看某个库的权限想知道某个用户对某个库有哪些权限可以执行SHOW GRANTS FOR appuser%;也可以查看当前用户权限SHOW GRANTS FOR CURRENT_USER();这个命令在排查“为什么能建表但不能删表”这类问题时非常有用。相比连接上去一个个试直接看 grants 输出一目了然。7. 库的备份与恢复别再只会 mysqldump7.1 逻辑备份与物理备份怎么选很多新手从接触 MySQL 开始就被教了一个命令mysqldump。这个命令本身没错但它解决的是逻辑备份场景也就是把数据以 SQL 形式导出来。对于 GB 级以上的数据逻辑备份速度会明显下降恢复起来也慢。这时候就需要物理备份工具了最常用的是 Percona XtraBackup。它直接拷贝 InnoDB 数据文件备份速度比 mysqldump 快一个量级尤其适合大库。简单对比场景推荐方案理由小库几百 MB 内mysqldump操作简单恢复直观中大型库GB 级以上XtraBackup 物理备份备份快、恢复快跨版本迁移mysqldump避免数据文件不兼容问题日常全量增量XtraBackup binlog恢复点可以做到分钟级跨版本迁移这点要特别强调MySQL 5.7 的数据文件直接拷到 8.0 实例里是无法识别的必须用逻辑方式导出再导入或者用升级工具走官方迁移流程。7.2 库级别备份命令如果你只备份某一个库不备份整个实例mysqldump 用法mysqldump -u root -p --single-transaction --databases mydb mydb.sql注意这里用--databases它会在备份文件里自动带上CREATE DATABASE和USE语句恢复的时候不需要提前手工建库。如果你不加--databases只是mysqldump -u root -p --single-transaction mydb mydb.sql那么这个备份文件里没有建库语句恢复前你必须手动先建好库再导入。两种用法的区别很关键别搞混。7.3 库的恢复操作针对带--databases的备份mysql -u root -p mydb.sql针对不带--databases的备份需要先建库再导入CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;mysql -u root -p mydb mydb.sql恢复时有个常见坑原库的字符集是 utf8mb4但新库建成了默认字符集 latin1导入后中文全变乱码。恢复前一定先确认目标库的字符集不要全指望备份文件里的SET NAMES语句它不会改变目标库的默认字符集。7.4 定时备份的简单思路生产环境如果还没上专业备份工具至少可以用 crontab 跑一个简单逻辑备份0 2 * * * mysqldump -u backup -pyourpass --single-transaction --routines --events --triggers --all-databases /backup/all_$(date \%Y\%m\%d).sql 2 /backup/backup.log然后配合 find 命令清理 7 天前的备份find /backup -name *.sql -mtime 7 -delete这套方案在小型项目里够用但要注意备份文件别放在和数据库同一块磁盘上否则磁盘满了会连带数据库出问题。8. 库操作中的锁与元数据问题8.1 ALTER DATABASE 会锁库吗这是一个很多人忽略的问题。ALTER DATABASE修改字符集、排序规则本质上修改的是数据字典里的元数据不是数据本身。在 MySQL 8.0 中这类 DDL 操作会获取元数据锁MDL。如果当前有其他会话正在执行长事务ALTER DATABASE可能会等待表现为语句卡住不动。遇到这种“卡住”的情况不要急着杀进程先查一下是谁在持有锁SHOW PROCESSLIST;找到State是Waiting for table metadata lock的会话再找到持有锁的源头通常是一个未提交的长事务。处理方式是和业务方确认后杀掉那个会话或者等它提交。8.2 8.0 的原子 DDL 对库操作的影响MySQL 8.0 的一个重大改变是原子 DDLCREATE DATABASE、ALTER DATABASE、DROP DATABASE这些操作要么全部成功要么全部回滚不再存在执行一半失败留下半残状态的情况。这对运维来说是个好消息以前 5.7 上执行DROP DATABASE中途报错可能库删了一半、另一半还在8.0 不会出现这种状态。但要注意原子 DDL 对DROP DATABASE的约束是如果库里存在被其他事务正在访问的表DROP DATABASE会被阻塞直到那些事务结束。这个阻塞不是报错而是等待。所以在执行大库删除时提前告知业务方否则你可能等上很久也不知道发生了什么。9. 常见问题排查速查表我把这几年在库操作上遇到的高频问题整理成一张表每一类都附了排查思路和解决方案你可以直接收藏备用。报错或现象可能原因排查手段解决方案ERROR 1007建库已存在重复建库查看SHOW DATABASES加IF NOT EXISTSERROR 1044无权限建库当前用户没有 CREATE 权限SHOW GRANTS FOR CURRENT_USER()root 授权 CREATE 或建库权限中文变问号字符集不是 utf8mb4SHOW CREATE DATABASE mydbALTER DATABASE/TABLE 字符集看不到某个库权限不足用 root 查看SHOW GRANTSGRANT ... ON mydb.* TO ...DROP DATABASE卡住存在长事务持有 MDLSHOW PROCESSLIST等待事务结束或 kill 事务会话磁盘空间没减少InnoDB 表空间未收缩df -h查 ibd 文件大小重建表空间或考虑物理收缩方案ERROR 1008删库不存在DROP DATABASE 目标不存在SHOW DATABASES加IF EXISTS表空间加密无法启用参数或硬件密钥管理问题查看 error log检查keyring配置确认 8.0.169.1 关于“mysql 服务无法启动”的排查很多人执行DROP DATABASE或ALTER DATABASE时没有异常但事后重启 MySQL 服务发现起不来了错误日志里一堆Invalid MySQL server upgrade或其他初始化错误。这种情况十有八九是误改了系统库的表结构或者删掉了系统表空间相关的数据文件。我之前处理过一个案例同事在执行脚本清库时把mysql系统库里的user表给清了重启服务后 MySQL 直接起不来。排查思路优先重启之前改了什么如果改了系统库的表尝试用备份恢复系统库千万不要顺手就把整个 datadir 删了重来。9.2 关于 Docker 环境下的库操作现在很多人用 Docker 跑 MySQL比如拉镜像、启容器然后进入容器执行 SQL。在容器环境里做库操作和裸机没有本质区别但有几个细节要注意容器重启后数据是否会丢取决于有没有挂载 volume。如果没挂载容器删了数据就没了。容器内执行 mysqldump最好把备份文件输出到挂载目录否则容器一删备份也没了。Docker 的默认资源配置可能限制 MySQL 的内存和磁盘性能大库备份时可能报错或奇慢建议先确认资源限制。关于 docker pull mysql 镜像偶尔报错的问题有一个常见是镜像层索引拉取失败重新 pull 或者更换镜像源通常能解决。这个问题和库操作本身无关但是如果你在 Docker 环境里建库建到一半发现连接断了先检查容器网络配置别急着怀疑 SQL 语句。10. 库操作与 SQL 排序逻辑的联动10.1 排序规则为什么影响查询这是很多人都没注意到的点。你在库上选了utf8mb4_general_ci还是utf8mb4_0900_ai_ci会直接影响到ORDER BY对字符串排序的结果。general_ci对中文排序是按 Unicode 编码顺序来的而0900_ai_ci会更接近语言习惯的拼音排序效果。如果你的业务有中文分词检索、中文排序需求排序规则的选择就非常关键。10.2 排查排序异常的思路如果你发现某条 SQL 在测试库和正式库上排序结果不一样第一反应不要改 SQL先查两个库的COLLATION是否一致SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA mydb;如果确实不一致可以通过修改排序规则让两边统一。但是注意修改排序规则同样会重写表结构耗时和数据量正相关。11. 存储过程与库操作的关系很多业务逻辑会写在存储过程里库操作时有一个很容易被忽略的点存储过程、函数、触发器是挂载在某个库下的DROP DATABASE 时它们会一并被删除。如果只是修改库的字符集存储过程不受影响但存储过程中如果有硬编码的字符串变量在字符集切换后可能出现乱码或长度判断异常。这不是 MySQL 的 bug而是字符串的字面量编码与库字符集不一致导致的。因此我的建议是你的存储过程里字符串变量尽可能使用CHARACTER SET utf8mb4显式声明避免依赖库级别的默认设置DECLARE v_name VARCHAR(100) CHARACTER SET utf8mb4 DEFAULT ;这种写法在库属性变更时能少踩很多坑。12. 库操作与 Flink CDC 同步的场景补充搜索词里有一条“使用 Flink 实现 MySQL 同步到 ClickHouse”这里涉及到 Flink CDC 对 MySQL 库级同步的原理顺带展开说一下。Flink CDC 会读取 MySQL 的 binlog基于行级变更事件进行同步。如果你在 MySQL 侧执行了ALTER DATABASE修改字符集这个 DDL 事件会被 Flink CDC 捕获并往目标端同步。如果目标端不支持同样的 DDL 语句同步任务可能报错中断。实际踩坑建议库级 DDL 尽量避开同步任务运行时段。如果同步任务中断先看 Flink 侧的错误日志确认是否是 DDL 同步失败再决定是跳过该 DDL 还是重建同步任务。大库执行ALTER TABLE ... CONVERT TO CHARACTER SET这类高耗时 DDL 时binlog 会产生大量事件同步任务很容易出现延迟积压提前做好监控。13. 易语言、C 连接 MySQL 时库操作的特殊注意事项搜索词里出现了“易语言不能载入支持库 ado数据库操作支持库1.4版”和“C 链接 MySQL”说明不少人是在写客户端程序去连 MySQL。这里关于库操作有一个很重要的点要提醒。用 ODBC 或 Connector/C 连接 MySQL 时你连接时需要指定一个默认数据库。如果这个库被删了客户端连接会直接失败报 “Unknown database”。而且像 ODBC 驱动在连接建立后如果库被 DROP驱动不会自动重连所有后续查询都会失败。所以在客户端程序里尽量做到连接串中数据库名可通过配置项修改不要写死。程序启动时做一次连通性检查明确返回库不存在的错误码。重连逻辑里要包含重新选择默认库的逻辑。另外如果客户端程序权限不足连库都看不到执行任何 SQL 都会报权限错误。这时候优先查授权SELECT user, host FROM mysql.user; SHOW GRANTS FOR appuserhost;14. 从库操作看整个数据库生命周期库是整个 MySQL 实例里最顶层的数据容器它就像一栋楼的框架楼里的每间房是表房里的家具是索引和数据。你建库时选好字符集和排序规则就是在决定这栋楼的水电管道规格你给库授权就是在决定谁能进哪一层你的备份策略则是在给这栋楼买保险。从我个人的实操经验来看库操作这个主题看似简单但最容易出问题的点反而是“太简单了所以不当回事”。我见过太多人CREATE DATABASE随便写、权限随意 grant、备份想起来才做最后线上出了问题只能干瞪眼。如果你现在刚开始管理一个 MySQL 实例我建议你给自己定几条铁律每次建库都必须显式指定字符集和排序规则不接受默认值每个库必须有明确的负责人和权限边界拒绝 root 一把梭每天强制检查备份任务是否成功而不是等出事再查任何ALTER DATABASE和DROP DATABASE操作都必须先走一遍备份流程。这几条是我自己踩过坑之后总结出来的不能说保证你永远不会遇到问题但至少能帮你把 80% 的坑提前挡在外面。最后再分享一个实用习惯我在建库时喜欢顺手把建库语句和授权语句记录到项目文档里一行建库、一行授权、一行说明用途。等三个月后你回头看就知道当初这个库是给哪个业务建的、为什么选了这套配置。这个习惯不花什么时间但在排障和交接时省下来的时间远超你想象。
