Reference
General MySQL

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:

Note: ReadySet proxies all traffic from application to database. You should also add the privileges your current application uses to function (eg. INSERT (opens in a new tab), DELETE (opens in a new tab), UPDATE (opens in a new tab))

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.