Mysql – Reset the root password, now I can’t see the main database

MySQL

I was getting access denied for root@localhost. To resolve this, I stopped the mysql services, ran mysqld –install and mysqld –initialized, added skip-tables in the my.ini file and was able to log back in with the root user got the password to reset. Cleared the skipped-tables from the my.ini file. Flush the privileges, granted the root all access. Put in the command "show databases;" but it only shows these databases and its missing one.

information_schema
mysql
performance_schema
sys

I looked at the my.ini file to see where the datadir was, looked at the path and I see the folder and the ibd file where the database resides, but it still does not show in MySQL. Any help on this would be greatly appreciated.

Best Answer

MySQL --initialize installs a clean data directory. It seems like that's what you got.

There may be a previous idb file there but its not connected to the database.

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

Mysql – Can’t access MySQL after sourcing a database and reboot

Oh, you nailed it!

It seems that my dump actually contained mysql.user data, so that after loading the database, I could only connect with these distant login information. Taking a look at this mysql.user table, it appears that these login were specified, not for localhost, but the distant host.

Anyway... Knowing this, I've been able to connect to mysql client, then reset these login info :

mysql –u root –p [distant password]
use mysql.user UPDATE mysql.user
SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
quit

I finally deleted these distant host lines from phpmyadmin, to be sure not to mess anything up.

Thanks a lot for your help !

Best Answer

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

Mysql – Can’t access MySQL after sourcing a database and reboot

Related Question