天下事有难易乎?为之,则难者亦易矣;不为,则易者亦难矣。人之为学有难易乎?学之,则难者亦易矣;不学,则易者亦难矣。

Linux下新建Mysql数据库,看不到mysql库和user用户表,权限问题解决

往事如烟 768次浏览 0个评论 扫描二维码

Linux下新建的mysql数据,进入mysql的时候,看不到mysql库,一般都是因为当前用户权限不足造成的。下面记录下自己的解决办法。

系统:CentOS 5.6、Mysql Server version: 5.1.71

解决方法

登录mysql后,因权限不足,看不到mysql库。

[root@hadoop001 ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye

停止mysql,使用口令mysqld_safe –skip-grant-table。

[root@hadoop001 ~]# service mysqld stop
Stopping mysqld: [ OK ]
[root@hadoop001 ~]# mysqld_safe --skip-grant-table
180719 09:37:09 mysqld_safe Logging to '/var/log/mysqld.log'.
180719 09:37:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

再打开一个服务窗口进入mysql库

发送到
[root@hadoop001 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看mysql所有库,并使用mysql库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

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
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| %         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)

查看如果存在root用户host为‘localhost’,请先删除,然后在添加

mysql> delete from user where host='root' and host='localhost';

添加root用户前,建议一下user表中,看下一共多少列,因为不同版本数据库,user表字段会有不同,将字段类型为type的全部设置为‘Y’,我这里一共是39个字段。

执行sql语句,添加root用户字段信息,并设置root用户密码为123456,执行flush privileges,退出mysql

mysql> INSERT INTO `user` VALUES ('localhost','root','','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','Y','Y','Y','','','','',0,0,0,0);
Query OK, 1 row affected (0.00 sec)

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| %         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | root |                                           |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> update user set password=password("123456") where user="root" and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password |
+-----------+------+-------------------------------------------+
| %         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye

关闭两个服务窗口,重新连接服务器,重启mysql数据库

[root@hadoop001 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@hadoop001 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

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
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password |
+-----------+------+-------------------------------------------+
| %         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

ok,到这里,已经可以正常启动,操作mysql数据库了。

如果需要添加用户,可以另一篇博文,通过添加用户,和授权用户的方式,控制远程登录mysql和添加其他用户操作msyql表。


ITZOO版权所有丨如未注明 , 均为原创丨转载请注明来自IT乐园 ->Linux下新建Mysql数据库,看不到mysql库和user用户表,权限问题解决
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址