Geek Answers Handbook

Export a large MySQL table as multiple smaller files

I have a very large MySQL table on my dev server: over 8 million rows of data. I successfully loaded the table using LOAD DATA INFILE.

Now I want to export this data and import it to a remote host.

I tried LOAD DATA LOCAL INFILE on the remote host. However, after 15 minutes, the connection to the remote host fails. I believe that the only solution for me is to export data to several smaller files.

At my disposal are the tools PhpMyAdmin, HeidiSQL and MySQL Workbench.

I know how to export as a single file, but not multiple files. How can i do this?

+6
source share
7 answers

I just imported / exported a (partitioned) table with a 50 millionth record, it took only 2 minutes to export it from a fast enough machine and 15 minutes to import it on my slow desktop. There was no need to split the file.

mysqldump is your friend, and knowing that you have a lot of data, it’s better to compress it

@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
+15
source

Take a look at mysqldump

Your lines should be (from the terminal):

export to backupfile.sql from db_name in your mysql:

 mysqldump -u user -p db_name > backupfile.sql

import from backup file into db_name in mysql:

 mysql -u user -p db_name < backupfile.sql

You have two options for sharing information:

  • Divide the output text file into smaller files (as many as you need, many tools for this, for example split ).

  • Export one table each time using the option to add a table name after db_name, for example:

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

Compressing files (text files) is very efficient and can minimize up to about 20% -30% of the original size.

Copying files to remote servers should be done using scp (secure copy), and communication should be done using ssh (usually).

Good luck.

+4
source

I found that the advanced options in phpMyAdmin let me choose how many lines to export, plus the starting point. This allows me to create as many dump files as needed to get the table on the remote host.

I had to adjust the php.ini settings, as well as the phpMyAdmin config 'ExecTimeLimit' setting since the dump file generation takes some time (500,000 lines each).

I am using HeidiSQL to import.

+3
source

As an example mysqldump approach for a single table

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

Importing is as easy as

 mysql -u username -ppassword yourotherdb < table_name.sql
+2
source

Use mysqldump to upload the table to a file. Then use tar with the -z option for the zip file. Transfer it to a remote server (using ftp , sftp or another file transfer utility). Then unzip the file on the remote server. Use mysql to import the file.

There is no reason to split the source file or export it to multiple files.

+1
source

How to split a large MySQL backup file into multiple files?

You can use 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 ...
+1
source

If you are not comfortable using the mysqldump command line tool, here are two GUI tools that can help you solve this problem, although you should be able to upload them to the server via FTP!

Adminer is a thin and very efficient DB Manager tool that is at least as efficient as PHPMyAdmin and has only ONE ONE FILE that must be uploaded to the server, which makes it extremely easy to install. It works better with large tables / databases than PMA.

MySQLDumper is a tool designed specifically for exporting / importing large tables / databases, so it will not have a problem with the situation you are describing. The only dowside is a bit tedious to install, as there are more files and folders (~ 350 files in ~ 1.5 MB), but there should be no problems downloading via FTP, and it will definitely get the work done :)

So, my advice: try Adminer first, and if this one doesn't work either, go to the MySQLDumper route.

0
source

More articles:


All Articles