tools

I don’t know how many folks, like me, have had troubles using phpMyAdmin to perform large MySQL database restores, but if you have, then perhaps this little set of pointers will help you out. I know this process has made my life easier.

As most of you probably already know, phpMyAdmin is a wonderful toolset. It makes database management and editting both simple and straightforward. However, one of the major drawbacks to the phpMyAdmin toolset is that it is VERY slow to upload large amounts of SQL or perform Database restores using the Import function. For those of us with large and very large MySQL databases this presents a major issue. So what can be done to get around the problem?

The obvious conclusion is that a tool other than phpMyAdmin is needed. MySQL Administrator is the tool I have discovered that works best for me. The tool is starightforward, secure, and fast. The process I have found to work is as follows:

• Download and install MySQL Administrator (and set it up so that it is able to log directly into your database server). This is easy to do; the only gotcha I encountered is: remember not to use http:// in your url address.
• “Connect” MySQL Administrator to the system where you keep your MySQL DB.
• Using the Backup function, create a backup of the DB and store it somewhere easy to locate.
• When you need to do a restore (or ‘yes’ even a copy) all you need do is log into MySQL Administrator, connect to your database system and access Restore Backup. Interestingly this backup need not have been generated via MySQL Administrator; I have used phpMyAdmin generated files successfully.
• If you are performing a simple restore, all you need to do is point the restore to the target DB and wait. In my expereince, it takes about 5 minutes to restore a 25MB database.

If this were all that we could do the tool, that would be enough, however, there are other useful functions that you can employ with a couple of small ‘tricks’. For example, if you want to create a new DB and freeze an old one, all you need to do is:

• create the new database per your usual process (remember the name you give to this new DB).
• backup the database you want to clone
• open a copy of the ‘backed up’ database in your favorite editor (I use geany)
• do a gloabl search and replace of the old database name with the new one- per the name you created in the first step above
• restore this editted file into your blank database using the functions within MySQL Administrator
• point your application to the database of your choosing– now that you have at least two.

I find this process to be extremely helpful in upgrading and testing systems.

I hope this little tutorial helpful. I wish I had figured this out years ago, myself.