MySQL (MariaDB) database backup script
If you need to make regular backups of your mysql databases and store them on the remote site here is one way you could do it.
Recently I build up 2 MariaDB servers, one master and the other slave. Alongside this replication I wanted to have daily backups of all the databases. I decided to make
mysqldump on slave server and push it to FTP server. Also I added check for files older then 7 days on FTP server and delete them. Script is in python3 using ftplib module.
There are definitely nicer approaches to this but this is the one I'm using for now. Adjust your FTP and DATABASE variables, and choose retention policy , that is after how many days the files are deleted :) In db_names put the names of all the databases you want to backup.
#!/usr/bin/env python3 # -*- coding: utf-8 -*- ''' File name: backup.py Author: Zoran Zorica Version: 0.5 ''' import os import time import ftplib ### FTP #### server = 'ftp.example.com' username = 'ftpuser' password = 'ftppasswd' ### remote path - notice / at the end for later delete full path path = "/ftp/mysql/" ### DATABASE ### db_host = 'localhost' db_user = 'dbuser' db_password = 'dbpasswd' db_names = [ 'database1', 'database2', 'database3', 'database4' ]; ### delete older then x days d = 7 ### set date to append to file name date = time.strftime('%d%m%Y') ### upload files to ftp server def upload(filename): ftp_connection = ftplib.FTP(server, username, password) ftp_connection.cwd(path) fh = open(filename, 'rb') ftp_connection.storbinary('STOR ' + filename, fh) fh.close() return 1 ### delete files older then x days on ftp server def deleteOld(): ftp_connection = ftplib.FTP(server, username, password) files = ftp_connection.mlsd(path="/ftp/mysql",facts=) now = time.time() for item in files: if ('type', 'file') in item.items(): mtime = time.mktime(time.strptime(item.get('modify'), "%Y%m%d%H%M%S")) if mtime < now - d * 86400: ftp_connection.delete(path + item) ### mysqldump all databases defined in db_names, upload them to ftp server, at the end delete local file for name in db_names: filename = name + "-" + date + ".sql" dumpcmd = "mysqldump -u" + db_user + " -p" + db_password + " " + name + " > " + filename os.system(dumpcmd) upload(filename) os.system("rm " + filename) ### delete files older then x on ftp server deleteOld()
Afterwards just make cron rule to run the script at specific time:
0 0 * * * /usr/bin/python3 /data/sql_backup/backup.py