Load Linux Time Zones into MariaDB with mysql_tzinfo_to_sql

CONVERT_TZ('2026-01-15 12:00:00','UTC','Europe/London') returns NULL on a fresh MariaDB install, and mysql_tzinfo_to_sql is the fix. It converts the Linux zoneinfo database into SQL you feed straight to the client, and this guide proves the named zone actually resolves afterwards.

Allow about 15 minutes. You need the mariadb-client package, a readable zoneinfo directory, access to the target MariaDB server, and a database account allowed to write the mysql system tables. The generator itself is read-only; the pipe into the MariaDB client is the state-changing part.

1. Check the installed command and source data

First confirm the alias, package version, and the usual Linux zoneinfo directory. These checks do not need elevated privileges:

$ command -v mysql_tzinfo_to_sql
/usr/bin/mysql_tzinfo_to_sql
$ readlink -f /usr/bin/mysql_tzinfo_to_sql
/usr/bin/mariadb-tzinfo-to-sql
$ dpkg-query -W -f='${Package} ${Version}\n' mariadb-client
mariadb-client 1:10.11.14-0ubuntu0.24.04.1
$ test -d /usr/share/zoneinfo && echo 'zoneinfo directory exists'
zoneinfo directory exists

The manpage calls the input a tz_dir. On Linux that is normally /usr/share/zoneinfo. Do not substitute one file, such as Europe/London, when you intend to load the complete database.

Checkpoint: You should have the command path, a package version, and a real zoneinfo directory before producing SQL.

2. Inspect generated SQL without changing MariaDB

Pass the directory to the generator and save its output in /tmp for inspection. This command reads zoneinfo files and writes only the temporary file:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mariadb-timezones.sql
$ wc -l /tmp/mariadb-timezones.sql
<line-count> /tmp/mariadb-timezones.sql
$ sed -n '1,8p' /tmp/mariadb-timezones.sql
set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on');
...

The line count is host-specific, so treat <line-count> as a placeholder for a positive number printed on your machine. The generated file contains SQL for the MariaDB time zone tables, including transition and leap-second handling. Do not run an unreviewed file from an untrusted source against a database.

Remove the temporary file when you have finished checking it:

$ rm -- /tmp/mariadb-timezones.sql

3. Load the complete Linux database

Loading changes the server's mysql database. It can affect applications that use named zones, so schedule it with the same care as other system-table maintenance. Confirm the target host and database account before pressing Enter.

Use the MariaDB client with an account that can write the system tables. The password prompt is preferable to placing a password in shell history:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mariadb --host DB_HOST --user DB_ADMIN --password mysql

Replace DB_HOST and DB_ADMIN with deliberate values. If the server is local and its authentication policy permits socket login, an account such as root may work; that is a database privilege decision, not a reason to run the generator with sudo. The command normally prints no success summary, so inspect its exit status:

$ printf 'load exit status: %s\n' "$?"
load exit status: 0

A non-zero status means the load did not complete cleanly. Read the client error, check the account privileges and target host, and do not assume that a partially processed stream can be safely ignored.

Checkpoint: The pipeline must finish with exit status 0. At this point the tables have changed, but a running server may still use cached time zone data.

4. Verify named zone support

Connect to the same server and ask MariaDB to resolve a named zone. This checks the data that applications normally consume:

$ mariadb --host DB_HOST --user DB_ADMIN --password --batch --skip-column-names mysql \\
    -e "SELECT CONVERT_TZ('2026-01-15 12:00:00','UTC','Europe/London');"
2026-01-15 12:00:00

The shown value is expected for this January example. A summer date may differ because Europe/London observes daylight-saving transitions. If the result is NULL, check the client exit status, the selected server, and whether the server has reloaded its time zone cache.

After a successful load, restart the MariaDB server during an approved maintenance window so it stops using previously cached time zone data. This is service-disrupting and requires the service manager and operational privileges for your installation. If a restart is not yet possible, treat the verification result as provisional.

5. Load one zone when a full import is not wanted

The two-argument form reads one zoneinfo file and assigns it a zone name. This is useful for a narrowly controlled database, but it does not provide the complete set of zones or aliases:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/Europe/London Europe/London | \\
    mariadb --host DB_HOST --user DB_ADMIN --password mysql

Use the exact name applications will request. A separate invocation is needed for each zone and file that the server needs. Verify it with the same CONVERT_TZ query, then restart the server as described above.

6. Handle leap seconds and replication deliberately

If your deployment specifically needs leap-second information, use the documented --leap form with the installed leap-second file:

$ test -r /usr/share/zoneinfo/leapseconds && echo 'leap-second file is readable'
$ mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/leapseconds | \\
    mariadb --host DB_HOST --user DB_ADMIN --password mysql

Do not add this option merely because it exists. Confirm that the application and database policy require leap-second data, and verify that the file exists on your host.

The --skip-write-binlog option prevents changes from being written to the binary log or sent to other Galera cluster members. That is a replication decision, not a general safety switch. Use it only when your replication design explicitly calls for a local update:

$ mysql_tzinfo_to_sql --skip-write-binlog /usr/share/zoneinfo | \\
    mariadb --host DB_HOST --user DB_ADMIN --password mysql

On a replicated service, stop and confirm the intended topology before using this form. A local table update can leave nodes with different time zone data.

7. Recover by restoring the default empty tables

If you need to undo a load and return to MariaDB's default empty time zone tables, the official reset is destructive. It removes all imported named-zone and transition data, so take a database backup and obtain change approval first:

$ mariadb --host DB_HOST --user DB_ADMIN --password mysql <<'SQL'
TRUNCATE TABLE mysql.time_zone;
TRUNCATE TABLE mysql.time_zone_name;
TRUNCATE TABLE mysql.time_zone_transition;
TRUNCATE TABLE mysql.time_zone_transition_type;
TRUNCATE TABLE mysql.time_zone_leap_second;
SQL

Restart MariaDB after the reset. Until then, old cached values may remain in use. If you need the data again, rerun the complete-directory load rather than editing these system tables by hand.

Done means