Vim - Mysql

Using vim as a MySQL IDE

While this example focuses on MySql this same method has worked for all databases I’ve tried.

I like doing as much as possible from the command line. I find the flexibility and speed outweighs any benefit from a GUI. This includes database access and tooling. While this does make accessing the database a lot quicker it require some setup with a few gotchas.

1. ssh bind ports must be different if you have multiple databases to manage or else you may end up connecting to the same database if ssh control files are enabled. In the examples below this is the number 3308
2. my.cnf naming convention must be honored so directory structure should change if you have multiple databases to mange ~/<database name>/my.cnf

[mysql]
user = <your user>
password = <your password>
host = 127.0.0.1
database = <your database>
port = 3308

Im assuming you need to manage a protected database. One with a jump server or some other authentication mechanism. Maybe its a mysql server on the staging box or some other server with an ssh hop involved.

Create ~/bin/mysql-web and make it executable. It must be an executable or vim will not see it. Sadly, alias’s wont work.

#!/bin/bash
MYSQL_HOME=$HOME/<database name>/my.cnf
ssh -nNL 3308:<database host name>:8806 <ssh host name> && mysql

At this point, you should be able to access the database and issue queries from the MySQL repl simply by typing mysql-web.

With this setup in place you have a lot of flexibility in accessing your data store. For example, you can use vim as a poor mans Sql or pull business KPI’s from a file checked into Github.

mysql-web < key_metrics.sql

Or quickly query the database with a multi line EOF bash command

mysql-web << EOF >
select * from users LIMIT 10;
EOF

You can also write an SQL file in vim and save the output to the current buffer for editing.

# Read the query to test
:!mysql-web < %
# Write the query to the current buffer
:r !mysql-web < %