Skip to content

Climsoft Database Configuration on LINUX (MySQL or MariaDB Server)

Jump to bottom
Marcellin edited this page Mar 28, 2019 · 4 revisions

Prerequisite:

  • The Linux server should be connected to the internet to be able to download and install required packages;
  • The installation of packages is done with the sudo or root privilege (In my case I have used the root user to install and configure);
  • Though the Linux Ubuntu was used to produce this guide, the same procedure is applied to other type of Linux Operating Systems.
  • MySQL is almost similar to Mariadb server.

Case 1: Mysql - Server

Step 1. Install mysql-server by using the following command:

1.# apt update
2.#apt install mysql-server. MySQL should now be installed and running. To check if mysql is now up and running, use the command:
3.# systemctl status mysql. If the system is up and running, you will see a screen similar to the one below:
Climsoft Database Configuration on Linux

Step 2. Secure mysql (create the password for mysql root user) by using the following command and instructions below:

# mysql_secure_installation

  • This will ask you for the MySQL root password that you set during installation. Type it in and press ENTER. Now you'll answer a series of yes or no prompts. Let's go through them:
  • Next you'll be asked if you want to change the root password. Since we just created the password when we installed MySQL, we can safely skip this. Hit ENTER to continue without updating the password.
  • The rest of the prompts can be answered yes. You will be asked about removing the anonymous MySQL user, disallowing remote root login, removing the test database, and reloading privilege tables to ensure the previous changes take effect properly. These are all a good idea. Type Y and hit ENTER for each.

Step 3. Open mysql connection:

# mysql -u root –p

  • Run the climsoft database script in mysql environement to create both Climsoft operation and test database as follow:
    mysql> source /path (e.g.: source /home/wmo/Dowloads/mariadb_climsoft_db_v4_all).

Step 4. Grant the Global privilege to the user

  • Grant the global privileges (%) to the user to allow this user to be able to login remotely from any Client PC (Windows or Linux OS) as follow; In MySQL environment, type the 2 commands in bold:
    Mysql> GRANT ALL PRIVILEGES ON . TO '[user-name]'@'%' IDENTIFIED BY '[new-password]'; Mysql> FLUSH PRIVILEGES;

Step 5. Allow Remote Access Login into the Linux Server

To enable Remote access users from clients PC (running windows and having Climsoft installed), follow the step below to edit the Linux server network configuration file (see below example for the case of Ubuntu):

  • The Network configuration file (msqld) path to edit with sudo or root privilege [case of mysql]: Vi /etc/mysql/mysql.conf.d/mysqld.cnf
  • Comment the default bind-address = 127.0.0.1 in the configuration file shown in the path below (i.e. # bind-address = 127.0.0.1 and add a line of bind-address to the server IP address. (e.g.: bind-address = 192.168.1.100).
  • Save the file and quit editing mode;

Step 6. Test Connection via any of the database administration tool

  • Connect from the Client PC using any Database Administration tool (in my case, I have used HeidiSQL) to see if you can access remotely the server;
  • Login in Climsoft on the Client side by setting database connection to the server IP address, database and port number to test if the connection is well established.

Case 2: Mariadb - Server

Step 1. Install mariadb-server by using the following command:

1. # apt update
2. #apt install mariadb-server. Mariadb-server should now be installed and running. To check if mysql is now up and running, use the command:
3. # systemctl status mariadb. If the system is up and running, you will see a screen similar to the one below:
Climsoft Database Configuration on Linux

Step 2. Secure mariadb (create the password for mariadb root user) by using the following command and instructions below:

# mysql_secure_installation

  • This will ask you for the Mariadb root password that you set during installation. Type it in and press ENTER. Now you'll answer a series of yes or no prompts. Let's go through them:
  • Next you'll be asked if you want to change the root password. Since we just created the password when we installed Mariadb, we can safely skip this. Hit ENTER to continue without updating the password.
  • The rest of the prompts can be answered yes. You will be asked about removing the anonymous Mariadb user, disallowing remote root login, removing the test database, and reloading privilege tables to ensure the previous changes take effect properly. These are all a good idea. Type Y and hit ENTER for each.

Step 3. Open mariadb connection:

# mysql -u root –p

  • Run the climsoft database script in mysql environement to create both Climsoft operation and test database as follow:
    mariadb> source /path (e.g.: source /home/wmo/Dowloads/mariadb_climsoft_db_v4_all)

Step 4. Grant Global privilege to the user

Grant the global privileges (%) to the user to allow this user to be able to login remotely from any Client PC (Windows or Linux OS) as follow; In MySQL environment, type the 2 commands in bold:
Mariadb> GRANT ALL PRIVILEGES ON . TO '[user-name]'@'%' IDENTIFIED BY '[new-password]'; Mariadb> FLUSH PRIVILEGES;

Step 4. Allow Remote Access Login into the Linux Server

To enable Remote access users from clients PC (running windows and having Climsoft installed), follow the step below to edit the Linux server network configuration file (see below example for the case of Ubuntu):

  • Change the network configuration file as follow: Login with root or sudo privileges;
    Vi /etc/mysql/mariadb.conf.d/50-server.cnf (and edit the file as follow: Comment the default IP address and add the static IP address of the server. E.g.: # bind-address = 127.0.0.1 and add a line of bind-address to the server IP address. i.e.: bind-address = 192.168.1.100
  • Save and quit editing mode

Step 5. Test Connection via any of the database admin tool

  • Connect from the Client PC using any Database Administration tool (in my case, I have used HeidiSQL) to see if you can access remotely the server;

  • Login in Climsoft on the Client side by setting database connection to the server IP address, database and port number to test if the connection is well established.

Clone this wiki locally