Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
Backup/restore using phpMyAdmin/SSH
Snake
Join Date: Mar 2005
Posts: 3,832

Cleveland, OH
by Snake Snake is offline 01 Feb 2006

Backing Up & Restoring a MySQL Database using phpMyAdmin / SSH
=======================================================

This is just a short mini-tutorial explaining how to backup parts of the mySQL database using phpMyAdmin and also via SSH (Secure Shell) access. This tutorial should be of use to most people, as it's an important part of any website/forum maintenance.

So, let's get started...

Step 1 - Backing up using phpMyAdmin

Note: Most good hosts provide phpMyAdmin (mySQL administration). If you do not know where it is or even if it exists on your server, just get in touch with your host.
  1. Login to phpMyAdmin.
  2. Select the database you wish to use.
  3. On the navigation bar on the top, select "Export" (besides SQL, Structure, Search, Query... etc.)
  4. You should now be presented with a nifty little page which shows all the tables in the database, SQL Options and Save Types. *
  5. To backup the whole database, click "Select All" under the list of tables in the page.
  6. To backup a selected few, just hold down CTRL on your keyboard and select the tables you wish to backup (release the key when all selecting is done).
  7. SQL Options can be left as default, but I suggest ticking the "Add DROP TABLE" option as if you are restoring a backup on a database that already exists and has the table in it - you will get a lot of errors! This way, by ticking the "Add DROP TABLE" option you will avoid the errors. For more information on the options just click the little "?" icon after SQL Options.
  8. If you want to save the backup as a file (recommended) then tick "Save as file" - Leave file name as is or edit for your needs. Select "gzipped" as compression. Now click "Go". If asked, choose "Save to disk" and save it wherever on your computer (it may take sometime depending on the size).
  9. If you want to show the whole backup SQL on your browser window, don't tick "Save as file" - once you have selected the tables you wish to backup (explained in step 5/6) click "Go". The page will now change and should show the SQL information (it may take sometime depending on the size). Copy and paste it to a text file or do whatever you want with it!
Note: Yes, the tables of the database will be shown to the far left in a frame, but they will also be displayed in a different form on this new page to the right in phpMyAdmin.

Congratulations! You have successfully backed up your database / selected tables!

Step 2 - Restoring your database using phpMyAdmin

Note: Most good hosts provide phpMyAdmin (mySQL Administration). If you do not know where it is or even if it exists on your server, just get in touch with your host.
  1. Login to phpMyAdmin.
  2. Select the database you wish to use.
  3. On the navigation bar on the top select "SQL" (besides Export, Structure, Search, Query... etc.)
  4. You should now be presented with a little page which allows you to run SQL query/queries on the database, either by inputting the query/queries to the input box or by locating a text file on your computer.
  5. You now have 2 options: 1) Paste the SQL which you backed up earlier in the "Input Box" and click "Go" or 2) If you saved the file on your PC then use the option below the first one; Click Browse > Locate the File > Click "Go". (Note: Most servers set a "Max File Size" for uploading the SQL, the size appears beside the Browse button.)
  6. It may take sometime for the file to be uploaded and fully run, so give it a chance. Once it worked, it will bring you back to the same page and should confirm if all went well "Your SQL-query has been executed successfully: The content of your file has been inserted."
Congratulations! If all went well, you have successfully restored your backed up database!

Step 3 - Backing up using SSH

Note: Some hosts do not offer SSH access, I suggest contacting your host and asking him/her if SSH access is enabled and if so the details. A good program to use for connecting via SSH is PuTTY (Get it at: http://www.chiark.greenend.org.uk/~sgtatham/putty/). Don't know how to use it? Search on Google for a tutorial.
  1. Connect to your host via SSH, login and run: mysqldump --opt -u user -p dbname > {path}/backup.sql
  2. You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line).
Congratulations! You successfully backed up your database using SSH.

Step 4 - Restoring using SSH

Note: Some hosts do not offer SSH access, I suggest contacting your host and asking him/her if SSH access is enabled and if so the details. A good program to use for connecting via SSH is PuTTY (Get it at: http://www.chiark.greenend.org.uk/~sgtatham/putty/). Don't know how to use it? Search on Google for a tutorial.
  1. Connect to your host via SSH, login & run: mysql -u user -p dbname < {path}/backup.sql
  2. You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line).
Congratulations! You have successfully restored your database using SSH.

That's basically it I think, I believe I have covered everything? Anything I missed? Comments or suggestions? Just let me know! Hope this tutorial was as useful to you as it is to me.

Thanks,
-Snake
www.metalgearforums.com

Last edited by Snake; 19 Dec 2007 at 11:25..
Views: 80683
Reply With Quote
Comments
  #2  
Old 03 Feb 2006, 02:12
RFViet RFViet is offline
 
Join Date: Jan 2006
My server just allows me to upload file size = 8Mb max. My sql file = 200 Mb. How do i restore ???
Reply With Quote
  #3  
Old 03 Feb 2006, 04:15
The Chief's Avatar
The Chief The Chief is offline
 
Join Date: Aug 2005
Originally Posted by RFViet
My server just allows me to upload file size = 8Mb max. My sql file = 200 Mb. How do i restore ???
You will have to use SSH, or ask your host to raise the max limit...
__________________
Think Hype Networks | MeandIsis
Reply With Quote
  #4  
Old 04 Feb 2006, 12:17
Snake's Avatar
Snake Snake is offline
 
Join Date: Mar 2005
Location: Cleveland, OH
Real name: Josh
Or you can split every tables with the maximum of 8MB in each file... That's the best way to go for!
Reply With Quote
  #5  
Old 07 Feb 2006, 09:48
Rhoads Rhoads is offline
 
Join Date: Dec 2004
I tried but i get a error:

Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect -/bin/bash-2.05b$

Whats the problem, i use the command from your post, with my user and database name.
Reply With Quote
  #6  
Old 07 Feb 2006, 14:37
Snake's Avatar
Snake Snake is offline
 
Join Date: Mar 2005
Location: Cleveland, OH
Real name: Josh
Hmm that's weird. I'm not sure about that error because it has nothing to do with my tutorial. And I bet you have done something wrong while attempting to restore your forums.
Reply With Quote
  #7  
Old 08 Feb 2006, 17:15
RFViet RFViet is offline
 
Join Date: Jan 2006
Originally Posted by Aftermath
Or you can split every tables with the maximum of 8MB in each file... That's the best way to go for!
Yeah , that's the way i want . How to do that ???
Reply With Quote
  #8  
Old 10 Feb 2006, 17:27
Logan70 Logan70 is offline
 
Join Date: Nov 2003
You can also try and use:

http://www.ozerov.de/bigdump.php

It will allow for large dumps to be staggered, making it look like smaller pieces.
Reply With Quote
  #9  
Old 12 Feb 2006, 03:04
Ramsesx's Avatar
Ramsesx Ramsesx is offline
 
Join Date: Aug 2005
Location: Southern Germany
I made it as described but my db file is only 1kb after downloading?
Reply With Quote
  #10  
Old 20 Feb 2006, 18:07
sinaluna sinaluna is offline
 
Join Date: Feb 2005
Should you close your board before backing up?

Thanks!
Reply With Quote
  #11  
Old 20 Feb 2006, 18:56
LincolnForums LincolnForums is offline
 
Join Date: Oct 2005
Originally Posted by Rhoads
I tried but i get a error:

Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect -/bin/bash-2.05b$

Whats the problem, i use the command from your post, with my user and database name.

This is due to a hosting problem, chances are good your host doesnt have something configured correctly. I had the same problem while using dreamhost

Originally Posted by sinaluna
Should you close your board before backing up?

Thanks!

while its not neccassary, but you do run the chance of loosing any posts/changes that are made while backing up
Reply With Quote
  #12  
Old 28 Mar 2006, 12:44
Snake's Avatar
Snake Snake is offline
 
Join Date: Mar 2005
Location: Cleveland, OH
Real name: Josh
Originally Posted by sinaluna
Should you close your board before backing up?

Thanks!
Well I'd say yes just in case you won't loose any forum data (such as threads, posts, members, etc) while performing a backup.
Reply With Quote
  #13  
Old 29 Mar 2006, 17:23
RFViet RFViet is offline
 
Join Date: Jan 2006
Originally Posted by Logan70
You can also try and use:

http://www.ozerov.de/bigdump.php

It will allow for large dumps to be staggered, making it look like smaller pieces.
thanks !!
Reply With Quote
  #14  
Old 02 May 2006, 00:00
imported_infitech's Avatar
imported_infitech imported_infitech is offline
 
Join Date: Apr 2004
Location: Queens, NY
Real name: Daniel Michel
Now i coul dnot backup using SSH FROM the old server because the old server did not support SSH, but i backed up using your phpmyadmin method and through admincp just in-case.

My new host offers SSH so i want to restore using SSH. Is this possible?

Also to i type in
Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Reply With Quote
  #15  
Old 03 May 2006, 06:12
Hornstar's Avatar
Hornstar Hornstar is offline
 
Join Date: Jun 2005
Real name: Matt
this command mysqldump --opt -u user -p dbname > /path....

Does it compress the database?

As my old database size was 929 megs (done through cpanel) and this one is 721 megs. (done through SSH)
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Article Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


New To Site? Need Help?

All times are GMT. The time now is 22:44.

Layout Options | Width: Wide Color: