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?
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, from autos)”… etc…
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:
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?
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.
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
