Lost all my MySQL DB's, help
 

Help, I think I've corrupted all my databases, I'm not sure.
I was messing around in phpMyAdmin earlier & I think changed some permissions.
Now all of my MySQL databases are showing up in phpMyAdmin as empty, ie no tables in any of them. :cry:
Even my mysql database is empty so there is no root user in it containing my password to access any of the other databases.
Whats wierd though is all my databases seem to contain data when I view them from the command line using 'ls -l'
When I try access a database from the command line as root I can access the database but it says permission denied when i try to show the tables.

Has anybody got an idea whats going on here :confused:

Thanks in advance for any replies.

mysql_install_db is a script that can be run to replace your user/grant tables. It can be edited to make sure that it does only the things you want.

Here's is a whole section on the --skip-grant-tables option and resetting the root password.

I dont know what happened to your databases, but if they are intact and it's just permissions that are screwed up, you may be able to reinstate them once you can login as root.

Hi nxny, thanks for the reply, I have the root password, ie
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@hostname]# mysql mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
~~~~~~~~~~~~~~~~~~~~~~~~~~~
It's when I try to see what tables are there I get access denied.

Another thing, I reinstalled mysql & phpMyAdmin & still having the same problem.
phpMyAdmin & Webmin both claim that all my databases have no tables and all contain 0 bytes.I know there is data in /var/lib/mysql because I just put my /var/lib/mysql directory into a tar ball & it's over 3MB.

Any more ideas?

Thanks

Can anyone tell me why I am able to access my databases using my root password but when I try to update or view it I get 'access denied for root@localhost'
I thought the root user would be able to do anything to the DB.

any ideas anyone?

Thanks

rm -rf /var/lib/mysql/mysql

That will destroy your grant tables.

Use mysql_install_db to restore those grant tables and insert rows similar to the ones you already have in mysql.user, mysql.db etc.

nxny, now why didnt I think of that :rolleyes:
Thank you VERY much, it works now & I havent lost any of my databases :D

Thanks again, you saved me some major headaches ;)

You're welcome.

I also have almost identical problem. The only difference is that I used RPMs to install MySQL on RedHat distribution. When I upgraded from 3.23 to 4 all tables from databases disapeared. The files were also present on the disks but the client could not access them.

When I downgraded to 3.23 again all the tables were visible again. I have performed that upgrade/downgrade procedure a few times always with the same result. I have tried everything I could remember (converting all ISAM tables to MyISAM,...).

The solution you have here should work for me to, I think. The fact that I am using RPMs should not be the problem. I will backup mysql database, empty it and then install new RPMs. I will report what will the result be.