Skip to main content
Last updated on

Grant MySQL user privileges

Preview

The MySQL connector is in Public Preview. Contact your Databricks account team to request access.

info

This page contains references to the term slave, a term that Databricks doesn't use. When the term is removed from the third-party software, we'll remove it from this page.

Learn how to grant the privileges required for the MySQL user that you use for ingesting data into Databricks.

Databricks recommends that you create a dedicated MySQL user solely for Databricks ingestion. This user requires specific privileges to read table data and access binary logs for change data capture.

Authentication methods​

The MySQL connector supports the following authentication plugins based on your MySQL version:

MySQL 5.7.44 and later:

  • sha256_password

MySQL 8.0 and later:

  • sha256_password
  • caching_sha2_password (default for MySQL 8.0)
note

If you use sha256_password or caching_sha2_password, the Test Connection button in the Databricks UI might fail even with valid credentials. This is a known limitation. You can still create the connection and proceed with pipeline setup.

Create the replication user​

To create a dedicated MySQL user for replication, run the following SQL commands on your MySQL database:

SQL
-- For MySQL version >= 8.x
CREATE USER 'lakeflow_connect_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'StrongP@ssw0rd!';

-- For MySQL version 5.7.x
CREATE USER 'lakeflow_connect_user'@'%' IDENTIFIED WITH sha256_password BY 'StrongP@ssw0rd!';

Replace StrongP@ssw0rd! with a strong password. The % wildcard allows connections from any host. For better security, replace % with the specific IP ranges or hostnames that Databricks will use to connect.

Required privileges​

Grant the following privileges to the replication user:

Privilege

Description

REPLICATION SLAVE

Required to read binary logs and track changes.

REPLICATION CLIENT

Required to access replication status and metadata.

SELECT(plugin, user) on mysql.user table

Required to validate the authentication plugin. You can grant either SELECT(plugin, user) or full SELECT on the mysql.user table.

SELECT on target databases and tables

Required to read initial snapshots and table metadata.

Grant statements​

Run the following SQL commands to grant the required privileges:

SQL
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'lakeflow_connect_user'@'%';

GRANT SELECT(plugin,user) ON mysql.user TO 'lakeflow_connect_user'@'%';

-- Grant select privilege on all user tables
GRANT SELECT ON *.* TO 'lakeflow_connect_user'@'%';

FLUSH PRIVILEGES;

Verify user privileges​

To verify that the user has the correct privileges, run the following command:

SQL
SHOW GRANTS FOR 'lakeflow_connect_user'@'%';

The output includes the following:

SQL
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'lakeflow_connect_user'@'%'
GRANT SELECT (`plugin`, `user`) ON `mysql`.`user` TO 'lakeflow_connect_user'@'%'
GRANT SELECT ON *.* TO 'lakeflow_connect_user'@'%'

Next steps​

After creating the replication user, configure your MySQL server for binlog replication. The configuration steps vary by deployment type: