Backing up and restoring a MySQL database

If you use a database, you need to back it up. More to the point, sooner or later you will need to restore it.

TO BACK UP WITH PHPMYADMIN
Open phpMyAdmin in a web browser. On a local installation this might be at http://localhost/phpmyadmin/. On a hosted account, see your host’s administration tools.

In the left frame, select the database you want to export (e.g. vtigercrm510). In the right frame, click on the Export tab.

In the “Export” box you will see a list of all the tables; click “Select All”. Insure that “SQL” is selected as output format. Check “Structure,” “Add DROP TABLE,” “Data,” and “Save as file.” Do not change the file name template. You may wish to select compression. Click “Go”. You will be asked for a path and filename.

When finished, click on the “Log out” icon in the left frame.

TODO: Does phpMyAdmin have a scheduled backup function? If so, document it.

TO BACK UP WITH MYSQL ADMINISTRATOR
Open MySQL Administrator. Be aware that it runs with the permissions of the system user that opened it regardless of the MySQL user you log in as.

Most hosting companies disallow remote access to MySQL by default, and you will need to enable it prior to logging in with MySQL Administrator.

In the left column, choose “Backup”. Select a database and using the green arrow add it to the backup list.

In the Advanced Options tab under Backup Execution, select the type of backup appropriate for your tables and “Backup selected database completely”. Under Output File Options, select “Add DROP Statements.”

You may want to save your project and/or schedule it to run periodically. If you have it run periodically, be sure to have older backups automatically deleted via cron.

Press “Start Backup” and select the location to back up to. If you have it run periodically, choose a location that will be covered by your regular system backups. Insure that the backup finishes properly.

TODO: Does MySQL Administrator offer compressed backups? Does MySQL Administrator work remotely on hosting accounts even if no ssh access is provided? Can MySQL Administrator schedule backups on remote hosting accounts? Test and document.

TO BACK UP WITH A BASH SCRIPT
The script, with thanks to John Bokma.

TO PERFORM AUTOMATED SCHEDULED BACKUPS
TODO: Write this section, using cron and mysqldump. Point out that this should be part of any standard backup routine.

TO RESTORE WITH PHPMYADMIN
Open phpMyAdmin in a web browser. On a local installation this might be at http://localhost/phpmyadmin/. On a hosted account, see your host’s administration tools.

In the left frame, select the database you want to import. If it does not appear in the left frame, create it: in the left frame press the “Home” icon, and in the right frame find “Create new database”. Provide the name of the new database and press “Create”. Check the left frame to make sure you are now in the database you want to import.

At the top of the right frame, select “Import”. In File to import, choose the database to restore. Select any other desired options (I use the default values) and press “Go”.

When finished, click on the “Log out” icon in the left frame.

TO RESTORE WITH MYSQL ADMINISTRATOR
MySQL Administrator cannot read backup files created by mysqldump. In my limited experience this seems to mean MySQL Administrator can only read backup files created by itself. TODO: Confirm that other tools such as phpMyAdmin can read backup files created by MySQL Administrator.

Open MySQL Administrator. In the upper left column, choose “Restore Backup”. In the lower left column, select the backup file you wish to restore (you may have to press the “Change Path” button) and the charset to use (usually UTF-8). In “Restore selected tables in” select “New Schema” and give the name of the database you want to recreate. Press “Restore Backup”.

If you are restoring a database that was not saved with “Add DROP Statements” enabled, your restoration may fail with an error message about a table already existing. To resolve this, delete the existing database. In the left column, choose “Catalogs” and right-click on the schema (database) you wish to delete. Select “Drop schema”. Then right-click on an existing schema and select “Refresh schemata” to insure that the database was dropped. Then proceed from step 2.

NOTES
Aside from the user databases, there are also the two system databases information_schema and mysql. TODO: Determine if it is advisable to back up or restore them.

REFERENCES
How to restore your Mysql database with phpMyAdmin (out of date but useful)
MySQL Administrator: backup and restore

Advertisements

About Warren Post

So far: Customer support guy, jungle guide, IT consultant, beach bum, entrepreneur, teacher, diplomat, over-enthusiastic cyclist. Tomorrow: who knows?
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

6 Responses to Backing up and restoring a MySQL database

  1. Pingback: Installing and configuring vtiger | Warren's tech notes

  2. Pingback: Installing MySQL locally | Warren's tech notes

  3. Pingback: Installing a new distribution | A maze of twisty little passages

  4. Pingback: Backing up a workstation with Back In Time | A maze of twisty little passages

  5. Pingback: Backup and disaster recovery solutions for Linux | A maze of twisty little passages

  6. Pingback: Backing up a workstation with dkopp | A maze of twisty little passages

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s