MySQL:- Select / INTO File

Background

Wanting to extract data out of MySQL.

Chose to use select along with “into outfile“.

 

Outline

  1. Preparation
    • Configuration
      • Review
  2. Process
    • SQL
  3. Review Data
    • Tools
      • cat

Tasks

Preparation

Configuration

Configuration – Review

Let us review the pertinent MySQL Engine configuration.

Outline

  1. System Configuration
    • secure_file_priv
      • Folders where File I/O can occur

Tasks

SQL
SQL – Syntax

SHOW VARIABLES LIKE "[variable]"

SQL – Sample

SHOW VARIABLES LIKE "secure_file_priv"

Output – Text

/var/lib/mysql-files/

Output – Grid

Explanation
  1. Here the folders designated for File I/O
    • /var/lib/mysql-files/

Process

Outline

  1. SQL

SQL

Syntax


select *

from   table

into   outfile [file]

Sample


select *

from   sakila.customer sc 

into   outfile '/var/lib/mysql-files/db.mysql.sakila.customer.20201021.0851AM.txt'

FIELDS TERMINATED BY ',' 

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n' 

Explanation

  1. Get Data
    • File Targeted:- /var/lib/mysql-files/db.mysql.sakila.customer.20201021.0851AM.txt
    • Field Termination:- Comma (,)
    • Row Termination:- Newline ( LF )
    • Literal Enclosed:- Double Quotes ( “)

 

Review Data

Outline

  1. Tools
    • cat

Tools

cat

Outline
  1. Formatting Characters ( Link )
    • -T to display TAB characters as ^I
    • -E to display $ at end of each line
    • -A to show up all the invisible characters:
Command – Syntax

cat [filename]

Command – Sample -01

sudo cat /var/lib/mysql-files/db.mysql.sakila.customer.20201021.0851AM.txt

Output – Image

Output – Text

158,1,"VERONICA","STONE","VERONICA.STONE@sakilacustomer.org",162,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
159,1,"JILL","HAWKINS","JILL.HAWKINS@sakilacustomer.org",163,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
160,2,"ERIN","DUNN","ERIN.DUNN@sakilacustomer.org",164,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
161,1,"GERALDINE","PERKINS","GERALDINE.PERKINS@sakilacustomer.org",165,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
162,2,"LAUREN","HUDSON","LAUREN.HUDSON@sakilacustomer.org",166,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
163,1,"CATHY","SPENCER","CATHY.SPENCER@sakilacustomer.org",167,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
164,2,"JOANN","GARDNER","JOANN.GARDNER@sakilacustomer.org",168,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
165,2,"LORRAINE","STEPHENS","LORRAINE.STEPHENS@sakilacustomer.org",169,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
166,1,"LYNN","PAYNE","LYNN.PAYNE@sakilacustomer.org",170,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
167,2,"SALLY","PIERCE","SALLY.PIERCE@sakilacustomer.org",171,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
168,1,"REGINA","BERRY","REGINA.BERRY@sakilacustomer.org",172,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
169,2,"ERICA","MATTHEWS","ERICA.MATTHEWS@sakilacustomer.org",173,0,"2006-02-14 22:04:36","2006-02-15 04:57:20"
170,1,"BEATRICE","ARNOLD","BEATRICE.ARNOLD@sakilacustomer.org",174,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
171,2,"DOLORES","WAGNER","DOLORES.WAGNER@sakilacustomer.org",175,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
172,1,"BERNICE","WILLIS","BERNICE.WILLIS@sakilacustomer.org",176,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
173,1,"AUDREY","RAY","AUDREY.RAY@sakilacustomer.org",177,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
174,2,"YVONNE","WATKINS","YVONNE.WATKINS@sakilacustomer.org",178,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
175,1,"ANNETTE","OLSON","ANNETTE.OLSON@sakilacustomer.org",179,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
176,1,"JUNE","CARROLL","JUNE.CARROLL@sakilacustomer.org",180,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
177,2,"SAMANTHA","DUNCAN","SAMANTHA.DUNCAN@sakilacustomer.org",181,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
178,2,"MARION","SNYDER","MARION.SNYDER@sakilacustomer.org",182,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"

Command – Sample – Show Special Characters
Outline
  1. Issue cat
    • Pass in -A to show all special characters
SQL

sudo cat /var/lib/mysql-files/db.mysql.sakila.customer.20201021.0851AM.txt -A
Output
Output – Image

Output – Text

158,1,"VERONICA","STONE","VERONICA.STONE@sakilacustomer.org",162,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
159,1,"JILL","HAWKINS","JILL.HAWKINS@sakilacustomer.org",163,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
160,2,"ERIN","DUNN","ERIN.DUNN@sakilacustomer.org",164,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
161,1,"GERALDINE","PERKINS","GERALDINE.PERKINS@sakilacustomer.org",165,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
162,2,"LAUREN","HUDSON","LAUREN.HUDSON@sakilacustomer.org",166,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
163,1,"CATHY","SPENCER","CATHY.SPENCER@sakilacustomer.org",167,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
164,2,"JOANN","GARDNER","JOANN.GARDNER@sakilacustomer.org",168,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
165,2,"LORRAINE","STEPHENS","LORRAINE.STEPHENS@sakilacustomer.org",169,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
166,1,"LYNN","PAYNE","LYNN.PAYNE@sakilacustomer.org",170,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
167,2,"SALLY","PIERCE","SALLY.PIERCE@sakilacustomer.org",171,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
168,1,"REGINA","BERRY","REGINA.BERRY@sakilacustomer.org",172,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
169,2,"ERICA","MATTHEWS","ERICA.MATTHEWS@sakilacustomer.org",173,0,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
170,1,"BEATRICE","ARNOLD","BEATRICE.ARNOLD@sakilacustomer.org",174,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
171,2,"DOLORES","WAGNER","DOLORES.WAGNER@sakilacustomer.org",175,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
172,1,"BERNICE","WILLIS","BERNICE.WILLIS@sakilacustomer.org",176,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
173,1,"AUDREY","RAY","AUDREY.RAY@sakilacustomer.org",177,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
174,2,"YVONNE","WATKINS","YVONNE.WATKINS@sakilacustomer.org",178,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
175,1,"ANNETTE","OLSON","ANNETTE.OLSON@sakilacustomer.org",179,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
176,1,"JUNE","CARROLL","JUNE.CARROLL@sakilacustomer.org",180,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
177,2,"SAMANTHA","DUNCAN","SAMANTHA.DUNCAN@sakilacustomer.org",181,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
178,2,"MARION","SNYDER","MARION.SNYDER@sakilacustomer.org",182,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
179,1,"DANA","HART","DANA.HART@sakilacustomer.org",183,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$
180,2,"STACY","CUNNINGHAM","STACY.CUNNINGHAM@sakilacustomer.org",184,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"$


References

  1. Linuxaria
    • Linux Terminal: Seeing the unseen characters with cat!
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s