How To Install and Secure phpMyAdmin on Ubuntu 18.04 LTS
This tutorial exists for these OS versions
- Ubuntu 24.04 (Noble Numbat)
- Ubuntu 22.04 (Jammy Jellyfish)
- Ubuntu 18.04 (Bionic Beaver)
On this page
phpMyAdmin is a free and open source administration tool for MySQL and MariaDB. phpMyAdmin is a web-based tool that allows you to easily manage MySQL or MariaDB databases. You can perform administration tasks such as creating, editing, or deleting databases, import and export database backups, run searches and managing users and permissions using phpMyAdmin.
Features
- Multi-language support.
- Import data from CSV and SQL.
- Provide live charts to monitor MySQL server activity like connections, processes, CPU/memory usage, etc.
- Searching globally in a database or a subset of it.
- Simple and easy to use web interface.
- Export data to various formats like, CSV, SQL, XML, PDF, Word, Excel, LaTeX, and others.
In this tutorial, we will be going to explain how to install and secure phpMyAdmin on Ubuntu 18.04 server.
Requirements
- A server running Ubuntu 18.04.
- A non-root user with sudo privileges.
Install phpMyAdmin
Before starting, you will need to install Apache and MySQL to your server. You can install it with the following command:
sudo apt-get install apache2 mysql-server -y
By default, phpMyAdmin is not available in the Ubuntu 18.04 default repository. So, you will need to add the third party PHP repository to your system.
You can add it by just running the following command:
sudo apt-get install software-properties-common -y
sudo add-apt-repository ppa:ondrej/php
Once the repository is added, install phpMyAdmin by running the following command:
sudo apt-get install phpmyadmin php-mbstring php-gettext -y
During the installation, you will be asked to choose the web server as shown below:
Choose Apache and click on OK button. You will be redirected to the following page:
Now, click on Yes button. You should see the following page:
Here, provide your MySQL application password for phpMyAdmin and click on the OK button.
Once the phpMyAdmin has been installed, enable the mbstring PHP extension with the following command:
sudo phpenmod mbstring
Next, restart Apache service to apply the changes:
sudo systemctl restart apache2
Configure User Authentication
phpMyAdmin automatically created a database user called phpmyadmin with the administrative password you set during the installation. But, it is recommended to create a separate user for managing databases through the phpMyAdmin web interface. In the latest version of MariaDB, the root MySQL user is set to authenticate using the auth_socket plugin by default. So, if you want to log in to phpMyAdmin as your root MySQL user, you will need to switch its authentication method from auth_socket to mysql_native_password.
First, log in to MySQL shell with the following command:
sudo mysql
Next, check the authentication method of MySQL user with the following command:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Output:
+------------------+-------------------------------------------+-----------------------+-----------+ | 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 | *AC18DF5CAE34BF4796EF975702F038A566B48B42 | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ 4 rows in set (0.01 sec)
In the above output, you should see that root user uses auth_socket plugin.
You can configure the root account to authenticate with a password by running the following command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Next, flush the privileges with the following command:
mysql> FLUSH PRIVILEGES;
Now, check the authentication methods by running the following command:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Output:
+------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | mysql_native_password | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | debian-sys-maint | *AC18DF5CAE34BF4796EF975702F038A566B48B42 | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ 4 rows in set (0.01 sec)
Next, create a separate user with name phpmyadminuser to connect to phpMyAdmin.
First, log in to MySQL shell:
mysql -u root -p
Enter your root password, then create a user with the following command:
MariaDB [(none)]> CREATE USER 'phpmyadminuser'@'localhost' IDENTIFIED BY 'password';
Next, grant appropriate privileges to phpmyadminuser with the following command:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'phpmyadminuser'@'localhost' WITH GRANT OPTION;
Finally, exit from the MySQL shell with the following command:
MariaDB [(none)]> exit;
Now, open your web browser and type the URL http://your-server-ip/phpmyadmin. You will be redirected to the following page:
Here, provide your username and password. Then, click on the Go button. You should see the PhpMyAdmin dashboard in the following page:
Secure PhpMyAdmin
phpMyAdmin is now installed and configured. But, it is recommended to secure your phpMyAdmin instance to prevent unauthorized access You can secure phpMyAdmin by using Apache's built-in .htaccess authentication and authorization functionalities.
To do so, first enable the use of .htaccess file overrides by editing phpmyadmin.conf file:
sudo nano /etc/apache2/conf-available/phpmyadmin.conf
Make the following changes:
<Directory /usr/share/phpmyadmin> Options SymLinksIfOwnerMatch DirectoryIndex index.php AllowOverride All
Save and close the file. Then, restart Apache service to apply the chnages:
sudo systemctl restart apache2
Next, create a .htaccess file inside phpmyadmin directory:
sudo nano /usr/share/phpmyadmin/.htaccess
Add the following lines:
AuthType Basic AuthName "Restricted Files" AuthUserFile /etc/phpmyadmin/.htpasswd Require valid-user
Save and close the file. Then, create a user named admin with the htpasswd utility:
sudo htpasswd -c /etc/phpmyadmin/.htpasswd admin
Output:
New password: Re-type new password: Adding password for user admin
Your phpMyAdmin instance is now secured with an additional layer of authentication.
Now, open your web browser and type the URL http://your-server-ip/phpmyadmin. You will be redirected to the additional account name and password that you just configured as shown below:
Now, provide your username and the password and click on the Log In button. You should be redirected to the regular phpMyAdmin authentication page.