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.

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.

first time i blog...

Hello All
For everyone who knows me "Bashar Shannak", they know that i hate to blog or write eventually, and that maybe i'm working more than 17 hrs per day and on call 24/7, huuuh why is all that about? what's your job to work all these hours? and what's the interesting to work 24/7?

All these questions have one answer, just three letters enough to answer all these questions; it's a "DBA", when you are a DBA then you are dealing with the most important and critical part in any company, which is "DATA".

To be a professional DBA in my point of view only, you have treat the "DATA" as your own baby, watching it grow in front your eyes, and try to keep it away from any risk, and study how to keep it alive and available, and have many copies "Backups" from it, and spread these copies everywhere to ensure that your data will still remain somehow.

When you get a call at 3:00 am on weekend's night or holidays, you wake up so quickly to see what is going on, is it an attack on your baby "DATA", or a human error dropped everything, or hardware failure you can't have an access to your "DATA", sure it's one of them or more but for sure it is NOT a good news, it's 3:00 am sure it's not good news, then you have to prepare you plans to restore everything the way it was, and you do that, not for extra money, or for hear a thank you words, no no, it's your job, it's your baby, can you offer money for a DAD who taking care of his baby, right?

So what is good to be a DBA? you don't have extra money for your extra effort -in some companies-, you are always online, your job is so risky, any mistake worth much!!!! so why you choose to be a DBA????

When you are a DBA, it's not a skills you need to learn it's also natural of you, you love to be in control, you love to monitor what's going on and how things going on. you love to draw a plans to be ready for any risks you'll have, you love to keep tracking on your data efficiency.
But the most thing interesting to be an DBA is the feeling when you can solve any problem happened to you baby, watching it up again, this feeling draw a smile, not only on my face, also on all people who concern of this data.

So after all it's a great to be a DBA...... from my point only ;)