Overview

This article describes the process of installing MySQL Community Server such that MySQL runs 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. This article describes the process of install MySQL Community Server.

Procedure

If you already have CentreStack installed with the "all-in-one" MySQL deployment, please refer to this article first: Migrating from MySQL All-in-One to Local MySQL Running as a Service

There are two methods for installing MySQL. The first uses a PowerShell script to install in an automated fashion. This is the recommended method as it is much less error prone. The second method is a manual installation. Use the manual installation if your aren't comfortable running PowerShell scripts. The Install-MySQL.ps1 script requires PowerShell 4 (Windows Server 2012 R2) or later. 

Automated Installation

1. Log in interactively to the CentreStack server and in a web browser navigate to this link: Install-MySQL
2. Click on the Download current folder icon:
   
   The Install-MySQL.zip will be downloaded (typically to your Downloads directory in your user profile).
3. Extract the Install-MySQL.zip to an empty directory. You will see two files in File Explorer:
   
4. Start an elevated PowerShell session (as Administrator).
5. Make sure scripts are allowed to run. Execute: 

   Set-ExecutionPolicy RemoteSigned

6. Change to the directory where the scripts were downloaded for example:

   cd $env:UserProfile\Downloads\Install-MySQL

7. Unblock the files in the directory:

   Get-ChildItem | Unblock-File

8. Execute the Install-MySQL.ps1 script:

   .\Install-MySQL.ps1

9. You may see this prompt which is expected if you haven't already trusted Gladinet's code signing certificate:

   Do you want to run software from this untrusted publisher?
   File C:\Tools\Install-Debugger.ps1 is published by CN="Gladinet, Inc.", O="Gladinet, Inc.", L=Fort
   Lauderdale, S=Florida, C=US and is not trusted on your system. Only run scripts from trusted publishers.
   [V] Never run  [D] Do not run  [R] Run once  [A] Always run  [?] Help (default is "D"):n

10. Enter: A
    This will trust any scripts signed with the Gladinet code signing certificate. 
11. The script will create a Logs directory in the directory that the scripts are located. Review the Install-MySQL_<timestamp>.log for errors.

Script Actions

The script is designed for the principle of least privilege in mind. In addition to the MySQL root user a csuser account is created that only has access to the csmain database. The intention is that CentreStack will access the csmain database using csuser not root.

The script creates two files in the interactive user's Documents directory:

mysql-init.txt
mysql-create.txt

mysql-ini.txt contains text similar to this:

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

Where the text in red is the root account's password (less the single quotes).

mysql-create.txt contains text similar to this:

 CREATE DATABASE IF NOT EXISTS csmain;
 CREATE USER 'csuser'@'%' IDENTIFIED BY 'password';
 GRANT ALL ON csmain.* TO 'csuser'@'%';

The first statement created the csmain database. 

The second statement created the csuser, allowing remote access from other computers. The text in red is the csuser account's password (less the single quotes).

The third statement grants all access to the csmain database to the csuser.  

Manual Installation

1. In a web browser navigate to: https://dev.mysql.com/downloads/windows/installer/5.7.html
2. Download the smaller Windows MSI Installer package:
   
3. In the next page click the No thanks, just start my download:
   
4. Execute the mysql-installer-web-community-5.7.x.y.msi installer package (where x.y are minor version numbers like "24.0", at the time of this writing)
5. Accept the license terms:
   
6. Select Custom:
   
7. CentreStack does not currently support MySQL Server 8.0 so click the Edit button:
   
8. Use these settings:
   1. Text: MySQL Server 5.7
   2. Category: MySQL Servers
   3. Age: Other Releases
   4. Architecture: 64-bit 
      
      Then click the Filter button
9. Expand MySQL Servers node enough times until you are able to select the most recent version (5.7.24 at the time of this writing):
   
10. Click the green arrow that points to the right to add that Product to the pane on the right:
    
11. You can expand the server product to see what features will be installed by default. The defaults are acceptable so click the Next button:
    
12. Click the Execute button to download the server product:
    
13. Click the Next button:
    
14. Click the Next button:
    
15. Leave Standalone MySQL / Classic MySQL Replication enabled and click Next:
    
16. Change the Config Type drop-down to Server Computer and leave TCP/IP enabled and the Port set to 3306 and Open Windows Firewall port for network access to enabled, then click Next:
    
17. Set the root account password and store the password in a safe place, then click Next:
18. Leave the Windows service default settings and click Next:
    
19. There is no reason to change the default setting such that the Document Store is disabled:
    
20. Click Execute:
21. Click Finish:
    
22. Click Next:
    
23. Click Finish:
    

Installing Workbench

1. From the Windows start menu launch MySQL Installer - Community
2. In MySQL Server is already installed click the Add... button:
   
3. Set:
   1. Text: MySQL Workbench
   2. Category: Applications
   3. Age: Current GA
      
      
4. Expand the Applications node as many times as necessary to show the product, then click the green right arrow:
   
5. Defaults are acceptable so click Next:
   
6. Click Execute to download the Workbench:
   
7. Click Next:
   
8. Click Finish:
   
9. Close the MySQL Installer window:
   

Configuring a Database and User

In this section a database named csmain will be created for hosting the CentreStack database. A user named csuser will be created and granted access only to the csmain database. 

1. Start MySQL Workbench.
2. Click on the localhost config:
   
3. Use the password for the root account that you saved earlier and enable the Save password in vault option then click OK:
   
4. In the Query1 window paste this text:

   CREATE DATABASE IF NOT EXISTS csmain;
   CREATE USER 'csuser'@'%' IDENTIFIED BY 'password';
   GRANT ALL ON csmain.* TO 'csuser'@'%';

5. Replace the password (the string in red in the previous step) with something more secure and store it in a safe place like a password vault.
6. Click the lightning icon to execute all statements (or Ctrl+Shift+Enter):
   
7. Click the refresh schema icon:
   
8. Notice the csmain database is created but it is empty (there are no tables yet):
   
9. Click the Users and Privileges node and notice the new csuser is created:
   
10. Clicking the Schema Privileges tab for the csuser shows that csuser has all access to the csmain database:
    
11. Close the localhost connection.
12. Create a new MySQL Connection:
    
13. In the Connect Name, give it a name that indicates it's csuser connecting to localhost, then in the Username field type csuser, then click the Store in Vault button:
    
14. Use the password you saved for csuser:
    
15. Click Test Connection:
    
16. The test should succeed then click OK:
    
17. Click the Close button in the connection manager.
18. Open the csuser connection and navigate to the csmain database.

Configuring CentreStack

Whether the automated install or the manual install of MySQL was used the CentreStack setup is the same

1. In the http://localhost/management/AdminDatabase.aspx page, select MySQL:
2. The
   
   1. Host Name: localhost
   2. Port: 3306
   3. Database Name: csmain
   4. User Name: csuser
   5. Password: Either the csuser's password from mysql-create.txt (in the automated install) or the csuser's password that was saved during the manual install.