MySQL is perhaps one of the most popular database systems
currently available. It has a lot of great features, it’s open source, and a
lot of Web-based applications use it for their back-end storage.

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.

Delivered each Tuesday, TechRepublic’s free Linux NetNote provides tips, articles, and other resources to help you hone your Linux skills. Automatically sign up today!