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.

# /opt/aspera/common/mysql/bin/mysql -h remote_db_ip_address -P port -umysql_username -pmysql_password < path_to_db_backup

The default MySQL port is 4406. For example,

# /opt/aspera/common/mysql/bin/mysql -h 54.182.111.111 -P 4406 -uroot -ptopsecret < /opt/aspera/faspex/backup/2015-07-01_23458/faspex.sql

Verify that the migration was successful.

# /opt/aspera/common/mysql/bin/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;

Configure Faspex to use the remote database.

Edit /opt/aspera/common/mysql/database.rb.yml
Change hostname to the IP address of the remote database, port to the MySQL port (4406, by default), and password to the remote MySQL database. For example,
```
---
:version: 1.1.22.99169
:process_id:
:data_dir: /opt/aspera/common/mysql/data
:hostname: 54.182.111.111
:port: 4406
:task status: 
   ...
   ...
:password:  topsecret
:setup_complete: true
```
Save your changes.
Edit /opt/aspera/faspex/config/database.yml
Locate the production section and change host to the IP address of the remote database, port to the MySQL port (4406, by default), and password to the remote MySQL database. For example,
```
 ...
production:
   encoding: utf8
   port: 4406
   username: root
   adapter: mysql
   database: faspex
   host: 54.182.111.111
   user: root
   password: topsecret
 ... 
```
Save your changes.

Edit /opt/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"/>
     <!-- connection URL: jdbc:mysql://HOST:PORT/DATABASE -->
     <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"/>
     <!-- connection URL: jdbc:mysql://HOST:PORT/DATABASE -->
     <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
```