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 grant the necessary permissions.

For the source database, we recommend that you use a backup or fast follower database instead of connecting directly 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'@'%';