Ubuntu MySQL常见问题如何解决

Ubuntu 上 MySQL 常见问题与排查步骤

一 安装与登录

• 安装与启动
  • 更新索引并安装：sudo apt update && sudo apt install mysql-server
  • 检查状态：sudo systemctl status mysql；未运行则：sudo systemctl start mysql
  • 设置开机自启：sudo systemctl enable mysql
• 安全初始化与登录
  • 运行安全向导：sudo mysql_secure_installation（设置 root 密码、移除匿名用户、禁止远程 root 等）
  • 本地登录：mysql -u root -p
  • 若安装时未设置密码或本地认证异常，可直接进入：sudo mysql，然后执行
    • ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’;
    • FLUSH PRIVILEGES;
• 远程登录准备
  • 修改 root 允许远程：UPDATE mysql.user SET host=‘%’ WHERE user=‘root’; FLUSH PRIVILEGES;
  • 开放防火墙：sudo ufw allow 3306/tcp
  • 注意：生产环境不建议直接开放 root 远程，建议创建专用账号并限制来源 IP

二 服务无法启动与配置错误

• 快速定位
  • 查看服务状态与日志：sudo systemctl status mysql、sudo journalctl -xe、sudo tail -n 50 /var/log/mysql/error.log
• 常见修复
  • 权限与目录：数据目录属主应为 mysql:mysql，如：sudo chown -R mysql:mysql /var/lib/mysql
  • 配置与 PID/Socket：检查 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 中 pid-file、socket 路径是否存在且一致；必要时修正后重启
  • 配置不存在或权限错误：确认 /etc/mysql/my.cnf 存在，权限 644、属主 mysql:mysql；若缺失可新建基础配置并重启
  • 安装/升级中断：执行 sudo dpkg --configure -a 完成配置
  • 表损坏：使用 mysqlcheck --all-databases --auto-repair 或引擎自带工具修复
  • 彻底重配（会重置配置与数据，谨慎）：先备份数据，再 sudo apt purge mysql-server mysql-client 后重装

三 认证与远程连接问题

• 认证插件不兼容
  • 现象：客户端报 Authentication plugin ‘caching_sha2_password’ cannot be loaded
  • 处理：在 MySQL 中将会话或目标用户的认证方式改为 mysql_native_password
    • 示例：ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’; FLUSH PRIVILEGES;
• 忘记 root 密码
  • 编辑 /etc/mysql/mysql.conf.d/mysqld.cnf，在 [mysqld] 下临时加入 skip-grant-tables
  • 重启：sudo systemctl restart mysql
  • 无密码登录后重置：ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’; FLUSH PRIVILEGES;
  • 移除 skip-grant-tables 并重启
• Navicat/客户端连接失败
  • 典型错误 1130：Host ‘xxx’ is not allowed to connect to this MySQL server
  • 处理：在 mysql 库中将相应用户的 host 改为 % 或指定网段，再 FLUSH PRIVILEGES;
  • 确认监听端口：sudo netstat -tap | grep mysql 应看到 3306 处于 LISTEN
  • 开放云服务器安全组与系统防火墙 3306/tcp

四 字符集与时区及常用参数

• 字符集
  • 编辑 /etc/mysql/mysql.conf.d/mysqld.cnf
    • 在 [mysqld] 加入：character_set_server=utf8mb4
    • 在 [client] 加入：default-character-set=utf8mb4
  • 重启：sudo systemctl restart mysql
  • 验证：SHOW VARIABLES LIKE ‘character_set%’;
• 时区
  • 编辑 /etc/mysql/mysql.conf.d/mysqld.cnf
    • 在 [mysqld] 加入：default-time_zone=‘+8:00’
  • 重启后验证：SHOW VARIABLES LIKE ‘%time_zone%’;
• 包开发与编译依赖
  • 缺少 mysql.h：安装 sudo apt-get install libmysqlclient-dev
• 大包与导入导出
  • 提升单包上限：在 [mysqld] 加入 max_allowed_packet=900M（按实际调整），重启
  • 验证：SHOW VARIABLES LIKE ‘%max_allowed_pack%’;

五 性能优化与维护

• 关键配置（示例为 16GB 内存，按实际调整）
  • innodb_buffer_pool_size=8G～12G（约内存的 50%～70%）
  • innodb_log_file_size=256M
  • innodb_flush_log_at_trx_commit=1（安全性优先；追求吞吐可评估 0/2）
  • max_connections（结合连接池与业务峰值）
• 索引与查询
  • 使用 EXPLAIN 分析执行计划；避免 **SELECT ***；为高频条件列建立合适索引（含复合索引，遵循最左前缀）
• 维护与监控
  • 定期 ANALYZE TABLE / OPTIMIZE TABLE；启用慢查询日志定位瓶颈
  • 监控工具：MySQL Performance Schema、PMM、企业监控等
  • 注意：MySQL 8.0 已移除查询缓存（query cache），无需再配置 query_cache_size