Mysql failure case
# # error: 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'; # # failed to authorize
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' # # users denied access
Resolve:
Mysql > select Grant_priv from mysql.user where Host='localhost'; # # N means no permission
+-+
| | 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.01sec)
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'; # # check the value of max_allowed_packet. Default is 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; # # temporarily change the max_allowed_ Packet value to 16MB. Restart fails.
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'; # # View, temporarily effective
+-+ +
| | 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 authorized user succeeded
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
# permanently modify parameters
[root@118 ~] # vim / etc/my.cnf
[mysqld]
Max_allowed_packet = 16m
Mysql restart takes effect
