Tutorial: Manually Change the Database Backend to MySQL or Amazon RDS from the Command Line⁠

In this tutorial, we've set up an RDS cluster on Amazon AWS, and our connection address is auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com:3306. For the following command-line tools. Our example uses the default port 3306. If another port is used, ensure you configure it in the .my.cnf file below.

• An installed Access Server.

• Console access and the ability to get root access.

• A supported database server.

1. Sign in to your console and get root privileges.

2. Edit the .my.cnf file:

   nano /etc/.my.cnf

3. Add this text, and adjust the username and password to the ones you've configured:

   [client]
   user=<MYSQL_USER_NAME>
   password=<MYSQL_PASSWORD>
   port=3306

   • If your username or password contains strange characters, add quotes around them.

4. Press ctrl+x, y, then enter to save and exit.

5. Save file permissions so only root can access it:

   chmod go-rwx /etc/.my.cnf

6. Add a symbolic link so the root user can use the mysql command line tool without entering credentials:

   ln -s /etc/.my.cnf /root/.my.cnf

1. Connect to the RDS instance with the mysql command line tool:

   mysql -h auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com

2. Use the mysql command-line prompt to create the databases:

   mysql> create database as_certs;
   Query OK, 1 row affected (0.01 sec)
   mysql> create database as_config;
   Query OK, 1 row affected (0.01 sec)
   mysql> create database as_log;
   Query OK, 1 row affected (0.01 sec)
   mysql> create database as_userprop;
   Query OK, 1 row affected (0.01 sec)

3. Ensure the web certificates are stored in the certificates database:

   cd /usr/local/openvpn_as/scripts/
   ./sacli --import GetActiveWebCerts

4. Stop the Access Server service before converting the databases to the database backend:

   systemctl stop openvpnas

1. Convert the databases you want to convert using the commands below (you don't have to convert them all):

   export SERVER=auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com
   ./dbcvt -t config -s sqlite:////usr/local/openvpn_as/etc/db/config.db -d mysql://$SERVER/as_config
   ./dbcvt -t certs -s sqlite:////usr/local/openvpn_as/etc/db/certs.db -d mysql://$SERVER/as_certs
   ./dbcvt -t user_prop -s sqlite:////usr/local/openvpn_as/etc/db/userprop.db -d mysql://$SERVER/as_userprop
   ./dbcvt -t log -s sqlite:////usr/local/openvpn_as/etc/db/log.db -d mysql://$SERVER/as_log
   unset SERVER

2. You may need to install the libmysqlclient-dev package on your system if you encounter an error message similar to this:

   error opening DB mysql://server/database: libmysqlclient.so.20: cannot open shared object file: No such file or directory

3. Modify the as.conf file to tell it where to look for each database (you don't have to move them all to the database backend):

   nano /usr/local/openvpn_as/etc/as.conf

4. Look for the lines starting with config_db, user_prop_db, certs_db, and log_db. Adjust them accordingly:

   config_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_config
   user_prop_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_userprop
   log_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_log
   certs_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_certs

5. Press ctrl+x, y, then enter to save and exit.

6. Finally, restart the Access Server service:

   systemctl start openvpnas

   • Access Server should come back online and function with the configured database backend options. You can test by moving the SQLite3 database files your Access Server no longer uses out of the /etc/db folder to another location and restarting the Access Server service. It should come back up without issue. If you are having problems getting Access Server to start, you can change your settings back or take a close look at the /var/log/openvpnas.log file to determine what is going on exactly. Usually, any error messages are visible there.