Humpty Dumpty Jumped Over The Wall
This blog / website runs on WordPress. An excellent software for people like me who want to focus on the content and leave presentation to a templated system. Whenever WordPress prompts me to update the software, I do not procrastinate the update; having learned it the hard way.
So a few days back when I was prompted to update to WordPress 3.2, I promptly clicked on the ‘Update Automatically’ button only to be be stumped! Turns out that my blog was running on MySQL 4.x database and WordPress 3.2 requires MySQL 5.x database.
I created another MySQL ver 5.x database so that I could copy the data from the existing database into it. Transferring the data turned out to be quite a challenge.
I did not want to use WordPress’s inbuilt Export / Import Tools since these tools work great for backing up and restoring articles and comments but fail to backup other tables in the database such as Users, Plugin settings etc. For that you need to Export / Import the database.
My blog is hosted at GoDaddy. Exporting the database using PhpMyAdmin tool provided by GoDaddy was easy enough. I exported the database in SQL format and downloaded it in ZIP format. The resulting 10.3 MB SQL file was compressed into 1.31 MB ZIP file by the web-server and downloaded to my PC.
Importing it back into the MySQL 5 database using PhpMyAdmin was the next big stumbling block. Turns out PhpMyAdmin on GoDaddy only allows importing of SQL files under 1 MB in size (mine was 10.3 MB). The interface claimed to support ZIP files.
So how do you import a 10.3 MB SQL file into a system that only allows 1 MB files?
Brilliant Idea #1:
Break the extracted SQL (10 MB) file into 2 parts (must break intelligently. Do not just use a splitting tool). ZIP up the parts (should come to approx. 500 KB ZIP file per part). Upload the ZIP files into the PhpMyAdmin Import functionality. Turns out, the Import function does not support ZIP files after all. Breaking the 10 MB file into 10 smaller SQL (uncompressed) files (and uploading them one at a time) was not an option I was even willing to explore.
Brilliant Idea #2:
Download a MySQL Administration Client on my laptop. Connect the client to the online database and upload the data from my PC. Turns out, GoDaddy does not allow a desktop client to connect to the databases on their systems. Though the databases appear to be on public IP, the databases only allow connects from their approved list of IPs.
Brilliant Idea #3:
Install an alternate PHP MySQL Admin Client in my web hosting space. One that does not have 1 MB import limitations. Turns out, researching SourceForge for suitable MySQL clients is really tough. My frame of mind was to discover a super quick fix; not get into legitimate research.
Fail Fail Fail.
Then it stuck me: why try to use packaged software when probably a line or two of PHP code could do the job? And I found just the right code on Google on my first try.
<?php passthru("nohup mysql -h my-godaddy-database-host -u my-database-username -pMy-database-password my-database.name < my-exported.sql"); echo("Done importing data."); ?>
Using FTP, I uploaded this PHP file (I had saved the code as updatedb.php) and the SQL file (my-export.sql). After upload, I simply called the PHP file (http://www.rajib.com/xxx/yyy/zzz/updatedb.php) and less than a second later, the message on my screen was ‘Done importing data’. If you are going to use the code above, remember to change “my-… ….” details with the correct details as required by your web-host.
Next I downloaded and opened wp-config.php from my WordPress installation and changed the Host, Database, User, Password details in it to reflect the new MySQL 5 database. I uploaded the file back (overwrite the older one) and voila!
My blog was running off the new database. Heck, I even nuked the old database from GoDaddy just to be sure.
I updated the installation to WordPress 3.2 in a jiffy and hope that I shall be able to continue to do so until Version Number demons come calling again.