[HOW-TO] Backup/restore using phpMyAdmin/SSH

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Darkblade
    Senior Member
    • Jul 2004
    • 690
    • 3.6.x

    [HOW-TO] Backup/restore using phpMyAdmin/SSH

    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 & 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 exsists and has the table in it - you will get a lot of errors! This way, by ticking the "Add DROP TABLE" 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/~sgtatha.../download.html). Don't know how to use it? Search on google for a tutorial.
    1. Connect to your host via SSH, login & 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 & 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 3 - 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/~sgtatha.../download.html). 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 & 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 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!
    Metal Gear Forums - Discussion on the popular series of computer and console stealth-based games.

    My Mods: Coming Soon | My Tutorials: Coming Soon
  • Remi
    Senior Member
    • May 2001
    • 222

    #2
    Hi

    If I have my "backup.sql" file on the server itself, Can I restore it using phpmyadmin.

    Let us say the path to the DB is:

    /home/user/backup.sql

    Thanks

    Comment

    • Darkblade
      Senior Member
      • Jul 2004
      • 690
      • 3.6.x

      #3
      Yes, you can. It should work fine.
      Metal Gear Forums - Discussion on the popular series of computer and console stealth-based games.

      My Mods: Coming Soon | My Tutorials: Coming Soon

      Comment

      • Cloud Strife
        Senior Member
        • Jun 2002
        • 440
        • 3.0.0 'Gold'

        #4
        Originally posted by Remi
        Hi

        If I have my "backup.sql" file on the server itself, Can I restore it using phpmyadmin.

        Let us say the path to the DB is:

        /home/user/backup.sql

        Thanks
        That depends on the size of your backup file... If its too big, then phpmyadmin can timeout... You might have to upload it table by table

        Comment

        • Darkblade
          Senior Member
          • Jul 2004
          • 690
          • 3.6.x

          #5
          Or you can just ask your host to increase the limit size of uploads.
          Metal Gear Forums - Discussion on the popular series of computer and console stealth-based games.

          My Mods: Coming Soon | My Tutorials: Coming Soon

          Comment

          • Tefra
            New Member
            • Mar 2005
            • 20
            • 3.6.x

            #6
            great it is what i was looking for . I tested and it works great except a little thing . Is there any table/size limit when you do the backup using the ssh way ? Well in my site i use one database for many things . It is totally 133 tables and 450mb size .
            Because of some problems i decided to split the forums from the other . When i use the ssh way it leaves some tables out . I don't know if this happens during the backup or the restore , i haven't download the file because of my download speed .

            Comment

            • Steve Machol
              Former Customer Support Manager
              • Jul 2000
              • 154488

              #7
              Originally posted by Tefra
              Because of some problems i decided to split the forums from the other . When i use the ssh way it leaves some tables out .
              I don't understand this. Please explain. If you are using this correctly, it will backup all the tables in the selected database.
              Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
              Change CKEditor Colors to Match Style (for 4.1.4 and above)

              Steve Machol Photography


              Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


              Comment

              • Tefra
                New Member
                • Mar 2005
                • 20
                • 3.6.x

                #8
                well i will do a full backup . I will restore it to the new database . I will test the forum if everything is ok and i will then drop the extra tables . I am noob but i think that this way the downtime of my site will be very small , some minutes i think .

                Btw in my question : Is there any table/size limit when you do the backup using the ssh way ? what is the answer ?

                If you have something else to propose please do it .
                Thank you in advance

                Comment

                • Steve Machol
                  Former Customer Support Manager
                  • Jul 2000
                  • 154488

                  #9
                  No, there is no such limit. I have seen databases in excess of 4Gb backed up successfully via SSH.
                  Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                  Change CKEditor Colors to Match Style (for 4.1.4 and above)

                  Steve Machol Photography


                  Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                  Comment

                  • tony adams
                    Senior Member
                    • Sep 2004
                    • 337

                    #10
                    If I have my "backup.sql" file on the server itself, Can I restore it using phpmyadmin.

                    Let us say the path to the DB is:

                    /home/user/backup.sql
                    am lost with this

                    if i had my backup file in public_html/restore/ what would be the string that i put in the file location/browse box

                    have tried most combinations and nothing happens

                    thanks

                    tony

                    Comment

                    • clearchanel
                      Senior Member
                      • May 2001
                      • 265
                      • 3.8.x

                      #11
                      database compression

                      I normally use the mysqldump -u[username] -p[password] databasename >/path/to/databasename.sql syntax, my backup result is just over 700 megs


                      When I use the mysqldump --opt -Q -u [username] -p [password] databasename > /path/to/backupname.sql syntax, the backup result is just over 400 megs.

                      Is this because the second syntax is compressing the database?

                      Comment

                      • Steve Machol
                        Former Customer Support Manager
                        • Jul 2000
                        • 154488

                        #12
                        Yes, the second option will create a smaller, optimized backup file. It's just as good though.
                        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                        Change CKEditor Colors to Match Style (for 4.1.4 and above)

                        Steve Machol Photography


                        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                        Comment

                        • anne123456
                          Member
                          • Dec 2004
                          • 34
                          • 3.0.3

                          #13
                          I'm trying to be sure I can backup and restore before our db goes 'live'. I used phpmyadmin and downloaded a backup no problem. However when I try to restore using these instructions I get the following error:
                          MySQL said:
                          #1007 - Can't create database 'dbname'. Database exists

                          Am I supposed to delete the db first and then restore or have I done something wrong?
                          Thanks,
                          Anne

                          Comment

                          • Steve Machol
                            Former Customer Support Manager
                            • Jul 2000
                            • 154488

                            #14
                            Either:

                            1. restore to a new db (recommended in case there is a problem)

                            Or:

                            2. Chose the 'DROP TABLE IF EXISTS' option when making the backup.
                            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                            Change CKEditor Colors to Match Style (for 4.1.4 and above)

                            Steve Machol Photography


                            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                            Comment

                            • welo
                              Senior Member
                              • Sep 2003
                              • 516
                              • 3.5.0 Pre-Release

                              #15
                              Note: For SSH backup/restore using puTTy, right click will paste your clipboard to the command line. This'll save you from having to type out the command and path everytime . Just make a backup.txt file will all the correct stuff and it'll go very fast.

                              It's also tidy to create a backup dir to dump to and restore from. Before downloading your backup you can always zip it:
                              zip backup-file.sql backup-file.zip

                              That'll save you some bandwidth.
                              "I can't seem to bring myself to say, 'Well, I guess I'll be toddling along.'
                              It isn't that I can't toddle. It's that I can't guess I'll toddle."

                              --Robert Benchley (1889-1945)

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...