So you have a larger mySQL database of more than 30 MB size, you want to move it to a new server, and you’re unable to import your database to the new server via phpMyAdmin, due to its time and size limitations.
But don’t worry, its not a big problem. It can be done using a tool BigDump
Here are the steps involved in moving your large database to a new server easily.
1. First you will have to download your old mySQL database to your local computer. This can be done via phpMyAdmin using the Export function.
2. Download the dumper from BigDump website, and unzip it to your local computer, using Winzip or any other unzip tool.
3. Create a new folder named e.g. “dump” on your new web server, and change its permissions to chmod 777. (Do change it back to default when you’re don’t with importing database.)
4. Open the unzipped file bigdump.php in a text editor like notepad, and adjust the database setting, i.e. database name, username, password.
5. Upload bigdump.php along with the dump file (downloaded from old server) to the new server under the directory we just created named “dump”
6. Now open the bigdump.php file by using a browser, i.e. Firefox, Internet Explorer, using address something like http://www.yourwebsite.com/dump/bigdump.php
7. Select the appropriate options and start the process, then wait for the process to finish, do not close your browser, if you do so, then you will have incomplete database on your new server. As the dump file is present on the server it will take less than a minute to complete the process, if the dump file is, consider 40 MB.
8. You must remove your dump (MySQL) file and the bigdump.php file from your server, also delete the dump folder, or change its permissions back to default.
June 14th, 2007 at 1:16 pm
thank you good article
June 20th, 2007 at 1:16 pm
What would you suggest for importing a 17GB sql file? I’m at a loss.
June 20th, 2007 at 4:03 pm
Hi Will,
You can try to import it using PhpMyAdmin, if it fails, then you can follow this tutorial to import your database.
You can also import tables separately, If you don’t want to do it by BigDump.
August 23rd, 2007 at 5:07 pm
I’d say its good for upto maybe a few MBs as mentioned in this article. However for situations similar to Will a web based solution will not be workable.
I have been experimenting with different options. Here is a list of failed trials:
1. Master-Slave configuration
2. Using MySQL Migration tool manager: May work on LAN but over the internet its a plain waste of time.
3. Using SQLYog: Another waste of time. Better try MySQL Migration first.
4. Copying the entire DB table files to the new server after creating the db in the new server without any tables: Failed. Check and Repair also did not work.
Some other way might be available short of copying it from disk to disk and hoping it will work… somehow on a different mysql server by fooling it into believing that its the same server as I tried in 4th method.
September 18th, 2007 at 10:34 am
Thanks a million for that nice tutorial, trying it right now, my db is about 56mb, not too bad I suppose
September 30th, 2007 at 7:24 pm
I had a massive db (100’s of gigabytes) and NO phpmyadmin or SSH administration tools to manage it (other than via remote host db connection).
Furthermore my old hosting company refused to help me - (they said it was too big to do a dump and post to me on disk!). However, I still managed to move it by writing a PHP script that handled the move.
The script was hosted on a temporary server that i had set up on a spare domain name. It toggled between accessing three db servers; the source db, the destination db and a ‘controller’ db that was used to log the processes. In its rawest sense I was simply selecting from the source db server and inserting into the destination db.
What made it slightly more complex was the fact that the source database was a live database and being updated every second by some user activity. So the whole ‘move process’ kept looping through, comparing source to destination and topping up the destination db based on last updated columns. (LONG LIVE LAST UPDATED COLUMNS - I now have them on every table).
It took about a week to move the database. I was quite pleased with the fact that the website was smoothly transferred to a new hosting company, (new dedicated server) with only about 30 mins downtime for the users.
I’m now in the, less stressfull, position of having SSH access plus PHPmyAdmin - so backing up now only takes a minute rather than week ;o)
The moral of the story - If you have time and patience, your db can be backed-up with just a READ permission on a db connection - what ever size it is.
November 9th, 2007 at 2:43 pm
I think you can handle gigs
see.. http://www.wanderings.net/notebook/Main/HowToImportLargeMySQLDataFiles
February 7th, 2008 at 8:46 pm
Ronny Stalker,
How can I contact you to hire you to deploy a similar solution for me?
Please contact me webmaster@safeena.org
February 16th, 2008 at 1:07 am
Hi Mahmoud,
I don’t really offer such a service - so cannot be hired to deploy it. I have, however, set up a project on google code and will post the scripts up there once I have removed any sensitive data out of them.
See: http://code.google.com/p/db-ez-move/
I’m not sure of your technical abilities - but anyone who has a basic understanding of MySQL and PHP could probably write a similar script or at least adapt it for your requirements. So, if you are not able to do it - it should be easy to hire someone in. The person who moves your db will also need to have a good understanding of your particular schema. Which tables are critical and in constant flux. which ones are static etc.
I will send you an email so that you have my address and can reply with any further questions.
Good luck.
February 16th, 2008 at 8:59 am
Thank you Ronny. I really appreciate your kind answer and the follow up by email.
July 17th, 2008 at 4:01 am
I’m backing up 900mb of db and can bigDump able to work on this?
I don’t know what i’m missing but I’m really following instructions (^_^)
please let me know
August 12th, 2009 at 3:53 am
Is their any option other than Export from Old server like Download data file from old server and replace it on new server