MySQL is an open-source relational database management system.

You can also use the MySQL data connector to connect to:

• A MariaDB database. MariaDB is a community-developed, commercially supported fork of MySQL.

• An Amazon Aurora database.

During workspace creation, under Connection Type, click MySQL.

Connecting to the source database

In the Source Settings section, you provide the connection information for the source database.

Providing the connection details

To provide the connection details for the source database:

1. In the Server field, specify the server where the source database is located.

2. By default, Tonic Structural uses all of the databases on the source server. To instead identify specific databases, add them to the Databases field. To add a database, type the database name, then press Enter. To remove a database from the list, click its delete icon.

3. In the Port field, provide the port to use to connect to the source data.

4. In the Username field, provide the username to use to connect to the source data.

5. In the Password field, provide the password for the specified user.

6. To test the connection to the source data, click Test Source Connection.

Ensuring encryption of source database authentication

The Enable SSL/TLS setting indicates whether to encrypt source database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion:

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

Blocking data generation on all schema changes

By default, data generation is not blocked as long as schema changes do not conflict with your workspace configuration.

To block data generation when there are any schema changes, regardless of whether they conflict with your workspace configuration, toggle Block data generation on schema changes to the on position.

Connecting to the intermediate database for upsert

MySQL supports the upsert process. When you enable upsert for the workspace, the data generation process initially writes the transformed data to an intermediate database.

After the initial data generation is complete, the upsert job writes new records to the destination database, and updates existing records in the destination database. It does not touch any other records that are in the destination database.

In the Upsert section, when you enable upsert, you are prompted to configure the upsert processing and to provide connection information for the intermediate database.

Providing the connection details

To provide the connection details for the intermediate database:

1. In the Server field, specify the server where the intermediate database is located. For each database selected from the source, Structural generates data into a database of the same name. To prevent a conflict, the server for the intermediate database must be different from the server for the source database and the destination database.

2. In the Port field, provide the port to use to connect to the intermediate database.

3. In the Username field, provide the username to use to connect to the intermediate database.

4. In the Password field, provide the password for the specified user.

5. To test the connection to the intermediate database, click Test Intermediate Connection.

Ensuring encryption of intermediate database authentication

The Enable SSL/TLS setting indicates whether to encrypt intermediate database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion:

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

Connecting to the destination database

For a MySQL workspace, you can write the destination data to either:

• A destination database server.

• A Tonic Ephemeral snapshot. For more information, go to Writing output to Tonic Ephemeral.

• A container repository. For more information, go to Writing output to a container repository.

Under Destination Settings, to write the transformed data to a database server, click Database Server.

The destination database always uses the same databases as the source database.

Providing the connection details

To provide the connection details for the destination database:

1. In the Server field, specify the server where the destination database is located. For each database selected from the source, Structural generates data into a database of the same name. To prevent a conflict, the server for the destination database must be different from the server for the source database and the intermediate database.

2. In the Port field, provide the port to use to connect to the destination data.

3. In the Username field, provide the username to use to connect to the destination data.

4. In the Password field, provide the password for the specified user.

5. To test the connection to the destination database, click Test Destination Connection.

Ensuring encryption of destination database authentication

The Enable SSL/TLS setting indicates whether to encrypt destination database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion :

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

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

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.

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;

The following additional grants are optional. They allow you to include triggers, routines, and events.

If you use any of these GRANT options, then Structural copies the respective object types from the source to the destination database. Otherwise they are excluded.

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

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

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

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'@'%';

Tonic Structural supports:

• MySQL 5.6, 5.7, and 8.x

• MariaDB 10.0 and above