Share one database with two Moodle instances

Share one database with two Moodle instances

by Dasu Gunathunga -
Number of replies: 12

Hi,

We want to keep one database for our two testing Moodle instances. Did anyone keep shared database among multiple Moodle instances? Is it possible to keep one database for many Moodle instances? What are the challenges, if we do down this path?


Thanks a lot for sharing your ideas and thoughts.  

Average of ratings: -
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Ken Task -
Picture of Particularly helpful Moodlers

Why?

Can think of more negatives than pluses ...

for one, very complicated config file for 2 sites - have seen such a set up for 4 sites.

updates ... which come frequent enough that updating the DB for each site becomes 'an adventure' ... and actually takes longer + more prone to human error.

upgrades ... even more prone to human error ...

Neither of those should be using the GUI/Web based updates or upgrades.

How does one test plugins???

So yeah could have one code base ... 2 moodledata directories ... 1 DB **if** one site uses Amdl_  prefix and the other site uses Bmdl_ prefix.

Oh, yeah ... let's talk about that single database ... logstore_standard_log table for one site gets in the 2 Gig range farily quickly .. X 2 ... 4 Gig's ... just for the 'who done it' table.   So memory usage of server (DB memory) will need to be monitored and increased more frequently.  Of course if you had a dedicated DB server with plenty of RAM to begin with ... no problem.

So again ... why? smile

'SoS', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Share one database with two Moodle instances

by Andrew Lyons -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers
I think you're over-thinking things. One Database does not mean one set of tables. We really don't have that many tables in Moodle that this is a problem. Memory is generally not a problem either. No more than having multiple DBs on the same server.
Average of ratings: -
In reply to Andrew Lyons

Re: Share one database with two Moodle instances

by Ken Task -
Picture of Particularly helpful Moodlers

Yep!  Guilty - over thinking.

LIke I mentioned, had seen a site such as described ... K12 - elem, intermediate, jh, and a hs.  Lesser used elem and Intermediate campuses.

Once admin got them all to a version, that's where they stayed the remainder of that academic year.  No look-sees at themes nor plugins. Now I know I've never seen a '0 day flaw' officially announced concerning Moodle code but did see one about libraries used by Moodle code the other day.  Personally, I get nervous if not running supported anything.

Is there any official Moodle docs that describe how to set one code base, one DB, for multiple sites ... URL please.

And one of these days, I'll learn not to respond in developers! :| ... just 'lurk' in learning mode.

'SoS', Ken


Average of ratings: -
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Andrew Lyons -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers
Hi Dasu,

Yes it is possible, and it's very easy. Just use a different dbprefix when setting up your site.

Challenges:
- Backup (more to backup)
- Contention (if the sites are busy)

Benefits:
- Cost saving - host several quieter sites easily.

Generally not too much of a problem though. We do it in a number of places.

Andrew
Average of ratings: Useful (1)
In reply to Andrew Lyons

Re: Share one database with two Moodle instances

by Jeff Webster -
Picture of Core developers Picture of Testers
We do a variant of what you are asking. We are using MySQL and we have one database server that we use for our test Moodle instances, we also share one web server. For each instance we have a copy of Moodle application, a moodledata directory and a database on the db server. This allows the test setups to look basically like production and when you are done with a test instance you just delete the three parts without affecting any of your other test instances.

test.example.com/site1
app -- /var/www/site1
files -- /opt/moodledata/site1
db - localhost:mdl_site1

test.example.com/site2
app - /var/www/site2
files - /opt/moodledata/site2
db - localhost:mdl_site2
Average of ratings: Useful (1)
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
One database can have multiple Moodles using it. However that means one set of tables for each Moodle instance. This is done by using a different table prefix during install, i.e instead of just one users table you can have mdl1_user, mdl2_users etc. This is straight forward and not problematic.

If you mean one set of Moodle code for multiple instances of Moodle that is called multi tenancy and is not available in standard Moodle.
Average of ratings: -
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Pawan Pandey -
I dont think their will be any challenges. Only thing which you have to keep in mind is
--> ALL Instance should share same type of codebase.
--> Their should be single data folder shared over all instance by utlizing Network file system like EFS in AWS + Caching
---> Upgradation needs to be perform using command line.

Last but not least, In config.php of all those instances, $CFG->dbhost, $CFG->dbname, $CFG->dbuser, $CFG->dbpass should be same.

Hope it can give you idea!!
Average of ratings: Useful (1)
In reply to Pawan Pandey

Re: Share one database with two Moodle instances

by Flotter Totte -
Picture of Plugin developers
We do exactly that and I can confirm that it works. I would like to add that if the code base is exactly the same and all things of above equal, it will wokr for sure. But the code base can also be "slightly" different, and it still will work fine.
Average of ratings: Useful (1)
In reply to Flotter Totte

Re: Share one database with two Moodle instances

by Dasu Gunathunga -
I tried this recently for my two dev sites. So far it is working fine. However I agreed with all the risk and problems associate with it. I believe we should not share one database for two production Moodle instances.
Average of ratings: -
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Andreas Grabs -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Translators

Hi Dasu,

There is still one think you should keep in mind. If you back up your database in a classic manner as a large single dump, it is not easy to restore a single system from this backup because the tables of both systems are dumped together. You need to find a way to back up both systems separately.

Best regards
Andreas

Average of ratings: Useful (2)
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Jerry Lau -
would not recommend it.. in fact if I was in charge, I would prohibit it. My selfish reasons are:

  1. How can you test succinctly if there was a problem?
  2. What if versions are different and you need to make changes globally to a db that may impact the other DB
  3. What if you want to shutdown the entire service? Now you have 2 group of users you have communicated and agree on a downtime
  4. How can you replicate in prod exactly to test in your test environment? What I do is to mirror each platform exactly down to OS patch level, moodle build, plugins, db's php version, patches, etc. By doing this and you do a test, you can expect the same errors on your prod and that lessens your "stress" once it comes time to actually put it on prod and there are no "surprises" and panic does not set it.
  5. and other reasons ...

just my 2 cents to help me rest easier at nights LOL
Average of ratings: Useful (2)
In reply to Dasu Gunathunga

Re: Share one database with two Moodle instances

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Hi Dasu,

May I ask what it is that you are trying to accomplish by having two instances of Moodle share a single database?

Best regards,

Michael

Average of ratings: -