Epsio Deployment Guide - MySQL¶

This guide will walk you through the process of preparing your PostgreSQL database for Epsio deployment.

Before you begin¶

Before proceeding with the deployment guide, ensure that you have the following:

A MySQL Database with version 5.7+.

1. Prepare and configure epsio user¶

This step will walk you through setting up your database to allow Epsio to connect to it. In this step, you will create a user Epsio can use, as well as a database Epsio will be able to create procedures/functions in.

Open a connection to your database and follow the steps below.

Create a database user for Epsio's exclusive use:

CREATE USER epsio_user IDENTIFIED BY '<password>';

Grant user permissions
Grant the epsio_user access to all tables in your database you want to creates views over by running the following commands:

-- Replication permissions
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO epsio_user;

-- Replace *.* with the relevant database names and table names you intend on using.
GRANT SELECT, CREATE, SHOW VIEW, CREATE VIEW, ALTER, INSERT, LOCK TABLES ON *.* TO epsio_user;

Create a database for Epsio's metadata:

NOTE: In case database will serve only as a source for Epsio, you can skip this step.

CREATE DATABASE epsio;
GRANT ALL PRIVILEGES ON epsio.* TO epsio_user WITH GRANT OPTION;

-- Grant additional permissions to locations you want Epsio to be able to create views.
GRANT CREATE, SHOW VIEW, CREATE VIEW, ALTER, INSERT, DELETE, DROP ON *.* TO epsio_user;

Continue to the next step to configure binlog.

2. Configure Replication¶

2.1 Check if replication is configured correctly¶

Run the following command to check if your MySQL is already configured correctly:

Self HostedAWS RDSAWS Aurora

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin';

-- Required configuration: ROW
SHOW VARIABLES LIKE 'binlog_format';

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

If all the above are configured correctly, skip to install Epsio.

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin';

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

-- Required configuration: ROW
SHOW VARIABLES LIKE 'binlog_format';

-- Value should be 1 hour or bigger
CALL mysql.rds_show_configuration();

If all the above are configured correctly, skip to install Epsio.

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin';

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

-- Required configuration: ROW
SHOW VARIABLES LIKE 'binlog_format';

-- Value should be 1 hour or bigger
CALL mysql.rds_show_configuration();

If all the above are configured correctly, skip to install Epsio.

2.2 Enable replication¶

Self HostedAWS RDSAWS Aurora

Enable binlog:

Edit your mysql conf file and add the following configurations:

server-id = 223344
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds  = 864000

For the above changes to take effect, you must restart your mysql server.

Enable log_bin_trust_function_creators: Run the following command;

SET PERSIST log_bin_trust_function_creators = 1;

Validate configuration: Verify that all the new configurations are enabled by running the following commands:

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin';

-- Required configuration: ROW
SHOW VARIABLES LIKE 'binlog_format';

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

Create a custom RDS parameter group. If your instance already uses a custom parameter group, skip to the next stage.

Custom Parameter Group

Edit the custom parameter group.

Set binlog_format parameter to "ROW". Edit logical_replication

Set log_bin_trust_function_creators to 1. Edit logical_replication

Associate the custom parameter group with your RDS instance. Go to the RDS management console, select your instance and click on the "Modify" button.

In the "Modify DB Instance" page, select the custom parameter group you created in the previous step.

Update DB Parameter Group

Make sure you choose "Apply Immediately" to apply the changes immediately.

Apply Parameter Group

Wait for the parameter group configuration to change to "Pending reboot" status.

The parameter group status can be found in the "Configuration" tab of your RDS instance. Reset DB

Then, reboot the database for the changes to take effect.

You'll know that the changes have taken affect when the status of your DB instance Parameter Group changes to "In Sync".

Parameter Group in Sync

After the instance reboots, edit the RDS retention policy:

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

Verify that all the new configurations are enabled by running the following commands:

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin';

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

-- Required configuration: ROW
SHOW VARIABLES LIKE 'binlog_format';

-- Value should be 1 hour or bigger
CALL mysql.rds_show_configuration();

Create a custom Aurora cluster parameter group. If your instance already uses a custom cluster parameter group, skip to the next stage.

Custom Parameter Group

Edit the custom aurora parameter group.

Set binlog_format parameter to "ROW". Edit logical_replication

Set log_bin_trust_function_creators to 1. Edit logical_replication

Associate the custom cluster parameter group with your Aurora cluster. Go to the RDS management console, select your cluster and click on the "Modify" button.

In the "Modify DB Cluster" page, select the custom parameter group you created in the previous step.

Update DB Parameter Group

Make sure you choose "Apply Immediately" to apply the changes immediately.

Apply Parameter Group

Wait for the parameter group configuration to change to "Pending reboot" status in your Aurora instance.

The parameter group status can be found in the "Configuration" tab of your Aurora instance. Reset DB

Then, reboot the Aurora instance for the changes to take effect.

You'll know that the changes have taken affect when the status of your Aurora instance cluster parameter group changes to "In Sync".

Parameter Group in Sync

After the instance reboots, edit the Aurora retention policy:

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

Verify that all the new configurations are enabled by running the following commands:

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin';

-- Required configuration: ON
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

-- Required configuration: ROW
SHOW VARIABLES LIKE 'binlog_format';

-- Value should be 1 hour or bigger
CALL mysql.rds_show_configuration();