How to Exclude Data for Specific Tables

Question

I am using mysqldump to create a canonical installation script for a MySQL database. I would like to dump the data for able half of the tables in the database, but exclude the data from the other tables. I am aware of the following two commands:

--no-data

--ignore-table

But the first applies to all tables, and I believe the second excludes the table entirely from the dump (e.g. create statements) not just the data in the table. Anyone know how to use mysqldump to achieve my goal?

EDIT:

found a near duplicate question: http://stackoverflow.com/questions/1764387/mysqldump-entire-structure-but-only-data-from-selected-tables-in-a-single-command

Answer 1

mysqldump -u user -h host.example.com -p database table1 table2 table3

Answer 2

You might find what you need here:

http://www.electrictoolbox.com/mysqldump-selectively-dump-data/

Using where statements is probably the easiest way to achieve what you are trying to do.

Answer 3

How about running two separate calls to mysqldump? One to create the database and ignore the tables you don't want data from. The other to just create the remaining tables without data. You could either run the two scripts separately, or concatenate them together to create a final script.

Answer 4

There is one other option to get everything done (in a single call to mysql itself) but it should probably never be attempted.

In tribute to H.P. Lovecraft, (and based upon Anuya's stored procedure to create INSERT statements) here's The Stored Procedure Which Must Not Be Called:

Note: This unholy, arcane stored procedure would only be run by a madman and is presented below purely for educational purposes.

DELIMITER $$
DROP PROCEDURE IF EXISTS `pseudoDump` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pseudoDump`(
  in_db varchar(20),
  in_tables varchar(200),
  in_data_tables varchar(200)
)
BEGIN
DECLARE Whrs varchar(500);
DECLARE Sels varchar(500);
DECLARE Inserts varchar(200);
DECLARE tablename varchar(20);
DECLARE ColName varchar(20);
SELECT `information_schema`.`TABLE_NAME` INTO tablename FROM TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME IN ( in_tables );
tabdumploop: LOOP
  SHOW CREATE TABLE tablename;
  LEAVE tabdumploop;
END LOOP tabdumploop;
SELECT `information_schema`.`TABLE_NAME` INTO tablename FROM TABLES WHERE TABLE_SCHEMA = in_db ;
datdumploop: LOOP
  SELECT group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')')) INTO @Sels from `information_schema`.`COLUMNS` where table_schema=in_db and table_name=tablename;
  SELECT group_concat('`',column_name,'`') INTO @Whrs from `information_schema`.`COLUMNS` where table_schema=in_db and table_name=tablename;
  SET @Inserts=concat("select concat('insert IGNORE into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');') as MyColumn from ", in_db,".",tablename, " where 1 group by ",@Whrs, ";");
  PREPARE Inserts FROM @Inserts;
  EXECUTE Inserts;
  LEAVE datdumploop;
END LOOP datdumploop;
END $$
DELIMITER ;

... thankfully, I was saved from witnessing the soul-wrenching horror this procedure must surely wreak by MySQL Bug #44009 ...