Split a large MySQL backup file into multiple files?

Mujkanovic · Dec 5, 2016

I have a large MySQL database file, around 1GB of data, I want to split into multi files and importing them into a database in my hosting. is It possible? is there a tool/way to do this task?

LJSHost · Dec 5, 2016

Hi Mujkanovic

Yes, you can do his. Are you trying to restore backups to phpmyadmin ?

Use the linux split command

split -l 5000 mysqldump.sql dbpart

The split command takes a file and breaks it into multiple files. The -l 5000 part tells it to split the file every five thousand lines.
Next is the path to your file, and the next part is the path you want to save the output to.

Once complete you will have a bunch of files called dbpart_ each containing 5000 database entries.
Files will be saved as whatever filename you specify (e.g. dbpart_) with an alphabetical letter appended.

Mujkanovic · Dec 5, 2016

LJSHost said:
Hi Mujkanovic

Yes, you can do his. Are you trying to restore backups to phpmyadmin ?

Use the linux split command

split -l 5000 mysqldump.sql dbpart

The split command takes a file and breaks it into multiple files. The -l 5000 part tells it to split the file every five thousand lines.
Next is the path to your file, and the next part is the path you want to save the output to.

Once complete you will have a bunch of files called dbpart_ each containing 5000 database entries.
Files will be saved as whatever filename you specify (e.g. dbpart_) with an alphabetical letter appended.

Cool guide I have not use this way before, I heard of MySQLDumper tool but I will try your way, I prefer using commands.

LJSHost said:
Files will be saved as whatever filename you specify (e.g. dbpart_) with an alphabetical letter appended.

What file extension will I get after splitting the main file? dbpart_a.sql?

The final question:

How can I import these files into a MySQL database? using phpmyadmin or commands?

LJSHost · Dec 5, 2016

Spilt is a old unix command that has been around long before GUI's or even MySQL.

You could also use:
split --bytes=100m file.sql file_

This will break the file into 10 100mb chunks.

The file extension will be dbpart_aa etc

You could use the command

cat dbdump_* | mysql -u root -p database_name

This will merge and restore the parts into the database_name specified

A 1GB .sql file is quite small you could just restore it using mysqldump from the command line, seems a little overkill splitting up a file this small. If it was 10GB I would understand the need.

DaRecordon · Feb 9, 2017

Mujkanovic said:
I have a large MySQL database file, around 1GB of data, I want to split into multi files and importing them into a database in my hosting. is It possible? is there a tool/way to do this task?

If you want to split your Mysql database into small files you can use SQLDumpSplitter

Step 1: Choosing your .sql file
You press the Browse button and select the file you want to split

Step 2: Set maximum filesize

You can select file size according to how much its capacity and it is up to you.
For example: 1024 kilobytes ~ 1MB

Step 3: Chose target directory

Completed the steps above and you press the button: EXECUTE

It will displayed a folder name SQLDumpSplitterResult, which will contain the .sql files has been splited as selected size.
After done, you can use phpMyAdmin to import data_1.sql, data_2.sql,..etc into your Mysql database

CaygriWEB · Jul 27, 2017

It need root system or MySQL root access to use?

Split a large MySQL backup file into multiple files?

Mujkanovic

Well-known member

LJSHost

Well-known member

Mujkanovic

Well-known member

LJSHost

Well-known member

DaRecordon

Well-known member

Shorty

New member

DaRecordon

Well-known member

Shorty

New member

CaygriWEB

Well-known member

LJSHost

Well-known member

Latest posts New threads

Latest posts

New threads

Latest Hosting Offers New Reviews

Latest hosting offers

Popular Contributors - Past 30 Days

Sponsors

Tag Cloud