How to exclude data for specific tables

Question

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

- there is no data

- ignore-table

But the first applies to all tables, and I believe that the second excludes the table completely from the dump (for example, creating statements), and not just the data in the table. Does anyone know how to use mysqldump to achieve my goal?

EDIT:

an almost duplicate question was found: mysqldump of the entire structure, but only data from the selected tables in one command

Answer 1

How to start two separate calls on mysqldump? One for creating a database and ignoring tables from which you do not want to receive data. Another is to simply create the remaining tables without data. You can run both scripts separately or combine them together to create the final script.

Answer 2

There is another possibility to do everything (in one call only mysql), but it should probably never be tried.

In deference to HP Lovecraft (and based on Anuya's stored procedure for creating INSERT statements ) here is a stored procedure that should not be called:

: , ​​ .

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 ;

... , , , , MySQL Bug # 44009..

Answer 3

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

Answer 4

, :

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

, , , .