mysql故障案例_mysql故障案例

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

###永久修改参数

[[email protected] ~]# vim /etc/my.cnf

[mysqld]

max_allowed_packet = 16M

mysql 重启生效

原文：http://blog.51cto.com/2416402119/2169953