Several MySQL?

[sjordi]

Hi,
we run Alfresco based on MySQL.
Our problem is that we would like to be able to have it point to several MySQL instances.
Alfresco here serves many administrative departments and if one goes South, we would like to be able to make a database restore only for them.

MySQL, unlike Oracle, etc… has to be fully shut down since instances are not independant. That means restoring everything for everyone and have all department stop working in the meantime.

What we would love to have is, say, 3 MySQL instance running concurently
(one on port 3306, the 2nd on 3307, the 3rd on 3308).
If we have to shutdown one MySQL, and keep the two others up, that would dramatically simplify our maintenance.

Is this possible?
Thanks for any recommandation.

[mrogers]

You can have a mysql cluster,  so you can shut down different nodes.

Its not clear the relationship between your different departments,  alfresco and the database.  Does each department have its own instance of alfresco.

And what do you mean by "go south".    Is that a disaster like a disk failure or something more regular and expected?

[sjordi]

Thanks for your help.

There will be one Alfresco instance as a start.
Probably 4 different department working together (4 different projects)

By going south, I think about someone totally messing up their files and screwing the logics behind the scene.
Sometimes they would call and say "can you put it back in the state it was yesterday at 3:15pm?"
That's what that would mean.

[mrogers]

Alfresco doesn't support partial backup and restore at the database level So I don't see how you could do this without your other departments getting unhappy as they loose their latest changes.     (Actually you might be able to shard your database and give a separate store to each department - but that is very advanced stuff.)

Nodes can be version controlled so you can roll back to previous versions.

You can also import and export various subsets of data (For example a share site document library), that's probably the way to go rather than attempting to do something at the database level.

[sjordi]

Yes
the idea was to have each department isolated from others in Alfresco.

Also if version controlling is reliable, we could come back to different versions.
But what if this crashes?

Also, what we will have is one of the departments feeding Alfresco from an SAP connector. They won't even be aware of Alfresco. There will be a connector between the two parties, and this one is supposed to also buffer new transactions in case Alfresco should be shut down.

We are currently in a reflection about backing up/restoring schemas. That's why we think "department per department", with less impact for each of them, should we need to shudtown MySQL in order to restore data.

[mrogers]

If you want each department "isolated" to the extent that you can take a department down or restore an old backup on an ad-hoc basis then I think you should look at separate instances of alfresco, one for each department.

However that will cause problems when it comes to sharing information between departments and cause you an extra admin overhead.