Backing up client specific data

This is probably the wrong place to be asking about database
backups... but this group goes off so:

I have a corporate client that wants their data backed up, and in
their hands. So, a full database backup and a promise isn't going to
cut it, and a full database backup in their hands is bad because we's
have to give them everyone elses data!

My first thought was to generate a bunch of CSV files... but the
database is quite complex and I'd need a separate file for each table.
That's fine I guess, but I'm curious if there is a better way?
Especially if the database often changes.

I'm using MySQL, so does anyone know if there are good tools out there
that can selectively backup data? can mysqldump do it? how? what about
mysql administrator?

Thanks

no need to give them access to everyone's data, you can instruct
mysqldump to only backup a single database, and then selectively
choose tables from that database:

mysqldump --user=username --password=password your_database table1
table2 table3 table4 -r database_backup_`date +"%a_%b_%d_%Y"`.sql

rsync the database to their machine and you're done

Mike

One option might be to create some views specific to the client, and
just mysqldump those views.

Jonzo wrote:

I'm using MySQL, so does anyone know if there are good tools out there
that can selectively backup data? can mysqldump do it? how? what about
mysql administrator?

Thanks

I like tshim's suggestion and that is the approach I would take (but
remember that he is talking about db views and not rails views)

An alternative is to write a simply ruby script that creates another db
and then does statements like this:
"create table tempdb.cars as (select productiondb.autos as auto, <other
fields and joins here> from autos)".. etc..

hth

ilan

thats probably the simplest idea huh!

Thanks for the suggestion :slight_smile:

hey these are all good suggestions! I knew this group was the right
place to come :wink:

unfortunately I can't use Mike's because our client data is all mixed
together...

Thanks everyone :slight_smile:

Is there something special I need to do in order to backup the data in
a view without backing up the original table in it's entirety?

Just a follow up - I ended up using Ilan's suggestion:

- create a new database
- generate the content with queries from the original database
- run mysqldump
- delete the database

I did this all from within rails! I'm pretty happy with the solution
now because it means that I can generate the backups through the web,
or with cron jobs, and that the generated files can then be downloaded
by admins or by the clients themselves. We probably won't let that
happen though because it'd be giving away our db design...

I also tried out creating views but there are several reasons why I
didn't carry on:

- I couldn't find a way to pass variables into a view (this is more
of a stored procedure thing), so I had to hard code the account id
into one view, and base the rest of the views on that.
- they views are stored in the same original database, so I would end
up with ~8 new tables/views for each client that wants to have
backups.
- when I tried to use mysql dump with them it didn't dump the data.
I'm not sure if there is a way around this but it wasn't easy to find
if there is.
- the view names would need to be different from the original table
names, this means that if the time came for the db to be regenerated
from a backup, it'd be more difficult than it needs to be.

I hope my experiences help someone else out!

Once again, thanks for all your help everyone.