Skip to content
GBG Documents & Biometrics Documentation

Migrating the database to MySQL

AssureID Connect supports MySQL or MySQL-compatible databases running the InnoDB database engine. GBG recommends Amazon Aurora for hosting a database in the cloud. GBG provides a sample script based on MySQL Workbench and the wbcopytables.exe file that comes with it.

Note: You can use any method to migrate the data from SQL Server to MySQL; however, due to differences in the database engines, it is important that you do not migrate the Configuration table because it contains values that are specific to the database engine.

The copy-migrated-tables.cmd script assumes that MySQL Workbench is installed on the current machine using the default settings (c:\Program Files\MySQL\MySQL Workbench 6.3 CE\ wbcopytables.exe). The copy-migrated-tables.cmd script is available in: AssureID_Connect_Enterprise.zip\Deploy\MySQLSetup

Depending on your configuration, either create an Amazon Web Services Aurora RDS cluster and instance, or install and configure a MySQL installation.

Create an Aurora RDS cluster and instance

Using Aurora, create an Aurora RDS Cluster and Instance with the following minimum settings:

Setting Description
Instance class db.r3.large (2 virtual CPUs, 15 GB RAM)
DB Parameter Group default.aurora5.6
Disk space 100 GB minimum

Note: See https://aws.amazon.com/rds/aurora/ for more information about Amazon Aurora.

Install and configure a MySQL Server configuration

Install and configure a MySQL Server configuration adjacent to the existing Microsoft SQL Server. GBG recommends the following minimum requirements:

Setting Description
MySQL version MySQL Server 5.6.10a or later
Database engine InnoDB database engine
Free disk space 100 GB or more

Note: See https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html for more information about the InnoDB Storage Engine. For more information about MySQL, go to https://dev.mysql.com/.

Migrate the database to MySQL

  1. Open copy-migrated-tables.cmd and set the variables as instructed. At a minimum, you need to supply the source and target ODBC connection strings.

  2. Change the source (unless you are running on the local SQL server and have Windows Authentication):

    set source="DRIVER={SQL Server};SERVER=;DATABASE={AssureIDConnect};UID=;PWD="

  3. Change the following variables to match your destination server and security settings:

    set username=”**”
    set password=””
    set hostname=”**”
    set port=”3306”

  4. Set the sslmode. Options are DISABLED, PREFERRED, REQUIRED, VERIFYCA and VERIFYIDENTITY:

    set sslmode=”PREFERRED”

  5. Open a command prompt window and run the copy-migrated-tables.cmd file to migrate the table data. See the following Example.

    Note: The time required to copy the data is dependent on the number of records to be copied. The LogEntry table is not migrated. This table contains only application logging information. The Configuration table is not migrated. Instead, it is populated with the default configuration shipped with AssureID Connect.

Example

Section titled “Example”
c:\MySQLSetup>;copy-migrated-tables.cmd
c:\MySQLSetup>;REM Workbench Table Data copy script
c:\MySQLSetup>;REM Workbench Version: 6.3.8
c:\MySQLSetup>;REM
c:\MySQLSetup>;REM Execute this to copy table data from a source RDBMS to MySQL.
c:\MySQLSetup>;REM Edit the options below to customize it. You will need to provide passwords, at least.
c:\MySQLSetup>;REM
c:\MySQLSetup>;REM Source DB: Mssql@DRIVER=SQL Server;SERVER= (Microsoft SQL Server)


Loading table information from file C:\Users\connectuser\AppData\Local\Temp\wb_tables_to_migrate.txt
15:03:31 [INF][copytable]: --table [AssureIDConnect] [dbo].[DocumentField] `AssureIDConnectDocumentField` [id] `id` [id], [document_id], CAST([library_field_id] as VARCHAR(64)) as [library_field_id], [region_id], [name], [type_name], [data_source], [value_chip_data_id], [value_data_id], [value_date], [value_string], CONVERT(VARBINARY(MAX), [value_binary], 0) as [value_binary], [value_binary_is_image], [reliability], [is_hidden], [x], [y], [width], [height], [created_date], [modified_date]
15:03:31 [INF][copytable]: --table [AssureIDConnect][dbo].[DocumentImage] `AssureIDConnect` `DocumentImage` [id] `id` [id], [document_id], [side], [light], [width], [height], [horizontal_resolution], [vertical_resolution], [mime_type], CONVERT(VARBINARY(MAX), [raw_data], 0) as [raw_data], [is_cropped], [top_right_x], [top_right_y], [top_left_x], [top_left_y], [bottom_left_x], [bottom_left_y], [bottom_right_x], [bottom_right_y], [created_date], [modified_date]
15:03:31 [INF][copytable]: --table [AssureIDConnect] [dbo].[Device] `AssureIDConnect` `Device` [id] `id` [id], [type_id], [serial_number], [has_contactless_chip_reader], [has_magnetic_stripe_reader], [created_date], [modified_date]
15:03:31 [INF][copytable]: --table [AssureIDConnect] [dbo].[Document] `AssureIDConnect` `Document` [id] `id` [id], CAST([instance_id] as VARCHAR(64)) as [instance_id], [user_id], [subscription_id], [device_id], CAST([library_document_type_id1] as VARCHAR(64)) as [library_document_type_id1], CAST([library_document_type_id2] as VARCHAR(64)) as [library_document_type_id2], [name], [issue], [size], [issuer_code], [issue_type], [class], [class_code], [keesing_code], [is_generic], [initial_classification_side], [is_inverted1], [is_inverted2], [orientation1], [orientation2], [presentation_changed], [has_partial_classification], [process_mode], [classification_mode], [authentication_sensitivity], [result], [classification_time], [extraction_time], [authentication_time], [status], [is_deleted], [cropping_mode], [cropping_expected_size], [created_date], [modified_date]


15:03:31 [INF][copytable]: Connecting to MySQL server at myproduction-aurora-dbcluster-1xxxxx1x1xxxx.cluster-xxxxxxxxx99x.us-east-1.rds.amazonaws.com:3306 with user connect
15:03:31 [INF][copytable]: Connection to MySQL opened
15:03:31 [INF][copytable]: Opening ODBC connection to [unknown] 'DRIVER={SQL Server};SERVER=;DATABASE={AssureIDConnect};UID=;PWD='


15:03:33 [INF][copytable]: ODBC connection to 'DRIVER={SQL Server};SERVER=;DATABASE={AssureIDConnect};UID=;PWD=' opened
.
.
.
END:`AssureIDConnect`.`User`:Finished copying 2 rows in 0m00s
END:`AssureIDConnect`.`LogEntry`:Finished copying 292171 rows in 8m58s
17:08:35 [INF][copytable]: Re-enabling triggers for schema 'AssureIDConnect'
17:08:35 [DB1][copytable]: Retrieving trigger definitions
17:08:35 [INF][copytable]: No triggers found for 'AssureIDConnect'


FINISHED