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[1].items():
            mtime = time.mktime(time.strptime(item[1].get('modify'), "%Y%m%d%H%M%S"))
            if mtime < now - d * 86400:
                ftp_connection.delete(path + item[0])

### 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