how to execute mysql command using script shell

Question

I want to execute mysql command using shell scripting in centos i am writing command over can anyone please tell me how to write this script and i am taking output in file so everytime when i execute script it create new file .

SELECT TABLENAME, tablerow, round(data_length/1024/1024 ,2 ) as dl , round(index_length/1024/1024 ,2 ) as il , round(data_length/1024/1024 ,2 ) as dl , round((data_length + index_length)/1024/1024 ,2)  "size in mb" from Information schema , TABLES WHERE TABLE_SCHEMA=" Db_name" order by data_length desc limit 10 in to out file ' ' FIELDS TERMINATED BY ' | ' ENCLOSED BY "  " TERMINATED BY '\R\N '

possible duplicate of [How to execute a MySQL command from a shell script?](http://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script) — runDOSrun, Feb 15 '15 at 17:43
no its not duplicate i want to run command as well to change output file everytime when its executes — Shashank Chouksey, Feb 15 '15 at 17:47
One of the answers there tells you how to write the output to a file. — runDOSrun, Feb 15 '15 at 17:49
If you want a different file name you need some kind of counter or a time stamp. — qwerty_so, Feb 15 '15 at 18:06

score 0 · Answer 1 · answered Feb 15 '15 at 17:46

Try this way:

mysql -u username -pPassword <database> -e "SELECT TABLENAME, tablerow, round(data_length/1024/1024 ,2 ) as dl , round(index_length/1024/1024 ,2 ) as il , round(data_length/1024/1024 ,2 ) as dl , round((data_length + index_length)/1024/1024 ,2)  "size in mb" from Information schema , TABLES WHERE TABLE_SCHEMA=" Db_name" order by data_length desc limit 10 in to out file ' ' FIELDS TERMINATED BY ' | ' ENCLOSED BY "  " TERMINATED BY '\R\N ';"

score 0 · Answer 2 · answered Feb 15 '15 at 18:00

mysql -u root -p <Hit enter>
root             <Here root is password>

show databases;

See databases first and then select.

use database YourDatabase

Then enter your command like :

>SELECT TABLENAME, tablerow, round(data_length/1024/1024 ,2 ) as dl , round(index_length/1024/1024 ,2 ) as il , round(data_length/1024/1024 ,2 ) as dl , round((data_length + index_length)/1024/1024 ,2)  "size in mb" from Information schema , TABLES WHERE TABLE_SCHEMA=" Db_name" order by data_length desc limit 10 in to out file ' ' FIELDS TERMINATED BY ' | ' ENCLOSED BY "  " TERMINATED BY '\R\N '

how to execute mysql command using script shell

2 Answers2