The style and format of the prompt in MySQL Shell can be customized to meet the desires of the person using it. We can configure the prompt to display different or trimmed-down information about our database connection and what mode we are using. This post will show how to add a visual cue to let you know when you are connected to a production database.

The Setup

Since you are reading about customizing MySQL Shell, you should already have it installed. If you don’t, download and install it before you continue.

Prompt Theme Files

The easiest way to customize the MySQL Shell prompt is to copy an existing prompt theme file. Several prompt files are included with MySQL Shell. These files are located in the following directories:

  • Windows – %programfiles%\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt\
  • MacOs – /usr/local/mysql-shell/share/mysqlsh/prompt/
  • Linux – /usr/share/mysqlsh/prompt/

The list of files in these folders will resemble:

README.prompt
prompt_16.json
prompt_256.json
prompt_256inv.json
prompt_256pl+aw.json
prompt_256pl.json
prompt_classic.json
prompt_dbl_256.json
prompt_dbl_256pl+aw.json
prompt_dbl_256pl.json
prompt_nocolor.json

For information on what the different files include, check out README.prompt.

We copy the prompt file we want to use as our base to the following locations:

  • Windows – %AppData%\Roaming\MySQL\mysqlsh\
  • MacOS & Linux – ~/.mysqlsh/

I will use the prompt_256pl.json for this demo because I like the Powerline font aesthetic. Here is what the default JSON looks like.

{
  "desc": "256/24bit color terminal theme with MySQL prefix, default schema, host:port, ssl, + indicator for X protocol, active mode. Requires Powerline patched font.",
  "classes": {
    "SQL": {
      "fg": "15",
      "bg": "166"
    },
    "JS": {
      "fg": "0",
      "bg": "221"
    },
    "Py": {
      "fg": "15",
      "bg": "25"
    },
    "schema": {
      "text": "%schema%"
    },
    "noschema": {
      "text": ""
    },
    "disconnected": {},
    "hostx": {
      "text": "%transport%+"
    },
    "hostc": {
      "text": "%transport%"
    },
    "SSLhostx": {
      "text": "%transport%+ "
    },
    "SSLhostc": {
      "text": "%transport% "
    },
    "ctrx": {
      "text": "",
      "bg": 38,
      "fg": 15
    },
    "ctrx.": {
      "text": " - ",
      "bg": 242,
      "fg": 15
    },
    "ctrx*": {
      "text": " ★ ",
      "bg": 38,
      "fg": 15
    },
    "ctrx^": {
      "text": " ☆ ",
      "bg": 38,
      "fg": 15
    },
    "ctrx*.": {
      "text": " ★ ",
      "bg": 38,
      "fg": 15
    },
    "ctrx^.": {
      "text": " ☆ ",
      "bg": 38,
      "fg": 15
    },
    "production": {
      "text": " PRODUCTION ",
      "bg": "red",
      "fg": "white"
    }
  },
  "variables": {
    "is_production": {
      "match": {
        "pattern": "*;%host%;*",
        "value": ";%env:PRODUCTION_SERVERS%;"
      },
      "if_true": "production",
      "if_false": ""
    },
    "target": {
      "match": {
        "pattern": "%socket%",
        "value": ""
      },
      "if_true": "%host%:%port%",
      "if_false": "localhost"
    },
    "transport": {
      "match": {
        "pattern": "%ssh_host%",
        "value": ""
      },
      "if_true": "%target%",
      "if_false": "%ssh_host% → %target%"
    }
  },
  "symbols": {
    "separator": "",
    "separator2": "",
    "ellipsis": "…"
  },
  "prompt": {
    "text": " ",
    "cont_text": "%linectx% ",
    "bg": "0"
  },
  "segments": [
    {
      "classes": [
        "disconnected%host%",
        "%is_production%"
      ]
    },
    {
      "text": " My",
      "bg": 254,
      "fg": 23
    },
    {
      "separator": "",
      "text": "SQL ",
      "bg": 254,
      "fg": 166
    },
    {
      "classes": [
        "disconnected%host%",
        "%ssl%host%session%"
      ],
      "shrink": "truncate_on_dot",
      "bg": 237,
      "fg": 15,
      "weight": 10,
      "padding": 1
    },
    {
      "classes": [
        "noschema%schema%",
        "schema"
      ],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding": 1
    },
    {
      "classes": [
        "%session%trx%trx%%autocommit%"
      ],
      "weight": -1
    },
    {
      "classes": [
        "%Mode%"
      ],
      "text": "%Mode%",
      "padding": 1
    }
  ]
}

If MySQL Shell is already open, we must restart it to add the theme. When we start MySQL Shell with this file as it is, we will see the following:

MySQL Shell default Look

Here is what it looks like when I connect to a MySQL instance, in this case, over SSH.

MySQL Shell look with connection

Prompt Background Color

When I used this prompt theme, I first noticed that the prompt’s background was a different color from the background of my terminal.

Mismatched colors of prompt and terminal background

This color difference makes me all twitchy. Since I am not using black as the background color for my terminal, I need to change the background color of the prompt. Yes, I could change the background of my terminal to black, but then you wouldn’t learn how to change the prompt background.

The block of JSON that manages the background color of the prompt (and should be around line 102) is:

  "prompt": {
    "text": " ",
    "cont_text": "%linectx% ",
    "bg": "0"
  }

The bg property controls the background color of the prompt. In this case, it is set to 0, which is black. I could do trial and error to find a color close to the background color, but if I did it that way, I would need to change the color again if I ever chose a different background color for my terminal. So, the easy way to address this color difference is to remove that setting. I changed the prompt block to:

"prompt": {
    "text": " ",
    "cont_text": "%linectx% "
  }

When I restart MySQL Shell, we can see that the background color for the prompt now matches the terminal background.

Prompt and terminal colors match

If I change my terminal theme, I will not need to change the prompt background again. Take a look below.

Prompt and terminal colors match

Update Prompt Text

The next thing that bothers me is that the host information is a bit long because it includes the SSH host and then the database host. I prefer shorter prompt text, so we are going to shorten this. The block of JSON we will modify to accomplish this is around line 94 in the transport variable definition.

"transport": {
      "match": {
        "pattern": "%ssh_host%",
        "value": ""
      },
      "if_true": "%target%",
      "if_false": "%ssh_host% → %target%"
    }

This block tells MySQL Shell that if the value of %ssh_host% is an empty string, set the value of the variable named transport to the value of the %target% variable—which will usually be the host and port separated by a colon (:).

If the value of %ssh_host% is any value other than an empty string, the value of transport is set to the value of %ssh_host%, followed by a character value for a right arrow, and ending with the value of the %target% variable.

For my purposes, the port number is not necessary. I am also not a fan of the extra spaces around the arrow. To fix this, I change the if_false line to the following:

"if_false": "%ssh_host%→%host%"

When I restart MySQL Shell and connect to the database again, the prompt looks like the image below. Note that the port is no longer shown, and the spaces around the arrow have been removed.

Shortened MySQL Shell prompt

Visual Cue For Production

I often have multiple database connections running at the same time. Because of this, I want to add a visual cue to let me know when I am connected to the production database. We already have a variable named is_production in our JSON. The definition for this variable is around line 72 and looks like the JSON below.

"is_production": {
      "match": {
        "pattern": "*;%host%;*",
        "value": ";%env:PRODUCTION_SERVERS%;"
      },
      "if_true": "production",
      "if_false": ""
    }

The default definition looks for an environment variable named PRODUCTION_SERVERS to see if the value matches that of the %host% variable. If it does, the value of is_production is set to production. If it does not match, that value of is_production is set to an empty string.

Instead of setting an environment variable, I will hardcode the values for this demo.

Also, because I am using SSH to connect to my production server, I will change the pattern to use the %ssh_host% variable. My changes are reflected in the JSON below.

"is_production": {
      "match": {
        "pattern": "%ssh_host%",
        "value": "gl-db-server"
      },
      "if_true": "production",
      "if_false": ""
    }

If the SSH host is gl-db-server, we should see a segment on the prompt indicating we are connected to a production server.

Production segment in MySQL Shell prompt

Change Class Values

This prompt is better, but the text color is supposed to be white. I am not sure why it is not, but I think it is because of accessibility, and the colors do not have enough contrast. To change the colors of the production label, we look at the production class that is around line 65.

"production": {
      "text": " PRODUCTION ",
      "bg": "red",
      "fg": "white"
    }

I will change the bg and fg colors to have more contrast. I will also change the value of text to PROD rather than PRODUCTION because we just regained some real estate on the prompt, and now we lost some of it again. My new block of JSON looks like this:

"production": {
    "text": " PROD ",
    "bg": 124,
    "fg": 15
}

I restart MySQL Shell and reconnect to see the changes.

High contrast production segment in MySQL Shell prompt

Moving a Segment

In our prompt, the parts that show different information are called ‘segments’. We can add, remove, and move segments in the theme. I want to move the PROD label toward the end of the prompt to catch my eye easier.

In our JSON file, the segments are defined with an array of JSON objects like the text below.

"segments": [
    {
      "classes": [
        "disconnected%host%",
        "%is_production%"
      ]
    },
    {
      "text": " My",
      "bg": 254,
      "fg": 23
    },
    {
      "separator": "",
      "text": "SQL ",
      "bg": 254,
      "fg": 166
    },
    {
      "classes": [
        "disconnected%host%",
        "%ssl%host%session%"
      ],
      "shrink": "truncate_on_dot",
      "bg": 237,
      "fg": 15,
      "weight": 10,
      "padding": 1
    },
    {
      "classes": [
        "noschema%schema%",
        "schema"
      ],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding": 1
    },
    {
      "classes": [
        "%session%trx%trx%%autocommit%"
      ],
      "weight": -1
    },
    {
      "classes": [
        "%Mode%"
      ],
      "text": "%Mode%",
      "padding": 1
    }
  ]

To move the production, we first remove "%is_production%" from the classes property in the first element of the segments array. This segment should now look like the following:

{
  "classes": [
    "disconnected%host%"
  ]
}

Next, we create a new JSON object before the last segment with an element in the classes array with a %Mode% value. We create a classes property as an array and add %is_production% as an element. The last three elements of segments should resemble the JSON below.

 {
  "classes": [
    "%session%trx%trx%%autocommit%"
  ],
  "weight": -1
},
{
  "classes": [
    "%is_production%"
  ]
},
{
  "classes": [
    "%Mode%"
  ],
  "text": "%Mode%",
  "padding": 1
}

When we restart MySQL Shell and reconnect, the PROD label is closer to the prompt’s end, making it more easily visible.

Properly positioned high contrast production segment in MySQL Shell prompt

 

Wrap Up

Customizing the prompt in MySQL Shell can allow us to show information we want and hide information we don’t want. We can also add visual cues to let us know when we are connected to production servers, and we can move the different segments so they fit our needs better. To learn more about customizing the prompt in MySQL Shell, check out the documentation and also take a look at the contents of the README.prompt file.