How to set up a MySQL database for AIDA64?

MySQL and MS SQL are one of the several databases AIDA64 supports.

Creating and configuring a MySQL database

We need to install the MySQL service on our server (in this case, XAMPP), and then add a new user. We need to enter a user name and a password then select “Any host”. We can also create a database here with the same name and grant all privileges. Finally, we have to click on the “Create user” button.

We need to revoke any extra user privileges, preserving only those which they will require to perform modifications necessary for report creation. Global access rights to MySQL should be left as they are (USAGE), but we need to modify database-specific privileges, which we can do by clicking the “Edit privileges” link.

In the “Edit privileges: User 'aida64'@'%' - Database aida64” window we have to locate “Database-specific privileges” and click on “Uncheck all”, then in the “Data” column we need to check the privileges we want. We must select SELECT, INSERT and UPDATE in order to make report creation possible. If we also want to allow users to delete from the database, we can select DELETE.

To allow AIDA64 to use the database we need to initialize the tables. To do this, we have to copy the “DB - MySQL.sql” query from the “SQL_Schema” subfolder and run it on the newly created database.

The full query for these settings

This is the full query that creates the database as well as the user with the required privileges, and then initialize the tables.

CREATE DATABASE IF NOT EXISTS `aida64` ;

GRANT SELECT , INSERT , UPDATE , DELETE ON `aida64` . * TO 'aida64'@'%';

SET PASSWORD FOR 'aida64'@'%' = PASSWORD('aida64');

CREATE TABLE aida64.Report (
    ID            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    RVersion      VARCHAR(255),
    RHost         VARCHAR(255),
    RHostComment  VARCHAR(255),
    RUser         VARCHAR(255),
    RLocation     VARCHAR(255),
    RDateTime     VARCHAR(16),
    RComplete     BIT NOT NULL
);

CREATE TABLE aida64.Item (
    INum          INT,
    IPage         VARCHAR(100),
    IDevice       VARCHAR(255),
    IGroup        VARCHAR(255),
    IField        VARCHAR(255),
    IValue        VARCHAR(255),
    IIcon         INT,
    IID           INT,
    ReportID      INT UNSIGNED NOT NULL,
    CONSTRAINT    cnstI1 FOREIGN KEY(ReportID) REFERENCES Report(ID)
);

CREATE TABLE aida64.NextID (
    TableName     VARCHAR(6) PRIMARY KEY,
    NextID        INT NOT NULL
);

INSERT INTO aida64.NextID (TableName, NextID) VALUES ('Report', 1);

Installing ODBC on each computer

You can read more about MySQL ODBC prerequirement here.

It is necessary to install a 32-bit MySQL Connector/ODBC (formerly known as MyODBC Driver) on all networked computers. This can be downloaded from http://www.mysql.com/products/connector/ .

Installation is simple with the Group Policy Management Console. We need to create a new GPO, or we can select the one we already use when starting AIDA64. Right-click the GPO, and select “Edit”.

Here, we can select if we want to assign the software installation to computers or users. In order to do this, we need to expand Computer configuration / Policies / Software settings / Software installation in the menu tree and select New Package.

Then we have to browse to the MSI file we want to install. The file should be available via a UNC path in a network share with read permission. Click “Open”, then in the “Deploy Software” window select “Advanced”, press the OK button, and click the “Security” tab. Here, grant read permissions to the users for the installation. “Authenticated users” have read permissions by default.

When we click OK, we immediately add the new software to be installed to the list.

At this point, only one thing remains to be done: refreshing the group policy on the clients. As we set up an event that runs at start-up, we will need to restart the PCs.

AIDA64 settings

If we use a MySQL database, we can specify a data source, although this is not required. We need to specify the server name or IP address and the port required for the database connection as well as the user name/password of the created user, then select the database and the driver. MySQL optimization, which accelerates the process of inserting new records to the database, requires MySQL 3.22.5 or newer.

Then we have to click the “Test” button to check the settings.

Firewall Settings

Finally, we need to check the firewall settings. We should open TCP/UDP port 3306 to allow all computers on the network to connect to the database.