crontab for mysql database cleanup and backup

Question

On my OpenBSD box I have the following crontab for user _mysql

0 4 * * * /backup/mysql_backup/mysql_backup.sh
0 9 * * 1 mysql -uroot -pMySQLpassword -e "purge master logs before date_sub(now(),interval 5 day);"

The first was aimed at backing up the mysql databases on this box. I now realize these jobs have not been running because user _mysql was denied permission to connect (I have confirmed that the password for the _mysql user is correct).

So, I am thinking I should have these jobs in the crontab of a different user but which (I assume they shouldn't be in root's crontab)? So, I am looking for suggestions on how to configure this. Or do I need to change my mysql configuration.

Edit

The script is on the same server as the datebase. These crontab entry fails with the error

connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','',...) failed: Access denied for user '_mysql'@'localhost' (using password: YES) at /usr/local/bin/mysqlhotcopy line 178 aigaion_copy DBI

Answer 1

You can use expire_logs_days=5 in your my.cnf to perform the same function as your second cron job. It is probably more reliable.

Answer 2

The system user running the cronjob (_mysql) shouldn't matter in relation to the user connecting to the database (root). The only thing would be the server hostname/ip the script is connecting from.

Is the script on the same server as the database? What is the exact error you get when trying to run the commands?

Answer 3

The user '_mysql'@'localhost' does not have the necessary permissions inside of the DB according to the posted error message.

You need to grant this user the necessary permissions or use different credentials inside of your /backup/mysql_backup/mysql_backup.sh script.