hello. i was wondering if someone could recommend a backup tool? i looked at mysqldump and a few other tools already, but would like some input on this.

this is for a medium sized web application with ~30 tables (mostly just bridge tables and a few large tables). we are using MyISAM.

here is what we would need:
something simple enough for an end-user to use
allows end-user to view the data in a readable format
each time they backup, the tables will be cleaned out afterwards.
backup/purge will be done only 4 times a year.

the big thing is that 1) the end users will be doing (well, they want to) the backups and 2) they want to be able to view the old data.

i was thinking of somehow creating a CSV or XML dump and having the end users importing that into MS Access or Excel for readability..

what tools do some of you use?

... see my signature, need I say more ?

Why not have them perform a select * from any table on the command prompt and let them capture the results in a notepad file (losers, sorry: users are not likely to be linux minded) ... guessing they're not able to pipe it to a file ... What's the use of keeping a 'backup' in readable text format ? Put the data back in the database if you want to read it !!! Before you know it, you're building a 'text-backup-file-interpreter' for them, so they can 'look up' some specific data in those files.

My humble advise: don't go there ... and again, my signature tells more than enough on how I feel about these kind of requests.

Gr,
Yveau

ahh heh. i was expecting an answer like that.

well, the clients really would like backup/archival functionality... (if it was me, i'm happy with just a sql dump.)

thanks anyways.

Thank god you're a good sport !
It was only my intention to make fun of the users who dare asking such things, not the victim (you in this case) ...

Good luck finding a feasible sollution for them ...

Gr,
Yveau

You probably want to do something like this:

1. Block users from accessing (or at least updating) the database.
2. Use mysqldump (see its manpage) to dump all data into a file called something like complete_dump.date Hints: --all-databases --no-create-db --no-create-info.
3. Truncate all tables. One solution is to use DELETE FROM. Another solution is using mysqldump again to dump only the database and table structure including DROP statements; this will drop tables and then recreate them. (See mysqldump man page.)
4. Insert the data from (2) into the "history database". This is done by simply dumping the dump file from (2) into the mysql client.
5. Unblock users from accessing the database.

You could probably create a single shell script that can do this and tell your users how to execute it.

a shell script might be too technical for them. but i do like your idea of using a "history database".

You could write a description on how to invoke it. Can't they read recipes?

Well, that was your idea! "[...] 2) they want to be able to view the old data."

hello, i thought i'd give an update on this (and seek out any comments). i had been working on a dynamic graph/chart for this web app and now have some time to delve into the backup/purge a bit more.

they just gave me MySQL 5 space (the web app is using MySQL 4 right now) and are letting me play with replication. i have never used this before.

i don't think we have root access (the servers are administrated by another department), so i'm wondering how much we can control.

also, i found out that they don't allow odbc connections on the regular servers but will allow it on the slave server.

this is how i think the backup/purge would work with replication:
- Master runs and all, sends data to slave.
- When users are ready to backup/purge, they open an Access or Excel file that connects to the slave, pick out what data they need, and save that file.
OR
- I make a web interface (PHP file) that creates an Excel file for them.
- Then in the web app there will be a button for them to push that runs a truncate script. It will truncate the master and slave tables for this app.

what do you think? is using replication worth it, or would it be better to go with the shell script?

thank you.