Readyset Docs

This guide will walk you through the process of configuring a MySQL 8.0 database to work with Readyset Cache. Please note that other versions are untested so you might run into issues.

Instructions

The replication settings and privileges below are required for deep caching, where Readyset Cache snapshots your tables and follows the replication stream to keep cached results up to date. Shallow (TTL-based) caching does not require replication or the snapshot/replication privileges; it needs only standard read access (SELECT) to run your queries. See Shallow Caching.

To ensure you have the correct permissions set, run the following commands as the database user you will use Readyset Cache with:

1. Ensure MySQL version 8.0 is running.

mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 8.0.33 |
+---------------+--------+
1 row in set (0.17 sec)

2. Ensure replication is enabled and properly configured .

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3040038 | No        |
| binlog.000002 |  34978315 | No        |
| binlog.000003 |  34978333 | No        |
+---------------+-----------+-----------+
3 rows in set (0.07 sec)

If any files are returned, binary logging is correctly enabled.

mysql> SELECT @@global.binlog_format, @@global.binlog_row_image, @@global.binlog_transaction_compression, @@global.binlog_encryption\G
*************************** 1. row ***************************
                 @@global.binlog_format: ROW
              @@global.binlog_row_image: FULL
@@global.binlog_transaction_compression: 0
             @@global.binlog_encryption: 0
1 row in set, 1 warning (0.00 sec)

Ensure binlog_format is set to ROW, binlog_row_image is set to FULL, binlog_transaction_compression is 0 and binlog_encryption is also set to 0.

3. Ensure Readyset Cache user has sufficient privileges.

Readyset Cache uses below list of privileges:

BACKUP_ADMIN - During initial snapshot, Readyset Cache executes the metadata lock LOCK INSTANCE FOR BACKUP to prevent unsafe statements such as DDL's from happening while snapshot is in progress.
LOCK TABLES - During initial snapshot, Readyset Cache takes a brief lock in the table in order to start a transaction and have a consistent view of the table data and correlate it with the binlog position taken from the output of SHOW BINARY LOGS.
REPLICATION CLIENT - During initial snapshot, Readyset Cache executes SHOW MASTER STATUS to get a consistent binlog file and position for each table.
REPLICATION SLAVE - Readyset Cache register itself as a replica for CDC (Change Data Capture). This enables Readyset Cache to automatically keep cache entries up to date when data changes in your database.
SELECT - Used to snapshot data within replicated tables.

Note: Readyset Cache proxies all traffic from application to database. You should also add the privileges your current application uses to function (eg. INSERT, DELETE, UPDATE)

Example:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON YOUR_DATABASE.* TO USER@'HOST';
mysql> GRANT BACKUP_ADMIN, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO USER@'HOST';

AWS RDS-only configuration

If you are using AWS RDS, you will also need to do the following:

1. Make sure binlog retention is enabled.

mysql> call mysql.rds_show_configuration;
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                          |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| binlog retention hours |    1  | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

If the value is set to NULL, you must configure binlog retention to be at least long enough for snapshotting to complete. A reasonable value here is one hour of retention for every 150 GB of database size.

Connecting to Readyset Cache

Readyset Cache is wire-compatible with MySQL, so you can use the mysql client to connect to it in the same way you would an ordinary database.

Readyset Cache uses the same username, password and database name as your upstream database. To connect, fill in those values and run:

mysql -h<READYSET HOST> -u<USER> -p<PASSWORD> -P<READYSET PORT> <DATABASE>

Once connected, you can see the status of tables in Readyset Cache by running:

mysql> SHOW READYSET STATUS;

For details on how clients authenticate to Readyset Cache, see MySQL Authentication.