Automating check and repair on MySQL Databases

Issue:

Cron job required for scheduling automatic checks and repairs on MySQL Databases.

Solution :

Use the following cron :

0 1 * * * /usr/bin/mysqlcheck -Aao –auto-repair -u -p’ ‘

Please note that this will show your MySQL password in the process list. You can get around this by writing the password to ~root/.db_shadow, chowned root, chmod 400, and then reference it as `cat ~/.db_shadow` in the cron command instead of the actual password (note the backslashes in that command) .

Or, if it is not running as root, instead running as a cPanel user, putting the password in a file called .db_shadow in the home of your account, and running the command as follows would achieve the purpose.

/usr/bin/mysqlcheck -Aao –auto-repair -u -p”$(< .db_shadow)"