Difference between revisions of "PhpMyAdmin"

From ArchWiki
Jump to navigation Jump to search
(Nginx Configuration)
m (PHP: style)
 
(143 intermediate revisions by 45 users not shown)
Line 1: Line 1:
[[Category:Web Server]]
 
 
{{lowercase title}}
 
{{lowercase title}}
 +
[[Category:Web admin interfaces]]
 
[[cs:PhpMyAdmin]]
 
[[cs:PhpMyAdmin]]
 
[[es:PhpMyAdmin]]
 
[[es:PhpMyAdmin]]
 
[[fr:phpmyadmin]]
 
[[fr:phpmyadmin]]
 +
[[ja:PhpMyAdmin]]
 
[[ru:PhpMyAdmin]]
 
[[ru:PhpMyAdmin]]
[[tr:PhpMyAdmin]]
+
[[zh-hans:PhpMyAdmin]]
[[zh-CN:PhpMyAdmin]]
+
[[Wikipedia:phpMyAdmin|phpMyAdmin]] is a web-based tool to help manage MySQL databases using an Apache/PHP frontend.
  
[http://www.phpmyadmin.net/ phpMyAdmin] is a web-based tool to help manage MySQL databases using an Apache/PHP frontend. It requires a working [[LAMP]] setup.
+
== Installation ==
  
==Installation==
+
[[Install]] the {{Pkg|phpmyadmin}} package.
Install the {{Pkg|phpmyadmin}} and {{Pkg|php-mcrypt}} packages:
 
# pacman -S phpmyadmin php-mcrypt
 
  
== Configuration ==
+
== Running ==
Ensure you do not have an older copy of phpMyAdmin.
 
# rm -r /srv/http/phpMyAdmin
 
 
 
Copy the example configuration file to your httpd configuration directory.
 
# cp /etc/webapps/phpmyadmin/apache.example.conf /etc/httpd/conf/extra/httpd-phpmyadmin.conf
 
  
Add the following lines to {{ic|/etc/httpd/conf/httpd.conf}}:
+
===PHP===
# phpMyAdmin configuration
+
Make sure the PHP [[PHP#MySQL/MariaDB|mysql]] extension(s) have been enabled.
Include conf/extra/httpd-phpmyadmin.conf
 
  
=== Adjust access rights ===
+
Optionally you can enable {{ic|1=extension=bz2}} and {{ic|1=extension=zip}} for compression support.
To allow access from any host, edit {{ic|/etc/webapps/phpmyadmin/.htaccess}} and change {{ic|deny from all}} into
 
allow from all
 
  
Alternatively, you can restrict access to localhost and your local network only. Replace ''192.168.1.0/24'' with your network's IP block.
+
{{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]].}}
deny from all
 
allow from localhost
 
allow from 127.0.0.1
 
allow from ::1
 
allow from 192.168.1.0/24
 
  
{{Note|The ::1 is required when using IPv6. Otherwise you might get an error similar to "Error 403 - Access forbidden!" when you attempt to access your phpMyAdmin installation.}}
+
===Apache===
 +
Set up Apache to use PHP as outlined in the [[Apache HTTP Server#PHP]] article.
  
=== Review apache phpmyadmin configuration ===
+
Create the Apache configuration file:
Your {{ic|/etc/httpd/conf/extra/httpd-phpmyadmin.conf}} should have the following information:
+
{{hc|/etc/httpd/conf/extra/phpmyadmin.conf|2=
{{bc|
+
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
        Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
+
<Directory "/usr/share/webapps/phpMyAdmin">
        <Directory "/usr/share/webapps/phpMyAdmin">
+
    DirectoryIndex index.php
                AllowOverride All
+
    AllowOverride All
                Options FollowSymlinks
+
    Options FollowSymlinks
                Order allow,deny
+
    Require all granted
                Allow from all
+
</Directory>
                php_admin_value open_basedir "/srv/:/tmp/:/usr/share/webapps/:/etc/webapps:/usr/share/pear/"
 
        </Directory>
 
 
}}
 
}}
  
You need the mysqli and mcrypt (if you want phpMyAdmin internal authentication) modules, so uncomment the following in {{ic|/etc/php/php.ini}}:
+
And include it in {{ic|/etc/httpd/conf/httpd.conf}}:
extension=mysqli.so
 
extension=mcrypt.so
 
  
[[Daemons#Restarting|Restart]] httpd.
+
# phpMyAdmin configuration
 +
Include conf/extra/phpmyadmin.conf
  
=== Add blowfish_secret passphrase ===
+
{{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]].}}
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)
+
After making changes to the Apache configuration file, [[restart]] {{ic|httpd.service}}.
  
You need to add a blowfish password to the phpMyAdmin's config file. Edit {{ic|/etc/webapps/phpmyadmin/config.inc.php}} and insert a random blowfish "password" in the line
+
===Lighttpd===
 +
Configuring [[Lighttpd]], make sure it is able to serve PHP files and {{ic|mod_alias}} has been enabled.
  
$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
+
Add the following alias for PhpMyAdmin to the config:
  
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:
+
  alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")
 
$cfg['blowfish_secret'] = 'qtdRoGmbc9{8IZr323xYcSN]0s)r$9b_JUnb{~Xz'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
 
  
The error should go away if you refresh the phpmyadmin page.
+
=== Nginx ===
  
=== Enabling Configuration Storage (optional) ===
+
Make sure to set up [[nginx#FastCGI]] and use [[Nginx#Server_blocks|server blocks]] to make management easier.
Now that the basic database server has been setup, it ''is'' functional, however by default, extra options such as table linking, change tracking, PDF creation, and bookmarking queries are disabled. You will see a message at the bottom of the main phpMyAdmin page, "The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why...", This section addresses how to to enable these extra features.
 
  
{{note|This example assumes you want to use the username '''pma''' as the controluser, and '''pmapass''' as the controlpass. These should be changed (the ''very'' least, you should change the password!) to something more secure.}}
+
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;
  
In {{ic|/etc/webapps/phpmyadmin/config.inc.php}}, uncomment (remove the leading "//"s on) these two lines, and change them to your desired credentials:
+
    # Allows limiting access to certain client addresses.
{{bc|1=
+
    ; allow 192.168.1.0/24;
// $cfg['Servers'][$i]['controluser'] = 'pma';
+
    ; allow ''my-ip'';
// $cfg['Servers'][$i]['controlpass'] = 'pmapass';
+
    ; deny all;
}}
 
  
You will need this information later, so keep it in mind.
+
    root /usr/share/webapps/phpMyAdmin;
 +
    location / {
 +
        try_files $uri $uri/ =404;
 +
    }
  
Beneath the controluser setup section, uncomment these lines:
+
    error_page 404 /index.php;
{{bc|1=
 
/* 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]['tracking'] = 'pma_tracking';
 
// $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
 
// $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
 
// $cfg['Servers'][$i]['recent'] = 'pma_recent';
 
}}
 
  
Next, create the user with the above details. Don't set any permissions for it just yet.
+
    location ~ \.php$ {
{{note|If you can't login to phpmyadmin, make sure that your mysql server is started.}}
+
        try_files $uri $document_root$fastcgi_script_name =404;
  
===== creating phpMyAdmin database =====
+
        fastcgi_split_path_info ^(.+\.php)(/.*)$;
Using the phpMyAdmin web interface:
+
        fastcgi_pass unix:/run/php-fpm/php-fpm.sock;
Import {{ic|/usr/share/webapps/phpMyAdmin/examples/create_tables.sql}} from phpMyAdmin -> Import.
+
        fastcgi_index index.php;
'''or'''
+
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
Using command line: {{ic|mysql -u root -p < /usr/share/webapps/phpMyAdmin/examples/create_tables.sql}}.
+
        include fastcgi_params;
  
===== creating phpMyAdmin database user =====
+
        fastcgi_param HTTP_PROXY "";
Now to apply the permissions to your controluser, in the SQL tab, 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:
+
        fastcgi_param HTTPS on;
{{bc|
+
        fastcgi_request_buffering off;
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 to take advantage of the bookmark and relation features, you will also need to give '''pma''' some additional permissions:
+
Or by subdirectory, e.g. {{ic|https://domain.tld/phpMyAdmin}}:
{{Note|as long as you did not change the value of '''$cfg['Servers'][$i]['pmadb']''' in {{ic|/etc/webapps/phpmyadmin/config.inc.php}}, then '''<pma_db>''' should be '''phpmyadmin'''}}
+
{{hc|/etc/nginx/sites-available/domain.tld|<nowiki>
{{bc|GRANT SELECT, INSERT, UPDATE, DELETE ON <pma_db>.* TO 'pma'@'localhost';}}
+
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;
  
Log out, and back in to ensure the new features are activated. The message at the bottom of the main screen should now be gone.
+
    root /srv/http/domain.tld;
 +
    location / {
 +
        try_files $uri $uri/ =404;
 +
    }
  
==Accessing your phpMyAdmin installation==
+
    location /phpMyAdmin {
Finally your phpmyadmin installation is complete. Before you start using it you need to [[Daemons#Restarting|restart]] httpd (Apache)
+
        root /usr/share/webapps/phpMyAdmin;
 +
    }
  
You can access your phpmyadmin installation using the following url:
+
    # Deny static files
http://localhost/phpmyadmin/
+
    location ~ ^/phpMyAdmin/(README|LICENSE|ChangeLog|DCO)$ {
 +
      deny all;
 +
    }
  
If you want to access it using {{ic|http://localhost/phpmyadmin}}, open '/etc/httpd/conf/extra/httpd-phpmyadmin.conf' and change:
+
    # Deny .md files
Alias /phpmyadmin/ "/usr/share/webapps/phpMyAdmin/"
+
    location ~ ^/phpMyAdmin/(.+\.md)$ {
to
+
      deny all;
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
+
  }
  
You should also read [https://bbs.archlinux.org/viewtopic.php?pid=632500 this thread].
+
  # Deny setup directories
 +
  location ~ ^/phpMyAdmin/(doc|sql|setup)/ {
 +
      deny all;
 +
  }
  
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 {{ic|/etc/webapps/phpmyadmin/config.inc.php}} to your hostname:
+
  #FastCGI config for phpMyAdmin
 +
  location ~ /phpMyAdmin/(.+\.php)$ {
 +
      try_files $uri $document_root$fastcgi_script_name =404;
  
{{bc|1=
+
      fastcgi_split_path_info ^(.+\.php)(/.*)$;
$cfg['Servers'][$i]['host'] = 'localhost';
+
      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;
  
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:
+
      fastcgi_param HTTP_PROXY "";
 +
      fastcgi_param HTTPS on;
 +
      fastcgi_request_buffering off;
 +
  }
 +
}
 +
</nowiki>}}
  
{{bc|
+
== Configuration ==
cd /usr/share/webapps/phpMyAdmin
+
The main configuration file is located at {{ic|/etc/webapps/phpmyadmin/config.inc.php}}.
mkdir config
 
chgrp http config
 
chmod g+w config
 
}}
 
  
==Lighttpd Configuration==
+
If the [[MySQL]] server is not on localhost, [[append]] the following line:
Configurating Lighttpd is similar to Apache. Make sure Lighttpd is setup to serve PHP files (see [[Lighttpd]]).
+
$cfg['Servers'][$i]['host'] = 'localhost';
  
Make an alias for phpmyadmin in your Lighttpd config.
+
=== Using setup script ===
  alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")
 
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/".
+
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]]:
  open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/:/etc/webapps/
 
  
Restart Lighttpd and go to [http://localhost/phpmyadmin/].
+
# mkdir /usr/share/webapps/phpMyAdmin/config
 +
# chown http:http /usr/share/webapps/phpMyAdmin/config
 +
# chmod 750 /usr/share/webapps/phpMyAdmin/config
  
==Nginx Configuration==
+
=== Add blowfish_secret passphrase ===
Configurating Nginx is similar to Apache (and Lighttpd, for that matter). Make sure Nginx is setup to serve PHP files (see [[Nginx]]).
+
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)'':
  
You can setup a sub domain (or domain) with a server block like so (if using php-fpm):
+
{{hc|1=/etc/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.
 
 
          root    /usr/share/webapps/phpMyAdmin;
 
          index  index.php;
 
 
 
          location ~ \.php$ {
 
                  try_files      $uri =404;
 
                  fastcgi_pass  unix:/var/run/php-fpm/php-fpm.sock;
 
                  fastcgi_index  index.php;
 
                  include        fastcgi.conf;
 
          }
 
  }
 
  
To access this url on your localhost, you can simply add an entry in /etc/hosts:
+
{{Note|This example assumes you want to use the default username '''pma''' as the {{ic|controluser}}, and '''pmapass''' as the {{ic|controlpass}}.}}
  127.0.0.1 phpmyadmin.<domain.tld>
 
  
You need to update PHP's open_basedir option to add the appropriate directories.
+
In {{ic|/etc/webapps/phpmyadmin/config.inc.php}}, uncomment (remove the leading "//"s), and change them to your desired credentials if needed:
Either in /etc/php/php.ini:
+
{{hc|/etc/webapps/phpmyadmin/config.inc.php|2=
  open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/:/etc/webapps/
+
/* 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';
  
Or if running php-fpm with a separate entry for phpmyadmin, you can overwrite this value in your pool definition (in /etc/php/fpm.d/<pool file>):
+
/* Storage database and tables */
  php_admin_value[open_basedir] = /tmp/:/usr/share/webapps/:/etc/webapps/
+
$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';
 +
}}
  
If the above doesn't fix it try adding the following to your NGINX Configuration below the other fastcgi_param (I think its something to do with the Suhosin-Patch)
+
===== Setup database =====
  fastcgi_param  PHP_ADMIN_VALUE  open_basedir="/srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/:/etc/webapps/";
+
Two options are available to create the required tables:
  
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].
+
* 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.
  
When using SSL, you might run into the problem that the links on the pages generated by phpMyAdmin incorrectly start with "http" instead of "https" which may cause errors. To fix this, you can add the following fcgi_param to your SSL-enabled server section (in addition to your usual fastcgi params):
+
===== Setup database user =====
  fastcgi_param HTTPS on;
+
To apply the required permissions for {{ic|controluser}}, execute the following query:
  
==Other (Older) information==
+
{{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.}}
  
This page holds a sample 'config.inc.php' file that you can place in the main phpMyAdmin directory so that it immediately starts working
+
{{bc|
 
+
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
'''Things you should do first'''
+
GRANT SELECT (
 
+
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create a 'controluser', so that phpmyadmin can read from the main mysql database.
+
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
 
+
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
{{bc|mysql -u root -p YOURROOTPASSWORD
+
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
mysql> grant usage on mysql.* to controluser@localhost identified by 'CONTROLPASS';
+
    Execute_priv, Repl_slave_priv, Repl_client_priv
}}
+
    ) ON mysql.user TO 'pma'@'localhost';
 
+
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
'''Where is phpmyadmin'''
+
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
 
+
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
in phpmyadmin 3.2.2-3 the file is missing /srv/http/ create this symlik
+
    ON mysql.tables_priv TO 'pma'@'localhost';
 
 
{{bc|ln -s /usr/share/webapps/phpMyAdmin/ /srv/http/phpmyadmin
 
 
}}
 
}}
  
'''Things you should change'''
+
In order use the bookmark and relation features, set the following permissions:
 +
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
  
controluser is set to controluser <br>
+
Re-login to ensure the new features are activated.
controlpass is set to password <br>
 
verbose is set to name_of_server
 
  
'''Sample 'config.inc.php' file'''
+
=== Enabling template caching ===
{{bc|1=
 
<?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 */
+
Edit {{ic|/etc/webapps/phpmyadmin/config.inc.php}} to add the line:
$i = 0;
 
  
/* Server ravi-test-mysql (http) [1] */
+
  $cfg['TempDir'] = '/tmp/phpmyadmin';
$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;
 
?>
 
}}
 

Latest revision as of 10:20, 12 May 2019

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 /etc/webapps/phpmyadmin/config.inc.php.

If the MySQL server is not on localhost, append the following line:

$cfg['Servers'][$i]['host'] = 'localhost';

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):

/etc/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 /etc/webapps/phpmyadmin/config.inc.php, uncomment (remove the leading "//"s), and change them to your desired credentials if needed:

/etc/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 /etc/webapps/phpmyadmin/config.inc.php to add the line:

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

See also