Transferring databases between servers

Transferring and deploying databases between servers is very simple to do when using SSH and SSH keys. Automatic scripts can be setup and enables you to be much more productive in producing websites. Below is a script that I used to transfer a database between our development and live servers.

/usr/bin/ssh -i /var/www/.ssh/id_dsa username@server "mysqldump -u username --password=password database | gzip > ~/backups/database_name.sql.gz;" scp "/usr/bin/ssh -i /var/www/.ssh/id_dsa" sername@server:backups/databasename.sql.gz /var/www/databases/; gzip -d /var/www/databases/database_name.sql.gz; mysql -u root database_name < /var/www/databases/database_name.sql; rm /var/www/databases/database_name.sql; /usr/bin/ssh -i /var/www/.ssh/id_dsa username@server "rm ~/backups/database_name.sql.gz; exit"

Replace the username, password and server locations with your own. If you mix around the statements you should be able to have scripts that go from live to development and also to your staging servers. This should mean all transferring of database data is automatic.