Backing up client specific data

rubyonrails-talk
1

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

2

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

3

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

4

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

5

thats probably the simplest idea huh!

Thanks for the suggestion :slight_smile:

6

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:

7

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?

8

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.