Share via

Grant MySQL user privileges

Important

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

Important

This article contains references to the term slave, a term that Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.

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

Databricks recommends that you create a dedicated MySQL user solely for Azure 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 Azure 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:

-- 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 Azure 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:

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:

SHOW GRANTS FOR 'lakeflow_connect_user'@'%';

The output includes the following:

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:

  • Last updated on