716-691-5201Schedule a Demo
ASQL docs / How-to guides

Mirror a MariaDB database

Keep chosen tables of a MariaDB (or MySQL) database in step in ASQL, then point reports and read-heavy pages at ASQL. Writes keep going to MariaDB.

1. Prepare MariaDB

In the MariaDB server's config:

log_bin          = mysql-bin
binlog_format    = ROW
binlog_row_image = FULL
server_id        = 1

Restart MariaDB, then create a user for ASQL:

CREATE USER 'asql_repl'@'10.0.0.%' IDENTIFIED BY 'choose-a-password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'asql_repl'@'10.0.0.%';
GRANT SELECT ON store.* TO 'asql_repl'@'10.0.0.%';

SELECT is for the first copy of each table; the two replication rights are for the binary log.

2. Point ASQL at it

On the ASQL server, put the password in a file only root and aios can read:

echo 'choose-a-password' | sudo tee /etc/aios/cdc.pass >/dev/null
sudo chown root:aios /etc/aios/cdc.pass && sudo chmod 0640 /etc/aios/cdc.pass

Add to /etc/aios/asql.conf:

CDC_HOST=10.0.0.5
CDC_PORT=3306
CDC_USER=asql_repl
CDC_PASS_FILE=/etc/aios/cdc.pass
CDC_DB=store
CDC_TABLES=orders,order_lines,customers,items
sudo systemctl restart aios-compute

At start, ASQL copies each listed table it does not have yet, all from one consistent snapshot, then streams changes from that point.

3. Check it

/opt/aios/bin/asql-check --quick     # service up, connection works, replication lag
/opt/aios/bin/asql-check             # also compares every table with MariaDB, row by row

In SQL, SHOW REPLICA STATUS shows the stream and SHOW ASQL MIRROR orders compares one table.

4. Send reads to ASQL

Point read-only connections (reports, portals, dashboards) at the ASQL server on port 3307. Leave writes on MariaDB: a write made directly to a mirrored table in ASQL is overwritten by the next change to that row from MariaDB.

Notes

  • Only row events are applied. A change MariaDB logs as a statement (MIXED or STATEMENT format) never arrives.
  • CREATE, ALTER, TRUNCATE and DROP on listed tables are applied, and so are view changes.
  • If CDC_TABLES is empty, every table in CDC_DB that appears in the binary log is mirrored, and nothing is copied first.
  • More detail: Mirroring MySQL and MariaDB.