Using mysqldump to do automated backups

One of my annoyances of MySQL is that all authentication is “password” based. I very much prefer PostgresQL’s use of Unix sockets, such that if you login as the “postgres” user (or whatever user you are running the cluster as), you are effectively root.

So, one has passwords for the root database user spread all over scripts when you have a MySQL install. Very annoying.

On debian systems (including Ubuntu and JEOS), there is a special userid created called “debian-sys-maint”, the init scripts use this ID for shutting down. The password is randomly created and is stored into /etc/mysql/debian.cnf. Read /etc/init.d/mysql if you want details.

How do use this with mysqldump? it does not have a –config option.

Finally, after a lot of googling, I came across the page:

it says:

defaults-extra-file The file specified with –defaults-extra-file=path, if any

So, mysqldump can be told to read additional config files!

sudo mysqldump --defaults-extra-file=/etc/mysql/debian.cnf --database myfoo