Cron job to delete spam comments

Q As I'm getting rather too many comment spam attacks on my web server, I thought I'd set up a cron job to delete the comments automatically ever so often, but I can't get the command to operate. I'd welcome any thoughts on the code I'm using:

mysql -h hostname -u username -ppassword -e
'delete from table_name where pn_cid > x'

I do have more than one database on the server, and I note the above line of code doesn't have any mention of which database it should address, so perhaps there is a missing switch?

A You do need to specify the database name (even if you have only one database), otherwise MySQL won't know how to apply your commands. The database name can be given as the last parameter on the command line, or within the commands you pass to the mysql program. These are equivalent:

mysql -u user -ppword -e "delete from table_name where pn_cid > x" database_name
mysql -u user -ppword -e "USE database_name; delete from table_name where pn_cid > x"
mysql -u user -ppword - "delete from database_name.table_name where pn_cid > x"

The first is simpler, but the others offer more flexibility. You can also pipe the commands to the MySQL client instead of using the -e option. This is useful if you want to run a number of commands, because you can put them in a file and do

mysql -u user -ppword <cmdfile

You also need to be aware that programs run by cron do not have the same environment as programs run from a shell. As there is no user login involved, the various environment variables in your profile are not set up. You can get around this by putting the commands in a short script:

#!/bin/sh
source /etc/profile #or ~/bash_profile or ~/.bashrc
mysql -uuser -ppword ...

Use whichever file contains your environment settings on the second line and set your cron task to call this script instead of running mysql directly. Using a script also makes testing slightly easier, as you know exactly the same commands are used whether you run it from a terminal or cron. Specifying the password on the command line is considered insecure because it is then available to any user on the machine for as long as the program is running, simply by looking at the output from ps. A safer option is to put the password in ~/.my.cnf, as

[client]
password=your_pass

and

chmod 600 ~/.my.cnf

makes sure the file is only readable by your user

Back to the list