CentOS - Mar 2010 - Connect to MySQL monitor on another host in the LAN ?

Hi,

I'm running a medical application on a local network, using MySQL as a 
database. The application can also connect to remote hosts, so the idea 
is to install it on one machine acting as a "server", and then install
it on the other machines on the LAN, but configure these to use the 
database on the "server". I guess before doing that, I'll better
try and
get comfortable with connecting remotely using the MySQL monitor itself, 
that is, without any application or some other frontend.

Up until now, I've only configured MySQL in two situations. Either on 
standalone desktop workstations, or on a web server (LAMP).

After installing MySQL (yum install mysql-server), the default setup has 
a few accounts without any password. First thing I usually do (correct 
me if this is wrong) is clean up these accounts and only keep one main 
root at localhost account. Something like this :

[root at raymonde:~] # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the
buffer.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host, password from user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | raymonde  |          |
| root | 127.0.0.1 |          |
|      | localhost |          |
|      | raymonde  |          |
+------+-----------+----------+
5 rows in set (0.00 sec)

mysql> delete from user where user = '';
Query OK, 2 rows affected (0.01 sec)

mysql> delete from user where host = 'raymonde';
Query OK, 1 row affected (0.00 sec)

mysql> delete from user where host = '127.0.0.1';
Query OK, 1 row affected (0.01 sec)

mysql> select user, host, password from user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
+------+-----------+----------+
1 row in set (0.00 sec)

mysql>  set password for root at localhost = password('********');
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password from user;
+------+-----------+------------------+
| user | host      | password         |
+------+-----------+------------------+
| root | localhost | 2d97271980b60f82 |
+------+-----------+------------------+
1 row in set (0.00 sec)

mysql> quit;

So much for the standalone setup.

Now what would be an orthodox (and somewhat secure) configuration to be 
able to connect to this MySQL server on 'raymonde' from another machine 
in the LAN ? Do I have to (re)create a root at raymonde MySQL account and 
eventually give it the same password than root at localhost ?

Cheers,

Niki

On 3/23/2010 10:56 AM, Niki Kovacs wrote:
> Hi,
>
> I'm running a medical application on a local network, using MySQL as a
> database. The application can also connect to remote hosts, so the idea
> is to install it on one machine acting as a "server", and then
install
> it on the other machines on the LAN, but configure these to use the
> database on the "server". I guess before doing that, I'll
better try and
> get comfortable with connecting remotely using the MySQL monitor itself,
> that is, without any application or some other frontend.
>
> Up until now, I've only configured MySQL in two situations. Either on
> standalone desktop workstations, or on a web server (LAMP).
>
> After installing MySQL (yum install mysql-server), the default setup has
> a few accounts without any password. First thing I usually do (correct
> me if this is wrong) is clean up these accounts and only keep one main
> root at localhost account.
Depending on the application, you'd usually want to create one or many 
users for each application with privileges limited to that application's 
database and only use the root account to create the new database(s) and 
manage users.  If it is a web app or something similar where individual 
users don't connect directly to the database you'd have one user in 
mysql for that app and a matching config in the web server.  If 
individual desktop clients will connect, you may want individual users 
in mysql for each connection so you can control the access levels for 
each one individually.
>
> Now what would be an orthodox (and somewhat secure) configuration to be
> able to connect to this MySQL server on 'raymonde' from another
machine
> in the LAN ? Do I have to (re)create a root at raymonde MySQL account and
> eventually give it the same password than root at localhost ?
The @host is where the client is coming from, not the server name.  If 
you don't care where each client originates you can say 'user@%'.
http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

-- 
   Les Mikesell
    lesmikesell at gmail.com

On Tue, Mar 23, 2010 at 04:56:43PM +0100, Niki Kovacs
wrote:
> 
> Now what would be an orthodox (and somewhat secure) configuration to be 
> able to connect to this MySQL server on 'raymonde' from another
machine
> in the LAN ? Do I have to (re)create a root at raymonde MySQL account and 
> eventually give it the same password than root at localhost ?
As Les mentioned, user at host == user on the client host, not user on the
server host.  That's a generic user, not necessarily tied to the
currently logged-in username.  So on raymonde you'd do

grant xxx on db.blah to user at clientHost identified by 'pass';

and someone on clientHost could do

mysql -uuser -ppass -h raymonde db

to connect.

One important distinction that MySQL makes (and some other dbms, like
PostgreSQL, do not necessarily do) is that each ''user'' is a
user-clienthost combination.  So

grant xxx on db.blah to user at clientHost1 identified by 'pass';
grant yyy on db.blah to user at clientHost2 identified by 'pass';
grant zzz on db.blah to user@% identified by 'pass';

creates *three* distinct users.  This can get very confusing if xxx,
yyy, and zzz are all different grants, or if you add grants later to
user at clientHost1 but not clientHost2, or if the passwords are different
(or if the user changes one of them).  So you should try to keep your
MySQL user database simple, to try to avoid this confusion.

Finally, if you want your passwords encrypted you can use --ssl, but
both the client and server need to support SSL.  I haven't used this
much, so you should check out the docs at dev.mysql.com first.

--keith

-- 
kkeller at speakeasy.net

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: not available
URL:
<http://lists.centos.org/pipermail/centos/attachments/20100323/e0b5ec46/attachment-0002.sig>