首頁 » PHP » 9. useful php project

phpmyadmin

由 datahunter 在 五, 17/08/2012 - 12:36 發表

最後更新: 2018-12-07

目錄

  • Version
  • login 方式
  • Disable AJAX popup
  • pmadb
  • phpMyAdmin 連不同 DB
  • Enable PhpMyAdmin 's Extra Features
  • HTTP to HTTPS
  • Show Full Field
  • Doc

 

PMA & PHP Version

 

  • 5.1     # PHP 8.0+
  • 4.6     # PHP 5.5+ is required
  • 4.5     # PHP 5.5+ is required
  • 4.4     # PHP 5.4+ is required

 

安裝

 

DL Page

https://www.phpmyadmin.net/downloads/

# All file

https://www.phpmyadmin.net/files/

Install

mkdir /usr/src/phpmyadmin; cd /usr/src/phpmyadmin

V=5.2.1

LINK=https://files.phpmyadmin.net/phpMyAdmin/$V/phpMyAdmin-${V}-english.tar.xz

wget $LINK

tar -Jxf phpMyAdmin-${V}-english.tar.xz         #  xz 有更大壓縮比

mv phpMyAdmin-${V}-english /usr/share        # 保持原目錄結構加 soft link

ln -s /usr/share/phpMyAdmin-${V}-english /usr/share/phpMyAdmin

cd /usr/share/phpMyAdmin

dnf install pwgen -y

cp config.sample.inc.php config.inc.php

chmod 000 setup examples

修改 config.inc.php

$cfg['blowfish_secret'] = ''; // 32 chars long, 可以用 `pwgen 32 1` 獲得

vi /etc/httpd/conf.d/phpmyadmin.conf

Apache 2.4 Config

# Apache 2.4

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin
Alias /pma        /usr/share/phpMyAdmin

<Directory /usr/share/phpMyAdmin/>
  AddDefaultCharset UTF-8
  AuthType Basic
  AuthName "Restricted Area"
  AuthBasicProvider file
  AuthUserFile htpasswd/phpmyadmin.htpasswd
  <RequireAny>
    Require ip 127.0.0.1
    #Require ip x.x.x.x
    Require valid-user
  </RequireAny>
</Directory>

<Directory /usr/share/phpMyAdmin/setup/>
  <RequireAny>
    Require ip 127.0.0.1
    #Require ip x.x.x.x
  </RequireAny>
</Directory>

<Directory /usr/share/phpMyAdmin/libraries/>
  Require all denied
</Directory>

Apache 2.2 Config

# Apache 2.2

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin
Alias /pma        /usr/share/phpMyAdmin

<Directory /usr/share/phpMyAdmin/>
   AddDefaultCharset UTF-8
   AuthType Basic
   AuthName "Restricted Area"
   AuthBasicProvider file
   AuthUserFile htpasswd/phpmyadmin.htpasswd
   Order Deny,Allow
   Deny from All
   Allow from 127.0.0.1
</Directory>

<Directory /usr/share/phpMyAdmin/setup/>
    Order Deny,Allow
    Deny from All
    Allow from 127.0.0.1
</Directory>

<Directory /usr/share/phpMyAdmin/libraries/>
    Deny from All
</Directory>

 

設定

 

設定檔

config.inc.php

Default configuration (不建議修改它)

/usr/share/phpMyAdmin/libraries/config.default.php

login 方式:

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';     // 要設定好 "$cfg['blowfish_secret']" 才用到

如果沒設定 blowfish_secret, 就會有 error: "The secret passphrase in configuration (blowfish_secret) is too short."

此外, blowfish_secret 要有 32 位長

// MySettings
$cfg['LoginCookieValidity'] = 1800;

lang:

# Default language to use, if not browser-defined or user-defined

$cfg['DefaultLang']='en';

# Force: always use this language

$cfg['Lang'] = 'en';

Disable AJAX popup:

$cfg['AjaxEnable'] = false;

SSL

# force using https while accessing phpMyAdmin
# Default value: false
$cfg['ForceSSL'] = false;

# enable SSL for the connection between phpMyAdmin and the MySQL server
$cfg['Servers'][$i]['ssl'] = false;

 

pmadb

 

refers to the phpMyAdmin configuration storage

used internally by phpMyAdmin for additional features
(such as bookmarking queries and change tracking).

The setting is left empty by default, meaning it is disabled.

config.inc.php

$cfg['Servers'][$i]['pmadb']

pmadb schema

examples/create_tables.sql

DOC

https://wiki.phpmyadmin.net/pma/Configuration_storage

 

 

localhost 與 127.0.0.1

 

Version: phpMyAdmin-4.6.6

Error

mysqli_real_connect(): (HY000/2002): No such file or directory

測試

/usr/share/phpmyadmin/config.inc.php

//$cfg['Servers'][$i]['host'] = 'localhost';    // 此設定連不到
//$cfg['Servers'][$i]['host'] = '127.0.0.1';  // 此設定連到

原因

On Unix, MySQL programs treat the host name localhost specially, in a way that is
likely different from what you expect compared to other network-based programs.
For connections to localhost, MySQL programs attempt to connect to the local server
by using a Unix socket file. This occurs even if a --port or -P option is given to
specify a port number. To ensure that the client makes a TCP/IP connection to the
local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP
address or name of the local server. You can also specify the connection protocol
explicitly, even for localhost, by using the --protocol=TCP option.

解決方案:

grep socket /etc/my.cnf

socket=/var/lib/mysql/mysql.sock

/etc/php.ini

mysqli.default_socket=/var/lib/mysql/mysql.sock

 

phpMyAdmin 連不同 DB

 

/**
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;


/**
 * Second server
 */
 
$i++;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '172.17.0.2';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

 

Enable PhpMyAdmin 's Extra Features

 

[1] 建立 Table 及 User A/C

mysql phpmyadmin -p < /usr/share/phpMyAdmin/sql/create_tables.sql

CREATE USER 'pma'@'localhost' IDENTIFIED BY '***';

GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'pma'@'localhost' WITH GRANT OPTION;

[2] 設定

// leave blank for no X support

$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = '***';
$cfg['Servers'][$i]['pmadb']       = 'phpmyadmin';

// 在 Home 多了 Appearance Settings (Manage your settings)

$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';

// Save 低某 Table 內 hide 某些 Column

$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';

// Manage your settings -> Navigation panel

$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';

 

Nginx Setting

 

mkdir /etc/nginx/snippets

ln -s /usr/share/phpMyAdmin /usr/share/pma   # 如果是透過 yum 安裝

snippets/phpmyadmin.conf

location /pma {
    alias /usr/share/pma;
    index index.php index.html index.htm;

    # Deny .ht* files
    location ~ /pma/\.ht { deny all; }

    # Deny static files
    location ~ /pma/(README|LICENSE|ChangeLog|DCO)$ { deny all; }
    # Deny .md files
    location ~ /pma/(.+\.md)$ { deny all;}

    # Deny setup directories
    location ~ /pma/(doc|sql|setup|libraries|locale|test)/ { deny all; }

    # Block access to default directories and files under these directories
    location ~ ^/pma/(bin|config|installer|logs|SQL|temp|vendor)($|/.*) { deny all; }

    #FastCGI config for pma
    location ~ /pma/.+\.php$ {
        if ($fastcgi_script_name ~ /pma/(.+\.php.*)$) {
            set $valid_fastcgi_script_name $1;
        }
        include fastcgi_params;
        # 看 local 的設定, default 9000
        fastcgi_pass 127.0.0.1:9056;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME /usr/share/pma/$valid_fastcgi_script_name;
    }
}

 

Tips

 

Browse 某 Table 時出現 "Notice"

Notice in ./libraries/dbi/DBIMysqli.class.php#523
 Undefined offset: 245

$cfg['SendErrorReports'] = 'never';

 

HTTP to HTTPS

 

redirect http to https

# http -> https
RewriteEngine On
RewriteCond %{HTTPS} off
RewriteCond %{REQUEST_URI} ^/pma                [OR,NC]
RewriteCond %{HTTPS} off
RewriteCond %{REQUEST_URI} ^/phpmyadmin         [OR,NC]
RewriteRule (.*) https://%{HTTP_HOST}/pma/      [R=301,L]

 * 純一去 /pma/

 

Show Full Field

 

After Search

Click on "+" Options

Select "Full texts" and click "Go"

 

Doc

 

http://docs.phpmyadmin.net/en/latest/config.html

 

»

Creative Commons license icon Creative Commons license icon