This article has been closed for comments on March 17th, 2009. Approach below has been shown to work. If you experience unexpected results following this approach, then please revise the search/replace string you provided while going trough .sql file. Keep in mind real root url path to dev/live Magento installation.

Maybe this post should have been named Magento MySQL table export, import, and 1452 error. Here’s why. For the last hour and a half I’ve been trying to transfer Magento database from one server to another. I’ve been getting MySQL 1452 error during the import all the time. My export gone pretty smoothly, no errors at all. I’ve exported using default settings of my WAMP servers phpMyAdmin application. Moving a database should be consisting of a two simple step. Step 1: Export your database to a file. Step 2: Import your database from a file to new MySQL server. Sounds easy, right. Well it is. At least it should be.

I wonder is it my bad karma, or what is it when it comes to Magento? Personal lack of experience? Whatever it is, result can sometimes be quite time consuming. Anyway here is my solution to MySQL 1452 error problem and entire process of moving magento from development server to live server.

First, make a backup of entire Magento database with all the default phpMyAdmin options. Backup your database to .sql file.

Let’s suppose your development site is located at http://dev.site.com and your live site is located at http://livesite.com.

Second step for you would be to open the backup file you created at first step and do Search/ReplaceAll from “dev.site.com” to “livesite.com”. Magento stores complete url paths inside the database. Therefore you’ll end up with database full of url paths. My backup file had total of 17300 occurrences of “dev.site.com”. Huge number.

Anyway this is a must do. There is no point at importing database to new server if you haven’t done this replacement since Magento pools links from database. So if you haven’t done replacement and have done import then basically your Magento on http://livesite.com would look for links of http://dev.site.com like http://dev.site.com/some-product-name. This off course isn’t good.

If you search/replaced your .sql file do not rush to import it. If you try to import file as is, you’ll most likely get some errors from MySQL concerning foreign key constraint an so on. I lost about 3 hours of mine time testing and checking things out to get around this. Here’s how. Open your .sql backup file again in your favorite editor and do the following steps.

Place these lines of SQL code on very top of the .sql file:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

Place these lines of SQL code on very end of the .sql file:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;

Now you’re ready to import your .sql file to live MySQL database. Or not? I suggest you ZIP your .sql file and then do import of .zip file trough phpMyAdmin. At least you’re upload will be faster. For example my .sql file was 8MB in size while .zip was 500KB. During the import you should not get any error messages.

Ok, for now you should have copied entire root folder content of your Magento from dev.site.com to root folder of livesite.com. There is only one more thing to do before you can open you’re page in browser.

Having in mind that the root of your Magento is http://livesite.com you now need to open http://livesite.com/app/etc/local.xml file and modify it’s database connection information accordingly to your new database in livesite.com. Save the changes open your browser to livesite.com. Your Magento should be working.

This guide should get you up n running if you’re moving you’re site from one hosting to another. There are few more things to keep in mind it the site is not working. First you need to check folder write permissions trough FTP. Folders like /app, /app/etc, /var, /var/log, /var/session and so on, need to have appropriate access rights (755, 775, 777). Play around with it. There is one more thing that can cause problems. That is .htaccess file. If you’re moving a file from one site to another, and dumping it into same folder, then you probably won’t need to change this. But if you’re dumping it into some sub folder like http://livesite.com/store/ then you’ll have to write following into .htaccess file “RewriteBase /products/” and don’t forget your .sql Search/replace all to set it now replace of “http://dev.site.com” with “http://livesite.com//products/” since you’re dumping Magento into sub folder.

Hope you make it; I did. It took me 7 hours to do something it takes 2h max. However, I’m ready for the round two now.

Feel free to post comments. I could use some community feedback