Engine Yard Developer Center

Promote a Database Replica

The database promotion feature allows you to demote a running database master. You can now change the size (volume or instance) of your database master or run a long migration on a replica and make it your new master. This feature comes with a few caveats. Read all sections carefully before you start a promotion:

Important: Do not initiate an application master takeover if you have initiated a database replica promotion. Initiating two major environment configurations simultaneously may leave your environment in an erroneous state. For example, the dashboard may show that an app slave has been successfully promoted when it has not. Wait until one takeover process has thoroughly completed before initiating a new one.

Use Cases

Recommended use-cases for replica promotion:

  • Replacing a defective or unresponsive database master.
  • Changing your database instance size (by creating a replica of the preferred instance size and promoting it).
  • Increasing the size of your database volume (by creating a replica with a larger /db volume and promoting it).
  • (MySQL only) Performing long migrations on a replica, then replacing the current database master with the migrated replica.

You can not undo a database promotion. Make sure you are promoting a database for the right reasons. Do not use the database promotion feature as a solution to fix the following problems:

  • High load
  • Table locks
  • InnoDB lock waits or dead locks
  • A full disk (100% utilized) on the database master (contact support for assistance in resolving this issue without data loss)
  • Swap

If you are unsure if a database replica promotion is right for you, open a ticket with Engine Yard Support.

Prerequisites

  • You have an environment with a database master and at least one healthy database replica that you have designated for promotion.
  • Your database replica should be current with the database master. We check for concurrency before initiating the promotion. See About Database Replica Promotion to understand our definition of current. To verify that the replica is current, execute the following commands:

    MySQL

    mysql -u root -e"SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"

    PostgreSQL

    psql -U postgres -t -c "SELECT (now() - pg_last_xact_replay_timestamp()) AS time_lag;"
  • Your application must be placed in maintenance mode to ensure that all traffic has stopped going to your application and database.

How to Promote your Database Replica

Engine Yard recommends that you perform these promotion and post-promotion tasks during your low-traffic hours or your typical maintenance window.

To promote your database replica:

  1. Log in to your Engine Yard account and navigate to the relevant environment.
  2. Cancel traffic to your app, workers or background and cron jobs (don't stop the environment). Then, enable your maintenance page using one of the following methods:
    • Navigate to the relevant environment, select Maintenance from the More Options area, and make the necessary configuration changes.
    • Use the ey web disable command on the Engine Yard CLI.
  3. Verify that there are no remaining database connections. See Checking for Connections regarding how to verify the connection.
  4. Click the Promote button associated with the database replica designated for promotion.

    promote_option_screenshot.png

    A confirmation page is displayed.

  5. Click the Promote Replica button and enter your Engine Yard password to proceed.

    Note: The default environment configuration has the Promotion Protection feature enabled, which requires you to re-enter your password.

    Your database master is being locked for writes and your replica is being promoted.

    replica_in_progress_screenshot.png

The promotion task is successful when you see that your database replica is now the new master. However, you must perform the post-promotion tasks to connect your application to the new database master.

You can see all promotion history by clicking the Promotions link in the More Options area of your environment.

Post-Promotion Tasks

These post-promotion tasks are critical for restoring functionality to your application. When you are ready, do the following:

  1. Redeploy your application servers to re-generate connectivity information to the new database master.
  2. Take your application out of maintenance mode (which will re-enable your application) using one of the following methods:
    • Navigate to the relevant environment, select Maintenance from the More Options area, and make the necessary configuration changes.
    • Use the ey web enable command on the Engine Yard CLI.

    Your application is live and available to your customers after you take it out of maintenance mode.

Note: The original database master remains in your environment as a detached utility instance. You can delete this instance or open a ticket with Engine Yard Support to initiate a root cause analysis of the failed instance.

utility_instance_screenshot.png

More information

This table provides other resources related to database promotion.

For more information about... See...
How database replica works About Database Replica Promotion
Adding a database instance to an environment Add a database replica (slave) to an existing environment
How to restore or load a database Restore or Load a Database
Your database Find Information about your Database

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.

Was this article helpful?
4 out of 4 found this helpful
Have more questions? Submit a request

Comments

  • Avatar
    Joe Heth

    When trying to run the mysql slave status command I get the following error:

    ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation

  • Avatar
    Tyler Poland

    Hi Joe,

    Thank you for the note. This command actually does need to be run as the root database user so I've adjusted the documentation accordingly. If you have any questions or we can assist further please let us know.

    Tyler

Please sign in to leave a comment.

Powered by Zendesk