MySQL 5 on Linux System

After installation of Linux, open Terminal and run the following commands:

sudo apt-get update

sudo apt-get install mysql-server

mysql_secure_installation

Change the root password? [Y/n] n

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] n

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

 

To install MySQL Workbench (optional), which is the GUI for the database:

sudo apt install mysql-workbench

 

Database setup

You need to set database user not only for localhost but also for the external connections, and push the following SQL commands or via the cmd line.

 

cmd line (not Workbench):

mysql -u root -p

create user 'root'@'%' IDENTIFIED BY 'root';

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin.1';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin.1';

grant all privileges on *.* to 'root'@'%' with grant option;

We recommend changing the password, as the default one is "admin.1". This password is required during EEI Server Installation. Through terminal, follow this tutorial.

 

Through the Workbench, click:

Database > Connect to Database… select database > OK

Then Server > Users and Privileges >Select root%—set the password and confirm it

 

Now we need to set database parameters that are required to modify for the EEI Server database.

Set the open_files_limit parameter as described below. All the other parameters are set in the .cnf file (counterpart to my.ini in Windows).

Open the Terminal and execute the following command to copy mysql.service file:

sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/

Open the /etc/systemd/system/mysql.service in texteditor (or nano, pico, vi ...) and add following lines:

LimitNOFILE=30000

LimitMEMLOCK=30000

Save the file and reload system configuration by the following command:

sudo systemctl daemon-reload

Now you need to modify /etc/mysql/mysql.conf.d/mysqld.cnf file (where are the db params).

Open file in texteditor or nano, pico, vi ... and add following lines under section [mysqld]:

sudo /etc/mysql/mysql.conf.d/mysqld.cnf

lower_case_table_names = 1

open_files_limit = 30000

bind-address = IP_OF_THIS_MACHINE, BUT NO 127.0.0.1

thread_stack = 256K

*bind-address – default value is 127.0.0.1. You have to set this address to IP of the machine where MySQL is running. In case of incorrect IP, EEI Installation cannot connect to MySQL.

 

Insert following parameters to the part InnoDB

innodb_buffer_pool_size=4G

innodb_flush_log_at_trx_commit=0

innodb_log_file_size=2G

*innodb_buffer_pool_size—set to 80% of the RAM size of MySQL machine

*innodb_log_file_size —set to 40% - 60% of the innodb_buffer_pool_size value

 

Add to the end these lines

log_bin_trust_function_creators=1

event_scheduler = ON

 

Restart MySQL to load the new parameters:

sudo service mysql restart

 

Debugging Database parameters

To verify the database parameters, run MySQL via Terminal and execute query for particular parameter value:

mysql -u root -p

show variables like '%lower_case_table_names%';

Verify the status of MySQL Service

Open the terminal, and type in the command.

systemctl status mysql.service

MySQL Service runs when the reported state is: active (running).


Sources

MySQL Installation

https://www.cyberciti.biz/faq/howto-install-mysql-on-ubuntu-linux-16-04/

Warning: World-writable config file '/etc/mysql/my.cnf' is ignored

https://stackoverflow.com/questions/32133353/unable-to-connect-to-mysql-database-in-ubuntu

Open_files_limit

https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied

MySQL command line commands

https://dev.mysql.com/doc/refman/5.5/en/getting-information.html