Open In App

How to take backup of MySQL database using Python?

Improve
Improve
Like Article
Like
Save
Share
Report

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:



Last Updated : 21 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads