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.