python 脚本备份 mysql 数据库到 OSS
脚本如下：
1 #!/usr/bin/python 2 ########################################################### 3 ########################################################## 4 5 # Import required python libraries 6 import os 7 import time 8 import datetime 9 10 # MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup. 11 # To take multiple databases backup, create any file like /backup/dbnames.txt and put databses names one on each line and assignd to DB_NAME variable. 12 13 DB_HOST = 'localhost' 14 DB_USER = 'root' 15 DB_USER_PASSWORD = '' 16 DB_NAME = '/home/dbbackup/dbnames.txt' 17 #DB_NAME = 'db_name' 18 BACKUP_PATH = '/mnt/backup/mysql/' 19 20 # Getting current datetime to create seprate backup folder like "12012013-071334". 21 DATETIME = time.strftime('%Y%m%d-%H%M%S') 22 23 TODAYBACKUPPATH = BACKUP_PATH + DATETIME 24 25 # Checking if backup folder already exists or not. If not exists will create it. 26 print "creating backup folder" 27 if not os.path.exists(TODAYBACKUPPATH): 28 os.makedirs(TODAYBACKUPPATH) 29 30 # Code for checking if you want to take single database backup or assinged multiple backups in DB_NAME. 31 print "checking for databases names file." 32 if os.path.exists(DB_NAME): 33 file1 = open(DB_NAME) 34 multi = 1 35 print "Databases file found..." 36 print "Starting backup of all dbs listed in file " + DB_NAME 37 else: 38 print "Databases file not found..." 39 print "Starting backup of database " + DB_NAME 40 multi = 0 41 42 # Starting actual database backup process. 43 if multi: 44 in_file = open(DB_NAME,"r") 45 flength = len(in_file.readlines()) 46 in_file.close() 47 p = 1 48 dbfile = open(DB_NAME,"r") 49 50 while p <= flength: 51 db = dbfile.readline() # reading database name from file 52 db = db[:-1] # deletes extra line 53 dumpcmd = "mysqldump -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql" 54 os.system(dumpcmd) 55 p = p + 1 56 dbfile.close() 57 else: 58 db = DB_NAME 59 dumpcmd = "mysqldump -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql" 60 os.system(dumpcmd) 61 62 63 print "Backup script completed" 64 print "Your backups has been created in '" + TODAYBACKUPPATH + "' directory" 65 66 print "current path: " + os.getcwd() 67 os.chdir(BACKUP_PATH) 68 print "current path: " + os.getcwd() 69 compress_file = TODAYBACKUPPATH + ".tar.gzip" 70 print compress_file 71 compress_cmd = "tar -czvf " + compress_file + " " + DATETIME 72 print "compress_cmd " + compress_cmd 73 os.system(compress_cmd) 74 print "Compress success" 75 76 remove_cmd = "rm -rf " + TODAYBACKUPPATH 77 print "remove_cmd " + remove_cmd 78 os.system(remove_cmd) 79 print "Remove success" 80 81 print "Start put OSS" 82 83 import shutil 84 import oss2 85 86 access_key_id = '' 87 access_key_secret = '' 88 bucket_name = 'x-backup' 89 endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com' 90 91 bucket = oss2.Bucket(oss2.Auth(access_key_id, access_key_secret), endpoint, bucket_name) 92 bucket.put_object_from_file('mysql/'+DATETIME+'.tar.gzip',compress_file) 93 print "Put OSS success"
再加上crontab 就搞定了
crontab -e
*/5 * * * * python /home/dbbackup/dbbackup.py >> /home/dbbackup/mylog.log 2>&1
