dbtool

tools/dbtool.py is the primary database management utility. It handles initial database setup, incremental updates, data migrations, backups, and several maintenance tasks.

Prerequisites

# Python 3 and pip are required
python3 --version

# Install dependencies
pip install -r tools/requirements.txt

Required Python packages: mariadb, gitpython, pyyaml, colorama, zmq. The tool also requires mysql (or mariadb) CLI tools to be available on PATH, or configured via tools/config.yaml.

Command-line reference

update
migrate
backup
setup
dump

Update the database to match the current repository state.

# Express update — only imports SQL files changed since last known version
python tools/dbtool.py update

# Full update — re-imports all SQL files
python tools/dbtool.py update full

What it does:

Optionally creates a backup (controlled by auto_backup in config.yaml)
Imports any missing protected tables (first-time setup only)
Imports all changed .sql files from sql/
Runs any pending data migrations
Updates the version hash stored in config.yaml

Protected tables (player data: characters, accounts, inventory, etc.) are never overwritten by an update. Only missing protected tables are imported on first run.

Check and run any pending Python data migrations without doing a full SQL import.

python tools/dbtool.py migrate

Migrations live in tools/migrations/ as individual Python modules. Each migration checks whether it needs to run and applies schema or data transformations that cannot be expressed as a plain SQL file replacement.

Create a database backup in sql/backups/.

# Full database backup
python tools/dbtool.py backup

# Lite backup — protected (player data) tables only
python tools/dbtool.py backup lite

Backup filenames include the database name, timestamp, and (for full backups) the git version hash: xidb-20241201-143022-a1b2.sql

Create and fully populate a new database. Requires the database name as a confirmation argument:

python tools/dbtool.py setup xidb

Creates the database with utf8mb4 character set and imports all SQL files.

Export live database values back into the SQL source files.

# Dump a single table
python tools/dbtool.py dump mob_spawn_points

# Dump all non-protected tables
python tools/dbtool.py dump

This is used by developers to capture database changes made directly in MariaDB and write them back to the repository SQL files.

Running python tools/dbtool.py without arguments opens an interactive menu:

o--------------------------------------o
|   LandSandBoat Database Management  |
|       Connected to xidb              |
|       #a1b2                          |
o--------------------------------------o
| e. Express Update (#a1b2)            |
| 1. Update DB                         |
| 2. Check migrations                  |
| 3. Backup                            |
| 4. Restore/Import                    |
| r. Reset DB                          |
| t. Maintenance Tasks                 |
| l. Launch Server                     |
| s. Settings                          |
| q. Quit                              |
o--------------------------------------o

Maintenance Tasks submenu

Accessible via t from the main menu:

Key	Action
`1`	Update git submodules
`2`	Set zone IP addresses (public-facing IP)
`3`	Server-wide announcement
`4`	Show table sizes (tables > 1 MB)
`l`	Configure single-process server (all zones on port 54230)
`b`	Configure multi-process server (3 processes)
`c`	Configure multi-process server (7 processes)
`d`	Dump a single table to its SQL file
`a`	Dump all non-protected tables to SQL files

config.yaml

Dbtool stores its own configuration in tools/config.yaml. This file is created automatically on first run.

- mysql_bin: "/usr/bin/"         # Path to MySQL/MariaDB CLI bin directory
- auto_backup: 0                  # 0=off, 1=full auto backup, 2=lite auto backup
- auto_update_client: true        # Automatically update CLIENT_VER in login.lua
- db_ver: "a1b2"                  # Git hash of the last successful update

Key	Type	Description
`mysql_bin`	string	Path to the directory containing `mysql`/`mysqldump` executables
`auto_backup`	int	`0` = no auto backup, `1` = full backup before CLI update, `2` = lite backup
`auto_update_client`	bool	Update `CLIENT_VER` in `settings/login.lua` when running `update`
`db_ver`	string	4-character short git hash of the last applied update, used for express mode

Environment variable overrides

Database credentials from settings/network.lua can be overridden with environment variables:

Variable	Settings equivalent
`XI_NETWORK_SQL_DATABASE`	`SQL_DATABASE`
`XI_NETWORK_SQL_HOST`	`SQL_HOST`
`XI_NETWORK_SQL_PORT`	`SQL_PORT`
`XI_NETWORK_SQL_LOGIN`	`SQL_LOGIN`
`XI_NETWORK_SQL_PASSWORD`	`SQL_PASSWORD`

Running inside Docker

The dev.docker-compose.yml includes a setup_database service that runs dbtool inside the container:

setup_database:
  command: ["python", "/server/tools/dbtool.py", "update"]
  environment:
    MARIADB_DATABASE: xidb
    MARIADB_USER: xiadmin
    MARIADB_PASSWORD: password
    XI_NETWORK_SQL_HOST: database
    XI_NETWORK_SQL_PORT: 3306
  depends_on:
    database:
      condition: service_healthy

docker compose -f dev.docker-compose.yml run --build setup_database

docker compose -f dev.docker-compose.yml run \
  -e XI_NETWORK_SQL_HOST=database \
  -e XI_NETWORK_SQL_PORT=3306 \
  build \
  python /server/tools/dbtool.py update

Launch server from dbtool

The interactive menu’s l option launches all four server processes using zone IP and port settings read from the zone_settings database table:

# What it runs:
xi_connect --log log/connect-server.log
xi_search  --log log/search-server.log
xi_world   --log log/world-server.log
xi_map     --log log/map-server-54230.log --ip <zoneip> --port 54230
# (one xi_map per distinct port in zone_settings)

Before launching a public server, use option 2 in the Maintenance Tasks menu (or dbtool.py directly) to set the correct external IP address in zone_settings.

Contributing

Architecture

Tools

Prerequisites

Command-line reference

Interactive menu

Maintenance Tasks submenu

config.yaml

Environment variable overrides

Running inside Docker

Launch server from dbtool

​Prerequisites

​Command-line reference

​Interactive menu

​Maintenance Tasks submenu

​config.yaml

​Environment variable overrides

​Running inside Docker

​Launch server from dbtool

Prerequisites

Command-line reference

Interactive menu

Maintenance Tasks submenu

config.yaml

Environment variable overrides

Running inside Docker

Launch server from dbtool