How to bring files in a filesystem in/out MySQL DB?

Question

The application that I am working on generates files dynamically with use. This makes backup and syncronization between staging,development and production a real big challenge. One way that we might get smooth solution (if feasable) is to have a script that at the moment of backing up the database can backup the dynamically generated files inside the database and in restore time can bring those file out of the database and in the filesystem again.

I am wondering if there are any available (pay or free) application that could be use as scripts to make this happen.

Basically if I have

/usr/share/appname/server/dynamicdir
/usr/share/appname/server/otherdir/etc/resource.file

Then taking the examples above and with the script put them on the mysql database.

Please let me know if you need more information.

Answer 1

the should be trivial to accomplish using PHP, perl, python, etc. are you looking for someone to write this for you?

Answer 2

Do you mean that the application is storing a files as blobs in the MySQL database, and/or creating lots of temporary tables? Or that you just want temporary files - themselves unrelated to a database - to be stored in MySQL as a backup?

I'm not sure that trying to use MySQL as an net-new intermediary for backups of files is a good idea. If the app already uses it, thats one thing, if not, MySQL isn't the right tool here.

Anyway. If you are interested in capturing a filesystem at point-in-time, the answer is to utilize LVM snapshots. You would likely have to rebuild your server to get your filesystems onto LVM, and have enough free storage there for as many snapshots as you think you'd need.

I would recommend having a new mount point just for this apps temporary files. If your MySQL tables are using InnoDB, a simple script to run mysqldump --single-transaction in the background, and then the lvm snapshot process, you could get these synced up to less then a second.