Tag: SQL

Backing up the WordPress Database

DarkerView is a substantial part of who I am. Recorded here are my writings, photos and experiences spanning years. A personal diary recording so much of my life. While my blogging effort goes back to 2005, it is only the last few years that are hosted on the newer WordPress based DarkerView. Still, I worry about losing it all, this would be a stunning blow, not something I wish to contemplate. Thus I make an effort to back everything up.

Hawaiian Double Hulled Canoe
A Hawaiian double hulled canoe awaiting a crew below Pu’ukohala Heiau
I use a plugin called, simply enough, WordPress Database Backup. The plugin is easy enough, just a few settings to make.

The settings are fairly stright forward to figure out… You can choose to exclude storing post revisions and any spam comments you may have not yet deleted. Any optional tables, not part of the basic WordPress setup, can be included. You can choose to set up an automatic backup at several different time intervals.

The plugin also gives you a few choices in what to do with the backup file. The backup can be stored on the same server as the site, emailed to the specified address, or simply downloaded to your local computer. I generally trigger my backups manually and download them to my local machine for storage at home.

The result is a compressed file containing the necessary SQL code needed to recreate the entire WordPress database. For DarkerView, a three year old blog with almost one thousand posts, the resultant backup file is just over a gigabyte.

Unpacking and perusing the resultant backup file is interesting. Laid before you is the entire structure and contents of the site in SQL query statements. In the case of DarkerView this currently results in over sixteen thousand lines of SQL. Not having really looked at the back-end database before it is quite interesting to examine.

The backup file does not include the uploaded files, the images and thumbnails. It is necessary to copy this section of the blog manually. This is easily accomplished with an FTP session opened to the hosting computer and a single copy command.

Database Work

I have spent quite a few hours lately revamping my observation database. The whole thing had been quite neglected as of late, never being properly updated since I changed over to WordPress on the main blog. The appearance had been left in a halfway state that didn’t match anything, never mind some serious bugs.

HCG88
Overlayed DSS image of HCG88. These overlays are generated on-the-fly using the object data.
In addition to adding all of my recent observations at the telescope, I have redone the style sheets. You can now select white-on-black, black-on-white, or night vision red, just look for the pull-down menu on the object page.

The printable version is still there, a clean black-on-white layout including inverted DSS images for printing. I took a cue from that printed version and kept the other new layouts very clean and uncluttered in appearance.

There is a nearby object section that picks up any close by objects for quick reference. Some attention has been paid to the search routines for better usability.

Those are just the visible changes, much of the work has gone into the back end to improve the quality of the underlying data. The whole thing uses Python and Tk on my local machine, this gets converted to SQL and PHP for the webserver. Most of the tools are automated at this point, and getting less buggy as I hunt down the little issues that I find with use.

I put the whole thing together for my own use, a place to organize my observing notes. But as it is online, anyone can use it. Let me know if you ever find it useful.