Split a large MySQL backup file into multiple files?

Mujkanovic

Well-known member
Collaborate
Registered
Joined
Apr 24, 2016
Messages
424
Points
18
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

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
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

Well-known member
Collaborate
Registered
Joined
Apr 24, 2016
Messages
424
Points
18
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.


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

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
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

Well-known member
Registered
Joined
Oct 7, 2016
Messages
242
Points
18
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

SQLDumpSplitter.jpg

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

phpmyadmin-import-database.png
 

CaygriWEB

Well-known member
Hosting Provider
Registered
Joined
Jul 4, 2017
Messages
305
Points
18
It need root system or MySQL root access to use?
 
Older Threads
Replies
6
Views
4,200
Replies
25
Views
8,522
Replies
4
Views
1,957
Replies
4
Views
2,372
Recommended Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top