Published Book on Amazon
 |
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 1 |
 |
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 2 |
출판된 한글판 도서
 |
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (상) |
 |
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (하) |
Original Book Contents
24.2.4 Using <MySQL Shell> – "mysql" command
24.2.4.1 Basic Framework of <MySQL Shell>
Use <MySQL Shell> to use MySQL database. <MySQL Shell> is a simple SQL Shell program that allows you to enter and edit your own database commands.
This can be used both in interactive manner and in noninteractive manner. When used interactively, the results are displayed in an ASCII-table format, and if used noninteractively, the results are displayed in tab-separated format.
This program starts with "mysql" command. The basic format of the command is as follows.
[Command Format]
| mysql -u<user-id> -p[password] [option] <database_name> |
[Main option]
| --help, -? | Display a help message and exit. |
| --host=host_name, -h host_name | Connect to the MySQL server on the given host. |
| --user=user_name, -u user_name | The MySQL user name to use when connecting to the server. |
| --password[=password], -p[password] | The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or –p option on the Shell, mysql prompts for one. Specifying a password on the Shell should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the Shell. |
The database name specified in this command is the database to be used in future SQL commands. If you do not specify a specific database here, you must specify the database to be used before executing the actual SQL command at a later time.
In this command, MySQL user ID must be specified using the "--user" or "-u" option. Unless otherwise specified, it tries to use current logon user account of the system.
The password for the user account can be specified using "--password" or "-p" option. If you specify only the option and do not enter the actual password, a separate prompt asking for the password appears and accepts the input.
24.2.4.2 Starting <MySQL Shell> Program
To work with a MySQL database, first <MySQL Shell> program must be started. To start <MySQL Shell> program, you must specify at least a database user id and password, and run the command as follows.
[Command Format]
| mysql -u <user-id> -p<password> <database_name> |
Enter the following command to start <MySQL shell>.
| pi@raspberrypi ~ $ mysql -u root –p |
| Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 Server version: 5.5.43-0+deb7u1 (Debian)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> |
Then, it will be prompted to enter password for the database user, and if you enter the required password, a connection to the MySQL database will be made and basic information about the MySQL database will be displayed.
When all the connections are complete, "mysql>" prompt appears, where you can enter MySQL Shell command and standard database SQL command to perform operations.
24.2.4.3 Using MySQL Shell Command
● MySQL Shell command
MySQL Shell command is used to confirm current state of <MySQL Shell> or to influence the future behavior and presentation of MySQL Shell.
All commands must ends with ";" or "＼g".
Available MySQL Shell commands are as follows. In each command, the one expressed with "＼" is an abbreviation for the command.
| command | abbr. | description |
| ? | ＼? | Synonym for `help'. |
| help | ＼h | Display this help. |
| clear | ＼c | Clear command. |
| connect | ＼r | Reconnect to the server. Optional arguments are db and host. |
| edit | ＼e | Edit command with $EDITOR. |
| exit | ＼q | Exit mysql. Same as quit. |
| quit | ＼q | Quit mysql. |
| use | ＼u | Use another database. Takes database name as argument. |
| source | ＼. | Execute an SQL script file. Takes a file name as an argument |
| status | ＼s | Get status information from the server. |
| system | ＼! | Execute a system shell command. |
| warnings | ＼W | Show warnings after every statement. |
| nowarning | ＼w | Don't show warnings after every statement. |
| delimiter | ＼d | Set statement delimiter. |
| delimiter | ＼d | Set statement delimiter. |
| ego | ＼G | Send command to mysql server, display result vertically. |
| go | ＼g | Send command to mysql server. |
| pager | ＼P | Set PAGER [to_pager]. Print the query results via PAGER. |
| nopager | ＼n | Disable pager, print to stdout. |
| tee | ＼T | Set outfile [to_outfile]. Append everything into given outfile. |
| notee | ＼t | Don't write into outfile. |
| | ＼p | Print current command. |
| prompt | ＼R | Change your mysql prompt. |
| rehash | ＼# | Rebuild completion hash. |
| charset | ＼C | Switch to another charset. Might be needed for processing binlog with multi-byte charsets |
● Help on MySQL Shell commands
You can use "help" command if you need help with MySQL Shell commands or standard database SQL commands that can be processed by <MySQL shell>.
Next is checking help for "use" command. This result shows some description, its syntax and how to handle about "use" command.
| mysql> help use ; |
| mysql> help use Name: 'USE' Description: Syntax: USE db_name
The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued:
USE db1; SELECT COUNT(*) FROM mytable; # selects from db1.mytable USE db2; SELECT COUNT(*) FROM mytable; # selects from db2.mytable
URL: http://dev.mysql.com/doc/refman/5.5/en/use.html
mysql> |
● Terminating <MySQL Shell>
To stop processing in MySQL shell and return to BASH Shell state, issue "exit" command or "quit" command. The following process will stop MySQL shell and return to BASH Shell.
| mysql> exit; |
| Bye |
| pi@raspberrypi ~ $ |
If you want to stop processing forcibly while a specific command is being executed, press [CTRL + C] to stop the command. If it does not work, you can abort MySQL shell itself by pressing [CTRL + C] again.
● Check current status of <MySQL Shell>
Use "status" command to see what state <MySQL shell> is currently in. It will then display information about the current state.
| mysql> status; |
| -------------- mysql Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (armv7l) using readline 6.2
Connection id: 65 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.43-0+deb7u1 (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 4 hours 32 min 9 sec
Threads: 1 Questions: 773 Slow queries: 0 Opens: 213 Flush tables: 2 Open tables: 24 Queries per second avg: 0.047 -------------- |
You can see MySQL version and related information on the above result. Here, you can see the information about the currently used database, but no special database is specified yet. You can also view information about currently used database users.
● Specifying database to use
To specify the database to be used in future standard database SQL commands, use "use" command. If you specify the database name from the first time when you execute "mysql" command, you do not need to specify it. You can also use "use" command to change from one database to another.
The following is an example, in the state that current database is not specified, to specify database "mysql" with "use" command and confirm the current status with "status" command. You can see that the database "mysql" is specified in the current database.
| mysql> use mysql; |
| Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed |
| mysql> status; |
| -------------- mysql Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (armv7l) using readline 6.2
Connection id: 67 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.43-0+deb7u1 (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 4 hours 39 min 59 sec
Threads: 1 Questions: 836 Slow queries: 0 Opens: 213 Flush tables: 2 Open tables: 24 Queries per second avg: 0.049 -------------- |
● Editing Command
If the command you need to run is too long to be entered in the Shell, you can "edit" command to enter or edit commands on [Vim] editor screen. For information on how to use [Vim], see [16.4.3 [Vim] Text Editor].
| mysql> use edit; |
When you enter all the contents, save it, and exit the program, "->" prompt to mean additional input appears. Here, Enter "＼g" meaning execution of Shell command and press [Enter] to execute the Shell command.
| mysql> edit -> ＼g |