I take it you are using REPAIR TABLE by hand.
I also take it your are doing an automatic table repair for MyISAM. (You have to remove or comment out the option of my.cnf
to disable automatic MyISAM table repair).
You can actually repair the table without using MySQL but mysqld cannot be accessing it. How?
You have to use the offline utility myisamchk
Suppose you have the following
- datadir is /var/lib/mysql
- database is
mydb
- table is
mytable
The table files would be
/var/lib/mysql/mydb/mytable.MYD
/var/lib/mysql/mydb/mytable.MYI
You can repair it with something like this
cd /var/lib/mysql/mydb
cp mytable.MY[ID] /tmp
cd /tmp
myisamchk -r mytable.MYD mytable.MYI
cd /var/lib/mysql/mydb
mv mytable.MY[ID] ..
mv /tmp/mytable.MY[ID] .
Afterwards, you can either login to mysql and do this
mysql> flush tables;
or restart mysql
service mysql restart
Give it a Try !!!
UPDATE 2014-01-08 15:46 EST
Based on the last comment
Hi Rolando, It is an Linux OS Virtual Machine. 2 Intel Xeon Core 2048 Mbyte Memory. The tables are MyISAM. I will post the rest off the data in a sec (it's hard because it keeps crashing remember)
I would stronly recommend that you get more memory. Why ?
MyISAM caches index pages to the MyISAM key buffer. MyISAM does not cache data. The mysqld process defers all data caching to the OS. That can be a little dangerous for all open MyISAM tables in a low memory DB Server.
A MyISAM table keeps a count of the number of times a file handle has been open against it. When MySQL is down, a MyISAM table with a nonzero file handle count is considered crashed.
Although I provided the answer to the question in fixing a crashed a MyISAM table without mysqld running, you have a bigger problem with a 2GB VM. It should be more like 8GB. You should also run mysqltuner.pl against the VM by doing this:
# wget mysqltuner.pl
# perl mysqltuner.pl
You can then learn if /etc/my.cnf is underconfigured.
Your master is not compatible with the 5.6 slave. binlog_checksum
is only available on 5.6, and so is UUID. I have successfully replicated servers between 5.5 and 5.6. Perhaps you can check if these options can be disabled.
2014-11-13 14:03:07 1022 [Warning] Slave I/O: Notifying master by SET
@master_binlog_checksum= @@global.binlog_checksum failed with error:
Unknown system variable 'binlog_checksum', Error_code: 1193 2014-11-13
14:03:07 1022 [Warning] Slave I/O: Unknown system variable
'SERVER_UUID' on master. A probable cause is that the variable is not
supported on the master (version: 5.1.71-log), even though it is on
the slave (version: 5.6.21-log), Error_code: 1193
Best Answer
It seems that you have network connectivity issues.
Assuming the SAN of your VPS is connected using the same network (very likely), the database server could lose connection to its drives intermittently. That can cause all kinds of consistency issues within the databases.
As this is a hosted environment, you cannot diagnose or fix network issues yourself. Talk to your hosting provider and have them look at it.