I would like to change Backup mySQL

Hi!

DrByte's Backup Mysql has to be one of the best, most necessary add-ons for any serious ZC. It has saved my tush many times!

But there is something missing that I have wanted for years: a simple gui for naming the files created.

Over the years of using it, I have grown accustomed to making a backup, then renaming the file via FTP. This is what it typically looks like:
01_addBackupMySQL.sql.gz
02_addTemplate.sql.gz
03_addImageHandler.sql.gz
04_addZenLightBox.sql.gz
…and so on.

But I am now building sites for clients, most of whom cannot easily accomplish this simple task. I instruct my clients to make backups before they do ANYTHING to the site. This results in gianormous lists of confusing backups to work with.

Adding an interface for naming the files as you make it would be ideal. But maybe building a gui is just too much work? A very simple fix would be to:
• Get rid of the DB name/user that is inserted at the beginning (unnecessary info)
• Make the date/time more user friendly

To make the date/time more friendly, it might be cool if the file name was something like this (red denotes what is same for every file created):
Date-YYYY-MM-DD__Time-HH-MM-SS.sql.gz

So a file I made today (Feb 8, 2013 at 1:15pm) would look like this:
Date-2013-02-08__Time-13-15-32.sql.gz

A series of files might look like this (usually matching the date in the center column):
Date-2013-01-28__Time-08-11-24.sql.gz
Date-2013-02-07__Time-15-55-03.sql.gz
Date-2013-02-08__Time-13-15-32.sql.gz

All of the code to scoop up and name a file is already in place. All that would need doing is reordering and adding the constants.

Can I get an 'Amen!' brothers and sisters? Anybody want to help do this?

Or at least point me to where the naming code lives so I can figure out?

Thanks,
Mike

BTW, another pet peeve I have is that the Admin Activity Archives are using the same folder. And because of how they are named, they show up at the top of the Backup mySQL list! Grrrr! 99.9% of users will never need/use these files. Why do they have to clutter up a list that WILL get lots of use?

I wish Admin Activity Archives used a different folder or were renamed to start with a 'z' so that they get sorted to the bottom of the list! Something as simple as this:
z_admin_activity_archive_2012-09-28_12-57-15.csv

But I won't touch that code because I understand the gravity of logging the activity… :)

Backup Mysql has to be one of the best, most necessary add-ons for any serious ZC
Absolutely...should be core (IMHO).

I agree with being able to name the files..I do the same. I've never had the time to do implement the functionality...and maybe I don't want to downgrade the code with my fudges!

torvista:

Absolutely...should be core (IMHO).

I agree with being able to name the files..I do the same. I've never had the time to do implement the functionality...and maybe I don't want to downgrade the code with my fudges!

Even if you can't name the files, at least cleaning up the current naming structure would go a long way to making life easier. I guess I don't mind terribly having to name files myself via ftp, I'm more concerned about the mess my clients will make.

I'm snooping thru code right now but have to leave in a minute, should be a very simple fix with minimal risk to damaging code.

Adding an interface, on the other hand, would require some effort. I'd be afraid to try it for the same reason.

I'm not sure I understand what the "issue" is. The files are already named with a prescribed naming convention. Is THAT what you don't like?
Or is it simply that the activity-log backups are also in that folder, and causing you some confusion?

Or is this an issue of your own making because you're constantly needing to restore from backup because you're doing dev work and breaking things frequently? .... and such problem will be rare in the normal course of operating a live site.

For my part there is no issue....I use automated backups and the naming doesn't matter.
However I back up manually before doing any db changes or imports and it would be nice to be able to name it on the page at the time rather than remotely.
Not really important for me, hence I have not bothered to change it....but seems important to help the monkeys the OP works for.

I restore from backup loads of times...repeatedly on my dev site, for example while trying to get mod sql scripts to work 100% in the SQL patch page.
I also move the db around between laptops depending where I have to work, and to have a current db to dry-run large imports on.

Its an essential mod for me, I wouldn't leave home without it.

Ditto to everything Torvista said, precisely as said.

What got me motivated was that a client added a Newsletter page the other day, something that gets coded by a wysiwyg editor on a ConstantContact type CRM site. Whatever they did caused the site to disappear and they freaked out. When I came to sort it out, apparently they had backed up a few times as they were adding other stuff, w/o noticing they had already killed the site.

So it took me a minute to sort it out. If there was a naming gui, would have been much easier.

DrByte, your mod is awesome and I love it. I realize the thought of making even minor changes is sacrilege, I really don't mean to offend!

Renaming the files (w/o adding a gui) is for the lest tech savvy (clients). When my client restored on their own, they restored the wrong files, thought they had lost a number of pages. I think they could have figured out the right file had they paid attention to the center column instead of looking at file names.

I have helped them to understand it better, don't think these guys will have a problem again. But they are not my only clients.

Adding a gui would be a convenience for all, but really not necessary for developer (though it would be nice). That would be best solution for monkeys, though. And it would be a convenience for me, as well

Ha! I'm not worried about sacrilege! But I'm flattered by the thought. Lol.

While I'm not opposed to the idea of a naming GUI, I am also mindful that the uninformed will similarly misname things and not have a clue which file to pick when they do a restore anyway. I'm not convinced it will completely eliminate the stated problem.

Adding a dialog to ask for a filename is doable ... just remember you'll need to sanitize inputs to be sure that no unacceptable characters are passed on to the actual backup process, else you'll get errors that there isn't currently any error-handling code for.

Renaming after-the-fact could be added easily with an ajax script. Still needs validation of acceptable characters. But happening after-the-fact could mean the file gets lost amidst a long list, and thus might be skipped by the newb.

Instead of changing the naming convention... how about allowing the end user to embed a "comment"? This could be embeded in the SQL file... This way both archived and current backups would include the "comment". Although parsing them all to display in admin would be a little extra work...

Not something I personally need, but just another way to save "Why did the last backup occur?"

DrByte,

I really hope you do get some satisfaction from the fact that we all look up to you as much as we do. For every one person you help directly by replying to questions in this forum, a dozen or more will stumble in and find the solutions you give. Everyone knows that solutions you give can pretty much be taken as the Word of God!

And the mods you have provided are spectacular. It must be neat to know that right at this very moment somewhere in Asia or S Dakota or the UK, there are people using things you created!

Backup MySQL, in particular, was a real stroke of genius!

That's why I'm embarrassed to even suggest that an improvement could be made. But I can't help it, I just think the way the files are named are bit messy.

This is what I did. I found the code for naming files in admin/backup_mysql.php (this line):

$backup_file = 'db_' . DB_DATABASE . '-' . ($tables_to_export != '' ? 'limited-' : '' ) . date('YmdHis') . '.sql';

And then I restructured it a bit:

$backup_file = '00-Date_' . date('Y') . '-' . date('m') . '-' . date('d') . '__Time-' . date('H') . '-' . date('i') . '-' . date('s')  . '.sql';

The resulting file names (see attached image):
• have the db info removed
• clearly state the date and time
• match (within a few seconds) the date/time given in center column
• appear above the Admin Activity Archives

DrByte, if you see any error in my restructuring, please let me know.

While I would greatly prefer a naming GUI, I don't trust my abilities enough to do it myself. If anybody wants to do it, I will be happy to beta!

Woah! Adding a comment would be great!!!

I was trying to avoid sql changes... but if you were going to the effort to allow file naming, adding a comment would be a very worthy use of time!

A comment in a g/zipped sql file my be difficult to extract automatically on a windows server, certainly my default xampp install cannot do gzip.
I think being able to optionally append some text to the default/improved date-time filename would be the most user/code friendly: it would still keep them in chronological order in the backup folder.

Is this good enough to clean an optional suffix?

        (isset($_POST['suffix']) && $_POST['suffix'] != '') ? $suffix = '_'.zen_db_prepare_input($_POST['suffix']) : $suffix = '';//sanitise suffix

        $bad_chars = array_merge(array_map('chr', range(0,31)), array("<", ">", ":", '"', "/", "\\", "|", "?", "*"));//array of illegal filename characters
        $suffix = str_replace($bad_chars, "", $suffix);//remove illegal filename characters
        $suffix = str_replace(" ", "_", $suffix);//replace spaces

I gave up on cleaning, transliteration etc.... what a world of pain.

I settled on this to add a suffix to the backup filename.

 case 'backupnow':        zen_set_time_limit(250);  // not sure if this is needed anymore?

if (isset($_POST['suffix']) && $_POST['suffix'] != '') {
$suffix = '_'.zen_db_prepare_input($_POST['suffix']);//sanitise input
$suffix = preg_replace('/\s+/', "_", $suffix, -1);//swap whitespace for an underscore
$suffix = preg_replace("/[^-a-zA-Z0-9_]+/", "", $suffix);//strip to simple ascii only
} else {
$suffix = '';
}
$backup_file = 'db_' . DB_DATABASE . '-' . ($tables_to_export != '' ? 'limited-' : '' ) . date('Y-m-d_H-i-s') . $suffix. '.sql';        

and this

$contents[] = array('text' => '<br />' . zen_draw_radio_field('download', 'yes', true) . ' ' . TEXT_INFO_DOWNLOAD_ONLY . '*<br /><span class="errorText">*' . TEXT_INFO_BEST_THROUGH_HTTPS . '</span>');      }
//steve bof add suffix to filename
$contents[] = array('text' => '<br />' . TEXT_ADD_SUFFIX . '<br />' . zen_draw_input_field('suffix', '', 'size="31" maxlength="30"'));
//eof
// display backup button

and this

define('TEXT_ADD_SUFFIX', 'Here you can add an optional suffix to the filename:');