Often, you may want to run a script that uses a username and password to access data in a MySQL database. Securely running a script like this manually is easy – simply use the ‘-p’ option for the MySQL client, and it will prompt you for the password. However, this is not an option if you want to automate the script.
There are several ways to provide the password in a way that can be used with automated scripts, but only one that is both flexible and secure. You can specify the password on the command line itself (with ‘mysql -p ‘); however, this allows the password be seen by other users who run commands like ‘ps’. Another option is setting the environment variable “MYSQL_PWD” to the password, but this can also be seen by other users.
A third, secure option is to place the password in a file named ‘.my.cnf’, in the home directory of the user running the script. However, since only one password can be placed in this file (which is used by the MySQL client every time it’s run), this isn’t a very flexible or good solution.
The last (and best) option relies on an undocumented option in mysql clients like ‘mysql’, ‘mysqldump’, and ‘mysqladmin’. This option is ‘–defaults-extra-file’, and allows you to specify a file to load configuration from. This means that you can create a configuration file that is specific to a certain script.
For example, if you wanted to run an automated script to print out the results of ‘mysqladmin proc’, you would want to create a configuration file (in this case, ‘extra.my.cnf’) with the following:
[client]
user=$USER
password=$PASSWORD
where $USER and $PASSWORD are the credentials you would like to use. Then, run the following:
‘mysqladmin –defaults-extra-file=extra.my.cnf proc’
This will use the credentials you specified in your configuration file to log into MySQL to display the current process list of the server.