General MySQL Configuration
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- During initial snapshot, Readyset executes the metadata lockLOCK INSTANCE FOR BACKUPto prevent unsafe statements such as DDL's from happening while snapshot is in progress.LOCK TABLES- 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 ofSHOW BINARY LOGS.REPLICATION CLIENT- During initial snapshot, Readyset executesSHOW MASTER STATUSto get a consistent binlog file and position for each table.REPLICATION SLAVE- 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- Used to snapshot data withing replicated tables.
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.