How do I split a large MySQL backup file into multiple files? [duplicate]

Question

I have a 250 MB backup SQL file but the limit on the new hosting is only 100 MB...

Is there a program that let's you split an SQL file into multiple SQL files?

Macmillan

If you want to do it fast and simple on Windows / Mac / Linux use: SQL Dump Splitter 3 and do not forget to manually add / remove transaction scripts. – Dapsang

Answer 1

You can split a large file in Eclipse. I have tried a 105GB file in Windows successfully:

Just add the MySQLDumpSplitter library to your project: http://dl.bintray.com/verace/MySQLDumpSplitter/jar/

Quick note on how to import:

• In Eclipse, Right click on your project --> Import
• Select File System and then Next
• Browse the path of the jar file and press Ok
• Select (thick) the MySQLDumpSplitter.jar file and then Finish
• It will be added to your project and shown in the project folder in Package Explorer in Eclipse
• Double click on the jar file in Eclipse (in Package Explorer)
• The MySQL Dump file splitter window opens which you can specify the address of your dump file and proceed with split.

Soulless

Answer 2

It's not pretty (because it just splits on size, not on what is logically in the file) but you can use the unix split tool to accomplish this:

mysqldump mydb | split -b 100m mydbbackup

Make sure to check the man page for split, your copy may or may not accept the 100m size argument. Some need to have the size specified in bytes.

When you go to restore from the file you'll have to use cat to join them all back together.

cat mydbbackup.1 mydbbackup.2 mydbbackup.3 | mysql

Forta

Sorry, this is not what I'm talking about. All I have is the 250 MB file and the old database no longer exists on the old host. We have a new host and the limit is 100 MB so I need multiple smaller files and create them just from the one 250 MB file ... can't do anything with mysqldump right now ... unless I import it locally where there are no restrictions then do a mysqldump ... but cat'ing won't work. – Macmillan

@BrianTHannan You can split the file afterwards with this command: split -l 5000 ./path/to/mysqldump.sql ./mysqldump/dbpart- See [webmaster-source.com/2011/09/26/… – Tied

Answer 3

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

Tercet

Please don't copy and paste the exact same answer to multiple questions. Each answer should be customized to answer the question being asked not provided a generic copy/paste. – Conformity