If you need a highly available database configuration where the loss of one node doesn’t result in loss of service and changes on one node are replicated to other nodes, you would probably reach for MariaDB, PostgreSQL, or one of the big boys like Oracle or SQL Server. Probably the last thing that would cross your mind is sqlite. After all, sqlite is not even multi-user, much less multi-node.
That’s very true for sqlite, but there’s a project called rqlite that adds both replication and high availability to SQLite, as well as other features such an an http(s) API, encrypted traffic, read-only nodes, and even hot backups. It’s kind of the ultimate LowEndDatabase :-)
In this tutorial we’ll setup a 3-node rqlite cluster.
Setting Up the VPSes
I’m going to use three VPSes for the cluster. rqlite uses the RAFT consensus algorithm for high availability and replication, which means that you need to size your clusters in odd numbers – 3, 5, etc.
I’ll be using the following config, in which I’ve replace the real IPs with fake numbers:
1.1.1.1 rqlite1.lowend.party 2.2.2.2 rqlite2.lowend.party 3.3.3.3 rqlite3.lowend.party
To install rqlite, I’ll grab the latest release from Github and set it up in /usr/local:
# cd /usr/local # curl -L https://github.com/rqlite/rqlite/releases/download/v5.3.0/rqlite-v5.3.0-linux-amd64.tar.gz -o rqlite-v5.3.0-linux-amd64.tar.gz # tar xvfz rqlite-v5.3.0-linux-amd64.tar.gz # ln -s rqlite-v5.3.0-linux-amd64 rqlite # cd rqlite
I did this on all three nodes.
Startup rqlite the First Time
The first time you start rqlite, it won’t have any configuration information about the nodes, so we’ll start rqlite1 as the cluster leader and then manually join the other nodes. In future starts/restarts, rqlite will know its config and we can start things in any order.
On rqlite1, I type:
# ./rqlited -node-id 1 -http-addr 1.1.1.1:4001 -raft-addr 1.1.1.1:4002 /usr/local/rqlite/db
The arguments break down as follows:
- -node-id: This can be any string, as long as it’s unique in the cluster
- -http-addr: This is the address and port I’m going to use for the HTTP interface, which is also used by the rqlite command-line client, etc.
- -raft-addr: This is the address and port that other nodes will connect to for intra-cluster traffic.
- /usr/local/rqlite/db: This directory (which will be crated if it doesn’t exist) will hold the database and state inforamtion.
After issuing the command, I see a lot of info go by:
_ _ _ | (_) | _ __ __ _| |_| |_ ___ | '__/ _ | | | __/ _ \ The lightweight, distributed | | | (_| | | | || __/ relational database. |_| \__, |_|_|\__\___| | | |_| [rqlited] 2020/05/25 11:08:14 rqlited starting, version v5.3.0, commit 5f762f01ecf2ad74ea398e20258239fca89777e2, branch master [rqlited] 2020/05/25 11:08:14 go1.14, target architecture is amd64, operating system target is linux [store] 2020/05/25 11:08:14 opening store with node ID 1 [store] 2020/05/25 11:08:14 ensuring directory at /usr/local/rqlite/db exists [store] 2020/05/25 11:08:14 SQLite in-memory database opened 2020-05-25T11:08:14.117-0700 [INFO] raft: Initial configuration (index=0): [] [store] 2020/05/25 11:08:14 bootstrap needed 2020-05-25T11:08:14.117-0700 [INFO] raft: Node at 1.1.1.1:4002 [Follower] entering Follower state (Leader: "") [rqlited] 2020/05/25 11:08:14 no join addresses set 2020-05-25T11:08:15.752-0700 [WARN] raft: Heartbeat timeout from "" reached, starting election 2020-05-25T11:08:15.752-0700 [INFO] raft: Node at 1.1.1.1:4002 [Candidate] entering Candidate state in term 2 2020-05-25T11:08:15.754-0700 [INFO] raft: Election won. Tally: 1 2020-05-25T11:08:15.754-0700 [INFO] raft: Node at 1.1.1.1:4002 [Leader] entering Leader state [store] 2020/05/25 11:08:15 waiting for up to 2m0s for application of initial logs [http] 2020/05/25 11:08:15 service listening on 1.1.1.1:4001 start node 2
Wait until you see the “service listening” before proceeding.
On node2, the command is the same with the addition of the -join parameter, which tells rqlite where to join the cluster:
# ./rqlited -node-id 2 -http-addr 2.2.2.2:4001 -raft-addr 2.2.2.2:4002 -join http://1.1.1.1:4001 /usr/local/rqlite/db
Note that rqlite is instructed to join the http address, not the RAFT address.
And now on node 3:
# ./rqlited -node-id 3 -http-addr 3.3.3.3:4001 -raft-addr 3.3.3.3:4002 -join http://1.1.1.1:4001 /usr/local/rqlite/db
And if you go back to node 1 you should see messages like this:
2020/05/25 11:09:53 [DEBUG] raft-net: 1.1.1.1:4002 accepted connection from: 3.3.3.3:36882
Restarting rqlite
At this point, you could stop all the rqlited sessions and restart them without the -join parameter, or with the other two nodes specified in -join. It’s not necessary for a human to pick a leader and manually join, so you can write startup scripts without worrying about which node is coming up first, etc.
rqlite Command Line Client
Your command line client will be at /usr/local/rqlite/rqlite. You can use it the same way you would the sqlite3 CLI, except that you’ll need to put in the hostname (and port, if you’ve changed it from the default). Let’s create and populate a table:
root@rqlite3:/usr/local/rqlite# ./rqlite -H rqlite3.lowend.party Welcome to the rqlite CLI. Enter ".help" for usage hints. rqlite3.lowend.party:4001> create table planets (from_sun integer not null primary key, name varchar(30)); rqlite3.lowend.party:4001> insert into planets (from_sun, name) values (1, 'Mercury'); 1 row affected rqlite3.lowend.party:4001> insert into planets (from_sun, name) values (2, 'Venus'); 1 row affected rqlite3.lowend.party:4001> insert into planets (from_sun, name) values (3, 'Earth'); 1 row affected
Now on node 1:
root@rqlite1:/usr/local/rqlite# ./rqlite -H rqlite1.lowend.party Welcome to the rqlite CLI. Enter ".help" for usage hints. rqlite1.lowend.party:4001> select * from planets; +----------+---------+ | from_sun | name | +----------+---------+ | 1 | Mercury | +----------+---------+ | 2 | Venus | +----------+---------+ | 3 | Earth | +----------+---------+ rqlite1.lowend.party:4001>
We can see that replication is working.
Testing rqlite High Avilability
Let's crash node 1 by hitting control-C in its rqlited session. Looking then on rqlite2, we see: 2020-05-25T11:09:52.883-0700 [WARN] raft: Heartbeat timeout from "1.1.1.1:4002" reached, starting election 2020-05-25T11:09:52.883-0700 [INFO] raft: Node at 2.2.2.2:4002 [Candidate] entering Candidate state in term 3 2020-05-25T11:09:52.885-0700 [ERROR] raft: Failed to make RequestVote RPC to {Voter 1 1.1.1.1:4002}: dial tcp 1.1.1.1:4002: connect: connection refused 2020/05/25 11:09:53 [DEBUG] raft-net: 2.2.2.2:4002 accepted connection from: 3.3.3.3:36882 2020-05-25T11:09:53.627-0700 [INFO] raft: Duplicate RequestVote for same term: 3 2020-05-25T11:09:54.288-0700 [WARN] raft: Election timeout reached, restarting election 2020-05-25T11:09:54.288-0700 [INFO] raft: Node at 2.2.2.2:4002 [Candidate] entering Candidate state in term 4 2020-05-25T11:09:54.290-0700 [ERROR] raft: Failed to make RequestVote RPC to {Voter 1 1.1.1.1:4002}: dial tcp 1.1.1.1:4002: connect: connection refused 2020-05-25T11:09:54.293-0700 [INFO] raft: Election won. Tally: 2 2020-05-25T11:09:54.293-0700 [INFO] raft: Node at 2.2.2.2:4002 [Leader] entering Leader state 2020-05-25T11:09:54.293-0700 [INFO] raft: Added peer 1, starting replication 2020-05-25T11:09:54.293-0700 [INFO] raft: Added peer 3, starting replication
Then let’s restart node1:
_ _ _ | (_) | _ __ __ _| |_| |_ ___ | '__/ _ | | | __/ _ \ The lightweight, distributed | | | (_| | | | || __/ relational database. |_| \__, |_|_|\__\___| | | |_| [rqlited] 2020/05/25 11:10:43 rqlited starting, version v5.3.0, commit 5f762f01ecf2ad74ea398e20258239fca89777e2, branch master [rqlited] 2020/05/25 11:10:43 go1.14, target architecture is amd64, operating system target is linux [store] 2020/05/25 11:10:43 opening store with node ID 1 [store] 2020/05/25 11:10:43 ensuring directory at /usr/local/rqlite/db exists [store] 2020/05/25 11:10:43 SQLite in-memory database opened 2020-05-25T11:10:43.144-0700 [INFO] raft: Initial configuration (index=6): [{Suffrage:Voter ID:1 Address:1.1.1.1:4002} {Suffrage:Voter ID:2 Address:2.2.2.2:4002} {Suffrage:Voter ID:3 Address:3.3.3.3:4002}] [store] 2020/05/25 11:10:43 no bootstrap needed [rqlited] 2020/05/25 11:10:43 no join addresses set 2020-05-25T11:10:43.144-0700 [INFO] raft: Node at 1.1.1.1:4002 [Follower] entering Follower state (Leader: "") 2020-05-25T11:10:44.825-0700 [WARN] raft: Heartbeat timeout from "" reached, starting election 2020-05-25T11:10:44.825-0700 [INFO] raft: Node at 1.1.1.1:4002 [Candidate] entering Candidate state in term 3 2020-05-25T11:10:44.829-0700 [INFO] raft: Node at 1.1.1.1:4002 [Follower] entering Follower state (Leader: "") 2020/05/25 11:10:46 [DEBUG] raft-net: 1.1.1.1:4002 accepted connection from: 2.2.2.2:47180 [store] 2020/05/25 11:10:46 waiting for up to 2m0s for application of initial logs [http] 2020/05/25 11:10:46 service listening on 1.1.1.1:4001 2020/05/25 11:10:47 [DEBUG] raft-net: 1.1.1.1:4002 accepted connection from: 2.2.2.2:47182
on node2:
2020/05/25 11:10:44 [DEBUG] raft-net: 2.2.2.2:4002 accepted connection from: 1.1.1.1:46620 2020-05-25T11:10:44.829-0700 [WARN] raft: Rejecting vote request from 1.1.1.1:4002 since we have a leader: 2.2.2.2:4002 2020-05-25T11:10:46.479-0700 [INFO] raft: pipelining replication to peer {Voter 1 1.1.1.1:4002}
You don’t really have to worry about all the “rejecting vote request”, “suffrage”, etc. info – it’s just there for informational purposes. The point is that if you test your queries again, you’ll see that all three nodes are in perfect sync. rqlite2 is now the leader, but if it were to die, one of the other two nodes would immediately hold an election per the RAFT protocol and pick a new leader.
Checking Cluster Status
rqlite comes with some sqlite3 language extensions, one of which is the .status command, which prints out a lot of information. I’ve trimmed off some of it in the output below:
rqlite2.lowend.party:4001> .status build: branch: master build_time: 2020-05-13T13:00:25-0400 commit: 5f762f01ecf2ad74ea398e20258239fca89777e2 version: v5.3.0 http: addr: 2.2.2.2:4001 redirect: 3.3.3.3:4001 node: uptime: 4m15.059332819s start_time: 2020-05-25T11:19:45.205084102-07:00 leader: addr: 3.3.3.3:4002 node_id: 3 raft: latest_configuration: [{Suffrage:Voter ID:1 Address:1.1.1.1:4002} {Suffrage:Voter ID:2 Address:2.2.2.2:4002} {Suffrage:Voter ID:3 Address:3.3.3.3:4002}] num_peers: 2 dir: /usr/local/rqlite/db metadata: 1: api_addr: 1.1.1.1:4001 2: api_addr: 2.2.2.2:4001 3: api_addr: 3.3.3.3:4001 nodes: [map[addr:1.1.1.1:4002 id:1] map[addr:2.2.2.2:4002 id:2] map[addr:3.3.3.3:4002 id:3]]
Here you can see that this node (rqlite2) has two peers, and the entire cluster configuration (and lots of other data) is displayed.
Securing rqlite
By default, rqlite transmits unencrypted. We can fix that by putting HTTPS in place. We’re going to use self-signed certificates in this tutorial.
First, generate the certificate:
root@rqlite1:/usr/local/rqlite# openssl req -x509 -nodes -newkey rsa:4096 -keyout key.pem -out cert.pem -days 3650 Generating a RSA private key ................................................................++++ ....................................................................................................................................++++ writing new private key to 'key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]:US State or Province Name (full name) [Some-State]:Oregon Locality Name (eg, city) []:Portland Organization Name (eg, company) [Internet Widgits Pty Ltd]:raindog Organizational Unit Name (eg, section) []:raindog Common Name (e.g. server FQDN or YOUR name) []:raindog Email Address []:raindog308@raindog308.com
Now shut down all nodes and restart them with additional HTTPS parameters. Here is rqlite1’s startup, with the new parameters bolded:
./rqlited -node-id 1 -http-addr 1.1.1.1:4001 -raft-addr 1.1.1.1:4002 -http-no-verify -node-encrypt -node-cert /usr/local/rqlite/cert.pem -node-key /usr/local/rqlite/key.pem /usr/local/rqlite/db
Backing Up rqlite
rqlite can do hot backups via the .backup command:
root@rqlite3:/usr/local/rqlite# ./rqlite -H rqlite3.lowend.party Welcome to the rqlite CLI. Enter ".help" for usage hints. rqlite3.lowend.party:4001> .backup bak.sqlite3 backup file written successfully rqlite3.lowend.party:4001> EOF (CTRL+D) root@rqlite3:/usr/local/rqlite# ls -l bak.sqlite3 -rw-r--r-- 1 root root 8192 May 25 12:24 bak.sqlite3
This file is just a normal sqlite3 file:
root@rqlite1:/usr/local/rqlite# sqlite3 bak.sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE planets (from_sun integer not null primary key, name varchar(30)); sqlite>
From this I could create a dump file and then restore it:
root@rqlite3:/usr/local/rqlite# echo ".dump" | sqlite3 bak.sqlite3 > restore.dump
root@rqlite3:/usr/local/rqlite# cat restore.dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE planets (from_sun integer not null primary key, name varchar(30));
INSERT INTO planets VALUES(1,'Mercury');
INSERT INTO planets VALUES(2,'Venus');
INSERT INTO planets VALUES(3,'Earth');
INSERT INTO planets VALUES(4,'Mars');
COMMIT;
root@rqlite1:/usr/local/rqlite# ./rqlite -H rqlite3.lowend.party
Welcome to the rqlite CLI. Enter ".help" for usage hints.
rqlite3.lowend.party:4001> drop table planets;
1 row affected
rqlite3.lowend.party:4001> .restore restore.dump
last inserted ID: 4
rows affected: 1
database restored successfullyMore Info
There’s more to rqlite, including APIs and clients for many programming languages. Visit the Github project page for full details.
Related Posts:
I also enjoy using LEBs! I have an empire of little guys for VPNs, larger guys for apps, and big guys for databases and storage. When I'm not in front a screen I'm into German Shepherd dogs, quality knives, target shooting, theology, tabletop roleplaying games, and forest hiking.
I enjoy writing technical articles here on LowEndBox to help people get more out of their systems. You can find me on LowEndTalk @raindog308
Thanks for your good post on rqlite. The version downloaded in your post is rather old though, 5.12.1 is much better.