Skip to contentManaged Kafka is live: Apache Kafka on AWS for half the cost of MSK

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.

SQL
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';

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:

SQL
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:

SQL
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:

SQL
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';