How to take backup of MySQL database using Python?

In this article, we are going to learn how to back up a MySQL database using python.

Database used:

Python modules required:

In this post, we are going to use the mysql-connector module of python to back up our data. You can install mysql-connector with the following command:

pip install mysql-connector

Backing Up Database:

So, it’s a fairly simple task to backup our database.

What we will do is, first of all, create a connection to the database (which we want to backup) using the mysql-connector module. Then we will create an instance of the cursor object obtained through that connection. Then we will first fetch all the table names using the “SHOW TABLES ” command of SQL and execute the method of the cursor object. 

Example:

Python3

import mysql.connector as m

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

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])

tables in the db

Note: The cursor.fetchall() method returns a list of tuples with table names as its elements. And we are running a for loop on that and storing the first element of that tuple (which is our table name) in our list so as to get a plain list that consists of all the table names.

Then we will create our backup database using the “CREATE” command of SQL and cursor object. We will do this in a try block so that if the created database already exists then it won’t return any error. The code for the above looks like this:

Python3

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

This will create our backup database. Now we will simply run the “USE” command with the cursor object to use the backup_dbname database instead of our current database.

Python3

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

This will change our current database to backup_dbname. So, in SQL when we run the following command we can create a copy of the table1 into a newly made table2.

CREATE TABLE table2 SELECT * FROM table1;

So, what we will do in our python code is that we will run a for loop on all our table names and execute a “CREATE” command for that table name (and make sure we are now using the backup_dbname as our current database and it does not have any tables as of now) and we will create a query, similar to the above and copy the table into the newly made table.

The code for that is:

Python3

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

Notice that we are referencing the original table through {db}.{table_name} as it exists in another database as our current database is backup_dbname.

So, the full picture of our code looks like this:

Python3

import mysql.connector as m

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

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

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

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

And running this code we create our backup database with all the tables and data in them. The geeksforgeeks_backup database (which we created in the process) looks like this: