Skip to main content

MySQL

MySQL is a relational database commonly used for application data. DuckDB's MySQL extension can read from MySQL-compatible databases, which lets you copy selected data into MotherDuck from a DuckDB client.

How it works with MotherDuck​

  1. Connect to MotherDuck from the DuckDB CLI, Python, or another DuckDB client.
  2. Install and load DuckDB's MySQL extension in that session.
  3. Attach the MySQL database, then create MotherDuck tables from selected MySQL tables or queries.

Example​

INSTALL mysql;
LOAD mysql;

ATTACH 'host=localhost port=3306 user=my_user password=my_password database=my_database'
  AS mysql_db (TYPE mysql);

CREATE TABLE my_table AS
SELECT *
FROM mysql_db.my_schema.my_table;

Load on a schedule with a Flight​

A Flight is Python that MotherDuck schedules and runs next to your data. Use one instead of running the copy by hand when the load should repeat on a cron, retry on a transient failure, and keep a run history.

The MySQL extension runs in a DuckDB client rather than on MotherDuck's server-side runtime, so the Flight opens an md: connection and installs the extension there:

import os

import duckdb


def main():
    con = duckdb.connect("md:")
    con.execute("INSTALL mysql; LOAD mysql;")
    con.execute(
        f"ATTACH 'host={os.environ['MYSQL_HOST']} port=3306 "
        f"user={os.environ['MYSQL_USER']} password={os.environ['MYSQL_PASSWORD']} "
        f"database={os.environ['MYSQL_DATABASE']}' AS mysql_db (TYPE mysql)"
    )
    con.execute(
        "CREATE OR REPLACE TABLE my_db.main.my_table AS "
        "SELECT * FROM mysql_db.my_schema.my_table"
    )


if __name__ == "__main__":
    main()

The MySQL password is a credential, so keep it in a Flight secret rather than in the Flight's source or config. Each PARAMS key is injected into the run as an environment variable, which is what the code above reads:

CREATE SECRET mysql_creds IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {
        'MYSQL_HOST': '<host>',
        'MYSQL_USER': '<user>',
        'MYSQL_PASSWORD': '<password>',
        'MYSQL_DATABASE': '<database>'
    }
);

Create the Flight with MD_CREATE_FLIGHT, passing the Python above as source_code, flight_secret_names := ['mysql_creds'], and a schedule_cron for the cadence you want. Mirror PostgreSQL tables into MotherDuck with a Flight is a fuller version of this pattern, built on the sibling postgres extension.

Related content​