Pack a MyISAM Table Safely with myisampack

An archive table that never changes but keeps eating disk space is what myisampack is for. It compresses the table and leaves it read-only from then on. The examples match the MariaDB 10.11.14 package installed here, where myisampack reports version 1.23.

Allow about twenty minutes for one table, plus time to test the application that reads it. You need shell access, the mariadb-server package, a MyISAM table that is not being written, and enough free space for temporary and backup files. Access to a database directory usually needs elevated privileges, but the utility itself should run as the owner of the table files where possible.

Warning: Packing changes the table in place. The result is read-only. Do not use this workflow for a table that must accept inserts or updates. Take a database backup before changing production data files, and schedule a maintenance window if MariaDB could touch the table.

1. Confirm the installed command

Start with read-only checks. They do not need sudo and help prevent copying syntax from a different MySQL or MariaDB release:

$ command -v myisampack
/usr/bin/myisampack
$ myisampack --version
myisampack Ver 1.23 for debian-linux-gnu on x86_64
$ dpkg-query -W -f='${Package} ${Version}\n' mariadb-server
mariadb-server 1:10.11.14-0ubuntu0.24.04.1

Checkpoint: continue only if this is the database installation whose table files you intend to change. The utility reads option files, including /etc/my.cnf, /etc/mysql/my.cnf and ~/.my.cnf, and reads the myisampack group. If an unexpected option appears, inspect those files or use --no-defaults as the first argument for a deliberately isolated invocation.

2. Locate the exact MyISAM files

myisampack expects an index file ending in .MYI. It uses the matching data file, normally ending in .MYD, and table definition file. Work from the database directory or provide the complete path:

$ DB_DIR='/var/lib/mysql/EXAMPLE_DATABASE'
$ TABLE='ARCHIVE_TABLE'
$ ls -l "$DB_DIR/$TABLE".{MYI,MYD,frm}
-rw-r----- 1 mysql mysql ... ARCHIVE_TABLE.MYD
-rw-r----- 1 mysql mysql ... ARCHIVE_TABLE.MYI
-rw-r----- 1 mysql mysql ... ARCHIVE_TABLE.frm

Replace both placeholders with real names. Do not guess from a logical SQL table name if several files have similar names. Check the owner, timestamps and size against your backup or inventory. A missing .MYI file is a reason to stop and repair the table first, not a reason to create an empty replacement.

3. Stop writes before the packing run

The safest arrangement is to stop MariaDB before changing the files. This is an elevated, service-disrupting action, so confirm that other applications can tolerate the outage and record how you normally start the service again:

$ sudo systemctl stop mariadb
$ systemctl is-active mariadb
inactive

If your installation uses a different service manager or a controlled read-only maintenance mode, use that local procedure instead. The manpage specifically warns against packing while the server may update a table when external locking is disabled. The --wait option only waits and retries when a table is in use; it is not a substitute for stopping writes or proving that no writer can race the operation.

Checkpoint: before continuing, confirm that the target files are not changing. If you cannot establish that, stop here and restore the service with sudo systemctl start mariadb rather than taking the risk.

4. Test the packing plan without changing the table

Use --test and --verbose against the index file. This checks the packing operation without actually packing it:

$ sudo -u mysql myisampack --test --verbose "$DB_DIR/$TABLE.MYI"
... packing statistics ...
$ printf 'test status: %s\n' "$?"
test status: 0

The statistics and their exact values depend on the table data. A non-zero status means the real run should not be attempted until the reported problem is understood. If the table is owned by another account, run as that account or use the least privileged account that can read and write the files. Avoid running the utility as root just to hide an ownership problem.

5. Make a backup, then pack the table

Make an independent backup first. The --backup option also preserves the data file as TABLE.OLD during the operation, but it is not a replacement for a tested database backup. Check that the destination has enough free space before proceeding.

$ sudo cp --preserve=all "$DB_DIR/$TABLE.MYD" "$DB_DIR/$TABLE.MYD.before-myisampack"
$ sudo cp --preserve=all "$DB_DIR/$TABLE.MYI" "$DB_DIR/$TABLE.MYI.before-myisampack"
$ sudo -u mysql myisampack --backup --verbose "$DB_DIR/$TABLE.MYI"
Compressing ...
Remember to run myisamchk -rq on compressed tables

The output normally includes compression statistics and a percentage, but the values are data-dependent. A table that would become larger is normally rejected. Do not add --force casually: it permits packing when the result is larger and also permits an existing intermediate .TMD file to be ignored. An old .TMD file can mean an earlier run was interrupted; investigate it before deciding whether removal or a forced retry is safe.

Checkpoint: inspect the files before rebuilding indexes. The data file should have changed, and the command should have completed without an error:

$ ls -l "$DB_DIR/$TABLE".{MYI,MYD,frm} "$DB_DIR/$TABLE.MYD.before-myisampack"
$ printf 'pack status: %s\n' "$?"
pack status: 0

6. Rebuild the indexes

Packing does not update the keys. Run myisamchk -rq on the packed index file, as the manpage requires and as the tool's own output reminds you. The longer form also sorts index blocks and creates statistics for the optimiser:

$ sudo -u mysql myisamchk -rq --sort-index --analyze "$DB_DIR/$TABLE.MYI"
$ printf 'index rebuild status: %s\n' "$?"
index rebuild status: 0

Do not run repair tools against a live table that another process can write. If this command fails, do not start the service and hope for the best. Keep the original files and backup intact, record the error, and restore the pre-packing files only after checking ownership and permissions. For a controlled rollback, stop MariaDB, move the packed .MYD and .MYI aside, put the matching .MYD.before-myisampack and .MYI.before-myisampack files back under their original names, then start MariaDB and verify the table. Use a real backup restore procedure if the copies are incomplete.

7. Start MariaDB and refresh its table handles

Put the packed files in the database directory if you packed a staging copy. Then start the service and flush its table handles:

$ sudo systemctl start mariadb
$ sudo systemctl is-active mariadb
active
$ mysqladmin flush-tables
$ printf 'flush status: %s\n' "$?"
flush status: 0

mysqladmin flush-tables may need authentication options appropriate to your installation. It forces the server to start using the new table. If the command is denied, use your normal administrative login rather than placing a password in the shell history.

Read the table from the application or a low-risk SQL query, then check the service journal and application logs for errors. A successful flush only shows that MariaDB accepted the table handles; it does not prove that every query or application expectation is correct.

8. Check the read-only boundary and recovery path

Packed MyISAM tables are intended for data that no longer changes, such as an archive. Test a representative read, then confirm the owner and mode have not become broader than before:

$ ls -l "$DB_DIR/$TABLE".{MYI,MYD,frm}
$ sudo -u mysql myisamchk -dvv "$DB_DIR/$TABLE.MYI" | sed -n '1,24p'
MyISAM file: ...
Record format: Compressed

The diagnostic text varies by release and table. Seeing a compressed record format is useful evidence, but it is not a substitute for an application read test. Keep the external backup until the retention policy says it can be removed. To unpack later, use myisamchk --unpack on the packed table during another maintenance window, then rebuild or verify indexes as your local procedure requires. Do not delete the .OLD or .before-myisampack files as part of an untested batch.

Done means