Description
This article explains how to migrate an Access Server configuration stored in an external MySQL database to SQLite3 on a new Access Server instance.
If you're looking for instructions on migrating from SQLite3 to MySQL (MariaDB) instead, refer to these guides:
- Admin Web UI method:
- Command-Line Interface method:
It's possible to save the Access Server configuration to an external MySQL database. This helps back up a current configuration in the event of an unexpected issue. You can then use a restore procedure to recover your VPN services.
Steps to Convert from MySQL to SQLite3
For this example, we will transfer an Access Server configuration stored in an external MySQL database hosted in DigitalOcean to a new Access Server recently deployed in DigitalOcean.
Check the configuration on the new Access Server
- Sign in to the Admin Web UI for the recently deployed Access Server hosted on a DigitalOcean droplet.
- Click User Management > User Permissions.
- Note that only the default admin user displays:
Convert the new server from MySQL to SQLite3
- Sign in to your console and get root privileges.
- Stop the Access Server service:
service openvpnas stop
Configure MySQL connection on the new server
- Ensure you have the connection details for your MySQL database with the existing Access Server configuration. Our example uses the following connection details:
-
MySQL User: openvpnas
-
MySQL User Password: pass123
-
MySQL IP or Domain: cluster-db-kevin-do-user-2366515-0.b.db.ondigitalocean.com
-
MySQL Port: 25060
-
- Connect to the new server's console and get root privileges.
- Open (or create) the MySQL credentials file:
nano /etc/.my.cnf
- Add the following, adjusting for your MySQL credentials:
[client]
Note: If your username or password contains strange characters, enclose them in quotes.
user=<MYSQL_USER_NAME>
password=<MYSQL_PASSWORD>
port=25060 - Save and exit (Ctrl+x, then y, and Enter).
- Restrict access to the file:
chmod go-rwx /etc/.my.cnf
- Create 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
Install the MySQL client
-
For Ubuntu/Debian:
apt-get update apt-get install mysql-client libmysqlclient-dev
-
For RHEL:
yum update yum install mysql
Test the MySQL connection
- Run the following command, replacing the values with your MySQL credentials:
mysql -u <MySQL_USER> -p -h <MySQL_IP> -P <MYSQL_PORT>
- Enter the MySQL password when prompted.
- If everything is ok with the connection, you should see something like this:
root@openvpnas:~# mysql -u openvpnas -p -h cluster-db-kevin-do-user-2366515-0.b.db.ondigitalocean.com -P 25060
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2199732
Server version: 8.0.30 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- If everything is ok with the connection, you should see something like this:
Now, you can proceed to transfer the data from the MySQL Server DB to sqlite3
Convert MySQL databases to SQLite3
- Run the following commands to convert the MySQL databases to sqlite3:
cd /usr/local/openvpn_as/scripts/
./dbcvt -t user_prop -s mysql://:@:/as_userprop -d sqlite:////usr/local/openvpn_as/etc/db/userprop.db
./dbcvt -t certs -s mysql://:@:/as_certs -d sqlite:////usr/local/openvpn_as/etc/db/certs.db
./dbcvt -t config -s mysql://:@:/as_config -d sqlite:////usr/local/openvpn_as/etc/db/config.db
./dbcvt -t log -s mysql://:@:/as_log -d sqlite:////usr/local/openvpn_as/etc/db/log.db- Example with actual values:
cd /usr/local/openvpn_as/scripts/
./dbcvt -t user_prop -s mysql://openvpnas:pass123@cluster-db-kevin-do-user-2366515-0.b.db.ondigitalocean.com:25060/as_userprop -d sqlite:////usr/local/openvpn_as/etc/db/userprop.db
./dbcvt -t certs -s mysql://openvpnas:pass123@cluster-db-kevin-do-user-2366515-0.b.db.ondigitalocean.com:25060/as_certs -d sqlite:////usr/local/openvpn_as/etc/db/certs.db
./dbcvt -t config -s mysql://openvpnas:pass123@cluster-db-kevin-do-user-2366515-0.b.db.ondigitalocean.com:25060/as_config -d sqlite:////usr/local/openvpn_as/etc/db/config.db
./dbcvt -t log -s mysql://openvpnas:pass123@cluster-db-kevin-do-user-2366515-0.b.db.ondigitalocean.com:25060/as_log -d sqlite:////usr/local/openvpn_as/etc/db/log.db
- Example with actual values:
Restart the Access Server
- Start VPN services:
service openvpnas start
- Once restarted, Access Server should be fully operational using SQLite3.
- Sign in to the Admin Web UI.
- Click User Management > User Permissions.
- The User Permissions page shows the usernames displayed from the transferred Access Server configuration:
If you have additional questions, please submit a ticket.
Comments
0 comments
Article is closed for comments.