Cloud Zone is brought to you in partnership with:

' ! Moshe Kaplan constantly helps successful firms getting to the next level and he is thrilled to uncover some of his secrets. Mr. Kaplan is a seasoned project management and cloud technologies lecturer. He is also known to be a cloud and SCRUM evangelist Moshe is a dSero.com Co-Founder. He was a R&D Director at Essence Security, led RockeTier and served as a board member in the IGT and as a department head at a top IDF IT unit. Moshe holds M.Sc and B.Sc from TAU. Moshe is a DZone MVB and is not an employee of DZone and has posted 57 posts at DZone. You can read more from them at their website. View Full User Profile

5 Options to Backup Your MySQL and Keep Your SaaS Online

12.30.2011
| 3211 views |
  • submit to reddit
An old customer was facing a major issue. They just expanded their service from a local shop to a global "follow the sun" service, providing SaaS to worldwide customers.
Their MySQL server were the finest, and their procedures are the same, including backup procedures. Yet, concepts that matched a local shop my not fit a global firm.

Going from Local to Global
Their current procedures include both binary backup and mysqldump of the server. Although both procedures are aimed to keep the service available while performing the backup, the bottom line is that the CPU is highly utilized and the service is poor.

Backup Methods
There are two major backup methods (you can read more about them and others at Lenz Grimmer's presentation):

  1. mysqldump: This method extracts the database content into a flat file. This file includes all the DDL and DML SQL statements. Although this method is considered as a "hot backup", it tends to reduce the SLA and leave users unsatisfied. Moreover, if the database is not locked during the process, you may result an inconsistent backup.
  2. Binary files backup: This method requires stopping the MySQL server. On the other hand if you copy the binary file to a disk on the same physical server, it could be a very short downtime. In order to keep the files in a safe place, you should later on backup these copied files over the network. Please notice that this method has a another disadvantage: one of the binary files may be corrupted and you may not notice that till getting to the money time.
Where the Backup Can Be Performed?
There are three possible locations:

  1. The Master Server: The safest since we know that data was not "disappeared" during replication. The significant drawback is service downtime...
  2. The Existing Slave Server: At the risk losing information that was not replicated correctly, we get a service with no downtime. Yet, notice that if you use this server for other tasks you may affect these tasks SLA.
  3. Dedicated Slave: This can be a lightweight machine (yes, even a VPS) that can perform backups once or more times a day without causing downtime and without affecting the service at all.

The Bottom Line or How to Keep Their Service Available?
Well, in this case, I suggested the customer choosing the Dedicated Slave option that in a very small price helped him reduce the service downtime and provide great SLA to their global customers.

 

Source: http://top-performance.blogspot.com/2011/12/5-options-to-backup-your-mysql-and-keep.html

Published at DZone with permission of Moshe Kaplan, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)