Difference between revisions of "PhpMyAdmin"

From ArchWiki
Jump to: navigation, search
(Added sample config file)
 
 
(227 intermediate revisions by 92 users not shown)
Line 1: Line 1:
This page holds a sample 'config.inc.php' file that you can place in the main phpMyAdmin directory so that it immediately starts working
+
{{lowercase title}}
 +
[[Category:Web admin interfaces]]
 +
[[cs:PhpMyAdmin]]
 +
[[es:PhpMyAdmin]]
 +
[[fr:phpmyadmin]]
 +
[[ja:PhpMyAdmin]]
 +
[[ru:PhpMyAdmin]]
 +
[[zh-hans:PhpMyAdmin]]
 +
[http://www.phpmyadmin.net/ phpMyAdmin] is a web-based tool to help manage MySQL databases using an Apache/PHP frontend.
  
=Things you should do first=
+
== Installation ==
Create a 'controluser', so that phpmyadmin can read from the main mysql database.
 
  
<pre>mysql -u root -pYOURROOTPASSWORD
+
[[Install]] the {{Pkg|phpmyadmin}} package.
mysql> grant usage on mysql.* to controluser@localhost identified by 'CONTROLPASS';
 
</pre>
 
  
=Things you should change=
+
== Running ==
  
controluser is set to controluser <br>
+
===PHP===
controlpass is set to password <br>
+
Make sure the PHP [[PHP#MySQL/MariaDB|mysql]] extension(s) have been enabled.
verbose is set to name_of_server
 
  
=Sample 'config.inc.php' file=
+
Optionally you can enable {{ic|1=extension=bz2}} and {{ic|1=extension=zip}} for compression support.
<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 */
+
{{Note|If {{ic|open_basedir}} has been set, make sure to include {{ic|/etc/webapps}} to {{ic|open_basedir}} in {{ic|/etc/php/php.ini}}.}}
$i = 0;
 
  
/* Server ravi-test-mysql (http) [1] */
+
===Apache===
$i++;
+
Set up Apache to use PHP as outlined in the [[Apache HTTP Server#PHP]] article.
$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 */
+
Create the Apache configuration file:
 +
{{hc|/etc/httpd/conf/extra/phpmyadmin.conf|2=
 +
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
 +
<Directory "/usr/share/webapps/phpMyAdmin">
 +
    DirectoryIndex index.php
 +
    AllowOverride All
 +
    Options FollowSymlinks
 +
    Require all granted
 +
</Directory>
 +
}}
  
$cfg['LeftFrameLight'] = true;
+
And include it in {{ic|/etc/httpd/conf/httpd.conf}}:
$cfg['LeftFrameDBTree'] = true;
+
 
$cfg['LeftFrameDBSeparator'] = '_';
+
# phpMyAdmin configuration
$cfg['LeftFrameTableSeparator'] = '__';
+
Include conf/extra/phpmyadmin.conf
$cfg['LeftFrameTableLevel'] = 1;
+
 
$cfg['LeftDisplayLogo'] = true;
+
{{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 a [[Secure Shell#Encrypted SOCKS tunnel]].}}
$cfg['LeftDisplayServers'] = false;
+
 
$cfg['DisplayServersList'] = false;
+
After making changes to the Apache configuration file, [[restart]] {{ic|httpd.service}}.
$cfg['DisplayDatabasesList'] = 'auto';
+
 
$cfg['LeftPointerEnable'] = true;
+
===Lighttpd===
$cfg['DefaultTabServer'] = 'main.php';
+
Configuring [[Lighttpd]], make sure it is able to serve PHP files and {{ic|mod_alias}} has been enabled.
$cfg['DefaultTabDatabase'] = 'db_structure.php';
+
 
$cfg['DefaultTabTable'] = 'tbl_structure.php';
+
Add the following alias for PhpMyAdmin to the config:
$cfg['LightTabs'] = false;
+
 
$cfg['ErrorIconic'] = true;
+
  alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")
$cfg['MainPageIconic'] = true;
+
 
$cfg['ReplaceHelpImg'] = true;
+
===Nginx===
$cfg['NavigationBarIconic'] = 'both';
+
 
$cfg['PropertiesIconic'] = 'both';
+
Make sure to set up [[nginx#FastCGI]].
$cfg['BrowsePointerEnable'] = true;
+
 
$cfg['BrowseMarkerEnable'] = true;
+
==== Subdomain ====
$cfg['ModifyDeleteAtRight'] = false;
+
 
$cfg['ModifyDeleteAtLeft'] = true;
+
Set up a separate configuration file for PHP as shown in [[nginx#PHP configuration file]] and use a server block such as:
$cfg['RepeatCells'] = 100;
+
 
$cfg['DefaultDisplay'] = 'horizontal';
+
{{bc|
$cfg['TextareaCols'] = 40;
+
server {
$cfg['TextareaRows'] = 7;
+
    server_name    phpmyadmin.<domain.tld>;
$cfg['LongtextDoubleTextarea'] = true;
+
    root    /usr/share/webapps/phpMyAdmin;
$cfg['TextareaAutoSelect'] = false;
+
    index  index.php;
$cfg['CharEditing'] = 'input';
+
    include php.conf;
$cfg['CharTextareaCols'] = 40;
+
}
$cfg['CharTextareaRows'] = 2;
+
}}
$cfg['CtrlArrowsMoving'] = true;
+
 
$cfg['DefaultPropDisplay'] = 'horizontal';
+
==== Subdirectory using location ====
$cfg['InsertRows'] = 2;
+
 
$cfg['EditInWindow'] = true;
+
This will allow access to PhpMyAdmin as in {{ic|<nowiki>https://domain.tld/phpMyAdmin</nowiki>}}:
$cfg['QueryWindowHeight'] = 310;
+
 
$cfg['QueryWindowWidth'] = 550;
+
{{hc|/etc/nginx/sites-available/domain.tld|<nowiki>
$cfg['QueryWindowDefTab'] = 'sql';
+
location /phpMyAdmin {
$cfg['ForceSSL'] = false;
+
  server_name domain.tld;
$cfg['ShowPhpInfo'] = false;
+
  listen 443 ssl http2;
$cfg['ShowChgPassword'] = false;
+
  root /srv/http/domain.tld;
$cfg['AllowArbitraryServer'] = false;
+
  index index.php; 
$cfg['LoginCookieRecall'] = 'something';
+
 
$cfg['LoginCookieValidity'] = 1800;
+
  location / {
?>
+
      try_files $uri $uri/ =404;
</pre>
+
  }
 +
 
 +
  # 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)$ {
 +
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
 +
      fastcgi_pass  unix:/run/php-fpm/php-fpm.sock;
 +
      fastcgi_index  index.php;
 +
      include        fastcgi.conf;
 +
  }
 +
}
 +
</nowiki>}}
 +
 
 +
== Configuration ==
 +
The main configuration file is located at {{ic|/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 {{ic|/usr/share/webapps/phpMyAdmin}} is writable for the {{ic|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 an 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)'':
 +
 
 +
{{hc|1=/etc/webapps/phpmyadmin/config.inc.php|2=
 +
$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 {{ic|controluser}}, and '''pmapass''' as the {{ic|controlpass}}.}}
 +
 
 +
In {{ic|/etc/webapps/phpmyadmin/config.inc.php}}, uncomment (remove the leading "//"s), and change them to your desired credentials if needed:
 +
{{hc|/etc/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';
 +
 
 +
/* 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 {{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.
 +
 
 +
===== Setup database user =====
 +
To apply the required permissions for {{ic|controluser}}, execute the following query:
 +
 
 +
{{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.}}
 +
 
 +
{{bc|
 +
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 templates catching ===
 +
 
 +
Edit {{ic|/etc/webapps/phpmyadmin/config.inc.php}} to add the line:
 +
 
 +
  $cfg['TempDir'] = '/tmp/phpmyadmin';
 +
 
 +
== See also ==
 +
 
 +
* [[Wikipedia:phpMyAdmin|Wikipedia]]

Latest revision as of 11:09, 5 July 2018

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 /etc/webapps to open_basedir in /etc/php/php.ini.

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 a Secure Shell#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.

Subdomain

Set up a separate configuration file for PHP as shown in nginx#PHP configuration file and use a server block such as:

server {
    server_name     phpmyadmin.<domain.tld>;
    root    /usr/share/webapps/phpMyAdmin;
    index   index.php;
    include php.conf;
}

Subdirectory using location

This will allow access to PhpMyAdmin as in https://domain.tld/phpMyAdmin:

/etc/nginx/sites-available/domain.tld
location /phpMyAdmin {
   server_name domain.tld;
   listen 443 ssl http2;
   root /srv/http/domain.tld;
   index index.php;  
  
   location / {
      try_files $uri $uri/ =404;
   }

   # 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)$ {
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
      fastcgi_pass   unix:/run/php-fpm/php-fpm.sock;
      fastcgi_index  index.php;
      include        fastcgi.conf;
   }
}

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 an 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 templates catching

Edit /etc/webapps/phpmyadmin/config.inc.php to add the line:

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

See also