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
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.
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.