Results 1 to 25 of 33
-
06-02-2006, 09:41 AM #1Disabled
- Join Date
- Jun 2006
- Posts
- 12
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.
-
06-03-2006, 04:27 AM #2Disabled
- Join Date
- Jan 2005
- Posts
- 183
Hi,
Is there any way to backup using SHELL command?
-
06-03-2006, 05:39 AM #3Disabled
- Join Date
- Jun 2006
- Posts
- 12
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.
-
06-03-2006, 05:42 AM #4Disabled
- Join Date
- Jun 2006
- Posts
- 12
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
-
06-10-2006, 03:32 PM #5Newbie
- Join Date
- Jun 2006
- Posts
- 5
Thanks for this great article
-
06-12-2006, 11:11 AM #6Disabled
- Join Date
- Jun 2006
- Posts
- 6
Seems useful!
-
06-13-2006, 03:14 PM #7Junior Guru Wannabe
- Join Date
- Jan 2006
- Location
- White Bear Lake, MN USA
- Posts
- 56
why dont you just simply backup your /var/lib/mysql directory and be done with it?
-
06-25-2006, 01:26 PM #8WHT Addict
- Join Date
- Oct 2004
- Location
- Orihuela Costa
- Posts
- 162
Quick tip for larger databases (10 MB+) -- if you have problem to download it try firefox. IE sometimes doesn't download properly.
-
07-13-2006, 01:55 PM #9Junior Guru
- Join Date
- May 2006
- Posts
- 244
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/
-
07-18-2006, 01:14 PM #10Disabled
- Join Date
- Jul 2006
- Posts
- 34
Yeah this is useufl, Im going to run through it now.
Thanks
-
07-20-2006, 03:17 AM #11Disabled
- Join Date
- Jul 2006
- Posts
- 124
Hi,
Originally Posted by agweb
-
12-28-2006, 02:26 PM #12WHT Addict
- Join Date
- Dec 2006
- Posts
- 131
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.
-
12-30-2006, 10:19 AM #13Web Hosting Master
- Join Date
- Jul 2005
- Posts
- 1,062
Nice Artilce! Will help out a lot of people.
-
01-13-2007, 05:40 PM #14Newbie
- Join Date
- Dec 2005
- Location
- canada
- Posts
- 14
Very good post..thanks a lot
Buzz
-
01-28-2007, 01:33 PM #15New Member
- Join Date
- Jan 2007
- Location
- Saint Petersburg, Florida
- Posts
- 1
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
-
01-28-2007, 02:54 PM #16Web Hosting Guru
- Join Date
- Feb 2005
- Location
- localhost
- Posts
- 5,473
its nice to see people offer their knowledge free of charge. The world would be a much better place if their were more people like you guys...
Respectfully,
Mr. Terrence
-
01-29-2007, 02:02 AM #17Newbie
- Join Date
- Jan 2007
- Posts
- 19
BigDump, going to try it also
-
06-22-2007, 04:26 PM #18Junior Guru Wannabe
- Join Date
- May 2007
- Posts
- 81
For simple backups without PHPMyAdmin, I have written a short shell script that creates backups including files and MySQL database. It creates a TAR/GZIP archive and sends it by e-mail (to myself of course). For more detail and script source code, please have a look at http://www.kyusl.com/scr.php?mod=01
-
06-28-2007, 03:06 PM #19Junior Guru
- Join Date
- Nov 2002
- Location
- Warsaw, IN
- Posts
- 184
-
06-29-2007, 02:19 AM #20Junior Guru Wannabe
- Join Date
- May 2007
- Posts
- 81
Indeed they are. And there are standard tools to make a backup of MySQL by archiving files directly (and therefore faster). Have a look at:
http://www.linux.com/feature/62177
I think that MySQL documentation describes some "official" ways (without 3rd party tools) too.
-
07-02-2007, 09:33 PM #21Newbie
- Join Date
- Jul 2007
- Posts
- 15
Is it possible to sechdule backups??
-
07-03-2007, 03:11 PM #22Junior Guru Wannabe
- Join Date
- May 2007
- Posts
- 81
-
04-09-2009, 08:50 PM #23Newbie
- Join Date
- Apr 2009
- Posts
- 7
thanx for sharing
----------------
-
04-21-2009, 11:11 AM #24Newbie
- Join Date
- Apr 2009
- Posts
- 14
Thanks for sharing
-
04-21-2009, 03:30 PM #25Newbie
- Join Date
- Apr 2009
- Posts
- 11
I like Sypex Dumper Lite sypex.net/products/dumper/
<<signatures to be set up in your profile>>Last edited by bear; 05-11-2009 at 07:40 AM.