datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server sunday,...
TRANSCRIPT
![Page 1: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/1.jpg)
<Insert Picture Here>
Shooting from the hip - MySQL at the command lineGiuseppe MaxiaMySQL Community Team Lead
Sunday, 11 April 2010
![Page 2: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/2.jpg)
Shooting from the hip.MySQL at the command line
Giuseppe MaxiaMySQL Community Team Lead
twitter: @datacharmer
$ mysql
Sunday, 11 April 2010
![Page 3: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/3.jpg)
about me -Giuseppe Maxia a.k.a. The Data Charmer MySQL Community Team Lead Long time hacking with MySQL features Formerly, database consultant, designer, coder. A passion for QA An even greater passion for open source ... and community Passionate blogger http://datacharmer.blogspot.com
Sunday, 11 April 2010
![Page 4: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/4.jpg)
Updated presentation slides
you will find an up to date copy of these slides at: http://tinyurl.com/cli-tut-2010
Sunday, 11 April 2010
![Page 5: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/5.jpg)
We love your feedback! Tell it to the Twitter community
#mysqlconf "your feedback here" Twitter me
@datacharmer "your feedback here" Blog about it (if you already have a blog) Find my boss in the corridors and tell him I was
great!
Sunday, 11 April 2010
![Page 6: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/6.jpg)
Feedback examples Tell it to the Twitter community
Tutorial on command line is fabulous!
Sunday, 11 April 2010
![Page 7: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/7.jpg)
Feedback examples Tell it to the Twitter community
Tutorial on command line sucks big time!
Sunday, 11 April 2010
![Page 8: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/8.jpg)
Feedback examples Twitter me
Sunday, 11 April 2010
![Page 9: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/9.jpg)
Feedback examples Twitter me
Sunday, 11 April 2010
![Page 10: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/10.jpg)
Feedback examples Twitter me
Sunday, 11 April 2010
![Page 11: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/11.jpg)
Feedback examples Find my line manager:
Kaj Arnö Tell him how good you
feel about this tutorial
Sunday, 11 April 2010
![Page 12: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/12.jpg)
THERE ARE 10 TYPES OF PEOPLE ...
Sunday, 11 April 2010
![Page 13: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/13.jpg)
THERE ARE 10 TYPES OF PEOPLE ...Those who understand binary numbers, and those who don't.
Sunday, 11 April 2010
![Page 14: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/14.jpg)
THERE ARE 10 TYPES OF PEOPLE ...Those who understand binary numbers, and those who don't.But that's another story.
Sunday, 11 April 2010
![Page 15: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/15.jpg)
What this tutorial is about Making GUI enthusiasts green with envy; Providing tools and tips for command line users; Learn a few tricks to make you achieve more and
type less.
Sunday, 11 April 2010
![Page 16: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/16.jpg)
the command line
WHY14
Sunday, 11 April 2010
![Page 17: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/17.jpg)
Why the command line• portability• efficiency• fun
15
Sunday, 11 April 2010
![Page 18: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/18.jpg)
the command line
NO PANIC
16
Sunday, 11 April 2010
![Page 19: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/19.jpg)
Command line panic
$_Sunday, 11 April 2010
![Page 20: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/20.jpg)
Command line panic
$_NOW WHAT?
Sunday, 11 April 2010
![Page 21: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/21.jpg)
the command line
BASICS18
Sunday, 11 April 2010
![Page 22: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/22.jpg)
The shell• It's a command interpreter• There are many flavors
> bash > zsh> ksh>
19
Sunday, 11 April 2010
![Page 23: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/23.jpg)
MySQL command line client• called "mysql"
> Takes SQL commands> sends them to the server> gets results
• but it can do much more ...
20
Sunday, 11 April 2010
![Page 24: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/24.jpg)
other tools• Shell scripts• Aliases• Perl• awk• sort• …. and a lot of Unix little helper
21
Sunday, 11 April 2010
![Page 25: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/25.jpg)
in case it wasn't clear
22
Sunday, 11 April 2010
![Page 26: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/26.jpg)
in case it wasn't clear• command line actions are
22
Sunday, 11 April 2010
![Page 27: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/27.jpg)
in case it wasn't clear• command line actions are
> commands (the name of the application to invoke)
22
Sunday, 11 April 2010
![Page 28: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/28.jpg)
in case it wasn't clear• command line actions are
> commands (the name of the application to invoke)> parameters (optional arguments for the application)
22
Sunday, 11 April 2010
![Page 29: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/29.jpg)
in case it wasn't clear• command line actions are
> commands (the name of the application to invoke)> parameters (optional arguments for the application)> and the ENTER key
22
Sunday, 11 April 2010
![Page 30: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/30.jpg)
in case it wasn't clear• command line actions are
> commands (the name of the application to invoke)> parameters (optional arguments for the application)> and the ENTER key
22
Enter
Sunday, 11 April 2010
![Page 31: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/31.jpg)
The Unix philosophy (1)• One tool for every feature
> make one tool that does one thing well> for many features, make many tools> make them work together (see next slide)
23
Sunday, 11 April 2010
![Page 32: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/32.jpg)
The Unix philosophy (2)• To make several tool cooperate:
> pipe the output of one tool to the input of another one.> for more actions, pipe more tools
$ sort < filename > uniq | nl
24
Sunday, 11 April 2010
![Page 33: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/33.jpg)
Unix pipes
25
Sunday, 11 April 2010
![Page 34: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/34.jpg)
Unix pipes
25
inputapplication <
Sunday, 11 April 2010
![Page 35: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/35.jpg)
Unix pipes
25
outputapplication >
inputapplication <
Sunday, 11 April 2010
![Page 36: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/36.jpg)
Unix pipes
25
outputapplication >
inputapplication <
outputapplication >input<
Sunday, 11 April 2010
![Page 37: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/37.jpg)
Unix pipes
25
outputapplication >
inputapplication <
outputapplication >input<
application | application
Sunday, 11 April 2010
![Page 38: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/38.jpg)
Unix pipes
25
outputapplication >
inputapplication <
outputapplication >input<
application | application
input output
Sunday, 11 April 2010
![Page 39: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/39.jpg)
Unix pipes = parallel processing
26
Sunday, 11 April 2010
![Page 40: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/40.jpg)
Unix pipes = parallel processing
26
application | application
Sunday, 11 April 2010
![Page 41: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/41.jpg)
Unix pipes = parallel processing
26
application | application
Sunday, 11 April 2010
![Page 42: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/42.jpg)
Unix pipes = parallel processing
26
application | application
output
Sunday, 11 April 2010
![Page 43: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/43.jpg)
Unix pipes = parallel processing
26
application | application
input output
Sunday, 11 April 2010
![Page 44: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/44.jpg)
Who gets what• shell• application• database server• pager
27
Sunday, 11 April 2010
![Page 45: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/45.jpg)
who gets what
28
shell prompt $ mysql
mysql client
mysql server
Sunday, 11 April 2010
![Page 46: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/46.jpg)
any hostwho gets what
29
shellmysql client
mysql server
Sunday, 11 April 2010
![Page 47: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/47.jpg)
who gets what$ mysql -u username -ppassword
30
First the applicationthen the server
Sunday, 11 April 2010
![Page 48: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/48.jpg)
who gets what$ mysql -u username -ppassword \ -e "select VERSION()"
31
goes to the server
goes to the client, then the server
Sunday, 11 April 2010
![Page 49: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/49.jpg)
who gets whatmysql> helpmysql> pager lessmysql> select 1;
32
goes to the server
goes to the client onlygoes to the client only
Sunday, 11 April 2010
![Page 50: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/50.jpg)
the command line
readline33
Sunday, 11 April 2010
![Page 51: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/51.jpg)
readline• Common component
> shell> mysql client> many CLI applications
34
Sunday, 11 April 2010
![Page 52: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/52.jpg)
Main cursor movements
35
Ctrl A Ctrl E
Start/End of line
Meta B Meta F
Back/Forward one word
previous/next command
Ctrl L clear screen and take current line on top
Sunday, 11 April 2010
![Page 53: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/53.jpg)
Changing text
36
Meta U Uppercase word
Meta L Lowercase word
Ctrl D delete word
Ctrl K delete to end of line
Ctrl - undo
for more tips:$ man readline
Sunday, 11 April 2010
![Page 54: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/54.jpg)
Some conventions in this talk
$ commandat the shell
mysql> command at the mysql prompt
37
Sunday, 11 April 2010
![Page 55: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/55.jpg)
the Bash shell
bash38
Sunday, 11 April 2010
![Page 56: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/56.jpg)
components• commands• aliases• variables• functions
39
Sunday, 11 April 2010
![Page 57: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/57.jpg)
commands$ echo "something"something
40
Sunday, 11 April 2010
![Page 58: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/58.jpg)
aliases$ alias mydate='mysql -e "select NOW()"'$ mydate+---------------------+| now() |+---------------------+| 2010-04-12 07:14:19 |+---------------------+
41
Sunday, 11 April 2010
![Page 59: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/59.jpg)
aliases$ alias myself='mysql' $ myself -e "select NOW()"'+---------------------+| now() |+---------------------+| 2010-04-12 07:14:19 |+---------------------+
42
Sunday, 11 April 2010
![Page 60: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/60.jpg)
variables$ MYBATCH='mysql -N -B'$ MYQUERY='select curdate()' $ $MYBATCH -e "$MYQUERY"2010-04-12
43
Sunday, 11 April 2010
![Page 61: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/61.jpg)
nesting variables# t.sh CMD="SELECT TABLE_NAME" TBL='information_schema.tables' WHC1="TABLE_SCHEMA='mysql'" WHC2="TABLE_NAME like 'u%' " Q1="$CMD FROM $TBL" Q2="WHERE $WHC1 AND $WHC2" Q="$Q1 $Q2" echo $Q echo $Q | mysql
44
Sunday, 11 April 2010
![Page 62: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/62.jpg)
nesting variables$ sh t.shSELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME like 'u%'TABLE_NAMEuser
45
Sunday, 11 April 2010
![Page 63: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/63.jpg)
the mysql command line client
mysql46
Sunday, 11 April 2010
![Page 64: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/64.jpg)
SQL commands from a file$ mysql < filename.sqlmysql> source filename.sql
• executes the commands contained in filename.sql• commands can be queries understood by the server• and commands for the client
47
Sunday, 11 April 2010
![Page 65: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/65.jpg)
SQL commands at the command line$ application | mysql$ echo "SELECT 1" | mysql$ mysql -e "SELECT 1"
• executes the commands from standard input• commands can be queries understood by the server• and commands for the client
48
Sunday, 11 April 2010
![Page 66: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/66.jpg)
batch mode$ echo "select curdate()" | mysqlcurdate()2010-04-12
49
Sunday, 11 April 2010
![Page 67: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/67.jpg)
table mode$ echo "select curdate()" | mysql -t+------------+| curdate() |+------------+| 2010-04-12 |+------------+
50
Sunday, 11 April 2010
![Page 68: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/68.jpg)
table mode$ mysql -e "select curdate()"+------------+| curdate() |+------------+| 2010-04-12 |+------------+
51
Sunday, 11 April 2010
![Page 69: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/69.jpg)
Forcing batch modemysql -B -e 'select curdate()'curdate()2010-04-12
52
Sunday, 11 April 2010
![Page 70: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/70.jpg)
Forcing batch mode - no headermysql -N -B -e 'select curdate()'2010-04-12
53
IMPORTANT!
Sunday, 11 April 2010
![Page 71: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/71.jpg)
Shell + mysql$ echo "The current server's date is `mysql -N -B -e 'select curdate()'`."
$ echo "The current server's date is $(mysql -N -B -e 'select curdate()')."
54
Sunday, 11 April 2010
![Page 72: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/72.jpg)
advanced command line techniques
loops55
Sunday, 11 April 2010
![Page 73: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/73.jpg)
loops basics$ for N in 1 2 3> do> echo $N> done123
56
Sunday, 11 April 2010
![Page 74: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/74.jpg)
loops basics$ for N in 1 2 3 ; do echo $N ; done123
57
Sunday, 11 April 2010
![Page 75: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/75.jpg)
loops basics$ for N in `seq 1 3` ;\ do echo "this is number $N" ;\ done
this is number 1this is number 2this is number 3
58
Sunday, 11 April 2010
![Page 76: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/76.jpg)
loops from file$ head /usr/share/dict/words AaaaaalaaliiaamAaniaardvarkaardwolf
59
Sunday, 11 April 2010
![Page 77: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/77.jpg)
loops from file$ for U in \`head /usr/share/dict/words ` ; do perl -le 'print uc shift' $U ; doneAAAAAALAALIIAAMAANI
60
Sunday, 11 April 2010
![Page 78: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/78.jpg)
loops from mysql results$ mysql -B -N -e 'show tables from world'CityCountryCountryLanguage
61
Sunday, 11 April 2010
![Page 79: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/79.jpg)
loops from mysql results$ for T in `mysql -B -N -e 'show tables from world'` ;\> do echo "CREATE TABLE world2.$T LIKE world.$T;" ; done
CREATE TABLE world2.City LIKE world.City;CREATE TABLE world2.Country LIKE world.Country;CREATE TABLE world2.CountryLanguage LIKE world.CountryLanguage;
62
Sunday, 11 April 2010
![Page 80: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/80.jpg)
piping loops back into mysql$ for T in `mysql -B -N -e 'show tables from world'` ;\> do echo "CREATE TABLE world2.$T LIKE world.$T;" ; done | mysql -vv
63
Sunday, 11 April 2010
![Page 81: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/81.jpg)
advanced command line techniques
nested loops
64
Sunday, 11 April 2010
![Page 82: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/82.jpg)
doing something for every tablein every database$ mysql -e "show databases"+--------------------+| Database |+--------------------+| information_schema || mysql || sakila || world |+--------------------+
65
Sunday, 11 April 2010
![Page 83: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/83.jpg)
doing something for every tablein every database$ mysql -e "show tables from world"+-----------------+| Tables_in_world |+-----------------+| City || Country || CountryLanguage |+-----------------+
66
Sunday, 11 April 2010
![Page 84: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/84.jpg)
pseudocodefor each database do for each table do print the table name
67
Sunday, 11 April 2010
![Page 85: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/85.jpg)
actual codealias mybatch='mysql -B -N'for D in `mybatch -e "SHOW SCHEMAS"`do for T in \ `mybatch -e "SHOW TABLES FROM $D"` do echo "$D.$T" donedone
68
Sunday, 11 April 2010
![Page 86: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/86.jpg)
advanced command line techniques
options files
69
Sunday, 11 April 2010
![Page 87: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/87.jpg)
Default option files/etc/my.cnf/etc/mysql/my.cnf (Debian, Ubuntu)$BASEDIR/my.cnf$HOME/.my.cnf
70
Sunday, 11 April 2010
![Page 88: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/88.jpg)
Custom option files$ mysql --no-defaults$ mysql --defaults-file=filename$ mysql --defaults-extra-file=filename
71
Sunday, 11 April 2010
![Page 89: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/89.jpg)
Option files sections[mysqld]# option for server
[client]# option for any mysql client
[mysql]# options specific to 'mysql' CLI app
72
Sunday, 11 April 2010
![Page 90: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/90.jpg)
Option files example
[client]user=mynamepassword=mypassword
[mysql]prompt="mysql (\h) {\u} [\d] > "
73
Sunday, 11 April 2010
![Page 91: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/91.jpg)
Option files: caution# protect your option files!
$ chmod 600 $HOME/.my.cnf
$ ls -l .my.cnf-rw------- 1 [...] 10:04 .my.cnf
74
Sunday, 11 April 2010
![Page 92: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/92.jpg)
promptmysql> prompt gm (\h) {\u} [\d] > PROMPT set to 'gm (\h) {\u} [\d] > 'gm (localhost) {root} [(none)] > use testDatabase changedgm (localhost) {root} [test] >
# \h -> host# \u -> user# \d -> database
75
Sunday, 11 April 2010
![Page 93: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/93.jpg)
advanced command line techniques
aliases76
Sunday, 11 April 2010
![Page 94: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/94.jpg)
An useful alias$ alias mysql_as_root=\'mysql --defaults_file=~/.my_as_root.cnf'
mysql_as_root -e "select user()"+----------------+| user() |+----------------+| root@localhost |+----------------+
77
Sunday, 11 April 2010
![Page 95: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/95.jpg)
An useful alias#.my_as_root.cnf'[client]user=rootpassword=your_mysql_root_password
[mysql]prompt="### [\h] {\u} (\d) > "
78
Sunday, 11 April 2010
![Page 96: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/96.jpg)
Another useful aliasalias mybatch='mysql -B -N '
mybatch -e "select version()"5.1.45-log
79
Sunday, 11 April 2010
![Page 97: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/97.jpg)
advanced command line techniques
functions80
Sunday, 11 April 2010
![Page 98: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/98.jpg)
functions• like aliases, but with parameters
81
Sunday, 11 April 2010
![Page 99: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/99.jpg)
sample functionfunction grant_new { mysql_as_root -e \ "GRANT $1 on $2 to $3 identified by \"$4\" "}
$ grant_new SELECT "world.*" john \ john_secret
82
Sunday, 11 April 2010
![Page 100: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/100.jpg)
sample function$ mysql_as_root \ -e 'show grants for john\G'*** 1. row ***Grants for john@%: GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*73A88CE13374128844E3ABB500F439A9C02C53A3'*** 2. row ***Grants for john@%: GRANT SELECT ON `world`.* TO 'john'@'%'
83
Sunday, 11 April 2010
![Page 101: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/101.jpg)
advanced command line techniques
getting query
details84
Sunday, 11 April 2010
![Page 102: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/102.jpg)
which query is being executed?# wrong.sql# this script has one erroruse test ;drop table if exists t1 ;create table t1 (id int);insert into t1 values (a);
85
Sunday, 11 April 2010
![Page 103: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/103.jpg)
which query is being executed?$ mysql < wrong.sqlERROR 1054 (42S22) at line 4: Unknown column 'a' in 'field list'
# WHERE IS THE ERROR?
86
Sunday, 11 April 2010
![Page 104: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/104.jpg)
which query is being executed?$ mysql -v < wrong.sql--------------drop table if exists t1----------------------------create table t1 (id int)----------------------------insert into t1 values (a)--------------ERROR 1054 (42S22) at line 4: Unknown column 'a' in 'field list'
87
Sunday, 11 April 2010
![Page 105: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/105.jpg)
getting execution times$ mysql -vv -e \'select rating,count(*) from sakila.film group by rating'+--------+----------+| rating | count(*) |+--------+----------+| G | 178 || PG | 194 || PG-13 | 223 || R | 195 || NC-17 | 210 |+--------+----------+5 rows in set (0.00 sec) 88
Sunday, 11 April 2010
![Page 106: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/106.jpg)
$echo 'select rating,count(*) from sakila.film group by rating' | mysql -vv--------------select rating,count(*) from sakila.film group by rating--------------
rating count(*)G 178PG 194PG-13 223R 195NC-17 210 89
getting execution times (batch mode)
Sunday, 11 April 2010
![Page 107: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/107.jpg)
getting execution times (batch mode)$ echo 'select rating,count(*) from sakila.film group by rating' | mysql -vvvB --------------select rating,count(*) from sakila.film group by rating--------------+--------+----------+| rating | count(*) |+--------+----------+| G | 178 || PG | 194 || PG-13 | 223 || R | 195 | 90
Sunday, 11 April 2010
![Page 108: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/108.jpg)
advanced command line techniques
getting distinct results
91
Sunday, 11 April 2010
![Page 109: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/109.jpg)
getting a single value$ mysql -e "show variables like 'port'"+---------------+-------+| Variable_name | Value |+---------------+-------+| port | 3306 |+---------------+-------+
# Here, we only want the port number
92
Sunday, 11 April 2010
![Page 110: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/110.jpg)
getting a single value$ mysql -NB -e "show variables like 'port'" port 3306
# still two values
93
Sunday, 11 April 2010
![Page 111: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/111.jpg)
getting a single value$ mysql -NB \ -e "show variables like 'port'" \ awk '{print $2}'
3306
$ mysql -NB \ -e "show variables like 'port'" \ perl -lane 'print $F[1]'
3306
94
Sunday, 11 April 2010
![Page 112: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/112.jpg)
getting a single value: is replication working?
$ mysql \ -e "show slave status \G" \ grep -i "slave_io_running"
Slave_IO_Running: Yes
# too many things. # We only want a 'yes' or 'no'
95
Sunday, 11 April 2010
![Page 113: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/113.jpg)
getting a single value: is replication working?
$ mysql \ -e "show slave status \G" \ grep -i "slave_io_running" \ awk '{print $2}'
Yes
# yes!
96
Sunday, 11 April 2010
![Page 114: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/114.jpg)
advanced command line techniques
combining column names
97
Sunday, 11 April 2010
![Page 115: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/115.jpg)
combining column names$ mysql -N -e 'desc world.City'+-------------+----------+----+-----+| ID | int(11) | NO | PRI || Name | char(35) | NO | | | CountryCode | char(3) | NO | | | District | char(20) | NO | | | Population | int(11) | NO | | +-------------+----------+----+-----+
98
Sunday, 11 April 2010
![Page 116: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/116.jpg)
combining column names# We want:# SELECT CONCAT_WS('|', # ID,Name,CountryCode,District, # Population) from City
99
Sunday, 11 April 2010
![Page 117: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/117.jpg)
combining column names (I)echo "select concat_ws('|', `mysql -N -e 'desc world.City' | \ perl -lane 'push @f, $F[0];' \ -e 'END{print join ",", @f}'`) from world.City"
100
Sunday, 11 April 2010
![Page 118: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/118.jpg)
combining column names (II)echo "select concat_ws('|', ` mysql -NB -e 'select group_concat(column_name) from information_schema.columns where table_name="City" and table_schema="world"'`) from world.City"
101
Sunday, 11 April 2010
![Page 119: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/119.jpg)
advanced command line techniques
dumping data
102
Sunday, 11 April 2010
![Page 120: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/120.jpg)
dumping a tab delimited data set # the naive way$ mysql \ -e "SELECT * into outfile '/tmp/city_data' from world.City"
# it works only if:# * /tmp/city_data does not exist# * /tmp is world writable
103
Sunday, 11 April 2010
![Page 121: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/121.jpg)
dumping a tab delimited data set $ mysql -B -N \ -e "SELECT * from world.City" \ > /tmp/city_data
# it works always
# if you omit the "-N" it also # includes a header
104
Sunday, 11 April 2010
![Page 122: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/122.jpg)
dumping a CSV $ mysql -B \ -e "SELECT * from world.City" \ perl -F"\t" -lane \ 'print join ',', map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' \ > /tmp/city_data
105
Sunday, 11 April 2010
![Page 123: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/123.jpg)
advanced command line techniques
script it106
Sunday, 11 April 2010
![Page 124: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/124.jpg)
use the mysql.sh script The companion code for this tutorial http://tinyurl.com/cli-tut-code-2010 It will save you much typing But use it at your own risk!
107
Sunday, 11 April 2010
![Page 125: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/125.jpg)
Usage• download the script• load it
108
$ source mysql.sh
Sunday, 11 April 2010
![Page 126: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/126.jpg)
mysql.sh functions• my_batch "query"• returns the result of the query. • Without header • Without box
109
$ my_batch "SELECT * FROM test.t1"
Sunday, 11 April 2010
![Page 127: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/127.jpg)
mysql.sh functions• my_value "what"• returns a single value
• e.g.
110
$ my_value "version()"$ my_value "@@server_id"
Sunday, 11 April 2010
![Page 128: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/128.jpg)
mysql.sh functions• my_var "what"• returns the value of a given variable
• e.g.
111
$ my_var port$ my_var datadir
Sunday, 11 April 2010
![Page 129: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/129.jpg)
mysql.sh functions• my_status "what"• returns the value of a given status item
• e.g.
112
$ my_status com_select$ my_status connections
Sunday, 11 April 2010
![Page 130: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/130.jpg)
mysql.sh functions• my_dump_dataset "query" [header]• returns the values of a given query separated by tabs• if a header is requested, it will be part of the result
• e.g.
113
$ my_dump_dataset "select * from world.City"
Sunday, 11 April 2010
![Page 131: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/131.jpg)
mysql.sh functions• my_csv "query" [separator]• returns the values of a given query separated by
commas, or by a given separator
• e.g.
114
$ my_csv "select * from world.City"
Sunday, 11 April 2010
![Page 132: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/132.jpg)
inside the command line client
handling output
115
Sunday, 11 April 2010
![Page 133: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/133.jpg)
pagermysql> SELECT * FROM world.City;
rec …rec …rec …
4000 + recordsunreadable
116
Sunday, 11 April 2010
![Page 134: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/134.jpg)
pagermysql> pager lessPAGER set to 'less'mysql> SELECT * FROM world.City;
# [ records in a manageable window ]
mysql> nopagerPAGER set to stdout
# back to normal117
Sunday, 11 April 2010
![Page 135: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/135.jpg)
More pager tricksmysql> pager vim -# like 'less', but you can also edit# the results and eventually save # them
118
Sunday, 11 April 2010
![Page 136: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/136.jpg)
More pager tricksmysql> pager md5summysql> pager sha1sum
# produce a CRC for the result set# instead of displaying it.# Useful for data comparison.# (Thanks to Baron Schwartz)
119
Sunday, 11 April 2010
![Page 137: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/137.jpg)
More pager tricksmysql> pager cat > /tmp/t1.txtmysql> select "one" union select "two";mysql> pager cat > /tmp/t2.txtmysql> select "one" union select "TWO";mysql> nopagermysql> \! vimdiff -o /tmp/t[12].txt
# see the difference between two # datasets
120
Sunday, 11 April 2010
![Page 138: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/138.jpg)
logging sessionsmysql> tee mylog.txt
# logs everything that you see on# the screen.# your commands, the results,# warnings, error messages, and so on.
mysql> notee
# back to normal121
Sunday, 11 April 2010
![Page 139: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/139.jpg)
Updated presentation slides
you will find an up to date copy of these slides at: http://tinyurl.com/cli-tut-2010
Sunday, 11 April 2010
![Page 140: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/140.jpg)
The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
123
Sunday, 11 April 2010
![Page 141: datacharmer.com/tutorial_uc2010/command_line_tutorial_2010.pdf · mysql client mysql server Sunday, 11 April 2010. who gets what $ mysql -u username -ppassword](https://reader033.vdocuments.us/reader033/viewer/2022042712/5f8f87c9c2f7a96f0c4c0e69/html5/thumbnails/141.jpg)
THANKS!
Question time
Comment on Twitter: @datacharmer
Sunday, 11 April 2010