mysql database is all broken ... help...
Currently I can log in as root (mysql -u root -p and then password) but when I do, I only see the information_schema table. I am missing two others, mysql and main. Main is what I call my "main" table.
When I try to access the main table I get access denied, except for under mysql safe mode.
I am no mysql or even linux server expert and yes I use root for access to the server.
Currently the server is running and my site is showing under mysql safe mode (mysql -skip-grant-tables).
How can I fix this, other then a full reinstall?
4 Replies
@Ghan_04:
Do you know where your MySQL data directory is kept? If so, can you go there and see what tables have directories there? Also check your MySQL error logs (which are problem somewhere around /var/log/ or so) to see if there is anything of note there.
Thank you for your response ….
I've checked the logs before, posting and I've googed 1000x's for anything ….
The logs don't have any errors or anything conclusive, mostly just me starting and stopping the server.
I also know where my mysql install is, and I know the table 'main' exists I can actually view it right in mysql under the mysql save mode. I just don't have access to it without being in safe mode. It's like my root user lost all access to the table.
Can you produce a dump (backup) of the 'main' database while MySQL is running in 'safe mode'? You can use the 'mysqldump' command to do this. If this works, at least you've got your data safely in your hands, and you might be able to load it back into another database. If nothing else works, you can also load the dump back into a fresh (re)install of MySQL.
No, you won't have to reinstall the whole server, just MySQL if it is corrupt. If you need to resort to reinstalling MySQL, make sure to produce a dump and also back up the entire MySQL data directory. The data directory is usually /var/lib/mysql, but it could differ depending on your setup. (After you confirm that your backups are safe, stop MySQL and delete the data directory before you run the reinstall, since it's apparently corrupted.)
@hybinet:
'mysql' and 'main' are not tables, they're databases. Databases contain tables. Just a technicality, but this distinction might help when you're searching for solutions.
Can you produce a dump (backup) of the 'main' database while MySQL is running in 'safe mode'? You can use the 'mysqldump' command to do this. If this works, at least you've got your data safely in your hands, and you might be able to load it back into another database. If nothing else works, you can also load the dump back into a fresh (re)install of MySQL.
No, you won't have to reinstall the whole server, just MySQL if it is corrupt. If you need to resort to reinstalling MySQL, make sure to produce a dump and also back up the entire MySQL data directory. The data directory is usually /var/lib/mysql, but it could differ depending on your setup. (After you confirm that your backups are safe, stop MySQL and delete the data directory before you run the reinstall, since it's apparently corrupted.)
Hi Thank you for your help, I assumed that would be the answer but I wasn't 100% sure. And yes, I did screw up the last post with table vs. database I am afflicted with sudden onset retardation on occasion.
I took a backup.
yum removed mysql
renamed /var/lib/mysql to /var/lib/mysqlx
yum install mysql mysql-server
created the new database main
restored back up and it seems to be back up and running fine.