Skip to content

Instantly share code, notes, and snippets.

@Fabryz
Forked from magemonkey/mysqldump-scp-mysql
Created June 30, 2016 09:31
Show Gist options
  • Save Fabryz/dc2410af7a6020f454388afd80a95147 to your computer and use it in GitHub Desktop.
Save Fabryz/dc2410af7a6020f454388afd80a95147 to your computer and use it in GitHub Desktop.
Copying a Magento Database from one server to another using 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment