Install phpMyAdmin with Apache on AWS Ubuntu 20.04
phpMyAdmin is a free and open-source web-based database management tool written in PHP. It provides a graphical web interface for users to manage MySQL or MariaDB database.
Download and Install phpMyAdmin on Ubuntu 20.04
sudo apt update
sudo apt install phpmyadmin
Notes: When it prompts you to select a web server to configure, hit the space bar to select apache2. (An asterisk indicates the item is selected). Then hit Tab key and Enter to confirm your choice.
In the next screen, select Yes to configure a database for phpMyAdmin with dbconfig-common. Then set a password for the phpmyadmin user in MariaDB/MySQL. This creates a DB named phpmyadmin with user phpmyadmin. You can check the previleges this user has by logging in with:
sudo mysql -u root
show grants for phpmyadmin@localhost;
exit;
Now run the following command to check if the /etc/apache2/conf-enabled/phpmyadmin.conf file exists.
file /etc/apache2/conf-enabled/phpmyadmin.conf
If there’s no error in the installation process, you should see the following command output.
/etc/apache2/conf-enabled/phpmyadmin.conf: symbolic link to ../conf-available/phpmyadmin.conf
If this file doesn’t exist on your server, it’s likely that you didn’t select Apache web server in the phpMyAdmin setup wizard. You can fix it with the following commands.
sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
sudo a2enconf phpmyadmin
sudo systemctl reload apache2
Again Now run the following command to check if the /etc/apache2/conf-enabled/phpmyadmin.conf file exists
file /etc/apache2/conf-enabled/phpmyadmin.conf
Accessing phpMyAdmin from a Sub-directory
example.com/phpmyadmin
If running on localhost:
localhost/phpmyadmin
If connection is refused, check your firewall or security group setting. If running on a local computer, open port 80 and 443:
sudo iptables -I INPUT -p tcp --dport 80 -j ACCEPT
sudo iptables -I INPUT -p tcp --dport 443 -j ACCEPT
If running UFW, do this:
sudo ufw allow 80,443/tcp
Access phpMyAdmin From a Sub-domain
Create a DNS record for the subdomain in Route 53
pma.example.com
Create 2 new VirtualHost files phpmyadmin.conf and phpmyadmin-le-ssl.conf:
First create phpmyadmin.conf with this command and add the info in the file below:
sudo nano /etc/apache2/sites-available/phpmyadmin.conf
### pma subdomain
<VirtualHost *:80>
ServerName pma.domain.com
DocumentRoot /usr/share/phpmyadmin
ErrorLog ${APACHE_LOG_DIR}/pma.error.log
CustomLog ${APACHE_LOG_DIR}/pma.access.log combined
RewriteEngine on
RewriteCond %{SERVER_NAME} =pma.domain.com
RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]
</VirtualHost>
# phpMyAdmin default Apache configuration
#Alias /phpmyadmin /usr/share/phpmyadmin
Options SymLinksIfOwnerMatch
DirectoryIndex index.php
# limit libapache2-mod-php to files and directories necessary by pma
php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tsudo cpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/:/usr/share/php/PhpMyAdmin/:/usr/share/php/Symfony/:/usr/share/php/Twig/:/usr/share/php/Twig-Extensions/:/usr/share/php/ReCaptcha/:/usr/share/php/Psr/Container/:/usr/share/php/Psr/Cache/:/usr/share/php/Psr/Log/:/usr/share/php/Psr/SimpleCache/
# Disallow web access to directories that don't need it
Require all denied
Require all denied
Save and close the file.
Next create phpmyadmin-le-ssl.conf and add the following information in the file:
sudo nano /etc/apache2/sites-available/phpmyadmin-le-ssl.conf
### PMA subdomain
<IfModule mod_ssl.c>
SSLStaplingCache shmcb:/var/run/apache2/stapling_cache(128000)
<VirtualHost *:443>
ServerName pma.domain.com
DocumentRoot /usr/share/phpmyadmin
ErrorLog ${APACHE_LOG_DIR}/pma.error.log
CustomLog ${APACHE_LOG_DIR}/pma.access.log combined
SSLCertificateFile /etc/letsencrypt/live/domain.com/fullchain.pem
SSLCertificateKeyFile /etc/letsencrypt/live/domain.com/privkey.pem
Include /etc/letsencrypt/options-ssl-apache.conf
Header always set Strict-Transport-Security "max-age=31536000"
SSLUseStapling on
Header always set Content-Security-Policy upgrade-insecure-requests
</VirtualHost>
</IfModule>
Save and close the file.
Enable this virtual host:
sudo a2ensite phpmyadmin.conf
sudo a2ensite phpmyadmin-le-ssl.conf
Reload Apache:
sudo systemctl reload apache2
You can now access the phpMyAdmin subdomain:
pma.example.com
Enable HTTPS on phpMyAdmin with Apache
Run the following command to install the Let’s Encrypt client (certbot) from Ubuntu 20.04 repository:
sudo apt install certbot python3-certbot-apache
Run the following command to obtain and install TLS certificate:
sudo certbot --apache --agree-tos --redirect --hsts --staple-ocsp --must-staple -d pma.example.com --email [email protected]
Troubleshoot phpMyAdmin Login Error
MariaDB root login might give you error on login. To fix that create another user with privileges:
Log into MariaDB server from the command line:
sudo mysql -u root
Create an admin user with password authentication:
create user admin@localhost identified by 'your-preferred-password';
Grant all privileges on all databases:
grant all privileges on *.* to admin@localhost with grant option;
Flush privileges and exit;
flush privileges;
exit;
or Set The Password For Root User
To change the root password, you have to shut down the database server beforehand.
sudo systemctl stop mysql
sudo systemctl stop mariadb
Now Start the database without loading the grant tables or enabling networking:
sudo mysqld_safe --skip-grant-tables --skip-networking &
Now Log into MariaDB server from the command line:
sudo mysql -u root
Now Let’s tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.
FLUSH PRIVILEGES;
Now we can actually change the root password.
For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Flush privileges and exit;
flush privileges;
exit;
You should now be able to log into phpMyAmin with the admin account and manage all databases.
Delete an User from MySQL
The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables.
DROP USER 'username'@'localhost';
