Ubuntu MariaDB常见故障及解决方法
sudo systemctl status mariadb查看服务是否运行,若未运行会显示错误信息。sudo tail -n 50 /var/log/mysql/error.log定位具体错误(如配置错误、权限问题)。/var/lib/mysql)属主为mysql用户,执行sudo chown -R mysql:mysql /var/lib/mysql;目录权限设为755,执行sudo chmod -R 755 /var/lib/mysql。/etc/mysql/mariadb.conf.d/50-server.cnf(或/etc/my.cnf),确认bind-address、port等配置正确(如bind-address设为0.0.0.0允许远程连接)。df -h查看磁盘使用情况,清理不必要的文件(如/tmp目录)或扩容磁盘。sudo aa-disable /etc/apparmor.d/usr.sbin.mysqld),或修改/usr/lib/systemd/system/mariadb.service中的ProtectHome为false,然后重启服务。sudo systemctl status mariadb),未启动则执行sudo systemctl start mariadb。sudo ufw allow 3306/tcp,然后sudo ufw reload。/etc/mysql/mariadb.conf.d/50-server.cnf中的bind-address,若为127.0.0.1(仅本地连接),改为0.0.0.0(允许远程连接),重启服务。ping <服务器IP>测试网络连通性,telnet <服务器IP> 3306验证端口是否可达。mysql -u root -p),执行GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password'(%表示允许所有主机),然后FLUSH PRIVILEGES。sudo systemctl stop mariadb。sudo mysqld_safe --skip-grant-tables &(跳过权限检查)。mysql -u root),执行FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'(替换new_password为新密码)。sudo mysqladmin -u root -p shutdown关闭服务,sudo systemctl start mariadb正常启动。/etc/mysql/mariadb.conf.d/50-server.cnf中的bind-address设为0.0.0.0(允许远程连接),重启服务。sudo ufw allow 3306/tcp,sudo ufw reload。CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'(创建远程用户),GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'%'(授权指定数据库权限),FLUSH PRIVILEGES。ping <服务器IP>测试网络,telnet <服务器IP> 3306验证端口可达。STOP SLAVE;),跳过当前错误(SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;),重启同步(START SLAVE;)。SHOW SLAVE STATUS\G,检查Slave_IO_Running和Slave_SQL_Running是否为Yes,Last_Error字段查看具体错误。sudo systemctl status mariadb)。/var/run/mysqld/mysqld.sock是否存在,若不存在则创建符号链接(sudo ln -s /var/lib/mysql/mysql.sock /var/run/mysqld/mysqld.sock)。SELECT user, host FROM mysql.user;,确保用户的主机字段包含localhost(本地连接)或%(远程连接)。LOAD DATA INFILE '/data/file.txt' INTO TABLE table_name CHARACTER SET 'utf8mb4'(指定源文件编码);导入时确保表字符集与文件一致(如ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4)。/etc/mysql/mariadb.conf.d/50-server.cnf中添加[mysqld]部分,设置character-set-server = utf8mb4、collation-server = utf8mb4_unicode_ci,重启服务。mysqladmin flush-hosts -u root -p(临时解决)。SET GLOBAL max_connect_errors = 1000(临时调整);或在/etc/mysql/mariadb.conf.d/50-server.cnf中添加max_connect_errors = 1000(永久生效),重启服务。