Goal

Provide the necessary instructions to install MySQL required to use Vivo-i18n

Prerequisite

To have carried out the procedure 1) Starting with basic installation: Java-Maven-Solr-GIT

Procedure

Step

Action

1) Execute the following instruction sequence:

sudo apt update
sudo apt install mysql-server

2) Securing

choose a password for root
eliminate the anonymous user
allow remote root connection
deletion of the test board
refill of the privileges table

sudo mysql_secure_installation

3) Setting user authentication and privileges

change-root-password must contain the password defined in step 2

sudo mysql
SELECT user,authentication_string,plugin,host FROM mysql.user;

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *D9DEF051BA53BF5AC0AD114CB5A4DA80B38C83FE | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'change-root-password';
FLUSH PRIVILEGES;
exit

4) Testing Authentication

Mysql now requires you to enter a password for root

sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

5) create a VIVO user for local and remote access

change-root-password must contain the password for the vivo user

sudo mysql -u root -p
Enter password: 
mysql>
CREATE USER 'vivo_i18n'@'localhost' IDENTIFIED BY 'change-root-password';
CREATE USER 'vivo_i18n'@'%' IDENTIFIED BY 'change-root-password';

6) Create the DB needed for VIVO

CREATE DATABASE vivodb_i18n CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON vitrodb.* TO 'vivo_i18n'@'localhost';
GRANT ALL PRIVILEGES ON vitrodb.* TO 'vivo_i18n'@'%';

7) Confirm the installation

SELECT user,authentication_string,plugin,host FROM mysql.user;

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *711841F70B99BE5C909F7990576BF5864330C88E | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *1E7991B7DEC6D1826ECFF03586E179300A3B8BBE | mysql_native_password | localhost |
| vivo_i18n        | *711841F70B99BE5C909F7990576BF5864330C88E | mysql_native_password | localhost |
| vivo_i18n        | *711841F70B99BE5C909F7990576BF5864330C88E | mysql_native_password | %         |
+------------------+-------------------------------------------+-----------------------+-----------+
6 rows in set (0.00 sec)

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| vivodb_i18n        |
+--------------------+
5 rows in set (0.00 sec)