MySQL
Preparation
To allow Shaped to connect to your MySQL database, you need to create a read-only user and share those credentials through the Create Dataset endpoint. You can create a read-only user on a MySQL database with the following commands:
# 1. Create a new user.
CREATE USER '[username]'@'%' IDENTIFIED BY '[password]';
# 2. Grant the user read access to all the tables in the schema. Note you can also
# restrict this to your specific user, item and interaction views.
GRANT SELECT ON [database_name].* TO '[username]'@'%';
# 3. Save.
FLUSH PRIVILEGES;
Network Access
If your database is publicy accessible, reach out to the Shaped team to get our IPs for allow listing. Otherwise, check out our private link docs.
Dataset Configuration
Required fields
| Field | Example | Description |
|---|---|---|
| schema_type | MYSQL | Specifies the connector schema type, in this case "MYSQL". |
| table | movies | The name of the table to sync. |
| user | your_user | Access account username. |
| password | pAssw0rd1! | Access account Password. |
| host | my-mysql-db.xxxxxxx.us-east-2.rds.amazonaws.com | Database hostname. |
| port | 3306 | Database port (the default for MySQL is 3306). |
| database | movielens | The name of the database that contains table to sync. |
| replication_key | updated_at | The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs. |
Optional fields
| Field | Example | Description |
|---|---|---|
| columns | ["productId", "color", "brand", "stockLevel"] | The name of the columns you wish to sync from MySQL into Shaped. If not specified, all columns will be synced. |
| unique_keys | ["productId"] | Specify a list of columns that uniquely identify a row in the table, if duplicate rows are inserted with these keys, the latest row will be used. |
| batch_size | 100000 | The number of rows to fetch from the database in each batch, changing this can improve throughput for large tables. The default is 10000. |
Dataset Creation Example
Below is an example of a MySQL dataset connector configuration:
name: your_mysql_dataset
schema_type: MYSQL
table: movies
user: your_user
password: pAssw0rd1!
host: my-mysql-db.xxxxxxx.us-east-2.rds.amazonaws.com
port: 3306
database: movielens
replication_key: updated_at
The following payload will create a MySQL dataset and begin syncing data from Shaped using the Shaped CLI.
shaped create-dataset --file dataset.yaml