How to skip certain database tables with mysqldump?

by

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}

More Related Contents:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. Error Code: 2013. Lost connection to MySQL server during query
  3. MySQL Trigger after update only if row has changed
  4. difference between primary key and unique key
  5. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  6. MySQL Query to select data from last week?
  7. Using an Alias in SQL Calculations
  8. Can a table field contain a hyphen?
  9. Adding a leading zero to some values in column in MySQL
  10. How do I add a custom CHECK constraint on a MySQL table?
  11. View’s SELECT contains a subquery in the FROM clause
  12. Check for x consecutive days – given timestamps in database
  13. MySQL Insert Into from one Database in another
  14. How do I get the ID of multiple inserted rows in MySQL?
  15. How to insert data to MySQL with auto-incremented column(field)?
  16. Case statement in MySQL
  17. Replace Into Query Syntax
  18. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  19. Laravel join with 3 Tables
  20. Difference between two table structure
  21. MySQL query, MAX() + GROUP BY
  22. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  23. How to insert a file in MySQL database?
  24. How to get multiple records against one record based on relation?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Foreign key constraints: When to use ON UPDATE and ON DELETE
  27. How to use a user variables in MySQL LIKE clause?
  28. How to speed up sql queries ? Indexes?
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment