mysqldump of particular records without create – drop of tables

Hi,
 
Recently i was in a situation where i need to take the dump of particular records in mysql. We are pretty much familiar with the normal mysqldump statement given below :
[sql]
mysqldump –user=usr –password=pwd database_name table_name > dumpFile.sql;
[/sql]

The above statement will provide us the dump of the respective database and table into the dumpFile.sql. But if you open the dump file you will see something like this :
 

//dumpFile.sql

[sql]
DROP TABLE IF EXISTS `table_name`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `table_name` (
…
) ENGINE=InnoDB AUTO_INCREMENT=204182 DEFAULT CHARSET=latin1;

—
— Dumping data for table `table_name`

LOCK TABLES `table_name` WRITE;
/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
INSERT INTO `table_name` VALUES (‘1′,’data1’);
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;
UNLOCK TABLES;
[/sql]

Because of the above dump, it will always first drop and recreate the table before sourcing your dump into the database.
So our first task to remove the creation and drop of table from the mysqldump, because when we take dump of particular records, What we want is just inserting the records in database without create and drop of tables and no loss of existing data.
 

//Creating Dump without create and drop of table :

[sql]
mysqldump –user=usr –password=pwd –no-create-info database_name table_name > dumpFile.sql;
[/sql]

If you compare the first and above statement there is only one change, –no-create-info option :
It don’t write CREATE TABLE statements that re-create each dumped table. So our dumpFile.sql now contains only this :

[sql]
—
— Dumping data for table `table_name`

LOCK TABLES `table_name` WRITE;
/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
INSERT INTO `table_name` VALUES (‘1′,’data1’);
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;
UNLOCK TABLES;
[/sql]

//Creating Dump of particular records using conditional clause and without create and drop of table :

[sql]
mysqldump –user=usr –password=pwd –no-create-info database_name table_name –where="id in (1,2,3)"> dumpFile.sql;
[/sql]

In above statement with –no-create-info, we removed the create-drop of table and by –where option we can specify our condition on basis of which we want to fetch the particular records. In above statement it will fetch the record in table that has value 1, 2 or 3 in field ‘id’. So our dumpFile.sql now contains only this :
[sql]
—
— Dumping data for table `table_name`
—
— WHERE: id in (1,2,3)

LOCK TABLES `table_name` WRITE;
/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
INSERT INTO `table_name` VALUES (‘1′,’data1’),(‘2′,’data2’),(‘3′,’data3’);
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;
UNLOCK TABLES;
[/sql]
 
I hope it helps. 🙂
 
Thanks,
Tarun Pareek
tarun@intelligrape.com

More Blogs by Me