Mysql – thesqldump entire structure but only data from selected tables in a single command

MySQLmysqldump

My database has 3 tables: table1, table2 and table3

I would like to do a mysqldump on this database with the following conditions:

Dump structure for all tables
Only dump data for table1 and table2, ignore data in table3

Currently, I do this with 2 mysqldump statements

mysqldump -u user -p -d db > db_structure.sql
mysqldump -u user -p db --ignore-table=db.table3 > table1_and_table2_data.sql

Import them in the same order they were dumped (structure, then data from table1 and table2)

Is there a way to combine this into a single mysqldump command?

Best Answer

You can't combine them in one command but you can execute both commands at the same time and output to the same file.

mysqldump -u user -p --no-data db > structure.sql; mysqldump -u user -p db table1 table2 >> structure.sql

to avoid having to enter the password twice you can do -ppassword (note the lack of space!). Also use --no-data in the first command or you end up with the data as well. -d isn't needed when you're doing just one database.

Related Solutions

Mysql – Skip certain tables with thesqldump

You can use the --ignore-table option. So you could do

mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table=DATABASE.table1 > database.sql

There is no whitespace after -p (this is not a typo).

To ignore multiple tables, use this option multiple times, this is documented to work since at least version 5.0.

If you want an alternative way to ignore multiple tables you can use a script like this:

#!/bin/bash
PASSWORD=XXXXXX
HOST=XXXXXX
USER=XXXXXX
DATABASE=databasename
DB_FILE=dump.sql
EXCLUDED_TABLES=(
table1
table2
table3
table4
tableN   
)
 
IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE} > ${DB_FILE}

echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}

Mysql – thesqldump data only

mysqldump --no-create-info ...

Also you may use:

--skip-triggers: if you are using triggers
--no-create-db: if you are using --databases ... option
--compact: if you want to get rid of extra comments

Best Answer

Related Solutions

Mysql – Skip certain tables with thesqldump

Mysql – thesqldump data only

Related Topic