Remote MySQL database backup with local Linux shell
There is a lot of talk about a lot of automation tools these days. Truth is though that you can cover a lot by simply using a normal shell script and some common Linux tools.
Below is an example what will 1) drop the local database and create a fresh one from scratch, 2) login to a remote server and backup the corresponding database there, 3) download it and import it so that I have a fresh local copy.
rm domain.sql
mysql -u root --password=123456 -e 'DROP DATABASE domain; CREATE DATABASE `domain` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;'
ssh root@domain.com 'rm domain.sql.bz2'
ssh root@domain.com 'mysqldump domain --user root --password=123456 > domain.sql'
ssh root@domain.com 'bzip2 domain.sql'
scp root@domain.com:/root/domain.sql.bz2 domain.sql.bz2
bzip2 -d domain.sql.bz2
mysql -u root --password=123456 --default_character_set utf8 domain < domain.sqlKey tools here is command line mysql, mysqldump, ssh, scp and bzip2. The bzip2 stuff is optional but I need it on some of my databases since they can be over 1 GB uncompressed but only a few tens of MB compressed. I’d rather let my CPUs do the work than the networks.
Below is an example of the reverse, this is a great one when synchronizing from my local development laptop to the remote staging server.
rm domain.sql
rm domain.sql.bz2
mysqldump domain --user root --password=1234 > domain.sql
bzip2 domain.sql
scp domain.sql.bz2 root@domain.com:/root/domain.sql.bz2
ssh root@domain.com 'bzip2 -d domain.sql.bz2'
ssh root@domain.com './reset_db.sh'
ssh root@domain.com 'mysql -u dblogin --password=password --default_character_set utf8 domain < domain.sql'Note that the contents of reset_db.sh is basically line #2 in the first listing above.