温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

mysql故障案例

发布时间:2020-06-07 09:15:20 来源:网络 阅读:779 作者:赵如俊 栏目:MySQL数据库

##错误: ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'dede'

mysql> create database dede;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47299
Current database: NONE

Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on dede.* to 'dede'@'localhost' identified by 'dede123'; ##授权时执行失败
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47302
Current database: NONE

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'dede' ##拒绝访问的用户

解决:
mysql> select Grant_priv from mysql.user where Host='localhost'; ##N代表没有权限
+------------+
| Grant_priv |
+------------+
| N |
| N |
| N |
+------------+
3 rows in set (0.00 sec)

mysql> select Grant_priv from mysql.user where Host='127.0.0.1';
+------------+
| Grant_priv |
+------------+
| Y |
+------------+
1 row in set (0.00 sec)

mysql> update mysql.user set Grant_priv='Y' where Host='localhost';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47312
Current database: NONE

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select Grant_priv from mysql.user where Host='localhost';
+------------+
| Grant_priv |
+------------+
| Y |
| Y |
| Y |
+------------+
3 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on dede.* to 'dede'@'localhost' identified by 'dede123';
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'dede'

mysql> show variables like 'read_only'
-> ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47323
Current database: NONE

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_allowed_packet'; ##查看max_allowed_packet的值,默认是1MB
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47339
Current database: NONE

+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)
mysql> set global max_allowed_packet=1024102416; ##临时修改max_allowed_packet值为16MB,重启失效
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47341
Current database: NONE

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'max_allowed_packet'; ##查看.,临时已生效
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

mysql> grant all privileges on dede.* to 'dede'@'localhost' identified by 'dede123'; ###grant授权用户成功
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47347
Current database: NONE

Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

###永久修改参数
[root@118 ~]# vim /etc/my.cnf
[mysqld]
max_allowed_packet = 16M

mysql 重启生效

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI