Configuring Faspex to Use a Remote Database (MySQL Only)

Faspex can be configured to use a remote database that only runs MySQL. To configure Faspex to use a remote database on a node that also runs Faspex background processes (so that the other Faspex node runs only the Faspex application), see Configuring Faspex to Use a Remote Database (Faspex + MySQL).

Set up the remote database.

Note: For this operation, Faspex requires MySQL version 5.7 or later. Earlier versions are not supported.
On the Faspex server, stop Faspex services and back up the local database.
```
 asctl faspex:stop
 asctl faspex:backup_database
```
Record the location of the database backup, which you use in the next step to migrate the database.

Grant remote access privileges to Faspex.

mysql > CREATE USER 'root'@'faspex_ip_address' IDENTIFIED BY 'mysql_password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'faspex_ip_address' WITH GRANT OPTION; FLUSH PRIVILEGES;

For example:

mysql > CREATE USER 'root'@'10.0.174.47' IDENTIFIED BY '*****'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.174.47' WITH GRANT OPTION; FLUSH PRIVILEGES;

Migrate the local database to the remote database.

 mysql -h remote_db_ip_address -P port -umysql_username -pmysql_password < path_to_db_backup

The default MySQL port is 4406. For example,

 mysql -h 54.182.111.111 -P 4406 -uroot -pXRs9sJFF5ja1BGlKHYLwzQ== < C:\Program Files [(x86)]\Aspera\Faspex\backup\2015-07-01_23458/faspex.sql

Verify that the migration was successful.

 mysql -h remote_db_ip_address -P port -umysql_username -pmysql_password

View the contents of the new database by running the following commands:

mysql use faspex;
mysql show tables;
mysql select * from e_packages;

On the Faspex server, configure Faspex to use the remote database.
1. Back up the C:\Program Files [(x86)]\Aspera\Common\mysql\database.rb.yml files.
2. Edit C:\Program Files [(x86)]\Aspera\Common\mysql\database.rb.yml.
  Change:
  - host to the IP address of the remote database.
  - port to the MySQL port (4406, by default).
  - password to the remote MySQL database password.
  - user to the remote MySQL database user.
    Note: By default, there is no user field. Faspex defaults to the root user. Add a new line to configure a different, non-root user. For example, :user: remote_faspex_user.
  For example:
```
---
...
:hostname: 54.182.111.111
:port: 4406
:task status: 
   ...
   ...
:user: remote_faspex_user
:password: XRs9sJFF5ja1BGlKHYLwzQ==
:setup_complete: true
```
  Save your changes.
3. Edit C:\Program Files [(x86)]\Aspera\Faspex\config\database.yml.
  Locate the production and change:
  - host to the IP address of the remote database.
  - port to the MySQL port (4406, by default).
  - username to the remote MySQL database user.
    Note: By default, Faspex also includes the user field, but uses the username field for the MySQL database user.
  - password to the remote MySQL database password.
  For example:
```
...
production:
  encoding: utf8
  port: 4406
  username: faspex
  adapter: mysql
  database: faspex
  host: 54.182.111.111
  user: root
  password: XRs9sJFF5ja1BGlKHYLwzQ==
```
  Save your changes.
1. Edit C:\Program Files [(x86)]\Aspera\Faspex\lib\daemons\np\etc\persistence.xml.
  Locate the <properties> section and change hibernate.connection.url to the IP address of the remote database, port to the MySQL port (4406, by default), and provide the username and password to the remote MySQL database.
```
...
   <properties>
     <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
     
     <property name="hibernate.connection.url" value="jdbc:mysql://ip_address:port/faspex"/>
     <property name="hibernate.connection.username" value="username"/>
     <property name="hibernate.connection.password" value="password"/>
     ...
   </properties>
...
```
  For example:
```
...
   <properties>
     <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
     
     <property name="hibernate.connection.url" value="jdbc:mysql://54.182.111.111:4406/faspex"/>
     <property name="hibernate.connection.username" value="root"/>
     <property name="hibernate.connection.password" value="aspera"/>
     ...
   </properties>
...
```
  Save your changes.
Shut down the local MySQL database and restart all other Faspex services.
```
 asctl mysql:disable
 asctl all:restart
```
If you need to restart the local MySQL database, revert the .yml files and then run the following command:
```
 asctl mysql:setup
```