PhpMyAdmin – PHP-Scripts To Manage MariaDB/MySQL

PhpMyAdmin – PHP-Scripts To Manage MariaDB/MySQL

By |

Last Updated on 2025-02-04 14:59 by Sture

Description:

phpMyAdmin is a free software tool developed in PHP designed to manage MySQL administration over the web. It supports a wide range of operations on MySQL and MariaDB. Common tasks such as managing databases, tables, columns, relationships, indexes, users, permissions, and more can be performed through the user interface. Additionally, you can execute any SQL statement directly.
WWW: https://www.phpmyadmin.net/.

Requirements

The following application(s) must be installed, configured, and running before phpMyadmin is installed:

  1. Apache – Enable Hypertext Transfer Protocol Secure (HTTPS)
  2. Apache – PHP Scripting Language (8.4.X Branch)
  3. Apache – PHP-FPM (FastCGI Process Manager)
  4. MariaDB – Multithreaded SQL Database Server/Client

Check the version of PHP installed with:

user@freebsdsrv:~ $ php -v [enter]
PHP 8.3.15 (cli) (built: Jan 30 2025 02:19:13) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.3.15, Copyright (c) Zend Technologies
    with Zend OPcache v8.3.15, Copyright (c), by Zend Technologies
user@freebsdsrv:~ $

In this example, PHP version 8.3.15 is installed.

Download phpMyAdmin Code

Download the latest version of the phpMyAdmin code. with:

user@freebsdsrv:~ $ fetch https://files.phpmyadmin.net/phpMyAdmin/5.2.2/phpMyAdmin-5.2.2-all-languages.tar.gz [enter]
phpMyAdmin-5.2.2-all-languages.tar.gz                   13 MB   27 MBps    01s
user@freebsdsrv:~ $

Extract the phpMyAdmin code

user@freebsdsrv:~ $ sudo tar zxvf phpMyAdmin-5.2.2-all-languages.tar.gz -C /usr/local/www/ [enter]
x phpMyAdmin-5.2.2-all-languages/
x phpMyAdmin-5.2.2-all-languages/.rtlcssrc.json
x phpMyAdmin-5.2.2-all-languages/CONTRIBUTING.md
x phpMyAdmin-5.2.2-all-languages/ChangeLog
x phpMyAdmin-5.2.2-all-languages/LICENSE
x phpMyAdmin-5.2.2-all-languages/README
x phpMyAdmin-5.2.2-all-languages/RELEASE-DATE-5.2.2

...
x phpMyAdmin-5.2.2-all-languages/vendor/williamdes/mariadb-mysql-kbs/src/Search.php
x phpMyAdmin-5.2.2-all-languages/vendor/williamdes/mariadb-mysql-kbs/src/SlimData.php
x phpMyAdmin-5.2.2-all-languages/yarn.lock
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo mv /usr/local/www/phpMyAdmin-5.2.2-all-languages/ /usr/local/www/phpMyAdmin [enter] 
user@freebsdsrv:~ $

Configuration

Display the MariaDB sock with:

user@freebsdsrv:~ $ sudo sockstat | grep "mariadb" [enter]
mysql    mariadbd    3620 20  tcp4   127.0.0.1:3306        *:*
mysql    mariadbd    3620 21  stream /var/run/mysql/mysql.sock
user@freebsdsrv:~ $

Create a copy of file /usr/local/www/phpMyAdmin/config.sample.inc.php to /usr/local/www/phpMyAdmin/config.inc.php with:

user@freebsdsrv:~ $ sudo cp /usr/local/www/phpMyAdmin/config.sample.inc.php /usr/local/www/phpMyAdmin/config.inc.php ; ls -l /usr/local/www/phpMyAdmin/config* [enter]
-rw-r--r--  1 root wheel 4810 Jan 31 23:57 /usr/local/www/phpMyAdmin/config.inc.php
-rw-r--r--  1 root wheel 4810 Jan 21 04:15 /usr/local/www/phpMyAdmin/config.sample.inc.php
user@freebsdsrv:~ $

Create a Blowfish secret required for cookie-based authentication to encrypt passwords in cookies. The secret must be a 32-byte long string of random bytes. Generate the Blowfish secret with (Example; DO NOT USE THIS CODE!):

user@freebsdsrv:~ $ sudo openssl rand -hex 16 [enter]
f0ae3f22ccd506ec5430f3ac6fde0d63
user@freebsdsrv:~ $

Edit file /usr/local/www/phpMyAdmin/config.inc.php with:

user@freebsdsrv:~ $ sudo ee /usr/local/www/phpMyAdmin/config.inc.php [enter]

Add 32 random characters to $cfg[‘blowfish_secret’] to encrypt passwords in cookies as in this example:

$cfg['blowfish_secret'] = 'f0ae3f22ccd506ec5430f3ac6fde0d63'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

..and then add $cfg[‘Servers’][$i][‘socket’] = ‘/var/run/mysql/mysql.sock’; as in this example:

...
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['socket'] = '/var/run/mysql/mysql.sock';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;
...

Create a UploadDir directory where SQL files can be uploaded by other means than phpMyAdmin (for example, FTP) and then set permissions with:

user@freebsdsrv:~ $ sudo mkdir /usr/local/www/phpMyAdmin/UploadDir; sudo chmod -vv 777 /usr/local/www/phpMyAdmin/UploadDir [enter]
/usr/local/www/phpMyAdmin/UploadDir: 040755 [drwxr-xr-x ] -> 040777 [drwxrwxrwx ]
user@freebsdsrv:~ $

Create a SaveDir webserver directory where exported files can be saved and then set permissions with:

user@freebsdsrv:~ $ sudo mkdir /usr/local/www/phpMyAdmin/SaveDir ; sudo chmod -vv 777 /usr/local/www/phpMyAdmin/SaveDir [enter]
/usr/local/www/phpMyAdmin/SaveDir: 040755 [drwxr-xr-x ] -> 040777 [drwxrwxrwx ]
user@freebsdsrv:~ $

Create a tmp for template caching with:

user@freebsdsrv:~ $ sudo mkdir /usr/local/www/phpMyAdmin/tmp ; sudo chmod -vv 777 /usr/local/www/phpMyAdmin/tmp [enter]
/usr/local/www/phpMyAdmin/tmp: 040755 [drwxr-xr-x ] -> 040777 [drwxrwxrwx ]
user@freebsdsrv:~ $

Create an Apache Include file to make phpMyAdmin available on the website with:

user@freebsdsrv:~ $ sudo ee /usr/local/etc/apache24/Includes/phpMyAdmin.conf [enter]

Add the following text;

Alias /phpmyadmin/ "/usr/local/www/phpMyAdmin/"
<Directory "/usr/local/www/phpMyAdmin/">
    DirectoryIndex index.php
    Options None
    AllowOverride Limit
    # range of access allowed
    Require ip 127.0.0.1 192.168.1.0/24
</Directory>

N.B.: Access is only allowed from computers connected to the network 192.168.1.0/24, the same network as this FreeBSD server!
The mbstring and mysqli extension is required by phpMyAdmin. Install php83-mbstring and php83-mysqli with:

user@freebsdsrv:~ $ sudo pkg install -y php83-mbstring php83-mysqli [enter]
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 3 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	oniguruma: 6.9.10
	php83-mbstring: 8.3.15
	php83-mysqli: 8.3.15

Number of packages to be installed: 3

The process will require 5 MiB more space.
1 MiB to be downloaded.
[1/3] Fetching php83-mysqli-8.3.15.pkg: 100%   52 KiB  53.6kB/s    00:01    
[2/3] Fetching php83-mbstring-8.3.15.pkg: 100%  922 KiB 944.0kB/s    00:01    
[3/3] Fetching oniguruma-6.9.10.pkg: 100%  253 KiB 258.9kB/s    00:01    
Checking integrity... done (0 conflicting)
[1/3] Installing oniguruma-6.9.10...
[1/3] Extracting oniguruma-6.9.10: 100%
[2/3] Installing php83-mysqli-8.3.15...
[2/3] Extracting php83-mysqli-8.3.15: 100%
[3/3] Installing php83-mbstring-8.3.15...
[3/3] Extracting php83-mbstring-8.3.15: 100%
=====
Message from php83-mysqli-8.3.15:

--
This file has been added to automatically load the installed extension:
/usr/local/etc/php/ext-20-mysqli.ini.sample
=====
Message from php83-mbstring-8.3.15:

--
This file has been added to automatically load the installed extension:
/usr/local/etc/php/ext-20-mbstring.ini.sample
user@freebsdsrv:~ $

Restart PHP-FMP and Apache for the changes to take effect.
Restart PHP-FMP with:

user@freebsdsrv:~ $ sudo service php_fpm restart [enter]
Performing sanity check on php-fpm configuration:
[04-Feb-2025 13:23:56] NOTICE: configuration file /usr/local/etc/php-fpm.conf test is successful
Stopping php_fpm.
Waiting for PIDS: 2041.
Performing sanity check on php-fpm configuration:
[04-Feb-2025 13:23:56] NOTICE: configuration file /usr/local/etc/php-fpm.conf test is successful
Starting php_fpm.
freebsdsrv:~ $

Show PHP-FMP status with:

user@freebsdsrv:~ $ sudo service php_fpm status [enter]
php_fpm is running as pid 2662.
freebsdsrv:~ $

Restart Apache with:

user@freebsdsrv:~ $ sudo service apache24 restart [enter]
Performing sanity check on apache24 configuration:
Syntax OK
Stopping apache24.
Waiting for PIDS: 2009.
Performing sanity check on apache24 configuration:
Syntax OK
Starting apache24.
user@freebsdsrv:~ $

Show Apache status with:

user@freebsdsrv:~ $ sudo service apache24 status [enter]
apache24 is running as pid 2698.
freebsdsrv:~ $

phpMyAdmin Dashboard

Access the phpMyAdmin dashboard via https://192.168.1.50/phpmyadmin/index.php.
Log in to phpMyAdmin dashboard with the MariaDB database root/DBpassWd credentials.
N.B.: phpMyAdmin is not completely set up!
Some features are not enabled, and this should be indicated on the phpMyAdmin dashboard. Click on the ‘Find out why’ at the end of this message and complete the operation in the phpMyAdmin configuration storage dashboard.

If you encounter a phpMyAdmin login problems:

user@freebsdsrv:~ $ sudo mysql -u root -p [enter]
Enter password: DBpassWd [enter]
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 11.4.4-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> SET old_passwords=0; [enter]
Query OK, 0 rows affected (0.000 sec)

root@localhost [(none)]> ALTER USER root@localhost IDENTIFIED BY 'DBpassWd'; [enter]
Query OK, 0 rows affected (0.428 sec)

root@localhost [(none)]> exit [enter]
Bye
user@freebsdsrv:~ $

Enable PMA function

Add user PMA with:

user@freebsdsrv:~ $ sudo mysql -u root -p [enter]
Enter password: DBpassWd [enter]
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 11.4.4-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> SET old_passwords=0; [enter]
Query OK, 0 rows affected (0.000 sec)

root@localhost [(none)]> CREATE USER 'pma'@'localhost' IDENTIFIED VIA mysql_native_password USING 'pmaPassWd'; [enter]
Query OK, 0 rows affected (0.428 sec)

root@localhost [(none)]> GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'pma'@'localhost'; [enter]
Query OK, 0 rows affected (0.428 sec)

root@localhost [(none)]> FLUSH PRIVILEGES; [enter]
Query OK, 0 rows affected (0 sec)

root@localhost [(none)]> exit [enter]
Bye
user@freebsdsrv:~ $

Enable use of PMA with:

user@freebsdsrv:~ $ sudo sudo ee +43 /usr/local/www/phpMyAdmin/config.inc.php [enter]
...
/**
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
// $cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'pmaPassWd';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';
...

phpMyAdmin Configuration File

user@freebsdsrv:~ $ sudo ee /usr/local/www/phpMyAdmin/config.inc.php [enter]
<?php
/**
 * phpMyAdmin sample configuration, you can use it as base for
 * manual configuration. For easier setup you can use setup/
 *
 * All directives are explained in documentation in the doc/ folder
 * or at <https://docs.phpmyadmin.net/>.
 */

declare(strict_types=1);

/**
 * This is needed for cookie based authentication to encrypt the cookie.
 * Needs to be a 32-bytes long string of random bytes. See FAQ 2.10.
 */
$cfg['blowfish_secret'] = '7dc4d9bc3eafddd348d0c1bb7d34d139'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

/**
 * Servers configuration
 */
$i = 0;

/**
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['socket'] = '/var/run/mysql/mysql.sock';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/**
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
// $cfg['Servers'][$i]['controlport'] = '';
// $cfg['Servers'][$i]['controluser'] = 'pma';
// $cfg['Servers'][$i]['controlpass'] = 'pmapass';

/* Storage database and tables */
// $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
// $cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
// $cfg['Servers'][$i]['relation'] = 'pma__relation';
// $cfg['Servers'][$i]['table_info'] = 'pma__table_info';
// $cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
// $cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
// $cfg['Servers'][$i]['column_info'] = 'pma__column_info';
// $cfg['Servers'][$i]['history'] = 'pma__history';
// $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
// $cfg['Servers'][$i]['tracking'] = 'pma__tracking';
// $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
// $cfg['Servers'][$i]['recent'] = 'pma__recent';
// $cfg['Servers'][$i]['favorite'] = 'pma__favorite';
// $cfg['Servers'][$i]['users'] = 'pma__users';
// $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
// $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
// $cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
// $cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
// $cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
// $cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

/**
 * End of servers configuration
 */

/**
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '/usr/local/www/phpMyAdmin/UploadDir';
$cfg['SaveDir'] = '/usr/local/www/phpMyAdmin/SaveDir';

/**
 * Whether to display icons or text or both icons and text in table row
 * action segment. Value can be either of 'icons', 'text' or 'both'.
 * default = 'both'
 */
//$cfg['RowActionType'] = 'icons';

/**
 * Defines whether a user should be displayed a "show all (records)"
 * button in browse mode or not.
 * default = false
 */
//$cfg['ShowAll'] = true;

/**
 * Number of rows displayed when browsing a result set. If the result
 * set contains more rows, "Previous" and "Next".
 * Possible values: 25, 50, 100, 250, 500
 * default = 25
 */
//$cfg['MaxRows'] = 50;

/**
 * Disallow editing of binary fields
 * valid values are:
 *   false    allow editing
 *   'blob'   allow editing except for BLOB fields
 *   'noblob' disallow editing except for BLOB fields
 *   'all'    disallow editing
 * default = 'blob'
 */
//$cfg['ProtectBinary'] = false;

/**
 * Default language to use, if not browser-defined or user-defined
 * (you find all languages in the locale folder)
 * uncomment the desired line:
 * default = 'en'
 */
//$cfg['DefaultLang'] = 'en';
//$cfg['DefaultLang'] = 'de';

/**
 * How many columns should be used for table display of a database?
 * (a value larger than 1 results in some information being hidden)
 * default = 1
 */
//$cfg['PropertiesNumColumns'] = 2;

/**
 * Set to true if you want DB-based query history.If false, this utilizes
 * JS-routines to display query history (lost by window close)
 *
 * This requires configuration storage enabled, see above.
 * default = false
 */
//$cfg['QueryHistoryDB'] = true;

/**
 * When using DB-based query history, how many entries should be kept?
 * default = 25
 */
//$cfg['QueryHistoryMax'] = 100;

/**
 * Whether or not to query the user before sending the error report to
 * the phpMyAdmin team when a JavaScript error occurs
 *
 * Available options
 * ('ask' | 'always' | 'never')
 * default = 'ask'
 */
//$cfg['SendErrorReports'] = 'always';

/**
 * 'URLQueryEncryption' defines whether phpMyAdmin will encrypt sensitive data from the URL query string.
 * 'URLQueryEncryptionSecretKey' is a 32 bytes long secret key used to encrypt/decrypt the URL query string.
 */
//$cfg['URLQueryEncryption'] = true;
//$cfg['URLQueryEncryptionSecretKey'] = '';

/**
 * You can find more configuration options in the documentation
 * in the doc/ folder or at <https://docs.phpmyadmin.net/>.
 */
Comments are closed.