0

I've got some problems with my database. I hope somebody can help me out.

Problem: The database keeps crashing on startup. In my log i see a couple off these errors:

[ERROR] /usr/libexec/mysqld: Table '.tablename' is marked as crashed and should be repaired

Normally i would repair this table using PHPMyAdmin or SSH but is it possible to do this without the database running? (because when i start it it crashes and stops)

Or is there any other way to get this all working again? I've got a back up but i will lose some data. The table that is crashed contains cache data, so i could miss that.

Thank you!

Gr. Matthijs

UPDATE by RolandoMySQLDBA

  • Please post contents of /etc/my.cnf
  • Is the DB Server bare metal machine or VM ?
  • How much RAM does the server have ?
  • Are all the tables InnoDB, MyISAM, or a mixture of both ?
  • Is the OS Linux or Windows ?
Community
  • 1
user3166736
  • 1
  • 1
  • 1
  • I posted questions in the your post for you to supply answers to – RolandoMySQLDBA Jan 07 '14 at 20:53
  • 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) – user3166736 Jan 08 '14 at 20:14
  • Content off my.cnf:

    [mysqld] old-passwords=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql

    Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links=0

    [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

     – user3166736 Jan 08 '14 at 21:45

1 Answers1

1

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.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Thank you for your reply. Unfortunately i've been struggling with this the laste couple off days without success. The shell commands keep turning up with a "permission denied" or "nosuch file or directory in the console".

    My thought is my shell user has no root access. But my host keeps telling me i have all the rights and can change anything by using the nodeworx control panel. In here i'm not able to find any setting to do this. Any thoughts?

    In the meantime i'm trying to get the password for the root user.

     – user3166736 Jan 07 '14 at 20:17
  • Nodeworx root password was available in the control panel under resources. Got my problem fixed using the above method and lot's off help.

    Thank you.

     – user3166736 Jan 08 '14 at 11:04