[help wanted]:MySQL backup system

[nova]

I would join Laugesen in his request for a backup/restore function. It would be nice, not to have to use the phpMyAdmin part. In my oppinion the function should create a single file you could download (or ftp) to your HD and/or your CD-writer.

If your database goes down the drain, there should be a restore function. I know that there can be some glitches if you have a working database with dublet entries, but the starting point should be a "clean" mySQL database, so you don't have to think about that.

It sounds simple, but I'm not a php expert, so I turn to this forum. :-)

Yours sincerly

Rasmus

[Joachim Müller]

let's make some kind of "quest" out of this: phpMyAdmin is open source released under GNU GPL as well as Coppermine; I have seen other free software using the phpMyAdmin function inside their software (for example phpBB or some typo3 backup modules).
It'd be a nice opportunity for some coder who'd like to contribute to coppermine to code this into a standalone piece of code (one file), taking into account to Coppermine structure, build some front-end bits and voila - we have a backup routine.
Who'd like to try and take the "quest" (and get all the "fame"; sorry, no "money" or "girls" come with this :wink: )?

GauGau

[Laugesen]

My idea was not to use a whole PHPMySQL system, I know that, but a simple way to save all coppermine related MySQL data in a file, stored local on pc.

And in the Install process, a function like a choice to use the xx.sql files in the reinstall

Hopefully to make it simple, not complicated like phpMYSQL is for "noobs"

[Joachim Müller]

yeah, I know that - the coder has to use some of phpMyAdmin functions, but his mod has to do two things (or buttons):
- backup the complete Coppermine gallery mySQL data
- restore it from a previous backup

To get an idea, check phpBB documentation: backup

GauGau

[azrael]

Might be a bit outside the meaning of the thread but just adding my
2 cents of worth into the discussion.
Isnt system backup something which should be outside the application?
Although it is a nice thing to integrate, it is sometimes the best thing to
keep an application for what it is and not try to integrate every know
feature known to man into it, since result often will be a bloated code and
a slow application.

Personally i wrote shell scripts for backing up the 30 or so mysql bases
i got running that i put into the cron.daily folder.
Basics is that i run mysqldump on the bases, take the output, tars and
gunzips it and gives it a filenane with todays name in and puts it in a
backupdir, runs daily and seamless in the background each night.
If you do regular backups of your server to tape, files will then be
included for offsite backup too.

If theres any interest to it i can post the scripts here.

[Tarique Sani]

I have been thinking about the bloat in CPG code... The first fact is that it is inevitable BUT the question is do we need to encumber everyone with the entire code?

I propose a system of modules / plugins - the API of which should be such that if the module files are placed within a directory of their own within the modules folder the Config / admin menu on top should be able to link to it directly and automagically.

I guess the back-up can be one such utility. The themes already work on this principal - BUT - that is being simplistic.

[Laugesen]

As a plug-in it would be great, then the users got a choice to DL it and use it or not. :wink:

[jasendorf]

Isnt system backup something which should be outside the application?

In a utopia where everyone owned their own server and had a shell account, yes... I suppose so.  But, remember, many people are stuck with far less access than that.

[azrael]

Ok, guess i am a bit spoiled accesswise having root access to the
production servers.
Although, the module idea that was proposed sounds like a rather good
idea, since everyone stands a bit freer to install what parts of the
application they can use. I can have my stripped down core version of
coppermine, small and optimized for speed due to heavy load, other
people get all the nice features they want.

Customizing would be easier in a set standard, and installation of named
plugins would hopefully go easier.
Hope you will follow this path.

--
Az.

[nova]

A plugin you could download and use (just like the templates and/or the languages) sounds like a really good solution. In that way you don't have to make the 'core' of Coppermine bigger or more complicated.

Yours sincerly

Rasmus

[Joachim Müller]

I agree that we don't have to add each and every feature one could think of into the core - the module idea seems to be tempting. But I've seen other projects trying this and fail...
An application like coppermine is mainly for those who actually can't code (non-geeks), because that's what they can do: put pics on their site to have some content after all.
Especially a backup of coppermine could be done in a separate file (only one more link in the admin menu) with just the look of coppermine - no big deal... Call it a module or whatever.
There are other parts of coppermine I'd like to drop (making them add-ons that can be downloaded and added separately): there are already to many themes  in the package, and way to many languages to maintain.

GauGau

[Tarique Sani]

The idea of module is also tempting for me because as I see it the lang arrays associated with that module will be in that module directory and not clutter the lang files of the core application also an API will be provided so that the module can default to english if the selected lang is not available

Right now I envisage just two category of modules
1) Those which integrate with the main admin (top menu) like Resize and Backup
2) Those which integrate with the image editing like edit description, crop and rotate image, add multimedia etc (will appear as drop-down below image in admin mode)

We can also have guidelines for designing modules and self imposed rating of difficulty of integration, though ideally a module should never require to edit a core CPG file

[Oxy]

I just have a question for those who are hosted with few rights or others who know the situation.
Do you have right to remotely execute sqldump ?
I mean, do most host allow that or not ?

Because even without getting a shell access maybe the function is available. I activated a free account on a public host but I could only try later they need 24h to activate my DB access (cron task ) so maybe some of you have the answer right now and it is always good to have several feedback.


ps: Is someone working on that ?

[Joachim Müller]

I doubt that there's a workaround if you don't have shell access.

GauGau

[Oxy]

If you do not have shell access this is not preventing you from executing the imagemagick converter tool for example so if servers propose access to sqldump why not : exec "/usr/bin/sqldump" .. ?

[jason]

backup (mysqldump)

Code Select Expand

#!/usr/bin/perl
open(STDERR, ">&STDOUT");
$|=1;
print "Content-type: text/html\n\n";
print "<pre>\n";
$t = `date`;
print "$t\n\n";
system("mysqldump -u USERNAME -pPASSWORD --opt DATABSE_NAME > /path/to/database.sql");
$t = `date`;
print "\n\n$t";
print "</pre>";

restore (mysql)

Code Select Expand

#!/usr/bin/perl
open(STDERR, ">&STDOUT");
$|=1;
print "Content-type: text/html\n\n";
print "<pre>\n";
$t = `date`;
print "$t\n\n";
system("mysql -u USERNAME -pPASSWORD DATABASE_NAME < /path/to/database.sql");
$t = `date`;
print "\n\n$t";
print "</pre>";

place perl scripts inside your cgi-bin directory and call them from your web browser, no need to split or have shell access.  delete perl scripts and database file from your site when you are done.

you could take this further by compressing the file and setup a date archival format that could be utilized from cron jobs.

mysql backup restore split chunks large database cron

[daniel.duarte]

If theres any interest to it i can post the scripts here.

Hello,

I've been using phpMyAdmin to do regular backups of the db to a file in the coppermine directory, and then I simply do regular backups of all the directory.

However I wanted to automate this process, and the idea of a daily cron job to backup is very tempting.

Could you please post the scripts, and some insctrutions? And restoring the backup, can be done with phpmyadmin?

Thanks!

[jason]

look up.

other than adding in a simple recursive copy command for your cpg files, what else are you looking for?

[Joachim Müller]

those in the know don't have need for a built-in backup....
Coppermine is being used by a lot of beginners who simply don't know how to use such a command. What we're looking for is an integration into the user interface of coppermine (similar to the backup function of phpBB) that has two buttons: "backup coppermine database" and "restore coppermine database" with some bells and whirrs starting when someone is about to restore the database (alerting him that all db content will be replaced with the stuff from the backup).

GauGau

[jason]

gaugau, the problem with integrating a backup/restore from a php page is it get's restricted by the php upload limitations.  while this only affects restores, it still hinders a "cute and simple" solution that other php based projects (like phpBB) have attempted (without splitting into chunks, who wants to do that!)

i supposed if the php backup/restore page created a local file on the webserver instead of downloading through the browser and then allowing the user to pick and choose which backup to restore from list of backups made....then it would work fine.

i *think* i can set this up.