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

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

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

Enable local file loading on the destination database

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 preferred_db.* 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.

Creating the destination database user

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

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

Configuring whether Structural creates the destination database schema

If you provide custom names for destination database schemas, then you cannot create the schemas yourself.

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 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.

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.