写这个系列已经到第23篇了MySQL相关的内容这是第二篇。上一篇把安装过程基本讲完了这篇准备说点安装完成之后立刻要面对的事初始化安全加固、账号权限怎么分配、配置文件哪些参数值得动、日常备份怎么做、遇到连接不上或者说Socket报错怎么排查。这些内容不像安装教程那样一步一截图但恰恰是实际用起来最花时间的地方。我尽量按自己的实操顺序来写想到哪说到哪但保证每一条都是自己踩过坑或者验证过的东西。如果你是刚在Linux服务器上装完MySQL 8.0正对着命令行一脸懵或者已经跑了一段时间但总出小毛病不知道怎么查这篇文章应该能帮你省点时间。内容会偏实用向理论部分只讲够用的程度重点放在“怎么操作”和“为什么这么操作”上。1. 安装完成之后别急着用先把这几步走完很多教程装完MySQL就结束了实际上装完离“能正常跑业务”之间还差好几步。我习惯按这个顺序处理先保证服务能起来再做安全初始化最后确认关键的目录和权限没问题。1.1 先确认服务状态和安装信息无论你是用apt、yum还是二进制包装的装完之后第一件事不是急着登录而是确认服务真的在跑。我之前在CentOS上装完MySQL 8.0systemctl status显示active但Navicat怎么都连不上折腾半天发现是防火墙没放行3306端口。这种基础问题最耽误时间。检查服务状态用这几条命令systemctl status mysqld # 或者 systemctl status mysql不同发行版服务名不一样Debian系一般叫mysqlRHEL系一般是mysqld两个都试一下就知道。如果服务没起来看错误日志最有效默认位置在/var/log/mysqld.log或者/var/log/mysql/error.log具体路径看配置文件。还要确认一下版本和安装路径mysql --version which mysql mysqld --verbose --help | grep -E ^datadir|^socket|^log-error最后一条命令会打印出MySQL实际使用的数据目录、Socket文件和错误日志位置这些信息后面排查问题的时候经常用得到。我建议把这几个路径截图或者记到笔记里省得每次都翻。1.2 安全初始化脚本必须跑一遍MySQL装完默认的root账号是没有密码的而且只有本地能登录。这时候直接跑一下官方提供的安全脚本mysql_secure_installation这个脚本会一步一步问你要不要设root密码、要不要删除匿名用户、要不要禁止root远程登录、要不要删除test测试库、要不要刷新权限表。新手容易纠结每一项选什么我的建议是root密码必须设而且要设强密码删除匿名用户选Y生产环境没理由留匿名用户禁止root远程登录选Yroot只保留本地登录远程用业务账号连删除test库选Y测试库留着没啥用还占地方刷新权限表选Y这个脚本做的是最基础的瘦身。把匿名用户和test库清掉能少很多潜在的风险。我见过有老哥图省事不跑这个脚本结果数据库被人用匿名用户登进去删了库后悔都来不及。1.3 打开通用日志确认一切正常刚装完还没开始正式用的时候我习惯把通用日志打开观察几天。通用日志会记录所有客户端连进来的连接信息包括来源IP、登录用户名、执行过的SQL排查问题的时候特别有用。临时打开直接改全局变量SET GLOBAL general_log ON; SET GLOBAL general_log_file /var/log/mysql/general.log;不用了记得关掉不然日志增长特别快尤其是业务量大的库几天就能吃满磁盘。我个人的习惯是只在排查问题的时候开平时保持关闭状态更推荐用慢查询日志来监控。2. 账号和权限管理我踩过的坑都在这MySQL的账号权限体系说简单也简单就是“谁在哪个IP能访问哪个库能干什么事”。但实际操作里很多人为了省事给应用账号开了个root权限或者ALL PRIVILEGES这个习惯特别不好。2.1 创建业务账号的基本原则我见过不少团队测试环境、生产环境都用root连数据库出了事根本没法追责——因为所有人的操作都是root身份日志里只能看到一大片root。正确的做法是一套环境一个账号一个应用一个账号宁多勿少。创建账号的语法是CREATE USER app_user192.168.1.% IDENTIFIED BY StrongPass123!;这里的192.168.1.%是限制登录来源网段%代表任意IP。老实说生产环境我建议限定来源IP网段而不是偷懒直接用%。万一某个开发拿着账号密码到处连限了IP至少能拦住一部分风险。2.2 最小权限授权够用就行授权的基本原则是只给应用需要的权限。一个只做查询的报表账号给个SELECT就够了没必要给INSERT和UPDATE更别给DDL权限。我常用的几个授权模板-- 只读账号适合报表查询 GRANT SELECT ON mydb.* TO readonly_user192.168.1.%; -- 读写账号适合业务应用 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_user192.168.1.%; -- 开发账号给到DML和建表权限 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX ON mydb.* TO dev_user192.168.1.%;授权完记得刷新权限FLUSH PRIVILEGES;FLUSH PRIVILEGES很多人会忘改完权限发现不生效就开始怀疑人生。那是因为MySQL的权限缓存没刷新跑一下就对了。2.3 权限导致的最典型报错我印象最深的一个报错是应用连接数据库时报ERROR 1044 (42000): Access denied for user但这个账号明明授权了。后来查出来是因为授权的时候库名写错了授权给了mydb但应用连的其实是mydb_test这个库开发又没把错误信息看清楚排查了两天才找到根因。还有一回是账号能连上但执行INSERT时报ERROR 1142 (42000): INSERT command denied这个特别经典——只授了SELECT的账号被拿来写数据了。这类问题其实都指向同一个原因权限给少了或者给错了目标对照着查就行。排查权限问题我习惯先把当前账号拥有的权限看清楚SHOW GRANTS FOR app_user192.168.1.%;这条命令会把该账号的所有权限明细列出来一目了然。3. 配置文件到底改什么我的my.cnf心得MySQL跑起来之后真正影响性能和稳定性的其实是配置文件。很多新人装完MySQL配置文件一行没动默认配置就那么跑起来。小项目无所谓稍微有点并发就直接教做人。但配置也不能乱改改之前得先搞明白每项参数的意思。3.1 先找到配置文件和生效优先级MySQL的配置文件叫my.cnfLinux下也有叫my.ini的那是Windows版主要位于/etc/my.cnf、/etc/mysql/my.cnf或者MySQL安装目录下的my.cnf。配置文件是有加载顺序的后面的会覆盖前面的。查看当前生效的配置mysqld --verbose --help | grep -A 1 Default options这条命令会列出配置文件搜索路径。然后可以通过SQL查看具体参数的实际值SHOW VARIABLES LIKE innodb_buffer_pool_size;3.2 新人最该调整的几个参数配置参数特别多但日常使用最常被提到的就那几个。我列一个简单的优先级清单innodb_buffer_pool_sizeInnoDB的缓冲池大小直接决定内存里能缓存多少数据。建议设为物理内存的60%-70%。这个参数是MySQL性能的关键默认值128M对于稍微像样点的服务器来说太小了。max_connections最大连接数默认151如果应用并发高会报Too many connections。建议根据实际需求上调并同时调整max_connections和open_files_limit。character_set_server和collation_server字符集相关建议从开始就统一为utf8mb4后面再改字符集是件非常痛苦的事情。slow_query_log慢查询日志开关配合long_query_time使用建议生产环境一直开着方便排查慢SQL。binlog_expire_logs_secondsbinlog过期时间默认30天如果磁盘紧张可以改短但要先确认备份策略是否依赖binlog。举例一台8G内存的机器我个人常用的配置段是[mysqld] port 3306 character_set_server utf8mb4 collation_server utf8mb4_unicode_ci innodb_buffer_pool_size 5G innodb_log_file_size 256M innodb_flush_log_at_trx_commit 1 max_connections 500 slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 2 binlog_expire_logs_seconds 604800innodb_buffer_pool_size设为5G是考虑到操作系统和其他进程也需要内存留了余量。innodb_flush_log_at_trx_commit 1是安全和性能的折中每次事务提交都会刷盘最安全但会牺牲一点性能如果业务能接受极端情况下丢1秒数据可以改成2性能提升明显。3.3 改完配置怎么验证生效改完my.cnf不是重启就完事了还得验证参数真的生效了。我先说一个我犯过的错有一次我改了innodb_buffer_pool_size重启MySQL后忘了刷新用SQL一查发现还是默认值当场就懵了。后来发现是配置文件路径写错了我改的那个文件MySQL根本没读。所以改完配置一定要查实际值SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE character_set_server;对比实际值和预期值是否一致不一致的话八成是配置文件没被读到或者改错位置了。4. 备份这事真出事的时候才想起就晚了我一直强调备份不是可选项是必选项。之前有朋友数据库被误删了一张表结果备份策略是“想起来才备份一次”最后只能靠binlog慢慢挖累得半死。如果是生产环境必须建立一套自动化的备份机制。4.1 mysqldump手动备份实操mysqldump是MySQL自带的逻辑备份工具优点是不依赖存储引擎、跨版本恢复比较方便缺点是数据量大时恢复慢。单库备份mysqldump -u root -p --single-transaction --routines --triggers mydb mydb_backup.sql参数说明--single-transactionInnoDB表备份时通过事务保证一致性备份过程中不锁表。这个对线上环境很重要不然备份期间业务写入会阻塞。--routines包含存储过程和函数。--triggers包含触发器。全库备份就加个--all-databasesmysqldump -u root -p --single-transaction --all-databases all_db_backup.sql备份出来的文件建议压缩一下SQL文件文本冗余度很高gzip能压掉一大半mysqldump -u root -p --single-transaction mydb | gzip mydb_backup_$(date %Y%m%d).sql.gz4.2 恢复操作没你想的那么复杂数据恢复也很简单就是把备份文件重新导入mysql -u root -p mydb mydb_backup.sql如果备份文件是gzip压缩的先解压再导入gunzip -c mydb_backup_20240101.sql.gz | mysql -u root -p mydb这里有个小坑如果目标库名和备份时的库名不一样需要在导入前手动建好同名库并注意字符集是否一致。我之前恢复过一批数据源库是utf8mb4目标库建的时候没指定字符集结果中文字符全变乱码了后来重新调整字符集又导了一遍白白多花半小时。4.3 定时备份脚本省心又安全手动备份偶尔弄一次还行固定任务还是交给crontab靠谱。在/etc/cron.d/或者crontab里添加定时任务0 2 * * * root mysqldump -u root -pYourPassword --single-transaction --all-databases | gzip /backup/mysql/all_$(date \%Y\%m\%d).sql.gz注意几个点密码写在命令行里会出现在进程列表和shell历史里安全上有风险。更稳妥的做法是用--defaults-extra-file指定一个只有root能读的配置文件。备份文件别和数据库放在同一个磁盘上磁盘坏了就全没了。备份脚本建议加个保留策略比如只保留最近7天或30天避免备份文件把磁盘撑爆。我自己用的方案是在/backup/mysql下按日期存放备份然后用find加crontab自动删除7天前的文件find /backup/mysql -name *.sql.gz -mtime 7 -delete5. 日志和基础监控出了问题有迹可循MySQL的日志体系是排查问题最重要的素材但很多人平时根本没注意过。真出问题的时候连日志文件在哪都不知道就很被动了。5.1 错误日志第一手故障信息错误日志记录启动、运行、关闭过程中的关键信息包括启动时的初始化情况、异常退出原因、连接报错等。默认路径通常在/var/log/mysql/error.log或/var/log/mysqld.log。看最近几十行用tail最方便tail -n 100 /var/log/mysql/error.log比如常见的Cant connect to local MySQL server through socket错误日志里往往会有更详细的说明比如磁盘满了、权限不对、mysqld没起来等。日志是定位问题最快的路径。5.2 慢查询日志和EXPLAIN入门慢查询日志会记录执行时间超过阈值的SQL语句是找出性能瓶颈最基础的手段。开启方式SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;这样执行时间超过2秒的SQL都会记到slow.log里。拿到慢SQL之后用EXPLAIN分析执行计划EXPLAIN SELECT * FROM orders WHERE user_id 10086 ORDER BY create_time DESC;EXPLAIN输出里的type列是关键最好到ref或const如果出现ALL就是全表扫描该优化索引了。rows列能估计扫描的行数也能帮忙判断SQL有没有走对索引。5.3 binlog不只是用来恢复数据的binlog是MySQL的二进制日志记录了所有的数据变更操作。它有两个主要用途数据恢复和主从复制。建议开启binlog并设置合适的过期时间server_id 1 log_bin /var/log/mysql/mysql-bin.log binlog_expire_logs_seconds 604800server_id在主从复制和多实例场景下必须配置即使当前单机我也建议一开始就写上省得后面要搭从库时再补。常用查看命令SHOW BINARY LOGS; SHOW BINLOG EVENTS IN mysql-bin.000001 LIMIT 10;binlog文件是二进制的不能直接cat看用mysqlbinlog工具解析mysqlbinlog /var/log/mysql/mysql-bin.000001如果误删了数据可以通过binlog恢复到误操作前的时间点这个以后可以单独写一篇细说。但前提是binlog开着否则只能靠物理备份碰运气了。6. 常见问题排查这些问题我基本都遇到过6.1 连接报错ERROR 2002 (HY000)这个报错大概是MySQL使用中最常见的了完整提示是ERROR 2002 (HY000): Cant connect to local MySQL server through socket /var/run/mysqld/mysqld.sock (2)这个报错的意思是客户端通过Socket文件连接本地MySQL失败。排查顺序我建议按这三步走第一步确认服务有没有起来systemctl status mysql如果服务没起来错误日志里会有原因最常见的是配置错误、磁盘空间满、或者端口被占用。第二步确认Socket文件和配置是否一致mysql -h 127.0.0.1 -P 3306 -u root -p如果通过TCP协议能连上通过Socket连不上那就是Socket路径不对或者没有权限。检查一下配置里的socket路径和客户端默认的路径是否一致不一致的话可以指定路径连接mysql -u root -p --socket/tmp/mysql.sock第三步确认运行MySQL的用户对Socket目录有写权限。这个报错在改了数据目录或Socket路径后经常出现本质是权限问题。6.2 忘记root密码的解决办法忘记root密码不需要重装MySQL。MySQL提供了一个跳过权限验证的启动模式可以临时进入系统改密码。步骤一关掉MySQL服务systemctl stop mysql步骤二用跳过授权表的方式启动mysqld_safe --skip-grant-tables 或者修改my.cnf配置文件在[mysqld]段加上skip-grant-tables然后重启MySQL。步骤三此时无需密码就能登录mysql -u root登录后立即修改密码FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY YourNewStrongPassword; FLUSH PRIVILEGES;步骤四恢复配置删掉skip-grant-tables或者在my.cnf里注释掉重启MySQLsystemctl restart mysql这里要特别提醒skip-grant-tables模式是没有任何访问控制的任何人都能无密码登录数据库绝对不能暴露在公网上。处理完密码问题后一定要记得关掉并检查有没有被陌生人连过。6.3 存储过程一个很实用的小场景存储过程在一些业务场景下很实用尤其是批量数据处理和定时任务。虽然很多人说存储过程不好维护但封装一些固定逻辑应用侧调用确实方便。举个实际例子我写过清理过期日志的存储过程DELIMITER $$ CREATE PROCEDURE clean_expired_logs(IN days INT) BEGIN DELETE FROM operation_log WHERE create_time NOW() - INTERVAL days DAY; END$$ DELIMITER ;调用方式CALL clean_expired_logs(7);需要注意MySQL默认的语句分隔符是分号定义存储过程主体的分号会被提前截断所以必须用DELIMITER先把分隔符改成别的执行完再改回来这一点新手经常忘记。6.4 字符集问题中文乱码的根源如果数据库、表和客户端连接字符集不一致中文显示就会乱码。最稳妥的方案是从一开始统一utf8mb4SET NAMES utf8mb4;连接字符串里也要加characterEncodingutf8mb4。如果是老库已经用了latin1之类的字符集改起来会很痛苦需要导出数据、改表结构、再导入时间成本和风险都不小。所以新库建表时选对字符集比后面再改要省一万倍的心。好这一篇大概就这些内容。MySQL能写的东西太多了索引优化、主从复制、性能压测每一个方向展开都是一大篇。这次先把自己日常最常用、踩坑最多的部分梳理了一遍正好把常用命令也串到一起了。想要的时候总会翻命令笔记不如就看这一篇速查。剩下的下一篇再接着聊。