Table of Contents
Backing up and Restoring a MySQL database with phpMyAdmin
To export/backup a database:
- Sign in to phpMyAdmin (see Logins and URLs).
- Select the database in the left navigation frame, which should bring up the database details in the main frame
- Select the Export tab
- Under Export, select SQL
- Select Structure if you'll be using the data to create new tables
- Select Add drop table if the data is intended to completely replace tables
- Select Data
- Select Save as File
- Select your preferred compression format (none, zipped, gzipped, or bzipped)
- Click Go (bottom-right)
The mysql data should download to your browser's default download destination.
To import/restore a database:
- Sign in to phpMyAdmin (see Logins and URLs).
- Select the SQL tab in the main phpMyAdmin frame
- Click Choose File
- Locate the SQL file on your computer
- Once you've selected your import file, click Go
If you get any Table already exists errors while importing data, re-save the sql with the *Add drop table* option checked, or simply delete the conflicting tables and run the import again.
Importing and Exporting a MySQL database from the command line
To export/backup a database
cd to whatever directory you wish to store the data, and enter:
mysqldump --default-character-set=latin1 -C -u USERNAME -p --opt --lock-tables=false --skip-add-locks --skip-extended-insert DATABASE > DATABASE.sql
Make sure you replace the values in CAPS with the correct info for your account (you can also change the character set if you wish).
To tgz compress the file DATABASE.sql for easier transportation:
tar czvf DATABASE.tgz DATABASE.sql
To import/restore a database:
cd to wherever the sql data is stored, and enter:
mysql -uUSER -pPASSWORD DATABASE < DATABASE.sql
Again, make sure to replace the values in CAPS.
