Weblog of Nazly Ahmed
A lot of things influenced me to write this post. In fact I wanted to write on this sometime back but finally I was able to squeeze some time. Lately I have been moving data between servers and the main problem I had was with large MySQL dumps because I have been using PHPMyAdmin for most of the MySQL operations and its one of the best tools available and most importantly its web-based. Even on my local development environment I'm comfortable with using PHPMyAdmin and on the web servers it can be very handy if the server is a shared hosting server. I would rather recommend using the command line client utilities that MySQL offers for import/export operations because its the safest. But you will need SSH access to your server. If you do have SSH access don't hesitate to choose this method above the others.
Using the mysqldump client it is possible to backup a database into a SQL file which will contain SQL statements that can recreate the database tables when restored.
The following command from shell can be executed to backup a specific database
mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
Click Here for more options on using mysqldump
You can use this command from shell to restore the database using the SQL dump file
mysql -u [username] -p [password] [databasename] < [backupfile.sql]
So thats quite basic and safest ways to import/export. But then you might ask whats the options you have if you are on a shared server and do not have SSH access. Well then PHPMyAdmin is the only choice available coz its web-based. Most servers have PHPMyAdmin as an option in the Server's Control Panel. Worst case if you don't have it or cannot find it you simply can download the source from www.phpmyadmin.net and upload the files via FTP and set it up. Installation is quite simple if you follow the Documentation.txt file. Once it is setup you can create databases etc. When you have selected a database from the left panel there will be tabs called SQL and Export. Using the SQL tab you can restore the database using your SQL dump file. Similarly you can use the Export option to export the data to a SQL file.
But then again you come across problems when you have a large MySQL dump. Exporting a large database won't be a problem but there are times that the SQL dump file tends to get corrupted for various reasons. Importing a large SQL dump file would create a problems coz with default installations there is a 2MB upload limit. This is not a PHPMyAdmin limit. This limit is set in the PHP configuration. To increase this upload limit you have to change the post_max_size and upload_max_filesize directives in the php.ini and then you can restore a large SQL dump. But if you are on a shared hosting server its highly unlikely that you can change the directives in the php.ini file. So thats where most people get stuck. When you are moving from your local machine to the server this is one problem you will face. Similar problem I faced when I had to move a large database from a server that I could SSH to a shared hosting server. I dumped the database to a SQL file using mysqldump command line utility and then when I tried to restore using PHPMyAdmin there was this upload limit. Arghh.. At that time I simply split the file manually into smaller files which are less than 2MB and uploaded one by one. It came to about 7 files at that time so didn't really bother about splitting them manually. This is a dirty trick but still effective but I won't suggest you to use this method. At a later time when I came across a similar instance I planned to write a tiny PHP script that would do the job. But thankfully I got a new server that I could SSH into.
So things can get bit messy at these situations so gotta figure out ways to overcome those with the limited resources we have. Lately I found BigDump: Staggered MySQL Dump Importer which seems to do the job on the web servers with hard runtime limit. So I guess I have more choices now. I haven't tried out this yet. Hopefully can play around with this next time when I have tight limits.