Introduction
It may be necessary to upgrade the current version of MySQL that Variphy is using to the latest production release available in order to address security concerns with older versions.
This document will cover the steps to upgrade from MySQL 5.0.x -5.5.x, to MySQL 5.6.21 (the latest production release as of this date).
Please note that Variphy does not support MySQL directly and that this document is provided for our customers’ convenience.
We have tested the following process in the lab to our satisfaction that there should not be any subsequent database issues following the upgrade.
Step 1) Download latest/desired MySQL installer
The current GA release of MySQL community server is 8.0, however, this process should also work with any previous or subsequent revisions. The full installer package for Windows can be found here.
You can skip the Login/Sign Up buttons and just click on the “No thanks, just start my download” link below:
https://dev.mysql.com/downloads/mysql/
Step 2) Stop and disable the “Variphy Insight” Service on the Variphy server
It is required that all CDR processing be stopped during the upgrade process, so that it is not trying to write CDR inserts during the database backup and restore portion of the process, or during the upgrade itself. To stop processing, open the Window services menu and stop the Variphy CDR Manager Service. It is also recommended to disable it in the service properties, since there will be some reboots required and we want it to stay off until the upgrade is complete
Step 3) Create a database “dump” backup file
Since the upgrade will essentially create a new database file, we will need to backup the CDR databases to a Mysqldump file, which will then be used to import the databases back into the new installation of MySQL. For large databases of several GB’s, this may take several hours. You will need to create the dump file on a drive with enough free space to hold a file of equal or greater size to the databases you are backing up. To check the database sizes, open a CMD window (right-click and run as Administrator), then from the root of C: browse to the location of the bin folder under the MySQL install path, e.g.:
cd Program Files\MySQL\MySQL Server 5.x\bin
-u
Then use the command to log in: mysql –u root –p [Enter].
Enter the password on the following line and you should see the Welcome mesage followed by the mysql> prompt.
You can check the database sizes by pasting in the following query at the mysql> prompt:
SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
GROUP BY table_schema ;
You’ll get an output table with the individual sizes. Make sure that wherever you’re dumping the databases to there is at least that much space available. It is also recommended to take a screen shot of the output for later comparison.
Next, open a second CMD window by right-clicking the Command Prompt Icon and running as Administrator. From the root of C:, browse to the bin folder, as when you logged into MySQL. This time though we are going to run the mysqldump utility in the bin folder (**Note – this is not done directly from within the MySQL CLI, hence the need for a second CMD window).
Once you are at the bin directory in the CMD window, run the following query to perform the dump. You will need to put the name of your CDR databases into the query exactly as they appear in the results from the previous query, and separating each with a space, i.e. cdr1 cdr2 cdr3 cdr4 etc. Then, specify a path to a folder where the dump file will reside, e.g. C:\SQLdump\filename.sql
mysqldump -u root –p –databases cdr cdr2 cdr3 cdr4 > c:\SQLdump\MyCDRdatabases.sql
For large databases, the cursor will appear to sit idle once you enter the password, but it is in fact backing up the databases to the specified file location. This process may take several hours. Once it completes, it will go back to the C:\… \bin prompt. You can then check the folder where you dumped the file to and should see a single large .SQL file there, containing all of your CDR databases.
UPGRADING MYSQL:
Once you have finished the mysql dump, the next step will be to completely remove the existing version of MySQL. This will require a reboot of the server. Once the server is rebooted, you can then proceed with installing the new MySQL and importing back the databases from the .SQL dump file.
Step 4) Check My.ini file for DB cache settings:
Prior to removing the current version of MySQL, it is advised to check the INNODB_BUFFER_POOL_SIZE setting in the My.ini file. This is the configuration file that MySQL loads when it starts up, and the INNODB_ setting determines how much memory will be available for data caching. When setting up the new MySQL, this setting will need to be adjusted to match in the new My.ini file.
The my.ini file can be located in a few different locations, depending on how MySQL was previously installed. The easiest way to determine the location is to open up the MySQL service properties window and look at the last part of the “Path to executable” string:
Browse to the location of the my.ini file and open it with notepad. Do a CTRL+F to search for Innodb_Buffer.
for example, if the setting is INNODB_BUFFER_POOL_SIZE=8G, the buffer is set for 8 Gigabytes.
Make a note of the innodb_buffer_pool_size= setting for later.
Step 5) Uninstall Existing MySQL:
You should be able to uninstall MySQL from from the Start Menu, under All Programs>MySQL >MySQL Installer-Community folder. Simply follow the defaults for the most part.
Once you get to the section requesting removal of the data folder, you may want to tell it NO for the time being, until you are sure the databases have been successfully re-imported from the SQL dump later. Once they are, the folder in question can be manually deleted.
Finally, you will be prompted for a reboot to finish the install.
Step 6) Install New MySQL
Run the installer that you previously downloaded and select MySQL Servers. In the next screen, drill down to MySQL Server version and click on the right arrow to add it to the install list, then NEXT.
On the following screen, you can click on the “Advanced Options” link to change the default install and data paths as desired, or just accept the defaults to continue.
Click EXECUTE to install on the subsequent screen, then NEXT to configure the product. Choose “Server Machine” if Variphy will be sharing resources with other applications on the server, or “Dedicated Machine” if the server is dedicated exclusively for Variphy and its related apps (FTP and MySQL). Then click on the Advanced Options box and NEXT.
Next, set the root account password. **Note – this should be set to exactly the same as the root password from the previous MySQL installation. Then click on “Add User” and specify an additional root account with the same password.
In the Windows Service configuration, pre-pend the MySQL service name with “Variphy-“. This is optional, but makes it easier to find the service as it will group it alphabetically with the other Variphy services.
In the Advanced Options page, UN-check the “Slow Query Log” box and hit NEXT.
On the “Apply Server Configuration” page, click EXECUTE, then FINISH once it has completed all the steps.
Step 7) Edit new my.ini file:
The new MySQL install is now complete. Next, you will want to edit the new my.ini file created from this install so that the innodb_buffer_pool_size setting is the same as in the old file (see previous section, ). If you have any difficulty saving changes to the file, stop the Variphy-MySQL service, copy the file to another location like your desktop, edit the file there, and then copy it back into the folder.
Once you have saved the file with the new setting, restart the Variphy-MySQL windows service for the change to take effect.
RESTORE DATABASES FROM DUMP FILE
Step 8) Right-click on the Command Prompt icon and click “Run as Administrator”:
Then go to the root of C: and type cd path to you rinstall directory’s bin folder, e.g.:
cd Program Files\MySQL\MySQL Server 5.x\bin
Step 9) Type the following command to restore the databases from SQL dump file you created earlier, e.g.:
Mysql –u root –p < C:\SQLdump\MyCDRdatabases.sql
Enter password: *******
Again, depending on the sizes of databases, this process will likely take several hours and the cursor will appear to sit inactive during this time. When it completes, it will take you back to the C:\ . . . \bin>prompt. Once complete, Test the databases to ensure data is intact.
Step 10) Test the databases:
From the CMD window, run the same command from earlier to check the database sizes, and compare them to the screenshot you took earlier to make sure the sizes are correct.
SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
GROUP BY table_schema ;
Next, log into Variphy Insight and test the call usage section for each cluster. You should be able to search for calls in each one, prior to the time that the CDR Manager was disabled. As long as each cluster can connect to the database without error and pull some calls from the previous day, then the databases should be fine. At this point, you can delete the old database files from the MySQL 5.5.x installation, if you did not choose to remove them during the uninstall process.
Step 11) Reactivate Variphy CDR Processing
Restart the Variphy Insight Service to begin processing records again. It may take a few hours for this to complete as well, as it will be writing records to the database while MySQL is also busy caching data.
Upgrade Complete
If you encounter any problems with the upgrade process, please contact support@variphy.com for assistance.
