METHOD 1 – phpMyAdmin
You can use phpMyAdmin to backup/restore your database if it is relatively small (~200k)
If your database is larger than that please skip to METHOD 2 – command line
BACKUP:
1) Select the database you’d like to back up from the column on the left
2) Click on “Export” from the top set of tabs
3) Select the tables from the list that you would like to backup, if you want to backup the
entire database hit “Select All”
– Select “Structure and data” from the bullet list
4) Selection boxes:
– Check the “Add ‘drop table'” box if you are moving the database to a new location and don’t
want to merge the old table with an existing one
– Click the “Save as file” box
– Use the “Save as file zipped” if you want to compress the backup before downloading it from
the server
5) Click the “Go” button, when prompted save the file to your local computer
RESTORE:
1) From the column on the left select the database to restore the from backup, if one doesn’t exist
you must first create it.
2) Click on “SQL” from the top set of tabs
3) Click on the “Browse” button next to “Or Location of the textfile:” near the bottom
4) Browse to the local backup and click “Open”
– If you have the local backup in a non-text file format, e.g. you selected “Save as file: zipped”
when you backed up the database, you’ll have to unzip the file on your local computer before you
can select it during this step
5) Click the “Go” button
– You should get a message like this:
Your SQL-query has been executed successfully :
The content of your file has been inserted. (X Instructions)
If not you might have a corrupted backup
METHOD 2 – command line
This method works regardless of the size of your database. You must have SSH access to your server. On (gs) plans you can invoke SSH access from within your Control Panel. On (dv) plans you must enable Shell Access through Plesk.
BACKUP:
1) Log into your server via SSH and cd into a directory where your user has write access. On (ss) plans
you would do something like this:
cd /var/www/html/
2) Enter the following command:
mysqldump –add-drop-table -u Username -p dbname > dbname.sql
– omit the ‘–add-drop-table’ argument if you’ll want to merge this backup with an existing database
upon restoral
– Where ‘Username’ is replaced by the mySQL username. On (ss) plans this user is the same as the
administrative FTP user. On (as) plans this user can be found by logging into Plesk, clicking
on the domain and going to databases and clicking on the database to be backed up.
– Replace ‘dbname’ with the name of the database to be backed up.
– Replace dbname.sql with what you’d like to name the backup.
3) Enter your mySQL password at the prompt. If you don’t know it you can reset it in your webcontrol or Plesk
admin panel
– If you get an error that looks like this:
ERROR 1045: Access denied for user: ‘Username@localhost’ (Using password: YES)
you have entered an incorrect password, please retype it carefully or reset it to something else via
the webcontrol panel or Plesk administrator
– On (ss) plans if you have reset the password in the webcontrol panel and are sure you’ve entered it correctly
on the command line this might be a permissions problem. Try renaming your database to something else in the
webcontrol panel and then back to the original, this often fixes permissions problems.
4) Use FTP to download the file to your backup location, probably your local computer.
– Don’t forget to delete the backup from your public html directory after you’ve found a safe place for it.
You don’t want to leave your backup lying around where anyone with a web browser can download a copy.
RESTORE:
1) Use FTP to upload the file to your server, your public html directory will work for now
– Don’t forget to delete the backup from your public html directory after you’ve done the database restoral.
You don’t want to leave your backup lying around where anyone with a web browser can download a copy.
2) Log into your server via SSH and cd to the directory where you’ve uploaded the file. On the (ss) plan if you
uploaded the backup into your public html directory you would use the command:
cd /var/www/html/
3) Enter the following command:
mysql -u Username -p dbname < dbname.sql
– Where ‘Username’ is replaced by the mySQL username. On (ss) plans this user is the same as the
administrative FTP user. On (as) plans this user can be found by logging into Plesk, clicking
on the domain and going to databases and clicking on the database to be restored.
– Replace ‘dbname’ with the name of the database to be restored.
– Replace dbname.sql with the name of the backup.
If you have a zipped backup of your database you can use this line instead:
gunzip < dbname.gz | mysql -u Username -p dbname
– Where ‘Username’ is replaced by the mySQL username. On (ss) plans this user is the same as the
administrative FTP user. On (as) plans this user can be found by logging into Plesk, clicking
on the domain and going to databases and clicking on the database to be restored.
– Replace ‘dbname’ with the name of the database to be restored.
– Replace dbname.gz with the name of the backup.
4) Enter your mySQL password at the prompt. If you don’t know it you can reset it in your webcontrol or Plesk
admin panel
– If you get an error that looks like this:
ERROR 1045: Access denied for user: ‘Username@localhost’ (Using password: YES)
you have entered an incorrect password, please retype it carefully or reset it to something else via
the webcontrol panel or Plesk administrator
Thank you!
By: Tuan on November 28, 2006
at 4:51 am
[…] In any case, a thorough analysis of the mysql database was in order and I decided to post this quick tutorial for performing quick / lengthy table checks for offline and online MySQL databases. […]
By: How to repair damaged MySQL tables | STAR dot Hosting Blog on May 12, 2009
at 12:33 pm
[…] In any case, a thorough analysis of the mysql database was in order and I decided to post this quick tutorial for performing quick / lengthy table checks for offline and online MySQL databases. […]
By: How to repair damaged MySQL tables | VPS Canada Directory on November 9, 2009
at 11:33 am
[…] In any case, a thorough analysis of the mysql database was in order and I decided to post this quick tutorial for performing quick / lengthy table checks for offline and online MySQL databases. […]
By: How to repair damaged MySQL tables » Server~Techs on March 20, 2010
at 10:16 am
[…] 0 Times in 0 Posts HowTo: Back-up / Re-store MySQL DataBase with myphpadmin gui or mysqldump command line tool here is link for export/backup and import/restore table on database with myphpadmin ** below […]
By: Wordpress Database Backup/Restore Plugin - Bizzteams on May 8, 2012
at 9:10 am