Hi,
\n
\nRecently 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 :
\n[sql]
\nmysqldump –user=usr –password=pwd database_name table_name > dumpFile.sql;
\n[\/sql]<\/p>\n
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 :
\n <\/p>\n
[sql]
\nDROP TABLE IF EXISTS `table_name`;
\n\/*!40101 SET @saved_cs_client = @@character_set_client *\/;
\n\/*!40101 SET character_set_client = utf8 *\/;
\nCREATE TABLE `table_name` (
\n …
\n) ENGINE=InnoDB AUTO_INCREMENT=204182 DEFAULT CHARSET=latin1;<\/p>\n
—
\n— Dumping data for table `table_name`<\/p>\n
LOCK TABLES `table_name` WRITE;
\n\/*!40000 ALTER TABLE `table_name` DISABLE KEYS *\/;
\nINSERT INTO `table_name` VALUES (‘1′,’data1’);
\n\/*!40000 ALTER TABLE `table_name` ENABLE KEYS *\/;
\nUNLOCK TABLES;
\n[\/sql]<\/p>\n
Because of the above dump, it will always first drop and recreate the table before sourcing your dump into the database.
\nSo 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.
\n <\/p>\n
[sql]
\nmysqldump –user=usr –password=pwd –no-create-info database_name table_name > dumpFile.sql;
\n[\/sql]<\/p>\n
If you compare the first and above statement there is only one change, –no-create-info<\/strong> option : [sql] LOCK TABLES `table_name` WRITE; <\/p>\n [sql] In above statement with –no-create-info, we removed the create-drop of table and by –where<\/strong> 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 : LOCK TABLES `table_name` WRITE;
\nIt don’t write CREATE TABLE statements that re-create each dumped table. So our dumpFile.sql now contains only this :<\/p>\n
\n—
\n— Dumping data for table `table_name`<\/p>\n
\n\/*!40000 ALTER TABLE `table_name` DISABLE KEYS *\/;
\nINSERT INTO `table_name` VALUES (‘1′,’data1’);
\n\/*!40000 ALTER TABLE `table_name` ENABLE KEYS *\/;
\nUNLOCK TABLES;
\n[\/sql]<\/p>\n\/\/Creating Dump of particular records using conditional clause and without create and drop of table :<\/strong><\/em><\/ul>\n
\nmysqldump –user=usr –password=pwd –no-create-info database_name table_name –where="id in (1,2,3)"> dumpFile.sql;
\n[\/sql]<\/p>\n
\n[sql]
\n—
\n— Dumping data for table `table_name`
\n—
\n— WHERE: id in (1,2,3)<\/p>\n
\n\/*!40000 ALTER TABLE `table_name` DISABLE KEYS *\/;
\nINSERT INTO `table_name` VALUES (‘1′,’data1’),(‘2′,’data2’),(‘3′,’data3’);
\n\/*!40000 ALTER TABLE `table_name` ENABLE KEYS *\/;
\nUNLOCK TABLES;
\n[\/sql]
\n
\nI hope it helps. \ud83d\ude42
\n
\nThanks,
\nTarun Pareek
\ntarun@intelligrape.com<\/p>\n