Install and Migrate MariaDB or MySQL for Windows

Introduction

This document provides instructions for how to install a new MariaDB database, and how to migrate an existing MySQL or MariaDB database to a MariaDB database located outside of the Ephesoft Transact folder structure.

This document was created to address the following important MariaDB-related changes that were made. These changes are effective starting with Ephesoft Transact 2019.2:

If you upgrade an older Ephesoft Transact instance that has a MariaDB or MySQL database installed inside the Ephesoft Transact folder structure, the Ephesoft Transact upgrade process will delete your database.

If you want to use MariaDB with a new installation of Ephesoft Transact, follow the steps in this document to install MariaDB before installing Ephesoft Transact.

If you are upgrading an older version of Ephesoft Transact that uses a MariaDB database or MySQL database installed inside the Ephesoft installation folder area, use this document to install MariaDB and migrate your old database content into the new MariaDB database before upgrading Ephesoft Transact.

If you are upgrading an older version of Ephesoft Transact that uses a MySQL database (regardless of where the database is installed), use this document to install MariaDB and migrate your MySQL database content to the new MariaDB database before upgrading Ephesoft Transact.

Prerequisites

The prerequisites for each installation are described below.

Instructions for Installing and Migrating MariaDB

Below are the different Ephesoft Transact upgrade and installation scenarios. Choose your applicable scenario and follow the general instructions listed. Further detailed instructions for these categories are linked to the Appendix below.

Scenario 1 – Fresh Installation of Ephesoft Transact

Before starting a fresh installation of Ephesoft Transact, you must install a supported version of MariaDB. Refer to MariaDB Installation.
MariaDB may be installed on a remote server or on the same server where Ephesoft Transact will be installed.
After MariaDB is installed, start the installation of Ephesoft Transact and select MariaDB as the database option.

Scenario 2 – Upgrade Installation of Ephesoft Transact

Based on your previous installation of MariaDB or MySQL, the upgrade installation of Ephesoft Transact may fall under one of the following scenarios. Before upgrading Ephesoft Transact, find your applicable scenario and complete the instructions listed:

MariaDB Installed as a Bundled Installation from a Previous Version of Ephesoft Transact

If you are using MariaDB from a bundled installation from a previous version of Ephesoft Transact, follow the steps below before upgrading Ephesoft Transact:

  1. Back up the Ephesoft Transact database from the pre-existing MariaDB instance. Refer to Create a Database Backup.
    Important: You must back up your databases in the above step before proceeding.
  2. Stop and disable the service for the old MariaDB database to prevent it from running.
    Important: The step above is imperative.
  3. Install a supported version of MariaDB. You can install MariaDB on the same server where Ephesoft Transact will be installed or choose a dedicated database server. Refer to Install MariaDB.
    • For production environments: Ephesoft recommends installing Ephesoft Transact and the database on separate servers.
    • For development or test environments: Although Ephesoft Transact and the database can be installed on the same server, doing so may cause Ephesoft Transact performance to suffer.
      Important: MariaDB must be installed outside of the Ephesoft Transact installation folders.
  1. Create a new database on the new MariaDB instance installed in step 3. Refer to Create a New Database on a New MariaDB Instance.
  2. Restore the pre-existing database backup from step 1 to the new MariaDB database. Refer to Restore and Migrate Existing Ephesoft Transact Data with Backup Files.
  3. Complete this step only if your password has changed in the new installation of MariaDB. Update the following files with the new database server hostname, database port number, root username, and password:
    • <Transact Installation Folder>\Javaappserver\conf\server.xml
    • <Transact Installation Folder>\Dependencies\mariadb\mariadbSetup\local-ephesoft-mariadb-setup.bat
    • <Transact Installation Folder>\Dependencies\mariadb\mariadbSetup\ephesoft-mariadb-setup.bat
    • <Transact Installation Folder>\upgrade-ephesoft-mariadb-setup.bat
  1. Start Ephesoft Transact and verify that the application is running.

MySQL Instance Installed Outside of Ephesoft Transact

If you are using MySQL with your Ephesoft Transact version, follow the steps below before upgrading Ephesoft Transact:

  1. Back up the Ephesoft Transact databases from the existing MariaDB instance. Refer to Create a Database Backup.
    Important: You must back up your databases in the above step before proceeding.
  2. Stop and disable the service for the MySQL database to prevent it from running.
    Important: The step above is imperative.
  3. Install a supported version of MariaDB. You can install MariaDB on the same server where Ephesoft Transact will be installed or choose a dedicated database server. Refer to Install MariaDB.
  4. Create a new database on the new MariaDB instance installed in step 3. Refer to Create a New Database on a New MariaDB Instance.
  5. Restore the database backup from step 1 to the new MariaDB instance. Refer to Restore and Migrate Existing Ephesoft Transact Data with Backup Files.
  6. Complete this step only if your password has changed in the new installation of MariaDB. Update the following files with the new database server hostname, database port number, root username, and password:
    • <Transact Installation Folder>\Javaappserver\conf\server.xml
    • <Transact Installation Folder>\Dependencies\mariadb\mariadbSetup\local-ephesoft-mariadb-setup.bat
    • <Transact Installation Folder>\Dependencies\mariadb\mariadbSetup\ephesoft-mariadb-setup.bat
    • <Transact Installation Folder>\upgrade-ephesoft-mariadb-setup.bat
  1. Start Ephesoft Transact and perform some tests to verify that the application runs properly.

MariaDB Instance Installed Outside of Ephesoft Transact

Ensure that the existing instance of MariaDB (that was installed outside of Ephesoft Transact) is a supported version as required to upgrade Ephesoft Transact. Refer to Platform Configuration and Third-Party Integrations – Ephesoft Transact 2019.2.

If the installed MariaDB database is a supported version of MariaDB, continue with the Ephesoft Transact upgrade process. Otherwise, follow the upgrade procedures in this document to upgrade to a supported version of MariaDB.

Conclusion

Upon successful completion of this document, you have installed or migrated MariaDB or migrated MySQL to MariaDB. You are ready to install or upgrade Ephesoft Transact. For additional information on how to install or upgrade Ephesoft Transact on Windows, refer to the following resources:

Appendix

Create a Database Backup

This section outlines how to configure and create backups for previously installed MariaDB or MySQL databases from previous versions of Ephesoft Transact. This process is required to restore and migrate the necessary data for Ephesoft Transact to function in the newly installed MariaDB instance and the newly created required databases.

Below is the SQL command and steps to use for the MariaDB database backup:

  1. Run the command below using Command Prompt, replace the placeholders for the root username, password, server hostname, and port number along with the database name and full folder path where the backup file will be created:
mysqldump.exe -u<root username> -p<root user password> -h<server hostname> -P<database port number> <Database name> --single-transaction --routines --quick --lock-tables=false > <Folder location>\Database_backup.sql
  1. Execute the command in step 1 from the MariaDB installation bin folder that is available in the following location: <Transact Installation Folder>\Dependencies\mariadb\bin\
  2. Complete the steps above for all three Ephesoft Transact databases (ephesoft, report, report_archive). Ensure to write each database dump to its own backup file. Remember to create backups for additional databases that may have been created on your original database (such as a separate database to hold FuzzyDB lookup tables, or a database to hold exported batch instance data). Below are the sample commands for all the databases:
    • “D:\Ephesoft\Dependencies\mariadb\bin\mysqldump.exe” -uroot -pPassw0rd -hlocalhost -P3306 ephesoft –single-transaction –routines –quick –lock-tables=false > C:\dbdump\ephesoft_backup.sql
    • “D:\Ephesoft\Dependencies\mariadb\bin\mysqldump.exe” -uroot -pPassw0rd -hlocalhost -P3306 report –single-transaction –routines –quick –lock-tables=false > C:\dbdump\report_backup.sql
    • “D:\Ephesoft\Dependencies\mariadb\bin\mysqldump.exe” -uroot -pPassw0rd -hlocalhost -P3306 report_archive –single-transaction –routines –quick –lock-tables=false > C:\dbdump\report_archive_backup.sql

Install MariaDB

  1. Go to https://downloads.mariadb.org/, and select View All MariaDB Releases in the MariaDB 10.3 series section.
  2. Select MariaDB version 10.3.11 (as required by Ephesoft) in the Name column.
  3. Select the appropriate .msi link (e.g., mariadb-10.3.11-winx64.msi) in the File Names column to begin the Windows installer download. When the download is complete, open the Windows installer package.
  4. Click Next on the MariaDB Setup screen.

Figure 1: MariaDB Setup Screen

  1. Read the end-user license agreement. Select the I accept the terms in the License Agreement option and click Next.

Figure 2: End-User License Agreement

  1. Choose your desired features to install and ensure that a database instance of the MariaDB Server is selected. All features, except the debug symbols, are installed by default. Change the installation location if you wish, but MariaDB must not be installed inside the Ephesoft Transact folder structure.

Figure 3: Custom Setup

  1. Enter your root password (from the original MariaDB installation) in the New root password field. Re-enter the password in the Confirm field and select the Use UTF8 as default server’s character set option (required).As an optional step, select the Enable access from remote machines for ‘root’ option. Click Next.

Figure 4: Default instance properties New root password Setup

    1. Select the Install as a service and Enable networking options. Update the Service Name and TCP port fields. Click Next.

Figure 5: Default instance propertiesInstall as service and Enable networking Setup

    1. Select the Enable the Feedback plugin and submit anonymous usage information option, if desired. Click Next.

Figure 6: MariaDB Setup — Enable the feedback plugin and submit anonymous usage information

    1. Click Install to begin installation.

Figure 7: Ready to Install MariaDB

    1. Wait for the MariaDB installation to finish.

Figure 8: Installing MariaDB

    1. Click Finish to complete installation and to exit the Setup Wizard.

Figure 9: Completed MariaDB 10.3 (x64) Installation

Create a New Database on a New MariaDB Instance

Below are the set of SQL commands that are needed to create the three required databases used in Ephesoft Transact. This process is required after installing a new MariaDB instance.

  1. Ensure that your old MariaDB or MySQL instance is stopped and disabled, and your new MariaDB instance is running.
  2. Connect to your new MariaDB instance with a SQL editor tool (HeidiSQL, Toad, MySQL command line, etc.).
  3. Replace the username, database name, and password placeholders in the set of commands below before executing them.
    Note: The placeholders entered must be replaced with credentials from the second database user that was created during the original Ephesoft Transact installation. Create additional statements to account for additional databases that may have been created on your original database (such as a separate database to hold FuzzyDB lookup tables).
CREATE DATABASE IF NOT EXISTS `<Ephesoft_Database>`;
CREATE DATABASE IF NOT EXISTS `<Report_Database>`;
CREATE DATABASE IF NOT EXISTS `<Report_Archive_Database>`;
GRANT USAGE ON *.* TO '<UserName>'@'localhost' IDENTIFIED BY '<Password>';
GRANT USAGE ON *.* TO '<UserName>'@'%' IDENTIFIED BY '<Password>';
CREATE USER IF NOT EXISTS '<UserName>'@'localhost' IDENTIFIED BY '<Password>';
CREATE USER IF NOT EXISTS'<UserName>'@'%' IDENTIFIED BY '<Password>';
GRANT ALL PRIVILEGES ON <Ephesoft_Database>.* TO '<UserName>'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON <Ephesoft_Database>.* TO '<UserName>'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON <Report_Database>.* TO '<UserName>'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON <Report_Database>.* TO '<UserName>'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON <Report_Archive_Database>.* TO '<UserName>'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON <Report_Archive_Database>.* TO '<UserName>'@'%' WITH GRANT OPTION;
  1. Complete this step only if your password has changed. Once the three required databases are created, update these details in the following resource tags in the <Transact Installation Folder> Ephesoft\JavaAppServer\conf\server.xml file. Refer to the figure below for entries to be updated.

Figure 10: Update Resource Tags

Restore and Migrate Existing Ephesoft Transact Data with Backup Files

These instructions are used to restore previous MariaDB or MySQL data using the backups created above and to migrate the Ephesoft Transact data to the newly created databases. This is a required step for customers who are migrating pre-existing MariaDB or MySQL databases from a previous version of Ephesoft Transact. Skip this section if you are an existing customer with a current MariaDB instance installed on a remote server or outside of the Ephesoft Transact directory.

Verify that the MariaDB instance installed is a supported version. Refer to Platform Configuration and Third-Party Integrations – Ephesoft Transact 2019.2 for version details. Upgrade the database if it falls below the minimum supported version.

Important: Ensure that the original MariaDB or MySQL database is stopped and disabled, and the new MariaDB database is running.

  1. Execute the following command in a Command Prompt below to restore the database backup files:
mysql.exe -u<root username> -p<root user password> -P<database server port> <Database name> < <Folder location>\database_backup.sql
  1. Replace the placeholders for the root username, password, server hostname, and port number along with the database name and full folder path where the backup file will be created. Below are the sample commands:
    • mysql -uroot -pPassw0rd -P3306 ephesoft < C:\dbdump\ephesoft_backup.sql
    • mysql -uroot -pPassw0rd -P3306 report < C:\dbdump\report_backup.sql
    • mysql -uroot -pPassw0rd -P3306 report_archive < C:\dbdump\report_archive_backup.sql