Skip to main content

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

Abstract

How to change Access Server's database backend to a MySQL-type or Amazon RDS database server from the command line.

Overview

Access Server can store configuration in MySQL-type database systems such as Amazon RDS, MySQL, and MariaDB.

By default, Access Server creates configuration files in a SQLite3 database on the server. This tutorial shows you how to convert to a separate database server.

If you set up a cluster of Access Servers, all of your Access Servers will connect to one database system to share configuration. You may even set this up with a cluster of database servers to create a truly fault-tolerant system. Refer to Tutorial: How to Set Up an Access Server Cluster for details.

The steps in this tutorial provide manual instructions for converting database files. You may want to store some or all configuration files in a MySQL-type database. Some use case examples for setting this up include:

  • Storing the logging database in a MySQL database but keeping the other configuration, certificates, and user properties in local SQLite3 files.

  • Storing configuration files in a cluster database setup to protect against data loss.

  • Any combination of storing locally or remotely.

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.

Access Server database compatibility

We've tested and confirmed that the latest Access Server version works on these operating systems with the following relational database management system (RDBMS) versions:

OS vs RDBMS

MySQL 5.7.36

MySQL 8.0.27

MariaDB 10.11.5

MariaDB 11.0.3

Ubuntu 20.04 (x86_64)

Ubuntu 20.04 (ARM64)

Ubuntu 22.04 (x86_64)

Ubuntu 22.04 (ARM64)

Ubuntu 24.04 (X86_64)

Ubuntu 24.04 (ARM64)

Debian 11

Debian 12

Red Hat 8

* only with an updated connector

Red Hat 9

Important

For MariaDB, we recommend using MariaDB 10.5.8 or newer. A known issue exists in MariaDB 10.4.3 due to a bug in the MariaDB code that causes connectivity issues. If you plan to use a version of the MariaDB server that is newer than available in repositories on the host where Access Server is installed, you may need to update the MariaDB connector. For more details, refer to the official MariaDB connector documentation.

  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:

    service openvpnas stop
  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_dbuser_prop_dbcerts_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:

    service openvpnas start
    • 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.