Large Database
Dealing with Massive Databases
I’ve had a bunch of problems in the past when dealing with large databases. I’m talking about 100MB+ with hundreds of thousands or even millions of records. So what I needed were some large database software utilities.
I’ve found 2 powerful tools to get the job done, and don’t worry they are both free.
sql dump splitter 2
SQL Dump Splitter
The first one is SQL Dump Splitter an amazing lightweight utility that breaks up those massive files in to smaller files. If you’ve ever tried to upload a large database into phpMyAdmin or similar you will always have problems. It’s max upload size is somewhere around 50MB for one, and it is notoriously buggy. SQL Dump Splitter breaks up the files into smaller bits and those bits are more easily “ingested” by phpMyAdmin or whatever interface you are using.
After using the splitter you’ll get a readme.txt file with this in it
“These files are created with SQLDumpSplitter 2.
If you want to restore the dump, you have to execute the file ipinfodb_one_table_full_DataStructure.sql first because it contains the structure like Tables.
After that, you can execute the other .sql-files as they contain the data of the now existing tables.
SQLDumpSplitter 2 by Philip Lehmann-Böhm
E-Mail: Philip@PhilipLB.de
Homepage: http://www.PhilipLB.de”
Restoring that file will create the database table, and running the others will put the data back in.
mysql dumper
MySQL Dumper
The next utility is called MySQL Dumper this is a replacement for phpMyAdmin. It’s faster, runs better, fails less, looks better, and best of all can be ran on port 80 (standard internet port). phpMyAdmin must be ran on some port that is blocked by many firewalls. MySQL Dumper has it’s own folder that it protects using .htaccess. This software backs up and restores databases at mind numbing speeds. It can chew through a 1 million row database in seconds. Amazing really. If you watch the video below you can see mysql dumper plow though some 1 million records in 24 seconds.
Here is what the official website has to say…
“MySQLDumper is a PHP and Perl based tool for backing up MySQL databases. You can easily dump your data into a backup file and – if needed – restore it. It is especially suited for shared hosting web spaces, where you don’t have shell access. MySQLDumper is an open source project and released under the GNU-license.”
[ad]
Also see / read
Best Open Source Database