The visualization shows live traffic^[1] aggregated from various projects that are funded by MDA under their Interactive Digital Media funding program.

The visualization is web-based. Its front-end is built with a lot of JavaScript goodness and Google Earth API; backend is powered by CakePHP with the data stream aggregated from various sites’ access logs and Google Analytics.

I had some thought, at that time, to open-source it I did not do it in the end.

10. The Devin Townsend Project — Ghost
9. Opeth — Heritage
8. Anathema — Falling Deeper
7. Björk — Biophilia
6. Aaron Lewis — Town Line
5. Steven Wilson — Grace For Drowning
4. Blackfield — Welcome to My DNA
3. Pain of Salvation — Road Salt Two
2. Dream Theater — A Dramatic Turn of Events
1. Symphony X — Iconoclast

And it’s the year I finally discovered Gentle Giant, probably the most creative progressive rock band ever!

A lot of the times, we simply store these hashes directly into databases, setting the field data type as string, or use the hashes as filenames (handy for creating cache files) or as part of URL.

While there is nothing wrong in that, sometimes it bothers me that hashes, which are hexadecimal (base-16) are stored in memory/storage spaces that are of larger base, eg. string-typed field in ASCII encoding can store 2⁸ different character types (essentially making it base-256), in UTF-8: base-(2³²), used as filenames in case-insensitive Non-POSIX Win32 NTFS: whatever base that is consisted of possible UTF-16 characters.

To improve on that, translate the hash, usually of hexadecimal, into numbers/strings with larger character sets. One of the easiest and handiest way to achieve this in PHP is through the use of base_convert function.

$nicerHash = base_convert($hash, 16, 36);

// gets us '9ou4tb14lz40cokgw4ocoscs8'
// from 'a3aca2964e72000eea4c56cb341002a4'

Unfortunately, as the largest-supported base of base_convert is only 36, this still does not give you the full range of supported character sets for most cases. It does, however, help in improving character-set utilization, shortening the resulting hash’s string length and at the same time, making the hash appears more human-friendly (at least to me with the fact my filenames are not all consists of only 0-f).

For better conversion of bases in PHP, you’ll have to write your own translation function. Do take note, however, that not all UTF-8 characters are printable and may result in corruption when transported through certain mediums, say as part of URL; for alpha characters, the letter case may also get lost in transition.

1. Configure PHP with ODBC support.

   yum install php4-odbc unixODBC

   You should check that it is successfully installed by doing phpinfo().

2. Obtain the latest DB2 ODBC driver from IBM. You can download it from here. Lookout for Driver for ODBC and CLI.
3. Untar it at a dir. I chose /opt/db2cli.
4. Edit odbcinst.ini to define a new ODBC connection. The file should be located at /etc/odbcinst.ini. Add the following:

   [DB2]
   Description     = DB2 Driver
   Driver      	= /opt/db2cli/lib/libdb2.so
   FileUsage       = 1
   DontDLClose     = 1

5. You can now connect to IBM DB2 on PHP via ODBC. You can use the following PHP script to test the connection:

   $database = 'DBNAME';
   $hostname = 'HOST';
   $port = 'PORT';
   $user = 'USERNAME';
   $password = 'PASSWORD';
   $driver = 'DB2';
   $conn_string = "DRIVER=\{$driver};DATABASE=$database;HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
   
   if (!$db = odbc_connect ($conn_string, $user, $password)) echo 'Error!';
   else echo 'Success!';
   
   odbc_close($db);

You can read about more information on PHP ODBC functions via PHP’s Manual.

Though this guide is based on PHP4, this should also work for PHP5 with some minor modifications. Otherwise, you can also check out DB2 extension of PHP.

U-Zyn tweets about PHP, database, cloud computing and how to procrastinate productively at @uzyn.

Today, I’m looking for a shortcut for editing of a cell in Numbers (a spreadsheet application in iWork suite) and this is what it says:

“Edit content in cell:
Select cell and click once, twice, or three times”

Source: http://www.apple.com/support/numbers/shortcuts/

Clicking (with a mouse) is not a keyboard shortcut, Apple!

Here’s how to get around that:

alter table TABLE_NAME add column COL_NAME_TEMP numeric(8,0);
update TABLE_NAME set COL_NAME_TEMP = CAST(COL_NAME as numeric);
alter table TABLE_NAME rename COL_NAME to COL_NAME_OLD;
alter table TABLE_NAME rename COL_NAME_TEMP to COL_NAME;

Verify data is ok & delete away the old column.

alter table TABLE_NAME drop COL_NAME_OLD;

Note: This article is written based on Ubuntu Linux 9.10 Server (Karmic Koala), having previously set up MySQL to run on EBS based on Eric Hammond’s guide.
Same idea should still applies on other Linux distributions, especially newer versions of Ubuntu Linux.

Overview

Since the introduction of Elastic Block Storage (EBS) on Elastic Compute Cloud (EC2), it is recommended to run MySQL on EC2 with EBS for both consistency and easy backup.

If you are looking for tutorial on how to run MySQL on EBS, Eric Hammond has a guide on how to get that set up.

As I/O requests on EC2 is chargeable (at a rate of $0.10 per 1 million requests), it can get costly pretty quickly having MySQL running on EBS with the growth of database size. It got quite out of hand that at some point in time, for one of my instances, the I/O for EBS alone was costing 3x more than the compute time charges for the particular EC2 instance!

That was when I knew I cannot keep MySQL running on EBS for too long before it got me broke.

General Idea

The general idea is to have MySQL run on ephemeral storage of EC2, which comes in rather large volume and most importantly, I/O is not chargeable. Ephemeral storage, however, is known as such because it is not consistent. If an instance is terminated or crashes, data on ephemeral storage will be irrecoverable.

To overcome that and achieve consistency, we will have to rely on EBS. As I/O intensive operations for MySQL are mainly during sorting, especially during SELECT queries, the point will then be to have I/O heavy operations done on ephemeral storage while using EBS solely for maintaining consistency.

Here’s one way on how we can achieve that:

1. Set up 2 MySQL servers with one being master and the other being slave, replicating the master.
2. Master server runs on ephemeral storage. Applications will be connecting directly to the master.
3. Replication slave runs on EBS.

You can either launch 2 EC2 instances, having master and slave on different instances, or for the this tutorial, we will be setting them up on a single EC2 instance for cost-saving purposes.

Guide is inspired from http://ubuntuforums.org/showthread.php?t=1154296 with improvements and adaptations for EC2.

Converting from single MySQL server to dual MySQL servers on a single EC2 instance

Assuming we already have a MySQL server with setup similar to Eric Hammond’s tutorial.
Data directory (lib): /vol/lib/

In order to allow mysqld_multi script to shutdown the MySQL server, we should run this SQL on the current MySQL installation before stopping it.

GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'SOME_SECURE_PASSWORD';

Stop MySQL

sudo /etc/init.d/mysql stop

Backup the current mysql init.d script and replacing with mysqld_multi.server

sudo mv /etc/init.d/mysql /etc/init.d/mysql_mono.server

sudo cp /usr/share/mysql/mysqld_multi.server /etc/init.d/mysql

Edit /etc/init.d/mysql, with any text editor like nano:

Find and replace

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin

with

basedir=/usr
bindir=/usr/bin

Copy data from EBS-backed stroage at /vol/lib/mysql to ephemeral storage at /mnt/mysql/lib, leaving the data on EBS-backed storage to be used by the replication slave.

sudo mkdir /mnt/mysql
sudo cp -pr /vol/lib/mysql /mnt/mysql/lib
sudo mv /vol/lib /vol/lib-repli 

Edit /etc/fstab to change the mount configuration of /var/lib/mysql from /vol/lib/mysql to /mnt/mysql/lib:

sudo umount /var/lib/mysql

# Edit /etc/fstab with your favorite text editor and make the replacement as described above

# Save and remount
sudo mount /var/lib/mysql

Next, configuring my.cnf. Backing up existing one and editing /etc/mysql/my.cnf with text editor.

sudo cp -p /etc/mysql/my.cnf /etc/mysql/my.cnf.mono

When editing /etc/mysql/my.cnf, here are some things to change and take note of:

• Rename [mysql] to [mysqld1]
• Copy the whole block of configs in [mysqld1] section, change the section name into [mysqld2] and paste it right below [mysqld1] section
• For [mysqld1], search and edit (uncomment if they are commented) the following:
  (very few changes as we are keeping it as close as possible to the earlier mono configuration)

  [mysqld1]
  server-id = 1
  log_bin = /var/log/mysql/mysql1-bin.log

• For [mysqld2], search and edit (uncomment if they are commented) the following:

  [mysqld2]
  pid-file = /var/run/mysqld2.pid
  socket = /var/run/mysqld2.sock
  port = 3307
  server-id = 2
  datadir = /vol/lib-repli/mysql
  log = /var/log/mysql/mysql2.log
  

Before we can start mysql, we will also need to update AppArmor to grant permissions to mysqld to update the files for mysqld2.
Backup, edit and add insert the following lines to /etc/apparmor.d/usr.sbin.mysqld before the last line with the closing curly bracket ( } ):

/vol/lib-repli/ r,
/vol/lib-repli/** rwk,
/var/run/mysqld/mysqld2.pid w,
/var/run/mysqld/mysqld2.sock w,

Restart AppArmor

sudo /etc/init.d/apparmor restart

And now start MySQL

sudo /etc/init.d/myql start

If everything is set up correctly, MySQL instances will silently run. You can verify that by invoking:

ps aux | grep mysql | grep -v grep

You should see 2 instances of MySQL running:
Master, with server ID 1, running on ephemeral storage, and on the default MySQL port 3306.
Slave, with server ID 2, running on EBS, and on port 3307.

Before starting up application that modifies/writes to MySQL, we should set up replication.

Setting up MySQL master-slave replication

Edit /etc/mysql/my.cnf once again to configure replication.

At [mysqld1], enter the list of databases you would like to replicate under binlog-do-db. If there are multiple databases, you can list them under multiple binlog-do-db. For the purpose of this tutorial, let’s assume we would like to set up replication for exampledb1 and exampledb2.

[mysqld1]
binlog-do-db=exampledb1
binlog-do-db=exampledb2

At [mysqld2], enter the same list of databases as above under replicate-do-db

[mysqld2]
replicate-do-db=exampledb1
replicate-do-db=exampledb2

Restart MySQL:

sudo /etc/init.d/mysql restart

Run these SQL queries on master (port 3306):

# Just to be sure that we are on master server. We should be getting 1.
SELECT @@server_id; 

GRANT REPLICATION SLAVE ON *.* TO 'replslave'@'localhost' IDENTIFIED BY 'YOUR_OWN_CHOSEN_SLAVE_PASSWORD';

FLUSH PRIVILEGES;
SHOW MASTER STATUS;
# Note down the results of this, we will need them as we set up replication slave

Run these SQL queries on slave (port 3307):

# Just to be sure that we are on slave server. We should be getting 2.
SELECT @@server_id; 

# Take note of MASTER_LOG_FILE and MASTER_LOG_POS
# You should be entering the values that we take down earlier from master server.
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='replslave', MASTER_PORT=3306, MASTER_PASSWORD='YOUR_OWN_CHOSEN_SLAVE_PASSWORD', MASTER_LOG_FILE='mysql1-bin.000001', MASTER_LOG_POS=106;

START SLAVE;
SHOW SLAVE STATUS \G

If replication is set up properly, we should see these two lines indicating that it is running well:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Complete!

Congratulations on successfully setting up MySQL master-slave replication on the same EC2 instance.

You can now run your MySQL-based applications. There should not be any changes needed for your applications to run as the master MySQL server is running default port and default socket.

Now you do not have to worry about I/O heavy operations on MySQL raising your cloud computing bills and at the same time having the peace of mind that your MySQL data is safely being replicated on a consistent EBS-backed storage.

For further safeguard, you can even take EBS snapshots from time to time.

Feedback

As this tutorial is written by attempting to retrace the steps I have taken for my setup, I may have missed some pointers.
Let me know in the comments below or email me (chua@uzyn.com) if you have any questions or suggestions, or just to let me know that you have successfully set it up by following this tutorial.

If you are into cloud computing, do consider subscribing to the blog. Thanks.

From website of Malaysia’s Ministry of Foreign Affairs.

From: capsule@forbes.net
To: chua@uzyn.com
Date: Sun, Nov 7, 2010 at 4:15 AM
Subject: Hi from me 5 years back

Greetings from your past. In the fall of 2005, you agreed to receive this
message, which has been preserved in the Forbes.com E-Mail Time Capsule.
For more details, visit the links below.

Here is the text of your message:

Hello. This is me 5 years back. Sitting in PGP^[1], NUS^[2]. 2 weeks before exams, caught up between work^[3] and studies.

I’m madly in love with Sek Ling.

__________________________________________________________________________
The Forbes Time Capsule: created & perpetuated by Codefix Consulting Inc.
http://blogs.forbes.com/davidewalt/2010/11/05/forbes-email-time-capsule/
http://codefix.net/capsule

Today, I am married to her.

More on this project from Forbes

05 November 2010 — Another follow-up after 5 years on Forbes.com
26 October 2006 — Follow-up on Forbes.com
21 October 2005 — The original story on Forbes.com

Good to be back again. 140 chars are killing me.