mysql 跳过已有的表,使用mysqldump跳过某些表

Is there a way to restrict certain tables from the mysqldump command?

For example, I'd use the following syntax to dump only table1 and table2:

mysqldump -u username -p database table1 table2 > database.sql

But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentation, so is brute-force (specifying all the table names) the only way to go?

解决方案

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

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} --no-create-info ${IGNORED_TABLES_STRING} >> ${DB_FILE}