MySQL报错总结

MySQL报错总结

一、MySQL权限问题

[root@mysql etc]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize
2019-04-18T10:54:00.803228+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-04-18T10:54:00.807444+08:00 0 [ERROR] Could not open file '/data/mysql/mysql3306/logs/error.log' for error logging: Permission denied
2019-04-18T10:54:00.807484+08:00 0 [ERROR] Aborting

 

解决办法:请根据自己的环境授权目录

chown -R mysql.mysql /path/mysql 
chown -R mysql.mysql /path/data/

二、常见MySQL初始化报错

/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决办法:

yum -y install libaio

通过ldd命令查看so文件缺少

[root@mysql-5 mysql3306]# ldd /usr/local/mysql/bin/mysqld
	linux-vdso.so.1 =>  (0x00007ffffc270000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8aac9d5000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007f8aac7d3000)
	libnuma.so.1 => /lib64/libnuma.so.1 (0x00007f8aac5c6000)
	libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f8aac38f000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f8aac18b000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f8aabf82000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f8aabc7a000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f8aab978000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f8aab761000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f8aab39e000)
	/lib64/ld-linux-x86-64.so.2 (0x000056067c0fb000)
	libfreebl3.so => /lib64/libfreebl3.so (0x00007f8aab19b000)

 

四、MySQL启动同时使用一个配置文件进程报错

2019-04-18T22:13:17.854048+08:00 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-04-18T22:13:17.854107+08:00 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

 

①、解决办法:

[root@mysql data]# cat mysql.pid 
78783

 

查看当前MySQL当前的进程记录PID号,保留当前的PID号,杀掉最新的即可

 

②、解决办法:

[root@mysql data]# ps -ef|grep mysql
mysql     78783      1  0 19:08 ?        00:00:11 /usr/local/mysql/bin/mysqld
mysql    125815  94476  0 22:27 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

 

利用ps -fe|grep mysql查看当前MySQL最新的进程PID号kill -9 PID号杀掉即可。

 

 

 

五、MySQL密码过期

[root@localhost mysql3306]# mysql -uroot -p -S /data/mysql3306/tmp/mysql.sock
Enter password: 
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

 

解决办法:

方法一、my.cnf配置文件下添加skip-grant-tables跳过权限认证问题。

安全起见同时加入skip-networking关闭MySQL的TCP/IP连接方式。

[msyqld]
skip-grant-tables
skip-networking

说明:添加成功以后关闭MySQL重启MySQL不需要输入密码直接登陆

然后修改MySQL密码

1、update修改密码如下

mysql> use mysql                            #进入MySQL用户库
Database changed
mysql> show tables;                         # 查看当前库下的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

查看表结构以下是MySQL5.7版本密码加密方式
mysql> desc user
| authentication_string  | text                              | YES  |     | NULL                 

密码修改方式

update mysql.user set authentication_string=password('新密码') where user='root';
flush privileges;

2、错误1820 (HY000):在执行此语句之前,必须使用ALTER USER语句重置密码。

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决办法:

alter user root@'localhost' identified by '新密码';
flush privileges;

 

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
百度已收录
Linux
  • 版权声明:本站原创文章,于2019年4月18日,由 发表,共 2925 字。
  • 转载请注明:MySQL报错总结 |

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:2   其中:访客  1   博主  1

    • avatar 阿丑 0

      骗字错了