mysql error 1227 42000_mysql导入报错

mysql error 1227 42000_mysql导入报错今天在学习mysql的时候,一顿蜜汁操作,再次使用mysql的时候发现,不管用啥子命令,都出现了一个报错mysql>selectuser,passwordfrommysql.user;ERROR1142(42000):SELECTcommanddeniedtouser‘root’@‘localhost’fortable‘user’看了一下报错信息,权限不够…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

今天在学习mysql的时候,一顿蜜汁操作,再次使用mysql的时候发现,不管用啥子命令,都出现了一个报错
mysql> select user,password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ‘root’@‘localhost’ for table ‘user’
看了一下报错信息,权限不够。。。那就是没有权限了,so,给他权限就好了

step01

退出数据库并且关闭mysql服务
 mysql> quit
 Bye
 [root@jinch ~]# /etc/init.d/mysqld stop
 Shutting down MySQL.. SUCCESS! 

step02

安全模式启动mysql,root用户登录
 [root@jinch ~]# mysqld_safe --skip-grant-tables &
 [root@jinch ~]# mysql -uroot -p123 mysql

step03

切换数据库&查看表信息中的root用户的localhost权限
 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
这里发现全部都是N ,表示root用户本地登陆没有权限

step04

修改root用户的localhost权限(两种写法)
写法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)
我这里有点傻。。。自己一个一个敲了一遍,可以直接用‘,’ 分割一次写完的,,,

step05

退出&重启&登陆
 mysql> quit
 Bye
 [root@jinch ~]# /etc/init.d/mysqld restart
 Shutting down MySQL.. SUCCESS! 
 Starting MySQL.. SUCCESS! 
 [root@jinch ~]# mysql -uroot -p123

step06

切换库
 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
 
 权限已经基本都有了
测试一下
 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)

好了,基本的权限又回来了

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/187024.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • iOS 和 swift 中常见的 Int、Int8、Int16、Int32和 Int64介绍「建议收藏」

    iOS 和 swift 中常见的 Int、Int8、Int16、Int32和 Int64介绍「建议收藏」经过一天的敲代码,小蜜蜂我已经也是人困马乏了。喂喂喂,蜜蜂,哪来的“马”啊?额。。。比喻!比喻!比喻!懂不懂?那既然这么累了,今天又为什么来写博客了?就像艾青诗人说的那句,为什么我眼里常含泪水?因为我对这土地爱得深沉。。。换成我的话就是,为什么我累了还写着博客?因为我对这代码爱得深沉。。。哈哈哈说完了上面的这些,那今天继续说说我的一点点滴事情,那就是如题目所说的

  • VIM查找关键字方法「建议收藏」

    VIM查找关键字方法「建议收藏」单击“Ctrl”+”:”进入命令行模式打“/”+“关键字”回车就可以查找了上一个匹配:”N“,下一个匹配:”n”(next)

  • 用python打包exe应用程序-PyInstaller

    1、PyInstaller简介PyInstaller是一个跨平台的Python应用打包工具,支持Windows/Linux/MacOS三大主流平台,能够把Python脚本及其所在的…

  • HOJ2275 Number sequence

    HOJ2275 Number sequence

  • 【Linux】open函数的参数和作用

    【Linux】open函数的参数和作用一、open函数用来干什么open函数在Linux下一般用来打开或者创建一个文件,我们可以根据参数来定制我们需要的文件的属性和用户权限等各种参数。二、open函数的定义和参数我们首先来看下open函数在Linux下的定义#include#include#includeintopen(constchar*pathnam

  • python函数闭包_python闭包的使用场景

    python函数闭包_python闭包的使用场景闭包首先了解一下:如果在一个函数的内部定义了另一个函数,外部的我们叫他外函数,内部的我们叫他内函数。在一个外函数中定义了一个内函数,内函数里运用了外函数的临时变量,并且外函数的返回值是内函数的引用

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号