How to Install MySQL and phpMyAdmin on Ubuntu 18.04 with NGINX Web Server

Welcome in today’s event loop on {{ PLP }}, previously we’ve learned about on how to display the famous Hello World phrase using Django template.  So, for today, this topic will be in preparation for MySQL database and setting up the phpMyAdmin.

Introduction

The MySQL database is the most important storage for any information that we’re about to keep, retrieve, delete and update using our web application, also the phpMyAdmin is the web interface for the MySQL database to securely manage over the browsers.

So, prepare your self once again and stay focus because this would be exciting and fun learning with {{ PLP }}.

Getting Started

This is in preparation for the CRUD operations which means “CREATE, READ, UPDATE, and DELETE” using Django framework in Python.

We’ll be using the MySQL Database for this lesson and set up the web-based interface for MySQL which is called phpMyAdmin.

Step 1:  Install MySQL Database.

First of all, always update your UBUNTU Linux distribution and execute this command.

1
sudo apt update

And then install the MySQL default package by executing the command below.

1
sudo apt install mysql-server

If prompt with “Y/n“, type “Y” and press ENTER to continue the installation.

Now, we need to secure the newly installed MySQL database and execute this command.  The series of prompt again with “Y/n” and type “Y” and press ENTER.

1
sudo mysql_secure_installation

The first prompt would be to choose which type of password validation policy, choose the number “2” which means the strongest password policy and press ENTER.

1
2
3
4
5
6
7
8
9
Press y|Y for Yes, any other key for No: Y

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     e

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

And then the 2nd prompt to enter the root password for your MySQL database, type the password, usually, you don’t see the characters you’re typing in, press ENTER and then confirm your password and press ENTER again.

1
2
3
4
5
6
New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes

Next step, remove the anonymous user by choosing “Y” and press ENTER to remove it.

1
2
3
4
5
6
7
8
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

And then disable the “root” user to access remotely, remove the “test” database, and reload the privileges tables as well.  So, now it’s done.

Now, we need to test the MySQL database if it’s running from our web server.  Execute this command below.

1
systemctl status mysql.service

Then the expected result would look like this, and it should run automatically even though when the web server restarted and it works!

1
2
3
4
5
6
7
8
9
10
11
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Thu 2018-12-06 01:16:09 UTC; 1h 45min ago
 Main PID: 17782 (mysqld)
    Tasks: 29 (limit: 1111)
   CGroup: /system.slice/mysql.service
           └─17782 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pi

Dec 06 01:16:09 localhost systemd[1]: Starting MySQL Community Server...
Dec 06 01:16:09 localhost systemd[1]: Started MySQL Community Server.
lines 1-10/10 (END)

To exit the MySQL service status display, press CTRL + C.  In any event that the MySQL service is not running, execute this command.

1
sudo systemctl start mysql

By default, the “root” MySQL database user is “auth_socket” authentication method, we need to change this to “mysql_native_password” so that we can use this to login as a normal user account with a username and password in phpMyAdmin web interface.

1
sudo mysql

And then execute this MySQL command to check the current authentication method for all the MySQL users.

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

The output shows the list of all MySQL users with authentication method.

1
2
3
4
5
6
7
8
9
10
+------------------+-------------------------------------------+---------------+
| user             | authentication_string                     | plugin        |
+------------------+-------------------------------------------+---------------+
| root             |                                           | auth_socket   |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_p|
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_p|
| debian-sys-maint | *AC90771C948C979BF52283DA1DFF3A090B5B397E | mysql_native_p|
| phpmyadmin       | *36352FA9FD062FC20C28E0D8E344D46B189AAECC | mysql_native_p|
+------------------+-------------------------------------------+---------------+
5 rows in set (0.00 sec)

Afterward, this will change the “root” MySQL user from “auth_socket” to “mysql_native_password“.  So, execute this MySQL command.

1
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';

The output would be.

1
Query OK, 0 rows affected (0.00 sec)

And then we need to reload the MySQL user privileges.

1
mysql>  FLUSH PRIVILEGES;

Re-query again the MySQL users authentication methods.

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

Now, the “root” user authentication method is finally the “mysql_native_password“.

1
2
3
4
5
6
7
8
9
10
+------------------+-------------------------------------------+---------------+
| user             | authentication_string                     | plugin        |
+------------------+-------------------------------------------+---------------+
| root             | *36352FA9FD062FC20C28E0D8E344D46B189AAECC | mysql_native_p|
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_p|
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_p|
| debian-sys-maint | *AC90771C948C979BF52283DA1DFF3A090B5B397E | mysql_native_p|
| phpmyadmin       | *36352FA9FD062FC20C28E0D8E344D46B189AAECC | mysql_native_p|
+------------------+-------------------------------------------+---------------+
5 rows in set (0.00 sec)

Exit the MySQL environment.

1
mysql>  exit

The MySQL Database is now UP and Running from our UBUNTU 18.04 with NGINX web server.

Step 2:  Install phpMyAdmin.

We need a web-based interface for our MySQL database to access it securely via the web browsers and manage it accordingly.  So, let’s execute this command to start installing the phpMyAdmin.

1
2
sudo apt install phpmyadmin php-mbstring php-gettext
sudo apt-get install php7.2-cli php7.2-fpm php7.2-curl php7.2-gd php7.2-mysql php7.2-mbstring zip unzip

If prompt with “Y/n“, press “Y” and press ENTER, then it will prompt you to select which web server to configure automatically for you.  By default, it’s the “apache2” and press ENTER to proceed.

Now, enter the “MySQL application password for phpmyadmin:” and then press ENTER again.

The next thing we need to do is to enable explicitly the PHP module called “mbstring” as phpMyAdmin requires this to be enabled.

1
sudo phpenmod mbstring

And then we need to restart the apache2 web service to load the changes.

1
sudo systemctl restart apache2

But, if restarting the apache2 is not successful and instead give you this warning information.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
apache2.service - The Apache HTTP Server
   Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset:
  Drop-In: /lib/systemd/system/apache2.service.d
           └─apache2-systemd.conf
   Active: failed (Result: exit-code) since Thu 2018-12-06 09:04:14 UTC; 8min ag
  Process: 3925 ExecStart=/usr/sbin/apachectl start (code=exited, status=1/FAILU

Dec 06 09:04:14 localhost apachectl[3925]: AH00558: apache2: Could not reliably
Dec 06 09:04:14 localhost apachectl[3925]: (98)Address already in use: AH00072:
Dec 06 09:04:14 localhost apachectl[3925]: (98)Address already in use: AH00072:
Dec 06 09:04:14 localhost apachectl[3925]: no listening sockets available, shutt
Dec 06 09:04:14 localhost apachectl[3925]: AH00015: Unable to open logs
Dec 06 09:04:14 localhost apachectl[3925]: Action 'start' failed.
Dec 06 09:04:14 localhost apachectl[3925]: The Apache error log may have more in
Dec 06 09:04:14 localhost systemd[1]: apache2.service: Control process exited, c
Dec 06 09:04:14 localhost systemd[1]: apache2.service: Failed with result 'exit-
Dec 06 09:04:14 localhost systemd[1]: Failed to start The Apache HTTP Server.

The reason why we’re receiving this error is because, the port 80 is actually use by the HTTP  protocol by default to serve any HTTP requests by the web clients like the users requesting for an HTML page and things a like.

So, in this case, we need to change the port used by apache2 which is the port 80 as well and definitely is a conflicting port obviously and it’s not allowed.

Please go to “/etc/apache2” and download this apache2 folder to your local drive and modify the file called “/etc/apache2/ports.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# If you just change the port or add more ports here, you will likely also
# have to change the VirtualHost statement in
# /etc/apache2/sites-enabled/000-default.conf

Listen 8079

<IfModule ssl_module>
  Listen 443
</IfModule>

<IfModule mod_gnutls.c>
  Listen 443
</IfModule>

# vim: syntax=apache ts=4 sw=4 sts=4 sr noet

Then change the “Listen 80” to “Listen 8079” but it’s up to you which port number you’re assigning for your apache2 web service to run.  The port number range would be from 0-65535.

Afterwards, CREATE a new file called “dev.pinoylearnpython.conf” under “/etc/apache2/sites-available“.  Because, we need to set up a Virtual Host and make it sure that the <VirtualHost *:8079> as well instead of port 80.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<VirtualHost *:8079>
    ServerAdmin support@pinoylearnpython.com
    ServerName dev.pinoylearnpython.com
    ServerAlias dev.pinoylearnpython.com
    DocumentRoot /var/www/html/dev.pinoylearnpython.com/public_html
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

    <Directory /var/www/html/dev.pinoylearnpython.com/public_html>
        Options Indexes FollowSymLinks
        AllowOverride All
        Order allow,deny
        allow from all
        Require all granted
    </Directory>
</VirtualHost>

Then change other necessary config values for “ServerAdmin, ServerName, ServerAlias, DocumentRoot, and the <Directory path>.”  

Upload these 2 files after all the changes and absolutely we can now restart the apache2 web service for the changes we’ve just made.

1
sudo systemctl restart apache2

Afterward, you should NOT be able to see the errors again and it’s just return nothing only and that’s good enough, phpMyadmin is now installed.

Then reload the apache2 by executing this command.

1
sudo systemctl reload apache2

Next, disable the default “000-default.conf“.

1
sudo a2dissite 000-default.conf

And then reload the apache2 one more time for the changes to take effect.

1
sudo systemctl reload apache2

Test the apache2 configuration by executing this command.

1
sudo apache2ctl configtest

The expected result should be “Syntax OK“.

1
2
AH00558: apache2: Could not reliably determine the server's fully qualified dome
Syntax OK

Finally, restart your apache2 one more time.

1
sudo systemctl restart apache2

That’s all about installing the phpMyAdmin, it’s done, awesome!.

Step 3:  Install Other Important PHP 7.2 Modules

We need to used the PHP 7.2 version the latest as of this writing and other important PHP 7.2 modules that really important for phpMyAdmin to run properly.

1
sudo apt-get install php7.2-cli php7.2-fpm php7.2-curl php7.2-gd php7.2-mysql php7.2-mbstring zip unzip

It should be smooth installations and then we need to check the current version of PHP just to verify it.

1
php -v

The expected result would be PHP 7.2 which is correct.

1
2
3
4
PHP 7.2.10-0ubuntu0.18.04.1 (cli) (built: Sep 13 2018 13:45:02) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.10-0ubuntu0.18.04.1, Copyright (c) 1999-2018, by Zens

Let’s check the PHP7.2-FPM if it’s running as well.

1
sudo service php7.2-fpm status

The output would be like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
PHP 7.2.10-0ubuntu0.18.04.1 (cli) (built: Sep 13 2018 13:45:02) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.10-0ubuntu0.18.04.1, Copyright (c) 1999-2018, by Zens
root@localhost:~/dev# sudo service php7.2-fpm status
● php7.2-fpm.service - The PHP 7.2 FastCGI Process Manager
   Loaded: loaded (/lib/systemd/system/php7.2-fpm.service; enabled; vendor prese
   Active: active (running) since Sat 2018-12-08 04:32:32 UTC; 3h 28min ago
     Docs: man:php-fpm7.2(8)
 Main PID: 6069 (php-fpm7.2)
   Status: "Processes active: 0, idle: 2, Requests: 40, slow: 0, Traffic: 0req/s
    Tasks: 3 (limit: 1111)
   CGroup: /system.slice/php7.2-fpm.service
           ├─6069 php-fpm: master process (/etc/php/7.2/fpm/php-fpm.conf)
           ├─6086 php-fpm: pool www
           └─6087 php-fpm: pool www

Dec 08 04:32:32 localhost systemd[1]: Starting The PHP 7.2 FastCGI Process Manag
Dec 08 04:32:32 localhost systemd[1]: Started The PHP 7.2 FastCGI Process Manage

Just in case the PHP7.2-FPM is not running, then just execute this command.

1
sudo service php7.2-fpm start

That’s it, all good to go for us.

Step 4:  Create a Symbolic Link for phpMyAdmin.

As you can see that the phpMyAdmin web interface automatically uploaded the web interface files under “/usr/share/phpmyadmin“, that’s great!.  To create a symbolic link, execute this command.

1
sudo ln -s /usr/share/phpmyadmin /var/www/html/dev.pinoylearnpython.com/public_html/phpmyadmin

In your case, replace the line after “var/www/html/” with your own path.

Step 5:  INSERT new location from your NGINX file from “/etc/nginx/sites-available/dev.pinoylearnpython.com”

We need to add new location means a new web address for phpMyAdmin so that we can access it over the browsers securely.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
server {

  listen 80;
  listen [::]:80;

  server_name dev.pinoylearnpython.com;

  root /var/www/html/dev.pinoylearnpython.com/public_html;
  index index.php index.html index.htm;

  location ~ \.php$ {
    include snippets/fastcgi-php.conf;
    include fastcgi_params;
    fastcgi_pass unix:/run/php/php7.2-fpm.sock;
    fastcgi_param SCRIPT_FILENAME /var/www/html/dev.pinoylearnpython.com/public_html$fastcgi_script_name;
  }

  # For connection time out issue
  # Ref: http://nginx.org/en/docs/http/ngx_http_proxy_module.html#proxy_connect_timeout
  location / {
    proxy_http_version 1.1;
    proxy_set_header Connection "";
    proxy_pass http://172.104.190.249:8000;
  }

  location /static/ {
    alias /var/www/html/dev.pinoylearnpython.com/public_html/static/;
  }
 
  location /phpmyadmin/ {
    alias /var/www/html/dev.pinoylearnpython.com/public_html/phpmyadmin/;
  }

  location /robots.txt {
    alias /var/www/html/dev.pinoylearnpython.com/public_html/static/robots.txt;
    access_log        off;
    log_not_found     off;
  }

  location /googleb75a09842fe5c750.html {
    alias /var/www/html/dev.pinoylearnpython.com/public_html/static/googleb75a09842fe5c750.html;
    access_log        off;
    log_not_found     off;
  }

  location ~*  \.(jpg|jpeg|png|gif|ico|css|js|pdf|ttf|ttc|otf|eot|woff|woff2)$ {
    expires 7d;
  }

  # this prevents hidden files (beginning with a period) from being served
  location ~ /\. {
    access_log        off;
    log_not_found     off;
    deny              all;
  }
}

This is the specific line that we’ve just added.

1
2
3
location /phpmyadmin/ {
    alias /var/www/html/dev.pinoylearnpython.com/public_html/phpmyadmin/;
  }

Upload the modified NGINX conf file then reload your NGINX for the new changes to take effect.

1
sudo systemctl reload nginx

Finally, we can access the newly configured phpMyAdmin at https://dev.pinoylearnpython.com/phpmyadmin/

In the next event loop on {{ PLP }}.

Congratulations!, if you can see the phpMyAdmin web interface and able to log in successfully, I’m happy for you and you’ve learned a lot in our today’s event loop.

For those who’re not able to successfully launch your phpMyAdmin web interface, don’t worry, leave a comment below and I’m happy to help you to succeed.

Up next, we will be going to do the CRUD (Create, Read, Update and Delete) operations involving the MySQL database.

See you in the next event loop on the Basic Django CRUD using AJAX, JSON, and JQuery – Monolithic Architecture.

That’s all, have fun learning with {{ PLP }}.

To help Filipino students to learn Python programming language with Django to enhance their capabilities in developing robust web-based applications with practical and direct to the point tutorials, step-by-step with actual information that I provided for you. Leave a comment below or email me at [email protected], thank you!