Silly MySQL trick of the day. This is probably no stranger to those work with MySQL all the time, but it’s a nice trick to have up your sleeve.

For reasons I still can’t wrap my head around, one of the MySQL tables we need for Cacti just up and vanished. It’s not really used for anything essential, so it’s not a great loss, just really annoying. So I need to recreate it. Fortunately, I have MySQL running on another machine.

mysql> desc poller_output;
+---------------+-----------------------+------+-----+---------------------+-------+
| Field         | Type                  | Null | Key | Default             | Extra |
+---------------+-----------------------+------+-----+---------------------+-------+
| local_data_id | mediumint(8) unsigned | NO   | PRI | 0                   |       |
| rrd_name      | varchar(19)           | NO   | PRI | NULL                |       |
| time          | datetime              | NO   | PRI | 0000-00-00 00:00:00 |       |
| output        | text                  | NO   |     | NULL                |       |
+---------------+-----------------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

It’s not that complicated. I could take a second and write out the CREATE TABLE statement I’d need to run on the machine missing the table. But here’s a neat trick (run on the system with the table, obviously):

mysql> show create table poller_outputG
*************************** 1. row ***************************
       Table: poller_output
Create Table: CREATE TABLE `poller_output` (
  `local_data_id` mediumint(8) unsigned NOT NULL default '0',
  `rrd_name` varchar(19) NOT NULL default '',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `output` text NOT NULL,
  PRIMARY KEY  (`local_data_id`,`rrd_name`,`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Why write out the CREATE TABLE statement when we can just let MySQL tell us exactly how to re-create a table? I initially discovered this with the considerably more complex issue of views (SHOW CREATE VIEW), but it extends to tables, too. So now we can just copy-and-paste. Viola!

Well not viola for me, but viola for anyone whose filesystem hasn’t gone corrupt.