How to backup a Mix MySQL database

in my last post i was talking about MySQL Backup strategies and Techniques, one of the most critical and difficult to backup is when we have a mix database.

First what do we mean byMySQL Mix database? we previously mentioned that MySQL has many Engines, such as MyISAM, Innodb, memory, ndbcluster, etc, so we can have in one database multiple engines inside it.

so let's suppose that we have a database mixed between two engines, "MyISAM and Innodb", what's the big deal when you want to take a backup for this kind of database, let's exam some situations for taking backups and restore the backups back:

• what if we take backup by using mysqldump, if the database size was small, then no problem, but if it was a large database then we need hours between taking backups (write it to text file), and restore it back. and the restoration time will be greater than the backup time, because it's load data to indexes too, so it's not a solution.
  
• if we take backup by mysqlhotcopy, just the MyISAM tables will backed up, and not the Innodb, and that because the mysqlhotcopy's procedure is to enter inside the database folder and copy every thing inside it by command "cp", so also it's not a solution.
• what if we copy the whole mysql directory, that will take a long time based on how many databases there are, and what the size of ibdata, which is always so large, and that because innodb tables takes a lot of storage., so we can't depends on this.
• ok then, first technique that we can depends on is taking the MyISAM tables by mysqlhotcopy, and using in the same time mysqldump to backup the Innodb only, so by this we minimize the backup time but still have a long restoration time.
• a replication is a solution, let's have another mirror database in another server, so the master server will have all the reads and writes and the slave server will be offline, so we take the backups from slave server without making any load of locking in the online database.
• clustering also could be a good solution, but it's expensive, because it's need hardwares and resources.