Tuesday, May 22, 2012

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


No comments: