Step 2: Moving database to the new host should be consisting of a two simple step. Step 2.1: Export your database to a file. Step 2.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 the error problems and entire process of moving magento database from one host to another.
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://xxx and your live site is located at http://yyy.
Second step for you would be to open the backup file you created at first step and do Search/ReplaceAll from “xxx” to “yyy”. Magento stores complete url paths inside the database. Therefore you’ll end up with database full of url paths. My backup file had total more than 100000 occurrences of “xxx”. 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://xxx would look for links of http://yyy like http://xxx/some-product-name. This off course isn’t good.
If you finish searching/replacing 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 13MB in size while .zip was 500KB. During the import you should not get any error messages.
Step 3: Having in mind that the root of your Magento is http://yyy you now need to open http://yyy/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.
Step 4: Clear old cache by empty the following folders: var/cache and var/session.
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://yyy/store/ then you’ll have to write following into .htaccess file “RewriteBase /store/” and don’t forget your .sql Search/replace all to set it now replace of “http://xxx” with “http://yyy/store/” since you’re dumping Magento into sub folder.
Step 5: This step is neccessary in the case you are using Magento 1.3 and moving to the host with PHP 5.3 version only. If you are using Magento 1.4 or your host is still using PHP 5.2, things should be fine now. You should enable Mage::setIsDeveloperMode(true); in your index.php file to see where is the problems. The problem caused by 2 functions which are deprecated in PHP 5.3: ___toString to __invoke and split() to explode().
Try to change in file /lib/Varien/Object.php on line ~ 484
public function ___toString(array $arrAttributes = array(), $valueSeparator=',')To this
public function __invoke(array $arrAttributes = array(), $valueSeparator=',')And in file /app/code/core/Mage/Core/Controller/Request/Http.php on line 274
$host = split(':', $_SERVER['HTTP_HOST']);to
$host = explode(':', $_SERVER['HTTP_HOST']);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