Upgrading MySQL from 32-bit to 64-bit
UPGRADING MYSQL FROM 32-BIT TO 64-BIT ... 1
Overview ... 1
Upgrading MySQL from 32-bit to 64-bit ... 1
Document Revision History ... 21
Overview
This document will walk you through upgrading your MySQL from 32-bit to 64-bit.
IMPORTANT: FOLLOW THESE INSTRUCTIONS VERY CLOSELY. If these instructions are not followed, you run the risk of damaging your server!
Upgrading MySQL from 32-bit to 64-bit
The following should be done on the LabTech server, NOT on a workstation.
1. Download the latest version of SQLyog.
2. Once installed, double-click on the SQLyog icon to launch.
3. Click on New to create new connection.
4. Enter the name of the connection and click OK.
5. Enter localhost in the MySQL Host Address.
6. Enter your LabTech Super Admin Username and Password.
7. Leave the Database(s) field blank.
8. All other fields can be left at their default. Your screen should look similar to the following:
Figure 1: Connect to MySQL
9. Click Connect.
10. Click OK to save the connection. Once successfully connected, the LabTech database is available.
11. Connect to the LabTech database, if not already connected.
12. Select Database > Backup/Export > Backup Database As SQL Dump…
Figure 2: Backup Database As SQL Dump
The SQL Dump window will display:
Figure 3: SQL Dump
13. Choose ‘labtech’ from the Database name drop-down list.
14. In the Export to file field, choose a location to export the structure and data file to.
15. Select Structure and Data.
16. Click Export. The file will be saved in the designated location with a .sql
extension. When the export has completed, an “Export Successfully’ will display in the lower left-hand corner of the SQL Dump window.
17. Click Done to close this window.
Figure 4: Exported DB File
18. From your LabTech server, click Start > Administrative Tools > Services.
Figure 5: Services
19. Locate ‘LabTech MySQL Service’ and right-click to select Stop to stop the service.
20. You will be asked to confirm other services being stopped.
Figure 6: Stop Other Services
21. Click Yes to stop these other services.
22. From a command prompt, type ‘cd c:\Program Files (x86)\LabTech\mysql\bin’.
Once at this prompt, execute the following command: ‘mysqld.exe --remove labmysql’.
NOTE: Be sure to include a double hyphen preceded by a space in the above command.
Figure 7: Command Executed
You should see ‘Service successfully removed’.
23. Download and run the MySQL Server 5.5.29 (mysql-5.5.29-winx64.msi) The Setup Wizard will display:
NOTE: LabTech is installed with MySQL 5.5.28a and only supports versions 5.5.xx with a minimum build of 5.5.28a. All other versions are not supported.
Figure 8: SQL 64-bit Installer
24. Click Next. The license agreement will display. Select the I accept the terms in the License Agreement if you accept them and click Next.
Figure 9: MySQL Setup Wizard
25. Select Typical.
Figure 10: Installation Settings
26. Click Install.
27. A MySQL Enterprise and Enterprise Monitor Service informational window will display. Click Next through these two screens.
Figure 11: Wizard Completed
28. Select the Launch the MySQL Instance Configuration Wizard checkbox, if not selected.
29. Click Finish to finish the installation. The MySQL Server Instance Configuration Wizard will begin.
Figure 12: MySQL Configuration Wizard
30. Click Next.
Figure 13: MySQL Detailed Configuration
31. Select Detailed Configuration and click Next.
Figure 14: Detailed Configuration—Server Machine
32. Select Server Machine and click Next.
Figure 15: Configuration—Transactional Database Only
33. Select Transactional Database Only and click Next.
Figure 16: Configuration—InnoDB Datafiles
34. Select the settings as shown in the above screen capture and click Next.
Figure 17: Configuration—Online Transaction Processing
35. Select Online Transaction Processing and click Next.
Figure 18: Configuration—Networking Options
36. Set the networking options as follows:
a) Select Enable TCP/IP Networking.
b) Select ‘3306’ for the Port Number from the drop-down.
c) Select Add firewall exception for this port.
d) Deselect Enable Strict Mode.
37. Click Next.
Figure 19: Configuration—Default Character Set
38. Select Best Support for Multilingualism and click Next.
Figure 20: Configuration—Windows Options
39. Configure the MySQL Server server instance, as follows:
a) Select the Install as Windows Service checkbox.
b) Enter LabMySQL in Service Name.
c) Select the Launch the MySQL Server automatically checkbox.
40. Click Next.
Figure 21: Configuration—Security Settings
41. Select the Modify Security Settings checkbox. Enter your New root password and Confirm. You can find this information in your Registry Editor: HKEY_LM
>SOFTWARE > Wow6432Node > LabTech > Setup, if necessary.
42. Click Next.
Figure 22: Configuration—Execute
43. Click Execute to start the configuration.
Figure 23: Configuration—Configuration Complete
44. Once the configuration has completed, you will see checkmarks next to each step of the configuration. Click Finish to close the Wizard.
45. Go back to SQLyog. You will need to log in as root with the root password.
Figure 24: Restore from SQL Dump
NOTE: Depending on the version of SQLyog, the Restore From SQL Dump option may no longer be listed. In more recent versions, it has been renamed to Execute SQL Script.
46. Select Database > Import > Restore From SQL Dump… or Execute SQL Script depending on your version of SQLyog.
47. Navigate to the dump file that you created earlier.
Figure 25: Execute Query
48. Click Execute. At this point, you may receive an error about packet size in the error log. Steps to fix the error will follow. If you did not receive any errors, proceed to step 52.
49. Enter ‘C:\Program Files\MySQL\MySQL Server 5.5’ in your Windows Explorer.
Figure 26: Log File
50. Right-click on the file named my. From the menu that appears, select Open with
> Notepad.
Figure 27: Log File
51. ‘max_allowed_packet=32M’ needs to be added to the my.cfg file. There is not a specific place that this needs to go in the file but we highly recommend you use the spot we show here. We have used “[mysqld]” (part of the pre-made file) as a reference so you see where to add the new text.
52. Save file.
53. Restart the ‘LabMySQL’ service (Start > Administrative Tools > Services).
54. Click OK on the error message you originally received if you haven’t and then click Execute again.
Figure 28: Execute Query
55. Depending on the version of SQLyog you are using, you may be required to restart SQLyog before the database and tables will appear. For older versions, you should now see all the labtech database tables.
Figure 29: LabTech tables
56. Open the Registry Editor (click Start and enter regedit in the Search box).
57. Expand HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > LabTech
> Agent.
58. Right-click in the right side of the window and select New > String Value.
59. Rename to ‘SQLLOC’.
60. Double-click on ‘SQLLOC’ and enter the path to your MySQL server (e.g.,
%Programfiles%\MySQL\MySQL Server 5.5\) in the Value Data field. This is used to perform daily backups and if it is not set, there may be compatibility issues in the future when it is not set.
IMPORTANT: The path must end with a backslash (\) or your backups will fail!
Figure 30: Value Data
61. From your Services window on the LabTech server, start the following services:
a) LabTech Database Agent b) LabTech Monitoring Service
c) LabTech Monitoring Service CheckUp Util d) LabTech Redirector
Figure 31: Start Services
62. Log back in to LabTech. You should see the agents starting to check back in.
63. IMPORTANT: The last step is to go your Program Files (x86)\Labtech\ folder and create a new text file named OWNMYSQL.CTL. You can do this by right-
clicking in the folder and choosing NEW > Text document. Windows, by default will append a ".txt" to the end of the file when you rename it unless you have system file extensions unhidden. You can find this option in File Explorer by going to Tools > Folder Options, then clicking on the View tab. Be sure you did not save as OWNMYSQL.CTL.TXT. This will be an empty file so you will not have to put anything in it. It just needs to be present so that the database is not corrupted when you upgrade to the next release of LabTech.
NOTE: If the menu bar does not appear in Windows Explorer, press the [Alt] key for the Tools menu to display.
Document Revision History
Date Notes
11/11/2011 New Document
11/29/2011 Updated SQL download link
12/13/2011 Added important note for ‘SQLLOC’ path.
07/05/2012 Removed root password information. Updated information for the SQLLOC registry key.
10/29/2012 Updated for MySQL version 5.5
02/11/2013 Updated MySQL link to latest version 5.5.29 02/18/2013 Added MySQL requirements
