Destinations
MySQL
Learn how to configure MySQL as a destination in Artie, including host and port settings, service account creation, destination database routing, and the required permissions for real-time replication.
Required settings
- Host name
- Port (default is
3306) - Service account, signing in with a password or, on Amazon RDS and Aurora, an IAM role
Creating a service account
In the Dashboard, pick the Authentication method that matches how the user signs in.
CREATE USER 'artie' IDENTIFIED BY 'password';
-- Allows Artie to merge data, create and alter destination tables, and clean up
-- its temporary tables.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO 'artie';Amazon RDS and Aurora only. Artie signs in with an RDS IAM authentication token instead of a password:
CREATE USER 'artie' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
-- Allows Artie to merge data, create and alter destination tables, and clean up
-- its temporary tables.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO 'artie';Then enable IAM database authentication, create the role, and configure the connector as described in Set up role assumption. The steps are the same for a destination.
Destination database routing
In MySQL, schemas and databases are the same thing. On your pipeline’s destination, choose Write to one schema or Write to multiple schemas to control which destination database Artie uses for each replicated table.
Write to one schema
Select Write to one schema and choose the destination database. Artie writes every selected source table to that database, regardless of its source database.
You can select an existing destination database or create a new one from the Dashboard. When you create a database from the Dashboard, Artie issues CREATE DATABASE using the destination service account. The global grant above includes the required CREATE privilege.
For an existing database, the service account needs SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and ALTER on that database. If you prefer a database-scoped grant for an existing database, use:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON artie_replica.* TO 'artie';Creating a database from the Dashboard requires the CREATE privilege at the global level, for example:
GRANT CREATE ON *.* TO 'artie';Write to multiple schemas
Select Write to multiple schemas to preserve each source database as a separate destination database. Artie writes a table from each source database to the corresponding destination database, optionally prefixed with the configured schema prefix.
For example, a source table in orders.events can be written to orders.events, or to replica_orders.events when the prefix is replica_.
Turn on Create missing schemas and Artie creates each missing destination database when the pipeline deploys, which needs the global CREATE ON *.* grant. Otherwise, create every destination database before starting the pipeline. Artie creates and evolves tables within those databases. The service account needs the same table-management permissions on every destination database:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON orders.* TO 'artie';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON replica_orders.* TO 'artie';