More Zonkyness

Well, relatively safely anyway.

The other day I felt the urge to knock up some SQL to clean up a MySQL table I’m responsible for. So I got the SQL prepared, but then needed to find out how to run this as a cron job (or batch file if you prefer). This was because I wanted to run the job on a regular and frequent basis; and I’m too lazy to sit around with a stopwatch running the same command every 15 minute.

I came up with a method that I knew wasn’t recommended – as it said so in the manual. The method involved passing the contents of a file to the command line (i.e. mysql -u root -p$(cat somefile). And today I decided to have a look around to see if there was a better way.

It turns out that much of the most visible advice on the Internet is wrong. It all suggests using the -p option where the account password is visible in the process table (i.e. you can read it with ps aux). If you can guarantee that no rogue person will be able to login to your server and read the process table, then you are safe; and I would like to ask how you can achieve the impossible.

Putting a password in a file (as suggested below) is at least somewhat better, but it would be nice if MySQL were to offer something a bit more sophisticated.

However, on to the method.

The first step is to create an options file, and make sure that it is readable only by the current user :-

$ touch mysql-options-root.cnf
$ chmod go= mysql-options-root.cnf

Once securely created, you can edit the file and add the relevant options :-

[client]
password=do-you-really-think-i-would-tell-the-internet-what-my-mysql-root-password-is?

That list of options is fairly minimal; it only specifies the password to use. You can add additional options – just use the normal command-line options without the hyphens.

To use this options file, add the command-line option –defaults-extra-file=${filename}. A full example follows :-

$ mysql --defaults-extra-file=mysql-options-root.cnf --user=root --host=polio < use misc;
heredoc> select count(*) from films;
heredoc> END

The output is not included (it’s none of your business how many films I’ve watched). And yes this does work for what I intended it to.

As has been pointed out, it is also possible to schedule batch jobs within MySQL itself which may be a better answer in most cases.