> For the complete documentation index, see [llms.txt](https://docs.whalesync.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.whalesync.com/connectors/mysql.md). # MySQL Two-way sync MySQL and MariaDB tables with Airtable, Notion, Google Sheets, and more. Covers connecting, privileges, record IDs, views, and field types. Whalesync syncs MySQL and MariaDB tables both ways. Each database is a base in Whalesync, and each table or view in it is a table. > [!NOTE] > **Syncing a view?** Add a `whalesync_mysql_id` column to it, as described in [Views](#views). Views sync one way, out of MySQL. ## Connecting to MySQL Choose MySQL in Whalesync. The connect dialog has two tabs, and you fill in one of them. **Connection details** | Field | What to enter | | --- | --- | | Host | The server's host name or IP address. | | Port | The server's port. Leave it blank for 3306. | | Database | The database to sync. Leave it blank to list every database the user can use. | | Username | The MySQL user Whalesync signs in as. | | Password | That user's password. | **Connection string** Paste a connection string such as `mysql://user:password@host:3306/database`. Whalesync accepts strings that start with `mysql://`, `mysql2://`, `mariadb://`, `jdbc:mysql://`, or `jdbc:mariadb://`, and SQLAlchemy's `mysql+pymysql://` and `mariadb+mariadbconnector://` forms. * If the password has special characters (`@ / ? # % &`), percent-encode them (`@` becomes `%40`), or use the Connection details tab instead. * Besides the user and password, Whalesync reads only a string's SSL options (`ssl-mode`, `sslaccept`, `ssl`, `useSSL`, and `requireSSL`). It ignores options that don't change how it connects, such as the character set, pool sizes, and timeouts, and it refuses any other option by name. * Options that point at certificate files on your computer, such as `sslca` or `sslcert`, aren't supported. Paste the CA certificate into the CA Certificate box instead. * Go-style strings (`user:password@tcp(host:3306)/database`), `Server=...;Uid=...;` strings, `mysqlx://`, `mysql+srv://`, and JDBC URLs with more than one host aren't supported. Use the Connection details tab for those. ### SSL Mode Both tabs have an SSL Mode setting. | SSL Mode | What it does | | --- | --- | | Automatic (the default) | Encrypts the connection without checking the server's certificate. If the server doesn't support encryption, Whalesync connects without it and saves the connection as Disabled. This happens only when you create or edit the connection, never during a sync. | | Required | Encrypts the connection without checking the certificate. It never falls back to an unencrypted connection. | | Verify CA | Encrypts the connection and checks that the server's certificate was signed by the CA certificate you provide. | | Verify identity | Checks the certificate as Verify CA does, and also checks that it names the host you connect to. Connect by that host name, not by an IP address. | | Disabled | Doesn't encrypt the connection. | For Verify CA and Verify identity, a CA Certificate box appears. Paste your provider's CA certificate, or click **Load from file** to load its `.pem` file. If your connection string has an SSL option, SSL Mode can make it stricter but not weaker. ### Before you connect * **The server must accept connections from the internet.** Whalesync can't reach a private address inside your network. If your server or firewall limits which addresses can connect, allow Whalesync's address, `34.66.3.22`. See [Allowlisting Whalesync IP addresses](https://docs.whalesync.com/resources/support/allowlist-ip.md). The MySQL user must also be allowed to connect from that address, for example as `'whalesync'@'%'`. * **SSH tunnels aren't supported.** Hosts that expose MySQL only over SSH, such as WP Engine and Kinsta, can't be connected. * **The user signs in with a password.** Whalesync supports `caching_sha2_password` (MySQL 8's default) and `mysql_native_password`. It doesn't support MariaDB's `ed25519` and `parsec`, or sign-in that sends the password in clear text (IAM, Microsoft Entra, LDAP, and PAM). * **Client certificates aren't supported.** A user created with `REQUIRE X509` is refused as if its password were wrong. ## Privileges The MySQL user needs these privileges on the database: | Privilege | What Whalesync uses it for | | --- | --- | | SELECT | Reading tables and views. | | INSERT, UPDATE, and DELETE | Syncing changes into tables. | | ALTER | Adding its ID column to a table that needs one (see [Record IDs](#record-ids)), and adding columns when you create fields from Whalesync. | | CREATE | Creating tables from Whalesync. | | REFERENCES | Creating link columns, which are foreign keys, in tables and fields Whalesync creates. | For example, to create a user for a database named `shop`: ```sql CREATE USER 'whalesync'@'%' IDENTIFIED BY 'a-strong-password'; GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, REFERENCES ON shop.* TO 'whalesync'@'%'; ``` For a sync that only reads from MySQL, SELECT is enough, unless a table needs setup. * **Column privileges count.** A column the user can't UPDATE syncs one way, out of MySQL, while the table's other columns sync both ways. * **Privileges from a role count only if it's a default role.** On MySQL, run `SET DEFAULT ROLE ALL TO 'whalesync'@'%';`. On MariaDB, run `SET DEFAULT ROLE role_name FOR 'whalesync'@'%';`. ## Supported Tables ### Record IDs Whalesync needs a column that identifies each row, which is filled in when the row is created and never edited. It uses the first of these the table has: 1. A `whalesync_mysql_id` column with a unique index. 2. An `AUTO_INCREMENT` primary key on one column. 3. A primary key on one column whose default creates a UUID: `UUID()`, `UUID_TO_BIN(UUID())`, or `UUID_TO_BIN(UUID(), 1)` on MySQL, and `UUID()` on MariaDB, or `UUID_v4()` and `UUID_v7()` from MariaDB 11.7. When Whalesync creates a row, it creates the UUID itself, the way the default would. A default that only includes a UUID, such as `CONCAT('cus_', UUID())`, doesn't count. 4. An `AUTO_INCREMENT` column that isn't the primary key but has a unique index of its own. Other unique columns, such as an email address or a slug, are never used. People edit them, and an edited ID would look like a deleted row and a new one. ### Tables that need setup When a table has none of these, Whalesync lists it as needing setup. Click **Add Whalesync ID column**, and Whalesync adds this column to the table, as its primary key if it has none, or as a unique column if it does: ```sql whalesync_mysql_id BIGINT AUTO_INCREMENT ``` On MySQL 8.0.23 and later and MariaDB 10.3.3 and later, the column is invisible, so your own `SELECT *` queries and inserts without a column list don't see it. On older servers it's an ordinary column. You can add `whalesync_mysql_id` yourself instead. Give it a unique index and make it NOT NULL. Make it `AUTO_INCREMENT`, or give it one of the UUID defaults above, so Whalesync can create rows. Without one, Whalesync can update and delete rows but can't create them. ### Tables Whalesync can't set up * **A partitioned table without a record ID.** MySQL requires every unique index on a partitioned table to include the columns it's partitioned by, so Whalesync can't add its column. Sync the table through a view instead (see [Partitioned tables](#partitioned-tables)). A partitioned table that already has a record ID, such as one partitioned by its `AUTO_INCREMENT` key, syncs as usual. * **A table whose `AUTO_INCREMENT` column isn't unique on its own**, as in `PRIMARY KEY (tenant_id, id)`. MySQL allows one `AUTO_INCREMENT` column per table, so Whalesync can't add its own. Add a unique index on the column, for example `ALTER TABLE orders ADD UNIQUE (id);`, then refresh your tables. The column becomes the record ID. * **A table with a `whalesync_mysql_id` column that isn't unique.** Add a unique index on it or rename it. ### Tables that sync one way * **MyISAM tables**, and tables in any other storage engine that can't roll back a change, sync one way, out of MySQL. Convert the table to InnoDB to sync it both ways. * **Views** sync one way. See [Views](#views). ### Tables that aren't listed * MEMORY tables, whose rows disappear when the server restarts, which would look like every record was deleted. * MariaDB sequences and temporary tables. * The system databases: `mysql`, `sys`, `information_schema`, and `performance_schema`. ## Views Whalesync syncs a view one way, out of MySQL, and never writes to it. MySQL can't tell Whalesync which row an insert through a view created. A view needs a column named `whalesync_mysql_id`, which Whalesync uses as each row's record ID. MySQL can't add a column to a view, so add it to the view's query. Its values must be: * **Unique.** No two rows share a value. MySQL doesn't record whether a view's column is unique, so Whalesync can't check it, and rows that share a value can be skipped or merged into one record. * **Never empty.** Whalesync skips rows with an empty `whalesync_mysql_id`. * **Stable.** A row keeps its value. A changed value looks like a deleted row and a new one. Most views can use the primary key of the table they read from: ```sql CREATE OR REPLACE VIEW active_customers AS SELECT id AS whalesync_mysql_id, name, email FROM customers WHERE status = 'active'; ``` Then click **Refresh tables** in Whalesync, and the view is ready to map. When no single column is unique, as in a view that joins tables, combine columns with `CONCAT`. A column that is unique on its own, such as the table's `id`, reads fastest. A view with `DISTINCT` or `GROUP BY` is recomputed for every page Whalesync reads, which is slow on a large view. ### Partitioned tables To sync a partitioned table that has no record ID, create a view over it. Use `id` when it's unique on its own: ```sql CREATE VIEW events_for_whalesync AS SELECT id AS whalesync_mysql_id, id, created_at, name FROM events; ``` Otherwise combine columns that are unique together: ```sql CREATE VIEW events_for_whalesync AS SELECT CONCAT(id, '-', created_at) AS whalesync_mysql_id, id, created_at, name FROM events; ``` The view syncs one way, out of MySQL. ## Supported Fields
MySQL typeStatusNotes
CHAR, VARCHAR✅ SupportedText. A value longer than the column allows is refused.
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT✅ SupportedText.
TINYINT, SMALLINT, MEDIUMINT, INT✅ SupportedNumber. A value outside the column's range is refused.
BIGINT✅ SupportedNumber. Every digit is kept.
DECIMAL✅ SupportedNumber. Every digit is kept.
FLOAT, DOUBLE✅ SupportedNumber.
BOOLEAN, TINYINT(1)✅ SupportedCheckbox. Any value other than 0 is checked.
DATE✅ SupportedZero dates (0000-00-00) and dates no calendar has (2026-02-30) read as empty.
DATETIME✅ SupportedDATETIME has no time zone, so Whalesync reads it as UTC. Whalesync writes times to the millisecond, so microseconds are dropped. Zero dates read as empty.
TIMESTAMP✅ SupportedRead correctly in any server time zone. Microseconds are dropped, as for DATETIME. MySQL can't store a TIMESTAMP after January 19, 2038, so later dates are refused.
TIME✅ SupportedText, such as 13:45:00.
YEAR✅ SupportedNumber, from 1901 to 2155, or 0.
ENUM✅ SupportedSingle select, with the column's options. A value that isn't one of them is refused. An option with an emoji is left out of the list, since MySQL reports the emoji as ?, but its values still sync.
SET✅ SupportedMulti-select, with the column's options, as for ENUM.
JSON✅ SupportedText, as MySQL stores it. MySQL reformats JSON, so {"a":1} reads back as {"a": 1}.
JSON with the comment whalesync:multi-select✅ SupportedMulti-select, stored as a JSON list such as ["VIP", "Wholesale"]. See Multi-select columns.
UUID, INET4, INET6 (MariaDB)✅ SupportedText.
Foreign key✅ SupportedA link, when it points at the other table's record ID in the same database. Map both tables. Other foreign keys sync as plain values.
BIT➡️ Supported (1-Way)Read as a number. BIT(1) flags are read-only too.
YEAR(2) (MariaDB)➡️ Supported (1-Way)MariaDB stores any year written to it as two digits, so it's read-only.
Generated columns➡️ Supported (1-Way)MySQL computes them.
AUTO_INCREMENT and record ID columns➡️ Supported (1-Way)Set when a row is created.
BINARY, VARBINARY, BLOB✖️ Not SupportedLeft out. A BINARY(16) UUID can still be a record ID or a link, shown as base64 text.
Spatial types (GEOMETRY, POINT, and others)✖️ Not SupportedLeft out.
VECTOR✖️ Not SupportedLeft out.
On MariaDB, a JSON column is a text column, and MariaDB keeps JSON as written. ### Empty values When a mapped field is empty and its column allows NULL, Whalesync writes NULL. When the column is NOT NULL, Whalesync writes an empty value instead: an empty string for text, 0 for a number, unchecked for a checkbox, and no options for a SET. Dates, times, JSON, and ENUM columns have no empty value, so clearing a NOT NULL one fails until it's filled in again. There are two exceptions: an ENUM with an empty option is cleared to it, and a date column whose default is the zero date (`0000-00-00`), as WordPress declares its dates, is cleared to that date. ### Multi-select columns MySQL has no list type. Give a JSON column the comment `whalesync:multi-select`, and Whalesync reads and writes it as a multi-select holding a JSON list. Tables Whalesync creates use this for multi-select fields. To mark one of your own columns: ```sql ALTER TABLE customers MODIFY tags JSON COMMENT 'whalesync:multi-select'; ``` `MODIFY` replaces the column's definition, so repeat settings such as NOT NULL in it. On MariaDB, the comment also works on text columns. ## Creating Tables in MySQL Whalesync can create tables and columns in MySQL, when you choose **Create new table** while setting up a sync, or **Create new field** or **Copy fields from** in the field mapper. See [Auto-create tables](https://docs.whalesync.com/features/auto-create-tables.md). * **Names are lower case**, with spaces and other symbols turned into `_`, so `Customer Contacts` becomes `customer_contacts`. Names are cut to 64 characters, and a name that's already taken gets `_2`, `_3`, and so on. * **Each new table** uses InnoDB and utf8mb4, in the database's own collation when that's a utf8mb4 one. Its record ID is `whalesync_mysql_id BIGINT AUTO_INCREMENT PRIMARY KEY`, so it needs no setup, and its comment is `Created by Whalesync`. * **A new table gets at most 180 columns**, since a MySQL row has limited room. Whalesync reports the remaining fields as not created. * **Text columns added to an older table** are utf8mb4, even when the table is utf8mb3 or latin1, so they can hold emoji. | Field in the other app | MySQL column | | --- | --- | | Text, long text, rich text, single select, and formulas | LONGTEXT | | Whole number | BIGINT | | Other number, such as a Notion number, and currency | DOUBLE | | Checkbox | BOOLEAN | | Date | DATE | | Date and time | DATETIME(3), in UTC, to the millisecond | | Multi-select | JSON, with the comment `whalesync:multi-select` | | Link | A column of the linked table's record ID type, with a foreign key to it that is set to NULL when the linked row is deleted | A link field that holds several links becomes a column that holds the first. Whalesync can't create a link in a MyISAM table, to or from a partitioned table, or to a view, a table that isn't InnoDB, or a table that still needs setup. It reports that link and still creates the other fields. ## Things to Keep in Mind > [!WARNING] > **Required columns without a default**\ > When a new record leaves out a NOT NULL column that has no default, Whalesync fills it with an empty value if it's a TEXT, BLOB, BINARY, VARBINARY, or BIT column and nothing else depends on it, such as a unique index, a foreign key, or a generated column. MySQL does the same outside strict mode, which is why WordPress tables work without mapping every column. A required VARCHAR or number column isn't filled in: map a field to it, or give it a default. > [!WARNING] > **Emoji need utf8mb4 columns**\ > A utf8mb3 or latin1 column can't store emoji, and MySQL refuses the change. The error message says how to convert the table: `ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4`. > [!CAUTION] > **Renaming a mapped table or column breaks its mapping**\ > If you rename one, you'll need to remap it in Whalesync. Don't change or drop the `whalesync_mysql_id` column either, since that breaks the sync. > [!NOTE] > **Changes reach Whalesync by polling**\ > MySQL can't notify Whalesync of a change, so there are no webhooks. Whalesync reads your tables on a schedule to find changes. ## Tested Versions Whalesync's automated tests run against MySQL 8.0, MySQL 8.4, and MariaDB 11.4 on every change. It has also been checked against MySQL 5.7 and 9.7, MariaDB 10.3, 10.5, 10.6, 10.11, and 11.8, and TiDB 8.5.