Install and Run the CockroachDB Client - Part 2
Introduction
This article is the second installment of an article series that shows how to install CockroachDB and set up a cluster in order to access its CockroachDB client interface. In the previous installment, we focused on the CockroachDB installation process. Now, we’ll pick up where we left off and review the basics of setting up a cluster, as well as accessing and using the CockroachDB CLI.
Start a CockroachDB cluster
Let’s begin our discussion with a look at the basic command used to start a CockroachDB cluster: cockroach start
. The start
command allows you to specify several options while starting a cluster. For example, the following command will start an insecure cluster that doesn’t use certificates:
1 | cockroach start --insecure |
NOTE: You can also initialize an insecure cluster with cockroach init --insecure
beforehand, but this isn’t necessary in most cases.
Start the CockroachDB cluster on macOS
If you’re running CockroachDB on macOS, you can use the brew services start
command to start the cluster:
1 | brew services start cockroachdb/tap/cockroach |
This command will use the launchd
service to run the cluster as a background daemon service whenever you restart your Mac. If you’d prefer to run the service in the terminal as a foreground service, use the cockroach start
command instead.
CockroachDB certificate directory location
To create a secure CockroachDB cluster, you’ll need to create certificates and set up a directory for them. The following command can be used to get more information on the cockroach cert
command:
1 | cockroach cert --help |
Create a certificate for CockroachDB
You’ll need to create a certificate if you want to secure your cluster. You can use the cockroach cert create-ca
command to accomplish this task. The first step is to create two directories on your machine using the mkdir
command: one for the certificates and one for the certificate keys.
1 | mkdir cockroachdb-certs-dir certs |
NOTE: You can name the certificate key directory whatever you’d like; however, you should name the certificate directory itself certs
.
1 2 3 | cockroach cert create-ca \ --certs-dir=certs \ --ca-key=cockroachdb-certs-dir/ca.key |
Next, use the create-client
command to create a certificate for a particular user:
1 2 3 4 | cockroach cert create-client \ macuser \ --certs-dir=certs \ --ca-key=cockroachdb-certs-dir/ca.key |
NOTE: Be sure to create a certificate for the root
user as well.
The command shown above will create a private RSA key for that user.
You can use the pwd
command in a UNIX terminal to get the full path for the certificate keys directory. Then, export that path to create a permanent entry for it in your system’s $PATH
variable. You can see an example of how to export the path below:
1 | export PATH="$PATH:/Users/macuser/Documents/certs" |
You can use the cert list
command to see the permanent certificates path for CockroachDB:
1 | cockroach cert list |
If this command doesn’t return information on a certificate file, try the following command instead:
1 | cockroach cert list --certs-dir=/Users/macuser/Documents/certs |
Once your certificate directory is set up, you can use the --certs-dir
option with the cockroach sql
command to access your cluster:
1 | cockroach sql --certs-dir=/Users/macuser/Documents/certs |
You may get an error that says Error: connections with user root must use a client certificate
— this just means you haven’t created a certificate for the root
user as well.
Start a secure CockroachDB cluster
The last step of the process is to create a node certificate for each node running on the cluster. You’ll need to use the create-node
command:
1 2 3 | cockroach cert create-node localhost \ --certs-dir=/Users/macuser/Documents/certs \ --ca-key=/Users/macuser/Documents/cockroachdb-certs-dir/ca.key |
After executing that command, you should be able to start the cluster using the node certificate stored in the certificate keys directory:
1 2 3 4 5 | cockroach start \ --certs-dir=/Users/macuser/Documents/certs \ --host=node1 \ --listen-addr=localhost:26257 \ --http-addr=localhost:8080 |
The cockroach start
command shown above should return something like the following:
1 2 3 4 5 6 7 8 9 10 11 12 | CockroachDB node starting at 2020-02-05 10:30:34.570684 +0000 UTC (took 0.6s) build: CCL v19.2.2 @ 2019/12/11 01:27:47 (go1.12.12) webui: https://localhost:8080 sql: postgresql://root@localhost:26257?sslcert=%2FUsers%2Fmacuser%2FDocuments%2Fcerts%2Fclient.root.crt&sslkey=%2FUsers%2Fmacuser%2FDocuments%2Fcerts%2Fclient.root.key&sslmode=verify-full&sslrootcert=%2FUsers%2Fmacuser%2FDocuments%2Fcerts%2Fca.crt RPC client flags: cockroach <client cmd> --host=localhost:26257 --certs-dir=/Users/macuser/Documents/certs logs: /Users/macuser/Downloads/cockroach-data/logs temp dir: /Users/macuser/Downloads/cockroach-data/cockroach-temp950761167 external I/O path: /Users/macuser/Downloads/cockroach-data/extern store[0]: path=/Users/macuser/Downloads/cockroach-data status: initialized new cluster clusterID: c7c15226-0efe-4902-99eb-f751b3a808b8 nodeID: 1 |
Access the CockroachDB CLI
Once your cluster is running, you can access the built-in CockroachDB CLI. This allows you to execute SQL statements, much like PostgreSQL or MySQL. You can use these statements to retrieve and modify CockroachDB data.
‘cockroach sql’ command
You can access the client interface by executing the cockroach sql
command in a terminal or command prompt window.
If you’re using a secure cluster and you get a certificate error that says “problem with CA certificate: not found”, just use the following command instead to connect to the CockroachDB CLI:
1 | cockroach sql --certs-dir=/Users/macuser/Documents/certs |
Once you’re inside the CockroachDB client interface, you can use the \l
command or the SHOW DATABASES;
SQL statement to list all of the databases stored on the cluster.
Let’s try to create a new database.
Create a database for CockroachDB
We’ll use the CREATE DATABASE
SQL keyword to create a new database for the cluster:
1 | CREATE DATABASE my_db; |
If the operation was successful, it should return a response of CREATE DATABASE
along with the time it took to execute the statement. Try the \l
command again to verify that the database was created.
Use Cockroachdb to connect to a database
If you’d like to connect to a specific database, just use the USE
SQL keyword followed by a database name. The SQL statement shown below will connect to the defaultdb
database:
1 | USE defaultdb; |
Tis statement should return a response that looks like the following:
1 2 | SET Time: 7.912ms |
Once you’re finished you can just type \q
to exit out of the CockroachDB client interface for your cluster.
Conclusion
Over the course of our two-part article series on using the built-in, SQL-based CockroachDB client interface, we’ve covered quite a bit of ground. After walking you through the CockroachDB installation process in the first article, we turned our focus to the setup and configuration process in this second article. In addition, we showed you how to access and use the CockroachDB client to execute SQL statements. With these step-by-step instructions, you’ll be able to get the client interface set up and working for your own CockroachDB installation.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started