MySQL Setup Guide
This guide covers the following deployments:
- MySQL (including AWS RDS for MySQL)
- AWS RDS for MariaDB
- PlanetScale
- Self-hosted Vitess
Follow the step for your deployment, then continue to the Complete Setup in Matia section.
| Deployment | Follow |
|---|---|
| MySQL / AWS RDS | Step 1 |
| AWS RDS for MariaDB | AWS RDS for MariaDB |
| PlanetScale | Step 2 |
| Self-hosted Vitess | Step 3 |
Prerequisites
- Matia connects from the following IPs:
23.21.86.124/32,52.20.96.22/32and44.219.180.239/32. If your database restricts inbound traffic, allow these IPs. (For PlanetScale, see IP restrictions below.)
Step 1: MySQL
-
MySQL version 8.0 or above
-
(Optional) A dedicated MySQL database user with replication permissions. Creating a dedicated user is recommended for better permission control and auditing:
CREATE USER <matia_user>@'%' IDENTIFIED WITH mysql_password BY 'password' -
Grant replication privileges to your database user. The user must also be granted SELECT privileges for all columns you wish to sync:
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <matia_user>@'%'; -
Make sure that your database is configured as follows:
[mysqld]binlog-format=ROWlog-bin=mysql-binlogserver-id=123456789expire-logs-days=7log-slave-updates=1- Enables ROW format binary log replication, which is required to perform incremental updates.
- Name the binary log (for example, mysql-binlog).
- If your configuration already has a log-bin entry, you don't need to change it.
- If your configuration already has a server-id entry, you don't need to change it. Otherwise, choose any number between 1 and 4294967295 as the server-id.
- Set the log expiration to a minimum of one day. We recommend seven days.
- Restart your MySQL server to effect these changes.
AWS RDS
- Grant read access to the
mysql.rds_heartbeat2system table:GRANT SELECT ON mysql.rds_heartbeat2 TO <matia_user>@'%'; - Grant read access to the
mysql.rds_configurationsystem table:GRANT SELECT ON mysql.rds_configuration TO <matia_user>@'%'; - Grant execute permission on the
mysql.rds_killstored procedure:GRANT EXECUTE ON PROCEDURE mysql.rds_kill TO <matia_user>@'%'; - Set the
binlog_formattoROWin your DB parameter group. - Enable automatic backups for your DB instance.
- If you connected Matia to a read replica, check the value of the
slave_parallel_workersparameter in your DB parameter group. If it's set to0, the following changes are not required. Otherwise, update the DB parameter group with:slave_preserve_commit_order = 1slave_parallel_type = LOGICAL_CLOCKbinlog_order_commits = 1
- If you modified any of the settings above, reboot the instance to apply the changes.
- We recommend setting your binlog retention period to seven days (168 hours):
CALL mysql.rds_set_configuration('binlog retention hours', 168);
AWS RDS for MariaDB
Matia reads changes from MariaDB through the binary log, the same way it does for MySQL. On AWS RDS, all server settings are managed through the DB parameter group instead of a my.cnf file.
Configure the DB parameter group
RDS doesn't let you change the default parameter group. If your instance uses the default group, create a custom one first.
- In the RDS console, go to Parameter groups and click Create parameter group.
- Select the mariadb family that matches your engine version (for example,
mariadb10.11), enter a name, and click Create. - Open the new parameter group, click Edit, and set:
binlog_format=ROWbinlog_row_image=FULL
- Click Save changes.
- Go to Databases, select your instance, and click Modify. Under Additional configuration, select the new parameter group, then click Continue and Apply immediately.
- Reboot the instance to apply the parameter group. The instance is unavailable while it reboots, so schedule this for a maintenance window.
Enable automatic backups
RDS turns on binary logging only when automatic backups are enabled.
- In the RDS console, select your instance and click Modify.
- Under Backup, set Backup retention period to at least 1 day.
- Click Continue and Apply immediately.
Set the binlog retention period
Connect to the instance with the mysql or mariadb client and set the binlog retention to seven days (168 hours):
CALL mysql.rds_set_configuration('binlog retention hours', 168);
Connect to a read replica (optional)
You can point Matia at a read replica so the load stays off your primary instance. If you do, set up the replica itself. It may not have the same settings as the primary, so check them on the replica:
- Automatic backups: select the replica, click Modify, and set Backup retention period to at least 1 day. RDS won't write binary logs on the replica without this.
- Parameter group: attach the same custom parameter group to the replica (
binlog_format=ROW,binlog_row_image=FULL), then reboot the replica during a maintenance window. - Binlog retention: connect to the replica and run:
CALL mysql.rds_set_configuration('binlog retention hours', 168);
Run the CREATE USER and GRANT statements on the primary. They replicate to the replica automatically. Use the replica's Endpoint as the Hostname in Matia.
Create the Matia user
Connect to the instance as the master user and run:
CREATE USER <matia_user>@'%' IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <matia_user>@'%';
On MariaDB 10.5.9 and above, also run:
GRANT REPLICA MONITOR ON *.* TO <matia_user>@'%';
Replace password with a strong password of your own. The user must have SELECT on every table you want to sync.
Note: In MariaDB 10.5 and above,
REPLICATION CLIENTis an alias forBINLOG MONITORandREPLICATION SLAVEis an alias forREPLICATION REPLICA. Either name works.REPLICA MONITORlets Matia check the instance's replication status, whichREPLICATION CLIENTno longer covers from 10.5.9.
Allow Matia access to the instance
If Matia connects directly, the instance must be Publicly accessible and allow inbound traffic from Matia's IPs. If the instance is in a private subnet, use SSH instead and allow access from your SSH server rather than from Matia's IPs.
Security group
- In the RDS console, select your instance. On the Connectivity & security tab, note the Endpoint and Port (usually
3306). - Click the link under VPC security groups.
- On the Inbound rules tab, click Edit inbound rules, then Add rule.
- Set Type to MYSQL/Aurora, set Port range to your port, and set Source to Custom. Enter one of Matia's IPs. Add a rule for each IP.
- Click Save rules.
Network ACLs
- On the Connectivity & security tab, click the VPC link, then the VPC ID.
- On the Details tab, click the Main network ACL link.
- On the Inbound rules tab, make sure there is a rule that allows Matia's IPs to reach your database port. Default VPCs already have an
ALL - 0.0.0.0/0 - ALLOWrule. - On the Outbound rules tab, make sure there is a rule that allows traffic to ports
1024-65535. If there is noALL - 0.0.0.0/0 - ALLOWrule, click Edit outbound rules and add one.
Use the Endpoint as the Hostname and the Port in the Complete Setup in Matia section, and select MySQL as the Server Type.
Step 2: PlanetScale
Matia reads changes from PlanetScale through the Vitess VStream API. Everything is configured in the PlanetScale console.
You don't need to configure the binlog, run
GRANTstatements, or change any server settings. The MySQL steps above don't apply to PlanetScale.
Create a password
- In the PlanetScale console, open your database and go to Settings > Passwords.
- Click New password.
- Enter a name (for example,
matia). - Select the branch you want to sync. This is usually your production branch (for example,
main). - Under Role, select Read-only. This covers both the initial sync and incremental changes.
- Click Create password.
- Copy the Host, Username, and Password. PlanetScale shows the password only once.
Find your keyspace
The keyspace has the same name as your PlanetScale database. If your database has more than one keyspace (for example, a sharded database), you can find them on the branch's overview page. Each Matia connector syncs one keyspace.
Connection values
Use these values in the Complete Setup in Matia section:
| Field | Value |
|---|---|
| Hostname | Host from the password page (for example, aws.connect.psdb.cloud) |
| Port | 3306 |
| Username | Username from the password page |
| Password | Password from the password page |
| Server Type | Vitess |
| VStream RPC Port | 443 (default) |
| Keyspace | Your PlanetScale database name |
Step 3: Self-hosted Vitess
These settings apply only to Vitess you run yourself. PlanetScale manages them for you.
- Matia's IPs can reach both VTGate ports: the MySQL port and the gRPC port used for VStream (set by
--grpc_port). - A Vitess user with read access to the keyspace you want to sync.
- Partial row features are turned off:
- Don't enable the noblob bit in the tablets'
--vreplication-experimental-flags. - Don't set
binlog_row_value_options=PARTIAL_JSON.
- Don't enable the noblob bit in the tablets'
- Full row images are set on the underlying MySQL tablets:
binlog_format=ROWandbinlog_row_image=FULL(the Vitess defaults).
Complete Setup in Matia
- Under Authentication Method, select:
- Direct to connect directly to your database.
- SSH to connect through an SSH tunnel on a server in your network. SSH is available for MySQL and MariaDB only. It isn't available yet for PlanetScale or Vitess.
- Enter the Username and Password for your database user. For PlanetScale, use the values from the password page.
- Enter the Hostname for your database.
- Enter the Port for your database.
- (Optional) If you are connecting through SSH, enter your SSH Hostname, Port, and Username. Make sure to add the Public Key we've generated for your organization to your server's authorized keys.
- Under Server Type, select:
- MySQL for MySQL and MariaDB connections. Then, optionally, enter the Database to replicate.
- Vitess for PlanetScale and self-hosted Vitess. Then enter:
- VStream RPC Port (Required): keep the default
443for PlanetScale. For self-hosted Vitess, enter your VTGate gRPC port (set by--grpc_port). - Keyspace (Required): the keyspace to sync. For PlanetScale, this is your database name.
Note: Each Vitess connection can have one keyspace only.
- VStream RPC Port (Required): keep the default
- Enter a Name for the connector.
- (Optional) Enter a Description for the connector.
- Select the Owner of the connector.
- (Optional) Verify that your database is successfully connected by clicking Test Connection.
- Click Connect.