将一个大的MySQL表导出为多个较小的文件

Question

我在本地开发服务器上有一个非常大的MySQL表：超过800万行数据。我使用LOAD DATA INFILE成功地加载了该表。

现在我想将这些数据导出并导入到远程主机上。

我尝试使用LOAD DATA LOCAL INFILE到远程主机。然而，大约15分钟后与远程主机的连接失败。我认为唯一的解决方案是将数据导出为多个较小的文件。

我可以使用PhpMyAdmin、HeidiSQL和MySQL Workbench等工具。

我知道如何导出单个文件，但不知道如何导出多个文件。我该怎么做？

- Billy

---

你可以尝试使用mysqldump仅导出所需的表，并通过导入重建它。 - Asad Saeeduddin

你能从本地机器连接到mysql服务器吗？ - Michael

@Michael - 当然可以。我一直在使用HeidiSQL进行数据库维护。 - Billy

然后只需将文件本地转储 - 分割文件，然后上传增量。 - Michael

Answer 1

我刚刚对一个拥有5000万条记录的（分区）表进行了导入/导出操作，它只需要在一台相对较快的计算机上花费2分钟就能够完成导出，而在我的较慢的桌面电脑上花费15分钟就能够完成导入。没有必要分割文件。

mysqldump是你的好帮手，并且如果你知道有很多数据，最好将其压缩。

 @host1:~ $ mysqldump -u <username> -p <database> <table> | gzip > output.sql.gz
 @host1:~ $ scp output.sql.gz host2:~/
 @host1:~ $ rm output.sql.gz
 @host1:~ $ ssh host2
 @host2:~ $ gunzip < output.sql.gz | mysql -u <username> -p <database>
 @host2:~ $ rm output.sql.gz

- Riccardo Galli

Answer 2

请看mysqldump，以下是命令行文本：

从你的mysql中的db_name导出到backupfile.sql文件：

mysqldump -u user -p db_name > backupfile.sql

从备份文件导入到你的MySQL数据库：

mysql -u user -p db_name < backupfile.sql

您有两个选项来分割信息：

1. 将输出文本文件分割成较小的文件（根据需要使用许多工具，例如split）。

2. 每次使用在db_name后添加表名的选项导出一个表，如下所示：

   mysqldump -u user -p db_name table_name > backupfile_table_name.sql

压缩文件（文本文件）非常高效，可以将其最小化为原始大小的20％-30％。

将文件复制到远程服务器应该使用scp（安全复制），并且交互应该使用ssh（通常为此目的）。

祝你好运。

- Reut Sharabani

---

"将输出文本文件分割成更小的文件（根据需要，有许多工具可用）" - 什么工具？这是问题：我该如何做？" - Billy

看一下编辑。添加Unix的“split”作为示例，应该在大多数系统上都可用。 - Reut Sharabani

Answer 3

我发现phpMyAdmin中的高级选项允许我选择导出多少行以及起始点。这使我能够创建任意数量的转储文件以将表格传输到远程主机。

由于生成每个文件需要一些时间（每个文件包含500,000行），因此我不得不调整我的php.ini设置以及phpMyAdmin配置的“ExecTimeLimit”设置。

我使用HeidiSQL进行导入。

- Billy

Answer 4

作为单个表的mysqldump方法的示例

mysqldump -u root -ppassword yourdb yourtable > table_name.sql

导入非常简单，只需执行以下操作：

mysql -u username -ppassword yourotherdb < table_name.sql

- Asad Saeeduddin

---

我发现这对于大规模的导入/导出特别快。对于楼主来说是个好建议。 - CodeTalk

是的，那很好，但我没有服务器的SSH访问权限。这并没有回答实际问题。 - Billy

@Billy 如果你有phpmyadmin访问权限，你仍然可以在转储文件上运行phpmyadmin导入。你可以选择进行部分导入，然后通过指定要跳过的查询数量作为成功发生的数量来继续。 - Asad Saeeduddin

Answer 5

如果您不喜欢使用mysqldump命令行工具，这里有两个GUI工具可以帮助您解决这个问题，尽管您必须能够通过FTP将它们上传到服务器！ Adminer是一个精简而高效的数据库管理工具，至少和PHPMyAdmin一样强大，并且只需要上传一个单一文件到服务器，非常容易安装。它在处理大型表/数据库时比PMA更有效。 MySQLDumper是一款专门开发用于导出/导入大型表/数据库的工具，因此它不会对您描述的情况产生任何问题。唯一的缺点是安装有点麻烦，因为有更多的文件和文件夹（约350个文件，大小约为1.5MB），但是通过FTP上传也不应该成为问题，它肯定能完成任务 :)

因此，我的建议是首先尝试Adminer，如果那个也失败了，请使用MySQLDumper路线。

- Larzan

Answer 6

使用mysqldump将表导出到文件中。 然后使用带有-z选项的tar压缩文件。 使用ftp、sftp或其他文件传输工具将其传输到远程服务器。 然后在远程服务器上解压文件。 使用mysql导入文件。

没有必要拆分原始文件或将其导出为多个文件。

- p.matsinopoulos

---

@Vyktor 请您再详细解释一下您的问题，我可能会缺少一些信息。我的第一个快速答案是，我不期望出现任何查询超时情况。 "mysql" 命令将在本地机器上处理 "xxx.sql" 文件（即导出结果），并进行导入。 "xxx.sql" 文件包含要导入的表的 "insert" 命令（大约有800万个这样的命令/语句，即与手头表的行数相等）。我没有看到任何原因导致这些 "insert" 命令由于任何超时而失败。 - p.matsinopoulos

这是一个共享主机 - 我无法通过命令行访问MySQL。 - Billy

问题是：如何拆分数据……你认为我应该这样做吗？对我来说，这显然是最简单的解决方案，因为我可以通过 HeidiSQL 轻松导入较小的文件。 - Billy

Answer 7

如何将大型MySQL备份文件拆分成多个文件？

您可以使用mysql_export_explode https://github.com/barinascode/mysql-export-explode

<?php 
#Including the class

include 'mysql_export_explode.php';
$export = new mysql_export_explode;

$export->db = 'dataBaseName'; # -- Set your database name
$export->connect('host','user','password'); # -- Connecting to database
$export->rows = array('Id','firstName','Telephone','Address'); # -- Set which fields you want to export
$export->exportTable('myTableName',15); # -- Table name and in few fractions you want to split the table
?>

At the end of the SQL files are created in the directory where the script is executed in the following format
---------------------------------------
myTableName_0.sql
myTableName_1.sql
myTableName_2.sql
...

- Leonardo Alfonso Tapia Delgado