Adding One More MySQL Slave to Cloud Based System (Amazon AWS)

MySQL Replication tends to be complex.

Creating a new slave to support your system expansion is not easier: coping large data files over the LAN, recovering replication, etc.

Fortunately, virtualization and SAN help us do it easier and faster.

Lucky us, Cloud Computing make it even easier.

Adding a New Slave is Easier than Ever

1. Select an existing slave that can be reboot w/o affecting the service.
1. Take the slave out of the production environment.
2. Stop the MySQL slave and perform a read lock: SLAVE STOP;FLUSH TABLES WITH READ LOCK;
3. Create an AMI based on this instance using AWS console or API.
4. Recover the instance upon AMI creation completion and get the slave back to production: UNLOCK TABLES;SLAVE START;SHOW SLAVE STATUS\G
2. Create a new slave based on the AMI
1. Launch a new slave using the AWS console or API.
2. Change the server's /etc/my.cnf file to fit a new slave instance:
1. Change the server-id=N value to a new numeric value.
2. Set relay-log='server-relay-bin' to the original slave server relay log name. (if you want to make it even easier, configure in the first slave's my.cnf relay-log=mysqld-relay-bin, and you'll be able to avoid this step).
3. Restart the MySQL instance: sudo /etc/init.d/mysql restart
4. Recover the MySQL: UNLOCK TABLES;SLAVE START;SHOW SLAVE STATUS\G
5. Add this new slave to the production environment.

Bottom Line

Expanding your business today is easier w/o the need for file copy or any other tedious action.

Keep Performing,

Moshe Kaplan 

How to Replicate Part of the Tables in MySQL

When you build a DWH or another intensive processing on a database slave copy, you may not want to replicate the whole database. 


How can you replicate only some of the tables or even part of existing tables to a slave?
In General there are 4 ways to replicate data:

1. Database Definition: You can define what database names will or won't be replicated. You can place all the tables that need to be replicated in one database and all the other in another one. That way you can replicate only the first database.
2. Tables Definition: You can define what specific table names you can replicate and what not.
3. Part of a Table: Replication by SELECT limitation is supported starting from version 5.1.21 instantly. Therefore, you can easily replicate only several columns (replication discards non existing columns if they do not exist in the slave). In earlier versions, you could perform Vertical Sharding, where you could keep some of the columns in one table, and the other in another table. Then you may replicate only one of the tables.
4. Part of a Table: Replication by WHERE limitation is not supported instantly. Therefore, you cannot easily replicate only some of the rows from a single table. Yet, you can perform Horizontal Sharding, where you can keep rows groups in different tables. Then you may replicate only some of the tables to your slave.

When Should You Avoid Partial Replication?

If your slave is being used as a copy for DRP or HA, you may still want it to fully match your master. If the copy is done for specific processing and you want to avoid unneeded replication, this is definitely the right tool for you.

Syntax is the King

Most of the syntax is performed in the slave and it is detailed in Sunny Walia post.

Keep Performing,

Moshe Kaplan

MySQL DRP Done Right

When your systems are up and running and money is getting into the bank, it is the best time to ask yourself: What if my system fails? What if my data center (yes, even your cloud one) goes down?


The Instant DRP Solution
The instant solution is preparing a backup data center. Then you should make sure that the web and application servers will be ready to take over when needed. Yet, what about taking care of your data? How will the database be managed across two different data centers?


Many cloud, web, mobile and service providers base their systems on MySQL. Therefore, this post is dedicated to best MySQL DRP strategies..


Fail and Then Manually Recover
The most simple and easy to implement method is the Master-Slave schema. This method supports:

1. "Master": A single database server that serves for write and read.
2. "Slaves" One or more servers that serves for read only.

When the master fails, you can promote one of the slaves to become the master.
This method can also be used to reduce stress on the master and enable various partitioning modes.


Fail and Recover Automatically
A more complex way to implement, but faster to recover (if nothing goes wrong) is implementing the Master-Master schema. In this schema both servers serve as masters and recover when one fails. Please notice that this method is a little bit tricky and you should avoid conflicts since the conflict resolution is poor:

1. You should need to either avoid auto increment fields or make sure that only one server is an active master at a time.
2. You should avoid same record modified by both parties.

Do It the Cloud Way
Cloud providers propose these days cloud based database (PaaS). These solutions remove the database management headache from your task list. Amazon for example provides the Rational Database Service (Amazon RDS). RDS provides a high available managed MySQL instance that supports instant recovery to any point in time. This service is more expensive than having your own MySQL instance, but if you are a medium shop, it may be the best offer for you.


Bottom Line
Select your MySQL DRP method based on your resources, recovery needs and the effort you willing to allocate. Choose Master-Slave for novice, Master-Master for experts and the cloud way to avoid the headache.


Keep Performing,
Moshe Kaplan 


P.S feel free to contact for DRP and MySQL consulting

Should MySQL backup be equal to system downtime?

I had a short conversation with my mate, Romi Kuntsman regarding a common issue in current systems: the database backup.
Since database systems are very active, the database backup process takes relatively long time and the whole system performance is going down (or in some cases the whole system stops responding and going down...)

Should I backup or Should I don't?
Well, first of all, keep backup your system. You never know when your system will corrupt due to hardware failure, when a hacker will decide to "check" your system, or when the regulator will visit your offices.

So how should I keep my system responding to users?
Well... lets take a look at storage systems: when you have a large storage machine (SAN) you usually do not backup the primary site machine, but rather backup the secondary storage machine by splitting it from the main site (or by using a snapshot of the secondary site). This way your primary machine keeps serving clients with interference, while the secondary machine is taking care of the backup. When backup is finished, the sync between the machines is being restored.

So it works for storage systems, how can it work for databases?
Well lets implement a similar design to your database system:

1. Install another MySQL instance
2. Configure this instance to be a slave of your master database
3. Schedule a job to bring down the sync, backup the slave and bring back the sync
   

And what about the hot backup?
Well, if you still want to maintain high availability during backup (and you should), implement a two slaves configuration, where the first slave is used for high availability and the second for backup.

Keep Performing,
Moshe Kaplan. RockeTier. The Performance Experts.