mySQL - how do you do backups?

If you want to use phpMyAdmin, select your database, and then click on the Export tab. Select all your desired tables. Most other options can be left at their default, but i would check "Add DROP TABLE" because if you restore the backup, you want it to drop the existing tables before it restores the backup. I would also check "Extended Inserts" so it specifies the column names in the Insert queries. Last, check the box in the last section - "Save as file". Choose a compression method if desired.

If you need to restore that backup, click the Import tab, and upload the file there.

Another way to do it is to use the command line. The following command will dump all databases into a single sql file.

mysqldump -u root -ppassword --all-databases >dbbackup.sql'

Another backup method you could do is install a backup script that can do that automatically by using a cron job.

Also, be sure that if there is any sensitive info in your database that you take proper measures to protect the backup file, just as you can restore the data with it, so can anyone who gets the file. Myself, I immediately use the program AxCrypt, which is free software and very simple to use, to encrypt the downloaded fiel right away... Make sure you don't forget the password to decrypt later.

-Greg

Thanks guys!

I've done a backup...hopefully I wont ever have to use it!

Depending how big your database is and how often the content is added/changed you should idealy back up the database weekly (some do it daily).
Also remember backups can get corrupted, especially if zipped or compressed so check file after download. Zip discs, CD's and DVD's can also fail (and hard drives crash) so is best to save at least two copies, one on your pc and one on a portable media - just keep them safe.

One more thing, this may sound simple but is something a lot of people don't do - save the backup to your computer, don't leave it on your hosts site because if the site goes down the backup goes with it.

My hosting company does off-site backups daily. You may want to check with yours to see if they do.

we have mirrered the DB to a new machine.. that's also ok

The scripts I have been used have abutton or a link - backup your database. I always use that link. As far as I understand in this moment all database date copy to my web hosting account and that's all.

As was mentioned before, most hosts have an option for a mySQL backup to run as a CRON job. The time constraints vary, but mine runs every Sunday night, stores on the same server as my domains' data, and I download it.

You should look into that, rather than manual methods.

I guess that there must be option in Cpanel to make backups. Make sure first that your current wbhoster don't provide additional fees for that.

phpmyadmin can do the backup.
If your database not huge,you find some php code do the backup easy.
If you want a auto backup soft,you can use the mysqlautobackup.
It have a good speed and can compress the backup file , Backup files manager easy to use,
and can schedule backup and restore databases