Deployment guide

This guide will walk you through the process of deploying Epsio for Epsio in your AWS environment.

Before you begin

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

• The VPC and subnet in your AWS account where you want to deploy Epsio in.
  Make sure that the VPC has the required network access policy.
• A MySQL Database with version 5.7+.
• An AWS user with the following permissions:
  • Create, launch, stop, and rollback CloudFormation stacks.
  • Create and modify IAM users, roles, and policies.

---

1. Prepare your database for Epsio

Open a connection to your database and follow the steps below. In step 3, you will supply Epsio with the password you created for the Epsio user.

Create a database for Epsio's metadata:

CREATE DATABASE epsio;

Create a database user for Epsio's exclusive use:

CREATE USER epsio_user IDENTIFIED BY '<password>';

Grant new user permissions to the Epsio database

GRANT ALL PRIVILEGES ON epsio.* TO epsio_user;

Grant replication permissions to the Epsio user

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO epsio_user;

Grant permission to create result tables and populate existing data

GRANT SELECT, CREATE, SHOW VIEW, CREATE VIEW, ALTER, INSERT, LOCK TABLES, DELETE, DROP ON *.* TO epsio_user;

If you wish to limit access to only specific tables / databases, replace the *.* pattern with the relevant database names and table names you intend on using.

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 instance is already configured correctly:

AWS RDSAWS AuroraSelf Hosted

-- 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 finish the deployment.

-- 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 finish the deployment.

-- 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 finish the deployment.

2.2 Enable replication

AWS RDSAWS AuroraSelf Hosted

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

Edit the custom parameter group.

Set binlog_format parameter to "ROW".

Set log_bin_trust_function_creators to 1.

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.

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

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.

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".

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.

Edit the custom aurora parameter group.

Set binlog_format parameter to "ROW".

Set log_bin_trust_function_creators to 1.

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.

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

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.

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".

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();

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';

---

3. Launch Epsio in your cloud environment

Epsio uses a Cloudformation stack to create and manage your Epsio deployment.

First, click "Create Deployment" in the dashboard, and select MySQL as your database.

Next, choose "AWS CloudFormation" in the sidebar, and in the "Install Epsio CloudFormation" step, click "Create Deployment".

This will launch a CloudFormation template used to launch your Epsio instance. In the CloudFormation template, supply the database information as well as the Epsio instance size:

---

Once Epsio successfully installs, you'll be redirected to the Epsio dashboard.

Any errors/warnings in connecting to your database can be viewed in the Epsio dashboard.

You are set to go and can create your first view. Visit the create_view for further details.