Mysql问题大合集
一，安装问题
1.开启报错
1.缺少包文件
[root@localhost ~]# /usr/sbin/mysqld start /usr/sbin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决：安装libaio
[root@localhost ~]# yum -y install libaio
2.配置文件用户没有权限
[ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root! 和 2022-12-15 11:05:09 37001 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root! 2022-12-15 11:05:09 37001 [ERROR] Aborting
解决：进入配置文件添加user用户
vi /etc/my.cnf user=mysql
3.初始化报错
[root@localhost ~]# mysql_install_db -bash: /usr/bin/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录
解决：缺少包文件
yum -y install perl perl-devel
4.启动报错1
[root@localhost ~]# tail -f /var/log/mysqld.log 2022-12-15 11:09:25 39331 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
解决：初始化数据库
mysql_install_db --user=mysql --ldata=/var/lib/mysql
5.启动报错2
[ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 mysql服务已开启再次重复开启也会报，属于正常
解决：杀死mysql进程
二，服务使用报错
一，登录错误
1.其他用户登录报错
MySQL ERROR 1045 (28000): Access denied for user ‘username‘@‘localhost‘ (using password: YES)
原因：错误根本原因在于我们的mysql用户列表里面可能设置了一些user为空的用户信息
解决：删除空信息
SELECT user, host FROM mysql.user; #返回结果大致如下： +------+-----------+ | user | host | +------+-----------+ | | localhost | | root | 127.0.0.1 | | root | ::1 | | test1| % | +------+-----------+ 4 rows in set (0.00 sec) drop user ''@'localhost' #删除完后再次查看user表确认删除 SELECT user, host FROM mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | test1| % | +------+-----------+ 3 rows in set (0.00 sec)
2.创建用户错误
create user 'jumpserver'@'%' identified by 'www.yuchaoit.cn'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决方法：
刷新权限表即可
mysql> flush privileges;
三，mysql忘记密码操作
1.在[mysqld]下添加skip-grant-tables，保存即可。
vi /etc/my.cnf skip-grant-tables
2.登录，修改密码
mysql -u root -p 不用输入密码，直接回车（出现Enter Password 也一样直接回车，即可登陆成功） update user set authentication_string=password(‘新密码’) where user=‘root’; flush privileges;
3.重启mysql解决
