6

What can I do with the MySQL client's system command? Why does it exist?

The most productive use I've seen is to look around the file system (e.g., to remember the file name you want to SOURCE or LOAD FILE).

mysql> system ls /tmp
backup.sql 
mysql> source /tmp/backup.sql
Query OK, 1 row affected (0.02 sec)

mysql>

As near as I can tell, you can't pass a query result to the STDIN of a command, you can't pass STDOUT of a command into a MySQL query.. it just doesn't seem widely useful.

8

There is actually a far more useful application for the SYSTEM command than simply executing ls to see the current directory contents:

As mentioned in the docs (and elaborated on by a comment), this is way to make backups:

echo "FLUSH TABLES WITH READ LOCK; SYSTEM snapshot.sh; UNLOCK TABLES;" | mysql

snapshot.sh contains code to make an atomic snapshot of the mysql tables, using LVM, ZFS or btrfs capabilites. You can't split this up into multiple commands from the shell script, because as soon as the mysql session is closed, the table lock is released.

6

I use it to check system stats without leaving the comfort of the mysql prompt. Very useful when debugging performance issues not to have to keep a separate window open, or logging in and out of mysql prompt:

mysql> \! top
mysql> \! free -m
mysql> \! iostat -xd 3 6

Note that \! is an alias for 'system' in the mysql command line.

6

You can exit from the mysql command line client at any time using the \q command. However, if you just wanted to do something quick 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. Otherwise, you would be terminating a DB Connection and closing any table you created using CREATE TEMPORARY TABLE.

You also have the luxury of executing a whole bash shell and staying in the shell until you decide to type exit and hit enter.

mysql> \! bash
[root@**** ~]# exit
exit
mysql>

Of course, don't stay out in that shell longer than the number of seconds listed in wait_timeout or interactive_timeout.

1

You can use system to create a script and then source the script to get the result back into mysql. For example:

mysql> system date +"set @d='%Y-%m-%d';" > t.t

mysql> source t.sql

mysql> select @d; -- d will contain the date or any other thing(s) you want to send back.

Error checking and handling is difficult so i wouldn't advise this but you can do it in a pinch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.