Databases

geplantGeplant für WardPane 2.1. Diese Funktion ist nicht in der aktuellen Version enthalten; die Seite beschreibt, wie sie funktionieren soll. Roadmap

WordPress, Laravel and most web applications keep their data in MySQL or MariaDB. WardPane 2.1 will run the database server on the same machine and let you manage databases from the panel, much like ISPmanager.

Installing the database server

The database server is not installed by default. install.sh installs MySQL or MariaDB on request, either during installation or later when you run it again. Before that, the Databases section is empty.

Sites and apps on the server connect to the database locally, so leave port 3306 closed in the firewall.

Managing databases

In the panel, open Databases. There you can:

• create a database and choose its owner, the user it belongs to (users who are not admins see and manage only their own databases, see Users);
• see the size of each database;
• create database users with a password and give them privileges on one or more databases;
• delete databases and database users.

Deleting a database deletes its data. If you may need the data later, make a dump first from the shell:

mysqldump -u myshop -p myshop > myshop.sql

One database per site

Give each site or app its own database and its own database user, with privileges on that database only. If one site is hacked, the attacker cannot read the data of the others. Make the passwords long and random: the app keeps the password in its config file, so nobody has to remember it.

Connecting from PHP

A PHP site connects with the database name, user and password from the panel; the server is on the same machine.

In PHP, localhost connects through the Unix socket and 127.0.0.1 over TCP. If a PHP site fails to connect to localhost, use 127.0.0.1.

WordPress (wp-config.php):

define('DB_NAME', 'myshop');
define('DB_USER', 'myshop');
define('DB_PASSWORD', 'the password from the panel');
define('DB_HOST', '127.0.0.1');

Laravel (.env):

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=myshop
DB_USERNAME=myshop
DB_PASSWORD=the-password-from-the-panel

The mysqli and pdo_mysql extensions are part of the PHP that install.sh --php installs. Block outgoing connections in the PHP settings does not affect the database, because connections to the server itself stay allowed.

Connecting from Node.js

Keep the connection settings out of the code and put them into the site’s environment variables on the Handler tab, for example DB_HOST=127.0.0.1, DB_USER, DB_PASSWORD and DB_NAME. With the mysql2 package the connection then looks like this:

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST || '127.0.0.1',
  port: 3306,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

The panel stores environment variables as plain text, and anyone who can open the site’s settings can see them. See Node.js.

SQLite

For a small site SQLite is often enough, and then no database server is needed. PHP has the sqlite3 extension. Keep the database file in a folder the app can write to and that is never served. On sites from before 2.0 that is data/ next to public/. On new sites the whole site folder is the document root, so list a writable folder in the PHP settings and close it to visitors with a rule in the .htaccess of the site root, for example RedirectMatch 404 ^/db/ for a folder db (see .htaccess). Put the rule above the folder, because .htaccess files inside writable folders are ignored.