MySQL Backup Techniques

MySQL Backup plan and implementation is one of the Most Critical and important technique that any successful DBA should have. In fact planning a MySQL backup strategy it's not that easy and can't be standard for all databases, so it's depend on what kind of system that database run, tables engine that database have, size of the database, traffic on the database, etc.

Systems are varying, some of it financial, educational, web 2.0 (such as forums, blogs, communities, etc), news, mobile services, etc. So defiantly there should be more than one strategy to take BACKUPs for these MySQL databases.

MySQL Engines varying too, you can have a database with MyISAM engine, other have InnoDB engine, some is mixed between these two engines, or also some database has NDB cluster, so you can't use one technique to all of these situation.

MySQL Databases also varying in its size, some database its size reaches in Terra Bytes, some of it didn't reach 2MB, so the techniques for taking these database backups also varying.

So we have to set a plan for backups after studying and analyzing what database we have, and here are some general tips:

· If you have a small database with all tables created by engine MyISAM, use a mysqlhotcopy utility rather than mysqldump, and that because it's faster when taking backups and restore it.

· If you have a large database with all tables created by engine MyISAM , you may use one of these techniques, replicate your database in another server and take the backup from the slave server, or turn on your binary log and copied daily, but if you use mysqlhotcopy or mysqldump, your database will be unavailable for a long time depends on how much large your database is.

· If you have a small database with all tables created by engine Innodb , you may use mysqldump to take a backup or using the commercial tool inoodb hotcopy.

· If you have a large database with all tables created by engine Innodb , you may use you may use one of these techniques, replicate your database in another server and take the backup from the slave server, or turn on your binary log and copied daily, and that will faster in taking backups and restore it.

· Another technique is clustering your database, and then all backups you take will not effect on the database and keep the availability to the maximum 99,999%.

These are some tips to how plan you MySQL backup strategy, and maybe in later I'll get deep in these techniques and show the advantages and disadvantages for each techniques.