Icy Phoenix

Hail all, I don't know how many of you share my frustrations of having myphpadmin corrupting your back-ups and occasionally missing out loads of data. The only way is to set "Maximal length of created query" to some ridiculous number to get most of the data, but back-up posts_text and other large tables separately. Well... not any more...


## This easy-to-use-utility will store your entire database to a raw text file located in your cache directory entirely through php.
## If your host allows you to execute system commands through exec(), then this backup file will also be compressed. The
## advantage of this is that it almost completely avoids corruption of a large backup, as what usually happens with most
## browser-based back-up routines (like in phpmyadmin), which try to send all the data through your browser. By storing the
## file on the server, this avoids the database - browser streaming error, which even occurs with compressed back-ups (since
## it all occurs on the fly). Once back-up files are created you then use the more secure method of transferring a large file
## through ftp - even if the back-up is a raw file, it is very much less likely to contain errors transferred through ftp than a
## back-up file compressed on-the-fly through the browser!
##
## There is just one file: eezydump.php
## Place it in your root directory and away you go. You have to be logged in as admin to run it!
##
## Compatibility: Basically anything that is phpbb based (may need to create a cache directory with chmod 777). I've written
## it to back up a couple of phpBBXS and icyphoenix based sites - it reads the config.php file in order to access the database.
## Rather than using a pre-defined database schema, it scans the database for tables, so it doesn't matter if you've added mods -
## it will find all the tables and fields and back them all up.
##
## Version: 0.0.1 - Alpha release - 20/03/08
##




Important note - cache directory & htaccess
If you are using the latest version of icy, you will need to modify the htaccess file, since this currently block any access to the file (so once the backup is created, you can't download / access it). Since the cache directory is effectively protected by the index file, and the htaccess file in the root, you can simply delete the cache .htaccess file. However, if you want peice of mind, you could change it from:


to

<Files *>
order deny,allow
deny from all
allow from xxx.xxx.xxx.xxx

</Files>

Where xxx.xxx.xxx.xxx is your IP address

*Edit* Blocking all and allowing your own IP still will not allow you to download the file.

Much better to do this:

remove everything in the htaccess file and replace with

It prevents directory listings, so still keeps your data safe.


Demos: The next Version of this script can be seen in action Here

Usage: Just a single click: "Export" and your entire database will be exported to a file. You do have some more control - you can deselect the default options like Drop table statement, Create table statement and Table data. After backup is created, you will be told of its time-stamped filename as shown here:



If you are on a server where the php system exec() command is enabled, you will also get a zipped file:



Some of you will no doubt have much much much bigger databases than me (7 MB), so I'll be keen to know if this copes well.

Ideas for a possible next version (maybe / maybe not implemented - script already does what I want it to do):

1. Either displaying file names as a link so you can click on them and download them via your browser, or refreshing browser and getting browser to automatically bring up the save prompt. This may seem strange after harping on about how much better it is to do via ftp - but since the data exists in a file of defined size, rather than a stream of outputs from sql, the data should still be of high integrity.
2. Adding a random alpha-numeric string to the filename to take it from being damn-near impossible for an outsider to guess the filename, to more impossible than an Islamic pope.


Final point - even though it is highly unlikely that anybody will be able to guess the exact time and date, and even the name of your database, in order to guess the back-up filename, it is better to be safe than sorry and delete your backups in the cache directory after transferring/downloading them.

At the end of the dump process, my web browser went white...no error messages.

I then checked my cache folder and the backup was there. :)

This is a great mod but I use the phpBB Backup Suite because it backs up both database and files. I do like that you incorporated the website template into the mod! Great job moreteavicar!

Chaotic wrote: [View Post]

At the end of the dump process, my web browser went white...no error messages.

I then checked my cache folder and the backup was there. :)

This is a great mod but I use the phpBB Backup Suite because it backs up both database and files. I do like that you incorporated the website template into the mod! Great job moreteavicar!

Thanks Chaotic - actually it wasn't hard to incorporate the website template - I just stuck to the convention in phpbb, phpBBXS and icy for the table and td class (all themes should have class = forumline & td lass row1), so that no matter the theme, it should come out like the rest of the site... ;)

I hadn't really thought about using the phpBB Backup Suite because I wanted just a simple, single script that could be uploaded and ran in a matter of seconds, backing up just the database. However it would be interesting to have a closer look at the Backup Suite to see how it compresses the backups, especially if it does it without use of the system exec(tar filename)- I know there are a number of php scripts out there with a zip-derived compression algorithm rather than relying on the server - something else I had thought about adding if I get time.

I'm not sure why your browser would go blank, the only thing I can think of is that maybe you had a very big database, so that by the time the back-up file was generated, your session had timed out! How big was the file / how long to generate?

Ah no... just downloaded the back-up suite... this too depends on running system commands to compress:

moreteavicar wrote: [View Post]

Chaotic wrote: [View Post]

At the end of the dump process, my web browser went white...no error messages.

I then checked my cache folder and the backup was there. :)

This is a great mod but I use the phpBB Backup Suite because it backs up both database and files. I do like that you incorporated the website template into the mod! Great job moreteavicar!

Thanks Chaotic - actually it wasn't hard to incorporate the website template - I just stuck to the convention in phpbb, phpBBXS and icy for the table and td class (all themes should have class = forumline & td lass row1), so that no matter the theme, it should come out like the rest of the site... ;)

I hadn't really thought about using the phpBB Backup Suite because I wanted just a simple, single script that could be uploaded and ran in a matter of seconds, backing up just the database. However it would be interesting to have a closer look at the Backup Suite to see how it compresses the backups, especially if it does it without use of the system exec(tar filename)- I know there are a number of php scripts out there with a zip-derived compression algorithm rather than relying on the server - something else I had thought about adding if I get time.

I'm not sure why your browser would go blank, the only thing I can think of is that maybe you had a very big database, so that by the time the back-up file was generated, your session had timed out! How big was the file / how long to generate?

Hi moreteavicar,

My database size is only 15.44MB. I removed the backup already so I'm not sure how big it was.

You have a great mod here! If you can make it backup files, I'll quit using phpBB Backup Suite in no time!

Chaotic wrote: [View Post]

At the end of the dump process, my web browser went white...no error messages.

Hi, I've got a suspicion the cause of the problem here might have something to do with the maximum file size limit on your server [I say might!]

I've tried this routine on a number of servers now, and only one has given me problems, similar to you what describe. It occurs on a freehost account I have, which has a limit on the size of files you can have in your account. If I try to do a full backup, the server halts the script, so no matter how verbose I try to make the script, I won't see the errors, just a blank page, because of the halt. I then see a large file in the cache directory, but since it exceeds the account file size quota, all access to it is forbidden! (except I can delete it). It is ok generating a backup without the table data, since this results in a compact file, and the resulting file can be accessed without a problem.

I think the only way around this issue is to then provide the option to parse through browser (actually, if you inspect the eezydump.php file, near the top you will see the line
- simply set this to FALSE to get it through the browser. This might sound like going back on the intention of having something which doesn't try and generate and stream on the fly - I've tested it with 6MB databases which phpmyadmin on a couple of my servers seems to have problems with, and so far no problems this way. I don't know how it will work with a 15MB file, but worth trying ;)

BTW the files generated by my routine is almost half that of phpmyadmin, because the latter uses "INSERT into table..." lines for every row of data, whereas I've set it up to use just a single INSERT statment for all rows within the same table. Of course, much of it depends on exactly how much data there is, so if you've got a lot of post whores, you probably won't notice much difference ;)
Anyway, I have updated this a little bit, so that you can choose between my "minimum" inserts and phpmyadmin style queries. I might also add a front-page option to allow streaming of the file through the browser... stay tuned ;)

BTW The newer version of this script which actually allows you to download the files from a link, rather than having to set up ftp.

You actually can see a demo of the next version of this backup utility here

There you can also see a demo of the corresponding EezyDumpInstaller which makes installing back-up files painless. It automatically detects a number of things that are useful if you are transferring a site to another server. For e.g. you might be setting up the database in the same way, with the same name. If you have a working config.php file in the directory, it will use that (unless the host puts different prefixes to database names) - useful still if just duplicating a set-up on the same host. It also auto-detects your new site script path and updates the board config accordingly (otherwise when you try and login, you will be redirected to the old site). If you don't have config (or it doesn't work) you just enter the new database details - these will be written to the config.php, along with the all iportant "$table_prefix" variable which it finds automatically from your back-up.

Release of these isn't far off, I'm still playing around with them both, but now you can preview them in action!

If you it would be possible to integrate it (an the restore feature as well) in ACP I think it would be great.

I will try this script as well. :mricy:

Thanks for creating and sharing. :wink:

Thanks MG, actually I have re-written this script to greatly improve it - it now has its own zip compression algorithm built in (within the same file), it no longer depends on using the exec() call to try and compress server site - this is quite important for me as my host disabled it :(.

Yes I have also problems with the built in backup facility in icy, as well as phpmyadmin... so this little project was born out of personal necessity... and frustration with my host!

For ACP integration - I guess this can just be a simple link - I also want to make it "portable" - it can in principle be used on any phpbb system. Thankfully you've kept the basic same session and admin variables... :mricy:

Check the Demo Link to see the latest version of the program in action - I'll release it here in the next day or so... (I have one more feature to add, and that is a multiple backupfile generator, so that if you specify a max file size, the raw files will not exceed that size (necessary due to a number of hosts that introduce limited file sizes - one of my freehost test accounts seems to have set the limit to 512kb! Icy had to be uploaded uncompressed!).

Also renaming to easyDump... ;)

Just for personal curiosity... did you try to compare the NEW backup feature in latest IP with this script?

Is there something that may be ported to standard IP Backup feature?

Mighty Gorgon wrote: [View Post]

Just for personal curiosity... did you try to compare the NEW backup feature in latest IP with this script?

Is there something that may be ported to standard IP Backup feature?

Hile, yes I've recently tested the latest backup feature in Icy and this works nicely (provided one remembers to chmod 777 the backup folder, as my host has disabled the php chmod function!) - previous versions didn't work properly, certainly not in XS, and from what I've tried in IP 15, and as I have a few different sites in existence of various pedigree of phbb, XS and icy, I created this script - it can be used anywhere. It's certainly not to replace the latest one in Icy though, which checking the code, doesn't look much different in principle to what I've done - what I mean here is the use of mysql queries to get the dB contents e.g. to generate the table structure (show create) etc.

One major difference though is the use of the db class in the Icy backup - I tried it (db class) initially, but found that it was better to avoid this class and use standard php mysql functions - the latter made it quite a bit quicker (though I didn't spend much time getting statistics for exact speed difference, so I'm being a bit unscientific here! Saying that, when you examine the db class library there are a few more things going on, namely the microtime statements everywhere... to get the time it takes to execute every query, and I've yet to see anything in phpbb which actually makes use of the sql_time object!)... Also some mysql functions are missing from the phpbb db class library, so you have to have a mix anyway (one sees this in the Icy backup script). Thus the EasyDump script is about as optimised as you can get, only performing the minimum necessary statements to extract data from mysql... Also doing it the way I did, db class independent, makes it a lot more portable - if I dropped the code for the page header and page tail (and phpbb session) and added a couple of lines for dB username and password (like in the instraller), then it would backup any mysql database, not just phpbb - e.g. Drupal. The reason for having the phpbb sessions management was purely a security feature to prevent random people accessing it and obtaining a copy of your db. That though could be overcome by creating its own session management, through its own db admin username and password table (and perhaps not even stored in dB, but in a file similar to config.php). All in all I just wanted to make backup as easy as possible.

Latest version, which I'll upload here soon, now has its own internal class to produce zip files as well as gz (actually this could perhaps be added to the icy dB backup script), and also the ability to generate multiple part files for servers with limited file size - easydump installer auto detects those part files and installs them (in order). Installer also now can be switched between the detected default theme and the new (internal) easy orange them ;)

These new features can be seen in the demos - I'll release them publicly soon (maybe later today)!

Ciao!

moreteavicar wrote: [View Post]

and I've yet to see anything in phpbb which actually makes use of the sql_time object!)...

And there a good reason for that... phpbb doesn't even have sql_time in its db class... its purely a phpBBXS and Icy thing :oops:

Whit a 115 MB of DB, I have had a max execution time error:

Fatal error: Maximum execution time of 360 seconds exceeded in eezydump.php on line 253

The same problem that I have with integrated backup function. As I sad the last time, I'll continue to use and suggest the Mysqldumper utility for bigger DB. And With a cronjob function on my server I can do a daily backup without any problem. The backup finish in about 90 seconds.
Anyway I think that it could be an useful and easy script for small DB's. :wink:

andrea75 wrote: [View Post]

Whit a 115 MB of DB, I have had a max execution time error:

Fatal error: Maximum execution time of 360 seconds exceeded in eezydump.php on line 253

The same problem that I have with integrated backup function. As I sad the last time, I'll continue to use and suggest the Mysqldumper utility for bigger DB. And With a cronjob function on my server I can do a daily backup without any problem. The backup finish in about 90 seconds.
Anyway I think that it could be an useful and easy script for small DB's. :wink:

115 MB :shock: :shock: Bloody hell you must gabble on one hell of a lot in Italy! (Actually been there enough times, I know the answer to that ;) ).

Any thanks for this post. I'm rewriting this mod presently, and one thing is to avoid these kinds of errors. I've never seen a phpbb DB as big as 115MB though, so this could be cause for a rethink! My own problem is some unknown limitation by a freehost on the script execution - php_ini says the max_execution_time is 30 seconds, yet it times out in almost half - so I suspect they've limited it in apache.

Anyway one simple thing I've currently done to improve performance is to not backup the sessions table, which I accidentally forgot to do in the above script. There is no need to back that table up, since by virtue of the backup, the sessions will be out of date, and thus utterly useless. And yet, the sessions table can easily be one of the biggest tables (hence my topic Here - some things in the way phpbb and icy operate are still not so well thought out, and some tables get bloated with redundant information. Do you have any information on how big your sessions table is? (Or maybe you already backup all your tables except the sessions table?)...

Anyway, at some point I hope to resolve the execution time issue by having the page reload itself every period governed by the max_execution_time setting (I also now have added something to the script to temporarily increase the max_time php.ini setting for the duration of the script, however, the apache server itself usually has a timeout of around 300s, so I'm surprised you got to 360s!). Another issue is if the safe mode is on, then you cannot change the max_execution_time - so thus the page reload option is better. This then means that the page display needs not to show the input forms, as every time the page reloads, you'll get an annoying warning from the browser about resubmitting form data (this is the point I got up to with this approach, have yet to change the way data is displayed, it may require having a second page if a refresh to the same page and different mode (e.g. easydump.php?mode=process) doesn't fix the browser post data problem... and the way it works then is to save table data temporarily in a $_SESSION array (along with table list - it won't be data for all of the tables, simply a record of where in the backup it has got to, in order to continue parsing from SQL). So there is potentially a way round this with a script, for those of us who don't have perl/cronjobs ;) Actually this approach is not too different to Mysqldumper, except it avoids javascript, and uses $_SESSION arrays instead.

Anyway, in the meantime, the main improvement, I hope, will be a change in the way the data is extracted from mysql and stored to disk. Currently it calls a save to disk routine several times for every row of data that is pulled from the table - and thus all these data writes slows down the script quite a lot. I'm rewriting it to store all the table data into a variable, and then at the end of all the rows, save to disk. The reason I did the save on every table data row it was to avoid exceeding RAM for the script, but thinking about it, you're much less likely to have that happen, and you're much more likely to be able to temporarily increase the RAM with ini_set than increase the execution time with something like set_time_limit(300); -

- I mean, whats the biggest table? posts_text, any idea how big yours is? One should at least be able to set RAM to 128MB to allow the script to save all your posts_text into a string, before writing to file.

This project is really growing! When I started out, all really I just wanted to dump a mere 6Mb database without the hassle of myphpAdmin screwing it up! ;)

Also I just spotted this:

Quote:

Security: MySQLDumper can generate a .htaccess-file to protect itself and all of your backup-files

I too have that feature on my current development stage - actually its for a feature to allow you to create your own backup directory, the htaccess file is then generated. It then keeps a record of all the directories you've created and saved backups to, so when you reload it, or use the installer, you have an idea where to look / what directories you used. All will be revealed soon ;)

In the meantime I will remove the script above - it is now "obsolete"...