Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.