Wednesday, 5 October 2016

Restoring a single table from a full mysqldump file



Yesterday I needed to restore one table from larger database dump file. To begin with I found out which lines I needed end with sed i got my data.
sed -n 955,991p data.sql > new_data.sql
Pretty easy, but in a large file you have to work your way to find the line you need. The solution I found was more specific and works like a charm. The first version of the command was a rather inaccurate because it was missing the "DROP TABLE IF EXIST" for the table you are restoring and you need to edit out few lines from the bottom of the new file before using it in order to prevent deleting the next table, "address". leaves "DROP TABLE IF EXIST" for the next table at the end of the new file.
sed -n -e '/CREATE TABLE.*`person`/,/CREATE TABLE/p' data.sql > new_data.sql
Leves this at the end og the new file.
--
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (

Then I came a cross better solution to get your table data. While this is the backup layout.
--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person `;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `person`
--

LOCK TABLES `person` WRITE;
/*!40000 ALTER TABLE `person` DISABLE KEYS */;
INSERT INTO `person` VALUES (1,'Emma'),(2,'Noah'),(3,'Olivia'),(4,'Liam'),(5,'Sophia');
/*!40000 ALTER TABLE `person` ENABLE KEYS */;
UNLOCK TABLES;

As you can see "UNLOCK TABLES" is the last line, so that is what we look for in the sed ccommand. 
sed -n -e '/DROP TABLE IF EXISTS.*`person`/,/UNLOCK TABLES/p' data.sql > new_data.sql

Now your new file should be ready to use, but I recommend that you check before you use.

--cheers
Labels: