Latest Tools

Mysql is the number one open-source database management system, which give the programmer or developer a lot of flexibility in managing huge and complex tables of data. This is very enticing to the developer who would like to spit out data on the fly using PHP - open-source, server-side programming language . However, many hosting companies like Godaddy limit the amount mysql will load or import to roughly 2M.

Mysql is the number one open-source database management system, which give the programmer or developer a lot of flexibility in managing huge and complex tables of data. This is very enticing to the developer who would like to spit out data on the fly using PHP - open-source, server-side programming language . However, many hosting companies like Godaddy limit the amount mysql will load or import to roughly 2M.

This poses a challenge to developers who have huge data files (some in the Gigabytes of data) to import. So here are 4 ways you can import large data into mysql:

1. If your data is around 20M you can run an sql query directly in phpMyAdmin. First create your database, then open you data file in notepad or any text editor. Copy your sql data, click on the sql menu in phpMyAdmin and paste your data and click go.

2. If the file is a little bigger you can log into shell and edit your php.ini files by typing in the following code: nano forward-slash etc forward-slash php.ini. Once there locate the "max allowed packet" amount and change it from 2M to something like 30M. This will allow you to import about 30M of data via regular import.

3. In the event the files is 50M or more, I would recommend using the mysql dump script called BigDump created by Alexey Ozerov. It is a simple script that connect to your server and dump the data line by line. However, if you have a really complicated database it may not upload your file and fail.

4. The best way to import large mysql data to phpMyAdmin is to use the SOURCE command. Using source command to import mysql file is easy, yet very few developers use it. To use it, add you file to your bin or cgi-bin directory, then log into shell and type in the following: USE DATABASENAME; then type SOURCE forward-slash bin forward-slash filename. It will immediately start entering you file into the database line by line.

However, this method is not for those inexperience with shell and a back-end environment. It is not for the newly aspiring amateurs that may end up making mistakes and damage important data.