I found a good script for backing up MySQL databases that’s simple to install and configure — automysqlbackup (alternate sourceforge link). I suggest reading Marius Ducea’s post about automysqlbackup for some tips on how to use it, but you basically download it, edit a few settings at the top (e.g. database connection info), make the script executable, and then either run it manually or add it to a cron job for regular scheduling. It uses the well-known mysqldump utility to make organized directories of backups, and it rolls backups so you don’t end up with a bajillion dump files after running it for a month.

MySQLhotcopy is another Perl script (that isn’t based on mysqldump) that can also back up your MySQL databases. It actually backs up the database files themselves instead of creating a big text dump, and some people say it’s faster and better than mysqldump-based solutions, especially for very large and/or active databases. I haven’t used MySQLhotcopy, but it may be worth checking out if for some reason automysqlbackup or mysqldump won’t work for you.

Lastly, 33% of our readers submitted this great tip for copying a MySQL database from one server to another (thx Scott):

mysqldump --opt --compress --user=USERHERE --password=PWHERE
 --host=SOURCE.HOST.HERE SOURCE_DB_NAME | mysql --user=USERHERE
 --password=PWHERE --host=TARGET.HOST.HERE -D TARGET_DB_NAME -C
 TARGET_DB_NAME

Note that it should be entered all on one line.