Well, relatively safely anyway.<\/p>\n
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.<\/p>\n
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.\u00a0mysql -u root -p$(cat somefile)<\/em>.\u00a0And today I decided to have a look around to see if there was a better way.<\/p>\n It turns out that much of the most visible advice on the Internet is\u00a0wrong<\/em>. It all suggests using the\u00a0-p<\/em> option where the account password is visible in the process table (i.e. you can read it with\u00a0ps aux<\/em>). If you can\u00a0guarantee<\/em> 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.<\/p>\n 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.<\/p>\n However, on to the method.<\/p>\n The first step is to create an options file, and make sure that it is readable only by the current user :-<\/p>\n Once securely created, you can edit the file and add the relevant options :-<\/p>\n 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. <\/p>\n To use this options file, add the command-line option –defaults-extra-file=${filename}<\/em>. A full example follows :-<\/p>\n 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.<\/p>\n 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.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"\r\n$ touch mysql-options-root.cnf\r\n$ chmod go= mysql-options-root.cnf\r\n<\/pre>\n
\r\n[client]\r\npassword=do-you-really-think-i-would-tell-the-internet-what-my-mysql-root-password-is?\r\n<\/pre>\n
\r\n$ mysql --defaults-extra-file=mysql-options-root.cnf --user=root --host=polio <