Mysql Backup Ideas

MySQL Backup of mixed type tables have been a night mare for me always. When it comes to the Win32 port serving a client application, which was a recent client requirement we at Saturn had to grease our brains a little too much. Finally all the requirements were met.

The Requirements were that all triggers, proceedures and functions along with table defenitions, permissions and data should be backed up. The backup and restore should be with maximum efficiency. Triggers should not be triggered when restoration is happening.

Considering all the above points, it was finally decided, to go with a suggested solution as follows. The structure of tables would be taken using show create table, parsed and properly formatted to a particular markup with a create table, a load data in file, and a set of alter tables.

Considering tables with mixed types, ie some tables are InnoDB, and others MyISAM, all InnoDB tables will be converted to InnoDB after the data has been populated, if there are any indexes ( there should be), and even auto_increment primary keys, are built after the data is populated. The data is taken as a csv using select into outfile. The triggers are taken from the data folder as files, and copied to a temporary folder.

The point where we had to break our heads were the functions and procedures, which was due to the mysql connector/v5 which we were using did not support delimiter, and finally what we did was to dump the proc table from mysql database with the same method. All the resulting files are zipped together with some meta information.

When the Restore was developed, we faced another problem, that simply dropping the TRG files did not activate the triggers, but we needed to restart the mysql server.

Finally all is well and the client is satisfied. The backups, taken will only be a full backup. But for the incremental one, it would be better to enable the replication service, and use mysqlbinlog.exe.