6593 pointsLike some sort of WPMU DEV God"Exceptionally helpfulLifetime member
drmike
Keeper of the Dark Chocolate
—
22nd April 2009 (3 years ago)
I'll be the first to admit that this is an area that I don't know a whole lot about.
I'm looking at the multiple dbs plugin and I realize that it uses a primary and a secondary database server. Or at least it's able to do so. Does it do this so it will fallback to the secondary if the first one is not responsive? When the first one comes back, does the data sync up between the two?
Is there any way we can do this and have the secondary server as a perminate resource, never to have anything deleted out of there so there always a record? (That thought is what got me thinking about this.)
We just use a single database setup and run daily backups to a backup server located elsewhere.
6593 pointsLike some sort of WPMU DEV GodExceptionally helpfulLifetime member
Keeper of the Dark Chocolate
—
22nd April 2009 (3 years ago)
#
I'll be the first to admit that this is an area that I don't know a whole lot about.
I'm looking at the multiple dbs plugin and I realize that it uses a primary and a secondary database server. Or at least it's able to do so. Does it do this so it will fallback to the secondary if the first one is not responsive? When the first one comes back, does the data sync up between the two?
Is there any way we can do this and have the secondary server as a perminate resource, never to have anything deleted out of there so there always a record? (That thought is what got me thinking about this.)
We just use a single database setup and run daily backups to a backup server located elsewhere.
7430 pointsLike some sort of WPMU DEV GodMindblowingly helpful memberLifetime member
Erstwhile founder
—
22nd April 2009 (3 years ago)
#
Hiya,
Does it do this so it will fallback to the secondary if the first one is not responsive?
Yep.
When the first one comes back, does the data sync up between the two?
MySQL replication is responsible for that task.
Is there any way we can do this and have the secondary server as a perminate resource, never to have anything deleted out of there so there always a record? (That thought is what got me thinking about this.)
Doing this via Multi-DB wouldn't be very efficient. Multi-DB only sends writes to one db server (not multiples for the same query). You have to setup replication to spread the changes out over all of the servers.
You're best bet would be to consult a MySQL expert and see if you could setup a Master - Master configuration with delete query not replicated to one of the MySQL instances. You could then use that.
Honestly though, I don't think it will work out as well as it seems it would. For example, over time the MySQL instance acting as the backup would grow pretty large.
6593 pointsLike some sort of WPMU DEV GodExceptionally helpfulLifetime member
Keeper of the Dark Chocolate
—
22nd April 2009 (3 years ago)
#
I suggested the daily backups, even hourly ones, but they want "as it occurs" backups. When a post or comment is made, have it get dropped into a database somewhere and not have it deleted if it's deleted on the site.
6593 pointsLike some sort of WPMU DEV GodExceptionally helpfulLifetime member
Keeper of the Dark Chocolate
—
22nd April 2009 (3 years ago)
#
That's what I'm thinking actually. Drop new posts, with the necessary meta information like ip address and date time, into one database and comments into another database. They haven't forwarded on to me what specific information that need saved, just a "we need everything saved" ticket.
I was thinking a "write only" database was a way to go. Easy way out of it.
7430 pointsLike some sort of WPMU DEV GodMindblowingly helpful memberLifetime member
Erstwhile founder
—
22nd April 2009 (3 years ago)
#
Yeh, Unfortunately I just don't think Multi-DB is the solution for this one.
I'd create a table for comments and a table for posts. The table structure can be identical to the actual tables with the addition of blog_id and post_id/comment_id columns. Then just use the "save_post" and "comment_post" hooks to chunk copies of everything saved into the tables.
If that install isn't using the the post and comment indexers then you could just strip out the modify/delete bits from those plugins and call it a day.
6593 pointsLike some sort of WPMU DEV GodExceptionally helpfulLifetime member
Keeper of the Dark Chocolate
—
22nd April 2009 (3 years ago)
#
Someone pointed out Donncha's sitewide plugin but that leaves the option of editing or deleting. I'm sure we can hack something out of that though. Same idea with comments.
I asked for a spec list of what needs to be saved. There's some ISO 9k requirement here so we may have to pester the lawyers on this one as well.
Responses (8)
Erstwhile founder — 22nd April 2009 (3 years ago) #
Hiya,
Yep.
MySQL replication is responsible for that task.
Doing this via Multi-DB wouldn't be very efficient. Multi-DB only sends writes to one db server (not multiples for the same query). You have to setup replication to spread the changes out over all of the servers.
You're best bet would be to consult a MySQL expert and see if you could setup a Master - Master configuration with delete query not replicated to one of the MySQL instances. You could then use that.
Honestly though, I don't think it will work out as well as it seems it would. For example, over time the MySQL instance acting as the backup would grow pretty large.
Thanks,
Andrew
Keeper of the Dark Chocolate — 22nd April 2009 (3 years ago) #
Thanks for the responses.
I'm assuming that's something we have to set up outside of the plugin, right?
Is there something you would recommend to keep a permanent record of everything?
thanks,
-drmike
Erstwhile founder — 22nd April 2009 (3 years ago) #
Hiya,
Yep:
http://dev.mysql.com/doc/refman/5.0/en/replication.html
Daily backups wouldn't work?
Thanks,
Andrew
Keeper of the Dark Chocolate — 22nd April 2009 (3 years ago) #
I suggested the daily backups, even hourly ones, but they want "as it occurs" backups. When a post or comment is made, have it get dropped into a database somewhere and not have it deleted if it's deleted on the site.
Erstwhile founder — 22nd April 2009 (3 years ago) #
I really can't think of anything outside of a plugin to handle that one.
Thanks,
Andrew
Keeper of the Dark Chocolate — 22nd April 2009 (3 years ago) #
That's what I'm thinking actually. Drop new posts, with the necessary meta information like ip address and date time, into one database and comments into another database. They haven't forwarded on to me what specific information that need saved, just a "we need everything saved" ticket.
I was thinking a "write only" database was a way to go. Easy way out of it.
Thanks,
-drmike
Erstwhile founder — 22nd April 2009 (3 years ago) #
Yeh, Unfortunately I just don't think Multi-DB is the solution for this one.
I'd create a table for comments and a table for posts. The table structure can be identical to the actual tables with the addition of blog_id and post_id/comment_id columns. Then just use the "save_post" and "comment_post" hooks to chunk copies of everything saved into the tables.
If that install isn't using the the post and comment indexers then you could just strip out the modify/delete bits from those plugins and call it a day.
Thanks,
Andrew
Keeper of the Dark Chocolate — 22nd April 2009 (3 years ago) #
Someone pointed out Donncha's sitewide plugin but that leaves the option of editing or deleting. I'm sure we can hack something out of that though. Same idea with comments.
I asked for a spec list of what needs to be saved. There's some ISO 9k requirement here so we may have to pester the lawyers on this one as well.
Thanks again,
-drmike
Become a member