Friday, May 8, 2009

PHPMyAdmin Large SQL File Dumping Problem

As I'm a web/WAP developer with PHP as the base scripting language and MySQL as the DBMS, I've to take backups of the DB at rather regular intervals to counter any catatrophic events. I noticed that dumping a large SQL file (say 8-10 MB or more) takes lots of time(around 10-12 mins. or more) through PHPMyAdmin. I used to attribute this to MySQL thinking, it to be slow at parsing the file. One sunny day, I opened task manager before starting the dump process. As I hit the submit button, I saw the processor usage shooting up to 100%. I changed to the "Processes" tab and was surprised to see that mysql process wasn't consuming any CPU power. It was Apache which was clogging up 99% of the CPU power. I tried to End the process but it refused to, and my PC finally crashed.
Taking a lesson from this, I tried an alterntaive way. I used the MySQL command line interpreter to dump the file and voila!!! The dump finished smoothly in less than 15 seconds!!!! So, that was an Apache bug. Right???
Ok, by the way here is the step by step process to carry out the same.
1. Copy your sql file to a convenient location(say root of C:\) and name it something simple (say 1.sql)
2. Now launch command prompt and change to the directory where MySQL is installed. Mine is in F:\xampplite\mysql\bin
3. Type
mysql -u root

and enter your root password if asked. You may use any other username instead of root, depending on your needs.
4. Create the database if you want to dump into a new DB

5. Instruct MySQL to use this new DB.
USE my_db;

6. Now use the SOURCE statement to dump the file
SOURCE C:\1.sql;

Watch out the process munching a couple of lays chips! By the time you finish two chips, mysql will finish the file :)
I'm uploading the screenshot for a 22MB SQL file dump.

This one's upto the time I pressed enter after SOURCE statement

This one is after execution

No comments: