OpenSimulator · EN

MySQL and MariaDB: operating an OpenSimulator database

Connections, InnoDB, backup, performance and SQL incident diagnosis without confusing services.

These procedures cover common OpenSimulator administration tasks. Verify settings for your version and prepare a backup before making changes.

Map databases before changing anything

ROBUST commonly hosts central services; simulators may use separate region databases. Additional modules can also have their own databases. Inventory ConnectionString in Robust.ini, GridCommon.ini and add-on configurations, while redacting secrets. Do not assume everything lives in a database named opensim.

SQL accounts and permissions

Use application accounts scoped to their databases and restrict allowed hosts. Apps on separate VMs require private network access to the database; never expose port 3306 to the public Internet. Never use sample INI credentials.

Example: adapt and verify on a test instance.

-- Dans le client SQL, remplacez base, hôte et secret :
CREATE DATABASE opensim CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'opensim'@'10.0.0.%' IDENTIFIED BY 'SECRET_FORT_A_REMPLACER';
GRANT ALL PRIVILEGES ON opensim.* TO 'opensim'@'10.0.0.%';
-- Vérifiez la compatibilité de la version OpenSimulator avant de choisir charset/collation.

InnoDB and resource monitoring

InnoDB cache, connections and logs can strongly affect a grid. Size MySQL memory relative to actual RAM and colocated processes; there is no universal buffer-pool size. Measure before and after, changing one variable at a time.

Example: adapt and verify on a test instance.

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW FULL PROCESSLIST;

SQL latency and queries

Find slow responses and lock contention, then inspect plans for actual problematic queries. Controlled slow-query logging can help. Do not add arbitrary indexes to OpenSimulator tables; inspect schema migrations and real workloads.

Example: adapt and verify on a test instance.

SHOW ENGINE INNODB STATUS;
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- Sur une requête confirmée, analyser :
EXPLAIN SELECT * FROM fsassets WHERE id = 'UUID_REEL';

MySQL disconnects after restart

A lost connection may surface in Robust.log or OpenSim.log, but recovery depends on the client and module. Compare SQL/ROBUST/region timelines and confirm which service recovered. Restarting regions may hide a failure to reconnect rather than fix it.

Example: adapt and verify on a test instance.

journalctl -u mysql --since '2 hours ago' --no-pager
journalctl -u mariadb --since '2 hours ago' --no-pager
# Adapter le journal OpenSimulator :
grep -nEi 'mysql|database|connection|timeout|exception' /chemin/vers/Robust.log | tail -n 120

MySQL versus MariaDB compatibility

The fork template refers to mysql_native_password for older MySQL clients. Do not blindly apply historical server-wide authentication advice to modern versions: available authentication differs by server and connector. Confirm the exact combination first.

Consistent database backup

A SQL backup alone does not protect FSAssets payloads or region OAR exports. For InnoDB data, mysqldump --single-transaction can provide a transactional view under appropriate conditions; on a running grid, filesystem payloads and extra services still need coordinated capture. Test an isolated restore.

Example: adapt and verify on a test instance.

# À lancer avec un accès SQL autorisé ; le dossier destination doit exister :
mysqldump --single-transaction --routines --triggers --events --databases opensim > /backup/opensim.sql
# Vérifier code retour, taille, intégrité et restauration d'essai.

SQL logs: first evidence

For database errors, correlate SQL log timestamps with ROBUST and simulator logs. Look for access denied, connection exhaustion, timeout, crashes, full disks and recovery attempts. systemd service names vary between mysql and mariadb.

Example: adapt and verify on a test instance.

sudo systemctl status mysql --no-pager
sudo journalctl -u mysql --since '1 hour ago' --no-pager
# Si le service s'appelle mariadb, adapter les commandes.

Keep FSAssets metadata distinct

The fsassets table contains identifiers, hashes and metadata, not the binary payload stored on disk. Its row count need not match file count due to deduplication. File relocation and repair procedures belong to the dedicated FSAssets chapter.

Further reading

Source code and references