Quite a usual task is to dump a database to do backups. You may even want to do this in a cronjob to snapshots, etc.
A very bad solution
A very bad solution is to hardcode the root password in the cronjob or in your backup script; doing so have a very high chance of exposing your password.
- It may appear in the cron.log
- It may be sent by email if you have an error
- It may appear in your history
- It is a bad idea to your backups using the root account
A better solution
You could create an account with read-only access to all your databases and use it to to your backups. This is indeed better but can lead to the same issues mentioned above
Putting the password in a file
The safest way to use passwords on the command line is to store them in a file and have a script load them when needed. You then just need to make sure those files have the correct permissions
An “already done for me” solution
As it turns out, installations of dbconfig on Debian/Ubuntu creates a user called debian-sys-maintainer. It is used to do MySQL management, mainly through the package manager. Well, this user has all the needed privileges to backup your database and you are sure it will always work. Unless, of course, you manually change the password without updating the file.
This script uses sudo so it will ask your password even if you forgot to prepend sudo.
Typical usage
$ export-database.sh my_database [mysqldump options] | gzip > /tmp/my_database.sql.gz
Quoting the mysqldump manual page:
ReplyDelete"Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line."
Surely there must be a way to convince mysqldump to use /etc/mysql/debian.cnf as the option file?
While it is indeed frowned upon to use a password in the command line, it is mostly because your password will be showing as plaintext a bit everywhere. For example: in your clipboard, in your bash history, in htop, in the process tables, etc.
ReplyDeleteHowever, one cannot read process informations he is not owner (if not root) so this, IMO, is not a big issue. Since we are passing the password by argument and not actually typing it in a shell, the clipboard/bash history is not an issue either.
All in all, using the config file directly is much more proper, I hadn’t thought about it. Thanks