Execute Linux command from MySQL prompt

When we are working on MySQL running on Linux, We also need to execute some Linux command.  As we need to see the listing the file, clear the MySQL prompt screen. There is no command in MySQL to clear the screen.  In MySQL help you will find the command “\c”, it will show you to clear the current input. It will just cancel the last statement on MySQL Prompt.

Now there are two ways to execute Linux commands on MySQL prompt.

1.    Using the keyword “system”

2.    Using the shortcut “\!”

If you want to clear the MySQL screen running on Linux, execute below command on MySQL prompt

mysql> system clear

or

mysql> \! Clear

These both command will clear the MySQL console.

To execute any other Linux add these keywords before the commands.  See the below example.

mysql> system ls -lrt /home/mysql/cluster

total 12

drwxrwxr-x.  2 mysql mysql 4096 Apr 24 23:46 ndb_data

drwxrwxr-x.  2 mysql mysql 4096 May  4 22:21 config

drwxrwxr-x. 16 mysql mysql 4096 May 16 20:28 MySQL_Data

mysql>

mysql> \! ls -lrt /home/mysql/cluster

total 12

drwxrwxr-x.  2 mysql mysql 4096 Apr 24 23:46 ndb_data

drwxrwxr-x.  2 mysql mysql 4096 May  4 22:21 config

drwxrwxr-x. 16 mysql mysql 4096 May 16 20:28 MySQL_Data

mysql>

The above example is showing the list of file in folder.

You can also execute MySQL specific commands (Taking backup etc). See below example.

mysql> system mysqldump -uroot  sakila>/home/mysql/sakila.sql

mysql>

mysql> system ls -lrt /home/mysql/

total 3280

drwxr-xr-x. 13 mysql mysql    4096 Apr 20 03:21 mysql-cluster-gpl-7.2.5-linux2.6-i686

lrwxrwxrwx.  1 mysql mysql      37 Apr 20 22:18 mysqlc -> mysql-cluster-gpl-7.2.5-linux2.6-i686

drwxrwxr-x.  5 mysql mysql    4096 Apr 24 23:46 cluster

-rw-r--r--.  1 root  root  3350468 May 21 02:00 sakila.sql

mysql>

mysql> \! tail  /home/mysql/sakila.sql

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-05-21  2:00:47

For taking backup I have executed below command on MySQL Prompt.

 mysql> system mysqldump -uroot  sakila>/home/mysql/sakila.sql

Once the execution of this statement is complete, I see the listing of file using below command.

mysql> system ls -lrt /home/mysql/

total 3280

drwxr-xr-x. 13 mysql mysql    4096 Apr 20 03:21 mysql-cluster-gpl-7.2.5-linux2.6-i686

lrwxrwxrwx.  1 mysql mysql      37 Apr 20 22:18 mysqlc -> mysql-cluster-gpl-7.2.5-linux2.6-i686

drwxrwxr-x.  5 mysql mysql    4096 Apr 24 23:46 cluster

-rw-r--r--.  1 root  root  3350468 May 21 02:00 sakila.sql

To check the backup file content

mysql> \! tail  /home/mysql/sakila.sql

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-05-21  2:00:47

See the below image for detail