Mirroring MySQL and MariaDB

ASQL can follow a MySQL or MariaDB server's binary log and keep chosen tables in step. For a step-by-step setup, see Mirror a MariaDB database.

How it works

At start, ASQL copies each listed table it does not have yet, all from one consistent snapshot of the source, and records the binary log position of that snapshot.
It then streams the binary log from that position (COM_BINLOG_DUMP, by file and position) and applies each transaction's rows together.
A table whose first copy was interrupted is copied again at the next start.

What the source needs

Setting	Why
`binlog_format=ROW`	only row events are applied; statement-logged changes never arrive
`binlog_row_image=FULL`	needed for tables without a primary key, whose rows are matched on every column
a user with `REPLICATION SLAVE`, `REPLICATION CLIENT` and `SELECT`	the stream, the log position, and the first copy

Checksums do not matter. The host can be a TCP address or a Unix socket path.

Settings

In /etc/aios/asql.conf:

Key	Meaning
`CDC_HOST`, `CDC_PORT`	the source (port default 3306)
`CDC_USER`, `CDC_PASS_FILE`	its login
`CDC_DB`	the source database; changes to other databases are ignored
`CDC_TABLES`	comma-separated tables to mirror
`CDC_LOG_FILE`, `CDC_LOG_POS`	a binary log position to stream from

With CDC_TABLES empty, every table in CDC_DB that appears in the binary log is mirrored, and nothing is copied first.

What is applied

Inserts, updates and deletes from row events.
CREATE, ALTER, TRUNCATE and DROP of listed tables, and view changes.
Each source transaction is applied as a unit.

A write made in ASQL to a mirrored table is overwritten by the next change to that row from the source. Things and Cells over mirrored tables update as the changes arrive.

Watching it

SHOW REPLICA STATUS;           -- position, lag, errors
SHOW ASQL MIRROR;              -- every mirrored table compared with the source
SHOW ASQL MIRROR orders;       -- one table

asql-check --quick reports the lag; asql-check compares every table row by row. Setting AIOS_CDC_TRACE=1 in the service's environment logs every applied statement.

Several mirrors of one source

Each ASQL root has its own replica id, so several mirrors can follow one source at once.