Ronaldo (1)How to restore big MySQL databases when PHPMyAdmin fails
As a blogger, the most important thing to do regularly aside from writing great contents is to back up the files of your site. You’ll never know when will you gonna need it, and it will surely break your heart if that time comes, and you don’t have any.
Just a check – when was the last time you backed up your site’s contents? Let’s say your blog suddenly went down, files lost and corrupted, would you be able to put it back online in an hour or less? How many posts will not be included when you restore it?
This leads to the next question – how frequent are you backing up your site? Daily? Twice a week? Weekly?
Oh well, this post is not about how to back up your site though, but if you want to check my post on how to backup your blog site “automatically” just follow the link.
Now, assuming you have a backup, make sure it’s working and you know how to restore it. But then again, non-techie bloggers like me are rarely comfortable working with MySQL commands, worse, we don’t even have shell or telnet access to our webserver (mostly true to shared hosting accounts).
A user-friendly option is to use the PhpMyAdmin which is available to most hosting cPanel accounts. However, this works pretty well if your blog’s database is less than 50MB (as in my case). Sometimes it works for MySQL databases of up to 100MB but you’ll run the risk of a server timeout and other errors which will prevent you from restoring your backup.
Just recently, a blogger friend of mine had this problem – she had to transfer her blog to a new hosting account, and unfortunately, cPanel to cPanel transfer was not possible.
Being a dedicated blogger, her blog’s database was about 400MB and there was no way it can be restored using PhpMyAdmin. Unless you have shell access to the server (which we don’t) and understand the following geeky command, life would be easier, but…
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
… but, I’m just too afraid to do that if ever I have access. And what are the other options?
Either you hire the services of techie guys and shell out a hundred bucks for it, or you can try doing it yourself using the very cool small PHP program called BigDump which allows restoring very large MySQL databases with ease.
This 10KB PHP utility program restores MySQL dump – your backup file in say, ‘backup.sql’ format or compressed dump file in say, ‘backup.gz’ or GZip format.
It restores big databases in staggered mode. You need to have access to your webserver files using an FTP program so you can upload both your backup file and the BigDump file in a temporary ‘dump’ directory (you better change hosting provider if you don’t have FTP access, and I’m serious).
You can download this program at BigDump website, and follow the simple instructions. At the minimum, you should be able to do basic things like opening a PHP program, provide database credentials like username, password, database location and database name of your blog.
If you can’t, better start learning some simple technical stuffs related to managing your blog site now before it’s too late.
I lost six to nine months of comments when my blog’s MySQL database got corrupted.
How it got corrupted is another story but with the backup all the time I thought my daily backups end up in a dedicated gmail account I setup to receive all scheduled backups and other cron jobs. For some reason they stopped working a long time back and so I lost a ton of data.
That’s quite a “disaster” bro! Grabe. Well, we always learn from experience.
the worst part about it was I thought I had my back covered. I had everything set for a daily backup but still ______ happens
I must admit, I have no back-up of my blog since almost a year.
I have no PC of my own now, and I am renting in an Internet cafe. But anyway, I will do my best to follow your advice.
Thanks!
Pare, that reminds me of the stuff about your hosting and account. To tell you honestly, I don’t understand how your hosting works. But still, try to get your most latest mySQL backup and we can host it somewhere safer and easier to manage. Just let me know.
Merry Christmas pala sayo.
I use bigdump and it’s very useful.. Of course, i don’t wanna cut and paste sql codes just to execute the script in series.. It’s time consuming, Thanks to bigdump, hehe..
Still very useful, especially for me who’s still new to shell commands – even if I am an experienced coder. Thanks a lot!
Thanks gem for sharing this blog post. This kind of “ping” me to trigger myself to back up my blogs. I think I only got 1 blog under wordpress that I would like to backup.
I love coding, but I hate shell scripts!!! I would rather use a MySql database manager software like the MySqlyog to back-up database.
Sige, one day, I would back-up my database. I know it’s a good practice. Tamad lang ako :p
sometimes, it’s better for me to manually divide the whole file of mysql dump into several files of dumps and then manually upload them 1 by 1. It’s time consuming but I like it. It’s like I am being challenged…. or is just like another training for enhancing my patience.