Playbook: MySQL Root Password Reset (traccar-db on vps-i1)

When this triggers

rotate-credentials.py MYSQL_PASSWORD rotation fails with:

MySQL ALTER USER failed: ERROR 1045 (28000): Access denied for user 'root'@'localhost'

Access denied for user 'root'@'127.0.0.1'

Root cause: MYSQL_ROOT_PASSWORD in traccar/.env (and the container’s runtime env) does not match the password MySQL data directory was initialized with. This happens when the env file is updated (e.g. during credential rotation) without reinitializing the MySQL data volume.

Confirm it

ssh root@217.154.82.162
PASS=$(docker exec traccar-db printenv MYSQL_ROOT_PASSWORD 2>/dev/null)
echo "PASS_LEN=$(echo -n "$PASS" | wc -c)"
docker exec -e MYSQL_PWD="$PASS" traccar-db mysql -u root -h 127.0.0.1 -e "SELECT 1;" 2>&1
# If "Access denied" → proceed with recovery below

Step-by-step fix

Downtime: ~60 seconds (traccar GPS app stops during recovery; traccar-db is inaccessible for ~30s)

ssh root@217.154.82.162
 
# 1. Stop the traccar app (GPS tracking paused briefly)
docker stop traccar
 
# 2. Stop traccar-db
docker stop traccar-db
 
# 3. Start a recovery container with --skip-grant-tables using the SAME image + volume
docker run -d \
  --name traccar-db-recovery \
  -v traccar_traccar-db:/var/lib/mysql \
  mysql:8.4.9 \
  mysqld --skip-grant-tables --skip-networking --skip-log-bin
 
sleep 12  # wait for mysqld to start
 
# 4. Flush privileges so ALTER USER works, then reset root password for BOTH root entries.
# MySQL 8 creates root@localhost (socket) AND root@% (TCP). Both must be updated.
TARGET_PASS=$(grep '^MYSQL_ROOT_PASSWORD=' /root/traccar/.env | cut -d= -f2-)
docker exec traccar-db-recovery mysql -u root -e "FLUSH PRIVILEGES;"
docker exec -e NEWPASS="$TARGET_PASS" traccar-db-recovery \
  mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '${TARGET_PASS}'; FLUSH PRIVILEGES;"
# Fix root@% — connects via TCP (127.0.0.1); separate entry from root@localhost
docker exec -e NEWPASS="$TARGET_PASS" traccar-db-recovery \
  mysql -u root -e "ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '${TARGET_PASS}'; FLUSH PRIVILEGES;"
unset TARGET_PASS
 
# 5. Stop and remove recovery container
docker stop traccar-db-recovery && docker rm traccar-db-recovery
 
# 6. Start traccar-db normally
docker start traccar-db
sleep 10
 
# 7. Verify root auth works — test BOTH socket and TCP
PASS=$(grep '^MYSQL_ROOT_PASSWORD=' /root/traccar/.env | cut -d= -f2-)
echo "--- Socket auth ---"
docker exec -e MYSQL_PWD="$PASS" traccar-db mysql -u root -e "SELECT user,host FROM mysql.user WHERE user='root';" 2>&1 | grep -v Warning
echo "--- TCP auth (127.0.0.1) ---"
docker exec -e MYSQL_PWD="$PASS" traccar-db mysql -u root -h 127.0.0.1 -e "SELECT user,host FROM mysql.user WHERE user='traccar';" 2>&1 | grep -v Warning
unset PASS
 
# 8. Restart traccar GPS app
docker start traccar
docker ps --filter name=traccar --format "{{.Names}} {{.Status}}"

After recovery

Once root auth works, re-run the credential rotation to also update the traccar user’s password:

gh workflow run credential-rotation.yml --repo radieu/p24-infra --ref dev \
  -f force_all=true -f only_services=MYSQL_PASSWORD

Playbook: Traccar crash-loops after MYSQL_PASSWORD rotation

When this triggers

traccar container restarts continuously after a MYSQL_PASSWORD auto-rotation run. Logs show:

Access denied for user 'traccar'@'172.19.0.x' (using password: YES) - CJException

Root cause: rotate-credentials.py updates /root/traccar/.env and traccar.xml, but if the XML update fails (e.g. XML-special char in password, file permission error), traccar.xml is left with the old password while MySQL already has the new one.

Fixed in rotate-credentials.py (2026-06-27, PR #1628) — this playbook covers the manual recovery if the bug recurs or if traccar.xml is out of sync for any other reason.

Confirm it

ssh root@217.154.82.162
docker logs traccar --tail=20 2>&1 | grep -i "access denied\|SAXParseException"
# "Access denied for user 'traccar'" → password mismatch between traccar.xml and MySQL
# "SAXParseException"               → traccar.xml is malformed (bad XML escape)

Step-by-step fix

ssh root@217.154.82.162
 
# 1. Read the CURRENT MYSQL_PASSWORD from /root/traccar/.env (set by auto-rotation)
#    and update traccar.xml with XML-escaping. Never echo the password.
python3 << 'PYEOF'
import re
from xml.sax.saxutils import escape
 
env = dict(l.strip().split('=',1) for l in open('/root/traccar/.env')
           if '=' in l and not l.startswith('#'))
pw = env.get('MYSQL_PASSWORD', '')
if not pw:
    print("ERROR: MYSQL_PASSWORD not found in /root/traccar/.env"); exit(1)
 
xml = open('/root/traccar/traccar.xml').read()
xml_new = re.sub(r"(<entry key='database\.password'>)[^<]*(</entry>)",
                 lambda m: m.group(1) + escape(pw) + m.group(2), xml)
if xml_new == xml:
    print("WARNING: No match found in traccar.xml — check key name"); exit(2)
open('/root/traccar/traccar.xml', 'w').write(xml_new)
print("OK: traccar.xml updated, pw length=" + str(len(pw)))
PYEOF
 
# 2. Verify traccar.xml is valid XML
python3 -c "import xml.etree.ElementTree as ET; ET.parse('/root/traccar/traccar.xml'); print('XML valid')"
 
# 3. Restart traccar
docker compose -f /root/traccar/docker-compose.yml restart traccar
 
# 4. Wait and verify
sleep 15
docker ps --filter name=traccar --format "{{.Names}} {{.Status}}"
curl -s -o /dev/null -w "Traccar API: %{http_code}\n" http://localhost:8082/api/server

Prevention

rotate-credentials.py (rotate_mysql_password()) now always updates traccar.xml immediately after /root/traccar/.env, using xml.sax.saxutils.escape() to handle special characters. The traccar container is restarted only after both files are updated.

If a future rotation still causes a crash, check:

docker logs traccar --tail=5 — SAXParseException = XML corruption, Access denied = password mismatch
Whether rotate-credentials.py’s ssh_run() call for the XML update returned an error

Where MYSQL_ROOT_PASSWORD is stored

MYSQL_ROOT_PASSWORD is stored in two locations that must stay in sync:

Location	How to access
`secrets/monitoring.env.sops`	`sops -d secrets/monitoring.env.sops \| grep MYSQL_ROOT_PASSWORD` (key name only)
`/root/traccar/.env` on vps-i1	deployed by `secrets-sync.yml` on merge to dev/main

If they diverge, the next MYSQL_PASSWORD auto-rotation will fail with Access denied — use this playbook to recover.

Prevention

The MYSQL_ROOT_PASSWORD in traccar/.env should only be changed by also applying it to MySQL. Never update MYSQL_ROOT_PASSWORD in the env file without running the recovery procedure above (unless the container is being freshly initialized with an empty data volume).

When rotating MYSQL_ROOT_PASSWORD manually:

Update it in secrets/monitoring.env.sops (SOPS write pattern — see docs/secrets-management.md)
Run this recovery procedure to apply the new value to MySQL
Sync to the server: push to dev/main — secrets-sync.yml redeploys /root/traccar/.env

Escalation

If the recovery container also fails to start (InnoDB crash, file corruption), restore from backup:

Wasabi bucket ecotrans-backups, prefix vps-i1/daily/ — daily mysqldump via backup-ionos.sh
Or contact OVH support for disk-level recovery

Playbook: TRACCAR_PASSWORD drift — SOPS stale after XML entity encoding

Read first (#5090): TRACCAR_PASSWORD serves two credentials — the MySQL traccar DB user (this section) and the Traccar app admin login used by POST /api/session. Repairing the drift below writes the MySQL password into TRACCAR_PASSWORD, which makes app login return 401; the reverse repair breaks mysqldump. Both halves and the durable fix (split MYSQL_PASSWORD into its own SOPS key) are in traccar-admin-key-rotation.md §Rotation — TRACCAR_PASSWORD.

When this triggers

backup-ionos.sh Step 1 (mysqldump) fails with Access denied for traccar user, even though the traccar app is running fine (GPS data flowing).

Root cause: TRACCAR_PASSWORD in secrets/monitoring.env.sops drifted from the actual MySQL traccar user password.

Why it happens

traccar.xml stores the database password with XML entity encoding — a password containing & is stored as &. Extracting with grep or sed returns the raw string foo&bar (28 chars) instead of the decoded value foo&bar (24 chars). SOPS then stores the wrong value and MySQL rejects it.

Confirm it

ssh root@217.154.82.162
# Test if SOPS pw works for mysqldump:
SOPS_PW=$(sops -d --input-type dotenv --output-type dotenv \
  /opt/p24-infra/secrets/monitoring.env.sops \
  | grep "^TRACCAR_PASSWORD=" | cut -d= -f2-)
MYSQL_PWD="$SOPS_PW" docker exec -e MYSQL_PWD="$SOPS_PW" traccar-db \
  mysqldump --single-transaction --no-tablespaces \
  -u traccar traccar --where="1 LIMIT 1" >/dev/null 2>&1 \
  && echo "SOPS pw: OK" || echo "SOPS pw: REJECTED"
unset SOPS_PW

Step-by-step fix

SCP fix-sops-final3.py to vps-i1 and run as root. The script:

Parses traccar.xml with xml.etree.ElementTree (correctly decodes & → &)
Verifies decoded password works for mysqldump
Decrypts current SOPS file, replaces TRACCAR_PASSWORD line
Re-encrypts with all 4 age recipients (--config /dev/null --age age1...,age2,...)
Canary decrypt + final mysqldump test

# From Windows dev machine:
scp fix-sops-final3.py root@217.154.82.162:/tmp/
ssh root@217.154.82.162 "python3 /tmp/fix-sops-final3.py"
# Ends with: [OK] SOPS fixed. Run backup-ionos.sh to test full backup.

After the fix on server, copy SOPS file back to git and commit to main:

scp root@217.154.82.162:/opt/p24-infra/secrets/monitoring.env.sops \
  /tmp/monitoring.env.sops.from-server
# Write with LF/no-BOM, canary decrypt, commit fix/* → PR → main

Prevention

Always extract passwords from traccar.xml using Python’s XML parser, not grep:

import xml.etree.ElementTree as ET
tree = ET.parse('/root/traccar/traccar.xml')
for entry in tree.findall('.//entry'):
    if 'database.password' in entry.get('key', ''):
        pw = entry.text  # correctly decoded: & not &amp;

Never use grep for XML values — it returns raw entities, not decoded values.

Audit Log — Log to infra_operations

After this operation completes, log it to the infra_operations audit table.

Python (Linux server — bms-4, vps-i1, vps-h1, or similar):

import sys
sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
 
log_op(
    actor="claude",  # "radieu" for manual human ops, "claude" for agent
    op_type="credential_rotation",
    resource="MYSQL_ROOT_PASSWORD",
    result="success",  # "success" | "failed" | "skipped"
    detail="MySQL root password reset and SOPS updated",
    env="bms-1",
    gh_issue=2730,
)

PowerShell (Windows dev machine):

$env:SUPABASE_URL = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_URL=").ToString().Split("=",2)[1].Trim()
$env:SUPABASE_SERVICE_KEY = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_SERVICE_KEY=").ToString().Split("=",2)[1].Trim()
python -c "
import os, sys
sys.path.insert(0, 'C:/code_2026/p24-infra')
from scripts.lib.log_op import log_op
log_op('claude', 'credential_rotation', 'MYSQL_ROOT_PASSWORD', 'success', 'MySQL root password reset and SOPS updated', 'bms-1')
"
$env:SUPABASE_URL = ''; $env:SUPABASE_SERVICE_KEY = ''

p24-infra Docs

Explorer

mysql-root-password-reset

Playbook: MySQL Root Password Reset (traccar-db on vps-i1)

When this triggers

Confirm it

Step-by-step fix

After recovery

Playbook: Traccar crash-loops after MYSQL_PASSWORD rotation

When this triggers

Confirm it

Step-by-step fix

Prevention

Where MYSQL_ROOT_PASSWORD is stored

Prevention

Escalation

Playbook: TRACCAR_PASSWORD drift — SOPS stale after XML entity encoding

When this triggers

Why it happens

Confirm it

Step-by-step fix

Prevention

Audit Log — Log to infra_operations

Graph View

Table of Contents

Backlinks