Running MySQL Without a Password on the Command Line

When you run a webserver there's always things that could be set up better, for reasons of security, reliability or speed. One of my undone tasks for an embarrassingly long time has been to stop backing up MySQL databases by passing along the username and password to a script at the command line.

I was regularly using the mysqldump program to make a backup of each database via a script that contained these commands:

  
    OF=/home/[username]/backup/$3-$(date +%Y%m%d).gz

    mysqldump --user=$1 --password=$2 $3 | gzip > $OF

The arguments $1, $2, and $3 are a username, password, and database name, respectively. The script creates a copy of the database with the current date in the filename and ends with .gz because it compresses the file with GZIP.

MySQL hates being run this way. Every single time I used the script, I got an email with the message, "Warning: Using a password on the command line interface can be insecure." Getting 12 of these warnings a day made me ignore the server's inbox entirely.

To do something about that security warning, I found several suggestions on this Stack Overflow post.

The one I liked best was to create an extra configuration file for each database that contained the access credentials. It takes this form:

  
    [client]

    user = [username goes here]

    password = [password goes here]

    host = [hostname or localhost goes here]

With this file, the mysqldump command can be rewritten to get the credentials from the new file using the defaults-extra-file argument:

  
    mysqldump --defaults-extra-file=/home/[username]/mysql-$1.cnf $2 | gzip > $OF

The configuration files are accessible only by the user running the script.

Mysql · Webpublishing · Scripting · 2020/06/14 · 0 COMMENTS · Link