To work with databases through the web interface, there is a web editor phpMyAdmin. This tool connects to a MySQL/MariaDB database and displays existing databases, allows you to create new ones, edit data in tables. In general, everything is the same as what can be done in the mysql console, but much more convenient and clearer, especially when working with data frequently. Consider installing phpMyAdmin on Ubuntu 20.04 with LAMP preinstalled.
We make all settings from the root user, if you are using a regular user, add before the command sudo.
1. Install the necessary components and the database editor itself
apt install php-mbstring
apt install phpmyadmin
During the installation process, the user will be prompted to select for which web server the editor should be configured.
- [*]apache2
- []lighttpd
In our case, a server with LAMP pre-installed and we choose apache2.
Next, it will be proposed to create a database for the database editor itself, where the editor's user settings and other data necessary for its operation will be stored. Here we select the Yes option and in the next window you will be asked to come up with a password for the phpmyadmin user. We enter the password and confirm it in the next window to avoid input errors.
Restart the web service to apply our installation configuration:
/etc/init.d/apache2 restart
/etc/init.d/php7.4-fpm restart
2. Basic settings
Let's follow the link http://server-ip/phpmyadmin to check the work, we can enter the login and password from any database, for example, the newly created one, the phpmyadmin login and the password we set. Sometimes, for convenience, it is required to work with several databases at once, in order to display them under one user, we need to create one and delegate the rights of the root user.
mysql -u root -p
CREATE USER 'alldbuser'@'localhost' IDENTIFIED BY 'пароль';
GRANT ALL PRIVILEGES ON . TO 'alldbuser'@'localhost';
FLUSH PRIVILEGES;
Now, when authorizing under the alldbuser user, all databases created on this server are displayed.
3. Security setup
In order to protect the database editor from guessing passwords and potential vulnerabilities, let's add authorization at the level without a server.
nano /usr/share/phpmyadmin/.htaccess
AuthType Basic
AuthName "Restricted Files"
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user
Add a user and specify a password for it:
htpasswd -c /etc/phpmyadmin/.htpasswd webdb-userTo apply the settings at the web server level, you need to change the global settings in the /etc/apache2/apache2.conf file, the AllowOverride value was All, the settings block should look like this:
AllowOverride All
Require all granted
Restart the service, depending on your web server:
/etc/init.d/apache2 restart
/etc/init.d/php7.4-fpm restart
Now, when you click on the link http://server-ip/phpmyadmin, a window will appear for entering your login and password. In our case, the login will be webdb-user. You can also add a configuration so that password entry from unknown ip addresses is not even prompted, and the user receives a Forbidden message, add a line to the file and specify your static ip addresses:
nano /usr/share/phpmyadmin/.htaccess
AuthType Basic
AuthName "Restricted Files"
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user
Allow from 77.88.99.11 22.33.44.55
The setup is complete, we strongly recommend that you enter passwords only using the https protocol with an SSL certificate, in order to avoid data interception.