> For the complete documentation index, see [llms.txt](https://docs.tonic.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tonic.ai/app/setting-up-your-database/mysql/mysql-before-create-workspace.md).

# Before you create a MySQL workspace

Before you create the workspace, the source and destination databases should already exist.

## Database setup requirements

### Source and destination on different servers <a href="#mysql-source-dest-different-servers" id="mysql-source-dest-different-servers"></a>

When Tonic Structural generates data for MySQL, the destination database uses the same name as the source database.

To prevent a conflict, the source and destination databases must be on different servers.

### Matching MySQL tablespaces on source and destination <a href="#mysql-match-tablespaces-source-dest" id="mysql-match-tablespaces-source-dest"></a>

The MySQL tablespaces on the source database must exist in the destination database.

### Enable local file loading on the destination database <a href="#mysql-dest-local-file-load" id="mysql-dest-local-file-load"></a>

Structural writes data from the source database to files on the destination database. It then uses the `LOAD DATA` statement to load the files into the destination database. If `LOAD DATA` is not enabled on the destination database, then data generation fails.

To enable `LOAD DATA`, run the following command on the destination database:

```
SET GLOBAL local_infile = 'ON';
```

## Creating database users for Structural

On each database, you must create a user that has the required permissions that Structural needs to function.

### Creating the source database user

The following is an example of how to create a new user, called `tonic`, and then grant the necessary permissions.

For the source database, we recommend that you use a backup or fast follower database instead of a direct connection to your production environment.

```
-- create a user. '%' matches a user coming from any host.
CREATE USER 'tonic'@'%' IDENTIFIED BY 'tonic_password';

-- give the user access to schema information
GRANT PROCESS ON *.* TO tonic;

-- give the user access to tables in your preferred DBs
GRANT SELECT, SHOW VIEW ON preferred_db.* TO tonic;

```

If you have stored routines that other database objects reference, then you must grant permissions for routines. Otherwise your jobs will fail. Stored routines include procedures and functions.

```
-- To include routines on MySQL 5.7
GRANT SELECT ON mysql.proc TO tonic;

-- To include routines on MySQL 8
GRANT SHOW_ROUTINE ON *.* TO tonic;
```

If you have triggers or events that are important to the functionality of your database, then you should also grant permissions for triggers or events.

```
-- To include triggers
GRANT TRIGGER ON preferred_db.* TO tonic;

-- To include events
GRANT EVENT on preferred_db.* TO tonic;
```

When you specify a  `GRANT` options for an object type, then Structural copies that object type from the source to the destination database. Otherwise the object type is excluded.

To verify the granted permissions, run `show grants for tonic`. The output is something like:

```
+-----------------------------------------------------------------------+
| Grants for tonic@%                                                    |
+-----------------------------------------------------------------------+
| GRANT PROCESS ON . TO tonic@%                                         |
| GRANT SHOW_ROUTINE ON . TO tonic@%                                    |
| GRANT SELECT, SHOW VIEW, EVENT, TRIGGER ON mysqlkt.* TO tonic@%       |
+-----------------------------------------------------------------------+
```

### Creating the destination database user

{% code overflow="wrap" %}

```
-- create a new user. 
CREATE USER 'tonic'@'%' IDENTIFIED BY 'tonic_password';

-- This user must be granted access to everything. Amazon RDS handles this differently
-- from vanilla MySQL.

-- On Amazon RDS 
"GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO `tonic`@`%` WITH GRANT OPTION"
GRANT SHOW_ROUTINE ON *.* TO `tonic`@`%` WITH GRANT OPTION

-- On vanilla MySQL
GRANT ALL PRIVILEGES ON * . * TO 'tonic'@'%';
```

{% endcode %}

## Configuring how Structural reads MySQL source data <a href="#mysql-source-data-read" id="mysql-source-data-read"></a>

The first step in data generation is to read the source data. Structural only needs to read source data from tables that use the De-Identify or Incremental table modes.

For Incremental mode, Structural always reads each table straight through.

For De-identify mode, how Structural reads the data is based on the size of the table and the Structural configuration.

### Using a single query per table <a href="#source-read-single-query" id="source-read-single-query"></a>

By default, batched reads are disabled, and Structural uses a single query to read each table.

### Enabling and configuring batched reads <a href="#source-read-batched" id="source-read-batched"></a>

When batched reads are enabled, Structural reads the table in batches that are sized by data volume. The rows stream in primary-key order. When a batch reaches a target amount of data, the batch ends. Batched reads replace a single long query with a series of shorter ones.

To enable batched reads, set the [environment setting](/app/admin/environment-variables-setting.md) `TONIC_MYSQL_KEYSET_BATCH_TARGET_MEGABYTES` to the number of megabytes in each batch. You can configure this setting from the **Environment Settings** tab on **Structural Settings**. The default value is `0`, which indicates that batched reads are disabled.

When batched reads are enabled, the [environment setting](/app/admin/environment-variables-setting.md) `TONIC_MYSQL_KEYSET_MAX_ROWS_PER_BATCH` also sets a limit on the number of rows. You can also configure this setting from the **Environment Settings** tab on **Structural Settings**.

For example, when data is sparse or a table is narrow, it might take a long time for a batch to reach the data volume limit. When the batch reaches the maximum number of rows, then even if it has not yet reached the data volume limit, Structural starts a new batch. By default, the row limit is 500,000.

Batched reads require a primary key. If the data does not have a primary key, then even if batched reads are enabled, Structural uses the single query.

## Configuring whether Structural creates the destination database schema <a href="#mysql-schema-creation-config" id="mysql-schema-creation-config"></a>

{% hint style="info" %}
If you provide custom names for destination database schemas, then you cannot create the schemas yourself.
{% endhint %}

By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.

If you prefer to manage the destination database schema yourself, then set the [environment setting](/app/admin/environment-variables-setting.md) `TONIC_MYSQL_SKIP_CREATE_DB` to `true`. You can configure `TONIC_MYSQL_SKIP_CREATE_DB` from the **Environment Settings** tab on **Structural Settings**.

When `TONIC_MYSQL_SKIP_CREATE_DB` is true, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.

Note that you can override this setting in individual workspaces. For more information, go to [Advanced workspace overrides](/app/workspace/workspace-configuration-settings/advanced-overrides.md).

During data generation, Structural deletes the data from the destination database tables, except in the following cases:

* Tables that use Preserve Destination or Incremental mode
* Upsert data generation

It then populates the tables with the new destination data.

For a diagram of the data generation process when you manage the destination schema, go to [Data generation process](/app/workflows/data-generation-run-job/data-generation-process.md#data-process-customer-schema).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.tonic.ai/app/setting-up-your-database/mysql/mysql-before-create-workspace.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.