Ubuntu MySQL常见故障及解决方法
sudo systemctl status mysql确认MySQL服务是否运行(显示“active (running)”为正常)。若未运行,尝试启动服务:sudo systemctl start mysql。sudo tail -n 50 /var/log/mysql/error.log查看详细错误信息(如配置文件语法错误、端口冲突等),根据日志定位问题。/etc/mysql/mysql.conf.d/mysqld.cnf(或/etc/mysql/my.cnf)是否有语法错误(如多余的空格、错误的参数值),修改后重启服务:sudo systemctl restart mysql。sudo netstat -tulnp | grep 3306查看3306端口是否被其他进程占用。若被占用,可停止占用进程(sudo kill -9 <PID>)或修改MySQL端口(在配置文件中更改port参数)。sudo apt remove --purge mysql-server mysql-client mysql-common && sudo apt install mysql-server。sudo systemctl status mysql确保服务已启动。sudo ufw status查看防火墙状态,若未允许MySQL端口(默认3306),执行sudo ufw allow 3306/tcp放行。bind-address参数(在mysqld.cnf中),若为127.0.0.1则仅允许本地连接,需改为0.0.0.0以允许远程访问,修改后重启服务。mysql -u root -p),执行SELECT User, Host FROM mysql.user;查看用户权限。若用户仅能从localhost访问,需授予权限:GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;(%表示允许任意主机)。ping <服务器IP>测试客户端与服务器的网络连接,确保网络通畅。sudo systemctl stop mysql。sudo mysqld_safe --skip-grant-tables &。mysql -u root。ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码'; FLUSH PRIVILEGES;。exit,然后sudo systemctl restart mysql。UPDATE mysql.user SET plugin='mysql_native_password' WHERE User='root'; FLUSH PRIVILEGES;(适用于MySQL 8.0+版本)。sudo mysqlcheck --all-databases --auto-repair检查并修复所有数据库的表。若仅需修复特定数据库,替换为sudo mysqlcheck -u root -p 数据库名 --auto-repair(需输入密码)。df -h查看磁盘使用情况,若/分区使用率过高,可通过以下命令清理:
sudo apt autoremove。sudo apt clean。sudo truncate -s 0 /var/log/mysql/error.log(谨慎操作,需备份重要日志)。sudo mysql --help | grep "Default options" -A 1找到配置文件路径(通常为/etc/mysql/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf),然后用sudo nano编辑文件,确保无语法错误(如重复的port参数、错误的datadir路径)。sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak),然后重新安装MySQL(sudo apt install --reinstall mysql-server)恢复默认配置。/var/lib/mysql,需确保其归属正确。执行sudo chown -R mysql:mysql /var/lib/mysql(将目录所有者设为mysql用户和组),sudo chmod -R 755 /var/lib/mysql(设置目录权限为755),然后重启服务:sudo systemctl restart mysql。