View source for Command Center Upgrade MySQL5 to MySQL8

=Command Center Upgrade Including MySQL5 to MySQL8=

'''THIS IS A WORK IN PROGRESS PLEASE CONSIDER THIS AS REFERENCE ONLY AT THIS TIME'''

'''THERE MAY BE ISSUES PRESENT IN THIS PROCEDURE CURRENTLY'''

The follow page walks through the process of upgrading from a system running MySQL5 to a new system running MySQL8.  MySQL8 is considered the standard moving forward.

==Upgrade to Recent Version that leverages MySQL5==

This step is critical as it puts the system in a known state for upgrading to MySQL8.  A later version of MySQL5 supported Command Center is v123 

Edit install.conf in the ~/cc_install directory and set the version as shown below

    command_center_image_version=123

Run the UpdateCommandCenter shell script after making the change 

    sudo ./UpdateCommandCenter.sh 

This will stop the currently running container, download the new images, install them, and restart the new version of the container. Confirm that everything is still working at this point to be sure that there are no errors.

There will still be the original container left after this is completed, it can be seen by performing the below command.  Note that the new container is 85a37aa455c2 and the old one is df6dc735424a.

      sudo docker container ls -a 
       CONTAINER ID   IMAGE                              COMMAND                  CREATED          STATUS                        PORTS                                       NAMES
    85e37aa455c2   lingoport/command-center_dev:123   "catalina.sh run"        27 minutes ago   Up 26 minutes                 0.0.0.0:8083->8080/tcp, :::8083->8080/tcp   jolly_bell
    5debec9603c6   jenkins/jenkins:lts-jdk11          "/usr/bin/tini -- /u…"   2 weeks ago      Exited (143) 44 hours ago                                                 competent_carson
    df6dc7e5424a   lingoport/command-center_dev:102   "catalina.sh run"        2 months ago     Exited (143) 27 minutes ago                                               strange_hawking
    ea33ced44224   mysql:5.7                          "docker-entrypoint.s…"   2 months ago     Up 2 weeks                    3306/tcp, 33060/tcp                         sad_lederberg

==Backup the MySQL5 Based DB==

Log into the MySQL Container 

    [ec2-user@ip-172-31-77-246 cc_install]$ sudo docker exec -it <CONTAINER ID> bash

Log into MySQL inside of the container and get the MySQL root password from the install.conf file

    mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 105
    Server version: 5.7.42 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2023, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | LRM                |
    | commandcenter      |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    6 rows in set (0.00 sec)

There are two databases that contain the required data for the upgrade.  One is LRM and the other is commandcenter as can be seen in the above output.

The directory /etc/mysql/conf.d is mounted as as a volume on the vm itself so it can be used as a location to place the backup file(s).  On the VM it is mounted as /home/<USER>/mysql/conf.d

    mkdir *etc/mysql/conf.d/db_backups*
    mysqldump -u root -p --databases LRM commandcenter > /etc/mysql/conf.d/db_backups/lrm_cc_dbs.sql

Exit out of the shell in the container and return back to the virtual machine shell.

Move the db_backups directory on the VM to /home/<USER>/db_backups so it is easily available
    mv /etc/mysql/conf.d/db_backups ~/

Stop the running docker containers for both Command Center and MySQL5 using the below command on each of their containers.

    sudo docker stop <CONTAINER ID>

==Upgrade to the MySQL8 Based DB Version==

At this point, the latest version of the Command Center Installation software is needed so that MySQL8 can be installed.  Note that there is a difference between the Installation scripts so its required to have an install script that supports MySQL8

Update the install.conf file to the next version "124" which will use MySQL8

    command_center_image_version=124

Run the UpdateCommandCenter.sh to perform the upgrade, this time it will be built with MySQL8 support as we are using the new install script.

Copy the db_backups directory that was created prior back into the volume directory so it can be accessible from within the container

    cp ~/db_backups ~/mysql/conf.d/db_backups

Log into the MySQL8 Container similar to before.

    [ec2-user@ip-172-31-77-246 cc_install]$ sudo docker exec -it <CONTAINER ID> bash

Go to the /etc/mysql/conf.d/db_backups directory and install the backup from  within the container

    cd /etc/mysql/conf.d/db_backups
    mysql -uroot -p < ./lrm_cc_dbs.sql

Exit out of the container shell and return back to the Virtual Machine

Confirm via web browser that its possible to log into Command Center using original credentials from MySQL5.7 system and that any existing projects are still present.

=New Backup Script Available=
There is a new Backup script that can be used to manually create a backup of this new system.  Create the new directory prior to running it.  It will store the backup in the home directory as shown.

    /home/<USER>/commandcenter/backup/

Simply run from the cc_install directory 

    sudo ./BackupCommandCenterDatabase.sh

Now that everything is upgraded to MySQL8 and a backup has been created , its is possible to install later versions of Command Center. Go back  to the install.conf file and set the version as desired.  Once set, run the UpdateCommandCenter.sh script to upgrade to the desired version.

    sudo ./UpdateCommandCenter.sh