LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 07-27-2012, 11:46 AM   #1
diwljina
Member
 
Registered: Jun 2009
Distribution: Slackware, Debian
Posts: 105

Rep: Reputation: 8
MySQL tables gone


Hi!

I'm not sure if this is the right subforum, but here it is.

After automatic update of mysql, I lost all tables in a database. I can't see them from console or phpMyAdmin. But when I go to /var/lib/mysql there it is 300MB directory. What happend and how can I get this database back?

I tried repairing with mysqlcheck, but nothing happened.
 
Old 07-27-2012, 11:48 AM   #2
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,566

Rep: Reputation: 411Reputation: 411Reputation: 411Reputation: 411Reputation: 411
After upgrading MySQL you should ALWAYS do the following commands:

Code:
mysql_upgrade -uroot -p
mysqlcheck -Aro -uroot -p
That should update your mysql database so that it can read the other databases correctly
 
Old 07-27-2012, 12:13 PM   #3
diwljina
Member
 
Registered: Jun 2009
Distribution: Slackware, Debian
Posts: 105

Original Poster
Rep: Reputation: 8
Hi Kustom42 and thanks for your response!

Automatic update is performed by cPanel and I guess it executes that command after updates. Nevertheless, I just tried with those and still nothing. No tables. Perhaps it wasn't due to update, but I have no idea why this happened.

Any other suggestion?
 
Old 07-27-2012, 12:17 PM   #4
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,566

Rep: Reputation: 411Reputation: 411Reputation: 411Reputation: 411Reputation: 411
What is the output of

Code:
ls -al /var/lib/mysql/
mysql -uroot -p -Dmysql -e"SELECT * FROM information_schema.tables WHERE table_schema = 'YourDatabaseName';"
 
Old 07-27-2012, 12:26 PM   #5
diwljina
Member
 
Registered: Jun 2009
Distribution: Slackware, Debian
Posts: 105

Original Poster
Rep: Reputation: 8
Code:
 ls -al /var/lib/mysql/
total 30800
drwxr-x--x 26 mysql mysql     4096 Jul 27 11:53 ./
drwxr-xr-x 75 root  root      4096 Jul  3 05:19 ../
drwx------  2 mysql mysql     4096 Jul 27 11:54 cphulkd/
drwx------  2 mysql mysql     4096 Jul 27 11:54 eximstats/
drwx------  2 mysql mysql     4096 Jul 27 11:54 horde/
-rw-rw----  1 mysql mysql 18874368 Jul 27 10:57 ibdata1
-rw-rw----  1 mysql mysql  5242880 Jul 27 10:57 ib_logfile0
-rw-rw----  1 mysql mysql        0 Jul 27 10:57 ib_logfile1
drwx------  2 mysql mysql    12288 Jul 27 11:55 company_workpress/
drwx------  2 mysql mysql    12288 Jul 27 11:05 company_products/
drwx--x--x  2 mysql mysql     4096 Jul 27 11:55 mysql/
srwxrwxrwx  1 mysql mysql        0 Jul 27 11:08 mysql.sock=
-rw-r--r--  1 root  root         6 Jul 27 11:05 mysql_upgrade_info
drwx------  2 mysql mysql     4096 Jul  3 05:19 roundcube/
company_products is problematic database.

Second command gives nothing. Empty set.
 
Old 07-27-2012, 12:37 PM   #6
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,566

Rep: Reputation: 411Reputation: 411Reputation: 411Reputation: 411Reputation: 411
Quote:
Originally Posted by diwljina View Post
company_products is problematic database.

Second command gives nothing. Empty set.

Did you replace the 'YourDatabaseName' with company_products?

What does

Code:
mysql -uroot -p -Dmysql -e"SELECT * FROM information_schema.tables;"
give you?
 
Old 07-27-2012, 01:01 PM   #7
diwljina
Member
 
Registered: Jun 2009
Distribution: Slackware, Debian
Posts: 105

Original Poster
Rep: Reputation: 8
I did replaced it. And that command gives me information about other working tables. It's just problematic database that gives me empty set.
 
Old 07-27-2012, 04:37 PM   #8
diwljina
Member
 
Registered: Jun 2009
Distribution: Slackware, Debian
Posts: 105

Original Poster
Rep: Reputation: 8
Since all .frm files were gone, I managed to find some old backup. I restored old backup and that way restored tables definition. Then I copied new .MYD and .MYI over this restored, while retaining .frm's. Last step was repairing database with mysqlcheck -r and everything was working like before.

Kustom42, thanks for your help, anyway.
 
Old 07-27-2012, 04:41 PM   #9
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,566

Rep: Reputation: 411Reputation: 411Reputation: 411Reputation: 411Reputation: 411
Yep, that was the next step. Restoring the actual .MYI and .MYD files is sketchy but it can work. The fact that you were getting no info when doing the query against your info schema table confirms that the issue is with your .frm files which tell the MySQL database your table info. Just wanted to add this follow up for anyone else that runs into the issue. This is the last step you can attempt before you are up the creek so to speak.
 
Old 07-27-2012, 05:03 PM   #10
diwljina
Member
 
Registered: Jun 2009
Distribution: Slackware, Debian
Posts: 105

Original Poster
Rep: Reputation: 8
So, without frms it would be impossible to restore database?
 
Old 07-30-2012, 11:36 AM   #11
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,566

Rep: Reputation: 411Reputation: 411Reputation: 411Reputation: 411Reputation: 411
Without any one of the files(.frm, .MYI, .MYD) it would be impossible to restore. It is good practice to setup a cron job to make a mysqldump of your databases for backup purposes.
 
Old 07-30-2012, 11:39 AM   #12
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6
Posts: 1,449

Rep: Reputation: 438Reputation: 438Reputation: 438Reputation: 438Reputation: 438
Quote:
Originally Posted by Kustom42 View Post
Without any one of the files(.frm, .MYI, .MYD) it would be impossible to restore. It is good practice to setup a cron job to make a mysqldump of your databases for backup purposes.
It's even better practise to replicate them to a slave server and take backups from the slave
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
list of tables in mysql sulekha Programming 3 04-05-2009 03:01 PM
mysql reinstallation distorted by previous tables in /var/mysql mad4linux Linux - Software 0 10-04-2005 01:39 PM
MySQL tables from 4.0 to 3.0 michaelbhahn Linux - Software 0 08-31-2004 10:23 AM
mysql tables with a - pk21 Linux - Software 7 05-23-2003 06:27 AM
mysql tables noord28 Linux - Newbie 0 02-20-2003 02:56 AM


All times are GMT -5. The time now is 10:14 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration