• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
  • 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..

TheBoss

Well-known member
#1
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:


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

After selecting Connect to MySQL you will be greeted by the following screen.



Click, Save & Continue installation..

Where you will be greeted by the following screen..



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

More to follow below...

Regards
 

TheBoss

Well-known member
#2
Now we move on to the config and use area..

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



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.



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.



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:



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.



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

More to follow..
 

TheBoss

Well-known member
#4
Now to the nitty gritty part...

Select Backup in NavPanel as shown:



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:



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



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



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



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.



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:
You have a flatrate and your computer is always online? Get yourself an account at DynDNS.org. Get a DynDNS-Client that updates your IP-Adress at the DynDSN-Server. Install a FTP-Server on your computer (i.e. FileZilla) and create a valid user. Tell MySQLDumper the userdata and create a cronjob to start the perlscript of MySQLDumper at the time you want to.
Now you don't have to do anything. Just wait until MySQLDumper logs on to your FTP-Server and sends the backup automatically.
I hope this guide helps even just one person from loosing their data....

Enjoy..

Regards..
 

TheBoss

Well-known member
#7
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..
 

steven s

Well-known member
#11
Is this independent of operating system on your computer? Or are there separate Windows/Mac/Linux versions?
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.
 

Digital Doctor

Well-known member
#12
Great tutorial.
Thanks for taking the time for thorough documentation / screenshots.
:)

If it didnt save it I wouldnt blame MySQLDumper for that..

More probably the execution time allowed in your php setup at your host provider, simply ask them to double the allowed time....

I have never had ANY failures with this and I have been using it since Noah built his Ark.. The most reliable method there is..

And some of my backups using this are MASSIVE not large...

Also: My backup is bigger than 1 GB. This way it is "hard to handle". What should I do?

Jump to the configuration and use the "multipart feature". Choose a dumpfile size and watch MySQLDumper splitting the big dump into separated files.
MySQLDumper is NOT subject to php timeouts.. PHP in itself is subject to the timeout limits setup by whoever installed the software on the server...

IE; If PHP is set to 5 mins instead of the normal 30 seconds when its compiled I doubt anyone would ever have any trouble at all...

The problem …


A PHP script has a maximum execution time that is usually set to 30 seconds on most server installations. A script running longer than this limit will simply stop working. This behavior makes backing up large databases impossible. Maybe you already had this specific problem when using other tools.
MySQLDumper fills a gap …


MySQLDumper uses a proprietary technique to avoid this problem. It only reads and saves a certain amount of data, then calls itself recursively via JavaScript and remembers how far in the backup process it was. The script then resumes backing up from that point.
The restore process is similar. Unlike other tools, splitting and splicing of large backup files is no longer necessary.
MySQLDumper can write the data directly into a compressed .gz file. The restore script is able to read this file directly without unpacking it. You can also use the script without compression, but using Gzip saves a lot of bandwidth. You can even configure the script to automatically send the backup file to an FTP account or your email adress.

I wrote this as guide for those who need a little help , not as a defacto dictatorship stating people have to use it...

If your tech savy and dont need the guide great use whatever floats your boat...

Or if your really tech savy and have 15 mins spare write your own guide then people will have a choice , the more guides the better I say...

Regards..
 

TheBoss

Well-known member
#13
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..
 

TheBoss

Well-known member
#15
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..
 
F

Floris

Guest
#16
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.
 

TheBoss

Well-known member
#17
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..
 
F

Floris

Guest
#18
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.
 

TheBoss

Well-known member
#19
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..
 
F

Floris

Guest
#20
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.