Secure way to backup MySQL databases?

Question

What is the best/secure way to backup a mysql database on windows server (2008)? I have "MySQL Administrator" but that requires that you save passwords for backup project. I'm not keen on doing as anyone gaining access to the server would then have easy access to the database. Can you do anything similar to SQL Server like using Windows authentication. If not what is the most secure (and practical) way of backups. Lastly, what are the privileges needed to backup a database? I have created a single user just for this task.

Please advise.

Answer 1

Your backup user need the RELOAD privilege and SELECT+LOCK TABLEs for each schema you need to backup.

The easiest way is to create a user bound to localhost only (eg. back@localhost) and use SSL certificates to authenticate user against the server (if your mysql installation has SSL support).

I am not sure you can use the Host authentication to login into your MySQL server. If I try on my GNU/linux box, typing mysql -p ask for the password of the currently connected user but I need to type in my password...

Hope that'll help a little.