Replacing a 32 bit embedded MySQL with a 64 bit version (4228393)

Prerequisites

1. MySQL 5.1.xMSI installer (64 and 32 bit binaries included) from mysql.com.

2. During the installation:

   a.     Choose server only, default paths.
   b.    In MySQL Server configuration Window, choose Server Machine. Leave TCP IP port default 3306
   c.    When asked, MySQL Root password: foglight
   d.    Choose to start as a service when Windows starts

Note: During the installations both servers can coexist as they are using different ports. New 64x is 3306 and 32 bit should be 13306.

The following is the procedure to move an embedded to an External database. The difference is you will use hostname: localhost and change the default DB port.

Step 1:  64 bit 5.1.x MySQL installation/config

Note: procedure tested with MySQL Community release 5.1.73-Win64. FMS 5.6.11 supports newer MySQL releases from the 5.5.x branch. download that version as well. 5.1.73-Win64 Community version Download MySQL   (will need to register for an Oracle account)

1. Once the Wizard start, select “Custom install.”

2. After the installation, select "Configure the MySQL Server now”.

3. Choose Detailed Configuration (selected by default)

4. Select MySQL instance configuration: Dedicated MySQL machine (Server machine if FMS is local), then next.

5. Database usage: Transactional Database Only, next.

6. InnoDB Tablespace Settings: choose your D or E drive (Different from drives used by OS or Foglight), next.

7. Set number of concurrent connections to the DB server: Manual Setting: 100 (note, to enable more than the default of 20 in Foglight, follow SOL93812 )

8. Enable TCP/IP Networking with default port (do not forget to change the port in FMS config file).

9. Enable Strict Mode, next.

10. Choose Manual Selected Default Char Set (latin1), next.

11. Choose to run as a Windows Service, check Lunch the MySQL Server automatically.

12. Check "Include Bin Directory in Windows PATH"

13. Select "foglight" as root password if FMS is using default db password, otherwise change according to your current DB root password.

14. Check "Enable root access from remote machine" if this is an external DB server, this will allow you to use the DB creation tool from Foglight, next.

15. Now your settings should be applied.

16. Edit "C:\ProgramData\MySQL\MySQL Server 5.x\my.ini"  and add the following line under [mysqld] section:
    max_allowed_packet=128M

17. Restart MySQL service.

Step 2:

Switching from an Embedded to an External Database
A performance improvement in Foglight if an external database is used. This section describes how to migrate from using an embedded MySQL database with the Management Server to using an external MySQL database.

1.  Stop the Management Server.  
       
2. Start the embedded database manually:    
   
   foglight_home>\bin\runDb.bat
   
3. Export the database content:    
   
   \mysql\bin\mysqldump  ‑ uroot -p[password] --port=13306 –routines --databases foglight >  \foglight.dump
   
   Note: default password is foglight
   ie: mysqldump  -uroot -pfoglight --port=13306 -–routines --databases foglight > C:\Quest_Software\Foglight\foglight.dump
   
4. Shut down the database:    
   
   \bin\shutdownDb.bat
   
5. Connect to the external MySQL instance via MySQL:   
    
   \mysql\bin\mysql  -h localhost -P 3306 -u root –p
   
6. Create the Foglight database instance by running:   
    
   mysql> source  \scripts\sql\mysql_create_db.sql
   
   Note: I have included a new mysql_create_db.sql, with default database name, schema and password (all are foglight). Use the included script or personalize with your own variables.

7. Re-import the data dump:

   Note: To avoid running into MySQL error 1153, (packet bigger than max_allowed_packet), run these commands before importing DB.

   set global net_buffer_length=1000000;
   set global max_allowed_packet=1000000000;
   Then import the data using the following command:
   mysql> source \foglight.dump

8. Configure the Management Server to use this new database by opening the following file:

   \config\server.config

9. Update the following values in the above file:

   server.database.host = "localhost";

   server.database.port = "3306";
   
   server.database.embedded = "false";

10. Remove the part of your Foglight installation that was used for the embedded MySQL database by deleting the directory

    
    \mysql.

11. Restart the Management Server.

12. Verify that the Management Server starts successfully:
    
    a. Open the most recent Management Server log file in \logs.
    b. Look for the message Forge Server startup completed and ensure that there are no errors listed after it.

13. Verify that the Management Server connects to the migrated external MySQL database. There are two ways to do so:
    
    • Open the most recent Management Server log file in \logs.server.databaseserver.database.embeddedfalse
    • Log in to the browser interface and navigate to Dashboards > Administration > Setup & Support > Foglight configuration