因为之前root安全设置问题，把root的select_priv, update_priv, insert_priv, delete_priv权限都给下掉了。
现在要grant 修改非root用户权限的时候发现修改不了，才发现是root权限的问题。
1、服务器mysql服务
修改mysql配置文件
[root@localhost ~] vi /etc/my.cnf
mysqld新增skip-grant-tables，wq保存退出
[mysqld]
skip-grant-tables
重启mysql服务
service restart mysql
进入mysql
mysql -u -p
查看mysql用户表信息，发现root没有CURD的权限
mysql> use mysql;
mysql> select * from user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | N | N | N | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *19718549ADAF2CCA50E0767A7133A921737836DF | N | 2021-12-08 10:19:18 | 180 | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2021-12-08 10:19:12 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2021-12-08 10:19:12 | NULL | Y |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set (0.00 sec)
发现问题直接update，修改它，如果其它权限没了，也可以修改
mysql> update `user` set select_priv='Y',insert_priv='Y',update_priv='Y',delete_priv='Y' where user='root';
mysql> select * from `user`;
mysql> exit;
修改my.cnf，注释掉skip-grant-tables
[mysqld]
#skip-grant-tables
重启mysql服务
service restart mysql
登录root账号，就可以用了
mysql -uroot -p
2、docker mysql服务
其实和服务器的修改方法一样
查看my.cnf挂载情况，如果已经知道了my.cnf的挂载位置，就不用执行这一步。
找到my.cnf配置文件，这边的挂载到宿主机的配置在/opt/mysql/config/my.cnf
注意 5d15960f2861 是我自己的 CONTAINER ID
[root@localhost ~] docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5d15960f2861 mysql:5.7.36 "docker-entrypoint.s…" 47 hours ago Up 51 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
09b2d3a28a58 redis:6.2.6 "docker-entrypoint.s…" 47 hours ago Up About an hour 6379/tcp, 0.0.0.0:6380->6380/tcp redis
[root@localhost ~] docker inspect 5d15960f2861 | grep Mounts -A 50
"Mounts": [
.......
{
"Type": "bind",
"Source": "/opt/mysql/config/my.cnf",
"Destination": "/etc/mysql/my.cnf",
"Mode": "rw",
"RW": true,
"Propagation": "rprivate"
},
......
],
......
[root@localhost ~] vi /opt/mysql/config/my.cnf
如果my.cnf没有挂载出来，那就进容器里面改my.cnf了，注意 5d15960f2861 是我自己的 CONTAINER ID
[root@localhost ~] docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5d15960f2861 mysql:5.7.36 "docker-entrypoint.s…" 47 hours ago Up 51 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
09b2d3a28a58 redis:6.2.6 "docker-entrypoint.s…" 47 hours ago Up About an hour 6379/tcp, 0.0.0.0:6380->6380/tcp redis
[root@localhost ~] docker exec -it 5d15960f2861 /bin/bash
root@5d15960f2861:/ vi 容器里的的my.cnf文件
root@5d15960f2861:/ exit
my.cnf 中的mysqld新增skip-grant-tables，wq保存退出
[mysqld]
skip-grant-tables
重启docker mysql服务
[root@localhost ~] docker restart 5d15960f2861
进入docker mysql容器的mysql服务
[root@localhost ~] docker exec -it 5d15960f2861 mysql -u -p
查看mysql用户表信息，发现root没有CURD的权限
mysql> use mysql;
mysql> select * from user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | N | N | N | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *19718549ADAF2CCA50E0767A7133A921737836DF | N | 2021-12-08 10:19:18 | 180 | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2021-12-08 10:19:12 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2021-12-08 10:19:12 | NULL | Y |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set (0.00 sec)
发现问题直接update，修改它，如果其它权限没了，也可以修改
mysql> update `user` set select_priv='Y',insert_priv='Y',update_priv='Y',delete_priv='Y' where user='root';
mysql> select * from `user`;
mysql> exit;
修改my.cnf，注释掉skip-grant-tables
[mysqld]
#skip-grant-tables
重启docker mysql服务
[root@localhost ~] docker restart 5d15960f2861
登录root账号，就可以用了
docker exec -it 5d15960f2861 mysql -uroot -p
网友评论