How to Take Backup of MySQL Database in Python

Introduction

Python is a powerful language that allows you to do tons of things. It even allows you to connect to databases and run SQL queries. Sometimes you may need to take backup of MySQL database in python application, or need a python script for the same. In this article, we will learn how to take backup of MySQL database in Python.

How to Take Backup of MySQL Database in Python

We will use mysql-connector module to backup MySQL database. Here are the steps to create a MySQL backup in Python. Please note, in this article, we will create a separate database for backup, and copy data from source database to target database. If you need to create a MySQL data dump, then it is advisable to use mysqldump tool directly from your terminal or command prompt.

Install Required Module

Open terminal and run the following command to install mysql-connector module:

pip install mysql-connector

List Database Tables

Next, we create a python script backup.py to create backup:

nano backup.py

Add the following lines to this script. Replace database name and connection details as per your requirement.

import mysql.connector as m

# database which you want to backup
db = 'testdb'

connection = m.connect(host='localhost', user='root',
					password='123', database=db)
cursor = connection.cursor()

# Getting all the table names
cursor.execute('SHOW TABLES;')
table_names = []
for record in cursor.fetchall():
	table_names.append(record[0])

In the above code, we first connect to our MySQL database testdb that we want to backup, and create a cursor instance to be able to run subsequent SQL queries. We use the cursor to get a list of all tables in our database. We will store the list of tables in a python list table_names. The method cursor.fetchall() returns a list of tuples with table names in them. So we need to loop through it and extract the plain text table names, to be able to use them in our subsequent SQL queries.

Create Database Backup

Next, we will create a new database testdb_backup where will store the backup of testdb database. We will use CREATE DATABASE command for this purpose. So add the following lines to your python script:

backup_dbname = db + '_backup'
try:
	cursor.execute(f'CREATE DATABASE {backup_dbname}')
except:
	pass

cursor.execute(f'USE {backup_dbname}')

After we create the new database, we run USE statement to switch to the new database. This is required in order to be able to copy data from the other database. Once that is done, we will run the following kind of commands to create a new table table2 and copy data from table1 table:

CREATE TABLE table2 SELECT * FROM table1;

What we will do is loop through the list of table names we created earlier, run the above CREATE TABLE statement for each table. Since we are connected to testdb_backup database which does not have any table yet, each CREATE TABLE statement will create new table in testdb_backup database, with same name as that in testdb database and copy that table’s data to the new table:

for table_name in table_names:
	cursor.execute(
		f'CREATE TABLE {table_name} SELECT * FROM {db}.{table_name}')

Here is the full code for your reference:

import mysql.connector as m

# database which you want to backup
db = 'testdb'

connection = m.connect(host='localhost', user='root',
					password='123', database=db)
cursor = connection.cursor()

# Getting all the table names
cursor.execute('SHOW TABLES;')
table_names = []
for record in cursor.fetchall():
	table_names.append(record[0])

backup_dbname = db + '_backup'
try:
	cursor.execute(f'CREATE DATABASE {backup_dbname}')
except:
	pass

for table_name in table_names:
	cursor.execute(
		f'CREATE TABLE {table_name} SELECT * FROM {db}.{table_name}')

Save and close the file. Make it executable:

chmod +x backup.py

Now you can execute the python script with the following command:

python backup.py

Conclusion

You have now backed up your MySQL Database with Python! You should really save this post for later if you are managing SQL Databases. If you found this usful then please share this and follow me! Also check out my Buy Me A Coffe