MySQL 如何将 mysqldump 的输出拆分为较小的文件？

Question

提问by lindelof

I need to move entire tables from one MySQL database to another. I don't have full access to the second one, only phpMyAdmin access. I can only upload (compressed) sql files smaller than 2MB. But the compressed output from a mysqldump of the first database's tables is larger than 10MB.

我需要将整个表从一个 MySQL 数据库移动到另一个。我没有对第二个的完全访问权限，只有 phpMyAdmin 访问权限。我只能上传（压缩）小于 2MB 的 sql 文件。但是第一个数据库表的 mysqldump 的压缩输出大于 10MB。

Is there a way to split the output from mysqldump into smaller files? I cannot use split(1) since I cannot cat(1) the files back on the remote server.

有没有办法将 mysqldump 的输出拆分成更小的文件？我无法使用 split(1)，因为我无法将文件 cat(1) 放回到远程服务器上。

Or is there another solution I have missed?

还是我错过了另一个解决方案？

Edit

编辑

The --extended-insert=FALSE option to mysqldump suggested by the first poster yields a .sql file that can then be split into importable files, provided that split(1) is called with a suitable --lines option. By trial and error I found that bzip2 compresses the .sql files by a factor of 20, so I needed to figure out how many lines of sql code correspond roughly to 40MB.

第一个发布者建议的 mysqldump 的 --extended-insert=FALSE 选项会生成一个 .sql 文件，然后可以将其拆分为可导入的文件，前提是使用合适的 --lines 选项调用 split(1) 。通过反复试验，我发现 bzip2 将 .sql 文件压缩了 20 倍，因此我需要计算大约 40MB 对应的 sql 代码行数。

Answer 1

采纳答案by Vinko Vrsalovic

First dump the schema (it surely fits in 2Mb, no?)

首先转储模式（它肯定适合 2Mb，不是吗？）

mysqldump -d --all-databases

and restore it.

并恢复它。

Afterwards dump only the data in separate insert statements, so you can split the files and restore them without having to concatenate them on the remote server

之后只转储单独插入语句中的数据，因此您可以拆分文件并恢复它们，而无需在远程服务器上连接它们

mysqldump --all-databases --extended-insert=FALSE --no-create-info=TRUE

Answer 2

回答by rubo77

This bash script splits a dumpfile of one database into separate files for each table and names with csplitand names them accordingly:

这个 bash 脚本将一个数据库的转储文件拆分为每个表的单独文件，并使用csplit命名并相应地命名它们：

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
# based on https://gist.github.com/jasny/1608062
####

#adjust this to your case:
START="/-- Table structure for table/"
# or 
#START="/DROP TABLE IF EXISTS/"


if [ $# -lt 1 ] || [[  == "--help" ]] || [[  == "-h" ]] ; then
        echo "USAGE: extract all tables:"
        echo " for T in `mysql -N -B -e 'show tables from dbname'`; \
   do echo $T; \
   mysqldump [connecting_options] dbname $T \
   | gzip -c > dbname_$T.dump.gz ; \
   done DUMP_FILE"
        echo "extract one table:"
        echo " mysqldump [connecting options] --tab=directory_name dbname  DUMP_FILE [TABLE]"
        exit
fi

if [ $# -ge 2 ] ; then
        #extract one table 
        csplit -s -ftable  "/-- Table structure for table/" "%-- Table structure for table \`\`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1"
else
        #extract all tables
        csplit -s -ftable  "$START" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
        mv $FILE foot
else
        csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
        mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
        NAME=`head -n1 $FILE | cut -d$'\x60' -f2`
        cat head $FILE foot > "$NAME.sql"
done

rm head foot table*

based on https://gist.github.com/jasny/1608062
and https://stackoverflow.com/a/16840625/1069083

基于https://gist.github.com/jasny/1608062
和https://stackoverflow.com/a/16840625/1069083

Answer 3

回答by Giuseppe Maxia

You say that you don't have access to the second server. But if you have shell access to the first server, where the tables are, you can split your dump by table:

您说您无权访问第二台服务器。但是，如果您对表所在的第一台服务器具有 shell 访问权限，则可以按表拆分转储：

for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done

This will create a gzip file for each table.

这将为每个表创建一个 gzip 文件。

Another way of splitting the output of mysqldump in separate files is using the --tab option.

将 mysqldump 的输出拆分到单独文件中的另一种方法是使用 --tab 选项。

##代码##

where directory_nameis the name of an empty directory. This command creates a .sql file for each table, containing the CREATE TABLE statement, and a .txt file, containing the data, to be restored using LOAD DATA INFILE. I am not sure if phpMyAdmin can handle these files with your particular restriction, though.

其中directory_name是空目录的名称。此命令为每个表创建一个包含 CREATE TABLE 语句的 .sql 文件和一个包含要使用 LOAD DATA INFILE 恢复的数据的 .txt 文件。不过，我不确定 phpMyAdmin 是否可以在您的特定限制下处理这些文件。

Answer 4

回答by mysql_user

There is this excellent mysqldumpsplitterscript which comes with tons of option for when it comes to extracting-from-mysqldump.

有这个优秀的mysqldumpsplitter脚本，它带有大量的选项，用于从 mysqldump 中提取。

I would copy the recipe here to choose your case from:

我会复制这里的食谱来选择你的案例：

1) Extract single database from mysqldump:
sh mysqldumpsplitter.sh --source filename --extract DB --match_str database-name
Above command will create sql for specified database from specified "filename" sql file and store it in compressed format to database-name.sql.gz.
2) Extract single table from mysqldump:
sh mysqldumpsplitter.sh --source filename --extract TABLE --match_str table-name
Above command will create sql for specified table from specified "filename" mysqldump file and store it in compressed format to database-name.sql.gz.
3) Extract tables matching regular expression from mysqldump:
sh mysqldumpsplitter.sh --source filename --extract REGEXP --match_str regular-expression
Above command will create sqls for tables matching specified regular expression from specified "filename" mysqldump file and store it in compressed format to individual table-name.sql.gz.
4) Extract all databases from mysqldump:
sh mysqldumpsplitter.sh --source filename --extract ALLDBS
Above command will extract all databases from specified "filename" mysqldump file and store it in compressed format to individual database-name.sql.gz.
5) Extract all table from mysqldump:
sh mysqldumpsplitter.sh --source filename --extract ALLTABLES
Above command will extract all tables from specified "filename" mysqldump file and store it in compressed format to individual table-name.sql.gz.
6) Extract list of tables from mysqldump:
sh mysqldumpsplitter.sh --source filename --extract REGEXP --match_str '(table1|table2|table3)'
Above command will extract tables from the specified "filename" mysqldump file and store them in compressed format to individual table-name.sql.gz.
7) Extract a database from compressed mysqldump:
sh mysqldumpsplitter.sh --source filename.sql.gz --extract DB --match_str 'dbname' --decompression gzip
Above command will decompress filename.sql.gz using gzip, extract database named "dbname" from "filename.sql.gz" & store it as out/dbname.sql.gz
8) Extract a database from compressed mysqldump in an uncompressed format:
sh mysqldumpsplitter.sh --source filename.sql.gz --extract DB --match_str 'dbname' --decompression gzip --compression none
Above command will decompress filename.sql.gz using gzip and extract database named "dbname" from "filename.sql.gz" & store it as plain sql out/dbname.sql
9) Extract alltables from mysqldump in different folder:
sh mysqldumpsplitter.sh --source filename --extract ALLTABLES --output_dir /path/to/extracts/
Above command will extract all tables from specified "filename" mysqldump file and extracts tables in compressed format to individual files, table-name.sql.gz stored under /path/to/extracts/. The script will create the folder /path/to/extracts/ if not exists.
10) Extract one or more tables from one database in a full-dump:
Consider you have a full dump with multiple databases and you want to extract few tables from one database.
Extract single database: sh mysqldumpsplitter.sh --source filename --extract DB --match_str DBNAME --compression none
Extract all tables sh mysqldumpsplitter.sh --source out/DBNAME.sql --extract REGEXP --match_str "(tbl1|tbl2)"though we can use another option to do this in single command as follows:
sh mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str "DBNAME.(tbl1|tbl2)" --compression none
Above command will extract both tbl1 and tbl2 from DBNAME database in sql format under folder "out" in current directory.
You can extract single table as follows:
sh mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str "DBNAME.(tbl1)" --compression none
11) Extract all tables from specific database:
mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str "DBNAME.*" --compression none
Above command will extract all tables from DBNAME database in sql format and store it under "out" directory.
12) List content of the mysqldump file
mysqldumpsplitter.sh --source filename --desc
Above command will list databases and tables from the dump file.

1）从mysqldump中提取单个数据库：
sh mysqldumpsplitter.sh --source filename --extract DB --match_str database-name
上面的命令将从指定的“filename”sql 文件为指定的数据库创建 sql 并将其以压缩格式存储到 database-name.sql.gz。
2）从mysqldump中提取单表：
sh mysqldumpsplitter.sh --source filename --extract TABLE --match_str table-name
上面的命令将从指定的“文件名”mysqldump 文件中为指定表创建 sql，并将其以压缩格式存储到 database-name.sql.gz。
3）从mysqldump中提取匹配正则表达式的表：
sh mysqldumpsplitter.sh --source filename --extract REGEXP --match_str regular-expression
上面的命令将从指定的“文件名”mysqldump 文件中为匹配指定正则表达式的表创建 sql，并将其以压缩格式存储到单个 table-name.sql.gz。
4）从mysqldump中提取所有数据库：
sh mysqldumpsplitter.sh --source filename --extract ALLDBS
以上命令将从指定的“文件名”mysqldump 文件中提取所有数据库，并将其以压缩格式存储到单独的 database-name.sql.gz。
5）从mysqldump中提取所有表：
sh mysqldumpsplitter.sh --source filename --extract ALLTABLES
以上命令将从指定的“文件名”mysqldump 文件中提取所有表，并将其以压缩格式存储到单个 table-name.sql.gz。
6) 从 mysqldump 中提取表列表：
sh mysqldumpsplitter.sh --source filename --extract REGEXP --match_str '(table1|table2|table3)'
以上命令将从指定的“文件名”mysqldump 文件中提取表，并将它们以压缩格式存储到单独的 table-name.sql.gz。
7) 从压缩的 mysqldump 中提取数据库：
sh mysqldumpsplitter.sh --source filename.sql.gz --extract DB --match_str 'dbname' --decompression gzip
上面的命令将使用 gzip 解压 filename.sql.gz，从“filename.sql.gz”中提取名为“dbname”的数据库并将其存储为 out/dbname.sql.gz
8) 以未压缩格式从压缩的 mysqldump 中提取数据库：
sh mysqldumpsplitter.sh --source filename.sql.gz --extract DB --match_str 'dbname' --decompression gzip --compression none
上面的命令将使用 gzip 解压缩 filename.sql.gz 并从“filename.sql.gz”中提取名为“dbname”的数据库并将其存储为普通的 sql out/dbname.sql
9) 从不同文件夹中的 mysqldump 中提取 alltables：
sh mysqldumpsplitter.sh --source filename --extract ALLTABLES --output_dir /path/to/extracts/
以上命令将从指定的“文件名”mysqldump 文件中提取所有表，并将压缩格式的表提取到单个文件中，table-name.sql.gz 存储在 /path/to/extracts/ 下。该脚本将创建文件夹 /path/to/extracts/ 如果不存在。
10）从一个完整转储中的一个数据库中提取一个或多个表：
假设您有一个包含多个数据库的完整转储，并且您想从一个数据库中提取几个表。
提取单个数据库： sh mysqldumpsplitter.sh --source filename --extract DB --match_str DBNAME --compression none
提取所有表，sh mysqldumpsplitter.sh --source out/DBNAME.sql --extract REGEXP --match_str "(tbl1|tbl2)"但我们可以使用另一个选项在单个命令中执行此操作，如下所示：
sh mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str "DBNAME.(tbl1|tbl2)" --compression none
上面的命令将在当前目录中的文件夹“out”下以 sql 格式从 DBNAME 数据库中提取 tbl1 和 tbl2。
您可以按如下方式提取单个表：
sh mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str "DBNAME.(tbl1)" --compression none
11）从特定数据库中提取所有表：
mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str "DBNAME.*" --compression none
以上命令将以sql格式从DBNAME数据库中提取所有表并将其存储在“out”目录下。
12) 列出mysqldump文件的内容
mysqldumpsplitter.sh --source filename --desc
上面的命令将列出转储文件中的数据库和表。

You may later choose to load the files: zcat filename.sql.gz | mysql -uUSER -p -hHOSTNAME

您可以稍后选择加载文件： zcat filename.sql.gz | mysql -uUSER -p -hHOSTNAME

Also once you extract single table which you think is still bigger, you can use linux split command with number of lines to further split the dump. split -l 10000 filename.sql
That said, if that is your need (coming more often), you might consider using mydumperwhich actually creates individual dumps you wont need to split!

此外，一旦您提取了您认为仍然更大的单个表，您可以使用带有行数的 linux split 命令来进一步拆分转储。 split -l 10000 filename.sql
也就是说，如果这是您的需要（更频繁地出现），您可以考虑使用mydumper，它实际上会创建您不需要拆分的单独转储！

Answer 5

回答by Lee Haskings

Late reply but was looking for same solution and came across following code from below website:

迟到的回复，但正在寻找相同的解决方案，并从以下网站发现以下代码：

##代码##

http://www.commandlinefu.com/commands/view/2916/backup-all-mysql-databases-to-individual-files

Answer 6

回答by vbarbarosh

I've recently created sqlsplit.com. Try it out.

我最近创建了sqlsplit.com。试试看。

Answer 7

回答by Philip

I wrote a new version of the SQLDumpSplitter, this time with a proper parser, allowing nice things like INSERTs with many values to be split over files and it is multi platform now: https://philiplb.de/sqldumpsplitter3/

我写了一个新版本的 SQLDumpSplitter，这次使用了一个合适的解析器，允许像 INSERTs 这样的好东西在文件上分割，它现在是多平台的：https: //philiplb.de/sqldumpsplitter3/

Answer 8

回答by Philip

You don't need ssh access to either of your servers. Just a mysql[dump] client is fine. With the mysql[dump], you can dump your database and import it again.

您不需要对任一服务器进行 ssh 访问。只需要一个 mysql[dump] 客户端就可以了。使用 mysql[dump]，您可以转储数据库并再次导入。

In your PC, you can do something like:

在您的 PC 中，您可以执行以下操作：

$ mysqldump -u originaluser -poriginalpassword -h originalhost originaldatabase | mysql -u newuser -pnewpassword -h newhost newdatabase

and you're done. :-)

你就完成了。:-)

hope this helps

希望这可以帮助

Answer 9

回答by LittleT15

i would recommend the utility bigdump, you can grab it here. http://www.ozerov.de/bigdump.phpthis staggers the execution of the dump, in as close as it can manage to your limit, executing whole lines at a time.

我会推荐实用程序 bigdump，你可以在这里获取它。http://www.ozerov.de/bigdump.php这会错开转储的执行，尽可能接近您的极限，一次执行整行。

Answer 10

回答by skoob

You can dump individual tables with mysqldump by running mysqldump database table1 table2 ... tableN

您可以通过运行 mysqldump 转储单个表 mysqldump database table1 table2 ... tableN

If none of the tables are too large, that will be enough. Otherwise, you'll have to start splitting the data in the larger tables.

如果没有一个表太大，那就足够了。否则，您将不得不开始拆分较大表中的数据。

MySQL 如何将 mysqldump 的输出拆分为较小的文件？

提问by lindelof

采纳答案by Vinko Vrsalovic

回答by rubo77

回答by Giuseppe Maxia

回答by mysql_user

回答by Lee Haskings

回答by vbarbarosh

回答by Philip

回答by Philip

回答by LittleT15

回答by skoob

相关推荐

最近更新

标签

MySQL 如何将 mysqldump 的输出拆分为较小的文件？

提问by lindelof

采纳答案by Vinko Vrsalovic

回答by rubo77

回答by Giuseppe Maxia

回答by mysql_user

回答by Lee Haskings

回答by vbarbarosh

回答by Philip

回答by Philip

回答by LittleT15

回答by skoob

相关推荐

SQL 查询长度的实际限制（特别是 MySQL）

MySQL 如何从mysql表中删除唯一键

MySQL 如何从mysql函数中的字母数字文本中修剪前导零

MySQL 如何在 CodeIgniter 中显示数据库中的图像？

相关推荐

最近更新

标签