Deutsche Version

Back to overview

Databases

Database management works with MySQL and MariaDB. It needs a client (mysql or mariadb) and a dump tool (mysqldump or mariadb-dump). If one is missing, the db commands say so and the menu entry shows [mysql not installed]. All other functions keep working.

The tool takes the credentials from the options file ST_MYSQL_CONFIG_FILE (default /root/.my.cnf), see Installation. Before every query it tests the connection with SELECT 1.

Names and privilege host

All users and privileges refer to the host localhost. The tool cannot create other hosts.

Create a database and user

server-tools db create --name shop_db --user shop_user
server-tools db create --name shop_db --user shop_user --password 'a-long-password'

Flow:

1. Names are validated. A password you set yourself must have at least ST_PASSWORD_MIN_LENGTH characters, without a password the tool generates one with ST_PASSWORD_LENGTH characters.
2. The database is created with ST_DEFAULT_CHARSET and ST_DEFAULT_COLLATION. If it already exists, the command aborts.
3. The user is created with CREATE USER IF NOT EXISTS. If that fails, the database just created is dropped again.
4. The user receives ALL PRIVILEGES on this database.
5. The credential file is written and the operation is recorded in the audit log.

If the user already exists, its password stays unchanged (IF NOT EXISTS). The credential file still contains the newly passed or generated password. For existing users, create the database with db create-for-user instead.

At the end the password is only shown if stdout is a terminal. Otherwise (hidden - see credentials file) is printed.

Credential file

Path: <ST_CREDENTIAL_DIR>/<database>.txt, mode from ST_CREDENTIAL_FILE_PERMISSIONS (600 or 400). The example shows the layout:

# Database Credentials for: shop_db
# Created: 2026-01-15 10:30:00
# =========================================

Database: shop_db
Username: shop_user
Password: <password>

# Connection Strings
# ------------------
# PDO DSN:
#   mysql:host=localhost;dbname=shop_db;charset=utf8mb4
#
# Symfony DATABASE_URL:
#   mysql://shop_user:<password>@localhost:3306/shop_db?charset=utf8mb4

Host localhost, port 3306 and utf8mb4 are hard-coded in the file, regardless of the configuration. The password appears unchanged in the DATABASE_URL. If a password you set yourself contains special characters such as @ or /, you have to URL-encode it there yourself.

Database for an existing user

server-tools db create-for-user --name shop_staging --user shop_user

Only creates the database and gives the existing user ALL PRIVILEGES. If the user does not exist, the command aborts. The credential file contains the text (existing user - no password stored) as the password.

Grant privileges

server-tools db grant --name shop_db --user report_user --level readonly

Database and user must exist. Granted privileges add up: if you grant all first and readonly afterwards, the user keeps ALL PRIVILEGES. Privileges can only be revoked through db delete (see below) or in the menu through "Reassign database to different user".

server-tools db grants --user shop_user

shows the GRANT statements of the user.

The menu additionally offers "Reassign database to different user": it revokes the privileges from the old user and gives them to the new one. If granting fails, the old user gets its privileges back.

Delete

# Delete the database, revoke privileges from shop_user, keep the user
server-tools db delete --name shop_db --user shop_user

# Delete the database and the user
server-tools db delete --name shop_db --user shop_user --drop-user

Flow:

1. The database must exist.
2. If ST_AUTO_BACKUP=true and the credential file exists, it is saved as db_<name>_<timestamp>.tar.gz in ST_BACKUP_DIR. This happens before the confirmation prompt.
3. Prompt Delete database '<name>'? (y/N):.
4. Without --drop-user: if the user given with --user exists, its privileges on the database are revoked first and DROP DATABASE follows. With --drop-user: DROP DATABASE comes first, then the user is dropped.
5. Finally the credential file is removed.

Only the credential file is backed up, not the content of the database. Make a dump with db backup first.

--drop-user without --user only deletes the database. If you drop a user, it also loses its privileges on all other databases.

Dumps and backups

server-tools db backup --name shop_db
server-tools db backup-all

Result: <ST_DB_BACKUP_DIR>/db_<name>_<YYYYMMDD_HHMMSS>.sql.gz with mode 600. The directory is created with mode 700 when needed. The dump runs with --single-transaction --routines --triggers.

backup-all backs up all databases except information_schema, performance_schema, mysql and sys. Databases with an invalid name are skipped and counted as failures.

Called as a CLI command, db backup-all currently backs up only the first database and then exits with code 1. In the menu ("Backup all databases") it runs through completely. For scripts, back up the databases one by one with db backup.

Restore and import

server-tools db restore --name shop_db --file /root/db-backups/db_shop_db_20260115_103000.sql.gz
server-tools db import  --name shop_db --file dump.sql

Both simply load the file into the existing database. The tool does not empty the database beforehand. Whether existing tables are replaced depends on the content of the dump. Dumps created with mysqldump contain DROP TABLE statements before CREATE TABLE by default.

Export

server-tools db export --name shop_db
server-tools db export --name shop_db --output /root/shop.sql.gz

Allowed extensions are .sql and .sql.gz. Without --output the file <ST_DB_BACKUP_DIR>/<name>_export_<timestamp>.sql.gz is created. The target directory must exist, db export does not create it. The file has mode 600.

List and info

server-tools db list
server-tools db info --name shop_db

db list shows all databases except the system databases and all users except root, mysql.sys, mysql.session, mysql.infoschema and debian-sys-maint as user@host.

db info shows the size in MB, the number of tables, the users with privileges on this database and whether a credential file exists.

Next

• Security and safeguards
• Troubleshooting