> ## Documentation Index
> Fetch the complete documentation index at: https://docs.goldsky.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL

[MySQL](https://www.mysql.com/) is a powerful, open source object-relational database system used for OLTP workloads.

Mirror supports MySQL as a sink, allowing you to write data directly into MySQL. This provides a robust and flexible solution for both mid-sized analytical workloads and high performance REST and GraphQL APIs.

When you create a new pipeline, a table will be automatically created with columns from the source dataset. If a table is already created, the pipeline will write to it. As an example, you can set up partitions before you setup the pipeline, allowing you to scale MySQL even further.

MySQL also supports Timescale hypertables, if the hypertable is already setup. We have a separate Timescale sink in technical preview that will automatically setup hypertables for you - contact [support@goldsky.com](mailto:support@goldsky.com) for access.

Full configuration details for MySQL sink is available in the [reference](/mirror/reference/config-file/pipeline#mysql) page.

## Role Creation

Here is an example snippet to give the permissions needed for pipelines.

```sql theme={null}

CREATE ROLE goldsky_writer WITH LOGIN PASSWORD 'supersecurepassword';

-- Allow the pipeline to create schemas.
-- This is needed even if the schemas already exist
GRANT CREATE ON DATABASE mysql TO goldsky_writer;

-- For existing schemas that you want the pipeline to write to:
GRANT USAGE, CREATE ON SCHEMA <schemaName> TO goldsky_writer;

```

## Secret Creation

Create a MySQL secret with the following CLI command:

```shell theme={null}
goldsky secret create --name A_MYSQL_SECRET --value '{
  "type": "jdbc",
  "protocol": "mysql",
  "host": "db.host.com",
  "port": 5432,
  "databaseName": "myDatabase",
  "user": "myUser",
  "password": "myPassword"
}'
```

## Examples

### Getting an edge-only stream of raw logs

This definition gets real-time edge stream of raw logs straight into a MySQL table named `eth_logs` in the `goldsky` schema, with the secret `A_MYSQL_SECRET` created above.

```yaml theme={null}
name: ethereum-raw-logs-to-mysql
apiVersion: 3
sources:
  my_ethereum_raw_logs:
    dataset_name: ethereum.raw_logs
    version: 1.0.0
    type: dataset
    start_at: latest
transforms:
  logs:
    sql: |
      SELECT
          id,
          address,
          topics,
          data,
          block_number,
          block_hash,
          transaction_hash
      FROM
          my_ethereum_raw_logs
    primary_key: id
sinks:
  my_mysql_sink:
    type: mysql
    table: eth_logs
    schema: goldsky
    secret_name: A_MYSQL_SECRET
    from: logs
```

## Tips for backfilling large datasets into MySQL

While MySQL offers fast access of data, writing large backfills into MySQL can sometimes be hard to scale.

Often, pipelines are bottlenecked against sinks.

Here are some things to try:

### Avoid indexes on tables until *after* the backfill

Indexes increase the amount of writes needed for each insert. When doing many writes, inserts can slow down the process significantly if we're hitting resources limitations.

### Bigger batch\_sizes for the inserts

The `sink_buffer_max_rows` setting controls how many rows are batched into a single insert statement. Depending on the size of the events, you can increase this to help with write performance. `1000` is a good number to start with. The pipeline will collect data until the batch is full, or until the `sink_buffer_interval` is met.

### Temporarily scale up the database

Take a look at your database stats like CPU and Memory to see where the bottlenecks are. Often, big writes aren't blocked on CPU or RAM, but rather on network or disk I/O.

For Google Cloud SQL, there are I/O burst limits that you can surpass by increasing the amount of CPU.

For AWS RDS instances (including Aurora), the network burst limits are documented for each instance. A rule of thumb is to look at the `EBS baseline I/O` performance as burst credits are easily used up in a backfill scenario.

### Aurora Tips

When using Aurora, for large datasets, make sure to use `Aurora I/O optimized`, which charges for more storage, but gives you immense savings on I/O credits. If you're streaming the entire chain into your database, or have a very active subgraph, these savings can be considerable, and the disk performance is significantly more stable and results in more stable CPU usage pattern.