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