mysql query as cron job?

I need to update a table of one of my site's databases on a regular basis. I have the right query that I already know works, but I'm unsure of the exact syntax for running it as a cron job. I think it's something like below, as I am doing this from the cpanel cron jobs area. Not sure how I tell it which database to use.


mysql -uusername -ppassword -hlocalhost -e "UPDATE table SET etc;"

Edit: ok I figured it out I don't need the -h (host) and I had to add "USE databasename; UPDATE table SET etc;"

 

Just be careful with scripts like this because it puts your username and password in plain text.

 

If you're more at home with running queries in PHP then you could always write a PHP script to do the update and make a cron job run the PHP script.

 

That was going to be my initial method but I'm not at home with PHP, I'm more like a distant neighbor.

Assuming no one can access my cpanel, are there any other security risks with a cron job that uses my password?

 

Well as long as your web hosting setup is secure you should be OK, after all most web apps have to store the DB username and password as plain text somewhere. Wordpress for example stores it in wp-config.php which is just a text file.

 

My biggest concern with a cron script is that cron creates output and logs. Servers routinely backup logs, so you can quickly lose accountability for where the text username and password are on the server. A hacker getting a hold of logs is one of the most common ways servers are compromised.

I would create a database user specifically for this purpose and make sure they don't have access from anywhere except the server. Also, I would using a > /dev/null and make sure cron isn't logging it.

 

Seems like sensible advice to me

 

For better security you can use create event instead of cron here (if your mysql user has create event privileges).

Regards