Pin Me

Create a MySQL Database Backup

written by: Strobe•edited by: Michele McDonough•updated: 6/30/2011

Are you sure your website would be back up and running in a few hours after a catastrophe happens? If you use a MySQL database to store user information and other website information, you need to have backups of your database in case of a failure. This article will review some of backup methods.

  • slide 1 of 1

    Okay, you’ve finally got your website the way you want it and its growing fast with hundreds of users thus far. Then, the unthinkable happens; a server crash or other malfunction wipes everything out. Your entire database is gone and the only way to restore your site is with a MySQL database backup.

    By creating a backup of your MySQL database, you can better handle a catastrophe like the one listed above. You never know what can happen, and you sure wouldn’t want to alienate the users you fought so hard to get to visit your website. So make a backup of your database and do it often. There are a few ways to create a backup of your MySQL database depending on how you access the database.

    To backup your MySQL database using shell / telnet access:

    mysqldump –u user -p pass database-name > mysqlbackup.sql

    Simply fill in the user with your username, pass with your password, and database-name with the name of the mysql database you would like to backup. The filename mysqlbackup.sql will be the actual backup file that you will use to restore the database in case of a failure.

    To backup your mysql database using phpMyAdmin, you simply go to your export tab in phpMyAdmin; there you will be given options for exporting the file to various formats and options. A full backup is always best as the size of the backup is usually pretty small and can be edited if you don’t need to reinstall certain items. You can even zip the backup to make it smaller.

    To backup your MySQL database using cpanel, log in and click on the “Backups” link. There you will be given the option of creating a full backup with all the files and MySQL databases that your website has or just creating a backup of the selected MySQL database. The choice is yours but a full backup is recommended.

    Creating a weekly backup or better yet a daily backup is a good idea. Even if things seem to go pretty smoothly you never know what can happen and as you’ve probably heard before it’s better to be safe than sorry.

    After creating a MySQL database backup, it’s best to keep a copy of the MySQL database backup file on another computer or some sort of removeable media. This will help ensure a backup is available in case of fire or flood. Remember it’s better to be safe than sorry.