Import Large MySQL Databases

Hello friends,
Today I am going to give you a useful tutorial on uploading big database to phpMyAdmin. Many times you have seen this error message at the time of loading database, "Got a packet bigger than 'max_allowed_packet' bytes". It means you are not allowed to upload big database pack to phpMyAdmin.
As a website owner or blogger, you may want to import your MySQL
dump from one server to another server database, but what you do if it exceeds the limit offered by phpMyAdmin. By default, phpMyAdmin provides the upload limit specified in the php.ini file. You can increase this upload limit size by editing the php.ini file if you have access to it, generally, if you are in a shared hosting you won’t be able to edit this file because it will affect all the accounts on that server. If you have shell access to your account you can import easily, see below for how to import using SSH.
Using BigDump.php (Recommended):
is a script written by Alexey Ozerov for importing large MySQL dumps. It’s a staggered MySQL dump importer, meaning the script executes only small part of the huge dump and restarts itself, the next session starts where the last was stopped. This process continues until it imports the whole database dump.
Download BigDump from here, http://www.ozerov.de/bigdump.zip
1.Adjust the database configuration in this file
2. Drop the old tables on the target database if your dump doesn't contain "DROP TABLE"
3. Create the working directory (e.g. dump) on your web-server
4. Upload bigdump.php and your dump files (.sql, .gz) via FTP to the working directory
5. Run the bigdump.php from your browser via URL like http://www.yourdomain.com/dump/bigdump.php
6. BigDump can start the next import session automatically if you enable the JavaScript
7. Wait for the script to finish, do not close the browser window
8. IMPORTANT: Remove bigdump.php and your dump files from the web-server
If you have completed the steps successfully you are done.
Using SSH:
Shared hosting account does not have shell access. If you have shell access you can use the following commands to dump your MySQL database.
#mysql -u [user] -p [password] -h [hostname] [databasename] <[file.sql]

Post a Comment Blogger