MySQL

Overview

This section describes how to install a new version of the software on a fresh system by using SQL replication.

Here is an overview of installation procedure:

Install first instance of TSS/Console

Proceed as follows:

Extract the archive "TSS-Console-10.1.Y.Z.cab"

Start the application UIM.exe. (Right Click and "Run as Administrator")

Select option "Install new version of TSS/Console" and press "Next" button.

Configuration options:

•Type: Type of  new installation in redundancy.

oNew database: TSS/Console is the first instance (redundancy or replication).

oExisting database: Another instance of TSS/Console has been installed previously on another server. You can press 'Search' button to find all existing database installed on mentioned server.

•DBMS (Database Management System): mySQL

•Server: Server where database is stored. If you have chosen option 'Existing database' select server mentioned during first instance TSS/Console installation.

•Login: Enter "root" user. You can also use a another user that you specifically created in MySQL, to connect to BT Trade database.

•Password:  Password to connect to DBMS (same used during MySQL installation).

Press "Next" button.

•Site: Select "IPTWebSite"

•Application Pool: Select "BTTrade" (Created during the "Configuring IIS" section)

•SQL Login: Login TSS application will use to connect to mySQL.

•SQL Password: Password TSS application will use to connect to mySQL.

•FTP Path: Path to the virtual "conf" directory "D:\FTPRoot"

•Component Manager Path: "D:\IPTWebSite\IptradeNet"

•No of TSS/Console: Redundancy instance. Depending of previous selected option it has a different meaning:

oNew database: Determine number of instance used in redundancy. Choose 2 if you do not want to use redundancy (one for primary and one for secondary) or more if you want to use redundancy. Instance number is always 1 for first installed TSS/Console.

oExisting database: Determine the instance number to install (it is not possible to select used instance).

Press "Install" button.

Enter Syslog parameters:

•Enable: If set to"True" enables Syslog log (This sets automatic start on the installed Windows Service)

•Path: "F:\Syslog"

•Cleaning period: Delete Syslog files that are older than this value (in days)

•File truncation: Maximum file size (in MB) of each Syslog log

•Cleaning size: Maximum total file size (in MB) of all Syslog log files

•Port: Port of Syslog service

Next are the status steps. If database already exists a confirmation message is displayed to ask you the confirmation to overwrite the database.

At the end of the process the following screen is displayed:

The TSS application is now installed in the displayed location and ready to be used. Note the displayed URL for future use.

For more information about IptradeNet application see corresponding section: IptradeNet/TSS/Console: Quick Start Guide.

Duplicate database

Do following step before installing next instance(s) of TSS/Console.

•Backup database created during first install on Primary TSS (Publisher)

•Restore this database where Secondary TSS (Subscriber) will be installed.

•During the next install of replicated TSS/Console select restored database.

Replication Configuration

Edit the configuration file

Apply the following configuration on Primary and Secondary server.

Steps are identical for each server except for "server-id" which must be different (for example : Primary server-id = 1 and Secondary server-id = 2)

•Open MySQL Workbench

•Open MySQL Connection to connect to.

•In Navigator panel, select Administration pane

•Select INSTANCE > Options File option then

 - Select General tab > set server-id = <Server_ID>

 - Select Replication tab > set replicate-do-db = <Database_name> and slave-net-timeout = 30

 - Select Logging tab > set log-bin = mysql-bin and binlog-do-db = <Database_name>

•Press Apply... button to display the Apply Changes to MySQL Configuration File panel

•Press Apply button to confirm changes

•Close MySQL Workbench

•Open Windows Services (Right Click Start > Run: services.msc), then restart the "MySQL8030" service

This configuration will allow the replication to replicate only the database specified with <Database_name>.

Without this configuration, the replication will be done for the entire database and may lead to issues

Create a replication user

If a replication user is not already created, make sure to set it up by following the steps below for both Primary and Secondary servers

•Open MySQL Workbench

•Open MySQL Connection to connect to.

•Select MANAGEMENT > Users and Privileges option  (in navigator pane) > Press Add Account button

 - Login Name: repl

 - Authentication Type: Standard

 - Limit Connectivity to Hosts Matching: %

 - Password: <Replication_password>

 - Confirm password: <Replication_password>

•Select Administrative Roles tab > select ReplicationAdmin option

•Press Apply... button to save changes

Initialize replication

Primary Server replication initialization

1.Execute following queries on Secondary server in order to get required information to set up replication on Primary server :

SHOW MASTER STATUS;

•Note the result of "SHOW MASTER STATUS" query as shown below :

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      107 |              |                  |

+------------------+----------+--------------+------------------+

<secondary_replication_file> = mysql-bin.000001 

<secondary_replication_position> = 107

SHOW VARIABLES WHERE Variable_name = 'port';

•Note the result of this query as shown below

+-----------------+--------+

| Variable_name   | Value  |

+-----------------+--------+

| port            | 3306   |

+-----------------+--------+

<secondary_port> = 3306

2.Execute following queries on Primary server in order to start the replication:

STOP REPLICA;

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<secondary_server_IP or FQDN>',

 SOURCE_PORT = <secondary_port>,

 SOURCE_USER='<secondary_replication_user>',

 SOURCE_PASSWORD = '<secondary_replication_password>',

 SOURCE_LOG_FILE = '<secondary_file>',

 SOURCE_LOG_POS = <secondary_position>,

 GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

For example :

STOP REPLICA;

CHANGE REPLICATION SOURCE TO SOURCE_HOST='tss-02.trading.com',

SOURCE_PORT = 3306,

SOURCE_USER='repl',

SOURCE_PASSWORD = 'mypassword',

SOURCE_LOG_FILE = 'mysql-bin.000002',

SOURCE_LOG_POS = 107,

GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

Secondary Server replication initialization

Steps are identical to the Primary server but adapted for Secondary server

1.Execute following query on Primary server:

SHOW MASTER STATUS;

•Note the result of "SHOW MASTER STATUS" query as shown below :

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      107 |              |                  |

+------------------+----------+--------------+------------------+

<primary_replication_file> = mysql-bin.000001 

<primary_replication_position> = 107

•Note the result of this query as shown below

+-----------------+--------+

| Variable_name   | Value  |

+-----------------+--------+

| port            | 3306   |

+-----------------+--------+

<primary_port> = 3306

2.Execute following queries on Secondary server:

STOP REPLICA;

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<primary_server_IP or FQDN>',

 SOURCE_PORT = <secondary_port>,

 SOURCE_USER='<primary_replication_user>',

 SOURCE_PASSWORD = '<primary_replication_password>',

 SOURCE_LOG_FILE = '<primary_file>',

 SOURCE_LOG_POS = <primary_position>,

 GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

For example :

STOP REPLICA;

CHANGE REPLICATION SOURCE TO SOURCE_HOST='tss-01.trading.com',

SOURCE_PORT = 3306,

SOURCE_USER='repl',

SOURCE_PASSWORD = 'mypassword',

SOURCE_LOG_FILE = 'mysql-bin.000002',

SOURCE_LOG_POS = 157,

GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

Verify replication state

To check that the replication is well enabled on both server, run the following query on each one

SHOW REPLICA STATUS;

Check that following columns resulting from this query are returning the expected result on both servers:

•Slave_IO_State : Waiting for master to send event

•Slave_IO_Running : YES

•Slave_SQL_Running : YES

Install other instance of TSS/Console

See section Install first instance of TSS/Console to install other instances TSS/Console.

Choose option "Type = Existing database" during installation.

This section can be executed several time depending of number or instance of TSS/Console using replication/redundancy.

Update Component Manager URLs for Secure Access

As part of the TSS Server Install Operating System > Configuring IIS guide, you were instructed To Configure TLS on IIS

All TSS Servers should be secured with certificates.

By default the UIM installer will create Console and TSS links with HTTP and the IP address of the server.

To update these links to use HTTPS and FQDN follow this guide: Update Component Manager URLs