mysql 看不到mysql库_MySQL 5.1 执行show databases没有mysql库

同事反映，在mysql的一套测试库中执行'show databases;'命令，看不到mysql库

-bash-4.1$ mysql -uroot -p

Enter password:

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.1.73    |

+-----------+

1 row in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| test               |

+--------------------+

2 rows in set (0.00 sec)

查看报错日志，没有什么异常

查看数据文件目录里面的文件和相关操作系统权限，正常，数据文件的目录也正确

mysql> show variables like '%data%';

+----------------------------+------------------------+

| Variable_name              | Value                  |

+----------------------------+------------------------+

| character_set_database     | latin1                 |

| collation_database         | latin1_swedish_ci      |

| datadir                    | /var/lib/mysql/        |

| innodb_data_file_path      | ibdata1:10M:autoextend |

| innodb_data_home_dir       |                        |

| innodb_stats_on_metadata   | ON                     |

| max_length_for_sort_data   | 1024                   |

| max_long_data_size         | 1048576                |

| myisam_data_pointer_size   | 6                      |

| skip_show_database         | OFF                    |

| updatable_views_with_limit | YES                    |

+----------------------------+------------------------+

11 rows in set (0.00 sec)

mysql> exit

Bye

-bash-4.1$ cd /var/lib/mysql

-bash-4.1$ ls -l

total 28716

-rw-rw----. 1 mysql mysql 18874368 Mar 14 18:02 ibdata1

-rw-rw----. 1 mysql mysql  5242880 Mar 14 18:02 ib_logfile0

-rw-rw----. 1 mysql mysql  5242880 Jul 21  2016 ib_logfile1

drwx------. 2 mysql mysql    12288 Oct 19 12:04 merlin

drwx------. 2 mysql mysql     4096 Sep 29  2014 monitor_gui

drwx------. 2 mysql mysql     4096 Mar 26  2014 mysql

srwxrwxrwx  1 mysql mysql        0 Mar 14 17:57 mysql.sock

drwx------. 2 mysql mysql    12288 Nov 24  2015 nacoma

drwx------. 2 mysql mysql     4096 Nov  4  2014 ngmdb

drwx------. 2 mysql mysql     4096 Mar 26  2014 test

drwx------. 2 mysql mysql     4096 Oct 19 12:06 trapper

在网上查了一下相关的案例，发现与权限表mysql.user表有关

解决方法：

关闭数据库

mysqladmin -u root -p shutdown

以跳过权限认证方式启动mysql

mysqld_safe --defaults-file=/etc/my.cnf--skip-grant-tables&

删除mysql.user表中host名为'localhost'的记录

mysql> use mysql

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

Query OK, 7 rows affected (0.00 sec)

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> commit;

Query OK, 0 rows affected (0.00 sec)

再次重启数据库后，正常

mysqladmin -u root -p shutdown

mysqld_safe --defaults-file=/etc/my.cnf &

更改root密码

mysqladmin -u root -proot password djg345