Install and Migrate MariaDB or MySQL for Windows

Introduction

The main purpose of this document is to describe how to install a new MariaDB instance and migrate pre-existing databases as necessary for the Windows operating system. All existing customers who have either a pre-existing Ephesoft Transact installed version of MariaDB or MySQL will need to install a new MariaDB instance and migrate their existing database to the newly installed MariaDB instance. Use this document prior to installing Ephesoft Transact for all MariaDB Windows customers.

Ephesoft Transact 2019.2 and future releases will no longer include MariaDB as part of the bundled installation. Support for MySQL database will also be removed from the above releases. This will impact both existing and new customers.

Ephesoft Transact will now support MariaDB versions 10.2.6 to 10.3.11. This includes only versions with a release status of stable and does not include alpha, beta, or release candidate (RC) versions.

Specific testing for Ephesoft Transact 2019.2 was conducted with MariaDB 10.3.11. Ephesoft recommends 10.3.11.

This procedure outlines instructions for the following installations:

  • Fresh installation of Ephesoft Transact with MariaDB
  • Upgrade installation of Ephesoft Transact with MariaDB
  • Upgrade installation of Ephesoft Transact with MySQL

Prerequisites

The prerequisites for each installation are described below.

Instructions for Installing and Migrating MariaDB

Fresh Installation of Ephesoft Transact

If you are installing Ephesoft Transact for Windows for the first time, install a supported version of MariaDB between 10.2.6 to 10.3.11 (recommended). Refer to MariaDB Installation. New customers are required to install a supported version of MariaDB as an Ephesoft Transact installation pre-requisite.

A supported version of MariaDB may be installed on a remote server or on the same server where Ephesoft Transact will be installed.

Once you have installed MariaDB, start the installation of Ephesoft Transact and select MariaDB as a database option.

Upgrade Installation of Ephesoft Transact

Based on a user’s previous installation of MariaDB or MySQL, the upgrade installation of Ephesoft Transact may fall under any of the following:

MariaDB Installed with a Previous Version of Ephesoft Transact

Follow the steps below before upgrading to the latest version of Ephesoft Transact:

  1. Install a supported version of MariaDB (10.2.6 to 10.3.11) on a server. Users can install MariaDB on the same server where Ephesoft Transact will be installed or choose a dedicated database server (recommended). Refer to MariaDB Installation.
  2. Create a new database on the new MariaDB instance installed in step 1. Refer to Create a New Database on a New MariaDB Instance.
  3. Backup the Ephesoft Transact database from the pre-existing MariaDB instance. Refer to Create a Database Backup.
  4. Restore the pre-existing database backup from step 3 to the new MariaDB database. Refer to Restore and Migrate Existing Ephesoft Transact Data with Backup Files.
  5. Update the following files with the new database server hostname, database port number, root username, and password only if they changed in new installation of MariaDB:
    • %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

6. Start Ephesoft Transact and verify that the application is running.

Remote MySQL Installed Outside of Ephesoft Transact

Follow the steps below before upgrading to the latest version of Ephesoft Transact:

  1. Install a supported version of MariaDB (10.2.6 to 10.3.11) on a server. Users can install MariaDB on the same server where Ephesoft Transact will be installed or choose a dedicated database server. Refer to MariaDB Installation.
  2. Create a new database on the new MariaDB instance installed in step 1. Refer to Create a New Database on a New MariaDB Instance.
  3. Backup the Ephesoft Transact database from the existing MariaDB instance. Refer to Create a Database Backup.
  4. Restore the database backup from step 3 to the new MariaDB database. Refer to Restore and Migrate Existing Ephesoft Transact Data with Backup Files.
  5. Update the following files with the new database server hostname, database port number, root username, and password only if they changed in new installation of MariaDB:
    • %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

6. Start Ephesoft Transact and verify that the application is running.

Remote MariaDB Installed Outside of Ephesoft Transact

Ensure that the supported version of MariaDB (10.2.6 to 10.3.11) is installed before upgrading to the latest Ephesoft Transact version. Refer to Platform Configuration and Third-Party Integrations – Ephesoft Transact 2019.2.

If the installed MariaDB is within the supported versions as stated above, users may proceed to upgrade Ephesoft Transact.

Conclusion

This concludes the steps required to install or migrate MariaDB or MySQL when upgrading to the latest version of Ephesoft Transact. Refer to the following instructions to upgrade Ephesoft Transact using Windows OS:

Appendix

MariaDB Installation

      1. Go to https://downloads.mariadb.org/, and select View All MariaDB Releases in the MariaDB 10.3 series section.
      2. Select a supported version (e.g.,10.3.11) in the Name column.
        Note: Version 10.3.11 of MariaDB is recommended by Ephesoft.
      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 download is complete, open the Windows installer package.
      4. Click Next on the MariaDB Setup screen.

Figure 1: MariaDB Setup Screen

5. 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

6. 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.

Figure 3: Custom Setup

7. Enter a new root password 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) to enable the case insensitivity coalition type.
   Note: Ephesoft Transact requires that the case insensitivity coalition type is enabled.
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

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

Figure 5: Default instance properties – Install as service/Enable networking setup

9. Select the Enable the Feedback plugin and submit anonymous usage information option, if desired. This option aids developers to improve MariaDB by collecting basic anonymous statistical information. Click Next.

Figure 6: MariaDB setup – Enable the feedback plugin and submit anonymous usage information

10. Click Install to begin installation.

Figure 7: Ready to Install MariaDB

11. Wait for the MariaDB installation to finish.

Figure 8: Installing MariaDB

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

Figure 9: This Concludes the Installation Portion for a MariaDB Instance.

 

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. Replace the username, database name and password placeholders in the above set of queries before executing them. These queries can be executed using any MariaDB client tool.
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>';

DROP USER '<UserName>' @ 'localhost';

GRANT USAGE ON . TO '<UserName>' @ '%' IDENTIFIED BY '<Password>';

DROP USER '<UserName>' @ '%';

CREATE USER '<UserName>' @ 'localhost' IDENTIFIED BY '<Password>';

CREATE USER '<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. Once the three required databases are created, update these details in all three resource tags in the Ephesoft\JavaAppServer\conf\server.xml file. Refer to the screenshot below for entries to be updated.

Figure 10: Update Resource Tags

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. In the query below, 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 query 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. Below are the sample queries for all the databases:
        • "D:\Ephesoft\Dependencies\mariadb\bin\mysqldump.exe" -uroot -pPassw0rd -hlocalhost -P3308 ephesoft --single-transaction --routines --quick --lock-tables=false > C:\dbdump\ephesoft_backup.sql
        • "D:\Ephesoft\Dependencies\mariadb\bin\mysqldump.exe" -uroot -pPassw0rd -hlocalhost -P3308 report --single-transaction --routines --quick --lock-tables=false > C:\\dbdump\report_backup.sql
        • "D:\Ephesoft\Dependencies\mariadb\bin\mysqldump.exe" -uroot -pPassw0rd -hlocalhost -P3308 report_archive --single-transaction --routines --quick --lock-tables=false > C:\\dbdump\report_archive_backup.sql

Restore and Migrate Existing Ephesoft Transact Data with Backup Files

This process is 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 step 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 installed is a supported version (10.2.6 to 10.3.11). Upgrade the database if it falls below the minimum supported version.

      1. Use the SQL command 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 queries:
        • mysql -uroot -pPassw0rd -P3306 ephesoft < C:\dbdump\epesoft_backup.sql
        • mysql -uroot -pPassw0rd -P3306 report < C:\dbdump\report_backup.sql
        • mysql -uroot -pPassw0rd -P3306 report_archive < C:\dbdump\report_archive_backup.sql