MySQL Notes

Some quick notes on installing MySQL 5 on a Debian or Ubuntu server using the command line.

1. Determine the available MySQL version(s)

This step is optional; to find out what MySQL versions are available, execute

root@server ~ # apt-cache search mysql-server
auth2db - Powerful and eye-candy IDS logger, log viewer and alert generator
mysql-cluster-server - MySQL database server (metapackage depending on the latest version)
mysql-cluster-server-5.1 - MySQL database server binaries
torrentflux - web based, feature-rich BitTorrent download manager
mysql-server - MySQL database server (metapackage depending on the latest version)
mysql-server-5.1 - MySQL database server binaries
mysql-server-core-5.1 - MySQL database core server files
cacti - Frontend to rrdtool for monitoring systems and services

Normally the mysql-server package will exist as a meta package which will install the latest MySQL version available on the system - in this case 5.1.

Use the function UNIX_TIMESTAMP():

mysql> SELECT ts, UNIX_TIMESTAMP(ts) FROM ts_test;
+---------------------+--------------------+
| ts                  | UNIX_TIMESTAMP(ts) |
+---------------------+--------------------+
| 2013-02-17 16:34:09 |         1361086449 |
+---------------------+--------------------+
1 row in set (0.00 sec)

To get the current epoch, use UNIX_TIMESTAMP() with out any arguments:

mysql> SELECT UNIX_TIMESTAMP();
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1361088422 |
+-----------------------+
1 row in set (0.00 sec)

A slight MySQL oddity: SELECT UNIX_TIMESTAMP(NOW()) is equivalent to SELECT UNIX_TIMESTAMP(), however SELECT UNIX_TIMESTAMP(UTC_TIMESTAMP()) will produce an epoch number in the past.

mysql> SELECT UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(UTC_TIMESTAMP());
+-----------------------+---------------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP(UTC_TIMESTAMP()) |
+-----------------------+---------------------------------+
|            1361088547 |                      1361056147 |
+-----------------------+---------------------------------+
1 row in set (0.00 sec)

A bit of a gotcha maybe, especially if you're used to the PostgreSQL CLI (where -U specifies the user name):

Although the connection to MySQL was seemingly successful, any commands only seem to result in the laconic message "Ignoring query to other database". I'm not quite sure why this happens, but -U has the following meaning in MySQL:

Once again a bit of a head-scratcher which would be helped at the very least by less ambiguous notification messages.

According to the changelog for the 5.1.12 beta release:

Incompatible change: Support for the BerkeleyDB (BDB) engine has been dropped from this release. Any existing tables that are in BDB format will not be readable from within MySQL from 5.1.12 or newer. You should convert your tables to another storage engine before upgrading to 5.1.12.

An interesting perspective from Lisa Vaas of eWeek.com on Oracle's purchase of InnoDB: The Truth Comes Out: Oracle Bought InnoDB Without a Clue