Tag Archives: backup

Zip MySQL database backup with PHP and send as email attachment

I was a happy user of MySQLDumper until a while ago, when my shared hosting upgraded to the cloud architecture and latest PHP version; with that, some modules for PERL went missing and I was left without a viable backup solution (MySQLDumper PHP approach uses a javascript reloading routine which is superslow and is not compatible with wget nor remote cronjobs like SetCronJob.com).

So I found a couple of tutorials on the net (this, and this), mixed them together, especially improving David Walsh’s routine, blended them with some healthy bzip2 compression, and shipped to my webspace.

I stripped the part where you can select which tables to backup; I figured that any backup deserving this name should be “all-inclusive”; also I think that anyone with the special need to backup only selective tables should also be smart enough to change the code to suit his/her needs.

UPDATE 9/9/11: I just added a snippet of code to blacklist some tables from the backup, since I needed to trim the size or it gave a fatal error about the available memory being full; just fill the array with rows as you see fit

This is the result:

<?php
$creationstart=strtok(microtime()," ")+strtok(" ");

$dbhost="your.sql.host";
$dbname="yourdbname";
$dbuser="yourmysqlusername";
$dbpass="yourmysqlpassword";

$mailto="mail.me@mymailbox.com";
$subject="Backup DB";
$from_name="Your trustworthy website";
$from_mail="noreply@yourwebsite.com";

mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);

$tablesblocklist=array(
    "tablename1"=>1,
    "tablename2"=>1,
    "tablename3"=>1,
);
$tables = array();
$result = mysql_query("SHOW TABLES");
while($row = mysql_fetch_row($result))
$tables[] = $row[0];
foreach($tables as $table) {
if (!isset($tablesblocklist[$table])) {
$result = mysql_query("SELECT * FROM $table");
$return.= "DROP TABLE IF EXISTS $table;";
$row2 = mysql_fetch_row(mysql_query("SHOW CREATE TABLE $table"));
$return.= "\n\n".$row2[1].";\n\n";
while($row = mysql_fetch_row($result)) {
$return.= "INSERT INTO $table VALUES(";
$fields=array();
foreach ($row as $field)
$fields[]="'".mysql_real_escape_string($field)."'";
$return.= implode(",",$fields).");\n";
}
$return.="\n\n\n";
}
}
$filename='db-backup-'.date("Y-m-d H.m.i").'.sql.bz2';

$content=chunk_split(base64_encode(bzcompress($return,9)));
$uid=md5(uniqid(time()));
$header=
"From: ".$from_name." <".$from_mail.">\r\n".
"Reply-To: ".$replyto."\r\n".
"MIME-Version: 1.0\r\n".
"Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n".
"This is a multi-part message in MIME format.\r\n".
"--".$uid."\r\n".
"Content-type:text/plain; charset=iso-8859-1\r\n".
"Content-Transfer-Encoding: 7bit\r\n\r\n".
$message."\r\n\r\n".
"--".$uid."\r\n".
"Content-Type: application/octet-stream; name=\"".$filename."\"\r\n".
"Content-Transfer-Encoding: base64\r\n".
"Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n".
$content."\r\n\r\n".
"--".$uid."--";
mail($mailto,$subject,"",$header);

$creationend=strtok(microtime()," ")+strtok(" ");
$creationtime=number_format($creationend-$creationstart,4);
echo "Database backup compressed to bz2 and sent by email in $creationtime seconds";
?>

(WordPress killed the indenting and I’ve no intention of fixing it manually)

The whole routine runs in roughly 7seconds on my not-so-bright hoster server, including bzip2’ing and mailing, while the original text-only output for David’s code took on the same server roughly 17seconds; probably it’s due to the removal of several redundant loops, and using the builtin mysql_real_escape_string() and implode() functions instead of David’s workarounds.

My sincere thanks go to the authors of the two guides, without whom I wouldn’t be writing this today 😉

Backup installed packages list in Ubuntu and restore via Synaptic

To backup your Ubuntu install you don’t just need to keep a copy of the /home folder (or partition), since you would still need to re-fetch all the packages you installed, and that can be time consuming, especially if you carefully chose the applications to add to the system.

Synaptic already offers a similar function, which is File > Save Markings As… (be sure to fill the check box “Save full state, not only changes”, otherwise you will be probably getting a 0byte file). You can then use the File > Read Markings function to restore the package list on another system/install.

What’s the deal with this? The function actually saves indiscriminately a list of all the installed packages, including those that were installed just because they were a dependence. For example if you sudo apt-get/aptitude install packagename you will probably install also packagename-data and packagename-core or something along those lines, as they are dependencies of packagename, but the dependencies may be more complex and deeper (for example, packagename-core may also require other packages in turn); dependencies can change over time, so if package A requires package B today, a month from now that may be not true anymore; so if you passively restore the whole packagelist, you would be installing package B even if that’s not needed anymore.

The solution is to save a list of only the “main” packages, which will in turn require the correct dependencies; this can be achieved with:

aptitude search "~i ?not(~M)" -F "%p install" > packagelist.txt

This saves into packagelist.txt the list of the installed packages (~i) that were not installed automatically (not(~M)), mantaining the same format of the list generated by Synaptic, that is “packagename install” in each row, so you can seamlessly import it from Synpatic.

How to use ImgBurn for batch build/create/burn ISO to backup to DVD

This guide is based on ImgBurn 2.4.4.0, which you can currently get from here. In case you’re reading this guide way after the publication date (may’09), then it could be possible the author added the functionality directly in the software, rendering this howto pretty much useless

Anyway, the whole point of this guide is doing with ImgBurn what it is not really intended to do, that is using it as a backup software to reverse on optical discs your sheer volume of un-copyrighted data. The guide is suited for users wanting to backup to single-side DVD’s.

As you may, or may not, know, ImgBurn has a built-in batch function which allows you to burn in batch mode, that is, when properly set, being able to queue as much ISO images as you need, and after pressing a button, only needing to exchange the automatically ejected burned DVDs with blank ones and close the tray between an ISO and the following one, meanwhile doing whatever you may like (surfing the internet, chatting, watching a movie, or even play a videogame) without much stress on your part unless these very automated steps.

What ImgBurn absolutely needs for the batch burn function tho, is ISO images, while normal users in need to backup files usually have those files in a normal directory structure; thus, we need to convert those directory structures in ISO files to be burnt by ImgBurn, but alas ImgBurn has no batch function to build ISO files, just a plain manual routine. Our job in this guide is to make a wholly batched process both to burn ISO files *and* create them beforehand from a predetermined folder structure.

 

The first part of the job is the heavy one, depending on the volume of data you need to backup: you need to do this mostly manually, unless you want to use other tools to help you with the space partitioning (like Ignition by KC Softwares, but I won’t cover this in the present guide). To make it short, you need to create a work folder, in which you will then create as many folders as the DVDs will be. For the example’s sake (all drive letters and patch are taken from my real paths), let’s say you got a big drive with lots of free space, let’s make it F:, then lets say you want your work folder to be F:\Burn; you will proceed to decide what data to backup, and then you will have to partition that data in 4.3GB chunks (roughly the writeable size of a commercial DVD recordable, be it either DVD-r or DVD+r), this partitioned data will be copied/moved inside subfolders of F:\Burn, to which subfolders you will try to give descriptive names, which will be needed later on. But enough for the babbling, here’s an explicative image:

work folders
Some fictitious folders containing our data to be burnt; each folder must not contain more than 4.3GB each, or it won’t be possiblt to burn it on a single DVD recordable, there is no limit though to the subfolders of each folder, each DVD can contain the directory structure you prefer. Notice the buildiso.bat file, we will be needing it later on.

 

This kind of work can be long, espoecially if you have lots of material to backup, as you need to find the best way to fit everything on 4.3GB disks, but after this, all the job will be mostly done by your PC with very less intervention on your part.

Here it comes the part where you need to setup ImgBurn properly to adjust to the job; so open the application and go into Tools > Settings > Build pane

imgburn build options page 1
imgburn build options page 2
This is how I set the Build options for ImgBurn, unexperienced users may want to have their panels looking exactly like these to reproduce the results, what I do suggest you for better results is ticking the "Don’t Prompt DivX Video Settings" option, or you risk to get interruptions by ImgBurn asking stuff during the process instead of going by itself.

 

Then go to the Write pane:

imgburn write options
For the batch ISO write function of ImgBurn to work smooth you should set the options as reported here.

 

You have pretty much done your preparation job in ImgBurn, we now come to the buildiso.bat file you noticed before. Simply create said file containing the command: (be sure to select/copy the whole line, the text is most probably scrolled horizontally)

@for /d %%i in (*.*) do "e:\program files\tools\imgburn\imgburn.exe" /mode build /buildmode imagefile /src "%%i\" /dest "F:\Burn\%%i.iso" /FILESYSTEM "UDF" /UDFREVISION "2.01" /VOLUMELABEL "%%i" /rootfolder yes /noimagedetails /start /close

You simply need to replace the paths in the command with the proper paths, where first path (e:\program files…) is the full path to the imgburn.exe file, and the second one (F:\Burn\) is the path to the work folder you created. What this file does, is setting up ImgBurn to go through every folder in your work folder and create an ISO image file from it, respecting its subfolder tree, and using the folder name for the ISO file name, and the ISO image label (that’s why you’d better choose descriptive folder names). Some notes on the command: here I use a one-way UDF filesystem, revision 2.01 (not the latest one), why? Because it suits my needs, as I just make backup disks, so I don’t create DVD-Video disks (UDF-only is not good for that), plus if I want to write a file larger than 2GB on disk, this way I can, without the limitation of the other filesystems; I also chose not the latest revision of UDF, as I took my time to read the relative page on Wikipedia, and checked that revision 2.01 is what pretty much gives the best functions together with the largest drive compatibility. Note: the ISO files created this way won’t be correctly opened by 7zip (to cite one program), at least on my pc, but they will be correctly mounted using DaemonTools, so you can still extract files from them.

At this point, you need to batch build the ISO files with the bat file, so copy the buildiso.bat in the root of the directory containing the data folders and start it, you will notice ImgBurn will appear and will begin creating the first ISO, after which it will autoclose and proceed to the next one. Notice that you don’t really need to have data folders and bat file in the same work directory you chose to contain the ISO files. Given how the batch file works, you can have those folders (always together with the bat file) anywhere else, for example on an external USB drive, or even network folder (even if it’s not recommended for transfer-speed reasons), in the end the ISO files will be created anyway in the work directory you chose (F:\Burn in this example): this way you don’t need to copy the folders over to another drive, in case you have them already stored elsewhere, just be sure each folder contains only up to 4.3GB of stuff.

At the very end of this process you will have a bunch of big .iso files in the work directory, together with corresponding .mds files (for easiness of the procedure you can use Sort files by type in Windows Explorer and delete all the .mds files, I did in my case and it all worked perfectly), and those are the files you need to batch write using ImbBurn; at this point you can choose to delete the source folders in case you don’t plan to use the data afterwards, since you got the ISO files coming from them. So, open ImgBurn and go to Write mode or even choose Write image file to disk from the Ez-Mode picker menu, and press the button with the folder symbol overlapped by a plus sign, in the source section on the left of Please select a file…, and a menu to queue up ISO files to be burned will appear; here you only need to drag’n’drop the ISO files from the Windows Explorer window to the white space in the menu:

imgburn write queue dialog
Something like this will appear after you drag and drop the .iso files to the write queue. Let’s take a look at the downmost checkbox named "Delete the image when done"; it is a useful ufnction if you want to backup the data without keeping a copy on the hard disk. Just select from the list of ISO files the ones you do not want to keep on the hard disk, and then activate this checkbox: in my case I wanted to delete all ISO files after writing, so I selected them all and checked the box. The "start writing" button in the image is grayed out, since I had no blank DVD inserted at the time (it was just a demo for this guide after all), but when you do insert a blank DVD the button will be selectable.

 

When the queueing is done, just press the Write button in the queue window and the backup to DVD will start; all you will need to do at this point is mind your very own business until the tray gets ejected, and which point you take out the warm just burned DVD and put a new blank one inside, closing the tray; ImgBurn will start writing the next ISO file as soon as it detects the blank disk has been inserted, until all the ISO files have been burned. Enjoy.

Batch script to copy your backups from cd/dvd optical disks to a hard disk/nas

4/14/2010. I recently decided to drop the habit of backing up all data to optical disks and use a 2 terabyte external hard disk instead. Not exactly cheaper, but what you lose in money (just once) you gain tenfolds in speed, comfort and ease of use, and physical space needed for storage. So I got a 2TB hard drive and fitted it in a USB external box.
Backing up all the new stuff there is no big deal, but it surely was to move the contents of the hundreds of “old” CD/DVD’s in there; doing it manually by dragging files onto the drive was going to be the most cumbersome thing, so I thought that if I had a utility that would let me put a disk inside the cd/dvd drive, and did it all by itself, that would be great. Also this utility had to eject the disk when done, so all I had to do was change it with the next disk in the serie, and after reinserting the tray the program would go on copying files.
In other words, all I wanted to do was to take out the just copied CD/DVD and put inside the next one without even touching a file manager but keep doing whatever else it was that I was doing (writing this howto for example, while every few minutes I reach to the disks stockpile and exchange the disk in the drive). Obvously, abiding by Murphy’s law, there was no such thing, or at least a hour worth of search didn’t bring up anything. Either I went full-manual or I made something like it myself: the time I was going to spend on the development would surely have been way less than the time I would have needed to select files, drag them to the drive, then wait for it to finish to change disks.

My choice fell onto AutoHotKey as the scripting tool (I already knew a little of it, plus it had just the functions I needed), so I invested some more than an hour into studying the language and building up from scratch the code to do exactly what I needed.
And in short, here it is (do not copy it yet, you need to read explanation below):

while 1
{
	empty = 1

	while empty
	{
		DriveGet, status, StatusCD, E:

		if (status = "stopped" OR status = "not ready")
			empty = 0
		
		Sleep, 500
	}


	SetWorkingDir, E:\

	Loop, *.*, 2, 1
		FileCreateDir, M:\%A_LoopFileFullPath%
	
	Loop, *.*, , 1
	{
		if (A_LoopFileName != "filename1.ext"
		&& A_LoopFileName != "filename2.ext"
		&& A_LoopFileName != "filename3.ext")
		{
			docopy = 0
			IfExist, M:\%A_LoopFileFullPath%
			{
				MsgBox, 4, Overwrite file?, Overwrite file %A_LoopFileFullPath%?
				IfMsgBox, Yes
					docopy = 1
			}
			else
				docopy = 1
			if docopy = 1
				FileCopy, %A_LoopFileLongPath%, M:\%A_LoopFileFullPath%, 1
			if ErrorLevel
				MsgBox, Could not copy "%A_LoopFileLongPath%" to "M:\%A_LoopFileFullPath%" with ErrorLevel = %ErrorLevel%.
			ErrorLevel = 0
		}
	}

	Drive, Eject, E:
	
	SetWorkingDir, C:\
}
	

As I said before, do not put it in action yet, you’re very likely to need to edit it to your needs.
Replace EVERY occurence of “E:” with the correct drive letter of the cd/dvd drive you’re going to use.
Replace EVERY occurence of “M:” with the correct drive letter of the hard drive you want to copy the files to.
The part with the “filenameX.ext” is for my convenience, and probably yours as well: I have some files on most optical disks that do not need to be copied over to the hard disk, mostly being a catalog file, or maybe the exe of the player for the videos, or whatever. So in place of “filenameX.ext” you need to put the name of those files (if any) that you want the program to ignore (if you need more of them, just duplicate the central line as needed, if you need less, or none at all, either edit that part to your needs, or if you’re not good at it, you can probably leave it as it is, since I do not think there are any files in your collection that are actually named “filenameX.ext”). The “SetWorkingDir, C:\” command is just a temporary buffer for when the disk is ejected, you can leave it as long as you have a C: drive!

Save the code in text format with .ahk extension, install AutoHotKey, and double-click on the .ahk file you just created; if I receive enough requests, I’ll upload an exe version of the utility so you can skip installing autohotkey. This is how the script works: when you launch it, unless it detects the presence of a disk inside the CDrom/DVDrom drive, it does nothing; as soon as you put a cd inside, it proceeds to copy all the files that have names different from the ignored ones, to the chosen destination, respecting the folder structure. If there are any files that already exist on the destination drive, it asks you if you want to overwrite them; there’s a little problem here, I have set the filecopy to do an overwrite, but it won’t work, if I try and overwrite it will just return an error. It’s not a big deal for me as I can just press OK and the program will go on by itself without copying the file, but if you really need to do an overwrite (for example a newer version of the same file) first delete the existing file, then press OK and it should work. As soon as the whole optical disk has been processed, the script will eject the tray of your drive, and pause until it detects again a disk inside, so you have all the time you want to switch disks. As soon as you put a new disk and close the tray, the program will continue the file copy process, in a batch fashion. To close it right click in the green “H” icon that appears in the system tray and press Exit.

I tested it on my pc alone, so I won’t be giving support if it doesn’t work for you… no time for that! I just thought it would have been nice to share something like it in case someone else needed to do the same thing.
On a side note, already a handful of disks failed on me, probably we’re talking about 4%; I didn’t reach yet the point where I switched to DVDs; as far as statistics are concerned, first place for unreadable cd’s are Verbatim Datalife (not plus), second (this is a surprise) TDK Reflex, and last, Traxdata tx; still surprisingly, a green die unknown brand of CDr’s, “CDV”, the very first ones I bought in my life, hence the oldest and the ones burned the longest time ago (about 9 years), were all perfectly readable; perfect results, at least until now, and with top reading speeds, are TDK metalAZO with white printable surface, and Verbatim DataLifePlus with silver surface, both of them (guess what) with blue die; special mention goes also to the TDK d-view’s, and the Verbatim Pastel-Disc, no failures there as well.