[CLUG-tech] Access to MySQL database on remote server.

Izak Burger isburger at gmail.com
Sun Feb 19 17:27:09 SAST 2006


On 2/18/06, Paul Scott <pscott at uwc.ac.za> wrote:
> In the my.ini file uncomment the part that says "skip-networking".

No, if you uncomment it it will skip networking, which means it
completely disables the networking part.  You want to comment it out
so that it does not skip networking and instead enables it (the
default upstream setting). Many distros disable it by default, for
security reasons.  Please set a password for the root mysql user
before enabling this.

You also have to grant privileges for the host you will be logging on
from, for example:

   GRANT ALL PRIVILEGES ON mydb.* to me at hostname identified by 'pw';

Other things to keep in mind with mysql:  It uses both the username
and hostname to determine how you should authenticate.  For example, I
can say:

   GRANT ALL PRIVILEGES ON mydb.* to me at localhost identified by 'localpw';
   GRANT ALL PRIVILEGES ON mydb.* to me at remotehost identified by 'rempw';

When I log in from localhost, mysql will expect the password
"localpw", but if I log in from the remote host it expects the
password "rempw".

You also have to remember to flush the privileges cache after making
changes to users:

   FLUSH PRIVILEGES;

One last trick, mysql understands the SQL % wildcard character when it
evaluates hostnames.  You can log into the mysql database (that is the
database by the name "mysql"), and you will find a table called "user"
therein, of which Host, User and Password are the first three columns.
 If you simply change the hostname to % the username/password pair
will apply for all hosts (or it did last time I tried it, which
admittedly was in 2001).

I seem to recall some issues with DNS as well.  Specifically, on some
hosts the IP 127.0.0.1 resolves to localhost.localdomain, because the
entry in /etc/hosts lists the fqdn first and the shorter "localhost"
alias second.  On those hosts I found that I either had to use
localhost.localdomain in my GRANT statements, or I had to swap the
names arround in /etc/hosts.  This sort of tells me that whatever
hostname you use also need to map correctly in DNS or you might get
all sorts of weirdness.

regards,
Izak


More information about the Clug-tech mailing list