mysqldump exclude tables

mysqldump -u username -p database --ignore-table=database.table1 > database.sql

If you want to ignore multiple tables you can use a simple 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 ${DATABASE} > ${DB_FILE}

echo “Dump content”
mysqldump –host=${HOST} –user=${USER} –password=${PASSWORD} ${DATABASE} ${IGNORED_TABLES_STRING} >> ${DB_FILE}

 

 

 

export tables only, no views

Hi there In my database I have some tables and views. How can I export all the tables( and not the views ) from my database from command line?

You can use mysqldump with the option –ignore-table to exclude the views individually. Or use mysqldump and remove the views with an application/manually. grep might be an option:

grep -v “CREATE VIEW” db.dump > db-without-views.dump