python-mysqlHow do I create a backup of a MySQL database using Python?

Creating a backup of a MySQL database using Python is a simple process. The following example code shows how to do it:

import MySQLdb

# Connect to the database
db = MySQLdb.connect(host="localhost", user="username", passwd="password", db="database")

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

# Create the backup file
file = open("backup.sql","w")

# Get all of the tables
cursor.execute("SHOW TABLES")

# Get the table names and write them to the file
tables = cursor.fetchall()
for table in tables:
    file.write("DROP TABLE IF EXISTS %s;\n" % table[0])
    cursor.execute("SHOW CREATE TABLE %s" % table[0])
    create_table = cursor.fetchone()
    file.write("%s;\n" % create_table[1])

# Close the connection
db.close()

This code will create a backup file called backup.sql which contains the SQL code to recreate the database tables.

The code consists of the following parts:

1. Import the MySQLdb module: This module provides access to the MySQL database.
2. Connect to the database: This is done using the MySQLdb.connect() method.
3. Create a cursor object: This is used to execute SQL queries.
4. Create the backup file: This is done using the open() function.
5. Get all of the tables: This is done using the SHOW TABLES SQL query.
6. Write the table names to the file: This is done using a for loop.
7. Close the connection: This is done using the db.close() method.

For more information, see the MySQLdb documentation.

Edit this code on GitHub