MySQL

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 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. 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), 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:

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:

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). 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.

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:

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:

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:

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 type	Status	Notes
CHAR, VARCHAR	✅ Supported	Text. A value longer than the column allows is refused.
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	✅ Supported	Text.
TINYINT, SMALLINT, MEDIUMINT, INT	✅ Supported	Number. A value outside the column's range is refused.
BIGINT	✅ Supported	Number. Every digit is kept.
DECIMAL	✅ Supported	Number. Every digit is kept.
FLOAT, DOUBLE	✅ Supported	Number.
BOOLEAN, TINYINT(1)	✅ Supported	Checkbox. Any value other than 0 is checked.
DATE	✅ Supported	Zero dates (0000-00-00) and dates no calendar has (2026-02-30) read as empty.
DATETIME	✅ Supported	DATETIME 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	✅ Supported	Read 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	✅ Supported	Text, such as 13:45:00.
YEAR	✅ Supported	Number, from 1901 to 2155, or 0.
ENUM	✅ Supported	Single 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	✅ Supported	Multi-select, with the column's options, as for ENUM.
JSON	✅ Supported	Text, as MySQL stores it. MySQL reformats JSON, so {"a":1} reads back as {"a": 1}.
JSON with the comment whalesync:multi-select	✅ Supported	Multi-select, stored as a JSON list such as ["VIP", "Wholesale"]. See Multi-select columns.
UUID, INET4, INET6 (MariaDB)	✅ Supported	Text.
Foreign key	✅ Supported	A 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 Supported	Left 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 Supported	Left out.
VECTOR	✖️ Not Supported	Left 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:

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.

• 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

Caution

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.

Caution

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.

Danger

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.