02 Jun

How to copy your Magento Database to your Developement VPS

This is a step by step How To, on getting your Magento Database exported from your production server into your development server. It has some more advanced dumps if you want to keep some of your core tables intact on your development server. I usually do at least core_config_data, because I don’t want production settings on my development server. Things like search indexing, template settings, developer settings.


#1)
#SSH to the server you want to copy the database from
#[ssh template] - replace [IP] with actual server IP
ssh root@[IP]

#2)
#Find the database credentials from the database that you are copying in app/etc/local.xml
#[nano template]
nano [path to web root]/app/etc/local.xml
#In this file look for node and find the nodes
#
#
#
# #
# 1
#

#3)
#Use the above information to fill in the following command template
#[mysqldump template]
#mysqldump -u [username] -p [dbname] > /tmp/dump.sql

#Using the above credentials found inside local.xml we get this command
#(notice that database username and database name are usually identical
#and that the password field is left empty, it will prompt you for the password)
mysqldump -u mage -p mage > /tmp/dump.sql

#Once the dump is complete you will have a file inside /tmp directory with the name dump.sql

#Here is a more advanced versions of mysqldump

#If you want to dump everything but "core_config_data" make sure to add table prefix if you use it
#[mysqldump ignore table template]
mysqldump -u [username] -p [dbname] --ignore-table=[dbname].core_config_data > /tmp/dump.sql

#If you want to update everything but core tables
#Notice "core_%" make sure to put any table prefix you might be using for magento if you use them like "mg_core_%"
mysqldump -u [username] -p [dbname] $(mysql -u [username] -p -D [dbname] -Bse "show tables where tables_in_[dbname] not like 'core_%'") > /tmp/dump.sql

#5)
#We now need to copy this file to the second server
#Use scp command to securely copy the dump file you created on the first server to the second server
#[scp template]
scp /tmp/dump.sql root@[2nd server's IP]:/tmp/dump.sql

#6)
#Exit first server
exit

#7)
#SSH to second server
#[ssh template]
ssh root@[IP]

#8)
#If you are updating a developement database (which is in most cases what you need this for)
#locate the database credentials like in step 2
#Otherwise if you create the database and use the credentials you entered when creating your database
#[mysql upload template]
#mysql -u [username] -p [dbname] < /tmp/dump.sql #Notice that the command is close to the dump command with the exception of the command #"mysql" instead of "mysqldump" and the direction "<" instead of ">"
#using the same credentials from step 2 we would have this command
#again you will be prompted for your database password
mysql -u mage -p mage < /tmp/dump.sql #9) #You are finished, although you will probably want to clear any cache on the second server #[rm template] rm -rf [path to web root]/var/cache/* #10) #Exit second server exit

Share this