16 May 2024
Support Center
»
Knowledgebase
»
General
»
How can I Backup and Restore a MySQL database?
Login
[Lost Password]
Email:
Password:
Remember Me:
Search
-- Entire Support Site --
Knowledgebase
Downloads
Article Options
Add Comment
Print Article
PDF Version
Email Article
Add to Favorites
How can I Backup and Restore a MySQL database?
Solution
phpMyAdmin
You can use phpMyAdmin from within the Plesk control panel to backup and restore your database if it is less than 10MB. If your database is larger than that please skip to command line option.
BACKUP:
1. Click databases in the Plesk control panel.
2. Click on the name of the database you'd like to backup.
3. Click
DB WebAdmin
. This will open a new browser window. Please disable your pop up blocker or allow from *.dotcomhost.com.
4. Select the database you'd like to back up from the column on the left.
5. Click on
Export
from the top set of tabs.
6. Select the tables from the list that you would like to backup. If you want to backup the entire database click
Select All
.
7. Select
Structure and data
from the bullet list.
8. 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 tables with existing tables.
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.
9. Click the
Go
button, then when prompted save the file to your local computer.
RESTORE:
1. From the column on the left select the database that you want to restore to. If it doesn't exist you must first create it. This can be accomplished in the Plesk control panel.
2. Click on
Import
from the top set of tabs.
3. Click the
Browse
button next to Or Location of the textfile: near the bottom.
4. Browse to the local backup and click
Open
.
5. Click the
Go
button .
You should see a message like this: Your SQL-query has been executed successfully : The content of your file has been inserted
If you receive an error please try the command line option below.
Command Line
This method works regardless of the size of your database. You must have SSH access to your domain. If you do not already have SSH access to your domain, you my submit a request to support to have this service added to your account for $5 per month.
BACKUP:
1. Log into your server via SSH and cd into a directory where your user has write access such as the
/private/
directory.
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 restore
Where
username
is replaced by the MySQL username.
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 the Plesk control 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.
4. Use FTP to download the file to your local computer for backup.
RESTORE:
1. Use FTP to upload the file to your server, the
/private/
directory is the best place for this.
2. Log into your server via SSH and cd to the directory where you've uploaded the file.
3. Enter the following command:
mysql -u Username -p dbname < dbname.sql
Where
username
is replaced by the MySQL username.
Replace
dbname
with the name of the database to be restored.
Replace
dbname.sql
with the name of the backup.
4. 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.
Replace
dbname
with the name of the database to be restored.
Replace
dbname.gz
with the name of the backup.
5. Enter your MySQL password at the prompt. If you don't know it you can reset it in the Plesk control 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.
Article Details
Article ID:
39
Created On:
19 Jan 2010 09:37 AM
This answer was helpful
This answer was not helpful
User Comments
Add a Comment
Sharing is good. If you have a comment about this entry, please feel free to share. The comments might be reviewed by our staff, and may require approval before being posted. Questions posted will not be answered. Please submit a Ticket for support requests.
Fullname:
Email: (Optional)
Comments:
Back
Home
|
Register
|
Knowledgebase
|
News
|
Downloads
Language:
English (U.S.)
Help Desk Software By Kayako SupportSuite v3.70.02