Database User Privileges Reset After Server Reboot

1. Missing FLUSH PRIVILEGES After Manual Grant

This is the most common cause, and it's subtle. You run a GRANT statement in MySQL or MariaDB, test it, it works. Then you reboot the server. Boom — privileges gone. What's actually happening here is that GRANT doesn't write the changes to disk unless you explicitly tell it to. The MySQL manual is clear: changes take effect immediately in memory, but they're stored in the mysql.user table. If you modify that table directly (INSERT, UPDATE, DELETE) instead of using GRANT, you must run FLUSH PRIVILEGES to sync memory to disk. But here's the kicker: even with GRANT, on some older MySQL versions (5.6 and earlier), or after a crash, the in-memory cache can be stale on reboot.

The real fix: Always run FLUSH PRIVILEGES after any privilege change, even if you used GRANT. It's a no-op in terms of performance, and it forces a complete reload from the privilege tables. Do this before rebooting:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

But wait — there's a scenario where this doesn't help. If the mysql.user table itself is corrupted or missing entries, FLUSH PRIVILEGES won't recreate them. Check with:

mysql> SELECT User, Host FROM mysql.user WHERE User = 'your_user';

If the row is missing after reboot, you've got a deeper problem — likely the table wasn't written to disk at all. This happens when you're using skip-grant-tables mode (bad idea) or when the server crashes before flushing. In that case, don't just re-grant. Dump the grants first:

mysql> SHOW GRANTS FOR 'your_user'@'localhost';

Then save that output. After reboot, re-run the grants and FLUSH PRIVILEGES. That ensures the data is written.

2. Init Script Rebuilding Grant Tables on Boot

Second common cause: your database server's init script or service file is configured to rebuild the privilege tables from scratch on every boot. This is rare in standard distributions, but I've seen it in custom Docker containers, badly configured MariaDB clusters, and some automated deployment tools (like Ansible roles that re-apply schemas).

What's actually happening here is that the startup script includes a command like mysql_install_db or mysql_system_tables_data.sql on every boot. That wipes and re-creates the mysql database, which includes the user privileges. You lose everything.

How to check: Look at your MySQL service file (usually /etc/systemd/system/mysql.service or /etc/init.d/mysql). Search for mysql_install_db, --initialize, or --bootstrap. If you see those in the ExecStart or ExecStartPre lines, that's your culprit.

The real fix: Remove those lines. The database should only be initialized once, typically at installation time. For systemd, the service should look like:

[Service]
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
ExecStartPre=

No mysql_install_db. After fixing the service file, reload systemd:

sudo systemctl daemon-reload
sudo systemctl restart mysql

Then re-grant your privileges. But here's the opinionated part: if you're using containers, don't put privilege creation in the startup script. Use a separate initialization script that runs once, like Docker's docker-entrypoint-initdb.d — that only runs on first boot.

For PostgreSQL, the equivalent is the pg_hba.conf being overwritten on restart. Some init scripts copy a default pg_hba.conf over your custom one. Check for cp /usr/share/postgresql/.../pg_hba.conf.sample in the init script. If you find it, remove that line. Your custom pg_hba.conf must persist across reboots.

3. Crash Recovery Corruption of Grant Tables

Third cause: less common, but nasty. Your database server crashes unexpectedly (power loss, kernel panic, OOM killer). On restart, it tries to recover the grant tables from the binary log or InnoDB redo log. If the recovery process encounters corruption — maybe a partial write to mysql.user during the crash — it might skip or truncate rows, effectively resetting your privileges.

What's actually happening here is that MySQL's crash recovery (or PostgreSQL's WAL replay) treats grant tables as normal tables. InnoDB's crash recovery is ACID, so it should be safe. But MySQL's mysql system tables were historically MyISAM (pre-8.0), which is not crash-safe. MyISAM tables can become corrupted on crashes, and recovery is often incomplete. Even in MySQL 8.0, where mysql.user is InnoDB, a crash during a FLUSH PRIVILEGES or ALTER USER can leave the table in an inconsistent state.

How to detect: Check the MySQL error log (/var/log/mysql/error.log) after reboot. Look for lines like:

[ERROR] /usr/sbin/mysqld: Table 'mysql.user' is marked as crashed
[Warning] Checking table: mysql.user

Or in PostgreSQL:

LOG:  database system was not properly shut down; automatic recovery in progress
WARNING:  relation "pg_authid" contains corrupted data

The real fix: Repair the grant table. For MySQL, stop the server, start it in safe mode:

sudo mysqld_safe --skip-grant-tables &

Then repair the table:

mysql> REPAIR TABLE mysql.user;
mysql> REPAIR TABLE mysql.db;

Then restart normally. For PostgreSQL, you sometimes need to rebuild the authentication catalog. Stop PostgreSQL, then run:

sudo -u postgres pg_resetwal -f /var/lib/postgresql/14/main

But that's a nuclear option — it resets the WAL and can lose transactions. Better to restore from a known-good backup of pg_authid. That means you need regular dumps of grant tables. I schedule a daily cron job:

0 2 * * * mysqldump --all-databases --routines --events > /backups/mysql_full_$(date +%Y%m%d).sql

For PostgreSQL, use pg_dumpall --globals-only. When crash recovery corrupts your grants, you restore just that dump.

Quick-Reference Summary Table

One last opinion: don't use skip-grant-tables as a workaround. It disables all authentication — a security nightmare. The three fixes above are the only ones you'll ever need.