Fabfile for pulling remote MySQL databases
December 3, 2014
I’m a firm believer in automating any task that I’m going to do more than once. Frequently I need to sync the databases on my local development environment to a remote production environment. Here’s a quick fabric fabfile I wrote to pull one or more databases from a remote server and use them to overwrite my corresponding local database(s).
Code
fabfile.py:
1 from fabric.api import local, run, env
2
3 env.hosts = ['remote_ssh_host']
4 env.user = 'remote_ssh_user'
5
6 databases = [
7 {
8 'local': {
9 'name': 'db1_local_name',
10 'user': 'db1_local_user',
11 'password': 'db1_local_password'
12 },
13 'remote': {
14 'name': 'db1_remote_name',
15 'user': 'db1_remote_user',
16 'password': 'db1_remote_password'
17 }
18 },
19 {
20 'local': {
21 'name': 'db2_local_name',
22 'user': 'db2_local_user',
23 'password': 'db2_local_password'
24 },
25 'remote': {
26 'name': 'db2_remote_name',
27 'user': 'db2_remote_user',
28 'password': 'db2_remote_password'
29 }
30 }
31 ]
32
33
34 def pull_databases():
35 global databases
36
37 # backup & gzip all remote databases
38 for index, database in enumerate(databases):
39 run("mysqldump -u "+database['remote']['user']+" -p'"+database['remote']['password'] +
40 "' "+database['remote']['name']+" | gzip -9 > temp_db"+str(index)+".sql.gz")
41
42 # secure copy to local environment
43 local("scp "+env.user+"@"+env.hosts[0]+":temp_db*.sql.gz .")
44
45 # copy remote backups to local database
46 for index, database in enumerate(databases):
47 local("zcat temp_db"+str(index)+".sql.gz| mysql --user='"+database['local']['user'] +
48 "' --password='"+database['local']['password'] +
49 "' --database "+database['local']['name'])
50
51 # cleanup files from local & remote environments
52 run("rm temp_db*.sql.gz")
53 local("rm temp_db*.sql.gz")Closing remarks
Insert typical disclaimer: This is just example code for my specific setup. Potential issues to look for are the use of additional env.hosts values, and naming conflicts, among other things. Also, login previously setup via ssh key. Please read the code and make sure you understand it before getting your execute on.