8 replies to this topic

[Vaielab]

Hello,

I am looking for a way to backup my database (mysql) that won't backup my whole database but only a client, and can restore only this client.
People can connect to my project, each with an username/password and everyone have their own space. And if one client do something wrong, I would like to be able to backup him, without restoring every other clients.
Every table that need to be separated have the id of the client.

At first I thought of using a separated database for each client, but this could be a little bit hard to manage, so now I'm trying to find a other way.

I heard of horizontal partition, but I'm really not sure to understand how this work... and if this can help me at all.

So if anyone have an idea
Thx

[WingedPanther]

So each client has their own tables? MySQL :: MySQL 5.0 Reference Manual :: 12.4.2.2 BACKUP TABLE Syntax

[Vaielab]

Nop, that would be too much easy, and we must make life complicated (I hate myself :P)

Each table that have separated data (separated by client) have an id client in them
I was thinking to generate some really hardcore script that would query everything and create delete statement for each table for this client, and insert statement for each row of 1 client in particular
So if I needed to restore a backup, I would only need to execute that sql script that was created by my hardcore script.

But... I was hoping to find a better solution:cool:

[WingedPanther]

How about this for an idea: use SQuirreL SQL Client to select the records, then copy them out as INSERT VALUES statements?

[Vaielab]

This will not work because of the auto increment
When I restore an client, I'll need to delete everything from this client and after that insert it.
But every relation will be broken when i'm gonna try to reinsert the data.

[WingedPanther]

It sounds like you need to either:
1) redesign your database, or
2) write a program to do this, handling the referential integrity for you.

[Vaielab]

Redesigning the database... at first I was thinking of using a different database for each client to solve this particular issue.
But I got into some problems: some table are for all clients, so I would either have to copy the data into each database or don't use referential integrity for theses tables and make 2 query (one for the db for the client, and one for the db for the system), and my other main problem would have been with updates, it could be a nightmare if I forget to update the db of one client

And I could have pretty much the same problem if I create different table for each clients (maybe even more complicated)

Do I miss something, or do I simply have to suck it up and create that program to handle the referential integrity?

[WingedPanther]

Here's my concern: the problem seems to be with some auto-numbers that are used in referential integrity. Do you actually need auto-numbers? Can you implement them in a different way? (there's an example of a UDF to create sequences here: MySQL :: MySQL 5.1 Reference Manual :: 21.3.2.5 Compiling and Installing User-Defined Functions)

One of the SQL Anti-Patterns is to use an auto-sequence field as the primary key, and then create a unique constraint on one or more other fields. That would tell me those other fields are the actual primary key, and you're just creating the auto-sequence because it's "the thing to do". Reexamine what you're doing.

[Vaielab]

I readed this chapters (2 days ago), but something we simply don't have the choose...
But I'll take a look at your link, it seem very interesting, thx!