MySQL 5.1Executing show databases has no mysql library
Colleagues reported that when the 'show databases;' command was executed in a set of mysql test libraries, the mysql library could not be seen.
-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)
Check the error log. There is nothing unusual.
Check the files in the data file directory and related operating system permissions, normal, the data file directory is also correct
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
I checked the relevant cases on the Internet and found that it was related to the permission table mysql.user table.
Solution:
Shut down the database
Mysqladmin-u root-p shutdown
Start mysql by skipping permission authentication
Mysqld_safe-defaults-file=/etc/my.cnf-skip-grant-tables &
Delete the record in the mysql.user table with the host name '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)
After restarting the database again, it is normal.
Mysqladmin-u root-p shutdown
Mysqld_safe-- defaults-file=/etc/my.cnf &
Change the root password
Mysqladmin-u root-proot password djg345
