While playing around with MySQL today I had an idea that generating various
mysql statements would actually be a good way to practice getting familiar with
vim. I remember back when I was getting started that I actually found
vim really daunting, almost so much so I nearly didn't get the habit off the ground, but then I found myself doing a ton of sysad-esque tasks and it was just so much easier to use once I got past the fear.
It's worth mentioning that there's a lot of new information for the beginning
vim user in this post. The idea is not for you to remember it all, it's to give you an arsenal of common commands that you can make use of by practicing getting a file from practically nothing to a more useful form.
To see if your computer already has
vim, please open Teminal / iTerm / your terminal app of choice and enter the following command:
This will create an empty file named
mysql-vim-playground if you have
vim installed. If you do not, and you see a command not recognized error, please install
vim with your package manager (
apt for most Linux distros and
brew for macOS).
If you are running a Windows machine, this exercise is for the Unix-based OS learner in you. If you are running Windows 10 you can try installing the Linux Subsystem for Windows. Alternatively, if you have access you can spin up a DigitalOcean droplet or Linode instance with your Linux distro of choice.
A quick note
Although you can copy and paste commands directly from this blog post, to help with your muscle memory I do recommend actually typing them out each time.
Put your right foot in...
Now that you have
vim opened, let's quickly get familiar with the basic-basics. There are two "modes" called "command mode" and "insert mode" (i.e. "edit mode").
vim will open in command mode by default, so to enter insert mode you simply hit the
i key. To return to command mode, hit the ESC key.
To get started, our users are Jayne Cobb, Inara Serra, George Parley, and Antimony Carver. You can copy and paste them, like so:
Jayne Cobb, Inara Serra, George Parley, and Antimony Carver
When you go to paste them in make sure you're in insert mode by hitting
i, otherwise you'll enter insert mode when
vim hits the
a character in
a is for append, which basically changes the cursor position when you enter insert mode.
Make some newlines
vim you can make substitutions with
:s. Please enter the following when in command mode:
We've just swapped the
, (that's a comma and a space) with a newline, which is signified by
\r (for "carriage return). The
g signifies a global change on that line, so every
, is replaced with a newline instead of only the first.
Although this was mostly successful, on our last line we have
and Antimony Carver. We want just the name, so in command mode navigate to the last line by hitting
d is the delete character, and w means "word". How
vim breaks up words can be a topic unto itself when discussing code lines, but in general when you are using "regular ol' written language" a word is what you'd expect.
Making usernames out of users
Now to do some next-level deletes. The usernames for these people are going to be their first names and last initials. We're going to delete the last word of every line except for the first character. The most brain-friendly way to do this is with a macro, unless you want to also make regex your friend on your first foray into
Making a macro will allow you to perform the action on the first line, then repeat it on other lines. To get started recording, when in command mode enter
@q. The first
q is to record, the second
q is the name of the register where it's being stored. What's a register? Think of it as "Save to Slot Q".
On the first line do the following commands:
- right arrow (to go over one character)
- left arrow
Now to stop recording hit
q (yes, again). What we just did was go from the start of line one, move forward a word, move forward a character, delete to the end of the word, move back a character, and delete the character under the cursor with
Now to repeat.
vim to start on line
2 and repeat until the last line of the file,
$. You'll encounter a lot of scenarios where
$ means "end". As a quick side exercise, hit
0 to go to the beginning of your current line and
$ to go to the end. Oscillate between the two a couple of times to get a feel for it.
norm @q plays the macro "stored in slot Q".
Herd some camels
At this juncture, there's just one more step to getting our usernames: removing the camel casing. Do this, we're going to make use of visual mode. Go to the beginning of line 1 and do the following:
Visual mode allows you to visually select a series of text and make changes on the whole selected text.
u makes all the selected characters lowercase and
U would make them all uppercase. If you did not want to use visual mode, you could alternatively navigate to each character and use
~ to flip its case.
Before we proceed
At this point you may have noticed a few things about
vim commands. That they usually come in individual letters or short words, like
norm, and that they are stackable. For example,
dw stacks to delete a word. You can also specify ranges on command lines, like
norm. Keep these properties in mind when you start branching out to learn new
vim commands, e.g.
e to hop words but landing on the last letter rather than the first.
We're going to create our users on a MySQL schema with multiple databases and going to grant a few different commands to give our users read only or read and write access to different databases.
Please note that since the primary goal of this exercise is to practice
vim with a real world example, that no MySQL databases have been set up. You're more than free to practice on your own, though, if you have some handy.
We're going to plan ahead and know that the databases in question are
gunnerkrigg so we'll need to create the users once and apply permissions to two different databases.
Do No Repeat Yourself ... well maybe this one time
Planning is important. Since we'll need to create users once and then add them to two databases, it'll help us if we copy out our usernames a few times. To yank a few lines of text, in command mode entery the following:
Navigate to the bottom of the file with
shift+G and without entering insert mode paste your copied items with
p. Go to the bottom with
shift+G and paste with
p again. It's important not to just paste twice with
pp (although you can) due to where your cursor falls: you'll have a couple of usernames not be in order any more.
To make the lines a little more readable, I've inserted newlines between each block of usernames. So my text file now looks like this:
jaynec inaras georgep antimonyc jaynec inaras georgep antimonyc jaynec inaras georgep antimonyc
The line numbers I'll use on subsequent steps will assume you have done the same.
Let's set up our creates on lines 1-4. Skipping over the finer points of MySQL, the command for creating a user looks like this:
create user 'USER'@'%' identified by 'SOOPERPASSWORD';
So let's do some swapping:
:1,4s/^/create user '/
$ means "end",
^ means "beginning". So here we're replacing the beginning of each line with
create user '. Now for the rest:
:1,4s/$/'@'%' identified by 'SOOPERPASSWORD';/
Now for the next block. The Firefly crew need RW access to their own ship's database, but maybe the students of Gunnerkrigg do not. They can have RO (read only) access though. The syntax for this, again skipping over MySQL learnings, is:
grant all privileges on <database>.<table> to 'USER'@'%'; grant select on <database>.<table> to 'USER'@'%';
For our file, let's enable line numberings with
:set nu. There, that'll make this next bit easier! Since in each case we're going to allow access to all tables in a database, instead of naming table
<table> we're going to use the wildcard character
:6,$s/^/grant all privileges on firefly.* to '/
And to finish the command:
So now your file should look like this:
create user 'jaynec'@'%' identified by 'SOOPERPASSWORD'; create user 'inaras'@'%' identified by 'SOOPERPASSWORD'; create user 'georgep'@'%' identified by 'SOOPERPASSWORD'; create user 'antimonyc'@'%' identified by 'SOOPERPASSWORD'; grant all privileges on firefly.* to 'jaynec'@'%'; grant all privileges on firefly.* to 'inaras'@'%'; grant all privileges on firefly.* to 'georgep'@'%'; grant all privileges on firefly.* to 'antimonyc'@'%'; grant all privileges on firefly.* to ''@'%'; grant all privileges on firefly.* to 'jaynec'@'%'; grant all privileges on firefly.* to 'inaras'@'%'; grant all privileges on firefly.* to 'georgep'@'%'; grant all privileges on firefly.* to 'antimonyc'@'%';
Ah, looks like we were a little overzealous with our
Always moving forward
Next step: delete the line where there was no username at all by navgiating to line
10 and entering
d$. Now, you recall that we don't want George and Antimony to have unchecked access to all of Firefly's database, so we'll fix that with:
Now to clean up the next batch for
And you may have guessed it, but:
Your final form
The final form of your file should look like this:
create user 'jaynec'@'%' identified by 'SOOPERPASSWORD'; create user 'inaras'@'%' identified by 'SOOPERPASSWORD'; create user 'georgep'@'%' identified by 'SOOPERPASSWORD'; create user 'antimonyc'@'%' identified by 'SOOPERPASSWORD'; grant all privileges on firefly.* to 'jaynec'@'%'; grant all privileges on firefly.* to 'inaras'@'%'; grant select on firefly.* to 'georgep'@'%'; grant select on firefly.* to 'antimonyc'@'%'; grant select on firefly.* to 'jaynec'@'%'; grant select on firefly.* to 'inaras'@'%'; grant all privileges on firefly.* to 'georgep'@'%'; grant all privileges on firefly.* to 'antimonyc'@'%';
Hardly recognizable from where we started, isn't it?
I hope this helps you ease into more
vim explorations 😁