MySQLdump via crontab – Pass –password=/hashed/password/file so I can use via crontab w/o using plain text password

hashsumMySQLpasswordSecurity

So I'm setting up a WordPress backup guide/making a backup schedule for myself for real.

I want to do MySQL dumps daily, but the command either requires 

-p then user input

or 

--password="plain text password"

Could I pass it to a file that is atleast MD5 or better hashed and protected to increase security but make the command require no user input?

Any help is appreciated!

For Reference here is the command I want to run 

mysqldump -u [username] --password=~/wp_backups/sqldumps/.sqlpwd [database name] > ~/wp_backups/sqldumps/"$(date '+%F').sql"

Best Answer

You have following password options:

  • provide the password on the command line through the -p option
  • provide the password via the MYSQL_PWD environment variable
  • put your configuration in the ~/.my.cnf file under the [mysqldump] section

In all cases your client needs a plain text password to be able to authenticate. You mentioned hashes, but the trait of a hash is that it's a one way conversion function (i.e. you won't be able to restore the original password from a hash), therefore it's unusable as the authentication token.

Since you are backing up the Wordpress database from, allegedly, the same account that hosts your Wordpress there is no security improvements of trying to hide the password from the user that runs Wordpress (the database credentials can be easily extracted from the wp-config.php file anyway).

So, I'd suggest to define the following ~/.my.cnf:

[mysqldump]
host = your_MySQL_server_name_or_IP
port = 3306
user = database_user_name
password = database_password

Then ensure that the file has the 0600 permissions. This way mysqldump does not need any database credential specified on its command line (they will be read from the ~/.my.cnf file.

Related Question