Executing shell commands from within the MySQL command line clientExecuting shell commands from within the MySQL command line client

Posted April 11th, 2008 in MySql

The MySQL command line client allows you to quickly and easily run sql queries from a console session, as well as load sql script files etc. Once you have started up the MySQL command line client it is possible to execute shell commands from within the console and even drop out to another bash (or similar) session. This post looks at how to do this.

Starting up the MySQL command line client

On a Linux or BSD system the "mysql" command line client will usually be somewhere in your path, so all you should normally need to do to connect is this:

mysql -u [username] -p [database]

where you would substitute [username] with your username and [database] with the database you wish to connect to. The -p flag tells the command line client that you will enter a password before the client starts up.

Once you have successfully connected, you will be greeted with something similar to this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 206 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Executing shell commands from within the MySQL command line client

Now that you're started up the MySQL command line client you can execute SQL commands etc. If you want to execute a shell command, use the \! command followed by the shell command. For example, if you wanted to get a directory listing of the current working directory, you would do this:

\! ls -l

The output would then look something like this (obviously the directory listing will be different for you, and will vary depending on what the current working directory is):

mysql> \! ls -l
total 40
drwxr-xr-x 2 chris users 4096 Apr  5  2007 cache
drwxr-xr-x 2 chris users 4096 Apr  5  2007 config
drwxr-xr-x 2 chris users 4096 Apr  5  2007 controllers
drwxr-xr-x 2 chris users 4096 Apr  5  2007 errors
drwxr-xr-x 2 chris users 4096 Apr  5  2007 hooks
drwxr-xr-x 6 chris users 4096 Dec 20 16:38 htdocs
drwxr-xr-x 2 chris users 4096 Apr  5  2007 libraries
drwxrwxrwx 2 chris users 4096 Mar  4 22:27 logs
drwxr-xr-x 2 chris users 4096 Apr  5  2007 models
lrwxrwxrwx 1 chris users   10 Dec 30 16:43 toolbox -> ../toolbox
drwxr-xr-x 2 chris users 4096 Mar 17 13:16 views
mysql>

You should be able to run just about any command. You could, for example, start up a text editor such as nano or vi like this:

mysql> \! nano

or

mysql> \! vi

Dropping to a system shell

You can exit from the mysql command line client at any time using the \q command, but if you just wanted to do something quickly from the command line you can open up another system shell and then return to the MySQL client by exiting from the shell. This can save time rather than exiting MySQL because you don't need to log in again when you start the MySQL client up again.

For example, to drop to a bash shell, you would do this:

mysql> \! bash

This would then open up a bash shell. When you have finished with the system shell you would execute the normal command to exit it (e.g. "exit" or ctrl+D) and will then be returned to the MySQL command line client.

Summary

The MySQL command line client allows you to execute system shell commands which can be useful because it means you don't need to drop out from the client, run your command, and the start the MySQL client up again. You should be able to run just about any system command and can even drop out to a full system shell before returning back to the MySQL command line client again. I will look at some of the other MySQL text commands in later posts.

Related posts:

Comments

blog comments powered by Disqus