Skip to content

Instantly share code, notes, and snippets.

@githubnando
Last active July 17, 2017 13:49
Show Gist options
  • Save githubnando/a4f3acaad10fed7bdd048f65d70a0bc1 to your computer and use it in GitHub Desktop.
Save githubnando/a4f3acaad10fed7bdd048f65d70a0bc1 to your computer and use it in GitHub Desktop.
mysqldump-ignoring-multiple-tables
#!/bin/bash
DB_HOST=""
DB_DATABASE=""
DB_USERNAME=""
DB_PASSWORD=""
read -r -d '' tableSizesQuery << EOF
SELECT
table_schema as 'Database',
table_name AS 'Table',
round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB'
FROM information_schema.TABLES
WHERE table_schema = '${DB_DATABASE}'
ORDER BY (data_length + index_length) DESC;
EOF
# echo $tableSizesQuery;
# exit;
EXCLUDED_TABLES=(
table1
table2
)
IGNORED='';
for TABLE in "${EXCLUDED_TABLES[@]}"; do
IGNORED+=" --ignore-table=${DB_DATABASE}.${TABLE}"
done
# if it's not innoDB, use --lock-tables=false instead of --single-transaction
mysqldump --host=${DB_HOST} --user=${DB_USERNAME} --password=${DB_PASSWORD} ${DB_DATABASE} ${IGNORED} --single-transaction=true -v > dump.sql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment