Mysql: Client Tips 2: Running Shell Commands from the Commandline

Ok, As you know I am trying to explore more of the mysql command line and exploring its features. I have already written about how to change default mysql prompt using the command "prompt" in one of my previous articles.

Continuing this series. I am focusing today on the "system" command or "/!".

Well, I always have some of the sql source files and I execute it from the mysql command line. But I just keep forgetting their names. And most of the times I either close the mysql session and do 'ls' at the command line or move the mysql session in background and do a 'ls'.

Mysql cli has this very useful command called "system" using which you can execute shell commands from mysql client itself.

just type

  system ls -l

or

  \! ls -l

Some of us, who are familiar with Vim, will like this "\!" version a lot, because we are already using ! to run system commands there.

You can run almost all system commands using either of system or '\!'

Some examples:

mysql:root@test [14:26:19 pm]$ \! pwd
/home/owner

mysql:root@test [14:29:33 pm]$ \! date
Thu Mar 26 14:29:46 SGT 2009

mysql:root@test [14:29:46 pm]$ \! bash
owner@rocky-desktop:~$ exit
mysql:root@test [14:30:12 pm]$

You see the last one is quiet interesting. You can actually open a shell using this command perform some tasks. Just exit from the shell and you are back in the mysql world.

Liked it ! Start playing with mysql client system command.

Mysql: Comments Trick

Nice article by Eli White. Now you can understand easily all those comments that you see in the mysqldump output.


MySQL Trick with Comments

MySQL has a custom extension (if you want to call it that) to it’s commenting system, which actually allows you to make conditional commands. The mysqldump program that many people use to back up their databases uses this concept heavily, which is where I first ran into it.

In it’s simplest form, this allows you to place commands in a SQL file that only MySQL will run:

/*! MySQL specific code */

This allows you to make a single SQL file, that not only will execute correctly (and in detail) on MySQL, but it will also work on any ANSI compatible database, since you’ve hidden any of the non-ANSI parts in MySQL conditional comments. So for example, MySQL allows you to set a variable declaring that all names will be a certain character set, such as UTF-8, and therefore you can do:

/*! SET NAMES utf8 */;