2

I use the following /etc/crontab code to create daily backups of my database in limit of the last 30 days:

0 8 * * * mysqldump -u root -PASSWORD --all-databases > /root/backups/mysql/db.sql
1 8 * * * zip /root/backups/mysql/db-$(date +\%F-\%T-).sql.zip /root/backups/mysql/db.sql
2 8 * * * rm /root/backups/mysql/db.sql
2 8 * * * find /root/backups/mysql/* -mtime +30 -exec rm {} \;

My problem:

I must put my password where -PASSWORD. This way my password is exposed if by mistake I entered to change something in this file and someone near to me saw it.

Is it possible to use the script without putting the password there? Alternatively, do you know a similar syntax that won't force me to write the password there for a cronjob?

2
  1. Create a script to do the complete dump, backup and cleanup.

  2. Schedule the script.

Additionally, the password to mysql may also be stored in a protected file and does not need to be given on the command line.

MySQL has a "End-User Guidelines for Password Security" document that you may want to consult.

To summarize that document:

Create .my.cnf in your home directory and add the password to it like this:

[client]
password=your_pass

Then remove read-permissions on the file for other users:

$ chmod 600 .my.cnf

or, equivalently,

$ chmod u=rw,go-rwx .my.cnf

This file, if named .my.cnf and placed in you home directory, will automatically be used by the mysql client program (as well as by mysqldump).

Still, do put the backup etc. into its own script and schedule that instead. That will be a whole lot easier to maintain than a number of cron jobs.

  • Thanks! Indeed I'll most likely move this to a script, but is there no way not use a password at all? I mean, I am the only one who uses this server and there is no sign it's going to change... Creating a protected file and reference it each time a password is needed seems to a non minimalist solution I would want to avoid, for now. – JohnDoea May 8 '17 at 14:50
  • @Benia If you use the name ~/.my.cnf for the configuration file, it will automatically be used by the MySQL client. – Kusalananda May 8 '17 at 15:16
  • @Benia Also, you can not seriously be concerned about putting your password in the crontab, and then say that you'd rather have no password. – Kusalananda May 8 '17 at 15:50
  • Oh, sorry, I malexplained myself. I meant that I would rather not use a password for creating dumps, because I personally see no security threat in exporting dumps. Maybe I'm missing something. – JohnDoea May 8 '17 at 15:52
  • @Benia The musqldump process attaches to the database as a client, just like the mysql client or any other process does that needs to query it. To be able to do that, it needs to authenticate with the server. You could possibly create a user on the server that only has the permissions needed to perform a dump, but that would mean that anyone could connect as that user and do a dump, not just your cronjob. – Kusalananda May 8 '17 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.