Problems with setting up clustering/L.A. JSS

Is there an entry on your MySQL server corresponding to the IP or hostname for the user your secondary JSS is connecting with?

Yeah, you have to tell MySQL to accept a connection from your secondary JSS server's IP instead of just localhost.

how would I find that? I didn't see that in any of the posted KB articles.

https://jamfnation.jamfsoftware.com/article.html?id=114

The post that @dpertschi linked to is the right idea, but it specifically is referring to creating a read only user. You'll need to create a MySQL user that can modify the database from your 2nd JSS:

GRANT ALL ON *databasename*.* TO *databaseuser*@*hostnameofjss1* IDENTIFIED BY '*my_password*';

Where:
databasename: name of your MySQL Database
databaseuser: create a MySQL user, you'll enter this user name in the JSS setup screen
hostnameofjss2: The hostname or IP of the JSS server that will try to connect to the DB
my_password: Password for databaseuser

Since this is granting access to a MySQL user from a particular host, you'll need one of these for each JSS in your cluster to allow them to talk to MySQL.

i think i screwed up.

I created a new account on the test box using this from the mysql docs (obviously changing the username and password)

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

as well as

GRANT ALL ON databasename.* TO databaseuser@hostnameofjss1 IDENTIFIED BY 'my_password';

When I went to JSS DB Utility, it now says it can't connect to the DB, access denied for the initial jss user account...

You don't need to run the CREATE USER....the GRANT ALL will create the user if it doesn't exist.

If you used the same databaseuser@hostnameofjss1 combination and a different password, you essentially changed the password that your JSS needs to access the MySQL Database. Just run the same command again to change the password back to what it was before and it should work again.

Then to add credentials for the second JSS, do this:

GRANT ALL ON databasename.* TO databaseuser@hostnameofjss2 IDENTIFIED BY 'my_password';

You can use the same username and password if you want to, but you need to have credentials configured for each JSS hostname/IP address.

Example:

GRANT ALL ON databasename.* TO jeffrey@localhost IDENTIFIED BY 'my_password';

GRANT ALL ON databasename.* TO jeffrey@jss2 IDENTIFIED BY 'my_password';

GRANT ALL ON databasename.* TO jeffrey@jss3 IDENTIFIED BY 'my_password';

To check your current state, use the following MySQL command to see all users and what database they have access to:

SELECT User, Host, Password FROM mysql.user;

I did those, I rebooted the machine, went back in to the JSS, but it still only shows the main entry for localhost under Limited Access and Clustering. The JSS 9.4 installer continues to fail when I try to connect to the DB from the 2nd jss.

SELECT User, Host, Password FROM mysql.user;
+--------------+-----------------------------------------------+-------------------------------------------+
| User         | Host                                          | Password                                  |
+--------------+-----------------------------------------------+-------------------------------------------+
| root         | localhost                                     |                                           |
| root         | macbook-pro.00490.cmp.non-rtl.us.shldcorp.com |                                           |
| root         | 127.0.0.1                                     |                                           |
| root         | ::1                                           |                                           |
|              | localhost                                     |                                           |
|              | macbook-pro.00490.cmp.non-rtl.us.shldcorp.com |                                           |
| jamfsoftware | localhost                                     | *B8B7B8C47EF8381F4C2BBD441DAF0E52AEE035DF |
| jssread      | localhost                                     | *D7DF0564F84B53DEDAC7206015AD3158EA2E206E |
--> was a typo| jssread      | srshofmacdply00cdply00                        | *D7DF0564F84B53DEDAC7206015AD3158EA2E206E |
| jssread      | srshofmacdply00                               | *D7DF0564F84B53DEDAC7206015AD3158EA2E206E |
+--------------+-----------------------------------------------+-------------------------------------------+

Not sure if I missed what server your using for MySQL but you may need to edit the bind address as its locked to only allow access from 127.0.0.1 by default. This is separate to any firewall rules you have setup allowing 3306.

it's all on mac hardware. the production JSS is on an xserve 10.9.4 w/ mysql 5.6.14

for my purposes right now, I have the test environment (actual DB installed on - JSS1)on 10.10 rMBP, and the test machine I'm trying to access from (JSS2) is a 10.9.4 mac mini server.

If its Mac OS X then I think you will need to edit the bind-address to allow connections from the other IP. On my linux server its in the /etc/mysql/my.cnf file. I think its the same for OS X.

To echo what @davidacland][/url][/url is mentioning here, is that by default the /etc/my.cnf (path differs from platform to platform) has a setting in it that binds MySQL to only listen to the local host for incoming connections. If you comment that bind address variable out and restart MySQL, it will then listen to incoming connections from other hosts. Most MySQL installs have the bind address enabled by default.

This is a copy of my MySQL config off my VM:

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
**#bind-address           = 127.0.0.1**

You can see that I commented out the bind address line, which points back to the local host only.

I hope this helps.

Thanks,
Tom

i didn't have a my.cnf / my.conf / etc file on my test environment. So I found one in a folder, which I modified and added the #bind-address line to it. restarted mysql, and still wouldn't connect.

I checked on my production JSS environment, I do have a my.conf on that, but it also does not have that #bind-address in it, so again I added it.

You may want to try doing the MySQL grant for the IP address of your secondary server, rather than the hostname (would look something like GRANT ALL on jamfsoftware.* to 'my_user'@'192.168.0.100' IDENTIFIED BY 'password'). MySQL ends up defaulting back to the IP address of the connection if the DNS resolution of the hostname fails any of its checks, so I've found it to generally be a more reliable way of connecting.

so, the 192.168.0.100 = the JSS that's hosting the actual DB, not the one on the DMZ?

In this case, it would be the IP address of the server in the DMZ. The host portion of a MySQL GRANT is specifying where a given user is allowed to connect from, so using the previous example we are basically saying "Grant all permissions on the jamfsoftware database to user 'my_user' when they connect from host '192.168.0.100' with a password of 'password'".

Stepping back a bit, here is how I would typically grant permissions if I was setting up a cluster with the following 2 servers:

Server 1 (Internal):
Running JSS and MySQL - IP 192.168.0.57

Server 2 (DMZ):
Running only JSS - IP 192.168.0.100

GRANT ALL on jamfsoftware.* to 'jamfsoftware'@'localhost' identified by 'password';
GRANT ALL on jamfsoftware.* to 'jamfsoftware'@'192.168.0.100' identified by 'password';

The first grant gives permission for the JSS to connect locally on server1, and the second gives the jss permission to connect remotely from 192.168.0.100 (server2).

A much less secure alternative that you could try for testing is this:

GRANT ALL on jamfsoftware.* to 'my_user'@'%' identified by 'my password';

The percent symbol is treated as a wildcard for the host, so this would be interpreted as "Give all permissions on database jamfsoftware to user my_user when they connect from any host". That definitely isn't something I would leave in place for a production system, but it can be nice to help troubleshoot whether the connection issue is from the GRANT, or some other outside factor (firewall rules, etc).