Python3 MySQL backup tool with mysqldump

Last week I wrote a blog post about creating a MySQL backup system with Python. With this system we would establish an SSH connection between the server and your computer, then we’d run mysqldump on the server and transfer the backup to your local machine. In this post I’m taking another approach. Now we’re going to run mysqldump on the local machine, which connects to the remote mysql server.

You can find my previous blog post about MySQL backups here.

Both implementations have some advantages and disadvantages. With this implementation, you don’t have to have ssh access to your database server, but the database server has to be reachable from the outside world. With this implementation mysqldump will run on your local machine, so you’ll have to have it installed of course.

To get started, we’ll install mysql-client locally. This will install mysqlump as well.

On Ubuntu you install mysql-client by running the following command.

When that’s done, we can start writing the code. Start with importing subprocess and time.

Then we’ll write the main backup function.

That’s already the complete backup function. Finally add the following lines to the program. This will allow you to start the backup process. Don’t forget to change “HOSTNAME”, “USERNAME” and “PASSWORD” with your own MySQL database credentials.

If you don’t have access to your database remotely, you can change the following line in your MySQL database config files to make this possible. Keep in mind that you’ll have to open the necessary ports as well. Both on your firewall as on your router. MySQL is configured with port 3306 as default.

Your database user has to be configured with remote permissions, as well as SELECT and LOCK permissions.

1 Comment

  1. Pingback: URL

Leave a Reply

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

Protected with IP Blacklist CloudIP Blacklist Cloud