MySQL - How to dump database and ignore some tables with mysqldump?
If you want to skip only one table while dumping a MySQL database:
mysqldump -u user_name -pyour_password --ignore-table=db_name.table_name > dump.sql
If you want to skip multiple tables:
#!/bin/bash
PASSWORD=your_password
HOST=host_name
USER=user_name
DATABASE=db_name
DB_FILE=dump.sql
EXCLUDED_TABLES=(
table_name1
table_name2
table_name3
)
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} --no-create-info ${IGNORED_TABLES_STRING} >> ${DB_FILE}
If you want to import the schema and skip its content:
mysqldump -h host_name -u user_name -p schema_name --no-data > db-structure.sql
mysqldump -h host_name -u user_name -p schema_name --no-create-info --ignore-table=schema_name.table_name --ignore-table=schema_name.table_name2 > db-data.sql
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
