How to transfer large Magento database from live to development server and other way round

By: Branko Ajzele, Last updated: Jan 08, 2011 | Published: May 26, 2009

I have been involved in Magento development for almost a year now. God knows I had (and still have) my moments of pain with it :). If you are in professional, everyday, PHP development that focuses mainly on Magento then your life probably isn’t all flowers and bees. Magento is extremely rich eCommerce platform, but its main downside IMHO is its size and code complexity. If you download Magento via SVN, you will sound find out it has around 11 600 and more files. This is no small figure. Transferring that much of files over the FTP can be a real night mare. Luckily we have SSH and tar command to handle this really neat.

But what about database. Today I worked on database with more than 20 000 products in store and with extremely large number of categories. What seemed like easy database transfer from live site to local developer machine to do a test and fix on few issues tunerd out to be an issue for itself. Without further delay, here is my favorite tool to handle all database related work from now on: Navicat.

Among my favorite features is the Data transfer. Directly moving one database to another among different MySQL servers works like a charm. I find yourself strangled among often database recommended action I suggests you test the trial version of this tool.

Here are some screenshots of Navicat Data transfer in Action.

Published in:

12 comments

Daniel says:
Oct 03, 2013 at 14:20
Hi,
Are there any tables in Magento, that can be safely truncated, before you transfer the database from live to development?
Reply
Mariusz says:
Jul 22, 2013 at 16:06
@Branko Ajzele,
Do you changing any settings when you do data transfer in Navicat? When I do it my store id is wrong and admin page has 404 etc. Many Thanks
Reply
Simon says:
Jul 02, 2013 at 8:56
You can also use the “official” tool for it: MySQL workbench.
http://dev.mysql.com/downloads/tools/workbench/
There is, of course, also MySQL dumper:
http://www.mysqldumper.de/
But, I can’t get it to work properly with databases > 1G
Reply
Robert says:
Apr 27, 2012 at 21:20
@TYV,
Do you know a way to add just the catalog products and categories I had in the old one, in a new magento 1.7 freshly installed?
That would be easy, and apart from the products there isn’t much to redo. All I need is to import the right table files and the old magento media folder files, but I don’t know exactly what..
Thanks for your help,
Robert
Reply
Robert says:
Apr 27, 2012 at 20:42
Hello TYV,
Thanks very much for your prompt answer, I need to do this to save 6 months of work.. do you have an e-mail adress or IM adress we can chat over? In case I’ll be stuck..
Best Regards,
Robert
Reply
Tyv says:
Apr 27, 2012 at 11:06
Hello Robert,
I don’t know if this will help you, but I have been made good experience with it.
I’ve tested a lot of PHP based tools to do a backup of my Magento CE 1.4.2.0 Database.
Nothing will work fast enough or I run into time outs.
Last thing I think is to do it via SSH console directly trough MySQL tools like mysqldump/mysql.
I switch often from live system to development, and copy my html and sql files to my localhost test server with these commands:
```
mysqldump -h SERVER -u USER -p --single-transaction -q --password=PASSWORD DATABASE | bzip2 > some_backup_2012-04-26.sql.bz2
```
After creating a Database I will import it simply with
```
bzip2 -d some_backup_2012-04-26.sql.bz2
mysql -u USER -p DATABASE < some_backup_2012-04-26.sql
```
To get it work directly, there must be changed 2 fields in the database:
```
web/unsecure/base_url
web/secure/base_url
```
that point to your new WebServer. Don’t forget to delete your var/cache/* Folder.
After setting up my local.xml file in app/etc/ all runs fine.
Little hint to get a smaller Database:
In Magento CE 1.4.2.0 there is a nice tool to clean your database a bit.
With
```
php -f webroot/shell/log.php -- status
```
you will see how much data is in your tables:
```
log_customer                   
log_visitor                    
log_visitor_info               
log_url                        
log_url_info                   
log_quote                      
report_viewed_product_index    
report_compared_product_index  
report_event                   
catalog_compare_item           
```
and you can clean it a bit out to save space.
I hope this will help you.
So long
Tyv.
Reply
Robert says:
Apr 26, 2012 at 17:48
Hello,
I have a big problem, I need to urgently restore my magento shop with all the content. Before I ruined everything I made a backup of the whole magento folder and a sql/xsv export of the database.
Now I placed the magento folder in my httpdocs as it was before, and I imported the sql dump file in the database. But when I try to open the website it doesn’t work, or sometimes the database seems to auto update with tables and only a standard magento home page comes up.
What should I do?? Thanks a lot!
Reply
Branko Fan says:
Jul 28, 2010 at 8:41
I know this is an old post, but I hope you monitor comments. I’ve used navicat for years (just not with crapgento). I cannot get data transfer to work (without crashing magento). I’m still using SQL dumps. There’s another panel of options in navicat’s data transfer dialog, you didn’t take a screen shot of. It has the nitty gritty details of the data transfer (like fk constraint, autoincrement, etc). Can you please post what extra options you’re using, because the defaults don’t work for me.
Reply
Branko Ajzele says:
May 28, 2009 at 7:43
@Scott I fully agree with SSH and god knows I do lot of my export import with of database with pure console mysql tool. However, sometimes it gives me such a headache.
@B00MER I love open source, I am for open source, but being open source sometimes is not enough. Personally I love NaviCat because I had great experience with it and it does the job for most of the time. In professional web development price of 100-200$ for a software should not be high if the software is stable and does the job.
Reply
Tomas Novoselic says:
May 28, 2009 at 7:24
@Scott, that was first thing that cross our mind ofc, but it just didn’t work for as at that moment 🙁
Reply
B00MER says:
May 27, 2009 at 23:25
I like NaviCat as well but a good free open source alternative is: http://www.heidisql.com/screenshots.php
Reply
Scott says:
May 26, 2009 at 14:39
Here is a good article on how to do it with ssh as well straight from magento. I have had great luck with moving large databases with this method. http://www.magentocommerce.com/wiki/groups/227/moving_magento_to_another_server
Reply

You made it all the way down here so you must have enjoyed this post! You may also like:

External database connection in Magento

Magento Switchable Install Script Setup Class

Magento tip: Get a small sized version of a large production database

12 comments

Leave a Reply Cancel reply