To replicate your MySQL on RDS tables Alooma needs a read-replica, user, and password to be set up.
While this is officially an optional step, we highly recommend it as it will significantly lessen the load of replication on your MySQL.
Setting up MySQL can be done in 3 steps: creating the replica, configuring it, and creating a user.
- Go to your Amazon RDS Dashboard. Select Instance Actions and then Create Read Replica.
- In the sidebar, click on Parameter Groups, then click the blue Create DB Parameter Group button. You will see a screen with 3 boxes and enter the settings:
DB Parameter Group Family: mysql5.6 DB Parameter Group Name: mysql56sync DB Parameter Group Description: MySQL5.6 for Sync
- Once completed, click Create.
- Next, select the icon to the left of the newly created parameter group, mysql56sync and click Edit Parameters. You will need to set a few of the parameters as follows:
binlog_format: ROW log_bin_use_v1_row_events: 1 read_only: 0 net_read_timeout: 3600 net_write_timeout: 3600 wait_timeout: 86400
- Once complete, click Save Changes to save your new settings.
- Click on Instance Actions dropdown and choose Modify:
- The Modify DB instance dialog will appear:
Set the following values (the other values can be the default value or be left empty):
Note: You will be required to enter a New Master Password. However, the password can be anything you choose (including your current password).
DB Engine Version: MySQL 5.6.xx (default) New Master Password: <YOUR_PASSWORD> Parameter Group: mysql56sync Apply Immediately: Check
- Once completed, select Apply Immediately and click Continue. When asked for confirmation, click Modify DB Instance:
You will now need to wait for the replica to become available before you can proceed... so take a break, have a coffee .
Once the instance is ready, select Instance Actions and then Modify. Set the following parameters:
Backup Retention Period: 7days Apply Immediately: Check
Now that you’ve modified the replica, once again you will need to wait until it becomes available before proceeding .
Almost done! It's time to create a user for your new replica.
- Log into the master server from a MySQL client (e.g., MySQL Workbench) with the master account.
- Create a user to be used only by our sync process. You can do this in MySQL like so:
mysql> CREATE USER 'alooma'@'%' IDENTIFIED BY 'YOUR_PASSWORD'; mysql> GRANT REPLICATION SLAVE, SELECT, RELOAD, REPLICATION CLIENT,
LOCK TABLES, EXECUTE ON *.* TO 'alooma'@'%'; mysql> FLUSH PRIVILEGES;
- For YOUR_PASSWORD, enter the password you want to use for this new user.
[Optional] Binary log retention
To ensure proper replication, it is our recommendation to retain binary logs for at minimum 7 days (168 hours). To do this, you can enter the following MySQL command:
mysql> call mysql.rds_set_configuration('binlog retention hours', 168);
That's it, you're ready to continue to connecting to MySQL.