MySQL Installation and Configuration
This section contains information for installing and configuring MySQL on a supported Windows operating system. For Linux installation and configuration instructions, see Installing MySQL on Linux.
To create and maintain a MySQL aPriori database you must install these:
• MySQL Server 5.6 or 5.7
• MySQL Workbench 6.3
Installing MySQL Server on Windows
aPriori requires that MySQL Server be installed on the server only. The MySQL Server installer and license are provided by aPriori and can be found with the aPriori installation media.
To begin MySQL server installation on Windows (MySQL Win Part 1)
1 Acquire and run mysql-installer-commercial-<version>.msi, the installer package for 64-bit servers.
Note: At the time of publication of this guide, the correct versions for 5.6 and 5.7 were 5.6.36.0.and 5.7.18.0 respectively. You may be installing a later version of the installer package.
2 Accept the license terms and click Next.
3 Select your Oracle Support option and click Next.
4 Select Custom and click Next
5 On the next screen expand MySQL Servers and Applications, then move the following modules to the Products/Features to Be Installed column.
• MySQL Server <version> - X64
• MySQL Workbench <version> -X64
You do not need any of the “MySQL Connectors” or “Documentation”. Click Next.
6 If any conflicts are reported, review and correct them, and then click Next.
7 Click Execute. When the execution is complete, to proceed to the configuration steps, click Next
MySQL Server Configuration screen (MySQL Win Part 2)
1 On the Type and Networking screen:
• For Sever Configuration Type select Server Machine from the dropdown
• The default port number is 3306. Change the number as required. Make sure that the Open Firewall port for network access check box is selected.
• Select the Show Advanced Options check box and then click Next.
• Determine whether to enable the Enterprise Firewall and select or clear the check box accordingly.
• Click Next.
2 By default, there is no password on the Accounts and Roles screen. To complete the installation, you must set a password for the built in MySQL "root" administrator. Be sure to remember the password because you must supply it later in the installation. For now, do not create any other users. Click Next.
3 On the Windows Service screen, the default settings here should be fine. However, if you prefer, you can change the name of the service and/or the account under which that service runs. Click Next.
4 (MySQL 5.7 only): If you are presented with a Plugins and Extensions screen, you can leave Enable X Protocol/MySQL as a Document Store disabled (unchecked). Click Next.
5 On the Advanced Options screen you should only change the default logging options if directed to do so by aPriori Customer Support. Typically, you should accept the default logging options, then click Next.
6 On the Apply Server Configuration screen, click Execute to apply the configuration settings. Use the Configuration Steps and Log tabs to monitor progress and troubleshoot issues. Once the configuration is done click Finish.
MySQL Workbench (MySQL Win Part 3)
1 When the install is complete, MySQL Workbench should start. If it does not, you can start it from the Windows Start menu.
2 If you changed the path of the data location earlier, you need to edit the connection. Right click on the Local instance of MySQL and select edit connection. Click the System Profile tab and then replace the path for \my.ini file with the path you chose for the data files. Click Close.
3 Connect to the local instance by clicking on the Local instance MySQL icon. If prompted, enter the password that you created for the “root” user.
4 Click Options File from the list on the left. If prompted, enter your password again.
5 On the General tab, scroll down and make sure that the default storage engine is INNODB and that the character set server is utf8.
6 Next, go to the Networking tab and change max_allowed_packet to 256M. (Take note of this value, particularly if aPriori Customer Support has directed you to use a different value. If you are running MYSQL Version 5.6.19 or later, you need it in the next step.)
7 [FOR MYSQL VERSIONS LATER THAN 5.6.19] Open the InnoDB tab and scroll to innodb_log_file_size. Ensure that this line is checked and set the value to at least six times the value of max_allowed_packet that you set in the previous step. For example, for the default max_allowed_packet value of 256M, enter a value of at least 1536M for innodb_log_file_size.
8 On the InnoDB tab, scroll to innodb_buffer_pool_size and ensure that it is set to a value at 1G or greater.
9 In the lower right corner, click Apply. Then, in the popup window, again click Apply.
10 To create a new schema, select the +schema icon.
11 Name the schema. For single production databases, "apriori" is a typical schema name. Click Apply.
12 Click Apply again and then click Finish.
13 You can create more schemas as necessary, such as "apriori_test" and "apriori_training" for test and training purposes, respectively.
Import standarddata.sql (MySQL Win Part4)
Next, import the standarddata.sql file into your newly created schemas. You can download and expand the standarddata.sql file from the aPriori-databases-<version>.zip file. For more information, see Chapter 1.
1 In the list on the left, click Data Import/Restore. Alternatively, in the menu bar, select Server > Data Import.
2 Select the Import from Self-Contained File radio button and browse to the standarddata<version>.sql file. For example:
C:\Users\<username>\Downloads\aPriori-databases-<version>\MySQL\ standarddata.sql
Click Open
3 Specify which schema to import into from the Default Target Schema drop-down menu. Click the Start Import button in the bottom right corner. Once it is finished, select the Import from Disk tab and repeat the same process for the other schemas that were created.
Create a user and assign privileges (MySQL Win Part 5)
1 Next create a user and assign privileges: Select Users and Privileges from the list on the left or from the Server menu.
2 Click the Add Account button located on the bottom left.
3 Enter a Login Name and Password. They can be anything you would like. Confirm the password, then click Apply.
4 Click on the Schema Privileges tab, then click the Add Entry button.
5 Select the Schemas matching pattern or name radio button. If all the schemas start with the same name you can type the beginning and use a wild card. For example: apriori% Click OK.
6 You need to select all the permissions under Object Rights (SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW), plus TRIGGER under DDL RIGHTS. Select each one and then click Apply.
You can exit MySQL Workbench.
FOR ENTERPRISE PLATFORM ONLY - Edit the startup file (MySQL Win Part 6)
If you are planning to install Enterprise Platform modules such as Cost Insight Report or Cost Insight Admin, you must set a system variable in the MySQL startup file to ensure that timestamp columns get correctly updated during future upgrades. This system variable (named explicit_defaults_for_timestamp) must be set to “1” (or “TRUE”). For information about why this setting is necessary, see the MySQL documentation at https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp.
1 Open Notepad (or your preferred text editor as an Administrator.
2 Navigate to the MySQL initialization file and open it. (This file is typically ProgramData\MySQL\MySQL Server <version>\my.ini If you cannot see the ProgramData folder, you need to turn on hidden files in Windows Explorer.
3 Scroll to the bottom of the file and add these lines:
# System variable added for aPriori
explicit_defaults_for_timestamp=1
4 Save the file.
Restart the MySQL service (MySQL Win Part 7)
Finally, stop and restart the MySQL service (MySQL<version>) from Control Panel \ Administrative tools \ Services.
Anti-virus Software Exceptions for MySQL (MySQL Win Part 8)
We recommend excluding the MySQL data directories from virus scans. In addition to the data directory, MySQL uses C:\WINDOWS\Temp when importing and exporting databases. This should also be excluded from virus scanning. By default, the MySQL data directory is located at: C:\ProgramData\MySQL\MySQL Server <version>\data
Installing MySQL on Linux
Important: aPriori requires a case-insensitive database. By default, MySQL on Linux is case-sensitive. Keep this in mind if you plan to use an existing Linux MySQL database server. To make the server case-insensitive, add the following line to the my.cnf file, then restart the MySQL process before importing the data.
lower-case-table-names=1
The following sections only apply when using MySQL on a Linux operating system. These instructions were written and tested with CentOS version 5.5. The specific Linux syntax may vary slightly between versions. These instructions assume the user has a general understanding of the specific version of Linux being used.
There are GUI interfaces for MySQL available on Linux which can also be used. Command line instructions are provided since your Linux server may not have a GUI installed.
You need:
• Root (administrative) privileges on the Linux server.
• The MySQL server and client RPM packages for the specific version of Linux on which you are installing (provided by aPriori).
• The aPriori database files which contain the aPriori schema (provided by aPriori).
Note: In this section, where a command is to be typed, it is preceded by either “shell>” or “mysql>”. These are command prompts and are not to be typed as part of the command.
MySQL Client Components (MySQL Linux Part 1)
Run the RPM package that was provided to you by aPriori Support. You may have to use the –nodeps and –force options as used in the following command.
Note that the names of the RPM files differ slightly from what is shown below depending on the version of Linux you are using.
shell> rpm -i –nodeps –force MySQL-client-advanced-5.6.16-1.el6.x86_64.rpm
MySQL Server Components (MySQL Linux Part 2)
Run the RPM package that was provided to you by aPriori Support. You may have to use the –nodeps and –force options as used in the following command.
shell> rpm -i –nodeps –force MySQL-server-advanced-5.6.16-1.el6.x86_64.rpm
The installation should complete on its own and start MySQL. You can verify that it is running by typing the following command to see the version information about MySQL.
shell> mysqladmin version
Securing MySQL on Linux (MySQL Linux Part 3)
MySQL has a default administrative user called root. After installing MySQL, the root user ID has no password. Follow these steps to set the root password.
1 Launch mysql
shell> mysql –u root –p
2 View accounts
mysql> SELECT User, Host, Password FROM mysql.user;
3 Use the following steps to allow root access from remote machines. This is an optional security setting which allows you to administer the database server from the MySQL Administrator GUI on a Windows computer.
mysql> create user root;
mysql> grant ALL on *.* to root;
4 Set the root password. Replace <yourpassword> below with the password you want to use.
mysql> UPDATE mysql.user SET Password = PASSWORD('<yourpassword>')
WHERE User = 'root';
WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
5 Optional: Remove the anonymous accounts. Note, the " ‘’ " just before the " @ " are two single quotes, not 1 double quote.
mysql> DROP USER ‘’@’localhost';
mysql> DROP USER ‘’@’localhost.localdomain’;
Note: The exact host name comes from the table in Step 2.
6 View accounts again.
mysql> SELECT user, host, password FROM mysql.user;
7 Exit MySQL
mysql> exit
MySQL Options File in Linux (MySQL Linux Part 4)
Edit the file /etc/my.cnf:
1 Go to the /etc directory and type:
shell> vi my.cnf
2 Press Alt-I to insert
3 Move down to the section under [mysqld] and type these two lines
max_allowed_packet=268436480
lower-case-table-names=1
4 [FOR MYSQL VERSIONS LATER THAN 5.6.19] Also add the following line:
innodb_log_file_size=1610612736
This value should be set to at least six times the value of max_allowed_packet that you set in the previous step.
5 At the end of the file, append these lines:
# System variable added for aPriori
explicit_defaults_for_timestamp=1
This setting ensures that timestamp columns get correctly updated during future upgrades. For information about why this setting is necessary, see the MySQL documentation at https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp.
6 Press ESC, type ‘:wq’ followed by Enter
7 Stop MySQL
shell> mysqladmin -u root –p shutdown
8 Start MySQL
shell> mysqld –user=mysql –log &
Creating the aPriori database and users in Linux (MySQL Linux Part 5)
1 Start the MySQL client again.
shell> mysql –u root –p
2 Create the databases and users
mysql> CREATE DATABASE apriori;
(optional) mysql> CREATE DATABASE apriori_test;
(optional) mysql> CREATE DATABASE apriori_training;
mysql> CREATE USER apriori IDENTIFIED BY 'apriori';
mysql> CREATE USER admin IDENTIFIED BY 'admin';
3 The following statements grant the proper privileges to the users. They assign a set of privileges to the users ‘apriori’ and ‘admin’ on the databases ‘apriori’, ‘apriori_test’, and ‘apriori_training’. The user ‘admin’ has all the privileges as ‘apriori’ plus DROP and CREATE. The permissions on all 3 of the databases are the same.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON apriori.*
to apriori;
ON apriori.*
to apriori;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, DROP, CREATE
ON apriori.*
to admin;
ON apriori.*
to admin;
(optional) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON apriori_test.*
to apriori;
ON apriori_test.*
to apriori;
(optional) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, DROP, CREATE
ON apriori_test.*
to admin;
ON apriori_test.*
to admin;
(optional) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON apriori_training.*
to apriori;
ON apriori_training.*
to apriori;
(optional) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, DROP, CREATE
ON apriori_training.*
to admin;
ON apriori_training.*
to admin;
mysql> exit
4 Import the database:
• Save the standarddata<version>.sql files on the Linux server. This file is contained within the database zip file that was provided with the software downloads.
• Go to the folder containing the database files and type the following commands, replacing <version> with the actual version number and platform of the file to be imported.
shell> mysql –u root –p apriori < standarddata<version>.sql
(optional) shell> mysql –u root –p apriori_test < standarddata<version>.sql
(optional) shell> mysql –u root –p apriori_training < standarddata<version>.sql