Running MariaDB: InnoDB, GTIDs and backups

MariaDB speaks MySQL’s client protocol and most of its SQL, yet the server that answers is a project of its own. The recorded run below asks a MariaDB 12.3.3 and a MySQL 8.4.11 server for twelve variables, and eleven of them differ. InnoDB on MariaDB has snapshot isolation on by default and no change buffer at all; its GTIDs name a transaction by three numbers rather than a server UUID and a sequence; mariadb-backup writes a hot physical copy and records the binary-log position beside it; and performance_schema stays off until someone turns it on. Each of those four facts comes from a run recorded on MariaDB 12.3.3 below.

MariaDB’s InnoDB keeps its own defaults

A MariaDB server started with no option file, and a MySQL 8.4.11 server next to it, give very different answers to the same SELECT @@global.<name> queries. InnoDB on MariaDB adds snapshot isolationsnapshot isolation: MariaDB's REPEATABLE READ with innodb_snapshot_isolation ON (the default from 11.6.2): a transaction that would UPDATE, DELETE or lock a row changed after its snapshot ends with ERROR 1020 and rolls back, instead of overwriting a newer row. as a default of REPEATABLE READ, so a stale write is refused with ERROR 1020 instead of overwriting a newer row, as the isolation lesson explains. The change bufferchange buffer: A structure that holds changes to secondary index pages that are not in the buffer pool, inside part of the pool, and merges them when those pages are read. innodb_change_buffering controls it; MySQL 8.4 sets it to none by default. More in “The buffer pool: LRU, flushing and warm-up” is gone from MariaDB 11.0 and later, so the table shows innodb_change_buffering as absent on MariaDB: reading it returns ERROR 1193 Unknown system variable, while MySQL still has it with the default none. Other defaults differ as well: innodb_io_capacity is 200 against MySQL’s 10,000, innodb_undo_tablespaces is 3 against 2, innodb_autoinc_lock_mode is 1 against 2, performance_schema is off (0) instead of on, and log_bin is off where MySQL 8.4 turns the binary log on by default. sql_mode keeps NO_AUTO_CREATE_USER where MySQL has ONLY_FULL_GROUP_BY and the zero-date rules. Side by side:

"""One MariaDB 12.3.3 and one MySQL 8.4.11 server side by side, with the defaults for the InnoDB and server
variables that differ. The change buffer is read on both: MariaDB 12.3 answers with ERROR 1193 because the engine
removed it, and the variable with it. innodb_snapshot_isolation is read on both: MariaDB answers with its value,
MySQL with the same error for the opposite reason, it has no such variable. Values that depend on the host
(innodb_flush_method on macOS) are labelled where they are printed. sql_mode is printed on its own below the table
because its values are long."""

from mysql_session import MariaDB, Server


ROWS = [
    ("innodb_snapshot_isolation",   None),
    ("innodb_change_buffering",     None),
    ("innodb_adaptive_hash_index",  None),
    ("innodb_io_capacity",          None),
    ("innodb_undo_tablespaces",     None),
    ("innodb_autoinc_lock_mode",    None),
    ("innodb_flush_method",         "host"),
    ("log_bin",                     None),
    ("binlog_format",               None),
    ("sync_binlog",                 None),
    ("performance_schema",          None),
    ("query_cache_type",            None),
]


def read(srv, name):
    """One value or the string 'absent' when the server has no such system variable."""
    code, out = srv._call([srv.CLIENT, f"--defaults-file={srv.client_cnf}", "-N", "-B",
                           "-e", f"SELECT @@global.{name}"])
    if code != 0:
        return "absent"
    return out.strip()


with MariaDB() as mdb, Server() as my:
    mdb.up()
    my.up()
    print("-- MariaDB", mdb.query("SELECT @@version"), "| MySQL", my.query("SELECT @@version"))
    print()

    rows = [("variable", "MariaDB 12.3.3", "MySQL 8.4.11", "")]
    for name, note in ROWS:
        rows.append((name, read(mdb, name), read(my, name), "(this host)" if note == "host" else ""))

    widths = [max(len(r[i]) for r in rows) for i in range(4)]
    sep = "+" + "+".join("-" * (w + 2) for w in widths) + "+"
    def line(r):
        return "|" + "|".join(" " + c.ljust(w) + " " for c, w in zip(r, widths)) + "|"
    print(sep)
    print(line(rows[0]))
    print(sep)
    for r in rows[1:]:
        print(line(r))
    print(sep)
    print()
    print("# sql_mode (one default per server)")
    print(" MariaDB 12.3.3: " + read(mdb, "sql_mode"))
    print(" MySQL   8.4.11: " + read(my, "sql_mode"))

-- MariaDB 12.3.3-MariaDB | MySQL 8.4.11

+----------------------------+----------------+--------------+-------------+
| variable                   | MariaDB 12.3.3 | MySQL 8.4.11 |             |
+----------------------------+----------------+--------------+-------------+
| innodb_snapshot_isolation  | 1              | absent       |             |
| innodb_change_buffering    | absent         | none         |             |
| innodb_adaptive_hash_index | 0              | 0            |             |
| innodb_io_capacity         | 200            | 10000        |             |
| innodb_undo_tablespaces    | 3              | 2            |             |
| innodb_autoinc_lock_mode   | 1              | 2            |             |
| innodb_flush_method        | O_DIRECT       | fsync        | (this host) |
| log_bin                    | 0              | 1            |             |
| binlog_format              | MIXED          | ROW          |             |
| sync_binlog                | 0              | 1            |             |
| performance_schema         | 0              | 1            |             |
| query_cache_type           | OFF            | absent       |             |
+----------------------------+----------------+--------------+-------------+

# sql_mode (one default per server)
 MariaDB 12.3.3: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 MySQL   8.4.11: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

GTIDs name a transaction by domain, server and sequence

A MariaDB GTIDA global transaction ID: a name that stays with one transaction on every server that applies it. MariaDB writes it as domain-server_id-sequence; MySQL writes the source's server UUID and a sequence number. is domain-server_id-sequence, three numbers. In the figure below, the source writes five transactions under domain 0 and two under domain 5, so its gtid_binlog_pos reads 0-1-5,5-1-2: one segment per domain the server has written under. A MySQL GTID is the source’s server UUID and a sequence, as the GTID lesson showed, so the two schemes meet at the GTID setGTIDs written as comma-separated segments. A MariaDB position keeps one GTID per domain, such as 0-1-5,5-1-2; a MySQL set lists ranges of sequence numbers for each source server UUID. idea and part at what each segment holds. A GTID domainGTID domain: The first number in a MariaDB GTID (domain-server_id-sequence): a group of writes that share an order, so independent write streams can be tagged with a different domain and applied in parallel on a replica. lets independent write streams keep their own sequence, and a replica may apply transactions of different domains in parallel and out of order. gtid_strict_mode, a strict modegtid_strict_mode: A MariaDB server variable, OFF by default: when it is ON, the server refuses any GTID whose sequence number would go backwards inside its GTID domain. switch, is OFF by default; turned on, it makes a server refuse a GTID whose sequence number would go backwards within its domain. Once slave_parallel_threads gives a replica worker threads, its default slave_parallel_mode=optimistic runs optimistic parallel replicationoptimistic parallel replication: MariaDB's default parallel-apply mode: worker threads apply replicated transactions at once, and when two of them collide on a row the later one is rolled back and retried.: several transactions apply at once, and the one that collides is retried. Four other values of the same variable trade parallel work against conflicts in other ways.

mariadb-backup takes a hot physical copy with the binary-log position

MariaDB’s physical backupphysical backup: A backup that copies the server's binary data files — InnoDB tablespaces, redo logs, binary logs — and is restored by copying them back; faster than a logical reload but tied to the server's version and layout. tool is mariadb-backupmariadb-backup: MariaDB's physical backup program, forked from Percona XtraBackup 2.3.8 and shipped with MariaDB. It copies InnoDB, Aria and MyISAM tables on Linux and Windows., shipped with the server. mariadb-backup --backup --target-dir=bk copies the running server’s data files into bk/ with the server online (MariaDB’s manual calls the method nearly non-blocking), and finishes with one line that names the restore point: MySQL binlog position: filename 'mariadb-bin.000001', position '855', GTID of the last change '0-1-3'. The same three fields go into bk/mariadb_backup_binlog_info as one tab-separated line, the point where a point-in-time recovery starts its binary-log replay. The copied files are not yet consistent with each other, because they were copied at different moments, so mariadb-backup --prepare --target-dir=bk applies the redo log captured during the copy until a MariaDB server can start on the folder. mariadb-upgrade is a separate tool that brings the system tables up to date after a major upgrade. The run keeps only the lines that teach, because the full --backup output is about 440 time-stamped lines:

"""mariadb-backup --backup and --prepare on a throwaway MariaDB 12.3.3 server with binary logging on.
--backup prints about 440 lines, most of them one line per file it copies, each with a time stamp; --prepare prints
about 25, the messages of the InnoDB recovery it runs. The lines that teach are the binlog position and completed
OK!: this script captures the whole output unprinted and prints only those lines (stamps removed), then lists the
backup folder and prints the mariadb_backup_binlog_info file."""

import re

from mysql_session import MariaDB


STAMP = re.compile(r"^\[00\] \d{4}-\d\d-\d\d \d\d:\d\d:\d\d ")
SIZE = {"backup": "about 440 time-stamped lines", "prepare": "about 25 lines"}


def mariadb_backup(srv, phase):
    """Runs mariadb-backup --<phase> --target-dir=bk unprinted and prints only the lines that teach."""
    args = ["mariadb-backup"]
    if phase == "backup":
        args.append(f"--defaults-file={srv.client_cnf}")
    args += [f"--{phase}", "--target-dir=bk"]
    code, out = srv._call(args)
    if code != 0:
        print(srv.clean(out))
        raise SystemExit(f"mariadb-backup --{phase} ended with status {code}")
    print(f"$ mariadb-backup --{phase} --target-dir=bk   # the full run prints {SIZE[phase]}")
    for line in srv.clean(out).splitlines():
        plain = STAMP.sub("", line)
        if "MySQL binlog position" in line or plain == "completed OK!":
            print(plain)
    print()


with MariaDB(options=["--server-id=1", "--log-bin=mariadb-bin", "--binlog-format=ROW"]) as mdb:
    mdb.up()
    mdb.sql("CREATE DATABASE shop; "
            "CREATE TABLE shop.t (id INT PRIMARY KEY) ENGINE=InnoDB; "
            "INSERT INTO shop.t VALUES (1), (2), (3)",
            note="Three statements in domain 0 before the backup: a database, a table, one three-row insert.")

    mariadb_backup(mdb, "backup")
    mariadb_backup(mdb, "prepare")

    mdb.listing("bk")
    mdb.show_file("bk/mariadb_backup_binlog_info")

-- Three statements in domain 0 before the backup: a database, a table, one three-row insert.
$ mariadb -t -e "CREATE DATABASE shop; CREATE TABLE shop.t (id INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO shop.t VALUES (1), (2), (3)"

$ mariadb-backup --backup --target-dir=bk   # the full run prints about 440 time-stamped lines
MySQL binlog position: filename 'mariadb-bin.000001', position '855', GTID of the last change '0-1-3'
completed OK!

$ mariadb-backup --prepare --target-dir=bk   # the full run prints about 25 lines
completed OK!

$ ls -p bk
aria_log.00000001  aria_log_control  backup-my.cnf  ib_logfile0  ibdata1
mariadb_backup_binlog_info  mariadb_backup_checkpoints  mariadb_backup_info
mysql/  performance_schema/  shop/  sys/  undo001  undo002  undo003
xtrabackup_binlog_pos_innodb

$ cat bk/mariadb_backup_binlog_info
mariadb-bin.000001	855	0-1-3

GTID strict mode and parallel apply, with a two-domain source and replica Pro: opens with a Pro, Premium or Ultimate pass

mariadb-backup to a prepared folder, and mariadb-upgrade for major steps Pro: opens with a Pro, Premium or Ultimate pass

Practise

The questions below work from the defaults table, the GTID figure and the backup run above, and the set opens with the idea your answers so far have left weakest:

Interview questions

Warm-up: what three numbers make up a MariaDB GTID, and what does each one mean?

A MariaDB GTID is written as domain-server_id-sequence, three integers separated by hyphens, as the recorded source shows with 0-1-5. The domain groups writes that share an order, so independent write streams can sit in different domains and a replica can apply them in parallel. The server id belongs to the server that first wrote the transaction (1 for the source in the run). The sequence is a per-domain counter that rises with each transaction (5 after five transactions in that domain). A set of GTIDs reads as one or more segments like 0-1-5,5-1-2.

Three questions follow, each asking for a diagnosis and a plan: a replica that stops in strict mode, a mariadb-backup restore whose point-in-time replay starts in the wrong place, and the choice of parallel-apply mode for a workload that has two independent streams.

Key takeaways

• MariaDB 12.3 keeps InnoDB snapshot isolation on by default, has no change buffer, and defaults innodb_io_capacity to 200 and performance_schema to off.
• A MariaDB GTID is domain-server_id-sequence, so a two-domain source shows two segments in its gtid_binlog_pos, as the recorded 0-1-5,5-1-2 run shows.
• mariadb-backup --backup writes a hot physical copy and the binary-log position into mariadb_backup_binlog_info, and --prepare makes the copied files consistent so a server can start on them.
• gtid_strict_mode refuses GTIDs that go backwards inside a domain, slave_parallel_mode sets how much a replica applies in parallel, and mariadb-upgrade brings the system tables up to a new major release.