SQL Reference

Ordinary SQL is the MySQL dialect accepted by go-mysql-server. This page is the statements HardhatDB adds, plus the replication statements it implements itself.

Raft status

SHOW RAFT STATUS;

One row:

The statement runs locally. On a follower, lag is how far that node is behind the commit index. suffrage is this process's membership.

Membership

These statements are leader operations. A follower forwards them. They fail when the process is not replicating.

RAFT ADD VOTER 'n4' '10.116.0.5:7001';
RAFT ADD NONVOTER 'n4' '10.116.0.5:7001';
RAFT REMOVE SERVER 'n4';

The id is the Raft server id (HARDHATDB_NODE_ID). The address is the Raft advertise address, host:port. A nonvoter copies the log and serves reads. It does not vote, so the quorum stays the voters. A write sent to it is forwarded to the leader. A read on another connection can lag. RAFT REMOVE SERVER drops a voter or a nonvoter.

Writes still commit on one leader. A faster machine for the voters raises that ceiling. A table whose writes do not fit that leader is placed with SHARD TABLE on a second set of peers. A size-based split stays on the current hosts and does not add a machine.

Binlog

FLUSH BINARY LOGS;

On the leader, this rolls every node's binlog together.

Backup

BACKUP TO '/var/backups/1';

Writes one directory on the server that runs the statement. A follower forwards it, so that server is the leader. A standalone process writes it locally. The directory is created when it is missing and refused when it already holds files.

The index in meta is 0 when the process is standalone. Events appended after the copy are not in this backup.

Load the directory into an empty data directory while that server is stopped:

hardhatdb restore --from /var/backups/1 --data /data/hardhatdb

The command refuses a data directory that already exists. Point the new process at a fresh Raft directory. HARDHATDB_RAFT_BOOTSTRAP=1 on restored data that already has a Raft index starts the group from that index instead of index 1. A standalone restore starts sql-server on the restored directory.

Replay the binlog tail on the leader of that new group. path is the backup's binlog/ directory, and n is the index in meta. Events at or below n are skipped.

RESTORE BINLOG FROM '/var/backups/1/binlog' AFTER 12;

The new group's own binlog records the replayed tail under new indexes. An external replica of the old GTID stream does not resume from this group. Run the statement on the restored group, not on the cluster the backup was taken from.

Upstream replica

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

Accepted source options are SOURCE_HOST, SOURCE_USER, SOURCE_PASSWORD, SOURCE_PORT, and SOURCE_AUTO_POSITION. Auto-position has to stay on. The current primary is the only node that connects. REPLICATE_REWRITE_DB is rejected. Moving an existing MySQL database is in Migration.

Sharding

SHARD TABLE db.table BY column [CHECK column]
  RANGE [START n] [END n]
  [GROUP id PEER id raft-addr forward-addr ...];

START and END are exclusive integer bounds. A statement with no RANGE is recognized and then rejected. Each PEER is a member id, a Raft address, and a write-forward address. Without GROUP, the range covers this process's data group.

A sharded insert includes the shard column. A write whose keys sit on several ranges is split per range and committed in two phases; see Sharding. A read that does not name the shard column is sent to every range that holds the table.