mysql使用報(bào)錯(cuò)1142(42000)的問(wèn)題及解決

更新時(shí)間：2023年08月30日 09:14:12 作者：Ahri-情書

這篇文章主要介紹了mysql使用報(bào)錯(cuò)1142(42000)的問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

mysql使用報(bào)錯(cuò)1142(42000)

在學(xué)習(xí)mysql的時(shí)候，一頓蜜汁操作，再次使用mysql的時(shí)候發(fā)現(xiàn)，不管用啥子命令，都出現(xiàn)了一個(gè)報(bào)錯(cuò)

mysql> select user,password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ‘root’@‘localhost’ for table ‘user’

看了一下報(bào)錯(cuò)信息，權(quán)限不夠。。。

那就是沒有權(quán)限了，so，給他權(quán)限就好了

step01

退出數(shù)據(jù)庫(kù)并且關(guān)閉mysql服務(wù)

 mysql> quit
 Bye
 [root@jinch ~]# /etc/init.d/mysqld stop
 Shutting down MySQL.. SUCCESS!

step02

安全模式啟動(dòng)mysql，root用戶登錄

 [root@jinch ~]# mysqld_safe --skip-grant-tables &
 [root@jinch ~]# mysql -uroot -p123 mysql

step03

切換數(shù)據(jù)庫(kù)&查看表信息中的root用戶的localhost權(quán)限

 mysql> use mysql;
 Database changed
 mysql> show tables;
 +---------------------------+
 | Tables_in_mysql           |
 +---------------------------+
 | columns_priv              |
 | db                        |
 | event                     |
 | func                      |
 | general_log               |
 | help_category             |
 | help_keyword              |
 | help_relation             |
 | help_topic                |
 | innodb_index_stats        |
 | innodb_table_stats        |
 | ndb_binlog_index          |
 | plugin                    |
 | proc                      |
 | procs_priv                |
 | proxies_priv              |
 | servers                   |
 | slave_master_info         |
 | slave_relay_log_info      |
 | slave_worker_info         |
 | slow_log                  |
 | tables_priv               |
 | time_zone                 |
 | time_zone_leap_second     |
 | time_zone_name            |
 | time_zone_transition      |
 | time_zone_transition_type |
 | user                      |
 +---------------------------+
 28 rows in set (0.00 sec)
 mysql> select * from user where user='root' and host='localhost'\G;
 *************************** 1. row ***************************
                   Host: localhost
                   User: root
               Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
            Select_priv: N
            Insert_priv: N
            Update_priv: N
            Delete_priv: N
            Create_priv: N
              Drop_priv: N
            Reload_priv: N
          Shutdown_priv: N
           Process_priv: N
              File_priv: N
             Grant_priv: N
        References_priv: N
             Index_priv: N
             Alter_priv: N
           Show_db_priv: N
             Super_priv: N
  Create_tmp_table_priv: N
       Lock_tables_priv: N
           Execute_priv: N
        Repl_slave_priv: N
       Repl_client_priv: N
       Create_view_priv: N
         Show_view_priv: N
    Create_routine_priv: N
     Alter_routine_priv: N
       Create_user_priv: N
             Event_priv: N
           Trigger_priv: N
 Create_tablespace_priv: N
               ssl_type: 
             ssl_cipher: 
            x509_issuer: 
           x509_subject: 
          max_questions: 0
            max_updates: 0
        max_connections: 0
   max_user_connections: 0
                 plugin: mysql_native_password
  authentication_string: NULL
       password_expired: N
 1 row in set (0.00 sec)
 ERROR: 
 No query specified

這里發(fā)現(xiàn)全部都是N ，表示root用戶本地登陸沒有權(quán)限

step04

修改root用戶的localhost權(quán)限（兩種寫法）

寫法1：

mysql> update mysql.user set Grant_priv='Y',Super_priv='Y' where user='root';
mysql> flush privileges;
mysql>grant all on *.* to 'root'@'localhost';

寫法2：

 mysql> update user set `Insert_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Update_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Delete_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Create_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Drop_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Reload_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Shutdown_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Process_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `File_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Grant_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `References_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Index_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Alter_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Show_db_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Super_priv` ='Y',`Create_tmp_table_priv` = 'Y' where user='root'' and host='localhost';
 mysql> update user set `Lock_tables_priv` ='Y' where user='root' and host='localhost';  
 mysql> update user set `Execute_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Repl_slave_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Repl_client_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Create_view_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Show_view_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Create_routine_priv` ='Y' where user='root' and host='localhost'';
 mysql> update user set `Alter_routine_priv` ='Y' where user='root' and host='localhost';;
 mysql> update user set `Create_user_priv` ='Y' where user='root' and host='localhost'; 
 mysql> update user set `Event_priv` ='Y' where user='root' and host='localhost';
 mysql> update user set `Trigger_priv` ='Y' where user='root' and host='localhost';
 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

我這里有點(diǎn)傻。。。自己一個(gè)一個(gè)敲了一遍，可以直接用‘,’ 分割一次寫完的，，，

step05

退出&重啟&登陸

 mysql> quit
 Bye
 [root@jinch ~]# /etc/init.d/mysqld restart
 Shutting down MySQL.. SUCCESS! 
 Starting MySQL.. SUCCESS! 
 [root@jinch ~]# mysql -uroot -p123

step06

切換庫(kù)

 mysql> use mysql;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 Database changed

step07

查看表信息

 mysql> select * from user\G;
 *************************** 1. row ***************************
                   Host: localhost
                   User: root
               Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
            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: Y
 Create_tablespace_priv: N
               ssl_type: 
             ssl_cipher: 
            x509_issuer: 
           x509_subject: 
          max_questions: 0
            max_updates: 0
        max_connections: 0
   max_user_connections: 0
                 plugin: mysql_native_password
  authentication_string: NULL
       password_expired: N
 1 row in set (0.01 sec)
 ERROR: 
 No query specified
 權(quán)限已經(jīng)基本都有了

測(cè)試一下

 mysql> create database jinc;
 Query OK, 1 row affected (0.00 sec)
 mysql> select user,host from mysql.user;
 +------+-----------+
 | user | host      |
 +------+-----------+
 | root | localhost |
 +------+-----------+
 1 row in set (0.00 sec)
 mysql> drop database jinc;
 Query OK, 0 rows affected (0.00 sec)

好了，基本的權(quán)限又回來(lái)了