MySQL 使用 mysqldump 跳过某些表

Skip certain tables with mysqldump

提问by Zac

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

有没有办法从 mysqldump 命令中限制某些表？

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

例如，我将使用以下语法仅转储table1 和 table2：

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

But is there a similar way to dump all the tables excepttable1 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?

但是有没有类似的方法来转储除table1 和 table2之外的所有表？我在 mysqldump 文档中没有找到任何东西，所以暴力（指定所有表名）是唯一的方法吗？

回答by Brian Fisher

You can use the --ignore-tableoption. So you could do

您可以使用--ignore-table选项。所以你可以这样做

mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table=DATABASE.table1 > database.sql

There is no whitespace after -p(this is not a typo).

后面没有空格-p（这不是打字错误）。

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 --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}

回答by Dawngerpony

Building on the answer from @Brian-Fisher and answering the comments of some of the people on this post, I have a bunch of huge (and unnecessary) tables in my database so I wanted to skip their contents when copying, but keep the structure:

基于@Brian-Fisher 的回答并回答了这篇文章中一些人的评论，我的数据库中有一堆巨大的（且不必要的）表，所以我想在复制时跳过它们的内容，但保留结构：

mysqldump -h <host> -u <username> -p <schema> --no-data > db-structure.sql
mysqldump -h <host> -u <username> -p <schema> --no-create-info --ignore-table=schema.table1 --ignore-table=schema.table2 > db-data.sql

The resulting two files are structurally sound but the dumped data is now ~500MB rather than 9GB, much better for me. I can now import these two files into another database for testing purposes without having to worry about manipulating 9GB of data or running out of disk space.

生成的两个文件在结构上是合理的，但转储的数据现在约为 500MB 而不是 9GB，对我来说好多了。我现在可以将这两个文件导入另一个数据库进行测试，而不必担心处理 9GB 的数据或磁盘空间不足。

回答by Alex

for multiple databases:

对于多个数据库：

mysqldump -u user -p --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 --databases db1 db2 ..

回答by DarckBlezzer

Another example for ignoring multiple tables

另一个忽略多个表的例子

/usr/bin/mysqldump -uUSER -pPASS --ignore-table={db_test.test1,db_test.test3} db_test> db_test.sql

using --ignore-tableand create an array of tables, with syntaxs like database.table

使用--ignore-table和创建一个表数组，语法如下database.table

--ignore-table={db_test.table1,db_test.table3,db_test.table4}

Links with information that will help you

包含对您有帮助的信息的链接

compress output mysqldump

压缩输出mysqldump

• mysqldump & gzip
• mysqldump to a tar.gz

• mysqldump 和 gzip
• mysqldump 到 tar.gz

Note: tested in ubuntu server with mysql Ver 14.14 Distrib 5.5.55

注意：在 ubuntu 服务器中测试，mysql Ver 14.14 Distrib 5.5.55

Import database

导入数据库

 mysql -uUSER  -pPASS db_test < db_test.sql

Simple script to ignore tables

忽略表格的简单脚本

#!/bin/bash

#tables to ignore
_TIGNORE=(
my_database.table1
my_database.table2
my_database.tablex
)

#create text for ignore tables
_TDELIMITED="$(IFS=" "; echo "${_TIGNORE[*]/#/--ignore-table=}")"

#don't forget to include user and password
/usr/bin/mysqldump -uUSER -pPASSWORD --events ${_TDELIMITED} --databases my_database | gzip -v > backup_database.sql.gz

回答by Benedikt K?ppel

To exclude some table data, but not the table structure. Here is how I do it:

排除一些表数据，而不是表结构。这是我如何做到的：

Dump the database structure of all tables, without any data:

转储所有表的数据库结构，没有任何数据：

mysqldump -u user -p --no-data database > database_structure.sql

Then dump the database with data, except the excluded tables, and do not dump the structure:

然后用数据转储数据库，排除的表除外，不要转储结构：

mysqldump -u user -p --no-create-info \
    --ignore-table=database.table1 \
    --ignore-table=database.table2 database > database_data.sql

Then, to load it into a new database:

然后，将其加载到新数据库中：

mysql -u user -p newdatabase < database_structure.sql
mysql -u user -p newdatabase < database_data.sql

回答by ThorstenC

You can use the mysqlpumpcommand with the

您可以将mysqlpump命令与

--exclude-tables=name

command. It specifies a comma-separated list of tables to exclude.

命令。它指定要排除的表的逗号分隔列表。

Syntax of mysqlpump is very similar to mysqldump, buts its way more performant. More information of how to use the exclude option you can read here: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#mysqlpump-filtering

mysqlpump 的语法与 mysqldump 非常相似，但其性能更高。有关如何使用排除选项的更多信息，您可以在此处阅读：https: //dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#mysqlpump-filtering

回答by hpaknia

For sake of completeness, here is a script which actually could be a one-liner to get a backup from a database, excluding (ignoring) all the views. The db name is assumed to be employees:

为了完整起见，这里有一个脚本，它实际上可以是从数据库中获取备份的单行脚本，不包括（忽略）所有视图。数据库名称假定为员工：

ignore=$(mysql --login-path=root1 INFORMATION_SCHEMA \
    --skip-column-names --batch \
    -e "select 
          group_concat(
            concat('--ignore-table=', table_schema, '.', table_name) SEPARATOR ' '
          ) 
        from tables 
        where table_type = 'VIEW' and table_schema = 'employees'")

mysqldump --login-path=root1 --column-statistics=0 --no-data employees $ignore > "./backups/som_file.sql"   

You can update the logic of the query. In general using group_concatand concatyou can generate almost any desired string or shell command.

您可以更新查询的逻辑。通常group_concat，concat您可以使用and生成几乎任何所需的字符串或 shell 命令。

回答by Alan

I like Rubo77's solution, I hadn't seen it before I modified Paul's. This one will backup a single database, excluding any tables you don't want. It will then gzip it, and delete any files over 8 days old. I will probably use 2 versions of this that do a full (minus logs table) once a day, and another that just backs up the most important tables that change the most every hour using a couple cron jobs.

我喜欢Rubo77的解决方案，我在修改Paul的之前没见过。这将备份单个数据库，不包括您不想要的任何表。然后它将对其进行 gzip，并删除超过 8 天的所有文件。我可能会使用 2 个版本，每天执行一次完整（减去日志表），另一个版本仅备份最重要的表，这些表使用几个 cron 作业每小时更改最多。

#!/bin/sh
PASSWORD=XXXX
HOST=127.0.0.1
USER=root
DATABASE=MyFavoriteDB

now="$(date +'%d_%m_%Y_%H_%M')"
filename="${DATABASE}_db_backup_$now"
backupfolder="/opt/backups/mysql"
DB_FILE="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt

EXCLUDED_TABLES=(
logs
)
IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE}  > ${DB_FILE} 
echo "Dump structure finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}
gzip ${DB_FILE}

find "$backupfolder" -name ${DATABASE}_db_backup_* -mtime +8 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

回答by rubo77

Dump all databases with all tables but skip certain tables

转储具有所有表的所有数据库但跳过某些表

on github: https://github.com/rubo77/mysql-backup.sh/blob/master/mysql-backup.sh

在 github 上：https: //github.com/rubo77/mysql-backup.sh/blob/master/mysql-backup.sh

#!/bin/bash
# mysql-backup.sh

if [ -z "" ] ; then
  echo
  echo "ERROR: root password Parameter missing."
  exit
fi
DB_host=localhost
MYSQL_USER=root
MYSQL_PASS=
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#MYSQL_CONN=""

BACKUP_DIR=/backup/mysql/

mkdir $BACKUP_DIR -p

MYSQLPATH=/var/lib/mysql/

IGNORE="database1.table1, database1.table2, database2.table1,"

# strpos   []
# strpos haystack needle [optional offset of an input string]
strpos()
{
    local str=
    local offset=
    if [ -n "${offset}" ]; then
        str=`substr "${str}" ${offset}`
    else
        offset=0
    fi
    str=${str/*/}
    if [ "${#str}" -eq "${#1}" ]; then
        return 0
    fi
    echo $((${#str}+${offset}))
}

cd $MYSQLPATH
for i in */; do
    if [ $i != 'performance_schema/' ] ; then 
    DB=`basename "$i"` 
    #echo "backup $DB->$BACKUP_DIR$DB.sql.lzo"
    mysqlcheck "$DB" $MYSQL_CONN --silent --auto-repair >/tmp/tmp_grep_mysql-backup
    grep -E -B1 "note|warning|support|auto_increment|required|locks" /tmp/tmp_grep_mysql-backup>/tmp/tmp_grep_mysql-backup_not
    grep -v "$(cat /tmp/tmp_grep_mysql-backup_not)" /tmp/tmp_grep_mysql-backup

    tbl_count=0
    for t in $(mysql -NBA -h $DB_host $MYSQL_CONN -D $DB -e 'show tables') 
    do
      found=$(strpos "$IGNORE" "$DB"."$t,")
      if [ "$found" == "" ] ; then 
        echo "DUMPING TABLE: $DB.$t"
        mysqldump -h $DB_host $MYSQL_CONN $DB $t --events --skip-lock-tables | lzop -3 -f -o $BACKUP_DIR/$DB.$t.sql.lzo
        tbl_count=$(( tbl_count + 1 ))
      fi
    done
    echo "$tbl_count tables dumped from database '$DB' into dir=$BACKUP_DIR"
    fi
done

With a little help of https://stackoverflow.com/a/17016410/1069083

在https://stackoverflow.com/a/17016410/1069083 的帮助下

It uses lzop which is much faster, see:http://pokecraft.first-world.info/wiki/Quick_Benchmark:_Gzip_vs_Bzip2_vs_LZMA_vs_XZ_vs_LZ4_vs_LZO

它使用更快的 lzop，请参阅：http: //pokecraft.first-world.info/wiki/Quick_Benchmark: _Gzip_vs_Bzip2_vs_LZMA_vs_XZ_vs_LZ4_vs_LZO