How to Install and Configure MySQL Server and Workbench on a Local PC

[cody.bearden]

Introduction:

This post will guide you through the steps of installing and configuring MySQL Server & Workbench on a local PC and establishing a database server connection with a Weintek HMI.

Software Version:

EasyBuilder Pro 6.03.02.393+
MySQL Installer

Related Tutorials:

How to search for or filter items within a recipe database
Video - SQL Sync, how to set up a Local SQL Database
Video - Sync Data to a central database with a SQL Query object
Video - SQL Query Object: Advanced Mode

Instructions:

Installing MySQL Workbench & Server

1. First, download the MySQL Installer here:
   

   

2. Navigate to the downloaded file and start the installation:
   

   

3. Choose the “Custom” setup type in the MySQL Installer and click “Next”':
   

   

4. On the following window, select the latest versions of MySQL Server & MySQL Workbench and add them to the “Products To Be Installed” list. Select “Next” when finished:
   

   

5. Select “Execute” to download MySQL Server and MySQL Workbench:
   

   

6. Select “Next” when the download is complete:
   

   

7. Select "Next’ to begin MySQL Server configuration:
   

   

8. Ensure that “TCP/IP” is selected and the port number is “3306”. Click “Next” when finished:
   

   

9. Choose the “Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)” option and click “Next”:
   

   

10. Enter a password for the MySQL Server root account and click “Next”:
    

    

11. The settings in the following sections can remain default. Select “Execute” on the final page:
    

    
    
    
    
    
    
    

12. Select “Finish” when MySQL Server configuration is complete:
    

    

13. Select “Next” and “Finish” on the following windows:
    

    
    
    
    

Building a Database

After installing MySQL Server & Workbench, a Schema must be created for synchronizing HMI historical data.

1. Launch MySQL Workbench and click the plus icon to build a connection with the Database Server:
   

   

2. On the following window, enter a “Connection Name,” “Hostname,” “Port” (3306), and “Username.” Click “OK” when finished:
   Note: For the “Hostname,” I’m using the loopback address of my PC, as this database server will be hosted locally. A loopback address will allow a device to send and receive its own data packets.
   

   

3. When finished, the new server can be found within your “MySQL Connections.” Click the new server and enter your password:
   

   

4. Select the “Create a new schema” icon and enter a name for the schema. Click “Apply” when finished:
   

   

5. Click “Apply” and “Finish” on the following windows:
   

   
   
   
   

6. In the “Administration” tab of the “Navigator,” select “Users and Privileges”:
   

   

7. Select “Add Account” and enter user credentials in the “Login” tab. Click “Apply” when finished:
   

   
   
   
   

8. Select all of the “Roles” and “Global Privileges” within the “Administrative Roles” tab. Select “Apply” when finished:
   

   

9. In the “Account Limits” Section, you may also configure maximum queries, updates, connections, and concurrent connections per user. Select “Apply” when finished:
   

   

EasyBuilder Pro Project Configuration

1. Within the “Data/History” tab, select “Database Server”:
   

   

2. Select “New” to configure a new Database Server:
   

3. In the “General” tab, enter the database server parameters configured earlier when we set up MySQL Server. Text entered in the “Comment” field will essentially name the server within your EasyBuilder Pro project and can make it easier to choose this server from a list within other objects that sync (i.e. the “Data Sampling” or “Event (Alarm) Log” object):
   Note: the PC’s loopback address (127.0.0.1) may be used in the “Database Server” section of the project during simulation, while the PC’s IP address must be specified before project download.
   

   

4. In the “Status/Control” tab, choose an address for the “Status” register and enable the “Control” address. The status addresses display the connection status with the SQL server on the HMI, and the control addresses can change connection parameters dynamically on the HMI. Select “OK” when finished:
   

   

5. To sync sampled data within your project to your MySQL server, within your “Data Sampling” object, enable the “History File” and the “Sync to database” options. Choose your MySQL server’s IP address from the “Database” drop-down list. Click “OK” when finished:
   Note: at least one “Data Record” must be configured before enabling the “History file.”
   
   

   

6. To sync event log data within your project to your MySQL server, within the “History/Control” tab of the “Event (Alarm) Log,” enable the “History” and the “Sync to database” options. Choose your MySQL server’s IP address from the “Database” drop-down list:
   
   

   

Note: data will be synchronized to the MySQL server only when the number of “Data Sampling”/“Event (Alarm) Log” records reach 10000. To start synchronization manually, please select the “Enable” check box in the “Control” section in the settings of the “Data Sampling”/“Event (Alarm) Log” object. Then, give a command by entering the corresponding value in the designated address.

Keywords

sql, mysql, server, workbench, work bench, local, database, install, installing, installer, schema, record, synchronize, synchronization, synchronized