Setting up mySQLdumper of a JaguarPC Server

[lloyd_borrett]

Setting up mySQLdumper on a JaguarPC Server

by Lloyd Borrett (lloyd@borrett.id.au - www.borrett.id.au) - 2006-07-31

Acknowledgements: The brilliant support team at JaguarPC who helped me to get mySQLdumper running on various web site accounts.


INTRODUCTION

You have a JaguarPC hosted web space using MySQL and you want to backup your MySQL database(s) automatically on a scheduled basis via a cron job. But you just don't know how to set that stuff up and manage it. What can you do?

The Solution.

Install mySQLdumper (http://www.mysqldumper.de/en/) and all of your sorrows are gone.

Using mySQLdumper you can run a backup of one or more of your MySQL databases whenever you like manually. The resulting backup files can be stored on your web server, emailed to you (if small enough) and/or sent via FTP to another server (great for when they are larger files).

You can also use mySQLdumper to schedule an automated MySQL backup via a cron job to run whenever you choose, as regularly as you choose.

mySQLdumper puts you in control of your MySQL database backups.

Note: mySQLdumper is Open-Source, that means it is free of charge. You can download and use the script for your own private purpose.


THIS STEP-BY-STEP GUIDE

When I first tried to install mySQLdumper on various JaguarPC hosted accounts, I struck a number of problems. Most were because of my lack of understanding Linux, PHP, Perl, Cron etc.

However, with the help of the JaguarPC support team, other JaguarPC users via the JaguarPC forums, plus the mySQLdumper team and users, I've been able to get mySQLdumper installed and running.

The least I could do by way of repayment, was to produce this step-by-step guide. (Plus it becomes my cheat sheet for rolling mySQLdumper out to my other JaguarPC accounts.)

For many of you, this guide may be way more detailed than you need it to be. For others, it may not be detailed enough.

Your comments, suggestions, recommendations, feedback etc. are most welcome. If you can help to make this guide better, then that would be just great.


-----------------------------

Step One. Download mySQLdumper

You need version 1.21 b12 or later. The earlier versions have a bug in the Perl crondump.pl script causing backups of multiple databases not to work. As I write this, the version you need is not available from the download space accessed from the mySQLdumper home page. You need to get it via the mySQLdumper forums at http://www.mysqldumper.de/board/viewtopic.php?t=1506.

Unfortunately, you don't get the ReadMe directory and installation details as a part of that download. However, if you following this guide carefully, you shouldn't need it. To get those files you'll have to download the "official" release from http://www.mysqldumper.de/en/.

-----------------------------

Step Two. Installation

- expand the files into a directory "mysqldumper".
- upload the folder "mysqldumper" to your web space.
- chmod the file "mysqldumper/config.php" to 777 (e.g. using Control Panel File Manager).

- chmod the directory "mysqldumper/msd_cron" to 755.
- chmod the file 'mysqldumper/msd_cron/crondump.pl" to 755.
- chmod the file 'mysqldumper/msd_cron/perltest.pl" to 755.
- chmod the file 'mysqldumper/msd_cron/simpletest.pl" to 755.

- start the script in your browser (http://www.<your domain name>/mysqldumper/).

-----------------------------

Step Three. Installation - Main Menu

Select your language and click on 'Installation'.

-----------------------------

Step Four. Installation - Step 1

Provide your hostname (typically 'localhost').

Provide your MySQL database user name and database username password. (On a JaguarPC account, all MySQL usernames are prefixed with your account username and then the database name, e.g. 'jmyaccnt_phpbb2'.)

NOTE: This MySQL database user name and password will be used to backup all of your MySQL databases. So if you have more than one MySQL database, you will also need to ensure that this database username has been granted access to the other MySQL databases. (Typically you would do this via the 'MySQL Database' option from the JaguarPC Control Panel.)

Click on 'Connect to MySQL'.

If you entered the details properly, you should be told that a database connect was established.

Click on 'Save and continue installation'.

----------------------------

Step Five. Installation - Step 4

On a typical JaguarPC account the installation script will not be able to create the working directories. You will need to create the directories specified manually, using Control Panel File Manager (Create New Folder) or your FTP program, and then chmod the folders to 0777.

- mysqldumper/work
- mysqldumper/work/backup
- mysqldumper/work/config
- mysqldumper/work/log
- mysqldumper/work/structure

Make sure you have created all of the above directories and chmod them to 777.

Click on 'Check my directories'.

The installation should then complete and take you to the mySQLdumper Home page.

----------------------------

Step Six. Create Directory Protection

mySQLdumper uses .htaccess for directory protection.

Click on the 'Create directory protection' option of the home page. It should be next to the highlighted red message 'Urgently recommended!'

Provide a name and password that you will use to access mySQLdumper. (You might choose to use your JaguarPC username and password, or something different.) Make sure you can remember what you specify.

Kind of encrypting: Select 'Crypt (Linux and Unix Systems)'.

Click on 'Create directory protection'.

mySQLdumper will then ask if it should be written now?

Click on 'Create directory protection' again.

On a JaguarPC account this step will typically fail.

You now need to create the "mysqldumper/.htaccess" and "mysqldumper/.htpasswd" files mySQLdumper will tell you about, with the exact content provided.

Once you have the "mysqldumper/.htaccess" and "mysqldumper/.htpasswd" files in place, click on 'Home'.

At this point, on some of my JaguarPC accounts mySQLdumper still works, and on some it does not. The new mySQLdumper .htaccess file conflicts with an existing .htaccess file I use in some of my home directories. On accounts where they aren't there, and accounts where PHP runs as CGI, it works just fine.

If it is working, you should get a message box from mySQLdumper asking for a username and password. Enter the values you used to create directory protection and click on 'OK'.

If everything is working, and you have been granted access, you should see the mySQLdumper home page and the Status Information.

----------------------------

Step Seven. Configuration

Now you need to configure your mySQLdumper installation so that it knows what it should do for you, and how to do it.

Click on 'Configuration'.

You will be taken to the 'Databases' configuration page.

There will be a list of the MySQL databases mySQLdumper has found on your system.

Select the 'activate MultiDump' box.

Select each MySQL database you want to include in your backup.

Click on 'Save'.

----------

Click on 'General'.

Make sure 'compressed (gz)' is selected.

Change the max size to an appropriate value for you.

Make sure 'GZip compression' is selected.

Select 'yes' for 'Multipart Backup'.

Change the maximum file size to an appropriate value.

Make sure 'Optimise Tables before Backup' is selected.

Make sure 'Complete Inserts' is selected.

Click on 'Save'.

----------

Click on 'Interface'.

Here you specify the language, theme etc. that mySQLdumper will use.

Change these values to whatever suits you.

Click on 'Save'.

----------

Click on 'Autodelete'.

Here you can specify how you want mySQLdumper to handle deleting old backups.

My choice is to select 'activated' so that 'delete backups automatically' happens.

Then I set 'Delete by age of files (in days)' to 30.
I set 'Delete by number of files' to 0.

Click on 'Save'.

----------

Click on 'Email'.

Here is where you tell mySQLdumper how to handle email notification. If you want to be notified when a backup runs, and you want to be emailed a copy of the backup files, the settings to use are:

Set 'Send email report' to 'yes'.

Then specify what email address you want the emails to be sent to.

Then specify the sender email address to use, e.g. webmaster@yourdomain.

Set 'Attach backup' to 'yes'.

Set the 'Maximum size of attachment' to a value large enough for your backup files.

For JaguarPC accounts, select 'sendmail'.

Change the sendmail address to '/usr/sbin/sendmail'.
IT WILL NOT WORK UNLESS YOU DO.

Click on 'Save'.

----------

Click on 'FTP'

Here is where you specify where mySQLdumper should FTP copies of the backup files to.

I have never used this option, so you are on your own here. When you're done making changes...

Click on 'Save'

----------

Click on 'Conscript'.

Here is where you set parameters for the mySQLdumper crondump script. The settings to use are:

Set 'File extension' to '.pl'.

Set 'Configuration file' to 'mysqldumper'.

Set 'Path of Perl scripts' to 'msd_cron/'.

Set 'Print output on screen' to 'yes'.

Set 'Log complete output' to 'yes'.

If you want to backup multiple databases via a multipart backup,

Set 'Use actual database' to 'no'.

Then for 'Database and Table-Prefix for the Cron job', use either '=Multidump Configuration' to just backup the database you specified back in the 'Databases' section of this configuration, or select '=all Databases=' to backup all of your MySQL databases.

I typically select '=Multidump Configuration='.

Set 'GZip compress' to 'activated'.

Set 'Send email report' to yes'.

Set 'Attach backup' to yes'.

I leave 'Send backup by FTP' set to 'no' as I have not used the FTP option as yet.

Click on 'Save'.

----------------------------

Step Eight. Check your Perl Installation

Now it is time to check that Perl is installed on your system such that mySQLdumper has what it needs to run scheduled automated backups.

Click on 'Backup'.

Click on 'Backup PERL'.

If you set the correct permissions on the "mysqldumper/msd_cron" directory and the script files in it, back in Step Two of this guide, the following should now be working.

Click on 'Test Perl'.

You should get a screen that says Perl works fine on your system.
If not, check your directory and file permissions again.
If your permission are okay and it is still not working, put in a support ticket to JaguarPC and ask them why Perl isn't running for you.

Click on 'Backup'.

Click on 'Backup PERL'.

Click on 'Test Perl Modules'.

You should get a screen saying that everything is installed okay.
If not, put in a support ticket to JaguarPC and ask them to install or fix whatever module is missing or malfunctioning.

----------------------------

Step Nine. Configuration of Perl Cron Script

This is how you customise the mySQLdumper crondump.pl script for your system, so that you can use Cron to schedule automated backups.

Click on 'Backup'.

Click on 'Backup PERL'.

Under 'Configuration for Perl Cron', look for two lines...

Entry in crondump.pl for absolute_path_of_configdir:
    /home/jmyaccnt/public_html/mysqldumper/work/config/

where 'jmyaccnt' will be your JaguarPC account name.

Open the file "mysqldumper/msd_cron/crondump.pl" in a text editor.

Lines 20 and 21 will be:

my $absolute_path_of_configdir="";
my $cgibin_path="";

Copy and paste the absolute_path_of_configdir value shown by mySQLdumper in your browser so that line 20 is something like...

my $absolute_path_of_configdir="/home/jmyaccnt/public_html/mysqldumper/work/config/";

Save the edited "crondump.pl" file.

Make sure the permission of the file is still chmod 755.

----------------------------

Step Ten. Running the mySQLdumper Perl Cron Script

It is about time that we checked that mySQLdumper and the Perl Cron script is now configured and working okay.

Click on 'Backup'.

Click on 'Backup PERL'.

Click on 'Run the Perl Cron script'

The condump.pl script should run and tell you what databases and tables it has backed up, plus what emails have been sent to you.

If you get any problems, check carefully through all of the above steps.

----------------------------

Step Eleven. Scheduling the mySQLdumper Perl Cron Script

This is how you tell the JaguarPC system how to schedule your automated mySQLdumper backup.

Click on 'Backup'.

Click on 'Backup PERL'.

Under 'Configuration for Perl Cron', look for two lines...

Commandline in the Shell or for the Crontab:
    perl /home/jmyaccnt/public_html/mysqldumper/msd_cron/crondump.pl config=mysqldumper.conf

where 'jmyaccnt' will be you JaguarPC account name.

Copy that Commandline.

Go into your JaguarPC Control Panel.

Under 'Tools', select 'Cron Jobs'.

Choose 'Standard'.

Enter the email address where the Cron Manager will send the cron output.

Paste the Commandline from your mySQLdumper installation in as the 'Command to run'.

Now set how regularly you want the cron job to run. On most of my JaguarPC accounts I usually set my backups to run once a day, somewhere between midnight and 4 a.m., my local time.

Note, the time you are specifying here is the time on the JaguarPC servers, which may not be your local time.

Click on 'Save Crontab'.

----------------------------

You should now have mySQLdumper installed and working on your JaguarPC account with a cron job scheduled to execute the MySQL database backup you specified as per the schedule you specified.

You can now use mySQLdumper to run a database backup whenever you choose, plus manage the backup files.

### ENDS ###

[zanerzack]

lloyd Thank you so much for this!!!

This backup of my databses is what I always wanted.

Only trouble I had was
$max_backup_files was not entered. I opened this file (mysqldumper.conf) in my editor
and added the value of 100.
AND NOW THE CRON runs with out errors.

Again, Thank you very Much.

[snowcreative]

chmod to 777 is generally not recommended now, especially if your server has been upgraded to using php as cgi. Do the folders HAVE to have this setting, or will they work with 755? Also, be sure to set the permissions on config.php to something like 444 when you're done editing the settings. You can change it back to 777 in the future if you need to change settings. That file should be kept locked down to prevent unwanted tampering.

[loki1023]

Anyone have any ideas how to get this script to work ? I get to the create the .htaccess files and I get a Internal Server Error and can procede no further =(

Update: I got around this issue by skipping the "Create Directory Protection" step in mysqldumper and using the web protection in cpanel.

[lloyd_borrett]

G'day,

I think the current JaguarPC setup lets the part where you Create Directory Protection, change the properties of the mysqldumper directory.

I think you'll find an error messge in your Error Log saying that your mysqldumper directory is writable by others.

If you chmod mysqldumper to 755 it will work.

Best Regards, Lloyd Borrett.

[scorpsy]

A big thanks to lloyd for this greatly detailed tutorial.

All went well running MSD v.1.22

[madzao]

#!C:\perl\bin\perl.exe w use strict; use CGI::Carp qw(warningsToBrowser fatalsToBrowser); warningsToBrowser(1); print "Content-type: text/html\n\n"; print '', "\n"; print "\n"; print "\n"; print "Wenn Du das siehst, funktioniert Perl auf Deinem System !

"; print "If you see this perl works fine on your system !"; print "\n"; # Wenn Du diese Zeile hier siehst, dann wird Perl nicht ausgefuehrt. # Frage Deinen Hoster, ob und wie Du Perl aktivieren kannst. # # If you can read this line Perl is not executed. # Ask your host how to activate Perl. #

I got the warning like my attachment or like above, what's wrong with my pc ? My Perl already activated .. thx

[madzao]

#!C:\perl\bin\perl.exe w use strict; use CGI::Carp qw(warningsToBrowser fatalsToBrowser); warningsToBrowser(1); print "Content-type: text/html\n\n"; print '', "\n"; print "\n"; print "\n"; print "Wenn Du das siehst, funktioniert Perl auf Deinem System !

"; print "If you see this perl works fine on your system !"; print "\n"; # Wenn Du diese Zeile hier siehst, dann wird Perl nicht ausgefuehrt. # Frage Deinen Hoster, ob und wie Du Perl aktivieren kannst. # # If you can read this line Perl is not executed. # Ask your host how to activate Perl. #

I got the warning like my attachment or like above, what's wrong with my pc ? My Perl already activated .. thx

and this is warning from console

C:\AppServ\www\mysqldumper\msd_cron>perl crondump.pl
Can't locate DBI.pm in @INC (@INC contains: C:/Perl/lib C:/Perl/site/lib .) at c
rondump.pl line 30.
BEGIN failed--compilation aborted at crondump.pl line 30.

please help me .. thx

[salewit]

Thanks so much for the tutorial! It worked semi-flawlessly for me. I get an error on the e-mail sending. Something tells me that when I changed the sendmail command to what was listed above, something went wrong. But it doesn't really matter as I use FullSync to FTP backup the site automatically anyway, and that will pick up the backups.

So involved just to do a little backup!