how to backup mysql database

[anfast]

how to backup mysql database? Thanks.

[NickCatal]

You are going to need to use the mysqldump program.

Check out the official documentation on the program (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)

After you dump the database, you are going to want to compress it somehow (the dumped file will be a lot of INSERT INTO commands, over and over and over again) and then save it.

Do this and you should be able to rollback to previous versions of software, which is nice if you are upgrading something like a forum where an upgrade could break any plugins/themes you may be using.

Also useful for testing purposes. You can insert it so that it has a different database name, then you can mess with a real copy of your data without screwing up what your actual visitors are seeing

[Ray]

If you are a shared hosting customer, you will need to make sure to specify the host (mysql.steadfast.net) that you are connecting to. The command should look similar to:

mysqldump -h mysql.steadfast.net -u mysql_username -ppassword mysql_database > backup_file.sql

Note the lack of space between the -p and the password. That is intentional. If you do not specify the password like that, it will prompt for a password, which should not be an issue if running it manually. If you are creating a script for backing up the databases though, you would need to have that entered in there.

[gunwitch]

Is it possible to backup MySQL databases automatically?
(cron job or a tool able to create a copy of DB every time when DB content is changed)

[Ray]

Sure.

You could create a cron job through the control panel to automatically back up your databases to an FTP accessible location or come up with a lot of ways to store the backed up data.

Doing so every time any data is changed could be extremely resource intensive, but a cron job every so often would probably not result in any problems.

[Scoundrel]

I'd suggest to use LVM snapshots for backups. This is really great method to create consistent backups quickly.