Difference between revisions of "PhpMyAdmin"

From ArchWiki
Jump to: navigation, search
m (changed /etc/webapps/phpMyAdmin/.htaccess to /etc/webapps/phpmyadmin/.htaccess (lower case phpmyadmin))
(Fixed "Option 3: subdirectory using alias")
 
(126 intermediate revisions by 52 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]]
 
[[fr:phpmyadmin]]
 +
[[ja:PhpMyAdmin]]
 +
[[ru:PhpMyAdmin]]
 +
[[tr:PhpMyAdmin]]
 +
[[zh-hans:PhpMyAdmin]]
 +
[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.
  
==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 ==
 
== Configuration ==
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>
+
You need to enable the {{ic|mysqli}} extension in PHP by editing {{ic|/etc/php/php.ini}} and uncommenting the following line:
cp /etc/webapps/phpmyadmin/apache.example.conf /etc/httpd/conf/extra/httpd-phpmyadmin.conf
+
extension=mysqli.so
</pre>
 
  
Add the following lines to {{Filename|/etc/httpd/conf/httpd.conf}}:
+
Optionally you can enable {{ic|bz2.so}} and {{ic|zip.so}} 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'' you use {{ic|open_basedir}} (it is not set by default), make sure that PHP can access {{ic|/etc/webapps}} by adding it to {{ic|open_basedir}} in {{ic|/etc/php/php.ini}}.}}
<pre>
 
echo -e "\nInclude conf/extra/httpd-phpmyadmin.conf" >> /etc/httpd/conf/httpd.conf
 
</pre>
 
  
=== Check php module configuration ===
+
===Apache===
 +
Set up Apache to use php as outlined in the [[LAMP#PHP|LAMP]] article.
  
Add the following lines to {{Filename|/etc/httpd/conf/httpd.conf}}:
+
Create the Apache configuration file:
<pre>
+
{{hc|/etc/httpd/conf/extra/phpmyadmin.conf|<nowiki>
# Use for PHP 5.x:
+
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
LoadModule php5_module        modules/libphp5.so
+
<Directory "/usr/share/webapps/phpMyAdmin">
AddHandler php5-script php
+
    DirectoryIndex index.php
</pre>
+
    AllowOverride All
 +
    Options FollowSymlinks
 +
    Require all granted
 +
</Directory>
 +
</nowiki>}}
  
Add index.php after "DirectoryIndex index.html"
+
And include it in {{ic|/etc/httpd/conf/httpd.conf}}:
<pre>
+
# phpMyAdmin configuration
# DirectoryIndex: sets the file that Apache will serve if a directory
+
Include conf/extra/phpmyadmin.conf
# is requested.
 
#
 
<IfModule dir_module>
 
    DirectoryIndex index.html index.php
 
</IfModule>
 
</pre>
 
  
=== Adjust access rights ===
+
{{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.}}
  
In {{Filename|/etc/webapps/phpmyadmin/.htaccess}}, comment out ''deny from all''. The line should look like this:
+
===Lighttpd===
<pre>
+
Configuring Lighttpd is similar to Apache. Make sure Lighttpd is setup to serve PHP files (see [[Lighttpd]]).
#deny from all
 
</pre>
 
  
Otherwise you'll get an error similar to "Error 403 - Access forbidden!" when you attempt to access your phpMyAdmin installation.
+
Make an alias for phpmyadmin in your Lighttpd config.
 
+
  alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")
=== Review apache phpmyadmin configuration ===
+
Then enable mod_alias, mod_fastcgi and mod_cgi in your config ( server.modules section )
  
Your {{Filename|/etc/httpd/conf/extra/httpd-phpmyadmin.conf}} should have the following information:
+
Restart Lighttpd and go to [http://localhost/phpmyadmin/].
<pre>
 
        Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
 
        <Directory "/usr/share/webapps/phpMyAdmin">
 
                AllowOverride All
 
                Options FollowSymlinks
 
                Order allow,deny
 
                Allow from all
 
                php_admin_value open_basedir "/srv/:/tmp/:/usr/share/webapps/:/etc/webapps:/usr/share/pear/"
 
        </Directory>
 
</pre>
 
  
You need the mcrypt (if you want phpmyadmin internal authentication) and mysql modules, so append the following to {{Filename|/etc/php/conf.d/custom.ini}}:
+
===Nginx===
  extension=mcrypt.so
 
  extension=mysql.so
 
  
=== Add blowfish_secret passphrase ===
+
Make sure to set up [[nginx#FastCGI]] with separate configuration file for PHP as shown in [[nginx#PHP configuration file]].
  
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) :
+
==== Option 1: subdomain ====
  
ERROR: The configuration file now needs a secret passphrase (blowfish_secret)
+
Using this method, you will access PhpMyAdmin as {{ic|phpmyadmin.<domain>}}.
  
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
+
You can setup a sub domain (or domain) with a server block such as:
  
$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
+
{{bc|<nowiki>
 +
server {
 +
    server_name    phpmyadmin.<domain.tld>;
 +
    root    /usr/share/webapps/phpMyAdmin;
 +
    index  index.php;
 +
    include php.conf;
 +
}
 +
</nowiki>}}
  
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:
+
==== Option 2: subdirectory using symlink ====
 
$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.
+
Using this method, you'll access PhpMyAdmin as {{ic|localhost/phpmyadmin}}, similarly to Apache.
  
==Accessing your phpMyAdmin installation==
+
To get PhpMyAdmin working with your [[nginx]] setup, first take note of the root of the server you want to use. Supposing it is {{ic|/srv/http}}, now create a symlink:
  
Finally your phpmyadmin installation is complete. Before start using it you need to restart your apache server by following command:
+
  # ln -s /usr/share/webapps/phpMyAdmin/ /srv/http/phpmyadmin
  
<pre>
+
==== Option 3: subdirectory using location ====
# /etc/rc.d/httpd restart
 
</pre>
 
  
You can access your phpmyadmin installation using the following url:
+
If for some reason you are unable to create a symlink in the root of the server or would just rather use location, you can use this example configuration.
  
<pre>
+
Using this method, you'll access PhpMyAdmin as {{ic|localhost/phpMyAdmin}}, similarly to Apache.
http://localhost/phpmyadmin/
 
or
 
http://localhost/phpmyadmin/index.php
 
</pre>
 
  
Note: 'localhost' is the hostname in your /etc/rc.conf file.
+
  location /phpMyAdmin {
 +
      root /usr/share/webapps;
 +
      index  index.php; 
 +
      try_files $uri $uri/ =404;
 +
      # Deny some static files
 +
      location ~ ^/phpMyAdmin/(README|LICENSE|ChangeLog|DCO)$ {
 +
          deny all;
 +
      }
 +
      # Deny .md files
 +
      location ~ ^/phpMyAdmin/(.+\.md)$ {
 +
          deny all;
 +
      }
 +
      # Deny some 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;
 +
      }
 +
  }
  
If you want to access it using:
+
==phpMyAdmin configuration==
 +
phpMyAdmin's configuration file is located at {{ic|/etc/webapps/phpmyadmin/config.inc.php}}. If you have a local MySQL server, it should be usable without making any modifications.
  
<pre>
+
If your MySQL server is not on the localhost, uncomment and edit the following line:
http://localhost/phpmyadmin
+
$cfg['Servers'][$i]['host'] = 'localhost';
</pre>
 
  
in '/etc/httpd/conf/extra/httpd-phpmyadmin.conf' change:
+
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'' user in {{ic|/usr/share/webapps/phpMyAdmin}} as follows:
 +
# cd /usr/share/webapps/phpMyAdmin
 +
# mkdir config
 +
# chgrp http config
 +
# chmod g+w config
  
<pre>
+
=== Add blowfish_secret passphrase ===
Alias /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) :
</pre>
 
  
to
+
ERROR: The configuration file now needs a secret passphrase (blowfish_secret)
  
<pre>
+
You need to add a unique password for the blowfish algorithm (which is used by phpMyAdmin to secure the authentication procedure) between the following {{ic|<nowiki>''</nowiki>}}. You can use any password generator for that matter, a key length of 32 is recommended.
Alias /phpmyadmin "/usr/share/webapps/phpMyAdmin"
 
</pre>
 
  
You should also read [http://bbs.archlinux.org/viewtopic.php?pid=632500 this thread].
+
{{hc|1=/etc/webapps/phpmyadmin/config.inc.php|2=$cfg['blowfish_secret'] = <nowiki>''</nowiki>; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */}}
  
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:
+
The error should go away if you refresh the phpmyadmin page.
 
 
<pre>
 
$cfg['Servers'][$i]['host'] = 'localhost';
 
</pre>
 
  
to your hostname specified in /etc/hosts and /etc/rc.conf under HOSTNAME.
+
=== Enabling Configuration Storage (optional) ===
 +
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.
  
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:
+
{{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.}}
  
<pre>
+
In {{ic|/etc/webapps/phpmyadmin/config.inc.php}}, uncomment (remove the leading "//"s on) these two lines, and change them to your desired credentials:
cd /usr/share/webapps/phpMyAdmin
+
{{bc|1=
sudo mkdir config
+
// $cfg['Servers'][$i]['controluser'] = 'pma';
sudo chgrp http config
+
// $cfg['Servers'][$i]['controlpass'] = 'pmapass';
sudo chmod g+w config
+
}}
</pre>
 
  
==Lighttpd Configuration==
+
You will need this information later, so keep it in mind.
  
The php setup for lighttpd is exactly the same as for apache.
+
Beneath the controluser setup section, uncomment these lines:
Make an alias for phpmyadmin in your lighttpd config.
+
{{bc|1=
  alias.url = ( "/phpmyadmin" => "/usr/share/webapps/phpMyAdmin/")
+
/* Storage database and tables */
Then enable mod_alias, mod_fastcgi and mod_cgi in your config ( server.modules section )
+
// $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';
 +
}}
  
Update open_basedir in /etc/php/php.ini and add "/usr/share/webapps/".
+
Next, create the user with the above details. Don't set any permissions for it just yet.
  open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/
+
{{note|If you can't login to phpmyadmin, make sure that your mysql server is started.}}
  
Make sure lighttpd is setup to serve php files, [[Lighttpd and FastCGI]]
+
===== creating phpMyAdmin database =====
 +
Using the phpMyAdmin web interface:
 +
Import {{ic|/usr/share/webapps/phpMyAdmin/sql/create_tables.sql}} from phpMyAdmin -> Import.
 +
'''or'''
 +
Using command line: {{ic|mysql -u root -p < /usr/share/webapps/phpMyAdmin/sql/create_tables.sql}}.
  
Restart lighttpd and browse to http://localhost/phpmyadmin/index.php
+
===== creating phpMyAdmin database user =====
 +
Now to apply the permissions to your controluser, in the [[MySQL|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:
 +
{{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';
 +
}}
  
==NGINX Configuration==
+
In order to take advantage of the bookmark and relation features, you will also need to give '''pma''' some additional permissions:
 +
{{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'''}}
 +
{{bc|GRANT SELECT, INSERT, UPDATE, DELETE ON <pma_db>.* TO 'pma'@'localhost';}}
  
Also similar to apache configuration (and Lighttpd, for that matter).
+
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.
  
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/
+
==Accessing your phpMyAdmin installation==
 +
Your phpMyAdmin installation is now complete. Before you start using it you need to restart Apache.
  
  sudo ln -s /usr/share/webapps/phpmyadmin /srv/http/<domain>/public_html/phpmyadmin
+
You can access your phpMyAdmin installation by going to http://localhost/phpmyadmin/
  
You can also setup a sub domain with a server block like so (if using php-fpm):
+
== Troubleshooting ==
  
  server {
+
=== Fixing open_basedir warning ===
          server_name    phpmyadmin.<domain.tld>;
 
          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...
+
If you see the following Warning when entering the homepage of PhpMyAdmin:
  
  sudo chown -R nginx:nginx /usr/share/webapps/phpmyadmin
+
{{bc|
 +
Warning in ./libraries/Config.class.php#1147
 +
file_exists(): open_basedir restriction in effect. File(./config.inc.php) is not within the allowed path(s): (/srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/)
 +
}}
  
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].
+
It means that phpmyadmin was not able to find where the {{ic|config.inc.php}} file is located.
  
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):
+
In order to fix that, you need to indicate the path in {{ic|/etc/php/php.ini}} of the {{ic|phpmyadmin}} directory containing the file, which should be {{ic|/etc/webapps}}, putting it at the end of the paths separated with a {{ic|:}} in the {{ic|open_basedir}} variable:
  
  fastcgi_param HTTPS on;
+
{{hc|head=/etc/php/php.ini|
 +
output=open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/''':/etc/webapps/'''
 +
}}
  
==Other (Older) information==
+
Once you have done that, [[restart]] {{ic|httpd.service}}.
  
This page holds a sample 'config.inc.php' file that you can place in the main phpMyAdmin directory so that it immediately starts working
+
Now refresh the page, and you should no longer have the warning.
  
'''Things you should do first'''
+
=== #2006 - MySQL server has gone away ===
  
Create a 'controluser', so that phpmyadmin can read from the main mysql database.
+
If, when trying to log into PhpMyAdmin, you encounter
 
+
#2006 - MySQL server has gone away
<pre>mysql -u root -pYOURROOTPASSWORD
+
mysql> grant usage on mysql.* to controluser@localhost identified by 'CONTROLPASS';
+
Connection for controluser as defined in your configuration failed.
</pre>
+
a fix seems to be to make sure you do not have SSL connection between PhpMyAdmin and MariaDB activated. Hence comment out or set to {{ic|false}} the following line:
 
+
{{hc|1=/etc/webapps/phpmyadmin/config.inc.php|2=
'''Where is phpmyadmin'''
+
$cfg['Servers'][$i]['ssl'] = true;
 
+
}}
in phpmyadmin 3.2.2-3 the file is missing /srv/http/ create this symlik
 
 
 
<pre>ln -s /usr/share/webapps/phpMyAdmin/ /srv/http/phpmyadmin
 
</pre>
 
 
 
'''Things you should change'''
 
 
 
controluser is set to controluser <br>
 
controlpass is set to password <br>
 
verbose is set to name_of_server
 
 
 
'''Sample 'config.inc.php' file'''
 
<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 */
 
$i = 0;
 
 
 
/* Server ravi-test-mysql (http) [1] */
 
$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 */
 
  
$cfg['LeftFrameLight'] = true;
+
{{Note|1=There surely must be a better fix since 'ssl = true' worked before. Also do not disable SSL if your PhpMyAdmin install is somehow not on the same server as MySQL!}}
$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 10:34, 3 September 2017

phpMyAdmin is a web-based tool to help manage MySQL databases using an Apache/PHP frontend. It requires a working LAMP setup.

Installation

Install the phpmyadmin package.

Configuration

PHP

You need to enable the mysqli extension in PHP by editing /etc/php/php.ini and uncommenting the following line:

extension=mysqli.so

Optionally you can enable bz2.so and zip.so for compression support.

Note: If you use open_basedir (it is not set by default), make sure that PHP can access /etc/webapps by adding it to open_basedir in /etc/php/php.ini.

Apache

Set up Apache to use php as outlined in the LAMP 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.

Lighttpd

Configuring Lighttpd is similar to Apache. Make sure Lighttpd is setup to serve PHP files (see Lighttpd).

Make an alias for phpmyadmin in your Lighttpd config.

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

Then enable mod_alias, mod_fastcgi and mod_cgi in your config ( server.modules section )

Restart Lighttpd and go to [1].

Nginx

Make sure to set up nginx#FastCGI with separate configuration file for PHP as shown in nginx#PHP configuration file.

Option 1: subdomain

Using this method, you will access PhpMyAdmin as phpmyadmin.<domain>.

You can setup a sub domain (or domain) with a server block such as:

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

Option 2: subdirectory using symlink

Using this method, you'll access PhpMyAdmin as localhost/phpmyadmin, similarly to Apache.

To get PhpMyAdmin working with your nginx setup, first take note of the root of the server you want to use. Supposing it is /srv/http, now create a symlink:

 # ln -s /usr/share/webapps/phpMyAdmin/ /srv/http/phpmyadmin

Option 3: subdirectory using location

If for some reason you are unable to create a symlink in the root of the server or would just rather use location, you can use this example configuration.

Using this method, you'll access PhpMyAdmin as localhost/phpMyAdmin, similarly to Apache.

 location /phpMyAdmin {
     root /usr/share/webapps;
     index   index.php;  
     try_files $uri $uri/ =404;
     # Deny some static files
     location ~ ^/phpMyAdmin/(README|LICENSE|ChangeLog|DCO)$ {
         deny all;
     }
     # Deny .md files
     location ~ ^/phpMyAdmin/(.+\.md)$ {
         deny all;
     }
     # Deny some 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;
     }
 }

phpMyAdmin configuration

phpMyAdmin's configuration file is located at /etc/webapps/phpmyadmin/config.inc.php. If you have a local MySQL server, it should be usable without making any modifications.

If your MySQL server is not on the localhost, uncomment and edit the following line:

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

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 user in /usr/share/webapps/phpMyAdmin as follows:

# cd /usr/share/webapps/phpMyAdmin
# mkdir config
# chgrp http config
# chmod g+w config

Add blowfish_secret passphrase

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)

You need to add a unique password for the blowfish algorithm (which is used by phpMyAdmin to secure the authentication procedure) between the following ''. You can use any password generator for that matter, a key length of 32 is recommended.

/etc/webapps/phpmyadmin/config.inc.php
$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

The error should go away if you refresh the phpmyadmin page.

Enabling Configuration Storage (optional)

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.

In /etc/webapps/phpmyadmin/config.inc.php, uncomment (remove the leading "//"s on) these two lines, and change them to your desired credentials:

// $cfg['Servers'][$i]['controluser'] = 'pma';
// $cfg['Servers'][$i]['controlpass'] = 'pmapass';

You will need this information later, so keep it in mind.

Beneath the controluser setup section, uncomment these lines:

/* 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';

Next, create the user with the above details. Don't set any permissions for it just yet.

Note: If you can't login to phpmyadmin, make sure that your mysql server is started.
creating phpMyAdmin database

Using the phpMyAdmin web interface: Import /usr/share/webapps/phpMyAdmin/sql/create_tables.sql from phpMyAdmin -> Import. or Using command line: mysql -u root -p < /usr/share/webapps/phpMyAdmin/sql/create_tables.sql.

creating phpMyAdmin database user

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:

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:

Note: as long as you did not change the value of $cfg['Servers'][$i]['pmadb'] in /etc/webapps/phpmyadmin/config.inc.php, then <pma_db> should be phpmyadmin
GRANT SELECT, INSERT, UPDATE, DELETE ON <pma_db>.* TO 'pma'@'localhost';

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.

Accessing your phpMyAdmin installation

Your phpMyAdmin installation is now complete. Before you start using it you need to restart Apache.

You can access your phpMyAdmin installation by going to http://localhost/phpmyadmin/

Troubleshooting

Fixing open_basedir warning

If you see the following Warning when entering the homepage of PhpMyAdmin:

Warning in ./libraries/Config.class.php#1147
file_exists(): open_basedir restriction in effect. File(./config.inc.php) is not within the allowed path(s): (/srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/)

It means that phpmyadmin was not able to find where the config.inc.php file is located.

In order to fix that, you need to indicate the path in /etc/php/php.ini of the phpmyadmin directory containing the file, which should be /etc/webapps, putting it at the end of the paths separated with a : in the open_basedir variable:

/etc/php/php.ini
open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/:/usr/share/webapps/:/etc/webapps/

Once you have done that, restart httpd.service.

Now refresh the page, and you should no longer have the warning.

#2006 - MySQL server has gone away

If, when trying to log into PhpMyAdmin, you encounter

#2006 - MySQL server has gone away

Connection for controluser as defined in your configuration failed.

a fix seems to be to make sure you do not have SSL connection between PhpMyAdmin and MariaDB activated. Hence comment out or set to false the following line:

/etc/webapps/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['ssl'] = true;
Note: There surely must be a better fix since 'ssl = true' worked before. Also do not disable SSL if your PhpMyAdmin install is somehow not on the same server as MySQL!