Fear not for I have returned

It’s been a while since I last updated and the truth is I just don’t have time to write proper blog posts. Keeping that in mind, but still wishing to keep this up-to-date. I’ve decided to post quick tips and snippets that I find useful. Here’s the first:

I’ve been doing some database work recently and I wanted to unify the character set and collation for all databases and tables. Changing the database collation was easy to do in bulk. The tables less so and I was not manually changing 13,014 tables, eventually I came up with this:

That should generate a bunch of statements that look like this:

For every table in every database except the information_schema, mysql and performance_schema databases. I just pipe these right back in to MySQL but you can output them to a text file and run that later if you prefer. This snippet should also to be useful for any mass/multiple/bulk table edits or changes.


By your command

It’s been a while since my last update, but as with any good IT guy I’ve been both too busy and too lazy to write anything. Anyway, I thought I’d make a note of some commands more for my own use that anyone else’s, but if someone else does find one useful then that’s a bonus.

Split a file at a word or pattern into multiple files:

Notes: Replace FILE with the file name you wish to run the command against.

Take a screenshot via SSH:

Notes: None.

Search for something that looks like an e-mail address:

Run a query on multiple tables in a database matching a pattern

Notes: Replace the markers where indicated (USER, PASSWORD etc) and obviously change the example query.

Command line screencast

Notes: Run nc ADDRESS 5000 to connect and watch.

Empty all log files

Notes: None.

Watch MySQL queries

Notes: Replace the markers where indicated (USER, PASSWORD etc).

Find potential duplicate files

Notes: None.


Mounting a case-insensitive filesystem on Linux

Recently I had a web developer who was coding on a Linux server, he was used to working on Windows servers and was having problems dealing with the case sensitivity of the EXT3 filesystem. I looked into it and in addition to using the Apache mod_speling module, also decided to go a step further. As root or using sudo do the following:

  1. Create a file of the size you wish the filesystem to be, 1GB in this example:
  2. Format the filesystem, fat32 in this example but you can use NTFS or another case-insensitive file system:
  3. Mount the filesystem and test it, you may wish to use uid= and gid= to mount it as a specific user and you’ll probably want to adjust the umask to something less permissive:
  4. Add this to /etc/fstab so that it gets mounted after a reboot:

I wouldn’t use this for production systems, in that situation I’d recommend a dedicated FAT32 or NTFS partition. If you do have to run this on a production system it should be fine for 99% of setups, it just feels like a quick hack to me.