One important part of running a database is maintenance, and that includes backups. However, due to the fact that the database is password-protected, one can't automatically back up the database without interactively supplying the password. And, depending on what you need to back up, you may even have to use the MySQL "root" user to do a full backup.
You can avoid those hassles by creating a configuration file for the user to do the backups (i.e., /root/.my.cnf). This user does not need to be the system root user; it can be any normal system user who has access to the database as the database's root user. The ~/.my.cnf file would look like:
[mysqldump]
user = root
password = secret
With this, you can automate a dump of the database via a cronjob, without being asked for a password. You will still need to supply a password using the mysqladmin or mysql tools, but not with mysqldump. Now, to automate the backup, run the following script out of cron:
#!/bin/sh
mysqldump -u root --all-databases >/var/lib/sqldump/mysql.dump
Some people may consider this horribly insecure, but it's no more insecure than doing it manually, especially if this is being done by the root (or even mysql) user. If the file is adequately protected (mode 0600), your database is just as safe. Remember that an attacker would have to obtain root privileges in order to read the file. If that attacker had root privileges, they could simply make a copy of your database, retrieve it, and open it on any MySQL installation at their home and be able to read the contents of your database at their leisure, without once requiring the database password.
Because there is just as much (or just as little, if you prefer) security in supplying the database root password in a configuration file, there is really no reason not to do it. The benefit is that providing password-less access can make things like automated database maintenance far easier.
No comments:
Post a Comment