How to Backup MySQL Database in Python

Database administrators need to regularly backup their MySQL database for security and compliance purposes. It allows you to easily recover your data in case of data loss or corruption. Python is a powerful programming language used by many system administrators to perform administrative tasks. It allows you to easily create and run scripts to automate various administrative tasks, including database backups. In this article, we will learn how to backup MySQL database in Python.

Why Backup MySQL Database in Python

There are several reasons why it is important to regularly backup MySQL database in Python.

1. Data Security – Backups protect your database from data losses and corruption caused due to malicious attacks.
2. Data Integrity – In case of issues, it is easy to rollback changes made since last backup. This ensures that your data is clean and usable.
3. Easy Management – Each database backup is a checkpoint that allows you to consistently develop your websites and applications that depend on it. They can be used to easily manage your data without depending on third-party data management solutions.
4. Testing – You can easily run tests on a copy of your backup database, instead of touching your production data.
5. Compliance – Many industries such as banking and finance require administrators and business owners to follow data security best practices. Setting up data backup process goes a long way in helping your organization be compliant.

How to Backup MySQL Database in Python

There are 3 main solutions to backup MySQL database in Python

1. Using Subprocess

Subprocess is a very useful Python module that allows you to easily run shell commands from within Python interpreter. It allows you to pipe commands, input, output and even get return value of executed command.

Every MySQL installation ships with a mysqldump command line utility that allows you to easily backup MySQL database from shell. Here is the command to backup MySQL database.

$ mysqldump -h<host> -u<username> -p<password> <database_name> > <path_to_backup_file>

Using subprocess module, you can directly run the above shell command from within Python. Here is a simple script to run the above command from Python, using subprocess. For that, let us create a new script first.

$ sudo vi db_backup.py

Add the following lines to it.

import subprocess

# Database connection details
host = "localhost"
user = "username"
password = "password"
database = "database_name"
backup_file = "/home/ubuntu/backup.sql"

# Run mysqldump command
command = f"mysqldump −h{host} −u{user} −p{password} {database} > {backup_file}"
proc = subprocess.run(command, shell=True)

if proc.returncode == 0:
    print("Database backup success")
else:
    print(f"Database backup failed - return code {proc.returncode}.")

Save and close the file. In the above code, we first import subprocess module. Then we define database connection details such as host, username, password, database name, etc. and also full path to backup file. Please make sure you mention the full path to backup file. Otherwise, the backup file will be created in the same directory as your Python script.

Then we define the MySQLdump command. We run this command using subprocess.run() function. We store the output returned by this command in proc variable. Depending on the return value, we display the final message about the success/failure of MySQL database backup.

You can easily run this script with the following command.

$ python db_backup.py

Similarly, here is the command to backup all databases on your system.

$ mysqldump -h<host> -u<username> -p<password> --all-databases > <path_to_backup_file>

You can modify the command variable in above script to the following to incorporate the above command.

command = f"mysqldump −h{host} −u{user} −p{password} --all-databases > {backup_file}"

On the other hand, here is the command to backup specific MySQL tables t1, t2, t3 from a database.

$ mysqldump -h<host> -u<username> -p<password> <database_name> t1 t2 t3 > <path_to_backup_file>

You can modify the command variable to the following to incorporate the above command.

command = f"mysqldump −h{host} −u{user} −p{password} {database_name} t1 t2 t3 > {backup_file}"

You can also use subprocess.popen() command to run shell commands from Python, depending on your convenience.

2. Using Mysql-connector

Alternatively, you can use a Python library to connect to your database and backup only those tables or rows that you need. Let us see how to do this.

For this purpose, we will use mysql-connector library. It is a pure Python-based MySQL library that allows you to connect to MySQL databases, run SQL queries and use their results. You can install it with the following command.

$ pip install mysql-connector

Once it is installed, you can create a script to backup your tables. Let us say you want to backup all tables from database db1 to another database db2. We have assumed that both database already exist on your system.

For this purpose, create an empty Python script.

$ sudo vi db_backup.py

Add the following lines to it. Replace <host>, <username>, <password> with the host, username and password of your database.

import mysql.connector as my 

# database name
db = 'db1'
host_name = <host>
username = <username>
password = <password>

conn = my.connect(host=host_name, user=username, password=password, database=db) 
cursor = conn.cursor() 

# Get all table names 
cursor.execute('show tables;') 
tbl_names = [] 
for table in cursor.fetchall(): 
	tbl_names.append(table) 

The above code connects to your MySQL database db1, retrieves a list of all its tables. Next, we add the code to backup tables from database db1 to db2.

backup_db = 'db2'
cursor.execute(f'USE {backup_db}')

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

Save and close the file. In the above code, we switch to the db2 database first and then run the following SQL query (CREATE TABLE…SELECT FROM) for each of the table names returned from db1. It will create a new table in db2 with the same name as that in db1 and copy its data from db1 table to db2 table.

CREATE TABLE table2 SELECT * FROM table1;

Run the script with the following command.

$ python db_backup.py

This provides a couple of massive advantages. First of all, you can choose to ignore certain tables during backup by modifying the for loop in above script. Here is an example to avoid backup of table t123.

for tbl_name in tbl_names: 
    if tbl_name != 't123':
        cursor.execute(f'CREATE TABLE {tbl_name} SELECT * FROM {db}.{tbl_name}') 

On the other hand, if you want to backup only certain rows of your tables, then you can modify the CREATE TABLE…SELECT FROM query in cursor.execute() function. Here is an example to backup only 1000 rows from each table.

for tbl_name in tbl_names: 
    cursor.execute(f'CREATE TABLE {tbl_name} SELECT * FROM {db}.{tbl_name}' LIMIT 1000) 

You may also use WHERE clause in above CREATE TABLE…SELECT FROM query to backup only specific rows.

3. Using Pyodbc

Both the above solutions work for only MySQL database. But what if you want to create a solution that can be easily modified to work for multiple database types? In such cases, you can use an ODBC (Open Database Connectivity) library like pyodbc that easily communicates with all major database types such as MySQL, PostgreSQL, Oracle and SQL Server. It allows you to easily modify your backup script, in case you choose to switch from MySQL database.

You can install pyodbc using the following command.

$ pip install pyodbc

Then create a new script.

$ sudo vi db_backup.py

Add the following code to it. Modify the database connection parameters as per your requirement. You need to also mention ODBC driver name in your system, for driver variable. You can check your driver name in Windows -> Control panel -> System and security -> Administrative tools -> ODBC Data Sources -> Driver tab.

import pyodbc

# Connection parameters
server = 'host'
database = 'db1'
username = 'db_username'
password = 'db_password'
driver = 'MySQL ODBC 5.3 ANSI Driver'

# Create a connection object
conn = pyodbc.connect('DRIVER={' + driver +'};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)

# Create a cursor object
cursor = conn.cursor()

The above code will connect to your database and create a cursor, just like mysql-connector in previous solution. Then add the following code to retrieve a list of all table names from database db1, create new tables with the same names in db2 and populate them using the table data from db1.

# Get table names 
cursor.execute('show tables;') 
tbl_names = [] 
for table in cursor.fetchall(): 
	tbl_names.append(table) 

backup_db = 'db2'
cursor.execute(f'USE {backup_db}')
for tbl_name in tbl_names:
     cursor.execute(f'CREATE TABLE {tbl_name} SELECT * FROM {db}.{tbl_name}') 

Save and close the file. Run it with the following command.

$ python db_backup.py

In this case, if you move your database to a different database service such as PostgreSQL or SQL Server, then all you need to do is change the name of ODBC driver in pyodbc.connect() function and your script will still work.

Please note, the 2 advantages (backup of specific tables and rows) mentioned in case of previous solution, are also available with this one.

4. Automate Database Backup

Now that we have learnt 3 different ways to do database backup from Python, let us go a step further and automate things even more. In all the above cases, you can do database backup with the following command.

$ python db_backup.py

But we need to manually run this command every time we need a database backup. What if we need to automate the script execution itself? You can easily do this using cron jobs. Cron jobs are scheduled tasks in Linux that are automatically executed at pre-defined times. To create a cronjob, open crontab file using the following command.

$ crontab -e

Add the following line to it to execute our python script every day at 10.a.m.

0 10 * * * python /full/path/to/db_backup.py >/dev/null 2>&1

Save and close the file. In the above command, ‘0 10 * * *’ means run the task every day at 10.a.m. It is followed by python command to execute db_backup.py file. Lastly, we redirect any errors and warnings to /dev/null so that script execution is not interrupted.

Conclusion

In this article, we have learnt 3 different ways to do backup of MySQL database from Python. The simplest way is to use subprocess as described in solution #1. It allows you to implement shell commands from Python. If you want to backup only specific rows or data from your database, then you will need to use custom SQL queries for this purpose. In this case, you need to use a library like mysql-connector. If you want to write a backup script that can be easily modified for all major databases, then use a library like PyODBC that works with all databases. Depending on your requirement, you can use any of the above solutions.

Also read:

What Does __name__==__main__ do in Python
What are metaclasses in Python
What Does Yield Keyword Do in Python