Friday, March 13, 2009

MySQL batch mode script

N.B. On most Unix systems, by placing the password in the command line with --password (even the above method for using a password file) you are making the password visible to local users, who can see the command string with a "ps" or "w" command.

Whilst some systems can be set to block this, and others would let you wrap the command in something that would overwrite what users could see as your command, the best way to do any automations like this is to create a specific unix user for the job (or use a user that is already secure) and place the password in the .my.cnf file for that user - making sure the permissions are set so that only the owner can read it

Note that in order to use batch mode efficiently you need
non-interactive authentification. The best way is per-user
configuration file: ~/.my.cnf

Create file and put a couple of strings:
password = yourpasswd

NB! Don`t forget to set proper permissions!
shell> chmod 400 ~/.my.cnf

Now mysql will use this password as you default password.

You can also pass any mysql parmeters in the sha-bang line.
For example:
#!/usr/bin/sql -t
#!/usr/bin/sql -X
or even
#!/usr/bin/sql -u someuser -ppassword

That`s it!

There are 10 kinds of people: those who understand binary and those who don`t.

No comments: