OPC to MySQL: Writing OPC to the MySQL database

Configuring how to write OPC data to a MySQL database is similar to configuring how to write data to MSSQL. Only the main differences are described in this manual.

1. Download and install the 32-bit version of MySQL ODBC Connector (the ODBC driver) for MySQL from mysql.org.

Fig. 1 Driver section

2. The structure of a table in the database will look like this:

2.1 In the case of writing a lot of OPC items

CREATE TABLE `opc_data` (
 `REC_ID` int(11) NOT NULL AUTO_INCREMENT,
 `TIMESTAMP` datetime DEFAULT NULL,
 `ITEM_NAME` varchar(25) NOT NULL,
 `ITEM_VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`REC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.2 In case of writing several OPC items

CREATE TABLE `opc_data_2` (
  `REC_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP` datetime NOT NULL,
  `ITEM1` varchar(30) DEFAULT NULL,
  `ITEM2` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`REC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you specify any other data type than String for ITEM1 or ITEM2 in the OPC settings, you should create the columns of the corresponding types while creating a table.

3. Create an ODBC data source for your MySQL database.

4. During "Binding", you should take into account that the names of the table and its columns are case-sensitive in MySQL.

Related articles:

• Inserting OPC data to MS Access database through ODBC (features: adding groups and items, inserting to an ODBC database)
• Adding a timestamp in the CSV file (features: OPC groups)
• How to log OPC data to a SQL database.
• OPC to MSSQL: Writing a lot of OPC tags to MS SQL 2008 database
• OPC to MSSQL: Writing several OPC tags to separate columns
• OPC to MySQL: Writing OPC tags to MySQL 5 database
• OPC to a database: Writing OPC tags to a database
• OPC to Excel: Writing OPC data to Excel
• Filtering data by a tag value and write data to a database only when the value will change
• Filtering data out if an OPC tag value is not equal to a specified value
• Processing or storing OPC data by an event from the OPC server
• Aggregating data from multiple servers to one OPC server

OPC and DCOM Configuration on Windows 2008 and Windows 7

• Part 1. Installing OPC Core Components.
• Part 2. OPC and DCOM Configuration. Creating a user and giving access permissions.
• Part 3. Configuring the Windows 2008 and Windows 7 firewall for DCOM and OPC.
• Part 4. DCOM Configuration. Dcomcnfg.
• Part 5. OPC and DCOM Configuration. Typical problems and solutions.

OPC and DCOM Configuration on Windows 10, 11, Windows Server 2019, 2022

• Part 1. Installing OPC Core Components.
• Part 2. OPC and DCOM Configuration. Creating a user and giving access permissions.
• Part 3. Configuring the Windows 10, 11, Windows Server 2019, 2022 firewall for DCOM and OPC.
• Part 4. DCOM Configuration. Dcomcnfg.
• Part 5. OPC and DCOM Configuration. Typical problems and solutions.