Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1

    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.

  2. #2
    Hi,

    Is there any way to backup using SHELL command?

  3. #3
    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.

  4. #4
    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. #5
    Thanks for this great article

  6. #6
    Seems useful!

  7. #7
    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?

  8. #8
    Quick tip for larger databases (10 MB+) -- if you have problem to download it try firefox. IE sometimes doesn't download properly.

  9. #9
    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. #10
    Yeah this is useufl, Im going to run through it now.

    Thanks

  11. #11
    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. #12

    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. #13
    Nice Artilce! Will help out a lot of people.

  14. #14
    Join Date
    Dec 2005
    Location
    canada
    Posts
    13
    Very good post..thanks a lot

    Buzz

  15. #15
    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

Page 1 of 3 123 LastLast

Related Posts from theWHIR.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •