Linux基础-9MySQL基础很多人刚接触Linux服务器时候第一件事不是配置防火墙也不是折腾各种服务而是先要把数据库跑起来。MySQL在Linux生态里的地位有点像水电煤几乎每个跑后端的Linux机器都得装一个。这系列文章前面讲了VM、目录结构、常用命令、用户权限什么的到这一篇就轮到MySQL了。这篇文章不打算讲那种“你复制一行命令就装好了”的教程那样的文章太多了。我想聊的是在Linux上把MySQL真正跑明白这件事。包括怎么选安装方式、装完之后怎么初始化、配置文件里哪些参数默认值其实有毒、日常操作该注意什么以及我这些年踩过的那些网上教程不会跟你说的坑。适合刚接触Linux、准备在服务器上部署应用的读者也适合那些装了好几次MySQL但总出莫名其妙问题的人。1. 内容整体设计与思路拆解1.1 为什么Linux和MySQL是绑定出现的你知道Windows上装MySQL什么体验吗双击安装包下一步下一步选个端口设个密码完事。图形界面帮你搞定了一切。但Linux不一样你装的是服务器系统服务器讲究的是可管理性、可重复性、可排查性。一条命令装完配置文件在哪、数据文件在哪、日志在哪、怎么启停每一样都清清楚楚掌握在你手里。这就是为什么生产环境几乎清一色Linux加MySQL的组合。MySQL本身是一个关系型数据库管理系统RDBMS跟Linux文件系统打交道比其他数据库更密切。比如它的数据目录就是一组文件binlog、redo log也都是文件。你对Linux文件系统、权限、进程的理解直接决定你能不能把MySQL运维好。比如数据目录权限不对MySQL启动直接报错磁盘满了binlog没清理数据库卡死这些本质上都是Linux层面的问题只是最后暴露在MySQL上而已。1.2 这篇文章围绕的三个核心能力这篇文章设计的核心就三个递进的能力装好、配好、用好。装好选择适合你的安装方式明白每种方式背后的原理遇到安装过程的问题知道怎么解决。配好装完不等于能用好的初始化、字符集、账号安全、关键参数这些配置项决定了你的MySQL是“能用”还是“好用”。用好掌握日常高频的命令和操作习惯知道怎么快速定位问题而不是遇到事情就重启大法。这三点每个都展开想清楚背后的逻辑比单纯记命令重要得多。命令是死的理解了原理遇到没见过的报错你也能推测出大概方向。2. 环境准备与安装方案选型2.1 服务器环境确认动手之前先确认三件事操作系统版本、硬件资源、网络情况。操作系统版本决定了你用什么方式装MySQL。比如CentOS 7自带的是MySQL 5.7的repo源而CentOS 8及以上自带的是MySQL 8.0Ubuntu 20.04默认源的MySQL版本是8.0.xUbuntu 22.04也差不多。不同大版本之间初始化方式和部分命令语法有差异。看清楚底下的系统是什么再去查对应的安装文档能少走很多弯路。硬件资源主要是内存。MySQL 5.7跑起来大概要400MB到1GB的内存如果你用默认配置在1GB内存的云服务器上装MySQL 8.0开机可能就内存耗尽。这里建议内存较小的服务器跑MySQL 5.72GB以上再考虑MySQL 8.0。磁盘方面确认一下数据目录所在分区至少有5GB左右余量后续数据库扩容再说。网络情况这个很多人忽略。在Linux上用yum或apt在线安装MySQL需要访问系统默认软件源或者官方源。国内云服务器有时候访问官方repo源非常慢甚至超时。这时候就需要配置国内镜像源比如阿里云、清华源等。如果你是企业内网环境无法访问外网就得走离线安装路线——用rpm包或二进制的tar包安装。2.2 在线安装与离线安装怎么选在线安装是我最推荐的方式也是绝大多数场景下最省事的方案。以CentOS 7为例安装MySQL 5.7就是先装官方repo然后一条yum命令搞定rpm -Uvh https://repo.mysql.com/mysql57-community-release-el7.rpm yum install -y mysql-community-server装完之后启动服务查看初始密码改密码就这么简单。在线安装的优点是自动处理依赖、自动注册systemd服务、后续升级方便一条yum update就行。缺点是对网络有要求repo源可能会变官方链接偶尔失效。离线安装适合内网隔离环境。我接触过不少政企项目服务器在独立业务区物理隔离外部访问都走前置机这时候别说yum源连系统本身的repo都用不了。这种情况有两种路线rpm包安装需要提前下载所有依赖包然后在目标机器上手动rpm -ivh安装。MySQL的rpm包会把MySQL和依赖拆成mysql-community-server、mysql-community-client、mysql-community-libs等多个包逐个安装即可。通用二进制包安装从官网下载mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz解压到指定目录初始化数据目录然后启动。这种方式最灵活目录、权限、启动方式全部自己掌控但需要手动配置的东西也多。我个人建议除非网络受限一律用在线安装。离线安装看着高大上但维护成本高出了问题排查起来也更费劲。新手选在线安装把精力花在理解和配置上不是花在跟依赖打架上。2.3 Docker方式装MySQL值得学吗热搜词里有一条“docker安装mysql失败”说明很多人在Docker上装MySQL翻车了。确实Docker跑MySQL其实很方便docker run -d --name mysql -e MYSQL_ROOT_PASSWORD123456 -p 3306:3306 mysql:5.7两条命令就起来了。但“起了”跟“能用”之间差了很远。Docker容器内的MySQL数据默认写在容器层里容器一删数据就没了所以必须挂载数据卷。然后配置文件、日志、时区都需要挂载进去。我发现很多人翻车的原因是容器时钟不对、时区不对MySQL的NOW()函数返回的时间跟宿主机差了8个小时排查到怀疑人生。Docker装MySQL不是不能用但你要意识到它是“运行在Linux之上的一层封装”它帮你隐藏了细节但那些细节问题并不会消失只是变成更难排查的形式。我的建议是学习阶段老老实实在Linux上用yum或者apt装MySQL等理解了数据目录、配置文件、日志这些核心概念再玩Docker到时候你就知道挂载卷、传参这些操作在解决什么问题了。3. 初始化配置与核心参数解析3.1 从初始密码到安全配置MySQL装好之后第一道门槛就是初始密码。以CentOS 7加MySQL 5.7为例装完服务后默认生成了临时密码存储在日志里systemctl start mysqld grep temporary password /var/log/mysqld.log输出类似这样2024-03-15T09:12:34.567890Z 1 [Note] A temporary password is generated for rootlocalhost: xxxxxxxx这是你第一次登录的唯一凭证拿着这个密码执行mysql -uroot -p进去之后第一件事就是改密码ALTER USER rootlocalhost IDENTIFIED BY NewPassword123!; FLUSH PRIVILEGES;注意MySQL 5.7默认开启了validate_password插件密码必须包含大小写字母、数字和特殊字符长度至少8位。如果你就是想用简单密码比如本地测试环境可以先卸载这个插件但生产环境不要这么干。我见过太多人在这栽跟头改了密码以后忘了记下次登录死活登不上或者改复杂了自己记不住最后只能reset。密码记牢或者用密码管理器。MySQL 5.7之后还有一个细节root账号默认只允许localhost登录也就是MySQL只能在本机连接。如果你用Navicat或者DataGrip从Windows远程连Linux上的MySQL会报错“Host xxx is not allowed to connect to this MySQL server”。解决办法是授权root远程访问或者新建一个账号指定允许来源IPCREATE USER appuser% IDENTIFIED BY UserPass123!; GRANT ALL PRIVILEGES ON *.* TO appuser%; FLUSH PRIVILEGES;这里我给新手一个建议如果是生产库不要给应用账号通配%授权而是限定来源IP或网段如192.168.1.%。没错开发的时候嫌烦但被扫库的人盯上的时候你就不嫌烦了。如果你用的是云服务器别忘了在安全组/防火墙里放行3306端口很多人授权都改了连不上还以为是MySQL问题结果发现是防火墙没开。3.2 my.cnf 配置文件哪些参数值得改MySQL装完后的默认配置文件在 /etc/my.cnfRHEL系或 /etc/mysql/mysql.conf.d/mysqld.cnfDebian系。默认配置能跑但离“合理”差得远。我列几个我每次安装都会调整的参数按优先级排序参数默认值建议值说明character_set_serverlatin1utf8mb4不设的话中文、表情符号存进去直接乱码collation-serverlatin1_swedish_ciutf8mb4_general_ci跟字符集配套的排序规则max_connections151300-500连接数上限太小了高并发直接报错innodb_buffer_pool_size128M物理内存的60%-70%InnoDB缓存越大读性能越好lower_case_table_names0Linux下默认0即可Windows迁移到Linux要注意大小写问题slow_query_logOFFON慢查询日志排查性能问题必备expire_logs_days07binlog保留天数日志文件清理innodb_buffer_pool_size是5.7之后影响最核心的性能参数InnoDB表的数据和索引都缓存在这里。如果内存8GB给MySQL分配5GB左右是合理的但要留出系统和其他进程需要的内存别一个全给MySQL吃光。关于lower_case_table_names这里必须多说一嘴。MySQL在Linux上默认是区分表名大小写的Windows上不区分。如果你的业务之前跑在Windows上表名是UserInfo迁移到Linux上SQL里写的是userinfo直接报错Table doesnt exist。解决方式是在初始化参数里加lower_case_table_names1让MySQL不区分大小写。但注意这个参数必须在你初始化数据目录之前设置好否则后面再改会导致数据和索引错乱。一旦初始化完毕这个参数就不能改了这是MySQL存在已久的一个硬约束。字符集这块也是重灾区。默认latin1在存中文的时候不是不行是各种奇怪问题比如字符串截断、乱码、排序异常。现在普遍都推荐utf8mb4这个是utf8的超集能存emoji表情能用中文相关的排序。建库的时候我一般直接指定CREATE DATABASE appdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;你光在my.cnf里设了还不够建库时显式指定才是真的稳。3.3 数据目录与日志文件理解MySQL的数据目录默认在/var/lib/mysqlRHEL系这里面有ibdata1、ib_logfile0、以及每个数据库对应的子目录。理解这些文件的作用对你后续排查问题帮助极大。ibdata1系统表空间文件存放数据字典和undo log等默认自动扩展。ib_logfile0/15.7或#innodb_redo目录8.0redo log崩溃恢复的关键。数据库子目录每个数据库对应一个目录每个表对应.frm5.7的表结构文件和.ibd表数据和索引文件MySQL 8.0以后.frm合并到数据字典里了目录里主要是.ibd文件。日志方面/var/log/mysqld.log记录启动信息、错误信息、以及之前的临时密码。binlog是二进制日志默认存放在数据目录下记录所有数据变更操作主从复制和数据恢复都靠它。binlog不清理会一直增长直到把磁盘占满。前面推荐的expire_logs_days参数就是自动清理binlog的策略。注意生产环境千万不要手动rm binlog文件。我见过有人磁盘满了直接rm -f binlog.0000xx导致后面主从复制直接断裂。正确做法是用PURGE BINARY LOGS命令清理或者让expire_logs_days自动清理。4. 实操过程与核心环节实现4.1 完整安装流程实录CentOS 7 MySQL 5.7我以CentOS 7为例把一次完整、干净的MySQL 5.7安装走一遍每一步为什么这么操作我都说清楚。第一步检查系统里是否已经存在MySQL或者mariadb。CentOS 7自带的mariadb是MySQL的开源分支端口都是3306不卸载的话会端口冲突rpm -qa | grep -E mysql|mariadb如果有输出先卸载例如yum remove -y mariadb-libs第二步安装官方repo源。rpm -Uvh会把mysql的yum源配置到/etc/yum.repos.d/mysql-community.repo之后yum就能从这个repo里找到MySQL相关的包rpm -Uvh https://repo.mysql.com/mysql57-community-release-el7.rpm如果你系统是CentOS 8/RHEL 8用el8的repo包是CentOS 7就用el7。选错了yum报错Parsing error。国内网络如果下不动官方源可以下载rpm文件到本地再rpm -ivh安装。第三步安装MySQL服务端和其他组件yum install -y mysql-community-server mysql-community-client依赖处理好安装结束会出现“Complete!”字样。这时候MySQL还没启动需要先初始化。5.7版本和之前的区别就是初始化时不需要手动执行mysql_install_db了启动服务时会自动初始化数据目录并生成临时密码systemctl start mysqld systemctl enable mysqldenable是设置开机自启这步很重要不然每次服务器重启MySQL都不会自动起来。我帮人排查过很多次“我重启了一下服务器数据库没了”其实就是少了这行命令。第四步查询临时密码并登录改密按3.1节的方法操作grep temporary password /var/log/mysqld.log mysql -uroot -p ALTER USER rootlocalhost IDENTIFIED BY YourPass123!; FLUSH PRIVILEGES;到这一步一个能用的MySQL就算起来了。验证一下服务状态和版本systemctl status mysqld mysql --version能正常输出版本号比如mysql Ver 14.14 Distrib 5.7.44说明安装无误。4.2 Ubuntu系统安装实录apt方式如果用Ubuntu安装流程差别比较大但更简单。Ubuntu 20.04及以上用apt直接可以安装官方源里的MySQL 8.0sudo apt update sudo apt install mysql-server sudo systemctl start mysql sudo systemctl enable mysqlUbuntu下MySQL初始化和CentOS不一样装好之后没有临时密码root账号默认使用auth_socket插件认证你只能用sudo身份登录mysqlsudo mysql -uroot登录进去以后需要把root认证方式改成密码认证ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY YourPass123!; FLUSH PRIVILEGES;MySQL 8.0默认认证插件用的是caching_sha2_password而很多旧版本客户端比如老版本Navicat不支持这个认证方式连接会报Authentication plugin cannot be loaded错误。遇到这种情况你需要在MySQL里把账号改回mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY YourPass123!;注意MySQL 8.0的默认字符集已经是utf8mb4了这一点比5.7省事不少。如果你仍在使用MySQL 5.7手动设置utf8mb4还是必须的。4.3 数据库日常操作建库、建表、授权、备份装好并完成基础配置后数据库的日常管理技能才是真正的核心。我把最高频的操作串一遍顺手把我常用的习惯告诉你。创建数据库和用户一手交库一手交人CREATE DATABASE appdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; CREATE USER applocalhost IDENTIFIED BY AppPass123!; GRANT ALL PRIVILEGES ON appdb.* TO applocalhost; FLUSH PRIVILEGES;这里权限是授予app用户对appdb这个库所有表的权限。如果应用和数据库不在同一台机器就改主机来源CREATE USER app192.168.1.100 IDENTIFIED BY AppPass123!;表结构和数据验证。建好库之后可以快速录入几行数据验证链路是通的USE appdb; CREATE TABLE test_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB; INSERT INTO test_table (name) VALUES (hello), (world); SELECT * FROM test_table;能看到输出两行数据说明表结构、存储引擎、字符集都没问题。备份策略这个是数据维护里最不能省的一环。我平时用的是逻辑备份加binlog相结合的方案逻辑备份就是mysqldump导出SQL简单直接mysqldump -uroot -pYourPass123! --single-transaction --routines --triggers appdb /backup/appdb_$(date %F).sql参数说明--single-transaction表示使用InnoDB的事务一致性备份备份过程中不锁表业务不用停--routines和--triggers是连存储过程和触发器一起导出。恢复就更简单了把SQL文件再导入MySQLmysql -uroot -pYourPass123! appdb /backup/appdb_2024-03-15.sql日常健康体检我习惯了每次上线前跑一遍这几个指令SHOW ENGINE INNODB STATUS; SHOW PROCESSLIST; SHOW VARIABLES LIKE character_set_server;SHOW PROCESSLIST特别重要当业务报数据库卡住时执行它能看到当前所有连接在干什么是否存在长时间运行的查询或者锁等待。发现长时间阻塞的连接用KILL线程ID的方式把它干掉KILL 12345;这个操作有点暴力但确实能救命。我见过一次业务卡死一条UPDATE跑了两小时不结束锁了一堆表一个KILL下去业务当场恢复。当然KILL之前要慎重确认别误杀重要的合法事务。4.4 用户管理与安全加固实际上MySQL安全是很多人忽略的部分尤其新手部署上线的时候。装完数据库改完密码就以为完事了其实还差得远。我整理了一套快速安全自检的清单删除无用账号SELECT user, host FROM mysql.user; 检查有没有多余账号特别是匿名账户。root账号远程登录生产环境不要给root开远程权限用专用账号连接。设置本地root密码复杂度区分rootlocalhost和root%后者宁可不要。关闭信任主机访问检查mysql.user表里的host字段不认识的HOST直接删除。生产库禁止ALL PRIVILEGES通配授权每个应用账号给够用的权限即可如SELECT、INSERT、UPDATE、DELETE、INDEX。再来一个细节问题同时运行多个MySQL实例的场景并不多但一台机器上装了多个版本或改过端口后你要注意防火墙上3306是不是被其他服务占用了。排查可以用lsof -i:3306或者netstat -tlnp。安全加固这一节操作起来都不难难的是养成习惯。我见过太多服务器被入侵、数据库被删库勒索的事件大部分都是因为root弱口令、远程权限开全、防火墙没有白名单这几个基础问题。这些东西安排到位了后续省心程度是几何级别的提升。5. 常见问题与故障排查实录5.1 安装和启动阶段的典型报错我来整理几个我自己实操中遇到过的、并在社区里被反复问到的典型问题整理成速查表方便你对照处理。现象排查方向解决方法mysqld启动失败无任何输出看error log/var/log/mysqld.log日志里会明确写失败原因比如数据目录权限不对Error: Cant connect to MySQL server (111)端口没监听或防火墙拦截netstat -tlnp看看3306是否LISTEN检查firewalld/iptables及云安全组Cant connect to local MySQL server through socketmysqld服务没起来systemctl status mysqld查看日志定位启动错误Access denied for user rootlocalhost密码错误确认改密码是否成功忘记密码时用skip-grant-tables跳过权限表重置Host is not allowed to connect to MySQL server账号主机限制在MySQL里修改host允许来源IP或新建远程账号Authentication plugin caching_sha2_password cannot be loadedMySQL 8.0认证插件和旧客户端不兼容把账号认证方式改为mysql_native_password其中忘记root密码是最多新手的痛。其实重置思路就一个在配置文件my.cnf的[mysqld]段加上skip-grant-tables重启MySQL后不需要密码就能登录然后重建root密码vim /etc/my.cnf # 在[mysqld]下加一行 skip-grant-tables systemctl restart mysqld mysql -uroot ALTER USER rootlocalhost IDENTIFIED BY YourNewPass123!;改完一定要记得把那一行注释掉然后重启服务。我见过有人忘了去掉skip-grant-tablesMySQL裸奔了一个多月账号密码跟没有一样任何人登录机器就能直接进数据库。5.2 端口冲突与socket连接问题MySQL连接分两种方式TCP/IP和Unix Socket。本地连接默认有时候走socket路径是/var/lib/mysql/mysql.sockRHEL系或/var/run/mysqld/mysqld.sockDebian系。报错“Cant connect to local MySQL server through socket /var/lib/mysql/mysql.sock”的时候第一反应是服务没起来先查看服务状态systemctl status mysqld ps aux | grep mysqld如果服务在但socket路径不对检查my.cnf里socket参数的指向和客户端连接时指定的一致就可以。多数情况是配置里改了socket路径但没有同步给客户端比如你用mysql命令连的时候它默认去读/etc/my.cnf里[client]段的socket配置如果这个段缺失或者路径不一致就会找不到socket文件。端口冲突一般是另外一个程序占用了3306。最常见的就是内置的mariadb没卸载干净。排查方法ss -tlnp | grep 3306如果你的3306被mariadb或者其他服务占用需要先停掉那个服务并禁用然后重新启动MySQL。如果实在没法释放3306就在my.cnf里把port改成3307同时所有客户端连接时指定-P 3307。但改端口会引出一堆连带问题比如防火墙策略、安全组、应用配置能不动尽量不动。5.3 数据导入乱码和字符集问题这是中文场景下最经典的坑。现象是SQL文件里明明是中文导入后select出来全是问号或者乱码。本质原因是连接层字符集和数据存储层字符集不对齐。正确做法有三步第一步确认目标库字符集是utf8mb4SHOW CREATE DATABASE appdb;如果不是修改ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;第二步导入时指定连接字符集mysql -uroot -p --default-character-setutf8mb4 appdb appdb.sql第三步确认SQL文件本身的编码就是UTF-8。有时候你用Windows记事本存的SQL文件虽然显示是中文但文件编码是GBK或者带BOM的UTF-8。导入之前用file命令检查file -i appdb.sql如果输出显示text/plain; charsetiso-8859-1之类不对劲的就说明文件编码有问题。先转码iconv -f GBK -t UTF-8 appdb.sql appdb_utf8.sql很多人在这一步栽跟头老是觉得是MySQL配置问题其实文件编码从一开始就是错的。我处理异常数据乱码时第一步永远先检查源文件的编码这个排查成本最低。5.4 忘记root密码与在线改密方案刚才提到了skip-grant-tables我再补充一下它的风险。5.7和8.0版本在skip-grant-tables模式下MySQL会跳过所有权限验证任何本地用户都能直接用root身份任意操作数据库。所以用完这个参数一定立刻关掉然后重启。还有一种更优雅的在线改密方案不需要重启MySQL前提是你还有另一个有管理权限的账号能登录mysql -uadmin -p UPDATE mysql.user SET authentication_stringPASSWORD(NewPass123!) WHERE Userroot AND Hostlocalhost; FLUSH PRIVILEGES;MySQL 8.0种方法写法有变化ALTER USER rootlocalhost IDENTIFIED BY NewPass123!; FLUSH PRIVILEGES;但凡是能用ALTER USER解决的就不要去动mysql.user表因为直接改表容易触发权限缓存不同步、或者字段格式不对导致无法登录。这是我给新手的明确建议。5.5 性能眼里最有价值的三个诊断命令数据库变慢是常态慢是不可接受的。排查性能问题我主要看三样东西SHOW PROCESSLIST看当前所有连接正在执行什么。如果有大量Sleep连接堆积说明连接池配置可能有问题如果有大量Copying to tmp table说明排序或JOIN操作在写临时文件大概率是索引缺失。慢查询日志打开之后超过阈值的SQL会记录SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;然后过一段时间看/var/lib/mysql/xxx-slow.log找到那些执行时间超过1秒的SQL分析执行计划。EXPLAIN查看执行计划EXPLAIN SELECT * FROM orders WHERE user_id 1234;关注type列是不是走的ALL全表扫描如果是说明user_id上没索引。加个索引解决往往速度呈几何级提升ALTER TABLE orders ADD INDEX idx_user_id (user_id);这三种工具掌握了你排查慢SQL的基本功就扎实了。很多生产故障提前用SHOW PROCESSLIST看几眼就能在业务被拖垮之前发现端倪。6. 经验总结与后续扩展写了这么多其实核心就一句话MySQL基础的扎实程度等于你对Linux系统理解的扎实程度。文件权限、目录结构、进程管理、日志排查每一个都是Linux基本功。装MySQL不难一条命令的事但把MySQL和Linux当作一个整体来理解和运维这才是长期能受益的能力。我后来接触过MySQL主从复制、MGR集群、分库分表中间件回头再看全部建立在“数据目录、binlog、配置参数、权限体系”这些基础概念之上。主从复制不知道binlog是什么你连为什么会有主从延迟都无法理解权限体系不懂GRANT语法多写一个账号就可能把数据库暴露出去。最后送你一个小技巧每次安装完MySQL把配置文件、初始化过程、遇到的问题记到自己的笔记里分门别类。我自己的笔记里积累了近十年的各种数据库异常案例处理过的坑多了未必能记住每个细节但笔记能让我在几秒内找到类似的解决方案。这条习惯我建议你从一开始就养成。