Copying a Magento Database from one server to another using mysqldump / scp / mysql

mysqldump-scp-mysql

#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 <connection> node and find the nodes <username> <password> <dbname>
#                <connection>
#                    <host><![CDATA[localhost]]></host>
#                    <username><![CDATA[mage]]></username>
#                    <password><![CDATA[12345678]]></password>
#                    <dbname><![CDATA[mage]]></dbname>
#                    <active>1</active>
#                </connection>

#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