---
id: runbook-mysql
title: MySQL runbook
---

[MySQL (MariaDB)](https://mariadb.org/) is an optional third-party database that can be used for [Access Control List (ACL)](../permissions/permissions-overview.md) storage in Deephaven. This is a legacy configuration; modern deployments use etcd for ACL storage. MySQL is only required if your installation has not yet migrated ACLs to etcd.

## Impact of MySQL failure

| Level            | Impact                                                                                                                                                                                                                                          |
| :--------------- | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Sev 1 - Critical | The [Authentication Server](./runbook-authentication-server.md), [ACL Write Server](./runbook-acl-write-server.md), and Deephaven Clients will be impacted. Query workers will also be affected and unable to check effective user permissions. |

> [!NOTE]
> This severity only applies if using MySQL for ACL storage. If ACLs are stored in etcd, MySQL failure has no impact.

## MySQL dependencies

MySQL has no dependencies on Deephaven services. It is a standalone database that must be running before Deephaven processes that use it can start.

**Dependent Deephaven services:**

- Authentication Server — reads ACL data.
- ACL Write Server — writes ACL data.
- Worker processes — read permissions during authorization.

## Checking MySQL status

Check MySQL service is running:

```bash
sudo systemctl status mariadb
```

Expected output should show `active (running)`.

Test MySQL connectivity:

```bash
sudo mysql -e "SELECT VERSION()"
```

## Viewing MySQL logs

View MariaDB log:

```bash
sudo cat /var/log/mariadb/mariadb.log
```

Tail the log to follow in real-time:

```bash
sudo tail -f /var/log/mariadb/mariadb.log
```

View error log:

```bash
sudo cat /var/log/mariadb/mariadb.log | grep ERROR
```

## Restart procedure

Restart MySQL:

```bash
sudo systemctl restart mariadb
```

> [!WARNING]
> Restarting MySQL temporarily interrupts ACL access for all Deephaven services. This prevents new user logins and permission checks until MySQL is back online.

Verify the restart was successful:

```bash
sudo systemctl status mariadb
```

Check database accessibility:

```bash
sudo mysql -e "USE dbacl_iris; SHOW TABLES;"
```

## Deephaven MySQL connection configuration

Deephaven processes connect to the `dbacl_iris` database using the `MysqlDbAclProvider` properties. See [ACL storage](../permissions/acl-storage.md#sql-storage) for full configuration details.

```properties
MysqlDbAclProvider.host=localhost
MysqlDbAclProvider.db=dbacl_iris
MysqlDbAclProvider.ssl=false
MysqlDbAclProvider.user=irisro
MysqlDbAclProvider.passwordFile=/etc/sysconfig/deephaven/auth/db_acl_ro_passphrase.txt
MysqlDbAclProvider.readWriteUser=irisrw
MysqlDbAclProvider.readWritePasswordFile=/etc/sysconfig/deephaven/auth/db_acl_write_server_passphrase.txt
```

## Backup and restore

### Backup MySQL ACLs

```bash
mysqldump --user [user] --password=[password] --databases dbacl_iris > /backup/dbacl_iris-$(date +%Y%m%d).sql
```

See [etcd and MySQL backup](../backup-restore-migrate/etcd-and-acls-backup.md#mysql-acl-backup-and-restore) for the full backup and restore procedure, including the preferred Deephaven backup script approach.

### Restore MySQL ACLs

> [!WARNING]
> Restoring overwrites all current ACL data. Ensure this is intentional.

```bash
mysql --user [user] --password=[password] -e "drop database if exists dbacl_iris"
mysql --user [user] --password=[password] -e "create database dbacl_iris"
mysql --user [user] --password=[password] dbacl_iris < /backup/dbacl_iris-[date].sql
```

## MySQL user management

### Verify Deephaven MySQL users

```bash
sudo mysql -e "SELECT User, Host FROM mysql.user WHERE User IN ('irisro', 'irisrw')"
```

### Reset Deephaven user password

```bash
sudo mysql -e "ALTER USER 'irisro'@'localhost' IDENTIFIED BY 'new_password'"
sudo mysql -e "ALTER USER 'irisrw'@'localhost' IDENTIFIED BY 'new_password'"
sudo mysql -e "FLUSH PRIVILEGES"
```

Update the corresponding password files referenced by `MysqlDbAclProvider.passwordFile` and `MysqlDbAclProvider.readWritePasswordFile`, then restart the Authentication Server and ACL Write Server.

### Grant permissions to Deephaven users

```bash
sudo mysql -e "GRANT SELECT ON dbacl_iris.* TO 'irisro'@'localhost'"
sudo mysql -e "GRANT ALL PRIVILEGES ON dbacl_iris.* TO 'irisrw'@'localhost'"
sudo mysql -e "FLUSH PRIVILEGES"
```

## Migrating from MySQL to etcd

Modern Deephaven deployments use etcd for ACL storage. To migrate:

See [Migrating ACLs to etcd](../installation/basic-install.md#appendix-g-migrating-acls-to-etcd) for detailed procedure. The [ACL storage](../permissions/acl-storage.md#migrating-from-sql-to-etcd) page provides the step-by-step `dhconfig acls` commands for export and import.

**Migration overview:**

1. Export ACLs from MySQL to XML.
2. Configure Deephaven to use etcd for ACLs.
3. Import ACLs to etcd.
4. Restart Deephaven services.
5. Verify ACLs work correctly.
6. Decommission MySQL (optional).

**Benefits of etcd-based ACLs:**

- Unified configuration storage.
- Better consistency with cluster state.
- No separate database to manage.
- Simpler backup/restore.
- Better integration with Deephaven tooling.

## Configuration files and locations

**System service:** Managed by `systemd`

**Service control:** `systemctl {start|stop|restart|status} mariadb`

**Configuration file:** `/etc/my.cnf`

**Data directory:** `/var/lib/mysql/`

**Log files:**

- `/var/log/mariadb/mariadb.log`
- Error log location configured in `/etc/my.cnf`

**Socket file:** `/var/lib/mysql/mysql.sock`

**ACL database:** `dbacl_iris`

## Related documentation

- [ACL storage](../permissions/acl-storage.md)
- [etcd and MySQL backup](../backup-restore-migrate/etcd-and-acls-backup.md)
- [Migrating ACLs to etcd](../installation/basic-install.md#appendix-g-migrating-acls-to-etcd)
- [dhconfig acls command reference](../configuration/dhconfig/acls.md)
- [System processes overview](../architecture/architecture-overview.md)
- [Authentication Server runbook](runbook-authentication-server.md)
- [ACL Write Server runbook](runbook-acl-write-server.md)
- [etcd runbook](runbook-etcd.md)
- [Official MariaDB documentation](https://mariadb.org/)