Category Archives: Mysql

14Jan/17

Repair crashed MySQL databases on LINUX servers

How to repair MySQL databases and tables

This article explian about how to repair MySQL database and table. As the mysql is updated time by time. Mysql deals with some tools that we can use it for repairing the databases and tables.

It is common that our database get corrupted due to many reason like it does not get restored properly or server get rebooted while updating database etc. We can repair the table or the corresponding databases through various steps by mysqlcheck command.

First of all before doing mysqlcheck we need to take the current mysql backup so that if any issues occurs we can able to revert it to the stable condition. The specific thing we should consider is that the mysqlcheck commands works on the database engine InnoDB.

Change the directory to mysql as follows,

cd /var/lib/mysql

InnoDB Engines

To use the mysqlcheck for InnoDB engines we have to follow the below steps

For checking all the database,

 mysqlcheck -c -u root -p --all-databases

To check a specific DATABASE

 mysqlcheck -c "DATABASE name" -u root -p

Checks all the tables under the mentioned database name

#mysqlcheck -c "DATABASE name" "TABLE name" -u root -p

Checks the mentioned tables under the DATABASE name

However if a mysql table passes the check it will display the “OK” for the table.

If the database table has displayed any error means we can repair the table by the following command,

 mysqlcheck -r "DATABASE name" "Table name" -u root -p

-Repair and optimization of tables for all databases

There’s a simple command to automatically check, repair and optimize all tables in all databases when running a MySQL server on Linux/Unix/BSD.

 mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

MyISAM Engines

To use the mysqlcheck for MyISAM engines we have to follow the below steps

If we are using MyISAM storage engine for mysql,we can use the myisamchk commands to repair the table.

The myisamchk command only works under the database or the tables using MyISAM engines.It will not be working under Innodb engine.

The mysqlcheck program enables us to check and repair databases while MySQL is running this is useful when we want to work on MySQL without stopping.

Steps for checking all the Mysql tables

myisamchk table name.

To check all the tables under the database,

myisamchk * .MYI

How to repair a table from myisamchk command,

myisamchk --recover "table name"

After the repair please check for the mysql repair and confirm regarding the fix

The above mentioned commands will help you to troubleshoot the mysql database and table repair

04Dec/14

Error : The adminbin “cpmysql” in the “Cpanel” namespace call to function “DBCACHE” ended prematurely: The subprocess reported the “” (255) error when it ended

This error was there in user’s cpanel section and no database, no db users etc were showing in the cpanel for the users.

On checking the /varlib/mysql/server.domain.com.err (mysql error log) I can see the mysql user table was corrupted and needed repair

141204 12:13:56 [ERROR] /usr/sbin/mysqld: Table ‘./mysql/user’ is marked as crashed and should be repaired

You can repair mysql database via command line

# mysqlcheck -r mysql

================
mysql.user
warning : Number of rows changed from 282 to 283
status : OK
================

You can see table mysql.user is crashed and after repairing the issue will get fixed.

18Mar/14

Change the Max File Upload Size for PhpMyAdmin in Plesk

In some cases, we need to restore  big databases using phpmyadmin. In that cases, we need to increase the upload limit in phpmyadmin to accomplish this.

Change the values (to the required value) “upload_max_filesize” & “post_max_size” in the file “/usr/local/psa/admin/conf/php.ini”

And restart the apache, here plesk service providing the apache for phpmyadmin, so restart psa.

/etc/init.d/psa stop

/etc/init.d/psa start

Now you can restore bigger databases from phpmyadmin

 

13Nov/13

[ERROR] /usr/sbin/mysqld: Can’t open file

 

I got this error while migrating  huge amount of accounts from one cpanel server to  another.

[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./dbname/tablename.frm’ (errno: 24)

errno: 24 means there is too many open files.  There is a mysql variable named “open_files_limit"  which shows how many open files are allowed in mysql by default. We cannot update this valuse using “Set”  command as this variable is a read only variable.

Continue reading