Overview

This article describes the process of migrating from the MySQL "all-in-one" deployment to another locally deployed MySQL server running as a Windows service. 

As of CentreStack version 9.5.5409.41172, MySQL Community Edition is the Default Database for the "all-in-one" deployment.

CentreStack's default "all-in-one" database is MySQL Server 5.7 Community Edition but CentreStack installs a bare bones deployment that lacks any of the MySQL management tools. The bare bones deployment also spawns the mysqld.exe process when the Gladinet Cloud Monitoring Service starts, but the mysqld.exe process does not shutdown when the Gladinet Cloud Monitoring Service stops. The best practice for production deployments of CentreStack is to migrate the default all-in-one database from the CentreStack server to an external database server running either MySQL or Microsoft SQL Server. There are other articles that document the migration process when using an external database server (or service), for example:

• Migrate All-In-One Database to Azure SQL
• Using AWS Aurora MySQL with CentreStack

If it is not possible to use an external database server or service, then the next best recommendation is to use the MySQL Installer to install MySQL Server 5.7 Community Edition as a Windows service, along with the MySQL Workbench and Connector/NET components. Running MySQL as a service and having the additional components installed makes the system more manageable. 

Procedure

Changing the all-in-one TCP port

The MySQL "all-in-one" database is installed such that it is listening on TCP port 3306. It is necessary to change the port prior to installing MySQL Server using the MySQL Installer. Prior to making any changes to the system, make a full backup or at least a VM snapshot or checkpoint (if possible).

1. Start an elevated command prompt (as administrator)
2. Execute:

   iisreset /stop

3. Execute:

   net stop GCMon

4. Start Task Manager and click on the Details pane. 
5. Wait for the GladinetCloudMonitor.exe process to exit. 
6. Right click on the header row and Click Select Columns:
   
7. Add the Command line column:
   
8. Sort the list by the Name column and select the mysqld.exe process. You should see the command line contains references to "c:\\CSDBROOT" directory which indicates the process for the CentreStack MySQL "all-in-one" database. Click the End task button to kill the process:
   
9. Start a text editor like Visual Studio Code elevated (running as Administrator)
10. Open "C:\CSDBRoot\conf\my.conf"
11. Locate this line:

    port=3306

12. Change the line to be:

    port=3307

13. Save the file
14. Reboot
15. After rebooting, open "C:\CSDBRoot\conf\initfile" in a text editor. The file will contain a single line similar to this:

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'lKnoZwwi'

16. The text in red between the single quotes is the password of the root user account. Copy the text (less the single quotes) to the clipboard.
17. Start a web browser and navigate to http://localhost/management/AdminDatabase.aspx (It may take a few minutes for the page to load)
18. Click on the drop down control that reads Default - all in one and and change it to
    My SQL:
    
19. Fill in the fields as seen in this screen with the password pasted from the clipboard (the string in "C:\CSDBRoot\conf\initfile" that follows IDENTIFIED BY, less the single quotes):
    
20. This action updates the registry such that the connection string now contains port 3307. The login page should now load since the database is available: http://localhost/portal/loginpage.aspx

Install MySQL Server

Now that the all-in-one bare bones deployment is listening on TCP port 3307, it's very easy to install MySQL using the MySQL Installer. Refer to this article: Installing MySQL Community Server

Migrate from all-in-one to localhost MySQL as a service

1. Verify that MySQL Workbench can used to connect to the MySQL instance listening on TCP 3306 as root.
2. Verify that the csuser user exists, and the csmain database exists:
   
3. Close the MySQL connection as root and make a new connection as csuser:
   
4. Test the connection and click OK to verify csuser is able to connect to MySQL on TCP port 3306.
5. Now that MySQL connectivity has been verified, in a web browser navigate to: http://localhost/management/MigrateDatabase.aspx
6. Fill the fields in the form as seen below. The password for csuser is in the "%USERPROFILE%\Documents\mysql-create.txt" if the Install-MySQL.ps1 script was used:
   Click MIGRATE once the fields are filled in.
7. The Install-MySQL-ps1 script creates an empty csmain database, click CONTINUE as this is expected behavior:
8. The data in the tables will be migrated from the old MySQL all-in-one to the new MySQL running as a service. Once completed you will see:
   
9. Navigate to http://localhost/portal/loginpage.aspx and sign in.
10. In MySQL Workbench connected to localhost over TCP 3306 run this query:

    SELECT * FROM csmain.xaf_audit ORDER BY TimeStamp DESC LIMIT 10;

11. Verify that the latest logon event is at the top of the list. This verifies that the new database is live. Verify that old events are also listed. This verifies that data was migrated.

Disable the all-in-one mysqld.exe process

Use this PowerShell script to disable the "all-in-one" mysqld.exe process that is spawned by the Gladinet Cloud Monitor service. The script must be run in an elevated PowerShell session ("as Administrator")

# Stop the Gladinet Cloud Monitor service
Stop-Service -Name 'GCMon' -Verbose

# Get the CentreStack install dir
$installDir = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\WOW6432Node\Gladinet\Enterprise').InstallDir

# Find the mysqld process running from installDir
Get-Process | Where-Object {($_.Name -eq 'mysqld') -and ($_.Path -like "$installDir*")} | Stop-Process -Force -Verbose

# Remove the DefaultConn registry value
$regKeys = @('HKLM:\SOFTWARE\Gladinet\Enterprise', 'HKLM:\SOFTWARE\WOW6432Node\Gladinet\Enterprise')
$regValue = 'DefaultConn'
foreach ($r in $regKeys) {
    if ( $null -ne ((Get-ItemProperty -Path $r).$regValue) ) {
        Remove-ItemProperty -Path $r -Name $regValue -Verbose -Force
    }
}

# Start the Gladinet Cloud Monitor service
Start-Service -Name 'GCMon' -Verbose

# Rename the Gladinet Cloud Monitor service
Rename-Item -LiteralPath 'C:\CSDBRoot' -NewName "CSDBRoot.old"