This section guides you through a worked data generation example in MySql. It includes steps to create an empty target database, which is then used in the example. It provides links to further examples and to resources to understand how the data generatorworks.

Contents

Preparation

Preparation of Data Generator

Please do the following before beginning the worked example

1. Install the data generator CLI.
2. Verify your installation by running the following command in a terminal window:
   1. Windows cmd: datagenerator.exe --version
   2. Windows PowerShell: ./datagenerator --version
   3. Linux: ./datagenerator --version

The data generator should report its version number as below (example in Windows cmd).

C:\DataGeneration\>datagenerator.exe --version
0.2.0.0

Preparation of Database Engine

MySQL comes with a command-line SQL query tool, mysql. It can be found under the directory where you have installed MySQL, in Windows this is typically C:\Program Files\MySQL\MySQL Server 8.0\bin\. You can run mysql by opening a command prompt directly in this directory, or add the directory to your PATH environment variable to enable running mysql from any where on your computer.

Executing SQL queries in mysql requires setting up a MySQL user and password. If you don't have one already, the easiest way to do this is using MySQL Workbench. Entering MySQL Workbench for the first time, you will be prompted to create a password for the user root. After creating the password and finishing the setup in MySQL Workbench, execute the following at a command prompt:

mysql -uroot -p

Enter your password when prompted. If successful you should see something similar to the following (can vary depending on your MySQL version):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now you have entered mysql and you should be able to execute SQL queries against the databases you created. To exit mysql, simply type:

quit

Create a target database

Create an empty database called TargetDatabase in MySQL.

You can do this using a graphical tool like MySQL Workbench.

Alternatively you can do this by executing this SQL query below, either in Workbench or in mysql.

CREATE DATABASE TargetDatabase;

Populate the database

First, connect to the newly created TargetDatabase, using pgAdmin or the command below in mysql:

USE TargetDatabase;

If connected successfully, you should see in mysql a line of output saying Database changed.

After that, download the SQL script sample-mysql-database.sql and execute it in the new database. 

Again, you can do this using Workbench, or you can execute the SQL query below in mysql. Substitute the correct path to the script file you have just downloaded.

SOURCE /scripts/sample-db/sample-mysql-database.sql;

This will create a sample database with four empty tables. It represents a simple social media website, where users create posts and comment on posts, and users belong to organizations.

Check the database contents

Check that the tables exist and are empty. Use Workbench, or use a SQL query as below which lists all the table names:

SHOW TABLES;

And a SQL query as below to list the data in a table:

SELECT * FROM `Users`;

Run the data generator

The commands below run the data generator. If the commands cannot run successfully, you may need to work through the connection strings section below to configure them to your environment.

Click on the sections below to view the example command lines:

./datagenerator.exe `
--database-engine mysql `
--target-connection-string "Server=localhost;Port=60503;Database=TargetDatabase;Uid=root;Pwd=Password1!;" `
--rows-to-generate 1000

./datagenerator.exe ^
--database-engine mysql ^
--target-connection-string "Server=localhost;Port=60503;Database=TargetDatabase;Uid=root;Pwd=Password1!;" ^
--rows-to-generate 1000

#!/usr/bin/env bash ./datagenerator.exe `
--database-engine mysql \
--target-connection-string "Server=localhost;Port=60503;Database=TargetDatabase;Uid=root;Pwd=Password1!;" \
--rows-to-generate 1000

Connection strings

You may need to change the connection strings to suit your environment. The connection strings identify the source and target database. 

Change Username and Password to match that of your own.

Use server to specify the MySQL server containing the database. In the example above we are using localhost to connect to an instance running on the same computer that is running the data generator.

We have further advice on connection strings in our troubleshooting and known limitations section. There is also a lot of information online about connection strings.

Output from the command

The command will produce detailed output similar to that shown below. The details may vary. Check that the last line of output says Data generation completed.

2023-11-07 14:16:49.622 +00:00 [INFO] DataGenerator has started in 'Debug' mode. Log folder is C:\ProgramData\Red Gate\Logs\TDM\DataGenerator.
2023-11-07 14:16:49.632 +00:00 [INFO] Validating data generation configuration data...
2023-11-07 14:16:49.746 +00:00 [INFO] Completed initial validation of data generation configuration data in 00h 00m 00s.113ms
2023-11-07 14:16:49.773 +00:00 [INFO] Using Application Insights telemetry reporter in bucket 'tdm-test' (debug) in developer mode.
2023-11-07 14:16:50.017 +00:00 [INFO] Running MySql database table extraction using target 'TargetDatabase' database on 'localhost' server...
2023-11-07 14:16:50.063 +00:00 [INFO] Successfully extracted 4 table(s) (with 4 relationships) from target 'TargetDatabase' database on 'localhost' server in 00h 00m 00s.046ms.
2023-11-07 14:16:50.138 +00:00 [INFO] Validating target 'TargetDatabase' database on 'localhost' server structure...
2023-11-07 14:16:50.153 +00:00 [INFO] Completed validation of target 'TargetDatabase' database on 'localhost' server structure in 00h 00m 00s.014ms...
2023-11-07 14:16:50.231 +00:00 [INFO] Creating data generation plan
2023-11-07 14:16:50.354 +00:00 [INFO] Starting to generate data
2023-11-07 14:17:11.346 +00:00 [INFO] Data generation completed in 00h 00m 21s.575ms.

Check the data in the target database

Check the contents of the tables in the target database. You should find that each table contains 1000 data rows.

Congratulations! This shows that the data has been generated successfully.

Further examples

The documentation on configuration files explains the formats and provides worked examples of their use, that build on this worked example. 

Learning more about how the data generator works

Now that you have carried out the worked example, you might like to understand more about how the data generator works. Please consult the page Using the worked example to understand the data generator. This page uses the output and the commands from the worked example to explain how the data generator produces referentially valid data.