Auto check and repair the MyISAM tables

The last two days a table of the database which is used by my blog had a crashed MyISAM table, and this prevented my blog to be accessible.

The corresponding log of MySQL server:

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

I have no monitoring tool set for my vps yet (it’s on my to do list 😛 , to set nagios) so I realised that my blog is not up when I tried to visit it. That’s  why a less time consuming action to solve this problem (than setting nagios) is to add a script in the crontab,  to check and repair tables . An every hour check is  good enough for this purpose:

/usr/bin/mysqlcheck --all-databases -uroot -ppassword -r

I hope a MyISAM table crash not being the reason for my blog's downtime anymore!

MySQL repair myisam table fails

I play the role of the “MySQL administrator” in some cases without having the appropriate expertise or knowledge, and in some cases, I find myself against some first time seeing situations. One situation like this I had never faced before was to fail the repair table command when a myIsam table is corrupted. For everyone there is a first time. Let’s see what the problem was:

I got connected through mysql client as root to the database server, I selected my database through use command and tried to execute the repair:


mysql -h localhost -u root -p
use database_name;
repair table table_name;

The output was:

Found link that points at 5427775770519881079 outside data file) at 788525176
Found block with too small length at 872968268; Skipped
Found link that points at 5131911463743613025 (outside data file) at 886920912
Found link that points at 5131911463743613025 (outside data file) at 888429508
Found block with too small length at 892989976; Skipped
Found block with too small length at 893943488; Skipped
Found block with too small length at 896942348; Skipped
Not enough memory for blob at 919756416 (need 1416319567)
Operation failed

This error probably means that your variable sort_buffer_size is not high enough to repair the table.
Everytime I tried to repair a table it was a success story! And now what? Don’t get anxious! There is another way to repair your corrupted myIsam table. There is a command line tool, myisamchk that you can use. Now take some care because this tool needs to be the only process that uses the corrupted table. You can choose to shutdown the database server, or there are lot of critical applications running on this database server, you have to stop the services that use the database which has the corrupted table. For example the database in which I had the corrupted table was used by two applications, webmail and sympa mailing list server. The corrupted table was inside the database sympa uses. I took the sympa service offline and then I was sure that this database was not accessed by anything else.

Thus, after reassuring that noone access the table you want to repair, you have to do the following steps:
1. cd to the data dir of the database where table_name.MYI file exists. Lets say mine was at :/var/data/mysql/data/sympa/
cd /var/data/mysql/data/sympa/
2. Check what’s your available free memory using something like top, leave a little memory ballon and decice the value of sort_buffer_size. Let’s use an example, if top gives about 3Gb of total ram free and cached then use sort_buffer_time=2G as soon in the example below:
myisamchk -o -f table_name.MYI -sort_buffer_size=2G
3. If this is successfull you are done! Just check the table status:

mysql -h localhost -u root -p
use database_name;
check table table_name;

else if the operation failed, maybe it’s a good idea to shutdown the mysql server, to free more ram and try again with higher value of sort_buffer_size.

It’s good to see this MySQL article too:

http://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html

Backup and restore MySQL – mysqldump a database – Grant Privileges

Back up a MySQL database

If you want to backup a MySQL database and you have access to shell, then you can easily do your job.

You can use mysqldump to create a simple backup of your database using the following syntax. 

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

after executing this command the result will be the backupfile.sql which is the backup file for your database. I shouldn’t pass the password argument [password] in the command because it would be available to other users of the system. If you leave it blank mysql client will ask you to enter your password. If you want to connect to a remote host to make a backup then the only thing you should do is to add -h [hostname | ip address] parameters like the syntax below.

mysqldump -h [hostname] -u [username] -p [password] [databasename] > [backupfile.sql]

This will work supposing you have privileges as the user you want to connect from the remote site.

There are a lot of combinations to dump a MySQL database and you can explore all of them here.

Restore a MySQL database from a dump

To restore a database from a file produced by mysqldump there is an easy walkthrough. Its the reverse way you resulted the dump file. Get a look at the following syntax.

mysql -u [username] -p [password] [databasename ] < [backupfile.sql]

you can restore the dump to a different database  or to a different database server and this is a good option if you want to make a replica of your data to make some tests.

Grant Privileges on a user in MySQL

To grant the privileges to user you have to connect to mysql as user that have privileges to grant privileges to other user as root user do.

As you may already know, to connect to the MySQL server as root you have to execute this command:

mysql -u root -p

once again do not provide your password to the command line. Now to grant privileges to a user you must keep in mind that you should give privileges to user from a host to some database table(s). Keeping this in mind is easy to remember the syntax of the command.

GRANT ALL PRIVILEGES ON 'databasename'.'databasetable'  TO 'username'@'host';
and then do
FLUSH PRIVILEGES

If you want to grant privileges on all tables or all databases just use the asterisk (* ) as follows:

GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'host';

If you want this user to have access from every host / ip address then:

GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'%';

and after each statement that affects the privileges of a user on a database you have to FLUSH PRIVILEGES.

In case you want to give this user the privilege to grant privileges then you have to use on of the above, using extra: “with GRANT option” . For example use:

  GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'%' WITH GRANT OPTION;

this will result a super user on this database and this user can connect from every site and he is able to grant privileges to other users for this database. Do not forget to FLUSH PRIVILEGES.

MySQL – Create database with collation and Character set utf8

If you want to create a database in MySQL with default charset and collate utf8, then you simply have to give the following command:

 

CREATE DATABASE "database_name"
 DEFAULT CHARACTER SET utf8
 DEFAULT COLLATE utf8_general_ci;