WeBWorK Main Forum

Setting up external database

Setting up external database

by Sean Fitzpatrick -
Number of replies: 7
This seems like a stupid question but somehow I've been unable to track down an answer after some time searching the forums and documentation.

I'm currently talking with central IT about moving our WeBWorK install from a physical server I maintain to their system (on one or more VMs) since our hardware is several years old and at some point maintenance issues will come up.

They asked if I want to keep using the internal mysql database, or move everything to their cluster. To determine costs, they want to know the size of the current database.

So far, I haven't managed to track down where, exactly, the DB lives to give them the size! I'm also not quite clear on where one edits the settings to change from the default setup to using a database on a different server.

Any help would be appreciated.
In reply to Sean Fitzpatrick

Re: Setting up external database

by Danny Glin -
On disk the database would be stored in the mysql data directory, which is defined by the datadir variable in /etc/my.cnf. On my CentOS system it is /var/lib/mysql. Within that directory there should be a folder for each database. In particular there should be one called webwork. The size of that directory should give you an indication of how much disk space your WeBWorK database takes up.

Configuring WeBWorK to use an external database is done in site.conf. There are some comments there about syntax. The short version is to use:
$database_dsn = "dbi:mysql:webwork:[hostname]:[port]";
You can leave off the port if you are using the standard mysql port. It's probably worth mentioning that the database server (and any firewalls in between) will have to accept connections from your WeBWorK server.
In reply to Danny Glin

Re: Setting up external database

by Sean Fitzpatrick -
Thanks. I was pretty sure it was in /var/lib/mysql but that folder doesn't allow read access on our system!
In reply to Sean Fitzpatrick

Re: Setting up external database

by Danny Glin -
That's probably a good thing. That folder should only be readable by the mysql user for security reasons. You should be able to access it using sudo.
In reply to Sean Fitzpatrick

Re: Setting up external database

by Nathan Wallach -
You might want to consider whether you are willing/able to be a front-runner in running WW in production inside a Docker image. It is certainly already possible (I am doing so), but is still cutting-edge (and probably has several downsides which I am not aware of or ignoring).

The current Docker setup is based on Ubuntu 16.04 (LTS), but we expect WW 2.15 to be set up to use Ubuntu 18.04 (LTS) instead.

If you are in less of a rush, work on planning how to run WW in Kubernetes and/or OpenStack is in early stage planning, with someone else already starting to experiment on this. Beta-testing will hopefully be possible sometime in the middle of 2020 or a bit earlier. People interested in this project, or willing to participate in development and/or testing can email me for more information.
In reply to Nathan Wallach

Re: Setting up external database

by Sean Fitzpatrick -
So, as it turns out, we are shopping for a new physical server.
Based on the cost per year to get a VM from IT, it was cheaper for our Dean's Office to just buy new hardware. Sadly, that means I'm still on the hook as a sysadmin -- I was looking forward to no longer being responsible for OS updates or backups.
(For the cost of a VM from IT for three years, with 8 vCPUs and 16 GB RAM, we can buy a two slot rack server with dual 10 core Xeon processors, 32 GB RAM, an SSD RAID array, dual power supply...)

We're hoping for delivery and setup in September.
Is there any advantage to running WW in Docker if it's the only thing running on the server?
In reply to Sean Fitzpatrick

Re: Setting up external database

by Danny Glin -
The one advantage to running WeBWorK in Docker (or a VM) if it's the only thing on the server would be the ability to simultaneously host a production server and a development/testing environment.

Because WeBWorK code is loaded directly into apache processes it's basically impossible to run two different versions of WeBWorK on the same instance of apache. By running WW in Docker or a VM you can spin up multiple instances at the same time, so you could be testing the next version of WeBWorK without it affecting your live server.

It's worth mentioning that buying one monolithic WeBWorK server provides you with a single point of failure. If you have a hardware failure on that machine, WeBWorK ends up offline until it's repaired. This is probably a good place to put in a plug for what little documentation there is on WeBWorK Backups: http://webwork.maa.org/wiki/Backup_and_Disaster_Recovery.

With that being said, enterprise-grade hardware (like rack-mount servers) tends to be very reliable. I can't remember the last time we had a hardware failure on our servers.
In reply to Danny Glin

Re: Setting up external database

by Sean Fitzpatrick -
Thanks for pointing out the documentation on backups. We'll want to make sure we're following that.
I agree with all the downsides of having a single WeBWorK server.
Unfortunately, they weren't enough to convince our university to move to something virtualized and distributed.
Of course, I could have ordered several small servers or thin clients instead of a single rack mount server, but then I get the extra workload associated with setting up and maintaining it.

I doubt we'll have problems. If we do, maybe a failure mid-semester with 1000 students relying on it will be enough of a fiasco that we suddenly find enough money to do it right.