1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. This forum has been archived. New threads and replies may not be made. All add-ons/resources that are active should be migrated to the Resource Manager. See this thread for more information.

Database Backup..Local & Offsite..

Discussion in 'Tips and Guides [Archive]' started by TheBoss, Nov 29, 2010.

  1. TheBoss

    TheBoss Well-Known Member

    An Easy Way For Backups For None Techy People.

    After reading of Peggy`s problem in another thread I decided to try and write this guide, it will be a work in progress as I doubt I have the time to complete it in one go or I might not have enough allowences for uploading all the graphics needed.

    The script I am going to guide you through is called MySQLDumper , latest stable version is v1.24 and it has multiple language support.

    You can download the latest stable version from here:
    http://sourceforge.net/projects/mysqldumper/files/

    A good tip after downloading the archive and extracting to a folder on your PC is to simply remove the language files you do not require by navigating to the language folder and removing all extras,me being English I only leave the /en/ folder and the lang_list.php file.

    Now you are ready to upload by FTP to your server.
    Navigate to your public_html folder , create a new folder with any name you want ie; 123dump, open that folder and upload the contents of the folder you extracted, by default it will be called msd1.24stable. So you should be uploading 8 folders & 13 files only. ( The contents of msd1.24stable)
    When all files are uploaded you need to CHMOD the config.php file to 0777.
    You should also create a new folder called work and inside that folder create 3 new folders called backup,config & log. Now CHMOD those 4 folders permissions to 0777 also.

    You need your database username & password at hand to continue...

    Your ready to install the script...

    Navigate to the folder on your server that you gave a unique name , 123dump.

    http://yourdomain.com/123dump/
    You should be greeted with this screen below:
    [​IMG]

    If you deleted all other language files only English will show..

    Select Installation.
    Here you need only enter your database username & password, as below pic shows..
    [​IMG]
    After selecting Connect to MySQL you will be greeted by the following screen.

    [​IMG]

    Click, Save & Continue installation..

    Where you will be greeted by the following screen..

    [​IMG]

    This screen is the completion of the basic install of the script...

    More to follow below...

    Regards
     
  2. TheBoss

    TheBoss Well-Known Member

    Now we move on to the config and use area..

    Click , Configuration on left side then General will bring up the following:

    [​IMG]

    The settings shown should be default or close to it depending on your server setup , you can leave as is for now...

    For offsite automatic backup follow the below steps

    Now select FTP Settings , which will bring up this screen. The fields will be empty of course and I have added my details.

    [​IMG]

    You of course need to fill in your own details of the server you want the backup to go to , this backup is in addition to the one that is created in the 123dump/work/backup folder where you installed the script.
    When you have filled in the details click Test Connection , if all goes well it should show this screen below.

    [​IMG]

    If you see OK everything has gone well so far. You must remember to select activated or the backup will NOT transfer. As shown below:

    [​IMG]

    Now click Save or again things just will not get transfered..

    If your lucky enough to have multiple servers you can use the other 2 FTP upload options at the same time, but after every addition or change you must select activated then Save..

    Again you will be greeted by te following screen which will indicate in green text if all has gone well.

    [​IMG]

    This concludes the setup and configuration for remote database backups..

    More to follow..
     
    Decent60 and Digital Doctor like this.
  3. jmurrayhead

    jmurrayhead Well-Known Member

    I've been using MySQL Dumper for a while now. It really is a great script.
     
  4. TheBoss

    TheBoss Well-Known Member

    Now to the nitty gritty part...

    Select Backup in NavPanel as shown:

    [​IMG]

    By default the Select Tables checkbox is clear & so is the Enter Comment text area.
    I use both areas as it gives me more control of what I am backing up and easier to navigate to when I need to do a restore. When ready select Start New Backup and it shows the following screen:

    [​IMG]

    You can either use the Select All or choose individually the tables you want to back up.

    Again when ready select Start Backup..

    You will be greeted by the following screen which tells you how your backup is progressing..

    [​IMG]

    When the backup is finshed it will display the following...

    [​IMG]

    It is reporting that the database backup has completed to both the onsite and offsite destinations.
    In my case it was an 80 meg database compressed to 14.4 Meg that completed both tasks in 49 seconds..

    Thats how easy a backup is...

    Now a restore..

    Go back to the Configuration / General area and make sure Delete Tables before resoring checkbox is selected, as shown

    [​IMG]

    Remember to click Save...

    By selecting Restore in NavPanel you will be greeted by a screen similar to the one below , the more backups you have the more that will show. This one only shows one as I only did this backup for this guide.

    [​IMG]

    The above only covers the basics that are needed , MySQLDumper is capable of many more things.
    Like splitting , dropping tables and merging databases if needed in times of dire need.
    You can also setup a cron job to do your databases backups without any intervention by you after initial setup..

    A quote from a tip on MySQLDuper`s site:
    I hope this guide helps even just one person from loosing their data....

    Enjoy..

    Regards..
     
  5. Cool

    Cool Active Member

    i also can just recommend mysqldumper. i´ve used it for some years now and it was the best one i ever had.
     
  6. Brogan

    Brogan XenForo Moderator Staff Member

    Nice job.

    I've moved this to "Tips, Tricks and Guides" as it's a useful resource.
     
  7. TheBoss

    TheBoss Well-Known Member

    You can by using your Server or Hosting CPanel password protect the folder where the dumper script resides, this stops anyone from gaining entry to the script.

    Regards..
     
  8. Elizabeth

    Elizabeth Well-Known Member

    Is this independent of operating system on your computer? Or are there separate Windows/Mac/Linux versions?
     
  9. TheBoss

    TheBoss Well-Known Member

    Quote From MySQLDumper site:



    Regards..
     
  10. Elizabeth

    Elizabeth Well-Known Member

    Thanks, altho I was asking about the Mac since most sites assume you run windows.
     
  11. steven s

    steven s Well-Known Member

    It runs on your website. You can also have it backup on a regular basis automatically.
    All this is fine as long as your server doesn't crash.
    I was ftping to another server when I used it.
    I also started using http://backup-smart.com/ which copies files from cPanel to your local drive.
     
    Elizabeth likes this.
  12. Digital Doctor

    Digital Doctor Well-Known Member

    Great tutorial.
    Thanks for taking the time for thorough documentation / screenshots.
    :)

     
  13. TheBoss

    TheBoss Well-Known Member

    One of the many plus points of MySQLDumper is its ability to pick and choose what tables you want backed up and restored...

    Lets say your forum goes belly up for some reason , which does happen as we all know.. You can at the restore stage select what tables you want restored from
    your latest backup. Lets say a vBulletin site goes down , with addons & hacks there could be over 200+ tables & you know what you have recently added has caused the forum failure you simply select every table manually bar the tables the new addon / hack created..
    You must remember when restore a backup though to make sure the script drops all tables 1st...So a clean restore can complete..

    In extreme cases I have managed to salvage a site by patching 3 seperate backups by simply dropping certain tables in the initial restore , then adding other tables from a previous backup & so on. To achive this you select clear tables before restore in the initial restore , complete the restore but dont try and open the forum as it will crash. Then go back and de-select clear tables before restore so that its not dropping anything. Choose the next backup your pulling from and select the tables you want and hit restore. You can repeat this proccess as many times as need be to get a site back up...

    Yes , it can be a pain in the ass to do it but its better than loosing a site and years of work....

    I would suggest you try this method on a test server first just so you get used to the steps involved...

    I once forgot to de-select clear tables before restore and lost about 4 hours work in the blink of an eye ( And a few swear words )..

    Regards..
     
  14. Floris

    Floris Guest

    Looks as complicated as using phpmyadmin or a shell command, tbh.
     
  15. TheBoss

    TheBoss Well-Known Member

    Floris , its no more complicated than setting up a simple script... Upload & setup takes about 4 mins max.

    Majority of the screens and directions posted are simply the default ones apart from database username & password...

    Simple as pie , more so to someone like your self who is tech savy..

    Regards..
     
  16. Floris

    Floris Guest

    I rather ssh -l user domain.com
    pass: ****

    mysqldump --opt -Q -u dbusername -p databasename > backupname.sql
    pass: ****

    And then FTP the backupname.sql to the remote server.

    3 step process that's guaranteed to work.
     
  17. TheBoss

    TheBoss Well-Known Member

    I dont doubt for one minute your prefered method...

    But as the BIG RED letters in the 1st post points out , this is a Guide for None Tech Savy People..

    Not everyone is comfortable using ssh or command line operations , thats why GUI interfaces were thought of to make tasks easier for people..

    Your 3 steps maybe guaranteed to work for you, but I have restored scores of corrupt databases for people with my prefered method and NEVER had a failure yet . So I stick to what works for me , more so if it means I need to manipulate multiple backups into one restored database. I doubt your method would be good for that but I may be wrong, it wouldnt be the first time..

    Regards..
     
  18. Floris

    Floris Guest

    If someone can get an ftp client, download it, unpack it, install it, and configure it to connect, connect with it, find the right folders, put the php files there, and configure its settings, and run it from the browser, fill in those details, etc .

    Then why can't they go to a terminal and ssh in and type the mysql command and use the .sql dump via ftp or otherwise?

    They're both not methods for tech savvy ppl in my opinion.
     
  19. TheBoss

    TheBoss Well-Known Member

    Well from one tech savy person to another , we will just have to disagree Floris..

    Before I make a judgement though I tend to at least try another method , this I have done..

    I gather you havent tried MySQLDumper by your previous comments and as the old saying goes...

    " Dont knock it until you`ve tried it "

    Regards..
     
  20. Floris

    Floris Guest

    Your help is of great help! Don't get me wrong. I am sure a lot of people will be helped with this.
    I just wish that these people would realize there are other methods and they're not as complex as they sound or seem.
    It's worth a try, either way. At the end it's about getting the job done of course.
     

Share This Page