My server crashed a while ago and three MySQL tables cannot be opened. I get the error "Didn't find any fields in table 'tablename'".
So i run mysqlshow databasename tablename
and this returns the following error:
"mysqlshow: Cannot list columns in db: dbecommunity, table: tbforumsections: Can't open file: 'tbforumsections.MYD'. (errno: 126)"
Any ideas how to fix this?
Run myisamchk -e *.MYI in your db data dir to get list of corrupt tables. Use
myisamchk -r -q table to try and do a quick recovery. if not, just use the -r option.
Make sure mysqld isnt running
I tried that. found it on the MYSQL documentation site.
It didn't work. I then tried coping the data file somewhere else. recreating the table and then coping the datafile back. repairing the index. but this failed as well.
in the end I recreated the table from scratch.
I am curious as to what might have caused this. it happened to three tables in one database simultaneously. it also caused two of my web pages to be corrupt.
I was using winxp in the office with putty to maintain a SSH connection to the server and Dreamweaver for web page editing. Dreamweaver crashed during ftp and then everything fell apart.
luckily I have a php script which produces a html page of the sql needed to create a table at the command line. I regularly save this as text on my pc. so I can ftp it up and feed it in to mysql. so recreating the dbase isn't difficult but the data isn't saved.
is the mysql backup table command worth using or should i use another method to backup my databases.
Dont you have any valid backup made by mysqldump?? in that case you can recreate the tables and import the data??
I have never had mysql corrupt is own data. When it happens, generally caused by a failing disk or another app. Try running fsck on your drive to see if there is any bad blocks.
I don't have super rights to the server as it's hosted. I get ssh access and special permissions but not root. I access a virtual server on a shared computer. hard ware access is limited. but disk problems are normally sorted and resolved without me ever knowing due to the level of redundancy - i just get emails telling me that stuff broke and then were fixed.
QUESTION. what does mysqldump do?
GUESS. Dumps all data into some sort of text file.
If so is it in the correct format to use the import from text file feature?
Yes, it is in the correct form to import back into a database. After dumping it to a file, when you open it you will basicly see mysql syntax that creates tables and inserts data. You can read the data back into the db with a command like mysql database < dump_file.sql. Mysqldump also supports remote dumping by doing something like mysqldump database | mysql -h some.host.com backupdb. That is an easy way to backup to a remote server.