Difference between revisions of "PhpMyAdmin"

From ArchWiki
Jump to navigation Jump to search
(update interlanguage links)
Tag: wiki-scripts
 
(208 intermediate revisions by 72 users not shown)
Line 1: Line 1:
[[Category:Web Server (English)]]
+
{{lowercase title}}
{{i18n|PhpMyAdmin}}
+
[[Category:Web admin interfaces]]
 +
[[cs:PhpMyAdmin]]
 +
[[es:PhpMyAdmin]]
 +
[[fr:phpmyadmin]]
 +
[[ja:PhpMyAdmin]]
 +
[[ru:PhpMyAdmin]]
 +
[[zh-hans:PhpMyAdmin]]
 +
[[Wikipedia:phpMyAdmin|phpMyAdmin]] is a web-based tool to help manage MySQL databases using an Apache/PHP frontend.
  
==Pre-Installation==
+
== Installation ==
See [[LAMP]] for a guide to setting up Apache, MySQL, and PHP.
 
  
==Installation==
+
[[Install]] the {{Pkg|phpmyadmin}} package.
To install [http://www.phpmyadmin.net/ phpMyAdmin], install the ''phpmyadmin'' and ''php-mcrypt'' packages with
 
<pre>
 
pacman -S phpmyadmin php-mcrypt
 
</pre>
 
  
==Configuration==
+
== Running ==
Ensure you do not have an older copy of phpMyAdmin.
 
<pre>
 
rm -r /srv/http/phpMyAdmin
 
</pre>
 
  
Copy the example configuration file to your httpd configuration directory.
+
===PHP===
<pre>
+
Make sure the PHP [[PHP#MySQL/MariaDB|mysql]] extension(s) have been enabled.
cp /etc/webapps/phpmyadmin/apache.example.conf /etc/httpd/conf/extra/httpd-phpmyadmin.conf
 
</pre>
 
  
Add the following lines to {{Filename|/etc/httpd/conf/httpd.conf}}:
+
Optionally you can enable {{ic|1=extension=bz2}} and {{ic|1=extension=zip}} for compression support.
<pre>
 
# phpMyAdmin configuration
 
Include conf/extra/httpd-phpmyadmin.conf
 
</pre>
 
  
You can type this into the terminal to produce the same effect:
+
{{Note|If {{ic|open_basedir}} has been set, make sure to include {{ic|/usr/share/webapps}} and {{ic|/etc/webapps}} to {{ic|open_basedir}} in {{ic|/etc/php/php.ini}}. See [[PHP#Configuration]].}}
<pre>
 
echo -e "\nInclude conf/extra/httpd-phpmyadmin.conf" >> /etc/httpd/conf/httpd.conf
 
</pre>
 
  
Add the following lines to {{Filename|/etc/httpd/conf/httpd.conf}}:
+
===Apache===
<pre>
+
Set up Apache to use PHP as outlined in the [[Apache HTTP Server#PHP]] article.
# Use for PHP 5.x:
 
LoadModule php5_module        modules/libphp5.so
 
AddHandler php5-script php
 
</pre>
 
  
Add index.php after "DirectoryIndex index.html"
+
Create the Apache configuration file:
<pre>
+
{{hc|/etc/httpd/conf/extra/phpmyadmin.conf|2=
# DirectoryIndex: sets the file that Apache will serve if a directory
+
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
# is requested.
+
<Directory "/usr/share/webapps/phpMyAdmin">
#
+
     DirectoryIndex index.php
<IfModule dir_module>
+
    AllowOverride All
     DirectoryIndex index.html index.php
+
    Options FollowSymlinks
</IfModule>
+
    Require all granted
</pre>
+
</Directory>
 +
}}
  
In {{Filename|/usr/share/webapps/phpMyAdmin/.htaccess}}, comment out ''deny from all''. The line should look like this:
+
And include it in {{ic|/etc/httpd/conf/httpd.conf}}:
<pre>
 
#deny from all
 
</pre>
 
  
Otherwise you'll get an error similar to "Error 403 - Access forbidden!" when you attempt to access your phpMyAdmin installation.
+
# phpMyAdmin configuration
 +
Include conf/extra/phpmyadmin.conf
  
Your {{Filename|/etc/httpd/conf/extra/httpd-phpmyadmin.conf}} should have the following information:
+
{{note|By default, everyone who can reach the Apache Web Server can see the phpMyAdmin login page under this URL. To change this, edit {{ic|/etc/httpd/conf/extra/phpmyadmin.conf}} to your liking. For example, if you only want to be able to access it from the same machine, replace {{ic|Require all granted}} by {{ic|Require local}}. Beware that this will disallow connecting to PhpMyAdmin on a remote server. If you still want to access PhpMyAdmin on a remote server securely, you might want to consider setting up an [[OpenSSH#Encrypted SOCKS tunnel]].}}
<pre>
 
        Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
 
        <Directory "/usr/share/webapps/phpMyAdmin">
 
                AllowOverride All
 
                Options FollowSymlinks
 
                Order allow,deny
 
                Allow from all
 
        </Directory>
 
</pre>
 
  
Open your {{Filename|/etc/php/php.ini}} and go to the line containing ''open_basedir'' and add the path(s) to your phpMyAdmin installation so it has the following:
+
After making changes to the Apache configuration file, [[restart]] {{ic|httpd.service}}.
<pre>
 
:/usr/share/webapps/:/etc/webapps/
 
</pre>
 
  
For example, mine contains the following:
+
===Lighttpd===
<pre>
+
Configuring [[Lighttpd]], make sure it is able to serve PHP files and {{ic|mod_alias}} has been enabled.
open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/srv/:/usr/share/webapps/:/etc/webapps/
 
</pre>
 
  
You need the mcrypt and mysql modules, so uncomment in {{Filename|/etc/php/php.ini}}:
+
Add the following alias for PhpMyAdmin to the config:
  ;extension=mcrypt.so
 
  ;extension=mysql.so
 
:to
 
  extension=mcrypt.so
 
  extension=mysql.so
 
  
=== Add blowfish_secret passphrase ===
+
  alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")
If you see the following error message at the bottom of the page when you first log in to /phpmyadmin (using a previously setup MySQL username and password) :
 
  
ERROR: The configuration file now needs a secret passphrase (blowfish_secret)
+
=== Nginx ===
  
You need to add a blowfish password to the phpMyAdmin's config file. Edit {{Filename|/etc/webapps/phpmyadmin/config.inc.php}} and insert a random blowfish "password" in the line
+
Make sure to set up [[nginx#FastCGI]] and use [[Nginx#Server_blocks|server blocks]] to make management easier.
  
$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
+
By preference; access phpMyAdmin by subdomain, e.g. {{ic|https://pma.domain.tld}}:
 +
{{hc|/etc/nginx/sites-available/pma.domain.tld|2=
 +
server {
 +
    server_name pma.domain.tld;
 +
    ; listen 80; # also listen on http
 +
    ; listen [::]:80;
 +
    listen 443 ssl http2;
 +
    listen [::]:443 ssl http2;
 +
    index index.php;
 +
    access_log /var/log/nginx/pma.access.log;
 +
    error_log /var/log/nginx/pma.error.log;
  
Go [http://www.question-defense.com/tools/phpmyadmin-blowfish-secret-generator here] to get a nicely generated blowfish_secret and paste it between the '' marks. It should now look something like this:
+
    # Allows limiting access to certain client addresses.
+
    ; allow 192.168.1.0/24;
$cfg['blowfish_secret'] = 'qtdRoGmbc9{8IZr323xYcSN]0s)r$9b_JUnb{~Xz'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
+
    ; allow ''my-ip'';
 +
    ; deny all;
  
The error should go away if you refresh the phpmyadmin page.
+
    root /usr/share/webapps/phpMyAdmin;
 +
    location / {
 +
        try_files $uri $uri/ =404;
 +
    }
  
==Accessing your phpMyAdmin installation==
+
    error_page 404 /index.php;
  
Finally your phpmyadmin installation is complete. Before start using it you need to restart your apache server by following command:
+
    location ~ \.php$ {
 +
        try_files $uri $document_root$fastcgi_script_name =404;
  
<pre>
+
        fastcgi_split_path_info ^(.+\.php)(/.*)$;
# /etc/rc.d/httpd restart
+
        fastcgi_pass unix:/run/php-fpm/php-fpm.sock;
</pre>
+
        fastcgi_index index.php;
 +
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
 +
        include fastcgi_params;
  
You can access your phpmyadmin installation using the following url:
+
        fastcgi_param HTTP_PROXY "";
 +
        fastcgi_param HTTPS on;
 +
        fastcgi_request_buffering off;
 +
  }
 +
}
 +
}}
  
<pre>
+
Or by subdirectory, e.g. {{ic|https://domain.tld/phpMyAdmin}}:
http://localhost/phpmyadmin/
+
{{hc|/etc/nginx/sites-available/domain.tld|<nowiki>
or
+
server {
http://localhost/phpmyadmin/index.php
+
    server_name domain.tld;
</pre>
+
    listen 443 ssl http2;
 +
    listen [::]:443 ssl http2;
 +
    index index.php;
 +
    access_log /var/log/nginx/domain.tld.access.log;
 +
    error_log /var/log/nginx/domain.tld.error.log;
  
Note: 'localhost' is the hostname in your /etc/rc.conf file.
+
    root /srv/http/domain.tld;
 +
    location / {
 +
        try_files $uri $uri/ =404;
 +
    }
  
If you want to access it using:
+
    location /phpMyAdmin {
 +
        root /usr/share/webapps/phpMyAdmin;
 +
    }
  
<pre>
+
    # Deny static files
http://localhost/phpmyadmin
+
    location ~ ^/phpMyAdmin/(README|LICENSE|ChangeLog|DCO)$ {
</pre>
+
      deny all;
 +
    }
  
in '/etc/httpd/conf/extra/httpd-phpmyadmin.conf' change:
+
    # Deny .md files
 +
    location ~ ^/phpMyAdmin/(.+\.md)$ {
 +
      deny all;
 +
  }
  
<pre>
+
  # Deny setup directories
Alias /phpmyadmin/ "/usr/share/webapps/phpMyAdmin/"
+
  location ~ ^/phpMyAdmin/(doc|sql|setup)/ {
</pre>
+
      deny all;
 +
  }
  
to
+
  #FastCGI config for phpMyAdmin
 +
  location ~ /phpMyAdmin/(.+\.php)$ {
 +
      try_files $uri $document_root$fastcgi_script_name =404;
  
<pre>
+
      fastcgi_split_path_info ^(.+\.php)(/.*)$;
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
+
      fastcgi_pass unix:/run/php-fpm/php-fpm.sock;
</pre>
+
      fastcgi_index index.php;
 +
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
 +
      include fastcgi_params;
  
You should also read [http://bbs.archlinux.org/viewtopic.php?pid=632500 this thread].
+
      fastcgi_param HTTP_PROXY "";
 +
      fastcgi_param HTTPS on;
 +
      fastcgi_request_buffering off;
 +
  }
 +
}
 +
</nowiki>}}
  
If you get the error "#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)" then you might want to change "localhost" in /etc/webapps/phpmyadmin/config.inc.php on this line:
+
== Configuration ==
  
<pre>
+
The main configuration file is located at {{ic|/usr/share/webapps/phpMyAdmin/config.inc.php}}.
$cfg['Servers'][$i]['host'] = 'localhost';
 
</pre>
 
  
to your hostname specified in /etc/hosts and /etc/rc.conf under HOSTNAME.
+
=== Define a remote MySQL server ===
  
If you would like to use phpmyadmin setup script by calling http://localhost/phpmyadmin/setup you will need to create a config directory that's writeable by the httpd in the /usr/share/webapps/phpmyadmin as follows:
+
If the [[MySQL]] server is a remote host, append the following line to the config file:
 +
$cfg['Servers'][$i]['host'] = 'example.com';
  
<pre>
+
=== Using setup script ===
cd /usr/share/webapps/phpMyAdmin
 
sudo mkdir config
 
sudo chgrp http config
 
sudo chmod g+w config
 
</pre>
 
  
==Lighttpd Configuration==
+
To allow the usage of the phpMyAdmin setup script (e.g. http://localhost/phpmyadmin/setup), make sure {{ic|/usr/share/webapps/phpMyAdmin}} is writable for the {{ic|http}} [[user]]:
  
The php setup for lighttpd is exactly the same as for apache.
+
# mkdir /usr/share/webapps/phpMyAdmin/config
Make an alias for phpmyadmin in your lighttpd config.
+
# chown http:http /usr/share/webapps/phpMyAdmin/config
  alias.url = ( "/phpmyadmin/" => "/usr/share/webapps/phpMyAdmin/")
+
# chmod 750 /usr/share/webapps/phpMyAdmin/config
Then enable mod_alias, mod_fastcgi and mod_cgi in your config ( server.modules section )
 
  
Update open_basedir in /etc/php/php.ini and add "/usr/share/webapps/".
+
=== Add blowfish_secret passphrase ===
  open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/
+
It is required to enter a unique 32 characters long string to fully use the blowfish algorithm used by phpMyAdmin, thus preventing the message '' ERROR: The configuration file now needs a secret passphrase (blowfish_secret)'':
 
 
Make sure lighttpd is setup to serve php files, [[Lighttpd and FastCGI]]
 
 
 
Restart lighttpd and browse to http://localhost/phpmyadmin/index.php
 
 
 
==NGINX Configuration==
 
 
 
Also similar to apache configuration (and Lighttpd, for that matter).
 
 
 
Create a symbolic link to the /usr/share/webapps/phpmyadmin directory from whichever directory your vhost is serving files from, e.g. /srv/http/<domain>/public_html/
 
 
 
  sudo ln -s /usr/share/webapps/phpmyadmin /srv/http/<domain>/public_html/phpmyadmin
 
  
You can also setup a sub domain with a server block like so (if using php-fpm):
+
{{hc|1=/usr/share/webapps/phpMyAdmin/config.inc.php|2=
 +
$cfg['blowfish_secret'] = '...';
 +
}}
  
  server {
+
=== Enabling Configuration Storage ===
          server_name    phpmyadmin.<domain.tld>;
+
Extra options such as table linking, change tracking, PDF creation, and bookmarking queries are disabled by default, displaying ''The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated.'' on the homepage.
          access_log      /srv/http/<domain>/logs/phpmyadmin.access.log;
 
          error_log      /srv/http/<domain.tld>/logs/phpmyadmin.error.log;
 
 
 
          location / {
 
                  root    /srv/http/<domain.tld>/public_html/phpmyadmin;
 
                  index  index.html index.htm index.php;
 
          }
 
 
 
          location ~ \.php$ {
 
                  root            /srv/http/<domain.tld>/public_html/phpmyadmin;
 
                  fastcgi_pass    unix:/var/run/php-fpm/php-fpm.sock;
 
                  fastcgi_index  index.php;
 
                  fastcgi_param  SCRIPT_FILENAME  /srv/http/<domain.tld>/public_html/phpmyadmin/$fastcgi_script_name;
 
                  include        fastcgi_params;
 
          }
 
  }
 
  
You may run into some issues with phpmyadmin telling you "The Configuration File Now Needs A Secret Passphrase" and no matter what you enter, the error is still displayed. Try changing the ownership of the files to the NGINX specified user/group, e.g. nginx...
+
{{Note|This example assumes you want to use the default username '''pma''' as the {{ic|controluser}}, and '''pmapass''' as the {{ic|controlpass}}.}}
  
  cd /usr/share/webapps/phpmyadmin
+
In {{ic|/usr/share/webapps/phpMyAdmin/config.inc.php}}, uncomment (remove the leading "//"s), and change them to your desired credentials if needed:
  sudo chown -R nginx:nginx *
+
{{hc|/usr/share/webapps/phpMyAdmin/config.inc.php|2=
 +
/* User used to manipulate with storage */
 +
// $cfg['Servers'][$i]['controlhost'] = 'my-host';
 +
// $cfg['Servers'][$i]['controlport'] = '3306';
 +
$cfg['Servers'][$i]['controluser'] = 'pma';
 +
$cfg['Servers'][$i]['controlpass'] = 'pmapass';
  
While you can enter anything for the blowfish password, you may want to choose a randomly generated string of characters (most likely for security reasons). Here's a handy tool that will do that for you on the web[http://www.question-defense.com/tools/phpmyadmin-blowfish-secret-generator].
+
/* 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';
 +
}}
  
==Other (Older) information==
+
===== Setup database =====
 +
Two options are available to create the required tables:
  
This page holds a sample 'config.inc.php' file that you can place in the main phpMyAdmin directory so that it immediately starts working
+
* Import {{ic|/usr/share/webapps/phpMyAdmin/sql/create_tables.sql}} by using PhpMyAdmin.
 +
* Execute {{ic|mysql -u root -p < /usr/share/webapps/phpMyAdmin/sql/create_tables.sql}} in the command line.
  
'''Things you should do first'''
+
===== Setup database user =====
 +
To apply the required permissions for {{ic|controluser}}, execute the following query:
  
Create a 'controluser', so that phpmyadmin can read from the main mysql database.
+
{{Note|Make sure to replace all instances of {{ic|pma}} and {{ic|pmapass}} to the values set in {{ic|config.inc.php}}. If you are setting this up for a remote database, then you must also change {{ic|localhost}} to the proper host.}}
  
<pre>mysql -u root -pYOURROOTPASSWORD
+
{{bc|
mysql> grant usage on mysql.* to controluser@localhost identified by 'CONTROLPASS';
+
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
</pre>
+
GRANT SELECT (
 +
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
 +
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
 +
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
 +
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
 +
    Execute_priv, Repl_slave_priv, Repl_client_priv
 +
    ) ON mysql.user TO 'pma'@'localhost';
 +
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
 +
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
 +
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
 +
    ON mysql.tables_priv TO 'pma'@'localhost';
 +
}}
  
'''Where is phpmyadmin'''
+
In order use the bookmark and relation features, set the following permissions:
 +
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
  
in phpmyadmin 3.2.2-3 the file is missing /srv/http/ create this symlik
+
Re-login to ensure the new features are activated.
  
<pre>ln -s /usr/share/webapps/phpMyAdmin/ /srv/http/phpmyadmin
+
=== Enabling template caching ===
</pre>
 
  
'''Things you should change'''
+
Edit {{ic|/usr/share/webapps/phpMyAdmin/config.inc.php}} to add the line:
  
controluser is set to controluser <br>
+
$cfg['TempDir'] = '/tmp/phpmyadmin';
controlpass is set to password <br>
 
verbose is set to name_of_server
 
  
'''Sample 'config.inc.php' file'''
+
=== Remove config directory ===
<pre>
 
<?php
 
/*
 
* Generated configuration file
 
* Generated by: phpMyAdmin 2.11.8.1 setup script by Michal Čihař <michal@cihar.com>
 
* Version: $Id: setup.php 11423 2008-07-24 17:26:05Z lem9 $
 
* Date: Mon, 01 Sep 2008 20:34:02 GMT
 
*/
 
  
/* Servers configuration */
+
Remove temporary configuration directory once configuration is done. This will also suppress warning from web interface:
$i = 0;
 
  
/* Server ravi-test-mysql (http) [1] */
+
# rm -r /usr/share/webapps/phpMyAdmin/config
$i++;
 
$cfg['Servers'][$i]['host'] = 'localhost';
 
$cfg['Servers'][$i]['extension'] = 'mysql';
 
$cfg['Servers'][$i]['port'] = '3306';
 
$cfg['Servers'][$i]['connect_type'] = 'tcp';
 
$cfg['Servers'][$i]['compress'] = false;
 
$cfg['Servers'][$i]['controluser'] = 'controluser';
 
$cfg['Servers'][$i]['controlpass'] = 'password';
 
$cfg['Servers'][$i]['auth_type'] = 'http';
 
$cfg['Servers'][$i]['verbose'] = 'name_of_server';
 
  
/* End of servers configuration */
+
== See also ==
  
$cfg['LeftFrameLight'] = true;
+
* [https://www.phpmyadmin.net/ Official website]
$cfg['LeftFrameDBTree'] = true;
 
$cfg['LeftFrameDBSeparator'] = '_';
 
$cfg['LeftFrameTableSeparator'] = '__';
 
$cfg['LeftFrameTableLevel'] = 1;
 
$cfg['LeftDisplayLogo'] = true;
 
$cfg['LeftDisplayServers'] = false;
 
$cfg['DisplayServersList'] = false;
 
$cfg['DisplayDatabasesList'] = 'auto';
 
$cfg['LeftPointerEnable'] = true;
 
$cfg['DefaultTabServer'] = 'main.php';
 
$cfg['DefaultTabDatabase'] = 'db_structure.php';
 
$cfg['DefaultTabTable'] = 'tbl_structure.php';
 
$cfg['LightTabs'] = false;
 
$cfg['ErrorIconic'] = true;
 
$cfg['MainPageIconic'] = true;
 
$cfg['ReplaceHelpImg'] = true;
 
$cfg['NavigationBarIconic'] = 'both';
 
$cfg['PropertiesIconic'] = 'both';
 
$cfg['BrowsePointerEnable'] = true;
 
$cfg['BrowseMarkerEnable'] = true;
 
$cfg['ModifyDeleteAtRight'] = false;
 
$cfg['ModifyDeleteAtLeft'] = true;
 
$cfg['RepeatCells'] = 100;
 
$cfg['DefaultDisplay'] = 'horizontal';
 
$cfg['TextareaCols'] = 40;
 
$cfg['TextareaRows'] = 7;
 
$cfg['LongtextDoubleTextarea'] = true;
 
$cfg['TextareaAutoSelect'] = false;
 
$cfg['CharEditing'] = 'input';
 
$cfg['CharTextareaCols'] = 40;
 
$cfg['CharTextareaRows'] = 2;
 
$cfg['CtrlArrowsMoving'] = true;
 
$cfg['DefaultPropDisplay'] = 'horizontal';
 
$cfg['InsertRows'] = 2;
 
$cfg['EditInWindow'] = true;
 
$cfg['QueryWindowHeight'] = 310;
 
$cfg['QueryWindowWidth'] = 550;
 
$cfg['QueryWindowDefTab'] = 'sql';
 
$cfg['ForceSSL'] = false;
 
$cfg['ShowPhpInfo'] = false;
 
$cfg['ShowChgPassword'] = false;
 
$cfg['AllowArbitraryServer'] = false;
 
$cfg['LoginCookieRecall'] = 'something';
 
$cfg['LoginCookieValidity'] = 1800;
 
?>
 
</pre>
 

Latest revision as of 15:36, 23 February 2020

phpMyAdmin is a web-based tool to help manage MySQL databases using an Apache/PHP frontend.

Installation

Install the phpmyadmin package.

Running

PHP

Make sure the PHP mysql extension(s) have been enabled.

Optionally you can enable extension=bz2 and extension=zip for compression support.

Note: If open_basedir has been set, make sure to include /usr/share/webapps and /etc/webapps to open_basedir in /etc/php/php.ini. See PHP#Configuration.

Apache

Set up Apache to use PHP as outlined in the Apache HTTP Server#PHP article.

Create the Apache configuration file:

/etc/httpd/conf/extra/phpmyadmin.conf
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
<Directory "/usr/share/webapps/phpMyAdmin">
    DirectoryIndex index.php
    AllowOverride All
    Options FollowSymlinks
    Require all granted
</Directory>

And include it in /etc/httpd/conf/httpd.conf:

# phpMyAdmin configuration
Include conf/extra/phpmyadmin.conf
Note: By default, everyone who can reach the Apache Web Server can see the phpMyAdmin login page under this URL. To change this, edit /etc/httpd/conf/extra/phpmyadmin.conf to your liking. For example, if you only want to be able to access it from the same machine, replace Require all granted by Require local. Beware that this will disallow connecting to PhpMyAdmin on a remote server. If you still want to access PhpMyAdmin on a remote server securely, you might want to consider setting up an OpenSSH#Encrypted SOCKS tunnel.

After making changes to the Apache configuration file, restart httpd.service.

Lighttpd

Configuring Lighttpd, make sure it is able to serve PHP files and mod_alias has been enabled.

Add the following alias for PhpMyAdmin to the config:

 alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")

Nginx

Make sure to set up nginx#FastCGI and use server blocks to make management easier.

By preference; access phpMyAdmin by subdomain, e.g. https://pma.domain.tld:

/etc/nginx/sites-available/pma.domain.tld
server {
    server_name pma.domain.tld;
    ; listen 80; # also listen on http
    ; listen [::]:80;
    listen 443 ssl http2;
    listen [::]:443 ssl http2;
    index index.php;
    access_log /var/log/nginx/pma.access.log;
    error_log /var/log/nginx/pma.error.log;

    # Allows limiting access to certain client addresses.
    ; allow 192.168.1.0/24;
    ; allow my-ip;
    ; deny all;

    root /usr/share/webapps/phpMyAdmin;
    location / {
        try_files $uri $uri/ =404;
    }

    error_page 404 /index.php;

    location ~ \.php$ {
        try_files $uri $document_root$fastcgi_script_name =404;

        fastcgi_split_path_info ^(.+\.php)(/.*)$;
        fastcgi_pass unix:/run/php-fpm/php-fpm.sock;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;

        fastcgi_param HTTP_PROXY "";
        fastcgi_param HTTPS on;
        fastcgi_request_buffering off;
   }
}

Or by subdirectory, e.g. https://domain.tld/phpMyAdmin:

/etc/nginx/sites-available/domain.tld
server {
    server_name domain.tld;
    listen 443 ssl http2;
    listen [::]:443 ssl http2;
    index index.php;
    access_log /var/log/nginx/domain.tld.access.log;
    error_log /var/log/nginx/domain.tld.error.log;

    root /srv/http/domain.tld;
    location / {
        try_files $uri $uri/ =404;
    }

    location /phpMyAdmin {
        root /usr/share/webapps/phpMyAdmin;
    }

    # Deny static files
    location ~ ^/phpMyAdmin/(README|LICENSE|ChangeLog|DCO)$ {
       deny all;
    }

    # Deny .md files
    location ~ ^/phpMyAdmin/(.+\.md)$ {
      deny all;
   }

   # Deny setup directories
   location ~ ^/phpMyAdmin/(doc|sql|setup)/ {
      deny all;
   }

   #FastCGI config for phpMyAdmin
   location ~ /phpMyAdmin/(.+\.php)$ {
      try_files $uri $document_root$fastcgi_script_name =404;

      fastcgi_split_path_info ^(.+\.php)(/.*)$;
      fastcgi_pass unix:/run/php-fpm/php-fpm.sock;
      fastcgi_index index.php;
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
      include fastcgi_params;

      fastcgi_param HTTP_PROXY "";
      fastcgi_param HTTPS on;
      fastcgi_request_buffering off;
   }
}

Configuration

The main configuration file is located at /usr/share/webapps/phpMyAdmin/config.inc.php.

Define a remote MySQL server

If the MySQL server is a remote host, append the following line to the config file:

$cfg['Servers'][$i]['host'] = 'example.com';

Using setup script

To allow the usage of the phpMyAdmin setup script (e.g. http://localhost/phpmyadmin/setup), make sure /usr/share/webapps/phpMyAdmin is writable for the http user:

# mkdir /usr/share/webapps/phpMyAdmin/config
# chown http:http /usr/share/webapps/phpMyAdmin/config
# chmod 750 /usr/share/webapps/phpMyAdmin/config

Add blowfish_secret passphrase

It is required to enter a unique 32 characters long string to fully use the blowfish algorithm used by phpMyAdmin, thus preventing the message ERROR: The configuration file now needs a secret passphrase (blowfish_secret):

/usr/share/webapps/phpMyAdmin/config.inc.php
$cfg['blowfish_secret'] = '...';

Enabling Configuration Storage

Extra options such as table linking, change tracking, PDF creation, and bookmarking queries are disabled by default, displaying The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. on the homepage.

Note: This example assumes you want to use the default username pma as the controluser, and pmapass as the controlpass.

In /usr/share/webapps/phpMyAdmin/config.inc.php, uncomment (remove the leading "//"s), and change them to your desired credentials if needed:

/usr/share/webapps/phpMyAdmin/config.inc.php
/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = 'my-host';
// $cfg['Servers'][$i]['controlport'] = '3306';
$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';
Setup database

Two options are available to create the required tables:

  • Import /usr/share/webapps/phpMyAdmin/sql/create_tables.sql by using PhpMyAdmin.
  • Execute mysql -u root -p < /usr/share/webapps/phpMyAdmin/sql/create_tables.sql in the command line.
Setup database user

To apply the required permissions for controluser, execute the following query:

Note: Make sure to replace all instances of pma and pmapass to the values set in config.inc.php. If you are setting this up for a remote database, then you must also change localhost to the proper host.
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
GRANT SELECT (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';

In order use the bookmark and relation features, set the following permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';

Re-login to ensure the new features are activated.

Enabling template caching

Edit /usr/share/webapps/phpMyAdmin/config.inc.php to add the line:

$cfg['TempDir'] = '/tmp/phpmyadmin';

Remove config directory

Remove temporary configuration directory once configuration is done. This will also suppress warning from web interface:

# rm -r /usr/share/webapps/phpMyAdmin/config

See also