Backup of Mysql database using PhpMyAdmin

[holhost]

Backup of Mysql database
It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.

0. Open phpMyAdmin.
1. Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
2. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
3. Select"SQL"-> for output format, Check "Structure" and "Add AUTO_INCREMENT" value. Check "Enclose table and field name with backquotes". Check "DATA", check use "hexadecimal for binary field". Export type set to "INSERT".
4. Check "Save as file", do not change the file name, use compression if you want. Then click "GO" to download the backup file.

Restoring a backup of a MySql database
1. To restore a database, you click the SQL tab.
2. On the "SQL"-page , unclick the show query here again.
3. Browse to your backup of the database.
4. Click Go.

[mack]

Thanls for the quick tips. You mentioned not to change the database name. I do change the name to "dbname-date" and store all backups in an archive, rather than simply replacing the old data with the new.

If you have a site where the data changes regularly then you need to be making regular backups. At least daily.

Having an archive of your backups can be very usefull if you need to later review some older data.

Thanks again for the post.

Mack.

[vordmeister]

I used to backup using phpmyadmin, but I found my backups were incomplete for larger databases and don't rely on that method any more.

These days I use mysqldump which creates an .sql file on the server that I can later download. Instructions I refer to are [vbulletin.com...]

If you do use PHPmyadmin check the content of the SQL file from time to time to make sure you have everything.

I'm on a windows server and to save time I've written a batch file that gives an easy weekly backup in zip format just in case the host's backups don't work, but that requires more privileges than you'd find on a shared host.

[mack]

I like vordmeister`s approach to automating the process. I imagine this would be fairly simple on a Linux box using a script triggered by a cron.

You need to make sure you download the files to get them coppied of the server. If the host`s server fails you might loose everything, including the automated backups.

Its good to see people are taking backups seriously. It is all to easy to simply rely on a host. If the said host then goes out of business and vanishes, so does your valable data.

Mack.

[lammert]

The main problem with using phpMyAdmin as a backup tool is that it is manual. You need discipline to perform the backup. Easier is to write a backup script under Linux which dumps the data to a SQL file regularly and copies it to another server or tape backup unit. Because a backup on the same machine has the same value as no backup.