如何将大型 (14 GB) MySQL 转储文件导入新的 MySQL 数据库？

How can I import a large (14 GB) MySQL dump file into a new MySQL database?

提问by TRN 7

How can I import a large (14 GB) MySQL dump file into a new MySQL database?

如何将大型 (14 GB) MySQL 转储文件导入新的 MySQL 数据库？

回答by Kresimir Plese

I've searched around, and only this solution helped me:

我四处搜索，只有这个解决方案对我有帮助：

mysql -u root -p

set global net_buffer_length=1000000; --Set network buffer length to a large byte number

set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number

SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour

source file.sql --Import your sql dump file

SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!

The answer is found here.

答案可以在这里找到。

回答by Brian Campbell

Have you tried just using the mysqlcommand line client directly?

您是否尝试过直接使用mysql命令行客户端？

mysql -u username -p -h hostname databasename < dump.sql

If you can't do that, there are any number of utilities you can find by Googling that help you import a large dump into MySQL, like BigDump

如果你不能这样做，你可以通过谷歌搜索找到许多实用程序来帮助你将大型转储导入 MySQL，比如BigDump

回答by Bill Ryder

I'm posting my finding in a few of the responses I've seen that didn't mention what I ran into, and apprently this would even defeat BigDump, so check it:

我在一些我看到的回复中发布了我的发现，这些回复没有提到我遇到的问题，而且显然这甚至会打败 BigDump，所以请检查一下：

I was trying to load a 500 meg dump via Linux command line and kept getting the "Mysql server has gone away" errors. Settings in my.conf didn't help. What turned out to fix it is...I was doing one big extended insert like:

我试图通过 Linux 命令行加载 500 meg 转储并不断收到“Mysql 服务器已消失”错误。my.conf 中的设置没有帮助。结果修复它的是......我正在做一个大的扩展插入，如：

    insert into table (fields) values (a record, a record, a record, 500 meg of data);

I needed to format the file as separate inserts like this:

我需要将文件格式化为单独的插入内容，如下所示：

    insert into table (fields) values (a record);
    insert into table (fields) values (a record);
    insert into table (fields) values (a record);
    Etc.

And to generate the dump, I used something like this and it worked like a charm:

为了生成转储，我使用了这样的东西，它就像一个魅力：

    SELECT 
        id,
        status,
        email
    FROM contacts
    INTO OUTFILE '/tmp/contacts.sql'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES STARTING BY "INSERT INTO contacts (id,status,email) values ("
    TERMINATED BY ');\n'

回答by Kisded Szabi - CodeRevolution

On a recent project we had the challenge of working with and manipulating a large collection of data. Our client provided us with a 50 CSV files ranging from 30 MB to 350 MB in size and all in all containing approximately 20 million rows of data and 15 columns of data. Our end goal was to import and manipulate the data into a MySQL relational database to be used to power a front-end PHP script that we also developed. Now, working with a dataset this large or larger is not the simplest of tasks and in working on it we wanted to take a moment to share some of the things you should consider and know when working with large datasets like this.

在最近的一个项目中，我们面临着处理和处理大量数据的挑战。我们的客户向我们提供了 50 个 CSV 文件，大小从 30 MB 到 350 MB 不等，总共包含大约 2000 万行数据和 15 列数据。我们的最终目标是将数据导入并操作到 MySQL 关系数据库中，以用于支持我们开发的前端 PHP 脚本。现在，处理如此大或更大的数据集并不是最简单的任务，在处理它时，我们想花点时间分享一些您在处理此类大型数据集时应该考虑和了解的事情。

1. Analyze Your Dataset Pre-Import

   I can't stress this first step enough! Make sure that you take the time to analyze the data you are working with before importing it at all. Getting an understand of what all of the data represents, what columns related to what and what type of manipulation you need to will end up saving you time in the long run.

2. LOAD DATA INFILE is Your Friend

   Importing large data files like the ones we worked with (and larger ones) can be tough to do if you go ahead and try a regular CSV insert via a tool like PHPMyAdmin. Not only will it fail in many cases because your server won't be able to handle a file upload as large as some of your data files due to upload size restrictions and server timeouts, but even if it does succeed, the process could take hours depending our your hardware. The SQL function LOAD DATA INFILE was created to handle these large datasets and will significantly reduce the time it takes to handle the import process. Of note, this can be executed through PHPMyAdmin, but you may still have file upload issues. In that case you can upload the files manually to your server and then execute from PHPMyAdmin (see their manual for more info) or execute the command via your SSH console (assuming you have your own server)

   LOAD DATA INFILE '/mylargefile.csv' INTO TABLE temp_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
   

3. MYISAM vs InnoDB

   Large or small database it's always good to take a little time to consider which database engine you are going to use for your project. The two main engines you are going to read about are MYISAM and InnoDB and each has their own benefits and drawbacks. In brief the things to consider (in general) are as follows:

   MYISAM

   • Lower Memory Usage
   • Allows for Full-Text Searching
   • Table Level Locking – Locks Entire Table on Write
   • Great for Read-Intensive Applications

   InnoDB

   • List item
   • Uses More Memory
   • No Full-Text Search Support
   • Faster Performance
   • Row Level Locking – Locks Single Row on Write
   • Great for Read/Write Intensive Applications

4. Plan Your Design Carefully

   MySQL AnalyzeYour databases design/structure is going to be a large factor in how it performs. Take your time when it comes to planning out the different fields and analyze the data to figure out what the best field types, defaults and field length. You want to accommodate for the right amounts of data and try to avoid varchar columns and overly large data types when the data doesn't warrant it. As an additional step after you are done with your database, you make want to see what MySQL suggests as field types for all of your different fields. You can do this by executing the following SQL command:

   ANALYZE TABLE my_big_table
   

   The result will be a description of each columns information along with a recommendation for what type of datatype it should be along with a proper length. Now you don't necessarily need to follow the recommendations as they are based solely on existing data, but it may help put you on the right track and get you thinking

5. To Index or Not to Index

   For a dataset as large as this it's infinitely important to create proper indexes on your data based off of what you need to do with the data on the front-end, BUT if you plan to manipulate the data beforehand refrain from placing too many indexes on the data. Not only will it will make your SQL table larger, but it will also slow down certain operations like column additions, subtractions and additional indexing. With our dataset we needed to take the information we just imported and break it into several different tables to create a relational structure as well as take certain columns and split the information into additional columns. We placed an index on the bare minimum of columns that we knew would help us with the manipulation. All in all, we took 1 large table consisting of 20 million rows of data and split its information into 6 different tables with pieces of the main data in them along with newly created data based off the existing content. We did all of this by writing small PHP scripts to parse and move the data around.

6. Finding a Balance

   A big part of working with large databases from a programming perspective is speed and efficiency. Getting all of the data into your database is great, but if the script you write to access the data is slow, what's the point? When working with large datasets it's extremely important that you take the time to understand all of the queries that your script is performing and to create indexes to help those queries where possible. One such way to analyze what your queries are doing is by executing the following SQL command:

   EXPLAIN SELECT some_field FROM my_big_table WHERE another_field='MyCustomField';
   

   By adding EXPLAIN to the start of your query MySQL will spit out information describing what indexes it tried to use, did use and how it used them. I labeled this point ‘Finding a balance' because although indexes can help your script perform faster, they can just as easily make it run slower. You need to make sure you index what is needed and only what is needed. Every index consumes disk space and adds to the overhead of the table. Every time you make an edit to your table, you have to rebuild the index for that particular row and the more indexes you have on those rows, the longer it will take. It all comes down to making smart indexes, efficient SQL queries and most importantly benchmarking as you go to understand what each of your queries is doing and how long it's taking to do it.

7. Index On, Index Off

   As we worked on the database and front-end script, both the client and us started to notice little things that needed changing and that required us to make changes to the database. Some of these changes involved adding/removing columns and changing the column types. As we had already setup a number of indexes on the data, making any of these changes required the server to do some serious work to keep the indexes in place and handle any modifications. On our small VPS server, some of the changes were taking upwards of 6 hours to complete…certainly not helpful to us being able to do speedy development. The solution? Turn off indexes! Sometimes it's better to turn the indexes off, make your changes and then turn the indexes back on….especially if you have a lot of different changes to make. With the indexes off, the changes took a matter of seconds to minutes versus hours. When we were happy with our changes we simply turned our indexes back on. This of course took quite some time to re-index everything, but it was at least able to re-index everything all at once, reducing the overall time needed to make these changes one by one. Here's how to do it:

   • Disable Indexes: ALTER TABLE my_big_table DISABLE KEY
   • Enable Indexes: ALTER TABLE my_big_table ENABLE KEY

8. Give MySQL a Tune-Up

   Don't neglect your server when it comes to making your database and script run quickly. Your hardware needs just as much attention and tuning as your database and script does. In particular it's important to look at your MySQL configuration file to see what changes you can make to better enhance its performance. A great little tool that we've come across is the MySQL Tuner http://mysqltuner.com/. It's a quick little Perl script that you can download right to your server and run via SSH to see what changes you might want to make to your configuration. Note that you should actively use your front-end script and database for several days before running the tuner so that the tuner has data to analyze. Running it on a fresh server will only provide minimal information and tuning options. We found it great to use the tuner script every few days for the two weeks to see what recommendations it would come up with and at the end we had significantly increased the databases performance.

9. Don't be Afraid to Ask

   Working with SQL can be challenging to begin with and working with extremely large datasets only makes it that much harder. Don't be afraid to go to professionals who know what they are doing when it comes to large datasets. Ultimately you will end up with a superior product, quicker development and quicker front-end performance. When it comes to large databases sometimes it's take a professionals experienced eyes to find all the little caveats that could be slowing your databases performance.

1. 分析您的数据集导入前

   我怎么强调这第一步都不为过！确保在导入之前花时间分析正在处理的数据。从长远来看，了解所有数据代表什么，哪些列与您需要的操作类型和操作类型相关，最终将节省您的时间。

2. 加载数据文件是你的朋友

   如果您继续尝试通过 PHPMyAdmin 之类的工具进行常规 CSV 插入，那么导入像我们使用过的（和更大的）这样的大型数据文件可能会很困难。它不仅会在很多情况下失败，因为由于上传大小限制和服务器超时，您的服务器将无法处理与某些数据文件一样大的文件上传，而且即使成功，该过程也可能需要数小时取决于我们您的硬件。创建 SQL 函数 LOAD DATA INFILE 是为了处理这些大型数据集，并将显着减少处理导入过程所需的时间。值得注意的是，这可以通过 PHPMyAdmin 执行，但您可能仍然遇到文件上传问题。

   LOAD DATA INFILE '/mylargefile.csv' INTO TABLE temp_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
   

3. MYISAM 与 InnoDB

   无论是大数据库还是小数据库，花点时间考虑将要用于项目的数据库引擎总是好的。您将要阅读的两个主要引擎是 MYISAM 和 InnoDB，每个引擎都有自己的优点和缺点。简而言之，需要考虑的事项（一般而言）如下：

   马来西亚

   • 降低内存使用率
   • 允许全文搜索
   • 表级锁定——写时锁定整个表
   • 非常适合阅读密集型应用程序

   数据库

   • 项目清单
   • 使用更多内存
   • 没有全文搜索支持
   • 更快的性能
   • 行级锁定——写时锁定单行
   • 非常适合读/写密集型应用程序

4. 仔细规划您的设计

   MySQL 分析您的数据库设计/结构将成为其性能的一个重要因素。花点时间规划不同的字段并分析数据以确定最佳的字段类型、默认值和字段长度。您希望容纳适量的数据，并在数据无法保证时尽量避免 varchar 列和过大的数据类型。作为完成数据库后的附加步骤，您希望查看 MySQL 建议的所有不同字段的字段类型。您可以通过执行以下 SQL 命令来完成此操作：

   ANALYZE TABLE my_big_table
   

   结果将是对每列信息的描述，以及关于它应该是什么类型的数据类型以及适当长度的建议。现在您不一定需要遵循这些建议，因为它们完全基于现有数据，但它可能有助于让您走上正轨并让您思考

5. 索引或不索引

   对于如此大的数据集，根据您需要对前端数据执行的操作为您的数据创建适当的索引是非常重要的，但是如果您计划事先操作数据，请避免在其上放置太多索引数据。它不仅会使您的 SQL 表变大，而且还会减慢某些操作，例如列添加、减去和附加索引。对于我们的数据集，我们需要获取刚刚导入的信息并将其分解为几个不同的表以创建关系结构，以及获取某些列并将信息拆分为其他列。我们在我们知道可以帮助我们进行操作的最少列上放置了一个索引。总而言之，我们采用了 1 个包含 2000 万行数据的大表，并将其信息拆分为 6 个不同的表，其中包含主要数据以及基于现有内容新创建的数据。我们通过编写小型 PHP 脚本来解析和移动数据来完成所有这些工作。

6. 寻找平衡

   从编程的角度来看，使用大型数据库的很大一部分是速度和效率。将所有数据放入数据库固然很好，但如果您为访问数据而编写的脚本很慢，那又有什么意义呢？在处理大型数据集时，花时间了解脚本正在执行的所有查询并创建索引以尽可能帮助这些查询，这一点非常重要。分析查询正在执行的操作的一种方法是执行以下 SQL 命令：

   EXPLAIN SELECT some_field FROM my_big_table WHERE another_field='MyCustomField';
   

   通过在查询的开头添加 EXPLAIN，MySQL 将吐出描述它尝试使用、使用过哪些索引以及如何使用它们的信息。我将这一点标记为“寻找平衡点”，因为尽管索引可以帮助您的脚本更快地执行，但它们同样可以轻松地使其运行得更慢。您需要确保索引需要的内容和仅需要的内容。每个索引都会消耗磁盘空间并增加表的开销。每次对表进行编辑时，都必须为该特定行重建索引，这些行上的索引越多，所需的时间就越长。这一切都归结为创建智能索引、高效的 SQL 查询以及最重要的基准测试，因为您可以了解每个查询正在做什么以及需要多长时间才能完成。

7. 索引打开，索引关闭

   当我们处理数据库和前端脚本时，客户端和我们都开始注意到需要更改的一些小事情，这需要我们对数据库进行更改。其中一些更改涉及添加/删除列和更改列类型。由于我们已经在数据上设置了许多索引，因此进行任何这些更改都需要服务器做一些认真的工作以保持索引到位并处理任何修改。在我们的小型 VPS 服务器上，某些更改需要 6 个小时以上才能完成……这对我们进行快速开发当然没有帮助。解决方案？关闭索引！有时最好关闭索引，进行更改，然后重新打开索引……特别是如果您要进行许多不同的更改。随着索引关闭，与数小时相比，这些变化只需要几秒钟到几分钟的时间。当我们对更改感到满意时，我们只需重新启用索引即可。这当然需要相当长的时间来重新索引所有内容，但它至少能够一次重新索引所有内容，减少了一项一项进行这些更改所需的总时间。这是如何做到的：

   • 禁用索引：ALTER TABLE my_big_table DISABLE KEY
   • 启用索引：ALTER TABLE my_big_table ENABLE KEY

8. 给 MySQL 一个调整

   在让您的数据库和脚本快速运行时，不要忽视您的服务器。您的硬件需要与数据库和脚本一样多的关注和调整。尤其重要的是查看您的 MySQL 配置文件以了解您可以进行哪些更改以更好地提高其性能。我们遇到的一个很棒的小工具是 MySQL Tuner http://mysqltuner.com/. 这是一个快速的小 Perl 脚本，您可以将其直接下载到您的服务器并通过 SSH 运行以查看您可能想要对配置进行哪些更改。请注意，在运行 Tuner 之前，您应该主动使用前端脚本和数据库几天，以便 Tuner 有数据可供分析。在新服务器上运行它只会提供最少的信息和调整选项。我们发现在两周内每隔几天使用一次调谐器脚本来查看它会提出什么建议非常好，最后我们显着提高了数据库性能。

9. 不要害怕问

   开始使用 SQL 可能具有挑战性，并且使用极大的数据集只会让它变得更加困难。当涉及到大型数据集时，不要害怕去找知道自己在做什么的专业人士。最终，您将获得卓越的产品、更快的开发和更快的前端性能。当涉及到大型数据库时，有时需要专业人士经验丰富的眼睛才能找到所有可能会降低数据库性能的小警告。

回答by towfiqpiash

Simple solution is to run this query: mysql -h yourhostname -u username -p databasename < yoursqlfile.sql

简单的解决方案是运行此查询： mysql -h yourhostname -u username -p databasename < yoursqlfile.sql

And if you want to import with progress bar, try this: pv yoursqlfile.sql | mysql -uxxx -pxxxx databasename

如果你想用进度条导入，试试这个： pv yoursqlfile.sql | mysql -uxxx -pxxxx databasename

回答by mobby

For Windows, I use Navicat Premium. It allows you to transfer database objects from one database to another, or to a sql file. The target database can be on the same server as the source or on another server.

对于 Windows，我使用 Navicat Premium。它允许您将数据库对象从一个数据库传输到另一个数据库，或传输到 sql 文件。目标数据库可以与源数据库位于同一台服务器上，也可以位于另一台服务器上。

Navicat Online Manual for windows

Windows 版 Navicat 在线手册

回答by Vinod Kumar

Use sourcecommand to import large DB

使用source命令导入大数据库

mysql -u username -p

> source sqldbfile.sql

this can import any large DB

这可以导入任何大型数据库

回答by Bela Bessenyei

according to mysql documentation none of these works! People pay attention! so we will upload test.sql into the test_db type this into the shell:

根据 mysql 文档，这些都不起作用！人们注意了！所以我们将 test.sql 上传到 test_db 中，在 shell 中输入：

mysql --user=user_name --password=yourpassword test_db < d:/test.sql

mysql --user=user_name --password=你的密码 test_db < d:/test.sql

This works for sure!

这肯定有效！

Thanks.

谢谢。

回答by Saif

navigate to C:\wamp64\alias\phpmyadmin.conf and change from:

导航到 C:\wamp64\alias\phpmyadmin.conf 并更改为：

php_admin_value upload_max_filesize 128M
php_admin_value post_max_size 128M

to

到

php_admin_value upload_max_filesize 2048M
php_admin_value post_max_size 2048M

or more :)

或者更多 ：）

回答by Abdul Aziz

You need

你需要

• Bigdump script bigdump.php from the download
• Dump file of your database created by phpMyAdmin or other tool, lets call it dump.sql. You can also use GZip compressed dump files, lets call it dump.gz.
• Access account for your mySQL database
• Access account for some web server with PHP installed. This web server must be able to connect to the mySQL database. This ability is probably present if your web server and the mySQL server are from the same ISP.
• Some good text editor like Notepad++ to edit the configuration file.
• Some FTP client to upload the files to the web server.
• Common knowledge about files, PHP, mySQL databases, phpMyAdmin, FTP and HTTP

• Bigdump 脚本 bigdump.php 从下载
• 由 phpMyAdmin 或其他工具创建的数据库转储文件，我们称之为 dump.sql。您还可以使用 GZip 压缩转储文件，我们称之为 dump.gz。
• 您的 mySQL 数据库的访问帐户
• 某些安装了 PHP 的 Web 服务器的访问帐户。此 Web 服务器必须能够连接到 mySQL 数据库。如果您的 Web 服务器和 mySQL 服务器来自同一 ISP，则可能存在此功能。
• 一些不错的文本编辑器，如 Notepad++ 来编辑配置文件。
• 一些FTP客户端将文件上传到Web服务器。
• 关于文件、PHP、mySQL 数据库、phpMyAdmin、FTP 和 HTTP 的常识