donate to charity, donations, charity donation,donate online, online donation,donate money, car insurance, auto insurance,insurance quote,hospital staffing software hospital software, Business,VoIP,solutions,medical practice ,donate, car, charity CALIFORNIA, donate car, charity hospital

PHP MyAdmin 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 catastrophic 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 PHP MyAdmin. 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 alternative 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 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

CREATE DATABASE my_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 :

Post a Comment