Uplevel the MySQL REST Service

The MySQL REST Service is a next-generation JSON Document Store solution, enabling fast and secure HTTPS access to data stored in MySQL, HeatWave, InnoDB Cluster, InnoDB ClusterSet, and InnoDB ReplicaSet.

The MySQL REST Service was first released on https://labs.mysql.com in 2023 using MySQL Router. During spring 2025, it was released on MySQL HeatWave and standard MySQL Community packages, along with MySQL 9.3. Always use MySQL Router as the entry point for the REST Service.

Last week, we released a new lab version, MySQL 9.4.0-labs-MRS13, in which MySQL Router is no longer necessary, as the REST Service is now running directly on the MySQL Server. This is something I also encouraged for some time, and now a new component handles this function.

In this post, we will explore how to utilize this new component and access our data stored in MySQL without requiring SQL.

Installation

The first step is to install the required packages. As I use Oracle Linux 9, I will utilize the corresponding RPMs. I will also install the latest MySQL Shell (9.4.0):

# dnf install mysql-community-client-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-community-client-plugins-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-community-common-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-community-icu-data-files-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-community-libs-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-community-libs-compat-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-community-server-9.4.0-13.labs_mrs_13.el9.x86_64.rpm \
mysql-shell-9.4.0-1.el9.x86_64.rpm

Last metadata expiration check: 0:19:26 ago on Sun 27 Jul 2025 06:25:16 PM UTC.
Dependencies resolved.
==========================================================================
 Package                        Arch   Version                    Size
==========================================================================
Installing:
 mysql-community-client         x86_64 9.4.0-13.labs_mrs_13.el9   4.1 M
 mysql-community-client-plugins x86_64 9.4.0-13.labs_mrs_13.el9   1.5 M
 mysql-community-common         x86_64 9.4.0-13.labs_mrs_13.el9   597 k
 mysql-community-icu-data-files x86_64 9.4.0-13.labs_mrs_13.el9   2.3 M
 mysql-community-libs           x86_64 9.4.0-13.labs_mrs_13.el9   1.5 M
 mysql-community-libs-compat    x86_64 9.4.0-13.labs_mrs_13.el9   1.4 M
 mysql-community-server         x86_64 9.4.0-13.labs_mrs_13.el9   105 M
 mysql-shell                    x86_64 9.4.0-1.el9                 96 M
Upgrading:
 openssl                        x86_64 1:3.2.2-6.0.1.el9_5.1      ol9_baseos_latest 1.5 M
 openssl-devel                  x86_64 1:3.2.2-6.0.1.el9_5.1      ol9_appstream     4.0 M
 openssl-libs                   x86_64 1:3.2.2-6.0.1.el9_5.1      ol9_baseos_latest 2.1 M
Installing dependencies:
 openssl-fips-provider          x86_64 3.0.7-6.0.1.el9_5          ol9_baseos_latest 8.1 k
 openssl-fips-provider-so       x86_64 3.0.7-6.0.1.el9_5          ol9_baseos_latest 575 k

Transaction Summary
==========================================================================
Install  10 Packages
Upgrade   3 Packages

Total size: 220 M
Total download size: 8.2 M
Is this ok [y/N]: y

When installed, we start mysqld:

$ sudo systemctl start mysqld
$ sudo grep password /var/log/mysqld.log
2025-07-27T18:48:14.015136Z 6 [Note] [MY-010454] [Server] A temporary
 password is generated for root@localhost: xxxxxxxx

We connect to MySQL and we change the generated password:

$ mysqlsh root@localhost
Please provide the password for 'root@localhost': ************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): no
Error during auto-completion cache update: You must reset your password
 using ALTER USER statement before executing this statement.
MySQL Shell 9.4.0

Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Your MySQL connection id is 11
No default schema selected; type \use <schema> to set one.

MySQL  localhost:3306 ssl  SQL > set password='<YOUR PASSWORD>';
Query OK, 0 rows affected (0.0491 sec)

Metadata Schema

To work, the MySQL REST Service component requires a metadata schema to be created and populated.

Creating the metadata is the recommended very first step, even before loading the component.

Even if it’s possible to create it with the command-line client, only using the recent MySQL Shell (mysqlsh), don’t try with the old classic MySQL client (mysql), I recommend using MySQL Shell for Visual Studio Code to set up and configure the MySQL REST Service.

Using MySQL Shell for Visual Studio Code

To create services, paths, and authentication, it’s more convenient to use the GUI of MySQL Shell for Visual Studio Code. If you don’t have the plugin installed yet, please install it, and these are the first steps to create your first REST Service:

You need to have a standard DB Connection to your server:

As you can see, I’ve previously created a user (fred) with all privileges, but I prefer not to use root.

When the connection is created, right-click on it on the list on the left and configure the instance for MySQL REST Service. This action will also generate the metadata schema we discussed previously:

We can see in the Server’s error log the generation of the metadata and other plugins:

Installing the component

Once the metadata is created, you can load the component that is stored in the default plugin directory:

[root@mysql1 RPMS]# cd /usr/lib64/mysql/plugin/
[root@mysql1 plugin]# ls -lh component_mysql_rest_service.so 
-rwxr-xr-x. 1 root root 16M Jul 22 12:15 component_mysql_rest_service.so

And you install it like every other component:

SQL > install component "file://component_mysql_rest_service";
Query OK, 0 rows affected (0.1288 sec)

And in the error log, we can see the following entries:

2025-07-27T18:54:31.955831Z 11 [System] [MY-015162] [Server] 
     MySQL Rest Service Component: Starting...
2025-07-27T18:54:31.988013Z 0 [System] [MY-015162] [Server] 
     MySQL Rest Service Component: Disabling 'mysqlx' plugin

We can see that by default, the MySQL REST Service disables the X plugin and uses that port. I will come back to that in a moment.

In case you see the following message repeating every 30 seconds, it means you have skipped the metadata creation:

2025-07-27T18:54:32.009698Z 13 [System] [MY-015162] [Server] 
     MySQL Rest Service Component: Metadata schema not detected, retrying in 30s...

If it’s the case, you can still easily create it from MySQL Shell:

SQL > configure rest metadata;
Query OK, 0 rows affected (1.9330 sec)

REST metadata updated successfully.

Variables

The component creates several global variables:

SQL > select * from performance_schema.global_variables 
      where variable_name like 'compon%rest_service%';
+----------------------------------------------------------+--------------------------------+
| VARIABLE_NAME                                            | VARIABLE_VALUE                 |
+----------------------------------------------------------+--------------------------------+
| component_mysql_rest_service.developer                   |                                |
| component_mysql_rest_service.http_port                   | 33060                          |
| component_mysql_rest_service.ssl_cert                    | /var/lib/mysql/server-cert.pem |
| component_mysql_rest_service.ssl_key                     | /var/lib/mysql/server-key.pem  |
| component_mysql_rest_service.tmp_router_plugin_directory |                                |
| component_mysql_rest_service.use_ssl                     | ON                             |
| component_mysql_rest_service.user                        | mysql_mrs_1                    |
+----------------------------------------------------------+--------------------------------+
7 rows in set (0.0055 sec

Status Variables

Some dedicated status variables are also available:

SQL > select * from performance_schema.global_status
      where variable_name like 'compon%rest_service%';
+-----------------------------------------------+----------------+
| VARIABLE_NAME                                 | VARIABLE_VALUE |
+-----------------------------------------------+----------------+
| component_mysql_rest_service.http_port_source | xplugin        |
| component_mysql_rest_service.ssl_cert_source  | mysql          |
| component_mysql_rest_service.ssl_key_source   | mysql          |
+-----------------------------------------------+----------------+
3 rows in set (0.0174 sec)

About X Protocol

As I said earlier, by default, the MySQL REST Service component uses the port for the X protocol (TCP/33060) and disables the X plugin.

If you want to continue using the X Protocol, you can define a different port for the REST Service using the variable component_mysql_rest_service.http_port.

SQL > install component "file://component_mysql_rest_service" SET GLOBAL
 component_mysql_rest_service.http_port=33063;

If the X Plugin is disabled on the server, then the port for MySQL REST Service will be 8543.

For the remainder of this blog, I will use the default MySQL REST Service port (33060), which is the same as the X Plugin.

Adding a Service

All the operations that we perform with MySQL Shell for Visual Studio Code can also be executed directly in MySQL Shell. We will see later how.

It’s time to create our first service:

As you can see, I’ve enabled it and published. We also have the option to link it to one or more authentication apps; we will discuss this later.

To test, we create a schema and a sample table (fred.t1).

Then we add the schema and the table to the REST Service:

Before discussing authentication, I will remove the required authentication for the table t1 to test the service:

Mind the little lock icons that disappeared:

Now, we can use a browser to test our service. Please note that the component uses the same certificate as the MySQL Server, which is self-signed by default:

It’s always possible to interact with the MySQL REST Service using MySQL Shell in command line as mentioned before:

Authentication

Currently, four vendors are available as an authentication method with the component:

MRS is the default one, where authentication is performed against MRS REST Service-specific accounts. Applications use SCRAM (Salted Challenge Response Authentication Mechanism) to authenticate a user securely.

With MySQL Internal, authentication is handled by MRS against MySQL server user accounts. Applications send the credentials (username and password) in clear text as part of a JSON request payload to the MySQL Router for authenticating a user.

This authentication method is recommended for HTTPS-only REST services and is most suitable for applications that are not exposed publicly.

The other ones use external methods. Please check the manual for more information.

MRS

Earlier, when we configured the service, we created the default REST authentication app and a dedicated user:

This authentication app is not easy to use with curl.

But we can use an SDK for our REST Service. Let’s create it and then try to authenticate using the MRS authentication app.

SDK

We start by dumping the SDK for our service on disk:

We pay attention to using the correct REST Service URL, but we can override it when we use the SDK, and as I will write the example in Python, I choose Python as the API language:

I rename the folder as sdk and I use it in my Python code, pay attention that async calls must be used:

from sdk.my_service import *

my_service = MyService(verify_tls_cert=False)


async def main():
    await my_service.authenticate(
        user="fred_rest",
        password="<STRONG PASSWOR>",
        app="MRS",
    )
    records = await my_service.fred.t1.find()
    for record in records:
        print(record.name)


asyncio.run(main())

And we can test it:

$ python test.py
uncle scott
lefred
miguel

MySQL Internal

To test the service with MySQL Internal Authentication, we need to create a user in MySQL and ensure that the Authentication app’s name is used during the request and matches it (you will see an example later).

The user can be created without any special privileges, as access to the data is granted to the MySQL REST Service user!

SQL > create user miguel identified by '< A STRONG PASSWORD >';
Query OK, 0 rows affected (0.0260 sec)
SQL > show grants for miguel;
+------------------------------------+
| Grants for miguel@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `miguel`@`%` |
+------------------------------------+
1 row in set (0.0009 sec)

We have two options here:

  • using a cookie (not recommended)
  • using a JWT token (recommended)

Cookie

I am using curl to perform the authentication to create a cookie:

$ curl -c cookie.txt -k  -X POST  -H "Content-Type: application/json" \
   -d '{"username": "miguel",
        "password": "< STRONG PASSWORD>",
        "sessionType: "cookie",
        "authApp": "MySQL" }' \
https://192.168.57.2:33060/myService/authentication/login

And then I use the cookie like this:

$ curl -s -b cookie.txt  -k  -X GET  https://192.168.57.2:33060/myService/fred/t1 | jq
{
  "items": [
    {
      "id": 1,
      "name": "uncle scott",
      "links": [
        {
          "rel": "self",
          "href": "/myService/fred/t1/1"
        }
      ],
      "createdAt": "2025-07-27 19:35:36.000000",
      "_metadata": {
        "etag": "AEA213DBFACE1BE77A9A220610B090060ECAABDC7D7F567C6AD59BE3178AD969"
      }
    },
    {
      "id": 2,
      "name": "lefred",
      "links": [
        {
          "rel": "self",
          "href": "/myService/fred/t1/2"
        }
      ],
      "createdAt": "2025-07-27 19:35:42.000000",
      "_metadata": {
        "etag": "2D8A0EC85F5F58C485E6172149E8C37738FD5FF2DA264151A32F948F222111D0"
      }
    },
    {
      "id": 3,
      "name": "miguel",
      "links": [
        {
          "rel": "self",
          "href": "/myService/fred/t1/3"
        }
      ],
      "createdAt": "2025-07-27 19:35:48.000000",
      "_metadata": {
        "etag": "FCEE36AB938B1F08921C058F1ED6752F333C0BED697E799159E489D2FB405737"
      }
    }
  ],
  "limit": 25,
  "offset": 0,
  "hasMore": false,
  "count": 3,
  "links": [
    {
      "rel": "self",
      "href": "/myService/fred/t1/"
    }
  ]
}

As stated before, during authentication, using a valid authApp name is essential:

JWT Token

Using a JSON Web Token is the preferred method to authenticate against MRS. It’s much preferred over the cookie one.

Let’s see how to use it:

$ response=$(curl -s  -k  -X POST  -d '{"username": "miguel",
  "password": "<STRONG PASSWORD>",
  "authApp": "MySQL", "sessionType": "bearer" }' https://192.168.57.2:33060/myService/authentication/login)

$ echo "Authorization: Bearer $(echo $response| jq -r .accessToken)" > /tmp/session.dat

$ curl -s  -k  -X GET  -H "$(cat /tmp/session.dat)" https://192.168.57.2:33060/myService/fred/t1/1 | jq
{
  "id": 1,
  "name": "uncle scott",
  "links": [
    {
      "rel": "self",
      "href": "/myService/fred/t1/1"
    }
  ],
  "createdAt": "2025-07-27 19:35:36.000000",
  "_metadata": {
    "etag": "AEA213DBFACE1BE77A9A220610B090060ECAABDC7D7F567C6AD59BE3178AD969"
  }
}

Command-Line

Of course, it’s also possible to configure and deploy everything using the MySQL Shell on the command line, which can be used to script the deployment, for example.

You can export the current commands from Visual Studio Code if you want to see what they are:

This will be the generated content of the SQL file:

CREATE OR REPLACE REST SERVICE /myService
    PUBLISHED
    AUTHENTICATION
        VALIDATION "/fred/t1"
    OPTIONS {
        "http": {
            "allowedOrigin": "auto"
        },
        "headers": {
            "Access-Control-Allow-Headers": 
                             "Content-Type, Authorization, 
                              X-Requested-With, Origin, X-Auth-Token",
            "Access-Control-Allow-Methods": "GET, POST, 
                                             PUT, DELETE, OPTIONS",
            "Access-Control-Allow-Credentials": "true"
        },
        "logging": {
            "request": {
                "body": true,
                "headers": true
            },
            "response": {
                "body": true,
                "headers": true
            },
            "exceptions": true
        },
        "includeLinksInResults": false,
        "returnInternalErrorDetails": true
    }
    ADD AUTH APP `MRS` IF EXISTS
    ADD AUTH APP `MySQL` IF EXISTS;

CREATE OR REPLACE REST SCHEMA /fred ON SERVICE /myService
    FROM `fred`
    AUTHENTICATION NOT REQUIRED;

CREATE OR REPLACE REST VIEW /t1
    ON SERVICE /myService SCHEMA /fred
    AS fred.t1 CLASS MyServiceFredT1 {
        createdAt: created_at,
        id: id @KEY @SORTABLE,
        name: name
    }
    AUTHENTICATION REQUIRED;

Conclusion

Using the MySQL REST Service has never been easier, thanks to the component running on the Server. It simplifies greatly the use of SSL Certificates.

The MySQL REST Service is an ideal choice for serving JSON Documents to your Progressive Web Apps (PWA), mobile apps, or for individuals seeking to simplify access to data.

In this post, I demonstrated how to provide access to a simple table; however, this approach also works for views, stored procedures, and functions.

Please test it and send us your feedback!

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue