Trying to connect phpMyAdmin to remote mySQL server ( 2002: can't connect )

Question

Trying to get phpMyAdmin to talk to a remote mySQL server. The config is below and there is already a user set up in mySQL DB to be able to log in from the specified host that PMA sits on. Hosting is provided by Rackspace (Rightscale) and both cloud servers behind the same firewall.

[config.inc.php]

<?php

  $cfg['blowfish_secret'] = '';
  $i = 0;

  $i++;
  $cfg['Servers'][$i]['host']          = 'XX.XX.XX.XX';   // MySQL hostname or IP address
  $cfg['Servers'][$i]['port']          = '';                 // MySQL port - leave blank for default port
  $cfg['Servers'][$i]['socket']        = '';                 // Path to the socket - leave blank for default socket
  $cfg['Servers'][$i]['connect_type']  = 'tcp';              // How to connect to MySQL server ('tcp' or 'socket')
  $cfg['Servers'][$i]['extension']     = 'mysql';            // The php MySQL extension to use ('mysql' or 'mysqli')
  $cfg['Servers'][$i]['compress']      = FALSE;              // Use compressed protocol for the MySQL connection
                                                             // (requires PHP >= 4.3.0)
  $cfg['Servers'][$i]['controluser']   = '';                 // MySQL control user settings
                                                             // (this user must have read-only
  $cfg['Servers'][$i]['controlpass']   = '';                 // access to the "mysql/user"
                                                             // and "mysql/db" tables).
                                                             // The controluser is also
                                                             // used for all relational
                                                             // features (pmadb)
  $cfg['Servers'][$i]['auth_type']     = 'config';           // Authentication method (config, http or cookie based)?
  $cfg['Servers'][$i]['user']          = 'USERNAME';              // MySQL user
  $cfg['Servers'][$i]['password']      = 'PASSWORD';  // MySQL password (only needed
                                                             // with 'config' auth_type)
  $cfg['Servers'][$i]['only_db']       = '';                 // If set to a db-name, only
                                                             // this db is displayed in left frame
                                                             // It may also be an array of db-names, where sorting order is relevant.
  $cfg['Servers'][$i]['hide_db']       = '';                 // Database name to be hidden from listings
  $cfg['Servers'][$i]['verbose']       = '';                 // Verbose name for this host - leave blank to show the hostname

  $cfg['Servers'][$i]['pmadb']         = '';                 // Database used for Relation, Bookmark and PDF Features
                                                             // (see scripts/create_tables.sql)
                                                             //   - leave blank for no support
                                                             //     DEFAULT: 'phpmyadmin'
  $cfg['Servers'][$i]['bookmarktable'] = '';                 // Bookmark table
                                                             //   - leave blank for no bookmark support
                                                             //     DEFAULT: 'pma_bookmark'
  $cfg['Servers'][$i]['relation']      = '';                 // table to describe the relation between links (see doc)
                                                             //   - leave blank for no relation-links support
                                                             //     DEFAULT: 'pma_relation'
  $cfg['Servers'][$i]['table_info']    = '';                 // table to describe the display fields
                                                             //   - leave blank for no display fields support
                                                             //     DEFAULT: 'pma_table_info'
  $cfg['Servers'][$i]['table_coords']  = '';                 // table to describe the tables position for the PDF schema
                                                             //   - leave blank for no PDF schema support
                                                             //     DEFAULT: 'pma_table_coords'
  $cfg['Servers'][$i]['pdf_pages']     = '';                 // table to describe pages of relationpdf
                                                             //   - leave blank if you don't want to use this
                                                             //     DEFAULT: 'pma_pdf_pages'
  $cfg['Servers'][$i]['column_info']   = '';                 // table to store column information
                                                             //   - leave blank for no column comments/mime types
                                                             //     DEFAULT: 'pma_column_info'
  $cfg['Servers'][$i]['history']       = '';                 // table to store SQL history
                                                             //   - leave blank for no SQL query history
                                                             //     DEFAULT: 'pma_history'
  $cfg['Servers'][$i]['verbose_check'] = TRUE;               // set to FALSE if you know that your pma_* tables
                                                             // are up to date. This prevents compatibility
                                                             // checks and thereby increases performance.
  $cfg['Servers'][$i]['AllowRoot']     = TRUE;               // whether to allow root login
  $cfg['Servers'][$i]['AllowDeny']['order']                  // Host authentication order, leave blank to not use
                                       = '';
  $cfg['Servers'][$i]['AllowDeny']['rules']                  // Host authentication rules, leave blank for defaults
                                       = array();

Please let me know if you need anymore info.

-- Malcolm

is there an error? Have you verified that the mysql server is actually listening on the ip address you are expecting? For instance, can you `telnet XX.XX.XX.XX 3306` and actually get a connection? — stew, Feb 08 '12 at 17:58
Yes I am getting a "#2002 Cannot log in to the MySQL server" when I try to login. Command line works. When I type in: mysql -u USERNAME -p -h HOST from the terminal I am able to log in correctly etc. — Malcolm Jones, Feb 08 '12 at 18:39
Have you configured the access privileges on the host where MySQL is running? — Sudheer, Feb 08 '12 at 17:42

score 2 · Answer 1 · answered Sep 23 '14 at 06:57

2

Checked it by replacing "xx.xx.xx.xx" with ip address of remote mysql server and make sure database is allowed to access through your ip address.

$cfg['Servers'][$i]['host'] = 'XX.XX.XX.XX';

answered Sep 23 '14 at 06:57

Innovator

507
2
11

score 0 · Answer 2 · answered Jul 23 '14 at 23:25

There are a few things that could be at play:

The app itself is not using the same source address as the one you're connecting to the MySQL server with from the command line. If the server and app are local to one-another, it could be a simple mix-up of 127.0.0.1 vs localhost, which MySQL treats as different hosts and thus they need different permission masks. Similarly, if your webapp is SNATed behind a firewall, it's very likely MySQL is seeing a different source IP.
Another common source of pain is with using DNS hostnames in privilege masks. If you are doing this (e.g. 'user'@'foo.bar.com') and the DNS resolution fails or your forward/reverse records don't match, MySQL won't allow the connection.

If neither of these are the case, you could also try temporarily loosening the permissions to 'user'@'%' to see if this alleviates the issue.

Trying to connect phpMyAdmin to remote mySQL server ( 2002: can't connect )

2 Answers2