---
title: "MySQL wire protocol"
description: "Connect with standard MySQL clients and drivers, and run MySQL SQL or ISO GQL against SurrealDB tables over the MySQL wire protocol."
canonical_url: "https://surrealdb.com/docs/reference/protocols/mysql-protocol"
section: "Protocols and APIs"
surrealdb_version: "4.x"
last_modified: "2026-11-19T06:06:22Z"
---

> Full SurrealDB documentation index: https://surrealdb.com/docs/llms.txt

# MySQL wire protocol

Connect with standard MySQL clients and drivers, and run MySQL SQL or ISO GQL against SurrealDB tables over the MySQL wire protocol.

_(since v4.0.0)_

The MySQL wire protocol listener lets a MySQL client, such as the `mysql` command-line client, JDBC through Connector/J, or the Python, Go, Node.js and PHP drivers, connect to SurrealDB on a TCP port and run queries. A connection runs the MySQL dialect of SQL against SurrealDB tables, and can switch to ISO GQL. Queries written for MySQL 8.0 run against SurrealDB tables. This page covers how to enable the listener, how to connect, and where the MySQL dialect differs from MySQL. The [Postgres wire protocol](/docs/reference/protocols/postgres-protocol.md) page covers the equivalent listener for PostgreSQL clients.

## Enable the listener

The listener is included in the official binaries, installers and Docker images, and is off until you give it an address. Pass `--mysql-bind` to [`surreal db start`](/docs/reference/cli/db/commands/start.md) to start it, on a separate address from the HTTP server:

```bash
surreal db start --user root --pass secret \
  --mysql-bind 127.0.0.1:3306 \
  memory
```

The environment variable equivalent is `SURREAL_MYSQL_BIND`:

**Bash**

```bash
export SURREAL_MYSQL_BIND=127.0.0.1:3306
surreal db start --user root --pass secret memory
```

**PowerShell**

```powershell
$env:SURREAL_MYSQL_BIND = "127.0.0.1:3306"
surreal db start --user root --pass secret memory
```

The server logs the address when the listener starts. A server started without `--mysql-bind` or `SURREAL_MYSQL_BIND` does not listen for MySQL connections. The [`mysql` route capability](/docs/learn/security/authorization/capabilities.md#http-route-names) also controls the listener: with `--deny-http mysql`, the server refuses each connection.

## Connect with `mysql`

```bash
mysql -h 127.0.0.1 -P 3306 -u root -psecret -D main/main \
  --get-server-public-key
```

On a plaintext connection, pass `--get-server-public-key` as above. The client then asks the server for its RSA public key and sends the password encrypted with it, as the default `caching_sha2_password` method requires over plaintext. Without the option, the `mysql` client fails with `ERROR 2061: Couldn't read RSA public key from server`. Over [TLS](#tls), the option is not needed.

The database name selects the namespace and the database as `ns/db`, with a single slash, in the same way as `Surreal-NS` and `Surreal-DB` on HTTP. `USE ns/db` changes both on an open connection.

The server announces itself as MySQL `8.0.35-SurrealDB`, and `SELECT VERSION()` and `SELECT @@version` both return that string.

### Authentication

A client signs in as a system user, such as the root user or a [database user](/docs/reference/query-language/statements/define/user.md) with a password. The server accepts the `caching_sha2_password`, `mysql_native_password` and `mysql_clear_password` authentication methods:

- `caching_sha2_password` is the default of MySQL 8 clients. It works over TLS, and over a plaintext connection from any address with the server's RSA public key, which the server generates when it first needs it.
- `mysql_native_password` serves older clients and drivers. The server verifies it directly only for a user who signed in earlier since the server started. Otherwise the server asks the client to switch to `mysql_clear_password`, so the client must allow clear-text passwords, as the `mysql` client 8.x does with `--enable-cleartext-plugin`.
- `mysql_clear_password` sends the password as clear text.

A sign-in that sends the password as clear text needs a TLS connection or a loopback address, such as `127.0.0.1`, and is refused with error `1045` `Authentication requires an encrypted (TLS) connection or loopback` otherwise.

### TLS

The listener uses the certificate and key that `--web-crt` and `--web-key` give the HTTP server. Without them, the listener serves plaintext connections and the server logs a warning. TLS is recommended for any connection that does not come from the same host.

### Client tools

`mysqladmin` (`ping`, `version`, `status` and `variables`) and `mysqlslap` run against the listener. `mysqldump` of a table runs to completion when given `--no-tablespaces` and `--skip-lock-tables`, and writes the rows of the table as `INSERT` statements. The structure section of the dump holds the SurrealQL definition that `SHOW CREATE TABLE` returns, so a MySQL server cannot replay it. Pass `--no-create-info` to dump the rows only.

## Query languages

A new connection runs the MySQL dialect. `SET dialect = 'gql'` switches an open connection to [ISO GQL](/docs/learn/querying/gql/overview.md), and `SET dialect = 'mysql'` (or `'sql'`) switches it back.

```sql
CREATE TABLE person (id INT PRIMARY KEY, name TEXT, age INT);
INSERT INTO person (id, name, age) VALUES (1, 'Ada', 36), (2, 'Bob', 28);
SELECT name, age FROM person WHERE age > 30 ORDER BY name;
```

SurrealQL and the PostgreSQL dialect are not available over this listener: `SET dialect = 'surrealql'` and `SET dialect = 'pgsql'` fail with error `1045`. Send SurrealQL over [HTTP](/docs/reference/protocols/http-protocol.md), [RPC](/docs/reference/protocols/rpc-protocol.md) or an SDK, and PostgreSQL queries over the [Postgres wire protocol](/docs/reference/protocols/postgres-protocol.md).

In the MySQL dialect, every record ID in a result is sent as its key, as a SQL client expects a primary key to be: `person:1` is sent as `1`. The tables are SurrealDB tables, so a SurrealQL query over HTTP or an SDK reads the same records.

## The MySQL dialect

The MySQL dialect parses MySQL 8.0 and 8.4 syntax, and runs `SELECT` with joins, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY` and `LIMIT`, aggregate functions, non-recursive common table expressions (`WITH`), `INSERT` (including `INSERT ... ON DUPLICATE KEY UPDATE`), `UPDATE`, `DELETE`, `CREATE TABLE`, `DROP TABLE`, `TRUNCATE` and the statements that drivers send while they connect. Prepared statements, statements sent several at a time, window functions, `START TRANSACTION`, `COMMIT`, `ROLLBACK`, `SAVEPOINT` and `SET autocommit` are supported. A statement that names a table in another database is refused.

User variables and prepared statements in SQL work as in MySQL. `SET @a = expr` keeps the value of the expression for the connection, a later `@a` reads it, and a variable that was never set reads as `NULL`. `PREPARE name FROM 'statement'` (or `FROM @variable`), `EXECUTE name [USING @a, ...]` and `DEALLOCATE PREPARE name` prepare and run a statement, and `EXECUTE` of an unknown name fails with `1243`. `COM_RESET_CONNECTION` clears both.

A column without an alias is named by its expression exactly as it was written, as MySQL names it, so `SELECT COUNT(*), MAX(k) + 1` answers the columns `COUNT(*)` and `MAX(k) + 1`. A name is cut at 255 bytes.

`CREATE TABLE` defines a field of a [sized number](/docs/reference/query-language/language-primitives/data-types/numbers.md#sized-numbers) type for each number column: `TINYINT` is `i8`, `SMALLINT` is `i16`, `MEDIUMINT` and `INT` are `i32`, `BIGINT` is `int`, `INT UNSIGNED` is `u32`, `FLOAT` is `f32` and `DOUBLE` is `float`. A value out of range for the column, such as `300` for a `TINYINT` or `-1` for an `INT UNSIGNED`, is refused with `1366`.

`CREATE TABLE` also records the type of each column as the field's [`SQL TYPE`](/docs/reference/query-language/statements/define/field.md#declaring-a-sql-type), and every write is checked against it, so a value too long for a `VARCHAR(n)` column is refused with `1406`. A `DECIMAL` column with a precision above 28 defines a [`bigdecimal`](/docs/reference/query-language/language-primitives/data-types/numbers.md#big-decimal-numbers) field. An `ENUM('s','m','l')` column defines a field of the [inline enum](/docs/reference/query-language/language-primitives/data-types/named-types.md#inline-enums) `enum<'s', 'm', 'l'>`, which refuses any other value with `1366` and orders its values in the order the column lists them.

The implemented functions are tested against the answers that MySQL 8.4 gives for the same arguments, and include the keyword forms `EXTRACT(YEAR FROM d)`, `POSITION('b' IN s)` and `SUBSTRING(s FROM 2 FOR 3)`, and `INTERVAL` arithmetic such as `DATE_ADD(d, INTERVAL 1 DAY)`. `LENGTH` counts bytes, as in MySQL. `CAST(x AS UNSIGNED)` of a constant follows MySQL as well: a negative integer wraps around to the top of the unsigned range, so `-1` becomes `18446744073709551615`, a number with a fraction is rounded first, and a number above the largest unsigned integer becomes `18446744073709551615`.

Many points behave as they do over the PostgreSQL dialect, which shares the same query planner. The [PostgreSQL dialect](/docs/reference/protocols/postgres-protocol.md#postgresql-sql) section describes these in more detail: tables that declare their columns, unqualified columns in a join, `t.*`, `LIKE` patterns, subqueries used as a value, window functions, and the limits on how deeply a `FROM` clause can nest. Over this listener, the failures that section describes are reported with the MySQL error numbers listed under [Error codes](#error-codes), and a `FROM` clause that nests too deeply fails with `1064`.

### Transactions and DDL

`START TRANSACTION` or `BEGIN` opens a transaction, and `SET autocommit = 0` makes each statement run in a transaction until `COMMIT`. As in MySQL, a DDL statement (`CREATE`, `ALTER`, `DROP`, `TRUNCATE` or `RENAME`) first commits the open transaction and then runs on its own, so a `ROLLBACK` does not take back a `CREATE TABLE`.

`CREATE TABLE` and `DROP TABLE` of several tables are each answered with one OK packet. If one part of the statement fails, nothing of the statement is kept, so `DROP TABLE a, missing` fails and keeps `a`.

`TRUNCATE TABLE t` and `TRUNCATE t` answer with an OK packet that reports 0 affected rows, as a plain statement, as a prepared statement and inside a batch of statements.

An `AUTO_INCREMENT` column draws its numbers from a sequence, and is `NOT NULL`. `TRUNCATE` starts the numbering again from 1, as does `CREATE TABLE` of a table that was dropped. Because `TRUNCATE` defines the sequence again, a user who may delete records but not define a sequence, such as an editor, is refused `TRUNCATE` of a table with an `AUTO_INCREMENT` column.

### System variables

A `SELECT` of system variables, such as `SELECT @@version, @@sql_mode, @@autocommit`, answers one column for each variable, in the order asked, named by its alias or by the variable. A number is sent as an integer, and other values as text. `@@name`, `@@session.name`, `@@local.name` and `@@global.name` read the same value, in any letter case. A connection with no database chosen can run a `SELECT` of only system variables, which drivers send before they choose a database.

The values are the fixed settings of the server, so a `SET` does not change what `@@name` reads. A name that MySQL 8.4 or 8.0.35 does not have fails with error `1193` `Unknown system variable`. A variable that MySQL has but this server does not model reads as `NULL`, and so do variables that describe the host, such as `@@datadir`, `@@hostname`, `@@port` and `@@version_compile_os`.

### `SHOW` and `DESCRIBE`

`SHOW` and `DESCRIBE` answer with MySQL 8.4's column names, order and types, with one row per table, field, indexed column or database:

| Statement | Answers with |
| --- | --- |
| `SHOW TABLES` | The tables of the current database, in a `Tables_in_<db>` column |
| `SHOW TABLE STATUS` | MySQL's 18 columns, one row per table, with sizes of `0`, times of `NULL`, and `VIEW` as the `Comment` of a view |
| `SHOW COLUMNS FROM t`, `SHOW FIELDS FROM t`, `DESCRIBE t`, `DESC t` | `Field`, `Type`, `Null`, `Key`, `Default` and `Extra`, one row per field |
| `SHOW INDEX FROM t`, `SHOW KEYS FROM t` | MySQL's 15 columns, one row per indexed column |
| `SHOW DATABASES` | The databases of the session's namespace, in a `Database` column |
| `SHOW VARIABLES` | `Variable_name` and `Value`, for the variables this server models |
| `SHOW ENGINES`, `SHOW COLLATION`, `SHOW CHARACTER SET` | MySQL's columns |
| `SHOW STATUS`, `SHOW PROCESSLIST`, `SHOW WARNINGS`, `SHOW ERRORS`, `SHOW TRIGGERS` | MySQL's columns and no rows |

Each `SHOW` reads under the session's own permissions, and needs a database to be chosen. `SHOW DATABASES` needs permission to view the namespace. A table that does not exist fails, and a `LIKE` pattern is limited to 512 characters. Some answers differ from MySQL:

- Columns and indexes are listed by name, not in the order they were defined.
- `SHOW CREATE TABLE` returns the SurrealQL definition, not MySQL DDL.
- With `LIKE`, the first column of `SHOW TABLES` is named `Tables_in_<db>`, where MySQL adds the pattern to the name.
- `Cardinality` in `SHOW INDEX` is `NULL`.
- An instance that denies built-in functions refuses these statements.

## Error codes

A failed statement is answered with the error number and SQLSTATE that MySQL 8.4 gives for the same kind of failure, for plain and for prepared statements. Drivers use the number to decide what to do, so PyMySQL, for example, raises `IntegrityError` for a duplicate key. The message text is SurrealDB's own.

| Number | SQLSTATE | Reported when |
| --- | --- | --- |
| `1062` | `23000` | A write conflicts with a `UNIQUE` index or a primary key. |
| `1048` | `23000` | A write gives `NULL` to a field that requires a value. |
| `1364` | `HY000` | A write leaves out a field that requires a value. |
| `1366` | `HY000` | A write gives a field a value of another type. |
| `1406` | `22001` | A write gives a text column a value longer than its declared type allows, such as `VARCHAR(5)`. |
| `1146` | `42S02` | The statement names a table that does not exist. |
| `1054` | `42S22` | The statement names a column that does not exist. |
| `1050` | `42S01` | `CREATE TABLE` names a table that already exists. |
| `1305` | `42000` | The statement calls a function that does not exist. |
| `1142` | `42000` | The user may not run the statement. |
| `1193` | `HY000` | The statement names a system variable that does not exist. |
| `3593` | `HY000` | A window function is used where it is not allowed, such as in `WHERE`. |
| `1213` | `40001` | The transaction conflicts with another transaction. Retry it. |
| `3024` | `HY000` | The statement exceeds its timeout. |
| `1235` | `42000` | The statement uses a feature that SurrealDB does not support. |
| `1644` | `45000` | `THROW` raises an error, or a subquery used as a value returns more than one row. |
| `1064` | `42000` | The statement cannot be parsed. |
| `1105` | `HY000` | Any other failure, where MySQL has no number for it. |

The numbers can differ from MySQL's for the same statement:

- Every refused value apart from a text that is too long is `1366`, where MySQL gives `1292` for a value that is not a valid date.
- SurrealDB refuses some values that MySQL converts, such as `'5'` or `true` for an `INT` column.
- `1/0` written into an `INT` column is `1366`, where MySQL gives `1365`.

## Not yet supported

The MySQL dialect refuses or differs from MySQL in these places:

- Recursive common table expressions are refused with `1235`, before the statement changes any data.
- The window function features that the [PostgreSQL dialect refuses](/docs/reference/protocols/postgres-protocol.md#not-yet-supported), such as `GROUPS` frames and `DISTINCT` inside a window call, are refused with `1235`.
- `INTERSECT ALL` and `EXCEPT ALL` are refused with `1235`, as are the other forms of `INTERSECT` and `EXCEPT` that the PostgreSQL dialect refuses. Text is compared exactly, so `SELECT 'a' INTERSECT SELECT 'A'` answers no rows, where MySQL's default collation ignores letter case.
- `SQL_CALC_FOUND_ROWS` is refused. `SQL_NO_CACHE`, `SQL_CACHE`, `SQL_SMALL_RESULT`, `SQL_BIG_RESULT`, `SQL_BUFFER_RESULT` and `HIGH_PRIORITY` are accepted and ignored.
- `ALTER TABLE` and `LOAD DATA` are not supported yet. A `LATERAL` subquery and an `ORDER BY` inside `GROUP_CONCAT` fail with `1064`.
- `information_schema` lists the tables and declared columns of the database in PostgreSQL's form: the tables are in the schema `public`, not in a schema named after the database, so `WHERE table_schema = DATABASE()` finds none, `data_type` holds PostgreSQL type names such as `integer`, and `column_type` is `NULL`. `SHOW TABLES` and `SHOW COLUMNS` answer in MySQL's form.
- `FOREIGN KEY` constraints are not supported. `CREATE TABLE` with one fails with `1105`.
- `LIKE` is refused with `1235` in an `INSERT`, `UPDATE` or `DELETE`, including in its `WHERE` clause, so `DELETE FROM t WHERE name LIKE 'a%'` fails before it deletes anything. It works in a `SELECT`. Functions, such as `SUBSTRING`, `LTRIM` and `CONCAT`, work in both.
- `CAST(x AS UNSIGNED)` of a value read from a column fails with `1366` when the value is negative, or is a text that is not a number such as `'12abc'`. MySQL wraps the negative value, and reads the number at the start of the text.
- `TIMESTAMPDIFF` with the unit `MONTH`, and compound interval units such as `DAY_HOUR`, are refused with `1235`.
- `TRUNCATE` keeps the records that the table's permissions do not let the user delete, and still answers OK.
- An `INSERT` that gives an `AUTO_INCREMENT` primary key `NULL` is refused, where MySQL numbers the row.
- `@@performance_schema` is `0`. `@@session.name` of a variable that is global only reads a value, where MySQL gives error `1238`.
- `SET GLOBAL timestamp` is error `1235`, where MySQL gives `1228`.
- A batch of several statements that names an unknown system variable fails as a whole before any statement runs.
- In a batch of several statements, only the first statement keeps its column names. Unnamed or repeated columns of later statements are named `?column?1`, `?column?2`, and so on.
- `LIKE ... ESCAPE` is refused, and `e` and `é` are not equal in a comparison.
- A `?` parameter placeholder is accepted only in a prepared statement.

## Capabilities and security

Connections are gated like other query surfaces:

- **The `mysql` route.** [`--deny-http mysql`](/docs/learn/security/authorization/capabilities.md#http-routes-and-rpc-methods) refuses every connection to the listener with error `1045`. The route also allows the MySQL dialect on the listener.
- **GQL.** `SET dialect = 'gql'` needs the `gql` route. When the server denies the route, the `SET dialect` statement fails, and a GQL query fails with `1045`.
- **Arbitrary queries.** [`--deny-arbitrary-query`](/docs/learn/security/authorization/capabilities.md#arbitrary-queries) applies to connections on this listener as it does on the other surfaces.

The listener accepts at most 1,024 connections at a time and answers further connections with error `1040` `Too many connections`. A packet is limited to 64 MiB, the value of `@@max_allowed_packet`, and a larger packet is refused with error `1153`.

## Related pages

- [Postgres wire protocol](/docs/reference/protocols/postgres-protocol.md): the equivalent listener for PostgreSQL clients, and the PostgreSQL dialect
- [PostgreSQL and MySQL compatibility matrix](/docs/reference/protocols/sql-compatibility.md): the two dialects compared feature by feature
- [`surreal db start`](/docs/reference/cli/db/commands/start.md): CLI reference including `--mysql-bind`
- [Capabilities](/docs/learn/security/authorization/capabilities.md): lock down arbitrary queries and routes
- [Migrating from MySQL](/docs/integrate/migrating/from-other-databases/from-mysql.md): map MySQL types to SurrealQL, and import data with Surreal Sync
