How to migrate SOD with Console database to AWS RDS

Overview

A prepackaged Aspera Server On Demand (SOD) with Console instance runs a MySQL database on local host for the Aspera Console application.  If you are using AWS you can configure the Aspera Console on your Server on Demand to use RDS MySQL instance. This article provides instructions on how to migrate Aspera Console database to AWS RDS.

Prerequisites

This article assumes the following:

  • You have purchased and booted up Aspera Server On Demand v.3.6.0 or higher
  • You can SSH as root to your Aspera On Demand Instance
  • You have user permissions to create RDS instances and security groups.
  • You know how to create RDS instances with a custom Parameter Group.  

Procedure

1) Create RDS instance with MySQL 5.5.42 or earlier

Make sure that security group(s) are setup to allow your SOD instance to connect to your RDS instance. Please note the following:

  • Before deploying your RDS instance, please create a Parameter Group with the settings defined on this page. The key requirement is that the Parameter group must have the parameter setting: log_bin_trust_function_creators = 1
  • The default port for MySQL in RDS is set to TCP/3306 (you will need this later).
  • You need to create a security group such that your Server can access the RDS instance
  • Use MySQL 5.5.42 or earlier on your RDS instance.

2) Stop the Console services and back up database

SSH into your SOD instance and perform the following.

# asctl console:stop
# asctl console:backup_database

3) Migrate Console database to RDS

From your SOD instance migrate your Console database to RDS.

# /opt/aspera/common/mysql/bin/mysql -h [host IP] -P [port number] -u [user] -p[password] < /opt/aspera/console/backup/DATE_VERSION/aspera_console.sql

For example

# /opt/aspera/common/mysql/bin/mysql -h 54.182.111.111 -P 3306 -u root -ptopsecret < /opt/aspera/console/backup/2016-05-01_234548/aspera_console.sql

4) Validate that migration is successful

To confirm that the data has been migrated, you should login to the RDS database and view the contents of the new database

# /opt/aspera/common/mysql/bin/mysql -h 54.182.111.111 -P 3306 -u root -ptopsecret
mysql> use aspera_console;
mysql> show tables;
mysql> select * from fasp_nodes;

5) Create a database and user in MySQL for the console reports

Get the password for aspera_reports by viewing the contents of /opt/aspera/console/config/database.yml. You will find the database, username and password which will look like this:

production_reports:
database: "aspera_console_reports"
username: "aspera_reports"
password: "FJovh51izSz"

From the Server On Demand instance, log into RDS. You can achieve this by running this command:

/opt/aspera/common/mysql/bin/mysql -uroot -h172.32.1.30 -pAspera123_ -P 3306 aspera_console
> create database aspera_console_reports;
> create user 'aspera_reports' identified by 'FJovh51izSz';

You can verify that the database was created with this command:

> show databases;

You will see a list of databases, including the aspera_console_reports database. Then you can exit, by typing:

> exit

6) Configure Console to use this RDS database

Edit the following files to reflect the information of your RDS instance

a) /opt/aspera/common/mysql/database.rb.yml

You will need to update the hostname, port, and password in this file.

---
:version: 1.1.19.99169
:process_id:
:data_dir: /opt/aspera/common/mysql/data
:hostname: 54.182.111.111
: username ?? :port: 3306 :task status: ... ... :password: topsecret :setup_complete: true

b) /opt/aspera/console/config/database.yml

In the 'production' section you need to update the port, host and password settings.

...
server_defaults:
...
host: YOUR RDS IP ADDRESS
port: 3306
...
production:
database: "aspera_console"
username: "root"
password: "topsecret"
...
... 

7) Complete the migration and restart console 

Before you can migrate the database, you need to manually patch your Console (this patch will be added to a future version of Console)

In the file:

/opt/aspera/console/app/models/transfer_security.rb 

On lines 493-496:

Change this:

          UNION SELECT
            jts.job_template_id
            , jts.id
            , users.id

To this:

          UNION SELECT
            jts.job_template_id
            , jts.id AS job_template_step_id
            , users.id AS user_id


Once you have updated that file, you can run the migration process:

# asctl console:migrate_database
# asctl console:restart

8) Shut down the local MySQL server, and restart all other Console services.

# asctl mysql:disable
# asctl all:restart
NOTE:  If you need to re-enable the MySQL service, first revert the YML files back and then run the command 'asctl mysql:setup'.

9) Disable the database free space warning in Console

Console is designed to warn you if there is insufficient space on your Console for the database. Now that you have moved Console off this system to RDS, you should disable the warning. Log into your Console, and navigate to Configuration -> Defaults, and find the option "Warn when database free space less than" and set it to zero.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk