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.<\/p>\n
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.<\/p>\n
mysql> desc poller_output;<\/strong>\n+---------------+-----------------------+------+-----+---------------------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+---------------+-----------------------+------+-----+---------------------+-------+\n| local_data_id | mediumint(8) unsigned | NO | PRI | 0 | |\n| rrd_name | varchar(19) | NO | PRI | NULL | |\n| time | datetime | NO | PRI | 0000-00-00 00:00:00 | |\n| output | text | NO | | NULL | |\n+---------------+-----------------------+------+-----+---------------------+-------+\n4 rows in set (0.00 sec)\n<\/pre>\nIt’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):<\/p>\n
mysql> show create table poller_outputG<\/strong>\n*************************** 1. row ***************************\n Table: poller_output\nCreate Table: CREATE TABLE `poller_output` (\n `local_data_id` mediumint(8) unsigned NOT NULL default '0',\n `rrd_name` varchar(19) NOT NULL default '',\n `time` datetime NOT NULL default '0000-00-00 00:00:00',\n `output` text NOT NULL,\n PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1\n1 row in set (0.00 sec)\n<\/pre>\nWhy 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!<\/p>\n
Well not viola for me, but viola for anyone whose filesystem hasn’t gone corrupt.<\/p>","protected":false},"excerpt":{"rendered":"
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 … Continue reading