Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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.

share|improve this answer
    
+1 for the \! tip –  altmannmarcelo Apr 30 '13 at 11:07

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.

share|improve this answer
    
hah, that's handy. –  Derek Downey Apr 29 '13 at 20:53

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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