MySQL Bulk Upload

By John Kim | July 19th, 2010

0
Response

If you are a php web developer, I’m sure you’re familiar with the usage of phpmyadmin. For those who is unfamiliar, phpmyadmin is a web tool that give a graphical user interface for the mysql database. Let’s just say it makes life easier on the web developers.

So as a any good developer should do, they should always back up the database in case anything horrible happens to the server. This is easy to accomplish using the export function in phpmyadmin. Now if your database has less than 16mb of data, importing that backed up information is simple through phpmyadmin. But what do you do when you have gigs of data to import? You definitely cannot use phpmyadmin to accomplish this as it will warn you of the size limit. In this article I’ll give a quick solution to this problem by using a mysql bulk upload, so lets start from the beginning.

1. login to phpmyadmin and choose a database

2. go to the export tab, make sure the ’save to file’ checkbox is on, then click the ‘Go’ button.

3. You now have a sql file downloaded to your computer.

4. Upload this file to a location on your web server, and keep a note of the location of the file.

5. open an SSH application, I use putty. Then place your host address, which is the same host address you use to connect to your server. Afterwords you connect, and you should get a command prompt type of interface.

*note you may have to ask your hosting company to activate ssh for you.

6. find the directory that your sql file is stored.
cd .. - goes back a directory
cd [folder name] - enter the folder
ls - displays all folders and files in the current directory

7. once you’re in the correct directory, then type in this command for a mysql bulk upload.

mysqldump -u [user name] -p [your password] [database name] > [File name].sql

*note that the information in the brackets means you need to fill in your own information.

So doing a mysql bulk upload is a simple process, but it definitely is not something you would know unless someone told you. I remember how frustrating it was to do a mysql bulk upload using the import function in phpmyadmin. I had to split my sql files into many different pieces in order to accomplish this. It was a monstrous task that took hours out of my life. So I hope my mysql bulk upload tutorial has helped enlighten you, and you are welcome to thank me monetarily by writing a check payable to John Kim.

so if you guys have any questions about the mysql bulk upload you can place a comment below.