Create a MySQL backup tool with Python3

MySQL databases are used quite a lot in the world. Most of the web applications that are built with PHP use MySQL for their database needs. Because of the excellent compatibility between PHP and MySQL. Since databases are used to, well, store data. When your application heavily relies on this data, it’s massively important that the data is kept safe. And that’s what we’re doing here.

In this tutorial I’m going to show you how to built a MySQL database backup tool that operates over SFTP. Of course this is only useful when your database server supports ssh and is configured with MySQL. We’ll be using Python3 to implement the backup tool.

For SSH and SFTP support we’ll be using pysftp, which requires Paramiko to run. So let’s start with installing Paramiko. Run the following command in the terminal to install Paramiko.

If Python pip is not installed you can do this by running the following command:

When Paramiko is installed, download pysftp here. Replicate the following file/folder structure on your machine.

 

When that’s all done, we can start with our python script.

Open mysqlbackup.py, which you created a moment ago. Add the following two lines to import the necessary packages.

Then we can write a runbackup function, which will backup all databases for a specified hostname/server.

To end things off, we only have to write a main function which will start the backup cycle for every defined host.

You can run

runbackup("HOSTNAME", "SSH USER", "SSH PASSWORD", "MYSQL USER", "MYSQL PASSWORD");

for every server you want to backup.

The full code:

 

2 Comments

  1. Pingback: Python3 MySQL backup tool with mysqldump | Daan Lenaerts

  2. Pingback: MySQL database backup with Cron | Daan Lenaerts

Leave a Reply

Your email address will not be published. Required fields are marked *

Protected with IP Blacklist CloudIP Blacklist Cloud