Amazon Aurora
User Guide (API Version 2014-10-31)

Replication Between Aurora and MySQL or Between Aurora and Another Aurora DB Cluster

Because Amazon Aurora MySQL is compatible with MySQL, you can set up replication between a MySQL database and an Amazon Aurora MySQL DB cluster. We recommend that your MySQL database run MySQL version 5.5 or later. You can set up replication where your Aurora MySQL DB cluster is the replication master or the replica, and you can replicate with an Amazon RDS MySQL DB instance, a MySQL database external to Amazon RDS, or another Aurora MySQL DB cluster.

You can also replicate with an Amazon RDS MySQL DB instance or Aurora MySQL DB cluster in another AWS Region. When you're performing replication across AWS regions, ensure that your DB clusters and DB instances are publicly accessible. Aurora MySQL DB clusters must be part of a public subnet in your VPC.

If you want to configure replication between an Aurora MySQL DB cluster and an Aurora MySQL DB cluster in another region, you can create an Aurora MySQL DB cluster as a Read Replica in a different AWS Region than the source DB cluster. For more information, see Replicating Amazon Aurora MySQL DB Clusters Across AWS Regions.

Warning

When you replicate between Aurora MySQL and MySQL, you must ensure that you use only InnoDB tables. If you have MyISAM tables that you want to replicate, then you can convert them to InnoDB before setting up replication with the following command.

alter table <schema>.<table_name> engine=innodb, algorithm=copy;

Setting up MySQL replication with Aurora MySQL involves the following steps, which are discussed in detail following in this topic.

1. Enable Binary Logging on the Replication Master

2. Retain Binary Logs on the Replication Master Until No Longer Needed

3. Create a Snapshot of Your Replication Master

4. Load the Snapshot into Your Replica Target

5. Enable Replication on Your Replica Target

6. Monitor Your Replica

Setting Up Replication with MySQL or Another Aurora DB Cluster

To set up Aurora replication with MySQL, take the following steps.

1. Enable Binary Logging on the Replication Master

Find instructions on how to enable binary logging on the replication master for your database engine following.

Database Engine Instructions

Aurora

To enable binary logging on an Aurora MySQL DB cluster

Set the binlog_format parameter to ROW, STATEMENT, or MIXED. MIXED is recommended unless you have a need for a specific binlog format. The binlog_format parameter is a cluster-level parameter that is in the default cluster parameter group. If you are changing the binlog_format parameter from OFF to another value, then you need to reboot your Aurora DB cluster for the change to take effect.

For more information, see Amazon Aurora DB Cluster and DB Instance Parameters and Working with DB Parameter Groups and DB Cluster Parameter Groups.

RDS MySQL

To enable binary logging on an Amazon RDS DB instance

You cannot enable binary logging directly for an Amazon RDS DB instance, but you can enable it by doing one of the following:

MySQL (external)

To set up encrypted replication

To replicate data securely, you can use encrypted replication.

Note

If you don't need to use encrypted replication, you can skip these steps.

The following are prerequisites for using encrypted replication:

  • Secure Sockets Layer (SSL) must be enabled on the external MySQL master database.

  • A client key and client certificate must be prepared for the Aurora MySQL DB cluster.

During encrypted replication, the Aurora MySQL DB cluster acts a client to the MySQL database server. The certificates and keys for the Aurora MySQL client are in files in .pem format.

  1. Ensure that you are prepared for encrypted replication:

    • If you don't have SSL enabled on the external MySQL master database and don't have a client key and client certificate prepared, enable SSL on the MySQL database server and generate the required client key and client certificate.

    • If SSL is enabled on the external master, supply a client key and certificate for the Aurora MySQL DB cluster. If you don't have these, generate a new key and certificate for the Aurora MySQL DB cluster. To sign the client certificate, you must have the certificate authority key that you used to configure SSL on the external MySQL master database.

    For more information, see Creating SSL Certificates and Keys Using openssl in the MySQL documentation.

    You need the certificate authority certificate, the client key, and the client certificate.

  2. Connect to the Aurora MySQL DB cluster as the master user using SSL.

    For information about connecting to an Aurora MySQL DB cluster with SSL, see Using SSL with Aurora MySQL DB Clusters.

  3. Run the mysql.rds_import_binlog_ssl_material stored procedure to import the SSL information into the Aurora MySQL DB cluster.

    For the ssl_material_value parameter, insert the information from the .pem format files for the Aurora MySQL DB cluster in the correct JSON payload.

    The following example imports SSL information into an Aurora MySQL DB cluster. In .pem format files, the body code typically is longer than the body code shown in the example.

    call mysql.rds_import_binlog_ssl_material( '{"ssl_ca":"-----BEGIN CERTIFICATE----- AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE -----END CERTIFICATE-----\n","ssl_cert":"-----BEGIN CERTIFICATE----- AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE -----END CERTIFICATE-----\n","ssl_key":"-----BEGIN RSA PRIVATE KEY----- AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE -----END RSA PRIVATE KEY-----\n"}');

    For more information, see mysql_rds_import_binlog_ssl_material and Using SSL with Aurora MySQL DB Clusters.

    Note

    After running the procedure, the secrets are stored in files. To erase the files later, you can run the mysql_rds_remove_binlog_ssl_material stored procedure.

To enable binary logging on an external MySQL database

  1. From a command shell, stop the mysql service.

    sudo service mysqld stop
  2. Edit the my.cnf file (this file is usually under /etc).

    sudo vi /etc/my.cnf

    Add the log_bin and server_id options to the [mysqld] section. The log_bin option provides a file name identifier for binary log files. The server_id option provides a unique identifier for the server in master-replica relationships.

    If encrypted replication isn't required, ensure that the external MySQL database is started with binlogs enabled and SSL disabled.

    The following are the relevant entries in the /etc/my.cnf file for unencrypted data.

    log-bin=mysql-bin server-id=2133421 innodb_flush_log_at_trx_commit=1 sync_binlog=1

    If encrypted replication is required, ensure that the external MySQL database is started with SSL and binlogs enabled.

    The entries in the /etc/my.cnf file include the .pem file locations for the MySQL database server.

    log-bin=mysql-bin server-id=2133421 innodb_flush_log_at_trx_commit=1 sync_binlog=1 # Setup SSL. ssl-ca=/home/sslcerts/ca.pem ssl-cert=/home/sslcerts/server-cert.pem ssl-key=/home/sslcerts/server-key.pem

    Additionally, the sql_mode option for your MySQL DB instance must be set to 0, or must not be included in your my.cnf file.

    While connected to the external MySQL database, record the external MySQL database's binary log position.

    mysql> SHOW MASTER STATUS;

    Your output should be similar to the following:

    +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000031 | 107 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

    For more information, see Setting the Replication Master Configuration in the MySQL documentation.

  3. Start the mysql service.

    sudo service mysqld start

2. Retain Binary Logs on the Replication Master Until No Longer Needed

When you use MySQL binlog replication, Amazon RDS doesn't manage the replication process. As a result, you need to ensure that the binlog files on your replication master are retained until after the changes have been applied to the replica. This maintenance helps ensure that you can restore your master database in the event of a failure.

Find instructions on how to retain binary logs for your database engine following.

Database Engine Instructions

Aurora

To retain binary logs on an Aurora MySQL DB cluster

You do not have access to the binlog files for an Aurora MySQL DB cluster. As a result, you must choose a time frame to retain the binlog files on your replication master long enough to ensure that the changes have been applied to your replica before the binlog file is deleted by Amazon RDS. You can retain binlog files on an Aurora MySQL DB cluster for up to 90 days.

If you are setting up replication with a MySQL database or RDS MySQL DB instance as the replica, and the database that you are creating a replica for is very large, choose a large time frame to retain binlog files until the initial copy of the database to the replica is complete and the replica lag has reached 0.

To set the binlog retention time frame, use the mysql_rds_set_configuration procedure and specify a configuration parameter of 'binlog retention hours' along with the number of hours to retain binlog files on the DB cluster, up to 2160 (90 days). The following example that sets the retention period for binlog files to 6 days:

CALL mysql.rds_set_configuration('binlog retention hours', 144);

After replication has been started, you can verify that changes have been applied to your replica by running the SHOW SLAVE STATUS command on your replica and checking the Seconds behind master field. If the Seconds behind master field is 0, then there is no replica lag. When there is no replica lag, reduce the length of time that binlog files are retained by setting the binlog retention hours configuration parameter to a smaller time frame.

If you specify a value for 'binlog retention hours' that is higher than 2160, then 2160 is used.

RDS MySQL

To retain binary logs on an Amazon RDS DB instance

You can retain binlog files on an Amazon RDS DB instance by setting the binlog retention hours just as with an Aurora MySQL DB cluster, described in the previous section.

You can also retain binlog files on an Amazon RDS DB instance by creating a Read Replica for the DB instance. This Read Replica is temporary and solely for the purpose of retaining binlog files. After the Read Replica has been created, call the mysql_rds_stop_replication procedure on the Read Replica (the mysql.rds_stop_replication procedure is only available for MySQL versions 5.5, 5.6 and later, and 5.7 and later). While replication is stopped, Amazon RDS doesn't delete any of the binlog files on the replication master. After you have set up replication with your permanent replica, you can delete the Read Replica when the replica lag (Seconds behind master field) between your replication master and your permanent replica reaches 0.

MySQL (external)

To retain binary logs on an external MySQL database

Because binlog files on an external MySQL database are not managed by Amazon RDS, they are retained until you delete them.

After replication has been started, you can verify that changes have been applied to your replica by running the SHOW SLAVE STATUS command on your replica and checking the Seconds behind master field. If the Seconds behind master field is 0, then there is no replica lag. When there is no replica lag, you can delete old binlog files.

3. Create a Snapshot of Your Replication Master

You use a snapshot of your replication master to load a baseline copy of your data onto your replica and then start replicating from that point on.

Find instructions on how to create a snapshot of your replication master for your database engine following.

Database Engine Instructions

Aurora

To create a snapshot of an Aurora MySQL DB cluster

  1. Create a DB cluster snapshot of your Amazon Aurora DB cluster. For more information, see Creating a DB Cluster Snapshot.

  2. Create a new Aurora DB cluster by restoring from the DB cluster snapshot that you just created. Be sure to retain the same DB parameter group for your restored DB cluster as your original DB cluster. Doing this ensures that the copy of your DB cluster has binary logging enabled. For more information, see Restoring from a DB Cluster Snapshot.

  3. In the console, choose Instances and click the primary instance (writer) for your restored Aurora DB cluster to show its details. Scroll to Recent Events. An event message shows that includes the binlog file name and position. The event message is in the following format.

    Binlog position from crash recovery is binlog-file-name binlog-position

    Save the binlog file name and position values for when you start replication.

    You can also get the binlog file name and position by calling the describe-events command from the AWS CLI. The following shows an example describe-events command with example output.

    PROMPT> aws rds describe-events
    { "Events": [ { "EventCategories": [], "SourceType": "db-instance", "SourceArn": "arn:aws:rds:us-west-2:123456789012:db:sample-restored-instance", "Date": "2016-10-28T19:43:46.862Z", "Message": "Binlog position from crash recovery is mysql-bin-changelog.000003 4278", "SourceIdentifier": "sample-restored-instance" } ] }

    You can also get the binlog file name and position by checking the MySQL error log for the last MySQL binlog file position or for the DB_CRASH_RECOVERY_BINLOG_POSITION entry.

  4. If your replica target is an Aurora DB cluster owned by another AWS account, an external MySQL database, or an RDS MySQL DB instance, then you can't load the data from an Amazon Aurora DB cluster snapshot. Instead, create a dump of your Amazon Aurora DB cluster by connecting to your DB cluster using a MySQL client and issuing the mysqldump command. Be sure to run the mysqldump command against the copy of your Amazon Aurora DB cluster that you created. The following is an example.

    PROMPT> mysqldump --databases <database_name> --single-transaction --order-by-primary -r backup.sql -u <local_user> -p
  5. When you have finished creating the dump of your data from the newly created Aurora DB cluster, delete that DB cluster as it is no longer needed.

RDS MySQL

To create a snapshot of an Amazon RDS DB instance

  1. Create a Read Replica of your Amazon RDS DB instance. For more information on creating a Read Replica, see Creating a Read Replica in the Amazon Relational Database Service User Guide.

  2. Connect to your Read Replica and stop replication by running the mysql_rds_stop_replication procedure.

  3. While the Read Replica is Stopped, Connect to the Read Replica and run the SHOW SLAVE STATUS command. Retrieve the current binary log file name from the Relay_Master_Log_File field and the log file position from the Exec_Master_Log_Pos field. Save these values for when you start replication.

  4. While the Read Replica remains Stopped, create a DB snapshot of the Read Replica. For more information on creating a DB snapshot, see Creating a DB Cluster Snapshot.

  5. Delete the Read Replica.

MySQL (external)

To create a snapshot of an external MySQL database

  1. Before you create a snapshot, you need to ensure that the binlog location for the snapshot is current with the data in your master instance. To do this, you must first stop any write operations to the instance with the following command:

    mysql> FLUSH TABLES WITH READ LOCK;
  2. Create a dump of your MySQL database using the mysqldump command as shown following:

    PROMPT> sudo mysqldump --databases <database_name> --master-data=2 --single-transaction --order-by-primary -r backup.sql -u <local_user> -p
  3. After you have created the snapshot, unlock the tables in your MySQL database with the following command:

    mysql> UNLOCK TABLES;

4. Load the Snapshot into Your Replica Target

If you plan to load data from a dump of a MySQL database that is external to Amazon RDS, then you might want to create an EC2 instance to copy the dump files to, and then load the data into your DB cluster or DB instance from that EC2 instance. Using this approach, you can compress the dump file(s) before copying them to the EC2 instance in order to reduce the network costs associated with copying data to Amazon RDS. You can also encrypt the dump file or files to secure the data as it is being transferred across the network.

Find instructions on how to load the snapshot of your replication master into your replica target for your database engine following.

Database Engine Instructions

Aurora

To load a snapshot into an Aurora MySQL DB cluster

  • If the snapshot of your replication master is a DB cluster snapshot, then you can restore from the DB cluster snapshot to create a new Aurora MySQL DB cluster as your replica target. For more information, see Restoring from a DB Cluster Snapshot.

  • If the snapshot of your replication master is a DB snapshot, then you can migrate the data from your DB snapshot into a new Aurora MySQL DB cluster. For more information, see Migrating Data to an Amazon Aurora DB Cluster.

  • If the snapshot of your replication master is the output from the mysqldump command, then follow these steps:

    1. Copy the output of the mysqldump command from your replication master to a location that can also connect to your Aurora MySQL DB cluster.

    2. Connect to your Aurora MySQL DB cluster using the mysql command. The following is an example.

      PROMPT> mysql -h <host_name> -port=3306 -u <db_master_user> -p
    3. At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into the Aurora MySQL DB cluster, for example:

      mysql> source backup.sql;

RDS MySQL

To load a snapshot into an Amazon RDS DB instance

  1. Copy the output of the mysqldump command from your replication master to a location that can also connect to your MySQL DB instance.

  2. Connect to your MySQL DB instance using the mysql command. The following is an example.

    PROMPT> mysql -h <host_name> -port=3306 -u <db_master_user> -p
  3. At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into the MySQL DB instance, for example:

    mysql> source backup.sql;

MySQL (external)

To load a snapshot into an external MySQL database

You cannot load a DB snapshot or a DB cluster snapshot into an external MySQL database. Instead, you must use the output from the mysqldump command.

  1. Copy the output of the mysqldump command from your replication master to a location that can also connect to your MySQL database.

  2. Connect to your MySQL database using the mysql command. The following is an example.

    PROMPT> mysql -h <host_name> -port=3306 -u <db_master_user> -p
  3. At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into your MySQL database. The following is an example.

    mysql> source backup.sql;

5. Enable Replication on Your Replica Target

Before you enable replication, we recommend that you take a manual snapshot of the Aurora MySQL DB cluster or RDS MySQL DB instance replica target. If a problem arises and you need to re-establish replication with the DB cluster or DB instance replica target, you can restore the DB cluster or DB instance from this snapshot instead of having to import the data into your replica target again.

Also, create a user ID that is used solely for replication. The following is an example.

mysql> CREATE USER 'repl_user'@'<domain_name>' IDENTIFIED BY '<password>';

The user requires the REPLICATION CLIENT and REPLICATION SLAVE privileges. Grant these privileges to the user.

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'<domain_name>';

If you need to use encrypted replication, require SSL connections for the replication user. For example, you can use one of the following statement to require SSL connections on the user account repl_user.

GRANT USAGE ON *.* TO 'repl_user'@'<domain_name>' REQUIRE SSL;

Note

If REQUIRE SSL isn't included, the replication connection might silently fall back to an unencrypted connection.

Find instructions on how to enable replication for your database engine following.

Database Engine Instructions

Aurora

To enable replication from an Aurora MySQL DB cluster

  1. If your DB cluster replica target was created from a DB cluster snapshot, then connect to the DB cluster replica target and issue the SHOW MASTER STATUS command. Retrieve the current binary log file name from the File field and the log file position from the Position field.

    If your DB cluster replica target was created from a DB snapshot, then you need the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS command when you created the snapshot of your replication master.

  2. Connect to the DB cluster and issue the mysql_rds_set_external_master and mysql_rds_start_replication procedures to start replication with your replication master using the binary log file name and location from the previous step. The following is an example.

    CALL mysql.rds_set_external_master ('mydbinstance.123456789012.us-east-1.rds.amazonaws.com', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0); CALL mysql.rds_start_replication;

RDS MySQL

To enable replication from an Amazon RDS DB instance

  1. If your DB instance replica target was created from a DB snapshot, then you need the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS command when you created the snapshot of your replication master.

  2. Connect to the DB instance and issue the mysql_rds_set_external_master and mysql_rds_start_replication procedures to start replication with your replication master using the binary log file name and location from the previous step. The following is an example.

    CALL mysql.rds_set_external_master ('mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0); CALL mysql.rds_start_replication;

MySQL (external)

To enable replication from an external MySQL database

  1. Retrieve the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS command when you created the snapshot of your replication master. If your external MySQL replica target was populated from the output of the mysqldump command with the --master-data=2 option, then the binlog file and binlog position are included in the output. The following is an example.

    -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.000031', MASTER_LOG_POS=107;
  2. Connect to the external MySQL replica target, and issue CHANGE MASTER TO and START SLAVE to start replication with your replication master using the binary log file name and location from the previous step, for example:

    CHANGE MASTER TO MASTER_HOST = 'mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = 'mysql-bin-changelog.000031', MASTER_LOG_POS = 107; START SLAVE;

6. Monitor Your Replica

When you set up MySQL replication with an Aurora MySQL DB cluster, you must monitor failover events for the Aurora MySQL DB cluster when it is the replica target. If a failover occurs, then the DB cluster that is your replica target might be recreated on a new host with a different network address. For information on how to monitor failover events, see Using Amazon RDS Event Notification.

You can also monitor how far the replica target is behind the replication master by connecting to the replica target and running the SHOW SLAVE STATUS command. In the command output, the Seconds Behind Master field tells you how far the replica target is behind the master.

Stopping Replication Between Aurora and MySQL or Between Aurora and Another Aurora DB Cluster

To stop binlog replication with a MySQL DB instance, external MySQL database, or another Aurora DB cluster, follow these steps, discussed in detail following in this topic.

1. Stop Binlog Replication on the Replica Target

2. Disable Binary Logging on the Replication Master

1. Stop Binlog Replication on the Replica Target

Find instructions on how to stop binlog replication for your database engine following.

Database Engine Instructions

Aurora

To stop binlog replication on an Aurora MySQL DB cluster replica target

Connect to the Aurora DB cluster that is the replica target, and call the mysql_rds_stop_replication procedure. The mysql.rds_stop_replication procedure is only available for MySQL versions 5.5 and later, 5.6 and later, and 5.7 and later.

RDS MySQL

To stop binlog replication on an Amazon RDS DB instance

Connect to the RDS DB instance that is the replica target and call the mysql_rds_stop_replication procedure. The mysql.rds_stop_replication procedure is only available for MySQL versions 5.5 and later, 5.6 and later, and 5.7 and later.

MySQL (external)

To stop binlog replication on an external MySQL database

Connect to the MySQL database and call the STOP REPLICATION command.

2. Disable Binary Logging on the Replication Master

Find instructions on how to disable binary logging on the replication master for your database engine following.

Database Engine Instructions

Aurora

To disable binary logging on an Amazon Aurora DB cluster

  1. Connect to the Aurora DB cluster that is the replication master, and set the binlog retention time frame to 0. To set the binlog retention time frame, use the mysql_rds_set_configuration procedure and specify a configuration parameter of 'binlog retention hours' along with the number of hours to retain binlog files on the DB cluster, in this case 0, as shown in the following example.

    CALL mysql.rds_set_configuration('binlog retention hours', 0);
  2. Set the binlog_format parameter to OFF on the replication master. The binlog_format parameter is a cluster-level parameter that is in the default cluster parameter group.

    After you have changed the binlog_format parameter value, reboot your DB cluster for the change to take effect.

    For more information, see Amazon Aurora DB Cluster and DB Instance Parameters and Modifying Parameters in a DB Parameter Group.

RDS MySQL

To disable binary logging on an Amazon RDS DB instance

You cannot disable binary logging directly for an Amazon RDS DB instance, but you can disable it by doing the following:

  1. Disable automated backups for the DB instance. You can disable automated backups by modifying an existing DB instance and setting the Backup Retention Period to 0. For more information, see Modifying a DB Instance Running the MySQL Database Engine and Working With Backups in the Amazon Relational Database Service User Guide.

  2. Delete all Read Replicas for the DB instance. For more information, see Working with Read Replicas of MariaDB, MySQL, and PostgreSQL DB Instances in the Amazon Relational Database Service User Guide.

MySQL (external)

To disable binary logging on an external MySQL database

Connect to the MySQL database and call the STOP REPLICATION command.

  1. From a command shell, stop the mysql service,

    sudo service mysqld stop
  2. Edit the my.cnf file (this file is usually under /etc).

    sudo vi /etc/my.cnf

    Delete the log_bin and server_id options from the [mysqld] section.

    For more information, see Setting the Replication Master Configuration in the MySQL documentation.

  3. Start the mysql service.

    sudo service mysqld start