Dedicated MySQL server

merc_support
New Contributor III

We're looking at moving our JSS DB to a dedicated MySQL server (from a local OS X installation) but there's no real information about the implications of this, or what exactly we'd need to change within tomcat to point to the new DB.

Would we be looking at a DB restoration and a clean install of the JSS, or can we simply point our current installation to the new DB?

Is anyone able to provide some guidance or advice?

Thanks in advance.

13 REPLIES 13

Kumarasinghe
Valued Contributor

You have to edit the DataBase.xml file ("<tomcat home>/webapps/ROOT/WEB-INF/xml/DataBase.xml")

You can just point to the new database by updating the file mentioned above.
e.g.-

<ServerName>your_mysql_sever_name_or_ip</ServerName>
<ServerPort>3306</ServerPort>
<DataBaseName>my_database_name</DataBaseName>
<DataBaseUser>my_database_username</DataBaseUser>
<DataBasePassword>xxxxxxxxx</DataBasePassword>

matt_jamison
Contributor

Build your new server and install MySQL.

Create the database on the new server.
create database jamfsoftware;

create the mysql user to access that database.
grant all on jamfsoftware.* to 'jssuser'@'FQDN or IP of JSS Server' identified by 'make a tough password and put it here';
# IF you use FQDN it must resolve or mysql won't let it connect. I would use IP.

make sure port 3306 is open from JSS to the new MySQL server.

Shut down JSS on the old server and do a mysqldump jamfsoftware > jamfsoftware.sql

Now shut down MySQL on the old server.

Copy jamfsoftware.sql over to the new server and import it.

mysql jamfsoftware < jamfsoftware.sql

Start up JSS and it should come up and say it can't connect to the database and ask you to verify the server and username/password. Enter the new info and you should be up and running.

Make sure MySQL on the old server doesn't start up again. Command depends on your OS. Once you are sure everything is good, you can uninstall MySQL.

Josh_S
Contributor III

For all intents and purposes, the MySQL database is what makes your JSS, well, yours. In broad strokes, this is what this will entail:

  1. On your new MySQL server, adjust permissions for your JSS user account to allow access from your JSS web app server.
  2. Stop your JSS web app so your database becomes static.
  3. Make a backup of your MySQL database.
  4. Import your database backup into your new MySQL server. You should now have two MySQL databases that are identical but on different servers.
  5. Stop MySQL on your OS X server.
  6. Start the JSS web app.
  7. Navigate to your JSS URL, it should complain that it can't connect to the database.
  8. Click the button to edit your database settings and input your new MySQL server information.

All this said, test test test, and test again, before you do this in production. I would recommend spinning up a new JSS on a virtual server just to see what that database relinking looks like. Also, for testing, you should be able to use an existing backup of your MySQL database without actually taking your production JSS offline. I would still do a test with this step just to see what it looks like though.

I have built more "testing environments" and imported more production backups into those environments than I can count. I still do at least three dry runs, and have back out plans thought out and documented, whenever I'm going to do a major change to my production JSS.

It also wouldn't be a bad idea, if you're looking at doing a change like this anyway, to look at getting your JSS off of OS X as well. I would also document where the database settings file is on the filesystem so if you have to manually change the database settings, you can.

merc_support
New Contributor III

Thanks everyone. We ended up taking the dump and importing it into the new server, and pointed the DataBase.xml file to the new location. The actual transfer all done in a matter of minutes.

jyergatian
Contributor

I'm in the process of setting up a JSS instance and am curious to know the technical benefits to having the MySQL Database on a separate server. Our intention is to have one IT JSS (Master) followed by an Internal JSS one in the DMZ (both Limited). All of this will be hosted on RHEL7. What benefits does one gain from separating the Database?

justinrummel
Contributor III

@jyergatian do you have traditional File Distribution Points or JDSs? How many?

- Justin

jyergatian
Contributor

I'm leaning towards the JDS solution for due to it's ease of use. There would be 3 all together, one paired with each JSS.

Chris_Hafner
Valued Contributor II

I think all the major points have been covered. I just wanted to point out that this type of stuff is covered in the CJA Certificate Course for those that wonder.

brad
Contributor

I would recommend a fast and redundant link between your MySQL server and your Tomcat server(s).

sadamson
New Contributor

I have been trying to run a test instance on AWS (using EC2 for the JSS and RDS - MySQL for the database). EC2 (with MySQL installed is working very well but if I wanted to move the database to RDS (or create the DB on RDS) and then interconnect JSS and the Database, I'm running into a few issues (not seeing the DB).

I am also looking at spinning up (again, test environment) JDS on a Ubuntu server within EC2 but again, having the JDS and JSS see each other has been a problem. With a test (free tier) environment on AWS this seems like this would be doable but I know I'm missing something.

Let me know if anyone has experience and thoughts about this. I've been trying to document what I have tried and appreciate any insight. Thanks all
Scott

emily
Valued Contributor III
Valued Contributor III

Hit up your JAMF Account Manager. Mine has offered to help me migrate my database from MariaDB to MySQL before, so I'm guessing they could come up with a plan of action for you.

tathomp7
New Contributor

Hello folks, so I followed the instructions as given above and everything worked great with the exception that when I navigate to the jss page and click on edit connections I am only given the option to enter a password. I notice that the error on this page says make sure that mysql is running on localhost. Which obviously it is not. Now I will also say when I restarted Tomcat from the JSS Database Utility, I got a pop that asked me to confirm the settings of the new location of the server. All of that went well with some tweaking and it appeared to connect. But I am still getting this problem, any suggestion?

nessts
Valued Contributor II

read your administrator guide page 23 and find the section where it tells you ho to give access to

GRANT ALL ON jamfsoftware.* TO 'jamfsoftware'@localhost IDENTIFIED BY 'jamfsw03';

you need to edit localhost to an IP address of the JSS