Skip to main content

MySQL setup

Community plugin

Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.

  • Maintained by: Community
  • Authors: Doug Beatty (https://github.com/dbeatty10)
  • GitHub repo: dbeatty10/dbt-mysql
  • PyPI package: dbt-mysql
  • Slack channel: #db-mysql-family
  • Supported dbt Core version: v0.18.0 and newer
  • dbt Cloud support: Not Supported
  • Minimum data platform version: MySQL 5.7 and 8.0

Installing dbt-mysql

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-mysql

For MySQL-specific configuration, please refer to MySQL configs.

This is an experimental plugin:

  • It has not been tested extensively.
  • Storage engines other than the default of InnoDB are untested.
  • Only tested with dbt-adapter-tests with the following versions:
    • MySQL 5.7
    • MySQL 8.0
    • MariaDB 10.5
  • Compatibility with other dbt packages (like dbt_utils) are also untested.

Please read these docs carefully and use at your own risk. Issues and PRs welcome!

Connecting to MySQL with dbt-mysql

MySQL targets should be set up using the following configuration in your profiles.yml file.

Example:

~/.dbt/profiles.yml
your_profile_name:
target: dev
outputs:
dev:
type: mysql
server: localhost
port: 3306
schema: analytics
username: your_mysql_username
password: your_mysql_password
ssl_disabled: True

Description of MySQL Profile Fields

OptionDescriptionRequired?Example
typeThe specific adapter to useRequiredmysql, mysql5 or mariadb
serverThe server (hostname) to connect toRequiredyourorg.mysqlhost.com
portThe port to useOptional3306
schemaSpecify the schema (database) to build models intoRequiredanalytics
usernameThe username to use to connect to the serverRequireddbt_admin
passwordThe password to use for authenticating to the serverRequiredcorrect-horse-battery-staple
ssl_disabledSet to enable or disable TLS connectivity to mysql5.xOptionalTrue or False

Supported features

MariaDB 10.5MySQL 5.7MySQL 8.0Feature
Table materialization
View materialization
Incremental materialization
Ephemeral materialization
Seeds
Sources
Custom data tests
Docs generate
🤷🤷Snapshots

Notes

  • Ephemeral materializations rely upon Common Table Expressions (CTEs), which are not supported until MySQL 8.0.
  • MySQL 5.7 has some configuration gotchas that might affect dbt snapshots to not work properly due to automatic initialization and updating for TIMESTAMP.
    • If the output of SHOW VARIABLES LIKE 'sql_mode' includes NO_ZERO_DATE. A solution is to include the following in a *.cnf file:
    [mysqld]
    explicit_defaults_for_timestamp = true
    sql_mode = "ALLOW_INVALID_DATES,{other_sql_modes}"
    • Where {other_sql_modes} is the rest of the modes from the SHOW VARIABLES LIKE 'sql_mode' output.
0