A lot of us have blogs, websites and web applications. A good number of us use private hosting providers like bluehost / dreamhost / mediatemple as opposed to hosted services such as wordpress / blogger / gmail, etc.
When you’re with a private hosting provider migrating between providers is a common scenario that occurs on reasons of cost, reliability, performance, demand and so on… Moving files across these providers is a breeze… FTP from one place to another…
The question that arises is, how do you move that huge mysql database??? I’ll walk you through two regular techniques that are generally used to move databases… And then the third technique that uses the BigDump tool…
All the three techniques require you to take a dump of the mysql database of your application(s) using phpmydmin / mysql CLI. Download the resultant .sql or .sql.gz (Compressed zip file) and keep it ready to move…
1. phpMyAdmin: Everyone knows this… and everyone that uses a private host has used it atleast one time… Once you’ve created your new database at the new host, open up phpMyAdmin and navigate to the newly created database. Click on the SQL link and click browse in the window that pops up. Select the .sql or .sql.gz file and select Import. The job is done! The problem with this technique however is that it is limited by the memory limit imposed on PHP by the server. Most cases, this is around 7-8MB.
2. mySQL CLI: This is the next advanced method of sql import. This is from the commandline (SSH) on the server. It is extremely simple and ready to go if you have shell access to your space on your server. The technique goes like this. Upload the .gz file via ftp onto your new site and login to your server via SSH using putty or equivalent tools and navigate to the location.
- Unzip the .gz file if you’ve used compression.
you@host~ tar -xvf database.sql.gz
- Dump the sql into your preselected database like so:
you@host~ mysql -h mysqlhosturl.com -u yourmysqlusername -p yourmysqlpasswd -p yourdbname < databasedump.sql
3. The BigDump technique: The first technique works, as mentioned, works for very small database backups and restores. The second technique works when you’ve got shell access to your hosting space. The third technique works when your sql file is too large for phpMyAdmin and you do not have Shell access to your hosting space.
BigDump works by what is called staggered import of SQL. It breaks down and batches SQL imports into the database, thus maintaining the memory limits of PHP and also getting the job done via a simple web interface.
Getting it to work is very simple. The script has only 4 parameters you need to configure: the mysql host, username, password and database name. Once the info is keyed in, upload the BigDump.php (download) to the folder where your big sql file is. Chances are that its already in a .gz file.
Using your browser, open up www.yourserverpath.com/bigdump.php. It automatically detects sql and .gz files placed in the current directory. Just hit start import and you’re good to go…
A screenshot is attached below:
There. That does the post… I felt the need to post about the tool as it helped me out of a very cheeky solution yesterday. And for the beta the developer calls it to be (Version 0.30 beta), the tool is amzingly stable. And it works like an absolute charm.
The developer website is here.
Till the next post…
Chao…
Recent Comments