mysql root用户关闭写权限后无法修改回来

来源：互联网发布：韩国漫画下载软件编辑：程序博客网时间：2024/06/04 19:25

今天来说说我在mysql上踩的一个坑，河河河河河。。。。

今天因为要去搞主从库备份，但是有一个问题是，主从库备份的时候，要保持数据的一致（目前我的做法这里是这样的，应该有更好的方法可以修改）。因为我们的数据库是有好多人在一起用的，而且还有定时器在往里边刷数据。这真是一个蛋疼的时候。

这时候我先看了看我的数据库的链接，都是在进行什么操作。使用了命令：show processlist 去查看数据谷中的进程如何。

mysql> show processlist;+-------+-----------+---------------------+-----------+-------------+-------+-----------------------------------------------------------------------+------------------+| Id    | User      | Host                | db        | Command     | Time  | State                                                                 | Info             |+-------+-----------+---------------------+-----------+-------------+-------+-----------------------------------------------------------------------+------------------+|    43 | mysqluser | 172.16.10.191:46434 | NULL      | Binlog Dump | 39481 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             || 61524 | root      | 172.18.34.34:50288  | NULL      | Sleep       |  1232 |                                                                       | NULL             || 62167 | root      | 172.18.34.13:60514  | yree      | Sleep       |   850 |                                                                       | NULL             || 63458 | root      | 172.16.10.183:34377 | MEIZU_BRO | Sleep       |    31 |                                                                       | NULL             || 63461 | root      | 172.16.10.183:34395 | MEIZU_BRO | Sleep       |    24 |                                                                       | NULL             || 63462 | root      | 172.18.34.34:51643  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63463 | root      | 172.18.34.34:51644  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63464 | root      | 172.18.34.34:51645  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63465 | root      | 172.18.34.34:51646  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63466 | root      | 172.18.34.34:51647  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63467 | root      | 172.18.34.34:51648  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63468 | root      | 172.18.34.34:51649  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63469 | root      | 172.18.34.34:51650  | MEIZU_BRO | Sleep       |    21 |                                                                       | NULL             || 63470 | root      | 172.16.10.183:34418 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63471 | root      | 172.16.10.183:34419 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63472 | root      | 172.16.10.183:34420 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63473 | root      | 172.16.10.183:34421 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63474 | root      | 172.16.10.183:34422 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63475 | root      | 172.16.10.183:34423 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63476 | root      | 172.16.10.183:34424 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63477 | root      | 172.16.10.183:34425 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63478 | root      | 172.16.10.183:34426 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63479 | root      | 172.16.10.183:34428 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63480 | root      | 172.16.10.183:34429 | MEIZU_BRO | Sleep       |    19 |                                                                       | NULL             || 63481 | root      | 172.18.34.13:55538  | MEIZU_BRO | Sleep       |    17 |                                                                       | NULL             || 63482 | root      | 172.18.34.13:55536  | MEIZU_BRO | Sleep       |    17 |                                                                       | NULL             || 63483 | root      | 172.18.34.13:55537  | MEIZU_BRO | Sleep       |    17 |                                                                       | NULL             || 63484 | root      | 172.18.34.13:55540  | MEIZU_BRO | Sleep       |    17 |                                                                       | NULL             || 63485 | root      | 172.18.34.13:55541  | MEIZU_BRO | Sleep       |    17 |                                                                       | NULL             || 63486 | root      | 172.16.10.183:34439 | MEIZU_BRO | Sleep       |    14 |                                                                       | NULL             || 63487 | root      | 172.18.34.13:55775  | MEIZU_BRO | Sleep       |    12 |                                                                       | NULL             || 63488 | root      | 172.18.34.13:55776  | MEIZU_BRO | Sleep       |    12 |                                                                       | NULL             || 63489 | root      | 172.18.34.13:55777  | MEIZU_BRO | Sleep       |    12 |                                                                       | NULL             || 63490 | root      | 172.18.34.13:55787  | MEIZU_BRO | Sleep       |    12 |                                                                       | NULL             || 63491 | root      | 172.18.34.13:55788  | MEIZU_BRO | Sleep       |    12 |                                                                       | NULL             || 63492 | root      | 172.18.34.34:51659  | MEIZU_BRO | Sleep       |    11 |                                                                       | NULL             || 63493 | root      | 172.16.10.183:34453 | MEIZU_BRO | Sleep       |     9 |                                                                       | NULL             || 63494 | root      | 172.16.10.183:34454 | MEIZU_BRO | Sleep       |     9 |                                                                       | NULL             || 63495 | root      | 172.16.10.183:34455 | MEIZU_BRO | Sleep       |     9 |                                                                       | NULL             || 63496 | root      | 172.16.10.183:34456 | MEIZU_BRO | Sleep       |     9 |                                                                       | NULL             || 63497 | root      | 172.16.10.183:34457 | MEIZU_BRO | Sleep       |     9 |                                                                       | NULL             || 63498 | root      | 172.16.10.183:34458 | MEIZU_BRO | Sleep       |     9 |                                                                       | NULL             || 63499 | root      | 172.16.10.184:51669 | NULL      | Query       |     0 | NULL                                                                  | show processlist || 63500 | root      | 172.16.10.183:34473 | MEIZU_BRO | Sleep       |     4 |                                                                       | NULL             || 63501 | root      | 172.18.34.34:51662  | MEIZU_BRO | Sleep       |     1 |                                                                       | NULL             |+-------+-----------+---------------------+-----------+-------------+-------+-----------------------------------------------------------------------+------------------+45 rows in set (0.00 sec)mysql>

好的首先我看到，确实是有很多机子在对其进行操作的，虽然有很多都已经sleep掉了，但是以防万一，首先我先将root 的远程写权限给它干掉。这样的话，没有用本地去连接的话，就不可以修改数据库。

这个操作就是去掉root权限的插入和修改操作

revoke insert,update on *.* from root ;

查看一下权限再：

mysql> select * from mysql.user\G*************************** 1. row ***************************                  Host: %                  User: root              Password: *3A307C6C3EB8C91C40676C2428752F3616A5BF63           Select_priv: Y           Insert_priv: N           Update_priv: N           Delete_priv: Y           Create_priv: Y             Drop_priv: Y           Reload_priv: Y         Shutdown_priv: Y          Process_priv: Y             File_priv: Y            Grant_priv: Y       References_priv: Y            Index_priv: Y            Alter_priv: Y          Show_db_priv: Y            Super_priv: Y Create_tmp_table_priv: Y      Lock_tables_priv: Y          Execute_priv: Y       Repl_slave_priv: Y      Repl_client_priv: Y      Create_view_priv: Y        Show_view_priv: Y   Create_routine_priv: Y    Alter_routine_priv: Y      Create_user_priv: Y            Event_priv: Y          Trigger_priv: YCreate_tablespace_priv: Y              ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0           max_updates: 0       max_connections: 0  max_user_connections: 0                plugin:  authentication_string:

好的，下来一阵乱操作。最后在主从库修改好后，那么问题来咯。我要恢复我root用户的权限的时候。发现不可以了哦，说没有权限。因为恢复权限说白了实际上还是在用root用户去进行写操作，但是被我搞掉了==

mysql> grant insert,update on *.* to root@'%' IDENTIFIED by'mysqluser' ;ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

略尴尬。

这时候有点蒙蔽，后来去网上查了下，这种情况可以用安全模式去搞。
首先找到到自己的mysql目录下的bin下的mysqld_safe
mysqld_safe –skip-grant-tables &
使用了这个命令之后。就会出现两行日志。

[root@cloudmoban-184 bin]# ./mysqld_safe --skip-grant-tables 161207 09:53:24 mysqld_safe Logging to '/data/mysql/cloudmoban-184.err'.161207 09:53:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql

这时候重新开一个窗口。使用mysql -u root 登录进去。注意这里是不需要密码的，因为是安全模式。

[root@cloudmoban-184 ~]# mysql -u root Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 82Server version: 5.5.52-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

这时候在这里。我就想直接在这里将权限给上不就OK？
于是我就试了一下

mysql>grant insert,update on *.* to root@'%' IDENTIFIED by'mysqluser' ;ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statementmysql> grant all on *.* to root@'%';ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

河河河河河河河又是满面尴尬。
后来网上查了一下，可以这样搞

mysql> use mysqlDatabase changedmysql> update user set Insert_priv ='Y' where user = 'root';Query OK, 1 row affected (0.00 sec)Rows matched: 4  Changed: 1  Warnings: 0

这时候首先先关掉mysql一下，然后正常的重启，否则安全模式不能退出。

哈哈哈。终于好了，看一下权限试试。

mysql> select * from mysql.user\G*************************** 1. row ***************************                  Host: %                  User: root              Password: *3A307C6C3EB8C91C40676C2428752F3616A5BF63           Select_priv: Y           Insert_priv: Y           Update_priv: Y           Delete_priv: Y           Create_priv: Y             Drop_priv: Y           Reload_priv: Y         Shutdown_priv: Y          Process_priv: Y             File_priv: Y            Grant_priv: Y       References_priv: Y            Index_priv: Y            Alter_priv: Y          Show_db_priv: Y            Super_priv: Y Create_tmp_table_priv: Y      Lock_tables_priv: Y          Execute_priv: Y       Repl_slave_priv: Y      Repl_client_priv: Y      Create_view_priv: Y        Show_view_priv: Y   Create_routine_priv: Y    Alter_routine_priv: Y      Create_user_priv: Y            Event_priv: Y          Trigger_priv: YCreate_tablespace_priv: Y              ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0           max_updates: 0       max_connections: 0  max_user_connections: 0                plugin:  authentication_string:

嗯，心满意足。

0 0