At one company I worked for, a rolling window of raw data equated to about 6 months and ate up 10 TB. The data was then processed into an RDBMS format which cost 6 TB of usable data which accounted for about 10 years of reportable data. The point being that at scale, these kinds of practices simply aren' t practical. Storage is expensive - probably the greatest compute expense. This provides several interesting challenges:

1. Backups - the innodb plugins are great and all, but the backup times on that much data just aren't that practical
2. Restore times - for large datasets - especially if you need replication to catch up after a restore getting back to an operational state can take days or even weeks
3. Creating/seeding new instances - Often the work you may be doing in dev/test involves ETL (Extract, Transform and Load) jobs on your dataset. These need to be validated using QA testing units, but this needs to be done in a non-destructive manner so that the origional production dataset is preserved. While in a disaster, you may be willing to deal with a long restore time on the understanding that backups are an insurance policy and the intent is to avoid them, the DevOps development workflow requires that, essentially, you be able to perform a restore or copy of your data on a regular basis (perhaps multiple times a day)
4. Capacity - Slinging around that much data at the scale I just described can be very I/O intensive. No only do you need to address the problems described in 1-3, but you need to do it in a way that doesn't cause outages or performance slowdowns to your production systems.

While the above considerations may not be a concern at smaller scales, at larger scales, these become huge problems. This means that it is extremely important that you define your requirements and forecast the size of your dataset.

Defining requirements

As a result, you need to define several requirements:

1. RTO - RTO or Restore Time Objective for backups are one of the most important drivers of database backup solutions. While, at first it might not appear relevant to most of the other problems, it becomes extremely relevant when you ask "What if I used my backup solution for creating or seeding new instances?". I'll cover some techniques for doing so in the next section.
2. RPO - RPO or Restore Point Objective for backups defines A) how far back you are able to restore (minutes, hours, days, weeks, months, or years) B) The backup interval at different tiers and C) how granularly you can restore. For example, for E-mail databases, Message Level Backups - restoring a specific E-mail - are often sought. Similarly, you may find that data over a few days is completely useless - so there is no point restoring back a year.
3. Size of your dataset - This is important because for a 1MB database, your RTO may be achieved with most backup products and solutions. For a 10TB database however, you will find that a full, row level backup to LTO 3 tape probably won't achieve your RTO and could interfere with your RPO because backups begin exceeding your backup window. You can't exactly just do a mysqldump on this large of a dataset, but could probably get away with that on the 1MB database.
4. Database consistency - A final thing that makes an immense difference in continuous deployment, site reliability, scalability and high-availablity when working with databses is your need (or lack thereof) for consistency. There are three basic types: immediate consistency, Just-In-Time (JIT) consistency and eventual consistency

In addition to the above major considerations, you also need to consider licensing and support requirements (open source or closed source; in house support, third party support or vendor support) application/language requirements (the connectors for many databases can be important; is your app compiled? Do you have access to the source code? Can you recompile it, or is it provided by a vendor? Or does it run on an interpreted language?) political requirements (Does your organization only trust Oracle? Do they hate oracle? Do they not trust MySql? How do you feel about MariaDB or Postgres?) and database engine (innoDB? MyISAM? Blackhole? NDB Cluster? Spider?) and historical or compatability requirements (we used PL/SQL for years and half our code is built into the oracle engine! How could we ever port over to MariaDB?!?)

With the generic considerations out of the way, let me provide you with some techniques and technologies for addressing the above. First, ask yourself if you really need to use an RDBMS or if unstructured data with something like Hadoop, CouchDB or even Object Oriented Storage (something like swift) is an option.

Second, consider looking into a cloud based solution. This outsources some of this headache and leaves the complicated problems to highly qualified (and paid) individuals. At scale however, you can find this really eats into your budget (cloud providers DO make a profit at this, and at a certain scale, you can just afford to employ these experts yourself,) or if you are working under specific security or political requirements (read: we can't do clouds) consider a hybrid NFS/FibreChannel Filer. Most of these filers, such as those by NetApp, Pure Storage and Tegile support a delta based snapshotting and cloning technique that can be very handy for A) taking backups, B) Restoring backups and C) Seeding new backups.

But that being said, these products allow you to take differential snapshots underneath your databse. You will need to script out a full "lock tables with read lock" on one of your database instances (a read-only slave is recommended) and dump your binlog position or GTID but for these filers, once you do, you will be able to use these snaps to create new instances of your database. You will want to put binlogs on a seperate partition and put only your database data on these partitions. Once you do, you will be able to clone these partitions (on NetApps, this is know as a "FlexClone") The only issue with these is that they cannot span datastores which means that all your I/O is still impacting the same datastore/volume and calculating deltas can cause some overhead in terms of CPU resources.

This is because for each block read the filer must determine if the data resides in the frozen origional snapshot, or in the delta. For volumes/stores with multiple snapshots, this might need to be checked multiple times. You can overcome this by refreshing the data (meaning, discard your snapshot and clone it again periodically - which might happen naturally and organically for a good continuous deployment environment) Or by permanently splitting the volume (known as a "Flex Split" in NetApp terminology) which will take a moment to permanently resolve the deltas and create an entirely new and seperate volume.

These delta clones have the added benefit of reducing your overall storage requirement - you can spawn several clones or instance of your production database data to do your development, testing and validation. If you are only keeping one copy of your large dataset plus the (what are likely to be) small deltas, you reduce your overall storage cost and footprint.

This however has one major flaw: All of your data - dev, test and prod is still using I/O on the same filer and storage head. To work around this, consider creating a slave database instance on a second filer which can be the seeding point for you Test and/or dev filer, or consider using a load balancer/applcation delivery controller for your application layer to mirror production requests into your testing (and/or dev) environment(s). This has the added benefit of throwing prodcution traffic at your QA/Test environment before promoting to production for issues that might not be immediately noticed. You can then check your logs for errors based on production traffic and user behavior.

This then should allow you to use a few script to programatically spawn and destroy entire (and large) datasets for use with continuous deployment methodologies.

Scalability and High Availability

Typically however, most poeple do not need fully synchronous replication - this is usually only needed for very specific (and exotic) high-write environments where multi-master is needed with table sharding. Most apps can deal with Just-In-Time consistency using a database proxy. For example, ScaleArc will monitor replication status and track where writes just went (to send subesquent read requests until replication catches up) to provide Just-In-Time consistency and the appearance of database consistency. ScaleArc is compatable with Postgres, MySQL, MariaDB, Oracle and MSSQL and can use regular expressions to shard/partition your databases for applications that can't use shard keys. It also has a robust REST API for your configuration management software to interact with - and their support team is outstanding

Postgres and Oracle should have the replication and sharding features you need, but ScaleArc will pair well if you need a proxy.

Ultimately, all these peices add up to a highly flexible environment suitable for continuous deployment and development if you are unable to simply use a cloud based environment and let your cloud provider deal with the above problems for you.

At one company I worked for, a rolling window of raw data equated to about 6 months of data and ate up 10 TB. The data was then processed into an RDBMS format, with some data portions hitting the cutting room floor. After, this dataset still weighed in at 6 TB of usable data which accounted for about 10 years of reportable data. 

The point being that at scale, these kinds of practices (never modifying or removing data) simply isn't practical. Storage is expensive - probably the greatest compute expense of most companies. This provides several interesting challenges:

1. Backups - the InnoDB plugins are great and all, but the backup times on that much data just isn't that practical.
2. Restore times - for large datasets - especially if you need replication to catch up after a restore getting back to an operational state can take days or even weeks.
3. Creating/seeding new instances - Often the work you may be doing in dev/test involves ETL (Extract, Transform and Load) jobs on your dataset. These jobs need to be validated using QA testing units, but this needs to be done in a non-destructive manner so that the original production dataset is preserved. While in a disaster, you may be willing to deal with a long restore time on the understanding that backups are an insurance policy (and the intent is to avoid disasters) the DevOps development workflow requires that, essentially, you be able to perform a restore or copy of your data on a regular basis (perhaps multiple times a day).
4. Capacity - Slinging around that much data at the scale I just described can be very I/O intensive. Not only do you need to address the problems described in 1-3, but you need to do it in a way that doesn't cause outages or performance slowdowns to your production systems.

While the above considerations may not be a concern at smaller scales, at larger scales, these become huge problems to tackle. This means that it is extremely important that you define your requirements and forecast the size of your dataset.

Defining requirements

As a result of that excercise, you need to define several requirements:

• RTO - RTO or Restore Time Objective for backups is one of the most important drivers of database backup solutions. While at first it might not appear relevant to most of the other problems described, it becomes extremely relevant when you ask "What if I used my backup solution for creating or seeding new instances?" (I'll cover some techniques for doing that in the next section)
• RPO - RPO or Restore Point Objective for backups defines A) how far back you are able to restore to (minutes, hours, days, weeks, months, or years) B) The backup interval or frequency at different tiers and C) how granularly you can restore. For example, for E-mail databases, Message Level Backups - restoring a specific E-mail - are often sought. Similarly, you may find that data older than a few days is completely useless - so there is no point restoring back a year. It depends on the kind of data you are working with.
• Size of your dataset - This is important because for a 1MB database, your RTO may be achieved with most backup products and solutions. For a 10TB database however, you will find that a full, row level backup to LTO 3 tape probably won't achieve your RTO and could interfere with your RPO because backups begin exceeding your backup window. You can't exactly just do a mysqldump on this large of a dataset, but could probably get away with that on the 1MB database.
• Database consistency - A final thing that makes an immense difference in continuous deployment, site reliability, scalability and high-availability when working with databases is your need (or lack thereof) for consistency. There are three basic types: immediate consistency, Just-In-Time (JIT) consistency and eventual consistency.

In addition to the above major considerations, you also need to consider:

• Licensing and support requirements (open source or closed source; in house support, third party support or vendor support, and does this fit your budget?)
• Application/language requirements (the connectors for many databases can be important; is your app compiled? Do you have access to the source code? Can you recompile it, or is it provided by a vendor? Does it run on an interpreted language?)
• Political requirements (Does your organization only trust Oracle? Do they hate Oracle? Do they not trust MySql? How do you feel about MariaDB or Postgres? Do we require an EAL certified DB product due to contracts?)
• Database engine (innoDB? MyISAM? Blackhole? NDB Cluster? Spider?)
• Historical or compatibility requirements (we used PL/SQL for years and half our code is built into the oracle engine! How could we ever port over to MariaDB?!?)

With the generic considerations out of the way, let me provide you with some techniques and technologies for addressing some of the above. First, ask yourself if you really need to use an RDBMS or if unstructured data with something like Hadoop, CouchDB, KV pairs, or even Object Oriented Storage (something like swift) is an option.

Second, consider looking into a cloud based solution. This outsources some of this headache and leaves the complicated problems to highly qualified (and paid) individuals. At scale however, you can find this really eats into your budget (cloud providers DO make a profit at this, and at a certain scale, you can just afford to employ these experts yourself) or if you are working under specific security or political requirements (read: we can't do clouds). If you need to run in-house, consider a hybrid NFS/Fibre Channel Filer. Most of these filers, such as those by NetApp, Pure Storage and Tegile support a delta-based snapshotting and cloning technique that can be very handy for A) taking backups, B) restoring backups and C) seeding new backups.

But that being said, some of these products allow you to take differential snapshots underneath your database. You will need to script out a full "lock tables with read lock" on one of your database instances (a read-only slave is recommended) and dump your binlog position or GTID for InnoDB based engines. But, when using these filers, once you script that, you will be able to use these snapshots to create new instances of your database. You will want to put binlogs on a separate partition and put only your database data on the other partition. With this, you will be able to clone these partitions (on NetApps, this is know as a "FlexClone") The only issue with this technique is that FlexClones cannot span datastores which means that all your I/O is still impacting the same datastore/volume and calculating the deltas on these volumes can cause some overhead in terms of CPU resources.

This is because for each block read, the filer must determine if the data resides in the frozen original snapshot, or in the delta. For volumes/stores with multiple snapshots, this might need to be checked multiple times. You can overcome this by refreshing the data (meaning, discard your snapshot and clone it again periodically - which might happen naturally and organically for a good continuous deployment environment) or by permanently splitting the volume (known as a "Flex Split" in NetApp terminology) which will take a moment to permanently resolve the deltas and create an entirely new and separate volume.

These delta clones have the added benefit of reducing your overall storage requirement - you can spawn several clones or instance of your production database to do your development, testing and validation. If you are only keeping one copy of your large dataset plus the (what are likely to be) small deltas, you reduce your overall storage cost and footprint.

This however has one major flaw: All of your data - dev, test and prod is still using I/O on the same filer and storage head. To work around this, consider creating a slave database instance on a second filer which can be the seeding point for you Test and/or dev filer, or consider using a load balancer/applcation delivery controller for your application layer to mirror production requests into your testing (and/or dev) environment(s). This has the added benefit of throwing prodcution traffic at your QA/Test environment before promoting deployments and code to production and allows you to test for issues that might not be immediately noticed otherwise. You can then check your logs for errors based on production traffic and user behavior in test.

This should allow you to use a few script to programatically spawn and destroy entire (and large) datasets for use with continuous deployment methodologies.

Scalability and High Availability

Typically however, most people do not need fully synchronous replication - this is usually only needed for very specific (and exotic) high-write environments where multi-master is needed with table sharding. Most apps can deal with Just-In-Time consistency using a database proxy. For example, ScaleArc will monitor replication status and track where writes just went (to send subsequent read requests to that location until replication catches up) in order to provide Just-In-Time consistency and the appearance of database consistency. ScaleArc is compatible with Postgres, MySQL, MariaDB, Oracle and MSSQL and can use regular expressions to shard/partition your databases for applications that can't use shard keys. It also has a robust REST API for your configuration management software to interact with - and their support team is outstanding.

Postgres and Oracle (Eg, GoldenGate and RAC Cluster) should have the replication and sharding features you need, but ScaleArc will pair well if you need a proxy.

Ultimately, all these pieces and technologies add up to a highly flexible environment suitable for continuous deployment and development if you are unable to simply use a cloud based environment and let your cloud provider deal with the above problems for you.