One main problem while moving my blog or my web application to another web hosting are moving and dumping the MySQL database. It should be no problem for small database or if an ISP has strong backup and restore feature but it will get you into headache for large database.

Hosting CPanel usually provided the backup and restore features but it will only restore your data without dumping the MySQL database. Dumping SQL from PHPMyAdmin or with MySQL command line limited for a few MB of file size. I get into big trouble while dumping my blog database with about 50 MB of SQL dump script while my ISP only limited the dump script for 2 MB.

Luckily, after a few Googling, I found bigdump, a simple and small PHP script with the capability to dump large MySQL database script. In my case, a 50 M MySQL database script has successfully dumped into database within 2 minutes process.

The usage is fairly easy. Just modify database connection script, upload the MySQL dump script and the modified bigdump script, open the web browser and then navigate to the relative path of Apache according to your upload folder.

Below is a step-by-step tutorial regarding the database dump script :

1. Download the  bigdump.php from this link
2. Unzip (untar) and edit file bigdump.php, modify the database configuration according to your environment.
3. Changes the default setting for  database encoding, I would like to recommend to use the encoding utf8 (replace $db_connection_charset = ”; with  $db_connection_charset = ‘utf8′;) except you used another encoding.

   // Database configuration
   $db_server   = localhost
   $db_name    = 'your database name';
   $db_username = 'your user name';
   $db_password = 'your password';
   

4. Upload the modified script
5. Click Start Import.
   

   

6. If you have a backup of MySQL dump and found  and error while inserting data,  try to investigating the SQL script. The SQL script created by CPanel has incorrect delimiter at the end of  content a shown on the image below.

7. DELIMITER ;;
   DELIMITER ;
   /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
   
   /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
   /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
   /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
   /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
   /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
   /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
   /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
   
   -- Dump completed on 2009-05-11  0:57:57
   

   Solve the problem by removing these lines.

Click here for more information regarding BigDump script.