Migration

HardhatDB speaks the MySQL wire protocol. Move a database by loading SQL into an empty server, then point clients at that server. InnoDB files stay on MySQL.

hardhatdb-cli runs the same steps. load copies schema and rows, then records the source @@GLOBAL.gtid_executed with SET @@GLOBAL.gtid_purged. replicate follows the MySQL binlog from that position when the destination already has the GTID set. A destination that has tables and an empty executed GTID set is refused, because starting there would insert the loaded rows again.

SQL has to be the MySQL dialect accepted by go-mysql-server. See Compatibility. The first boot creates one caching_sha2_password superuser, so MySQL grants do not come across. Clients use that account, or accounts created afterward with CREATE USER and GRANT.

Empty server

Leave HARDHATDB_SEED_EXAMPLE unset. The first boot creates the account and an empty data directory.

HARDHATDB_BOOTSTRAP_PASSWORD=secret go run ./cmd/hardhatdb sql-server

HARDHATDB_BOOTSTRAP_PASSWORD is required on that first boot and is not saved for later boots. The default user is root and the default host is %.

A published container needs HARDHATDB_MYSQL_HOST=0.0.0.0. See Getting Started.

Dump and load

hardhatdb-cli load reads tables from MySQL and writes them to HardhatDB, then recreates views, triggers, routines, and events without DEFINER. The command compares row counts and exits non-zero when a count differs.

hardhatdb-cli load mysql://root:secret@127.0.0.1:3306 \
  hardhatdb://root:secret@127.0.0.1:3307 appdb

Stop writes on MySQL before the copy when the cutover has to match the source exactly. A dump is the same copy for a large database. --single-transaction keeps an InnoDB dump consistent while the application is still writing. Writes that commit after the dump starts are absent from the file.

mysqldump --single-transaction --skip-triggers --no-tablespaces \
  --set-gtid-purged=OFF --databases appdb > app.sql
 
mysql --host=127.0.0.1 --port=3306 --user=root --password=secret < app.sql

A dump that includes SET @@GLOBAL.gtid_purged records that position when the replica is stopped. hardhatdb-cli load does the same from @@GLOBAL.gtid_executed. The loader recreates views, triggers, routines, and events and removes DEFINER from those statements.

Compare row counts on both servers before changing the application.

Catch up from the MySQL binlog

Use replication when MySQL still has the GTIDs after the loaded position. hardhatdb-cli replicate reads the host, port, user, and password from the source URL, then runs the statements below. Tables with an empty executed GTID set are refused. Tables with a recorded GTID set are the catch-up path. A source URL with tls set passes SOURCE_SSL = 1. The replica reads HARDHATDB_SOURCE_TLS_CA, HARDHATDB_SOURCE_TLS_CERT, and HARDHATDB_SOURCE_TLS_KEY when it dials.

hardhatdb-cli replicate mysql://repl:secret@mysql.example:3306 \
  hardhatdb://root:secret@127.0.0.1:3307 appdb

The current primary connects. A follower does not. Details are in Replication.

On MySQL:

gtid_mode=ON and enforce_gtid_consistency=ON
Row-based binary logging
A replication user that can read the binlog
server_id other than 2. The replica dials with server id 2
TLS when MySQL requires it. Set SOURCE_SSL = 1, or HARDHATDB_SOURCE_TLS=1 for the env path, and put the CA, cert, and key paths in HARDHATDB_SOURCE_TLS_CA, HARDHATDB_SOURCE_TLS_CERT, and HARDHATDB_SOURCE_TLS_KEY on every node

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'mysql.example',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'secret',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;
 
START REPLICA;

SOURCE_AUTO_POSITION stays on. SOURCE_AUTO_POSITION = 0 is rejected. The same host, port, user, and password can be set before the first start with HARDHATDB_SOURCE_HOST, HARDHATDB_SOURCE_PORT, HARDHATDB_SOURCE_USER, and HARDHATDB_SOURCE_PASSWORD. HARDHATDB_SOURCE_TLS=1 stores TLS on that env path. On a cluster, set them on every node. The password is a local file beside the Raft directory, so a promoted node can dial only when it already has that file. TLS paths are process environment, read again on each dial.

SET @@GLOBAL.gtid_purged writes the executed and retrieved GTID sets while the replica is stopped. hardhatdb-cli load runs it from the source @@GLOBAL.gtid_executed. hardhatdb-cli replicate then starts at that position. A destination that already has tables and an empty executed GTID set is refused. When MySQL has already purged binlogs, load the dump and skip the replica.

A HardhatDB data directory can be written out the same way while its server is stopped. The file is schema, rows, and the executed GTID set:

hardhatdb dump --data data/hardhatdb --out app.sql

Load that file into an empty directory on the new binary, then start the server. hardhatdb-cli load remains the path from MySQL.

Row events apply to tables that already exist. Query events, including DDL, run as SQL. Limit row events to one database with a wildcard filter:

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('appdb.%');

REPLICATE_DO_TABLE, REPLICATE_IGNORE_TABLE, REPLICATE_WILD_IGNORE_TABLE, REPLICATE_DO_DB, and REPLICATE_IGNORE_DB are also accepted. REPLICATE_DO_DB and REPLICATE_IGNORE_DB values have to contain a .. REPLICATE_REWRITE_DB is rejected. Table filters apply to row events. A query event whose default database fails REPLICATE_DO_DB or REPLICATE_IGNORE_DB is skipped. A query event with an empty database still runs.

SHOW REPLICA STATUS reports Replica_IO_Running, Replica_SQL_Running, Executed_Gtid_Set, Retrieved_Gtid_Set, and Seconds_Behind_Source.

Cut over

hardhatdb-cli status prints the replica fields. hardhatdb-cli cutover waits until Retrieved_Gtid_Set matches Executed_Gtid_Set, Seconds_Behind_Source is 0, and both threads are running, then runs STOP REPLICA. The default wait is 5 minutes. Make MySQL read-only before that. The command does not change MySQL.

hardhatdb-cli status hardhatdb://root:secret@127.0.0.1:3307
hardhatdb-cli cutover hardhatdb://root:secret@127.0.0.1:3307

With a replica, wait until Retrieved_Gtid_Set matches Executed_Gtid_Set and Seconds_Behind_Source is 0, then stop the job:

STOP REPLICA;

Point the application at HardhatDB. The driver stays a MySQL driver. Change the host, port, user, and password. When HARDHATDB_TLS_CERT and HARDHATDB_TLS_KEY are set, clients use TLS.

On a cluster, any node accepts a write and forwards it to the leader. A read on another connection can still see an older copy. Pass every address and try the next one when a connection breaks:

MYSQL_ADDRS=10.116.0.2:3306,10.116.0.3:3306,10.116.0.4:3306

Replication Sharding