Shell Script to export MySQL table data into a text file

Hi, our today's topic is "Write Shell Script to export MySql table data into a text file". I will try my best to explain each and everything in steps. Let's start :

Following is full shell script

#tableexport.sh

 

#(1) define variables

DBNAME=test

FNAME=/usr/tmp/$(date +%Y.%m.%d)-$1.txt

#(2)creates an file with column names seperated by comma in a single line

mysql -u$2 -p$3 $DBNAME -B -e "SELECT COLUMN_NAME FROM information_schema.COLUMNS C WHERE table_name = '$1';" | awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' > $FNAME

#(3)appends newline to mark beginning of data vs. column titles

echo "" >> $FNAME

#(4)dumps data from DB into /usr/tmp/tempfile.txt

mysql -u$2 -p$3 $DBNAME -B -e "SELECT * INTO OUTFILE '/usr/tmp/tempfile.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' FROM $1;"

#(5)merges data file and $FNAME file column names

cat /usr/tmp/tempfile.txt >> $FNAME

#(6)deletes tempfile

rm -rf /usr/tmp/tempfile.txt

You will call the script using following nomenclature.

sh   tableexport.sh  table_name  db_username  db_password

I will be using a table named bookmarks of test database for explanation.

Let’s discuss each and every step now.

I will be using a table named bookmarks of test database for explanation.

Let’s discuss each and every step now.

1)    In first step we have defined two variables.
       

       DBNAME  : It represents the database name
       FNAME : It specifies the file name and it’s location that we have to populate
   
2)    Second step is the most important one. It would extract Column names from the table
       and would write them into a single line separated by commas. One more thing to pen
       down is that to run this script file we have to pass three arguments
   
        a) Table Name b) Database Username and c) Database Password

       So in this second step $1 represents the 1st argument means table name, $2 represents
       the 2nd argument means database name and $3 represents the 3rd argument means
       database password. 
   
       Let’s go into more detail. I will divide the whole command into parts and explain
       it further.
   
       [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
       FROM  information_schema.COLUMNS C WHERE table_name = 'bookmarks';"
   
        This command will fetch the column information of bookmarks table of test database
        and the output  would be
   
        //output starts here
       COLUMN_NAME
       bookmark_id
       bookmark_name
       bookmark_url
       created_date
       modified_date
       is_active
       //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';"
      | awk '{print $1}'
   
      Ask is an important unix utility. Here in out example it would read first word of the line
      and print it. As in out case whole column name is a single word so output would be same.
   
      //output starts here
      COLUMN_NAME
      bookmark_id
      bookmark_name
      bookmark_url
      created_date
      modified_date
      is_active
      //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';" |
      awk '{print $1}' | grep -iv   ^COLUMN_NAME$
   
      It would display all the columns except the COLUMN_NAME. So output would be
   
      //output starts here
      bookmark_id
      bookmark_name
      bookmark_url
      created_date
      modified_date
      is_active
      //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';" | 

      awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g'
   
      sed is again an important utility of unix.  In our example it would replace starting of the
      line (represented by ^) and end of the line (represented by $) by double quotes. Here
      g specifies the global substitute otherwise it would work only for first line.
     

      Ouput would be :
   
      //output starts here
      “bookmark_id”
      “bookmark_name”
      “bookmark_url”
      “created_date”
      “modified_date”
      “is_active”
      //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';" |
      awk '{print $1}' | grep -iv  ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' |
      sed 's/\(.*\),/\1./' > $FNAME
   
      Tr : Translate characters.  In this case it would replace ‘\n’ means new line character
      with comma.
   
      We have again used sed here. It would replace the last comma with space. So the final
      output of this step would be
   
      //output starts here
      “bookmark_id”,“bookmark_name”,“bookmark_url”,“created_date”,“modified_date”,
      “is_active”
      //output ends here
   
      It would be written to the $FNAME file.

3)   As the documentation says it would just put us in next line so that we can copy other
      data in $FNAME file’s next line. 

4)   Next step would just get the content from table, would separate them by comma and
      enclosed by double quotes and put them in a file /usr/tmp/tempfile.txt.

5)   5th step would append the tempfile.txt file data into $FNAME.

6)   Will delete temporary file : tempfile.txt.

Thanks!!!!!!!!!! Enjoy Programming :)