Taking MySQL Offline the Easy Way: Using ignore-db-dir

The idea for this post came from a client transitioning from SQL Server to MySQL. In SQL Server, taking a database offline is a single command (ALTER DATABASE SET OFFLINE), but MySQL doesn’t have a direct equivalent.

That got me thinking — how can we achieve something similar in MySQL? And that’s where ignore-db-dir comes in. Sometimes, you need to take a MySQL database offline quickly — maybe to perform maintenance, isolate a corrupted database, or simply prevent access during a critical update.

While there are several ways to achieve this, ignore-db-dir stands out as an unconventional but incredibly simple method. It's not the go-to solution for most DBAs, but when you need a quick and reversible way to take a database offline without touching the data, it gets the job done.

Why ignore-db-dir?

Think of ignore-db-dir as a quick, non-destructive way to make a MySQL database temporarily disappear without actually deleting it. As of MySQL 5.7.11 and later, ignore-db-dir allows the target directory to contain other files, such as logs or hidden files (starting with a dot), without causing startup issues. This provides flexibility while still effectively isolating the database. The data remains intact in the filesystem, but MySQL acts as if the database doesn’t exist — making it perfect for quick maintenance or troubleshooting.

How to Use ignore-db-dir

Stop MySQL:
Before making any changes, stop the MySQL service to safely apply the directive:

sudo systemctl stop mysql

Edit the MySQL Configuration:
Open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Apply the ignore-db-dir Directive:
Add this line under the [mysqld] section:

ignore-db-dir = mydatabase

Restart MySQL:
Now, start the MySQL service again:

sudo systemctl start mysql

Verify the Database is Offline:
Check the list of databases:

SHOW DATABASES;

Trying to access the ignored database will return:

ERROR 1049 (42000): Unknown database 'mydatabase'

Bringing the Database Back Online

Remove the Directive:
Edit the configuration file and comment out or delete the ignore-db-dir line:

# ignore-db-dir = mydatabase

Restart MySQL Again:

sudo systemctl restart mysql

Verify Access:
Now, the database will reappear in the list of databases, and you can access it as usual:

USE mydatabase;

When to Use ignore-db-dir (and When Not To)

Use It When:

• You need to isolate a specific database quickly without impacting other databases.

• You want a fast, reversible way to take a database offline.

• You need to prevent accidental writes during maintenance.

Avoid It When:

• The database is part of a replication setup — the replicas may desync.

• The database is under heavy transactional load, potentially causing data inconsistency.

Quick Mentions: Other Ways to Take MySQL Offline

While ignore-db-dir is the quickest and least invasive method, other options include:

• Revoking User Access: Effective for targeted database access control.

• Setting Read-Only Mode: Prevents writes but allows reads.

• Renaming the Database Directory: Fast but riskier and not recommended in production.

• Blocking Network Access: Isolates the database without stopping MySQL.

Wrapping Up

The ignore-db-dir directive is a hidden gem for anyone looking to temporarily take a MySQL database offline without touching the data. It’s simple, effective, and easily reversible — making it perfect for quick maintenance, troubleshooting, or isolating a problematic database. Just remember to document the change and notify affected teams to avoid confusion. And if you need a more surgical approach, consider the other methods mentioned above.