Connect SPSync to MySQL or MariaDB

This guide covers MySQL and MariaDB you run yourself, Azure Database for MySQL, Amazon RDS and Aurora MySQL, and Google Cloud SQL for MySQL. SPSync uses one destination kind, mysql, and the same connection string shape for all of them.

Before you start

MySQL or MariaDB, reachable from the machine that runs SPSync.
Nothing to install on that machine. SPSync connects with a pure Python driver, PyMySQL, so no client library is needed.
Rights to create a database and a user.

Create the database and a least-privilege user

On MySQL and MariaDB a schema is a database. SPSync loads into a database named by dataset_name (for example sync) and uses a second database, sync_staging, while it merges a round. Create both and give one user full rights on only those two. The script is from the MySQL documentation and has not been run here against a test server.

CREATE DATABASE sync;
CREATE DATABASE sync_staging;
CREATE USER 'spsync_loader'@'%' IDENTIFIED BY 'use-a-long-random-password';
GRANT ALL PRIVILEGES ON sync.* TO 'spsync_loader'@'%';
GRANT ALL PRIVILEGES ON sync_staging.* TO 'spsync_loader'@'%';

Replace '%' with the address SPSync connects from if you can. CREATE VIEW is covered by ALL PRIVILEGES on those databases, which reporting views need.

Create the two databases first, as above. A user whose rights are limited to named databases cannot create a new one, so SPSync needs them to exist.

Allow SPSync to reach it

MySQL listens on TCP 3306. On the desktop app and a self-hosted server, the connection starts from that machine. For the Azure managed application, allow the address it connects out from, or put the database on the same virtual network, instead of opening it to the internet.

Self-hosted: set bind-address so the server listens on the right interface and allow the SPSync address in the host firewall.
Azure Database for MySQL (Flexible Server): on the server’s Networking page, add a firewall rule for the address, or use private access in a virtual network and run SPSync inside it.
Amazon RDS and Aurora MySQL: add an inbound rule for port 3306 to the security group, with the source written as /32. On Aurora use the cluster (writer) endpoint.
Google Cloud SQL: under Connections, add the address as an authorized network, or use the Cloud SQL Auth Proxy.

Encrypt the connection

PyMySQL tries TLS when the server offers it, but it carries on without it when it cannot. To require TLS and check the server certificate, add ssl_ca with the path to the CA file and no other ssl_ parameter:

mysql+pymysql://spsync_loader:PASSWORD@HOST:3306/sync?ssl_ca=/path/to/ca.pem

PyMySQL has no ssl_mode setting, so using one makes the connection fail before it starts. Do not add ssl_verify_identity=true next to ssl_ca: it makes PyMySQL discard the CA path and turns checking off. ssl_ca on its own already requires TLS and verifies the host name.

Azure Database for MySQL: use the DigiCert Global Root G2 certificate Microsoft publishes. The retired Single Server SKU used the Baltimore root and wanted user@servername as the login. Flexible Server takes a plain user name.
Amazon RDS and Aurora: download the regional RDS CA bundle and point ssl_ca at it.
Google Cloud SQL: through the Auth Proxy connect to 127.0.0.1:3306 with no ssl_ parameters, because the proxy handles TLS. For a direct connection, use ssl_ca, ssl_cert and ssl_key from the client certificate you download from the instance’s Connections page.

Authentication SPSync supports today

A user name and password in the connection string. Microsoft Entra ID tokens for Azure and IAM database tokens on AWS or Google Cloud are not supported. The Cloud SQL Auth Proxy uses Google credentials to reach the instance, but the credential SPSync gives MySQL stays a user name and password.

Connect from SPSync

The connection string names the driver. HOST is your server’s name:

mysql+pymysql://spsync_loader:PASSWORD@HOST:3306/sync?ssl_ca=/path/to/ca.pem

URL-encode any @, : or / in the password.

Desktop app: open the Destination screen, choose MySQL, paste the string into Connection string and choose Test connection.

CLI:

export SPSYNC_DSN='mysql+pymysql://spsync_loader:PASSWORD@HOST:3306/sync?ssl_ca=/path/to/ca.pem'
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind mysql --connection-string-env SPSYNC_DSN --dataset-name sync \
  --client-id <entra-app-client-id>

AI assistant: with the admin MCP server connected, ask for the destination requirements, then let the assistant create the destination. You enter the string on the secure entry page.

MariaDB

The same string and driver work. MariaDB is wire-compatible, and SPSync runs its test suite against both MySQL 8.4 and MariaDB 11.

What Test connection and doctor check

Both open a connection with the string and run SELECT 1. A pass means the host is reachable, TLS worked and the password is accepted. It does not prove the user can create tables. spsync doctor reports the same probe as destination-connectivity.

Common errors

What you see	What it means and the fix
`Can't connect to MySQL server` or `timed out`	A firewall, security group or authorized network is blocking port 3306, or the host is wrong.
`Access denied for user`	Wrong password, or the user was created for a different host than SPSync connects from.
`Access denied for user ... to database`	The user has no rights on `sync` or `sync_staging`. Run the grants above.
`unexpected keyword argument 'ssl_mode'`	PyMySQL has no `ssl_mode`. Use `ssl_ca` instead.
`Data too long for column 'state'`	A replica created by an older release. Run the two statements in “Fix a replica created by an older release” below once.

Fix a replica created by an older release

If you see Data too long for column 'state', run these two statements once, in the database named by dataset_name:

ALTER TABLE _dlt_pipeline_state MODIFY `state` LONGTEXT NOT NULL;
ALTER TABLE _dlt_version MODIFY `schema` LONGTEXT NOT NULL;

These statements are not run by SPSync’s own tests.