Some MySQL Goodies

I’ve been working on migrating this site to Drupal v6.2. In order to do so, I’ve created some test/development areas to work through the transition. These areas required me to repeatedly copy my live (version 5) database over to various test and staging servers. I researched and perfected a few one-shot command line recipes to handle all of this. I figured I’d share them with the world.

In each of these examples, the multiple lines are to improve readability. All of these commands should be run on a single line.

First off, we have the basic “mysqldump” command, used to create a backup. This syntax can be found anywhere, but it’s the starting point for the rest of these recipes.

mysqldump -Ce -u[username] -p[password] -h[database server] --add-drop-tables [dbname] > [filename.sql]

A few notes: You don’t want spaces between the -u, -p and -h flags, unlike most command-line tools. The -Ce flags compress the data transfer and output extended inserts in your restore code. This speeds things up considerably, but doesn’t work with every version of mysql.

You can also back up all the databases by substituting “–all-databases” for the database name. You can also pipe this through gzip to compress it by putting “| gzip” in between [dbname] and “>”. Nice for storing regular backups, these files compress nicely.

You can restore pretty easily by just putting the whole thing back through mysql:

mysql -C -u[username] -p[password] -h[database server] [dbname] < [filename.sql]

Since you put the “–add-drop-tables” flag into the backup, any existing tables in your restore DB that you’re restoring will be dropped and re-created. This will speed things up a bit, and makes for a tidier restore. It also helps if there’s been any schema changes since your last backup.

But what if you’re using a database that, say, you’ve upgraded and there’s been new tables added to it that you’d rather not have around? The easiest option is to just drop the database and re-create it. But if you’re on a shared hosting account, you probably don’t have that kind of access. So you’ll have to drop the tables by hand.

This typically involves a query like “drop table ‘table1’; drop table ‘table2’; etc…”, but that’s pretty tedious. Even if you use a graphical or web-based mysql management client, it’s still an awful lot of checkboxes to click. Instead, you can use that handy –add-drop-tables flag to generate a script that will do that for you, dynamically. You just need to make sure you parse out all of the commands to re-create those tables. Here’s what that script looks like:

mysqldump -C u[username] -p[password] -h[database server] --add-drop-table --no-data [dbname] | grep ^DROP > [filename.sql]

(Be sure to give it a different file name than your backup script!)

Or, if you want to get a bit fancier, you can pipe this whole script to a mysql command to drop the tables in one fell swoop:

mysqldump -C u[username] -p[password] -h[database server] --add-drop-table --no-data [dbname] | grep ^DROP | mysql -C -u[username] -p[password] -h[database server] [dbname]

Once you’ve cleared out your database, you can use the same technique to move your production database to your development server in one line:

mysqldump -Ce -u[source_username] -p[source_password] -h[source_database server] --add-drop-tables [source_dbname] | mysql -C -u[destination_username] -p[destination_password] -h[destination_database server] [destination_dbname]

And, finally, put both the clear and copy commands on one line, separated by a semicolon, to execute the whole thing at once. Instant Copy!

mysqldump -C u[destination_username] -p[destination_password] -h[destination_database server] --add-drop-table --no-data [destination_dbname] | grep ^DROP | mysql -C -u[destination_username] -p[destination_password] -h[destination_database server] [destination_dbname] ; mysqldump -Ce -u[source_username] -p[source_password] -h[source_database server] --add-drop-tables [source_dbname] | mysql -C -u[destination_username] -p[destination_password] -h[destination_database server] [destination_dbname]

Admittedly, that’s a lot more than you probably want to type on a single line, so you’ll probably want to write a script. But if you’re just doing this for a day or something while you work on a site upgrade or whatever, it’ll get the job done for you for sure!

Written on May 15, 2008

DRM: 0, Music Industry: 0, iTunes: 101

Apparently market forces are making DRM passé. By locking down iTunes store purchases, Apple’s managed to maintain a stranglehold on its customers and a dominant position to negotiate contracts with music providers. Nice job, Music Industry!

> “The record companies don’t like dealing with Apple, because Apple is in a position where it can dictate the economic terms and dictate the business models,” says [Bill Rosenblatt, DRM specialist]. “What’s going to draw people away from iTunes? One answer is to get rid of DRM.”

And, furthermore, Mr. Rosenblatt says DRM has no effect whatsoever on reducing piracy. As if that wasn’t obvious from the start.

You gotta love it when the free market works the way it’s supposed to.

From The Guardian

Written on May 15, 2008

Don't Blog. Write.

Robin Hobb’s new book, Renegade Mage, just came out. I thought I’d pop over to her blog (which I haven’t looked at in years) and see if she had anything to say about this book or upcoming projects.

Instead of a blog, I found an [odd little scavenger hunt](http://www.robinhobb.com/] featuring pictures of her cats, and then in the attic, a cautionary tale for all would-be writers. A tale of missed deadlines, sleepless nights, and banal, unsatisfying, writing.

> Ah, my writer friend. It is harsh but it must be said. Compared to the studied seduction of the novel, blogging is literary pole dancing. Anyone can stand naked in the window of the public’s eye, anyone can twitch and writhe and emote over the package that was not delivered, the dinner that burned, the friend who forgot your birthday. That is not fiction. That is life, and we all have one. Blogging condemns us to live everyone else’s tedious day as well as our own.

> …Oh, my dearest writer friend. Be strong. Resist the siren call.

> Don’t blog. Write.

Written on May 14, 2008

Community and Independent Developers

This morning, I posted an article that was highly critical of Matthew Drayton’s management of the Interarchy file transfer application since he, as Nolobe, purchased it from the original developer. Specifically, I was frustrated with the lack of communication and shutting of communication channels between the big 9.0 release and the much-needed 9.01 bug fix which just came out.

Only a few hours after I posted this article, Matthew contacted me to apologize for the release and also to explain the circumstances which made a timely release of 9.01 impossible. As my criticism was both public and unjustified, I’ll apologize here, publicly, for this criticism. I have also unpublished that article.

However, beyond the specific criticism, it does demonstrate the importance of maintaining open communication with your customers. While most customers respond favorably to open communication, I think it’s especially important for small and independent companies, including independent software developers. This is probably even more important for independent developers who sell exclusively online, since their customers are much more likely to be part of the blogging/forum posting/twittering crowd.

People who purchase from independent developers act like grass roots supporters of a political campaign. Whether or not it’s justified, they feel that they are on a first-name basis with their favorite software’s developer, and they tend to especially watch new products from the same company.

This relationship is based on trust and communication. Those developers who actively maintain blogs, participate in forums, or who simply email quickly and responsively to requests can generate very passionate users. (Even if their software isn’t terribly high quality!)

Of course, those supporting customers come to count on this open communication. If it breaks down, it can leave customers feeling abandoned, and make them lose faith in the developer and their software. It can cause them to cease upgrading or even to defect to other programs. And, of course, there’s the beatings that an unresponsive developer can face on forums such as VersionTracker and MacUpdate.

What some developers overlook (and, again, I am not picking on Matthew here) is that this intimacy is a two way street. Just as the open communication helps users learn to use their software better, it is also a fantastic tool for priming the market for new updates and new products. And, perhaps even more importantly, it creates opportunities for the developer to get their users’ aid when they need it. Whether that’s a request for patience on an overdue update, advice on where to move web hosting to, or to gather a group of volunteer beta testers or even contributors. (documentation wiki, anyone?)

When Nolobe went “dark,” and stopped posting to blogs and pulled its forums, I lost confidence in the company and the software. I hadn’t upgraded to Interarchy 9 and was still using 8 until a less buggy version was available. Even though the developer was doing his utmost to get that 9.01 update out the door, it took a few months.

In the grand scheme of things, that isn’t much.

On the other hand, I’ve been using Interarchy (well, Anarchie and then Interarchy) for more than ten years. Seeing it change owners and then become unreliable on the next update is something else entirely.

Should Matthew have posted, at a minimum, a blog entry saying “It’ll come out later, please be patient?” It couldn’t have hurt. When a favorite restaurant is closed, you at least expect a sign saying when they’ll be open again – whether that’s tomorrow morning and you just caught them outside of business hours, or if it’s in a few weeks while they renovate.

Sometimes a person doesn’t even have the time or energy to even do that much. But for the users, the faithful supporters of a business, that note can mean everything.

Want proof? I just purchased the Interarchy 9 upgrade I’d been holding off on.

I didn’t buy it by way of apology for my undue critique. I bought it for two reasons: It fixed the bugs that made me hold off on the upgrade in the first place; and Matthew’s prompt and charitable email, even after my harsh criticism of Matthew himself – not just his software or his company. This email restored my faith in Matthew and Nolobe as stewards of one my mainstay programs. After all, what could be more personal and intimate than that personal email?

Written on April 15, 2008

MacFUSE vs. ExpanDrive

MacFUSE with SSHFS gives you Finder-level access to your SSH and SFTP servers. It’s been out for a while and is free and open source. Now it’s being challenged by ExpanDrive, which does much the same thing, but costs $29 (at a special introductory price, no less).

So which one is better?

I’ve been using MacFUSE ever since it first came on the scene in order to access my DreamHost web server. For most tasks, I’d much rather copy a file within the Finder than open a custom client program, log into a server, and deal with upload/download target directories, external editors, etc. So MacFUSE’s SSHfs module has been an absolute dream for me.

Of course, it’s also a bit frustrating. It seems like Interarchy’s always just a bit faster at browsing my SFTP server. Copying files seems to send them back home and out to the server again, so a file duplication in the same filesystem that should take a few minutes takes ten or more! And then there’s the times that it just sort of craps out on me for some reason. Those times are rare, but they happen.

So when Daring Fireball tipped me off to Magnetk’s ExpanDrive utility, I decided to give it a try and run it through it’s paces.

The ExpanDrive developers comment that it’s based on MacFUSE, but has been variously improved with better caching and other “secret sauce.” It also handles reconnects after your computer goes to sleep, and claims to deal gracefully with poor network connections..

I gave it some straightforward tests. I opened and edited files; copied large and small files back and forth (and timed the copies); and then did my best to break things by opening disk images on the mounted SFTP drive, expanding and compressing zip files, and duplicating large files.

The results? They’re the same. Really. The same.

I got some slightly different performance on copying small files from the ExpanDrive rather than through MacFUSE if I’d already downloaded them once. This must be the caching they’ve referred to. But the difference was negligible, since it only applied to smaller files.

Both were horrid at duplicating files, and both crashed the Finder once. (It came right back with the remote SFTP drives mounted in both cases)

Now ExpanDrive is very easy to install and use, which definitely scores some points with me. But MacFUSE is no longer a horrid geeky affair to get SSHFS up and running – just a regular package installer and then an SSHFS application that loads your drive with a double-click. You can also use a utility like MacFusion to give you a nice GUI to mount drives. A bit more complex, maybe, but not bad. (MacFusion also re-mounts filesystems after your computer wakes up)

Based on my tests, I’m sticking with MacFUSE and SSHFS. Mostly I just need it as an alternative to an FTP client, and it works fantastically for that. If I were to keep the remote disk up and running all the time (a la my iDisk), then I might be more interested in ExpanDrive’s incremental improvements over the basic MacFUSE setup. (Although I could use JungleDisk – also based on MacFUSE, but using AmazonS3 instead of SFTP – to do the same thing and enjoy the cheapest online storage out there as well as the peace of mind of having all my remote files encrypted)

Regardless, it’s exciting to see all the interesting projects that have come out of the FUSE and MacFUSE projects. There’s just nothing quite like browsing your web server’s image directories using the CoverFlow view and with QuickLook readily available.

Written on March 27, 2008

OmniWeb 5 Ad Blocking Lists

OmniWeb 5 has an excellent ad blocking mechanism. What it doesn’t have is the ability to easily subscribe to or import some of the blacklists out on the internet, such as those used by the Adblock Plus Firefox add-on.

In order to make up for this deficit (at least partially), I downloaded the latest EasyList filter list and modified it so that it uses proper, OmniWeb-compatible, regular expressions.

The one downside with this list is that there is no way to automatically import the blacklist and whitelist files into OmniWeb. Instead, you have to edit OmniWeb’s preferences file directly to add the entries. Instructions on how to do this are included with the rest of the download.

I have no plans to continue updating this list. It’s current as of March 11, 2008. Consider it a good start for your ad filtering and update it manually as needed. I’ve included instructions on how to make your own custom file if you’re so inclined.

Written on March 11, 2008

RIP: Gary Gygax

Gary Gygax, creator of Dungeons & Dragons, died on March 4th. I cannot begin to estimate how many hours (and dollars!) I’ve spent playing, studying up on, imagining, and otherwise enjoying his games and those that came after.

Written on March 8, 2008

Nik's Picks: Mondrianum

If you have a Mac, you have friends and family who assume you’re a designer. “Please make me a business card!” they plead, “Can you set up a brochure for me?”

You’re a nice person, though, so you break out AppleWorks and start putting stuff together. Hey, even someone with my level of design skills can put up some boxes and text and a picture. The problem comes when you have to pick what color to make everything.

Let’s see… Red, Blue, and Green. Those are nice colors. But why does it look like crap?

Enter Mondrianum, or color picking for farm animals and the mentally disabled. Mondrianum is a wicked cool plug-in for MacOS X’s color picker that lets you browser color sets on Adobe’s Kuler site. (Kuler, if you haven’t heard of it, is a site where design nerds can create and share color palettes with one another.)

Thanks to Kuler, a lot of folks with WAY better design sense than you have, created a wealth of cool color palettes. Pick a palette, pick your colors, and go to town!

Now THAT’S a nice brochure!

Written on January 25, 2008