Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

MySQL Administrator- pointers
 
rabideau
Jr. Member
 
Avatar
Total Posts:  28
Joined:  2008-05-15
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Fibo
Sr. Member
 
Avatar
Total Posts:  107
Joined:  2008-06-25
Marseille, France
 

When restoring large backups, I very often use bigdump (see http://www.ozerov.de/bigdump.php) which works great for files of any size.

There are 2 constraints though:
1 - The backup with phpmyadmin must NOT use the ‘extended insert’ option
2 - You need to be sure that the SQL file/ script you are restoring has Unix, not windows, end of line.
If you edit your file on windows, than presumably it will get “windows end of line”, not Unix ones… you need then to transfer the file wit a ftp program that converts these line changes.

Again: this is a very simple, highly powerful weapon.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top