Install Mysql and Phpmyadmin

Installing MySQL to Manage Site Data
Now that you have a web server, you need to install MySQL (a database management system) to store and manage the data for your site.

Install MySQL by typing:

$ sudo apt install mysql-server

The MySQL database software is now installed, but its configuration is not yet complete.

To secure the installation, MySQL comes with a script that will ask whether we want to modify some insecure defaults. Initiate the script by typing:

$ sudo mysql_secure_installation

This script will ask if you want to configure the VALIDATE PASSWORD PLUGIN.

Answer Y for yes, or anything else to continue without enabling.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:
If you’ve enabled validation, the script will also ask you to select a level of password validation. Keep in mind that if you enter 2 – for the strongest level – you will receive errors when attempting to set any password which does not contain numbers, upper and lowercase letters, and special characters, or which is based on common dictionary words.

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Next, you’ll be asked to submit and confirm a root password:

Please set the password for root here.

New password:

Re-enter new password:
For the rest of the questions, you should press Y and hit the ENTER key at each prompt. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes we have made.

Note that in Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.
sudo
If using the auth_socket plugin to access MySQL fits with your workflow, you can proceed to Step 3. If, however, you prefer to use a password when connecting to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

$ sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

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

Outputsudo

+------------------+-------------------------------------------+-----------------------+-----------+
| 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 | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

For MySQL 5.7.6 and newer

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and older

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

If the ALTER USER command doesn’t work use:
# Update the password for the root user:

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('YourNewPasswordBuddy'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

#if you omit (AND Host=’localhost’) section, it updates the root pass regardless of its host

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

mysql> FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

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

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

4 rows in set (0.00 sec)
You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:

exit
Note: After configuring your root MySQL user to authenticate with a password, you’ll no longer be able to access MySQL with the sudo mysql command used previously. Instead, you must run the following:

mysql -u root -p

After entering the password you just set, you will see the MySQL prompt.

At this point, your database system is now set up and you can move on to installing PHPmyadmin.

Change MySQL Password Policy

Login to MySQL command prompt and execute below query to view current settings of validate_password.

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

7 rows in set (0.06 sec)

The default level is MEDIUM, we can change it to LOW by using the below query. The LOW level required only password’s length to min 8 characters.

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.02 sec)

Install Phpmyadmin

$ sudo apt install phpmyadmin php-mbstring php-gettext

Now, select the web server that should be automatically configured to run phpMyAdmin. Choose the web server with UP/DOWN arrows and hit the SPACEBAR key. Once you chose the web server, you will see a * (star) symbol in-front of it. Hit the TAB key to choose OK and again hit ENTER key to continue.

Hit ENTER to continue.

Choose Yes to configure database for phpmyadmin:

Provide mysql application password phpmyadmin.

Edit Nginx to enable http://localhost/phpmyadmin

sudo nano /etc/nginx/sites-available/default

location /phpmyadmin {
 root /usr/share/;

 # if phpmyadmin is in /var/www/html directory use below
 # root /var/www/html/;
 
  index index.php;
  try_files $uri $uri/ =404;

 location ~ ^/phpmyadmin/(doc|sql|setup)/ {
  deny all;
 }

 location ~ /phpmyadmin/(.+\.php)$ {
  fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;
  fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
  include fastcgi_params;
  include snippets/fastcgi-php.conf;
 }
}

You can see from this output that the root user will authenticate using a password. You can now log in to the phpMyAdmin interface as your root user with the password you’ve set for it here.

Get phpmyadmin config user and password for /usr/share/phpmyadmin

sudo nano /etc/phpmyadmin/config-db.php

cd /var/www/html/
sudo wget https://files.phpmyadmin.net/phpMyAdmin/4.9.0.1/phpMyAdmin-4.9.0.1-all-languages.zip
sudo unzip phpMyAdmin-4.9.0.1-all-languages.zip
sudo mv phpMyAdmin-4.9.0.1-all-languages/ phpmyadmin/
sudo mkdir -m 777 phpmyadmin/config/
sudo /etc/init.d/apache2 restart

Known Error on custom Phpmyadmin setup

P. The $cfg[‘TempDir’] (./tmp/) is not accessible. phpMyAdmin is not able to cache templates and will be slow because of this.

$ sudo mkdir -m 777 /var/www/html/phpmyadmin/tmp

P. The configuration file now needs a secret passphrase (blowfish_secret).