Tuesday, April 28, 2009

Mysql: How to dump only the table data


Share at Facebook

How to download the table data by removing the create table format?

You can easily integrate your mysqldump command with -t parameter to exclude the create table part from your mysql dumping. The -t parameter will remove the drop table, and create table portion from your dumped data. And it will only consist the raw data as insert into format.

mysqldump -uUSERNAME -pPASSWORD DATABASE_NAME TABLE_NAME -t > TABLE_NAME.SQL

Here,
USERNAME = your mysql username;
PASSWORD = your mysql password;
DATABASE_NAME = your database name;
TABLE_NAME = the table you want to dump only the data.

From the above mysql dump command, the -t parameter is used to exclude the create table part from the dumped data. When you'll open your .sql file after dump, you'll find that it has the rows from your table.

The -t parameter is used when you have two same tables at your two different machines, and you want to migrate only the data. So the drop/create table won't require by you. That's why the -t to remove those part, and you can easily load the dumped file into your mysql table safely.

Example:
mysqldump -uroot test test1 -t > table.sql

Output:
LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES ('name\'s','desc\'s'),('test','test;test;asdasd;akdajh;test');
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;




No comments: