« Return to Databases

Getting Started

After submitting a request for a MySQL database, a username and password be created for you, this information along with your endpoint name will be sent to you via our ticketing system. Store this information somewhere secure, and do not share this information with others.

User: <your-db-username>
Pass: <your-db-password>
Host: <mysql-shared-endpoint-name>
Port: 3306

The MySQL service is backed by a pair of servers in HA replication mode. One serves as the primary for READS and WRITES, and the read-replica can be used for READ queries only. These endpoints are available only within the HPC networks and cannot be accessed from elsewhere in the UVA WAN. You cannot use MySQL tools remotely (from University offices, labs, or home offices over VPN).

To use MySQL from the command-line, use the mysqlclient module in Rivanna:

$  module load mysqlclient

Or use the appropriate library for the language you are coding in to establish a connected client.

You can now create a connection to the server. Use port 3306 (the default port):

$  mysql -h <mysql-endpoint-name> -u <your-username> -p
Password: ***********

Basic Operations

As a dictionary, Redis allows you to set and retrieve pairs of keys and values. Think of a “key” as a unique identifier (string, integer, etc.) and a “value” as whatever data you want to associate with that key. Values can be strings, integers, floats, booleans, binary, lists, arrays, dates, and more.

KEY                 VALUE
--------------------------------------------------------
hello               world
1234                5678
1a2b3c              /path/to/file.csv
124a                AGCCCCTCAGGAGTCCGGCCACATGGAAACTCC

To view all keys (once you have established a connection to the Redis server):

redis.uvarc.io:6379> keys *
1) "hello"
2) "1234"
3) "1a2b3c"
4) "124a"

Then use a specific key to fetch its value:

redis.uvarc.io:6379> get hello
"world"

To set a new key/value:

redis.uvarc.io:6379> set herman melville
OK

Set an expiring key/value (EX in seconds, PX in miliseconds)

redis.uvarc.io:6379> set jane eyre EX 30
OK

Delete a key/value:

redis.uvarc.io:6379> del herman
OK

Working Alongside Other Users

Redis allows for the creation and management of multiple databases, called “indexes”. By default new connections are attached to index 0 but this can be changed to the integer of another index. Keys/values stored in one index are unavailable to another index. Use select to move between indexes.

redis.uvarc.io:6379> select 0
OK
redis.uvarc.io:6379> set hello world
OK
redis.uvarc.io:6379> get hello
"world"
redis.uvarc.io:6379> select 1
OK
redis.uvarc.io:6379[1]> get hello
(nil)

Indexes need not be created in order. We suggest you select a high arbitrary number for a private index. Populate and empty it as you find necessary. However, in the standard security environment remember that your keys/values are visible to other Rivanna users.

To connect to the Redis endpoint and specify an index other than 0, use the -n flag with the integer of the index. The redis-cli prompt will indicate when you are using a non-zero index:

$ redis-cli -h redis.uvarc.io -n 17
redis.uvarc.io:6379[17]> 

Advanced Operations

• Data Types & Structures
• Values are not constrained
• Lists
• Sets
• Incremental Counters
• Command Repetition
• Random Keys

Data Types & Structures

In addition to strings and integers, Redis supports the following data types and data manipulations:

• Lists
• Sets
• Hashes
• Increments
• Command repetition
• Random Keys
• Sorted sets
• Secondary indexes
• Scripts

Values are not constrained

Remember that the “value” half of a key/value pair does not have to contain only a single value. It can essentially be populated with multiple, separated values, so long as you can anticipate the order, and identity of those values. In this way a key/value is akin to a “row” of a comma-separated data file.

To implement this functionality, you have two options:

1. Use a hash. Hashes in Redis store multiple objects within the same key, i.e. sets of key/value pairs within a single key/value pair. Hashes are named, then subkeys and their values are defined:

redis.uvarc.io:6379> hset hash-key subkey1 value1 subkey2 value2
OK

Then fetch all values:

redis.uvarc.io:6379> hgetall hash-key
1) "subkey1"
2) "value1"
3) "subkey2"
4) "value2"

Fetch a specific field:

redis.uvarc.io:6379> hget hash-key subkey2
"value2"

2. Store your payload as JSON. Redis will store your JSON data as one long string, which you can then parse:

redis-cli -h redis.uvarc.io --raw
redis.uvarc.io:6379> set json_key '{"eventType": "purchase", "amount": 5, "item_id": "XXX"}' 
OK
redis.uvarc.io:6379> keys *
"json_key"
redis.uvarc.io:6379[5]> get json_key
{"eventType": "purchase", "amount": 5, "item_id": "XXX"}

Note the use of the --raw`` flag when invoking the redis-cli` tool. This ensures that response data is decoded back to UTF-8 instead of bytes.

Lists

Create a list by pushing a value into it:

redis.uvarc.io:6379> LPUSH dbs redis 
(integer) 1 
redis.uvarc.io:6379> LPUSH dbs mongodb 
(integer) 2 
redis.uvarc.io:6379> LPUSH dbs mysql 
(integer) 3 
redis.uvarc.io:6379> LPUSH dbs mysql 
(integer) 4

Pushing a new value into a list gives the new value the 0 position of the list. (To add new values to the end of the list use the RPUSH command.) List values can be duplicated within the list.

Get a list range back by defining the min and max indices you want:

redis.uvarc.io:6379> LRANGE dbs 0 10
1) "mysql" 
2) "mysql"
3) "mongodb" 
4) "redis"
redis.uvarc.io:6379> LRANGE dbs 0 1
1) "mysql"
2) "mysql"

You can also LPOP, LPUSH, and LTRIM as well as RPOP, RPUSH, and RTRIM with Redis lists.

Sets

You can populate a set within a single key. Set members already present cannot be duplicated within the set:

redis.uvarc.io:6379> sadd set1 bananas
(integer) 1
redis.uvarc.io:6379> sadd set1 apples
(integer) 1
redis.uvarc.io:6379> sadd set1 grapes
(integer) 1
redis.uvarc.io:6379> sadd set1 bananas
(integer) 0

Then retrieve the set members:

redis.uvarc.io:6379> smembers set1
1) "grapes"
2) "apples"
3) "bananas"

Incremental Counters

Use Redis as a counter or tracker:

redis.uvarc.io:6379> set counter 1
OK
redis.uvarc.io:6379> incr counter
(integer) 2
redis.uvarc.io:6379> incr counter
(integer) 3

Increment by integers other than 1:

redis.uvarc.io:6379> get counter
1
redis.uvarc.io:6379> incrby counter 3
(integer) 4
redis.uvarc.io:6379> incrby counter 6
(integer) 10

Command Repetition

If you need the same command to be repeated N times, simply preface your command with that integer:

redis.uvarc.io:6379> set counter 1
OK
redis.uvarc.io:6379> 5 incr counter
(integer) 2
(integer) 3
(integer) 4
(integer) 5
(integer) 6

Random Keys

Using a database populated with keys and values, some workflows could make use of this as a queue for jobs or batches to be processed. Your process could retrieve a random one:

redis.uvarc.io:6379> randomkey
"herman"
redis.uvarc.io:6379> randomkey
"hello"

Working with redis in Code

Redis has many available SDKs for most modern languages. Every operation available via the cli is available in those SDKs.

Some popular choices:

• Python
• C++
• R
• MATLAB
• Perl
• Go
• Others

Using redis in Your Research

We are frequently asked by researchers how to incorporate databases into their work. Here are four suggestions for how Redis might help your research::

1. Queue - Have a list of files or batches that need processing? Redis can hold the queue and let jobs retrieve single values at a time until they work the queue down to empty. Redis can even be used as a simple Pub/Sub message broker.
2. Cache - Store interim results or data for use in later computation. This could be a faster and more scalable replacement for temporary text files.
3. Dictionary or Lookup - Use an extended key/value store as an in-memory lookup resource for reference values. Where you may have previously stored reference values in a text file or relational DB table, Redis would likely outperform that pattern. Transactions with Redis are also atomic, which means multiple keys can be set, retrieved, or modified at the same time without risking data concurrency.

Other Resources

• Redis Documentation
• Try Redis Online
• Redis Cheatsheet