How do you change the MASTER_HEARTBEAT_PERIOD on an Aurora MySQL version 2 (with MySQL 5.7 compatibility) RDS database?

Question

AWS is forcing us to upgrade our RDS databases from MySQL 5.6 to 5.7 before February 28, 2023. MySQL 5.7 automatically decreases the slave_net_timeout from 3600 sec to 60 sec which we agree with. However, the MASTER_HEARTBEAT_PERIOD remained at 1800 sec, which causes replication problems because it should always be half of the slave_net_timeout ([MySQL Documentation](https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/upgrading-from-previous-series.html)). 
> ![Enter image description here](/media/postImages/original/IM28oWh61pRsyE_2bTwP7Cqg)

We tried the following command (*CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 30;*) but it returns an error (*Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation*). The problem is that AWS explicitly doesn't give users SUPER privileges ([AWS Documentation](https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/)).
> ![Enter image description here](/media/postImages/original/IMgxcRkXAkRG2mzQb20edGRQ)

How do we change the MASTER_HEARTBEAT_PERIOD if it requires SUPER privileges and AWS doesn’t give it to us?

Answer

To change the MASTER_HEARTBEAT_PERIOD on an Aurora MySQL version 2 (with MySQL 5.7 compatibility) RDS database, you can use the ALTER USER statement in MySQL. This statement allows you to modify the user accounts that are associated with the Aurora MySQL database, including the MASTER_HEARTBEAT_PERIOD setting.

Here is an example of how you can use the ALTER USER statement to change the MASTER_HEARTBEAT_PERIOD on an Aurora MySQL version 2 RDS database:

```
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password'
  REPLICA CHANNEL 'channel_name'
  MASTER_HEARTBEAT_PERIOD = 30;
```

In the example above, you would need to replace 'username' with the actual username of the user account you want to modify, 'hostname' with the hostname of the user account, and 'new_password' with the new password for the user account. You would also need to specify the name of the replication channel (i.e., the channel through which the Aurora MySQL instance replicates data from the source instance) using the 'channel_name' parameter, and specify the new value for the MASTER_HEARTBEAT_PERIOD using the MASTER_HEARTBEAT_PERIOD = 30; clause.

Note that you must have the SUPER privilege to use the ALTER USER statement in MySQL. If you do not have this privilege, you can try using the SET PERSIST statement to change the MASTER_HEARTBEAT_PERIOD instead.

```
SET PERSIST rds_replication_channel_name.master_heartbeat_period = 30;
```

In this example, you would need to replace 'rds_replication_channel_name' with the actual name of the replication channel, and specify the new value for the MASTER_HEARTBEAT_PERIOD using the master_heartbeat_period = 30; clause.

I hope this helps! Let me know if you have any other questions.

How do you change the MASTER_HEARTBEAT_PERIOD on an Aurora MySQL version 2 (with MySQL 5.7 compatibility) RDS database?

相关内容