Python Script: Taking mysql backup and moving backup to a remote server

Hello Friends,

Below is the script to take mysql backup and moving it to a remote server.


#!/root/python/Python-3.3.0/python

import os
import time
import datetime
date1 = time.strftime('%Y%m%d%H%M%S')
f = open('/root/mypython/dblist.txt') # this files contains the name of databases
line = f.readline()
f.close()
words = line.split()
for word in words:
    cmd = "mysqldump -u root -pabhishek {0} > {0}_{1}.sql".format(word, date1) # takes backup in the same location as script
    cmd2 = "zip {0}_{1}.zip {0}_{1}.sql".format(word, date1) # zips the backup just taken
    cmd3 = "rm -f {0}_{1}.sql".format(word, date1) # deletes the .sql backup just taken. after this step only .zip backup remains. remove this line if u need both .sql and .zip
    cmd4 = " scp {0}_{1}.zip root@192.168.1.105:/home/laitkor/dev_test ".format(word, date1)
    os.system(cmd)
    os.system(cmd2)
    os.system(cmd3)
    if os.system(cmd4)== 0:
        result = "Backup Successful"
print(result)


_______________________________________________________________

Things to be remember

1. The cmd4 command do a scp. You must have key based login enable from your server to remote server otherwise you will be asked password of remote server, which the script can't provide.

To enable key based login, see below:

http://pc2solution.blogspot.in/2013/03/how-to-enable-key-based-login-in-linux.html

2. The first line of the script makes it executable, so for scheduling backup, you just need to enter full path in crontab, e.g. if you want to run this script every ten minutes do following entry in crontab 
*/10 * * * * /home/myscript.py

3. Make script executable by chmod 777 or whatever permission you want.