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用户表，权限问题解决！
