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 :
- Download the bigdump.php from this link
- Unzip (untar) and edit file bigdump.php, modify the database configuration according to your environment.
- 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.
[code language=’cpp’]
// Database configuration
$db_server = localhost
$db_name = ‘your database name’;
$db_username = ‘your user name’;
$db_password = ‘your password’;
[/code] - Upload the modified script
- Click Start Import.
- 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.
- [code language=’cpp’]
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
[/code]
Solve the problem by removing these lines.
Click here for more information regarding BigDump script.
i’m having the delimiter problem as well, just want to confirm what lines should we remove ? is it only delimiter; & delimeter;; lines or the whole lines being displayed on the last image on your article? thanks for giving out such a detailed article, great effort 😀
i’m having the delimiter problem as well, just want to confirm what lines should we remove ? is it only delimiter; & delimeter;; lines or the whole lines being displayed on the last image on your article? thanks for giving out such a detailed article, great effort 😀
It looks as though the bigdump.php process would take longer to complete as you would be generating the dump, downloading the dump file, uploading the dump file, and importing the dump file across your old host, local machine, and new host.
If you are using cPanel on both your old host and new host you should be able to add your local machine as an access host and run mysqldump locally, then execute the dump file against (or pipe it to) a connection to your new cPanel host.
It looks as though the bigdump.php process would take longer to complete as you would be generating the dump, downloading the dump file, uploading the dump file, and importing the dump file across your old host, local machine, and new host.
If you are using cPanel on both your old host and new host you should be able to add your local machine as an access host and run mysqldump locally, then execute the dump file against (or pipe it to) a connection to your new cPanel host.
@erik
It’s at the end of your sql dump.. Or,if you have similar problem as I do (can’t edit large file) you could add another comment in file bigdump.php.
$comment[] = ‘DELIMITER’;
CMIIW
@erik
It’s at the end of your sql dump.. Or,if you have similar problem as I do (can’t edit large file) you could add another comment in file bigdump.php.
$comment[] = ‘DELIMITER’;
CMIIW