General MySQL

If you are not using AWS RDS or Aurora MySQL, use these general instructions to configure your MySQL database.

Instructions

To ensure you have the correct permissions set, run the following commands as the database user you will use Readyset 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 user has sufficient privileges.

Readyset uses below list of privileges:

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

Note: Readyset 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 (opens in a new tab) 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.

MySQL ProxySQL MySQL Integration