Tag Archives: MySQL

Developer Toolbox

MySQL pager configuration

Leave a comment

Have you ever typed in a SQL query that returns thousands and thousands of results, and you had to Ctrl-C to stop it, which would then also terminate your MySQL console process? Annoying, isn’t it!

Well, there is actually a pager configuration for MySQL. My favorite pager is less, ’cause less is more, right? Paradoxically, it has more features than more, for in more you wouldn’t be able to scroll up, for example.

To make MySQL use less as the pager, you should add the following line to your my.cnf file, in the [client] block:

[client]
pager = "less -X -F"

Here is what the arguments do:

  • -X: prevent less from clearing the screen when exiting. This is useful when you want to have the results on screen after quitting less, to be able to compose your next SQL command
  • -F: tells less to quit immediately if the buffer fits on one screen. Without this, you’d have to quit less first before you can type the next SQL command, even though it fitted perfectly on one screen.
Developer Toolbox

MySQL configuration tips and tricks

Leave a comment

A web developer often has to access different MySQL databases for different projects. To quickly navigate through them, I like to use project-specific MySQL configuration snippets and aliases. My projects usually have some kind of a namespace, a 2-3 character code specific to that project. Just to give an example, let’s call my project Xylophone Ytterbium Zeta, and its namespace code is XYZ.

I would create a my.cnf file in my project’s directory with the following content:

host = localhost
user = xyzuser
password = xyzpassword
database = xyzdatabase

If I now run:

$ mysql --defaults-file=~/projects/xyz/my.cnf

I will be logged into the project’s database immediately, without having to enter my credentials.

However, the --defaults-file argument is quite a long argument to type in every time, so I would create a bash alias, so that all I need to do is:

$ myxyz

to get into the database. To create this bash alias, I’d simply add the following line to my ~/.bash_aliases file:

alias myxyz="mysql --defaults-file=~/projects/xyz/my.cnf"

Of course your .bash_rc file needs to know about this file, so check if you have the following snippet in there:

if [ -f ~/.bash_aliases ]; then
    . ~/.bash_aliases
fi

The latest Ubuntu versions should have that already prepared for you.