hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Web Hosting Talk Tutorials : Hosting Security and Technology Tutorials : Backup of Mysql database using PhpMyAdmin
Reply

Forum Jump

Backup of Mysql database using PhpMyAdmin

Reply Post New Thread In Hosting Security and Technology Tutorials Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 06-02-2006, 09:41 AM
manuel_accu manuel_accu is offline
Disabled
 
Join Date: Jun 2006
Posts: 0

Backup of Mysql database using PhpMyAdmin


Backup of Mysql database
It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.

0. Open phpMyAdmin.
1. Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
2. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
3. Select"SQL"-> for output format, Check "Structure" and "Add AUTO_INCREMENT" value. Check "Enclose table and field name with backquotes". Check "DATA", check use "hexadecimal for binary field". Export type set to "INSERT".
4. Check "Save as file", do not change the file name, use compression if you want. Then click "GO" to download the backup file.

Restoring a backup of a MySql database
1. To restore a database, you click the SQL tab.
2. On the "SQL"-page , unclick the show query here again.
3. Browse to your backup of the database.
4. Click Go.



Sponsored Links
  #2  
Old 06-03-2006, 04:27 AM
thewebhostingdir thewebhostingdir is offline
Disabled
 
Join Date: Jan 2005
Posts: 181
Hi,

Is there any way to backup using SHELL command?

  #3  
Old 06-03-2006, 05:39 AM
manuel_accu manuel_accu is offline
Disabled
 
Join Date: Jun 2006
Posts: 0
Yes,

Backup without phpMyAdmin :

phpMyAdmin has some file size limits so if you have large databases it may not be possible

to backup using phpMyAdmin. Then you have to use the command line tools that comes with

Mysql. Please note that this method is untested.

Mysql backup without phpMyAdmin
PHPMyAdmin can't handle large databases. In that case straight mysql code will help.

1. Change your directory to the directory you want to dump things to:

user@linux:~> cd files/blog
2. Use mysqldump (man mysqldump is available):

user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver
-u mysqlusername -p databasename (tablename tablename tablename) | bzip2
-c > blog.bak.sql.bz2

Enter password: (enter your mysql password)
user@linux~/files/blog>
Example:
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c >

blog.bak.sql.bz2

Enter password: my-password
user@linux~/files/blog>

The bzip2 -c after the pipe | means the backup is compressed on the fly.

Sponsored Links
  #4  
Old 06-03-2006, 05:42 AM
manuel_accu manuel_accu is offline
Disabled
 
Join Date: Jun 2006
Posts: 0
Oh....!

I just forgot to post to restore MySql database using shell command:

Mysql restore without phpMyAdmin

The restore process consists of unarchiving your archived database dump, and importing it

into your Mysql database.

Assuming your backup is a .bz2 file, creating using instructions similar to those given for

Backing up your database using Mysql commands, the following steps will guide you through

restoring your database :

1. Unzip your .bz2 file:

user@linux:~/files/blog> bzip2 -d blog.bak.sql.bz2
Note: If your database backup was a .tar.gz called blog.bak.sql.tar.gz file, then,

tar zxvf blog.bak.sql.tar.gz
is the command that should be used instead of the above.
2. Put the backed-up sql back into mysql:

user@linux:~/files/blog> mysql -h mysqlhostserver -u mysqlusername
-p databasename < blog.bak.sql

  #5  
Old 06-10-2006, 03:32 PM
like_php like_php is offline
New Member
 
Join Date: Jun 2006
Posts: 2
Thanks for this great article

  #6  
Old 06-12-2006, 11:11 AM
innovative2001 innovative2001 is offline
Disabled
 
Join Date: Jun 2006
Posts: 4
Seems useful!

  #7  
Old 06-13-2006, 03:14 PM
agweb agweb is offline
Junior Guru Wannabe
 
Join Date: Jan 2006
Location: White Bear Lake, MN USA
Posts: 49
why dont you just simply backup your /var/lib/mysql directory and be done with it?

__________________
Garrett Bauer
Custom Realtor MLS programming solutions, design, hosting
Ag-webhosting

  #8  
Old 06-25-2006, 01:26 PM
MarbleHost.com MarbleHost.com is offline
WHT Addict
 
Join Date: Oct 2004
Posts: 144
Quick tip for larger databases (10 MB+) -- if you have problem to download it try firefox. IE sometimes doesn't download properly.

  #9  
Old 07-13-2006, 01:55 PM
tamar tamar is offline
Junior Guru
 
Join Date: May 2006
Posts: 232
I was looking for an automated way to do this for multiple databases, and I stumbled upon this tool which I highly recommend:

http://sourceforge.net/projects/automysqlbackup/

  #10  
Old 07-18-2006, 01:14 PM
[IG]Chris [IG]Chris is offline
Disabled
 
Join Date: Jul 2006
Posts: 33
Yeah this is useufl, Im going to run through it now.

Thanks

  #11  
Old 07-20-2006, 03:17 AM
mitto mitto is offline
Disabled
 
Join Date: Jul 2006
Posts: 116
Hi,

Quote:
Originally Posted by agweb
why dont you just simply backup your /var/lib/mysql directory and be done with it?
yes, this is very simple way for server administrator. what if client do not have access to the location : /var/lib/mysql/database?

  #12  
Old 12-28-2006, 02:26 PM
mifbody mifbody is offline
WHT Addict
 
Join Date: Dec 2006
Posts: 129
Thumbs up

I found a very easy way to do the backup with large databases: BigDump.php (google, you'll find the download links)

BigDump is a PHP file that you upload to your server with your database information. You then upload SQL files to the same directory, select the file in BigDump and then click "Import." What it does is go through the large file only processing X amount of lines at a time, then waiting Y amount of milliseconds and then processing X amount of lines, etc. My database is about 1gig thanks to my vbulletin attachments table, and I was able to successfully import my database to a new server using BigDump. It was really REALLY easy and I'm thankful that I found this file.

  #13  
Old 12-30-2006, 10:19 AM
mehboobashraf mehboobashraf is online now
Web Hosting Master
 
Join Date: Jul 2005
Posts: 803
Nice Artilce! Will help out a lot of people.

  #14  
Old 01-13-2007, 05:40 PM
buzzyback buzzyback is offline
Newbie
 
Join Date: Dec 2005
Location: canada
Posts: 13
Very good post..thanks a lot

Buzz

  #15  
Old 01-28-2007, 01:33 PM
gsmystic gsmystic is offline
New Member
 
Join Date: Jan 2007
Location: Saint Petersburg, Florida
Posts: 0
Very useful information as we cannot afford to lose any database data that was expensive and time consuming to collect in the first place.
Many thanks,
John

Reply

Related posts from TheWhir.com
Title Type Date Posted
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
Hackers Pack More Sophistication into Simple Scripts to Attack PHP Applications Web Hosting News 2013-09-09 11:52:48
Carbonite Launches Enhanced Server Backup for Small Businesses Web Hosting News 2013-07-26 16:31:57
BackupAgent Integrates Cloud Backup Platform with Citrix CloudPortal Web Hosting News 2012-09-18 16:14:43


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

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 Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?