Our Java server application logs data to a SQL database, which may or may not be on the same machine. Currently we use MS SQL Server, and we're now porting to MySQL. A user configures database backup parameters on our app server, e.g. time of day to run a backup, and the app server executes SQL Server's BACKUP DATABASE command at the appropriate time, via a sproc. It does incremental backups daily and full backups weekly.
MySQL lacks an equivalent feature to tell the database from a client connection to back itself up. Options we're considering are:
- Create a UDF to shell out to mysqldump (or copy database files), which can be called from our app server via a sproc. Essentially we'd be implementing a version of BACKUP DATABASE for MySQL.
- Create a service to run on the MySQL box that can get the backup settings from the app server and run mysqldump (or file copy) locally.
- Create a backup sproc to mimic mysqldump, e.g. SHOW CREATE TABLES and SELECT INTO OUTFILE for each table.
Setting up a cron job, Perl script, third-party app or other tricks that'd work great in a data center aren't preferred; this is a shrink-wrap package that needs to be pretty robust and hands off.
Database sizes can range from roughly 10MB to 10GB.
I'm aware of the binary logs for the incremental piece. I figure the general solution will probably apply to them as well, if we decide to use them.
This is all on Windows 2003 32-bit or 2008R2 64-bit, MySQL 5.1.
The UDF option seems the best to me. The UDF Repository (http://www.mysqludf.org/) has mysqludf_sys, which may be all we need, but I thought I'd ask for opinions since after extensive googling it doesn't seem like others have reached the same conclusion, or maybe our needs are just out of the ordinary. Our app is the only thing in MySQL, so I'm not worried about other users having access to our UDF.
Any solutions I'm overlooking? Any experience with using UDFs in such a way?
Thanks, Eric