phpMyAdmin is a free, open source and web interface based database management tool for MySQL and MariaDB. It's PHP based application to interact with MySQL and MariaDB easily. Using phpMyAdmin you can manage MySQL databases, user accounts and privileges, execute SQL-statements, import and export data in a variety of data formats and much more.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related phpMyAdmin queries.
In this context, we shall look into the steps to install phpMyAdmin with Apache on Ubuntu 18.04 server.
How to Install phpMyAdmin with Apache on Ubuntu ?
Before proceeding with this Installation Procedure, ensure that the following Prerequisites are met:
You should logged in on Ubuntu server as non-root user account with sudo privileges.
Ensure that LAMP (Linux, Apache, MySQL and PHP) stack is installed on your Ubuntu machine.
For improved Security, it is recommended to access your phpMyAdmin installation over HTTPS connections. Therefore, it will prevent from unnecessary attacks to phpmyadmin. If your domain is not secure with an SSL/TLS certificate, you can follow this guide to Secure Apache with Let's Encrypt on Ubuntu.
To start installation you need to update Ubuntu package index and upgrade system by using the following command:
$ sudo apt update
$ sudo apt upgrade
Next, install the phpMyAdmin package from the default Ubuntu repositories with the below command:
$ sudo apt install phpmyadmin php-mbstring php-gettext
You will be prompted for few configuration options.
At first, it will ask to choose web server.
Select Apache by click on OK button.
Next, you will be ask to use dbconfig-common to configure the database for phpMyAdmin.
Select Yes and hit Enter key to continue.
Now enter password for phpMyAdmin to register with the database, select OK and press Enter.
Again, you will be prompted to confirm the password by entering same password and hit on OK button.
Next, you need to enable php-mbstring extension by executing below command:
$ sudo phpenmod php-mbstring
After that, you need to restart Apache to take effect of your configuration changes by typing:
$ sudo systemctl restart apache2
How to Create MySQL User and Set Privileges ?
In Ubuntu systems, the root MySQL user is using the auth_socket plugin by default to authenticate. It means that you can't authenticate as a root by providing a password. This will increase some greater security and usability in many cases, but it can also complicate when you need to allow an external program like phpMyAdmin.
In order 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.
It will not be good to change authentication method for the MySQL root user.
Since phpMyAdmin requires users to authenticate with a password, so instead of changing authentication method we will create a new administrative MySQL account in order to access the interface.
This user will have the same privileges as the root user and will be set to use the mysql_native_password authentication method.
Now, we will use that user to login to phpMyAdmin and do further administrative tasks on our MySQL or MariaDB server.
You can login by below command:
$ sudo mysql
Next, create a new administrative user with strong password and grant appropriate permissions by typing:
mysql> CREATE USER 'newadmin'@'localhost' IDENTIFIED BY 'STRONG-PASSWORD';
mysql> GRANT ALL PRIVILEGES ON . TO 'newadmin'@'localhost' WITH GRANT OPTION;
How to Access phpMyAdmin ?
To access the phpMyAdmin interface open web browser and type your server's public IP address or domain name followed by /phpmyadmin:
Enter the administrative account username and password which you created on previous step and hit on Go button.
After successful log in, you’ll see the phpMyAdmin user interface.
How to Secure phpMyAdmin ?
Generally, phpMyAdmin is a popular target for attackers, so we can add extra security layer to make phpMyAdmin directory password protected by setting up a basic authentication.
At First, we will create a password file with user using the htpasswd tool that comes with the Apache package. We will store the .htpasswd file in /etc/phpmyadmin directory:
$ sudo htpasswd -c /etc/phpmyadmin/.htpasswd newadmin
Here, newadmin is administrative username which we created for access phpMyAdmin interface. You can choose any user name of your choice. Once you execute above command it will prompt you to enter password and confirm password as below:
Re-type new password:
Adding password for user newadmin
Now we will configure Apache to password protect the phpMyAdmin directory and use the .htpasswd file.
To do it open phpmyadmin.conf by typing :
$ sudo nano /etc/apache2/conf-available/phpmyadmin.conf
Make changes as following:
Options +FollowSymLinks +Multiviews +Indexes # change this line
# Add new line start
AuthName "Authentication Required"
# Add new line end
After that, Save and close the file.
To take effect restart the Apache service by type:
$ sudo systemctl restart apache2
Now, when you access your phpMyAdmin subdirectory, you will be prompt for the additional account name and password that you just configured:
Once you will enter basic authentication details then only you will be taken to the phpMyAdmin login page where you need to enter your MySQL administrative user login credentials.
[Need urgent assistance in fixing phpMyAdmin errors? We can help you. ]