Re: mysql configuration
On 05/01/2015 08:37 AM, Chris Kottaridis wrote:
I am running on a fedora 21 machine. I installed mysqld from RPM. I
believe I have the 3306 port available thru the firewall and I used
systemctl to get the daemon started and it seems to be up and running. I
made no other configuration changes other then what the RPM installation
did.
I am running in a vmware virtual host and it has 2 virtual ethernet
cards attached, ens33 and ens37. I have assigned IP addresses accordingly:
ens33: 192.168.200.131
ens37: 192.168.223.63
The hostname is fed21.cjk.net.
In DNS fed21.cjk.net is assigned to 192.168.223.63.
I have no hostname assoicated with 192.168.200.131.
I noticed in the mysqld.log file:
-------------------------------------------------------
2015-05-01 07:07:22 1393 [Note] Server hostname (bind-address): '*';
port: 3306
2015-05-01 07:07:22 1393 [Note] IPv6 is available.
2015-05-01 07:07:22 1393 [Note] - '::' resolves to '::';
2015-05-01 07:07:22 1393 [Note] Server socket created on IP: '::'.
2015-05-01 07:07:22 1393 [Note] Event Scheduler: Loaded 0 events
2015-05-01 07:07:22 1393 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.24' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL
Community Server (GPL)
2015-05-01 07:09:05 1393 [Warning] Hostname 'fed21.cjk.net' does not
resolve to '192.168.200.131'.
2015-05-01 07:09:05 1393 [Note] Hostname 'fed21.cjk.net' has the
following IP addresses:
2015-05-01 07:09:05 1393 [Note] - 192.168.223.63
-----------------------------------------------------
Which as mentioned above the hostname is indeed mapped to 192.168.223.63
and not 192.168.200.131.
When I try to run this script:
==============================================
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=test;host=127.0.0.1",
"joe", "joe's password",
{'RaiseError' => 1});
# Disconnect from the database.
$dbh->disconnect();
exit;
===============================================
$ ./nosrcapp.pl
DBI connect('database=test;host=127.0.0.1','joe',...) failed: Host
'192.168.200.131' is not allowed to connect to this MySQL server at
./nosrcapp.pl line 8.
You can see I get an error immediately from the mysqld server that it
doesn't like 192.168.200.131, which is a little weird because I am
accessing through 127.0.0.1. The script didn't even get a chance to send
the login request. You can see if I telnet in the server immediately
sends the message on connection:
$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
HHost '192.168.200.131' is not allowed to connect to this MySQL
serverConnection closed by foreign host.
$
I set wireshark to only look at the loopback interface and it shows:
While it seems weird that when sending to 127.0.0.1 the system uses a
source address of 192.168.200.131 instead of 127.0.0.1, but seems to
know to respond to 127.0.0.1, the only question I am interested in is:
What mysqld configuration am I missing that is not allowing
192.168.200.131 access ?
Of secondary importance is if there is a way to tell mysqld to expect
the hostname to be mapped to 192.168.223.63 instead of 192.168.200.31,
but if that doesn't affect functionality then I am much less concerned
about that.
This is a local environment so I have no cncerns about access and I
essentially want any address to be able to access from either the
192.168.200 network or 192.168.223 network as well as through the loopback.
What mysqld configuration am I missing ?
Hello Chris,
No expert here but I've been down this road a couple times ;)
Mysql maintains its own database (called mysql) that controls access to
its databases, tables, columns, etc through a permission system. I'm
guessing that Joe hasn't been granted any of those permissions.
The command you need is "GRANT". In its simplest form it looks
something like this:
GRANT permissions ON database@host TO user IDENTIFIED BY 'password';
e.g.
GRANT select,insert,update,delete ON test@localhost TO joe IDENTIFIED BY
"joe's password";
The GRANT command holds the keys to the kingdom. You definitely want to
google on "mysql and grant" before you start using it. After you've
experimented with the GRANT command remember to remove any test
privileges from the mysql database. Whatever you do, don't lose those
GRANT settings or you won't be able to get back in unless you use root
access. (You did remember to set a root password, didn't you?)
Good luck,
Mike Wright