Monthly Archives: August 2015

Export MySQL query results to CSV from CLI

This post is mostly for me to be able to quickly look up the way to export MySQL query results to CSV from command line 🙂

Assuming there’s a users table with name and email as the columns, and you can get all users using this command:

You can export this data from the command line to a csv file like below:

The command run above generates a “tab separated file” or “tsv”

In case you want an Excel compatible csv, run this command:

The above query will produce a file with rows like these:

Couple of nits:
1) MySQL should have write permissions to the directory in which you’re trying to write the file.
2) There shouldn’t already be a file with the same name – which is a bit of a bummer when you’re iterating over queries and are trying to create the right version of the file.

Automating FTP transfers on Linux

I was recently working on moving a lot of files between servers, but was hugely constrained by the fact that the servers had only FTP to work with. Even though I understand that it’s not secure AT ALL, there was nothing I could do but work on automating FTP transfers on linux. For FWIW, if you have the choice, DO NOT use FTP to transfer files between servers. You can use tools like rSync which make this process easy. Now that the disclaimers are out of the way, let’s dive in.

Any FTP client basically operates by issuing a bunch of commands to the remote servers in a sequence. The basic idea is like this:

FTP CLIENT> Open connection to server
FTP CLIENT> This is my username and password (notice how it’s not encrypted in any way, and thus is open to getting sniffed by any one monitoring the traffic between your machine and the destination machine, which is why it’s insecure)
FTP CLIENT> Move to directory of interest
FTP CLIENT> Issue a “get” command to download files to your local computer
FTP CLIENT> Finally say “KTHXBAI”. 🙂

Let’s look at a file that issues these commands and assume it’s saved as ftp_commands.txt

Now let’s use the ftp command and actually run it:

The -n modifier is the one that does the trick here. It actually prevents ftp from attempting to automatically login upon issuing the “open” command. In this mode, ftp commands expects the user name and password, which we feed to it right after the open command and we’re able to login to the server.

Get more productive with SSH using tmux

I’m sure all of us who’ve used SSH to manage a server have faced these issues at some point:

  1. Process that’s running gets killed if the internet gets disconnected
  2. Multi-tasking on the server (like reading log files and debugging some code at the same time) is a pain
  3. No way to save and retrieve work.

Enter tmux. Tmux is a terminal multiplexer. What’s that? In simple words, it lets you have multiple tabs open on a server. You can move around tabs, long running processes don’t get killed and what’s more, you can pick up just where you left off the last time.

Tmux is very memory efficient and it will run as long as the server doesn’t reboot. Tmux is also useful for pair programming, debugging something with your buddy, etc. Multiple people can join the same tmux and see what’s happening in the server.

I am very fond of tmux and use it on all the servers that I manage. In fact, it’s the first thing I configure on any server.

tmux session

Tmux is very useful when you have to manage remote servers

Tmux is also very configurable and so you can change it the way you want it. If you spend a lot of time on SSH managing servers, I would recommend you to go ahead and spend time learning tmux and how to use it. You can thank me later! 🙂

Here’s my tmux.conf:

It comes with these shortcuts:

Create new window: alt + n
Move between windows: shift + left arrow (to move left) or shift + right arrow (to move right)
Kill a window: ctrl + d
Rename a window: alt + r
Move a window: shift + up arrow (to move right) or shift + down arrow (to move left)
Detach from tmux: ctrl + b (press and let go) then d

I also add some aliases to my .bashrc so it’s easy to deal with tmux

Once you’ve added the above to the .bashrc file and sourced it, you can use tmux really well

Opening a new tmux session: tm
Listing all the tmux sessions: tl
Joining an existing tmux session: ta

Hope this helps!