Battleangel | Personal

For once I’m at a loss. Though I am sure it is only temporary. The conundrum in question revolves around how to best backup your MySQL database. With emphasis on speed, automation and server load / security. Solutions would have to be free or easily crackable. Restoration of the database is of no concern since that is a singular event. Solutions must be able to run regularly, say during the night, every other day or so and use an intelligent way to make sure the files are intact (at least not download empty files) and that backups are either overwritten or better yet rotated to avoid filling up the disk if one were to croak. As such, a plain and dumb command line solution (like a win32 emulation of Cron and .bat files) will not suffice.

The most obvious way would be to automate the downloading of the backups that are available via Cpanel. On the plus side, they are actual gzipped files and can be linked to directly. The problem is that I haven’t found a way to automate the regular downloading of the files. Most backup software that allows for such scheduling don’t offer HTTP access (only FTP). This would be the preferable approach. Software would need to handle login procedures obviously. I did try a recent beta of Getright 6 but being such a cheapskate I got a cracked version that wouldn’t function properly. And besides, I don’t think I want to have Getright running 24/7 just so that I can download a few odd backups. Plus, the “daily downloads” and “sync” features seemed to both miss the point. An amalgamate of the two features would have made a better backup downloader. The best way would be to have some lightweight download manager that is specially crafted to handle scheduled backup events. But such software usually falls into its own category of backup solutions where other rules apply. It would seem there is no solution that can do both.

Another obvious way is to use some tool like MySQL Administrator to connect to the database directly and pull the data table by table. About the only thing going for that option is that it is very nerdy. The downsides are plentiful. For instance, you have to allow access to your external IP range or domain (by default the database only accepts localhost). Also, at least MySQL Administrator was clearly meant to run on a localhost database as it is extremely slow on a remote connection. Though that may be a problem with my database. The point is that it is slow. As for other tools I was unable to find any that were up to date. And it seems stupid to download the actual tables when you can get a gzipped version either from Cpanel or MySQL itself.

Another less obvious way perhaps is to create a cron job or some other scripting solution that either sends you the gzipped file (outside of Cpanel’s system) or perhaps better yet, stores the backup in the root folder (preferably outside of public_html that is) so that you can use some backup software to access it via FTP. There are plenty of software that can access FTP sites, but like I said, I have yet to find a similar backup software that can take daily backups of single files from HTTP and save them locally. Yet, it seems like a complicated way to go about things when the gzipped files are ready and waiting in Cpanel.