Connecting to MariaDB Databases
MariaDB is a community-developed fork of the MySQL relational database management system and is used at Opalstack to provide MySQL-compatible database services.
This document shows how to connect to your MariaDB databases on Opalstack from clients and applications that use MariaDB and/or MySQL databases.
The parameters used to connect to a MariaDB database on your Opalstack server are:
-
Host:
localhost
for local connections made directly on the server, oropalN.opalstack.com
for remote connections from an external host (replace 'opalN' with your Opalstack server hostname, available on the control panel dashboard). Note that you must enable external access on your MariaDB database before you can make remote connections to your database. -
Port:
3306
-
Socket:
/var/lib/mysql/mysql.sock
- Database name: your MariaDB database name
- Database user name: your MariaDB database user name
- Database user password: your MariaDB database user password
Note that many applications use a default configuration with the host as localhost
and the port and socket as mentioned above so those parameters might be optional or not present in your application settings. Consult your application's documentation for its specific configuration requirements.
The following examples use mydb
as the MariaDB database name, mydbuser
as the database user name, and mydbpassword
as the database user password. Replace them with your own database name and credentials.
Command line client
First log in to a SSH session as a shell user on your server, then...
mysql -p -u mydbuser mydb
...and enter your database user password when prompted.
Python
First install the mysqlclient
library in your environment...
source env/bin/activate
pip install mysqlclient
... then in your Python code:
import MySQLdb as mysql
conn = mysql.connect(host='localhost', user='mydbuser',
password='mydbpassword', database='mydb')
PHP
In your PHP code:
$conn = new mysqli("localhost", "mydbuser", "mydbpassword", "mydb");
Javascript
First install the mysql
package into your Node.js project directory...
npm install mysql
... then in your Javascript code:
var mysql = require('mysql');
var conn = mysql.createConnection({
host: 'localhost',
user: 'mydbuser',
password: 'mydbpassword',
database: 'mydb'
});
conn.connect();
Ruby
First install the mysql2
gem...
gem install mysql2
... then in your Ruby code:
require 'mysql2'
conn = Mysql2::Client.new(:host => "localhost", :username => "mydbuser",
:database => "mydb", :password => "mydbpassword")
Opalstack's control panel gives you the option to enable external access for your MariaDB databases, but for additional security we recommend that you make remote connections through a SSH tunnel.
To use a SSH tunnel for MariaDB access:
- 1
Create a MariaDB and user.
- 2
Create a shell user.
- 3
On the client machine making the remote connection (for example, your own computer) open your terminal application and run the following command to establish the tunnel, replacing
myuser@opalN.opalstack.com
with your shell username and Opalstack server hostname:ssh myuser@opalN.opalstack.com -L 3306:127.0.0.1:3306 -N
Note:
If you have a MySQL server already running on the client machine, then you will need to change the local port used by the tunnel. The following example uses port 3307 as the local port:
ssh myuser@opalN.opalstack.com -L 3307:127.0.0.1:3306 -N
If you're using PuTTY for your SSH connections, use
3306
as the source port and127.0.0.1:3306
as the destination under "Category > Connection > SSH > Tunnels".
- 4
Finally, on the client machine configure your application to connect to your database with the following parameters:
-
Host:
127.0.0.1
-
Port:
3306
(or whatever your local port is) - Database name: your MariaDB database name
- Database user name: your MariaDB database user name
- Database user password: your MariaDB database user password
-
Host:
Your Opalstack service includes a web-based database administration tool called Adminer which is similar to phpMyAdmin.
To access Adminer:
- 1
Go to
https://SERVERNAME.opalstacked.com/adminer/
in your browser, replacingSERVERNAME
with your Opalstack server name (for example "opal1" or "vps2").- 2
Complete the login form as follows:
- Server: MariaDB
- Username: your MariaDB user name
- Password: your MariaDB user password
- Database: your MariaDB database name.
- 3
Click the Login button to log in to your database with the credentials you provided in step 2.