mysql Command-line Client

mysql 명령줄 클라이언트에 대하여

mysql (from MariaDB 10.4.6, also called mariadb) is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options. mysql (MariaDB 10.4.6부터는 mariadb라고도 불립니다.)은 간단한 SQL 셸입니다.(GNU readline 기능 포함) 대화형 및 비 대화형 사용을 지원합니다. 대화형으로 사용될 경우 쿼리 결과는 ASCII-테이블 형식으로 표시됩니다. 비 대화형으로 사용될 경우(예시 : 필터로 사용되는 경우) 쿼리 결과는 탭으로 구분된 형식으로 표시됩니다. 출력 형식은 명령어 옵션을 사용해 변경할 수 있습니다.

If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by returning the result set using the mysql_use_result() C API function in the client/server library rather than mysql_store_result(). 큰 결과집합을 위한 메모리가 충분하지 않은 문제가 발생하면, --quick 옵션을 사용하세요. 이 옵션은 mysql이 서버로부터 한번에 결과집합을 검색한 하여 표시하기도 전에 메모리에 버퍼링 하는 대신, 한번에 한 행씩 받아오도록 합니다. 이는 client/server 라이브러리의 C API 함수 중에서 mysql_store_result()함수 대신 mysql_use_result()함수를 사용하여 결과집합을 반환함으로서 이루어집니다.

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: mysql 사용은 매우 간단합니다. 명령 인터프리터 프롬프트에서 아래와 같이 호출하세요:

mysql db_name

또는 아래와 같이 호출하세요:

mysql --user=user_name --password=your_password db_name

Then type an SQL statement, end it with “;”, \g, or \G and press Enter. 그런 다음 SQL문을 작성하고, 끝에 ";"또는 \g 또는 \G를 붙인 후 엔터를 누르세요.

Typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits. Control-C를 입력하면 mysql이 현재의 구문을 강제 종료합니다. 이 작업을 수행 할 수 없거나 명령문이 종료되기 전에 Control-C를 다시 입력하면 mysql이 종료됩니다.

You can execute SQL statements in a script file (batch file) like this: 다음과 같이 스크립트 파일 (배치 파일)에서 SQL 문을 실행할 수 있습니다.

mysql db_name < script.sql > output.tab

From MariaDB 10.4.6, mariadb is available as a symlink to mysql. MariaDB 10.4.6부터, mariadb는 mysql의 심볼릭 링크입니다.

From MariaDB 10.5.2, mysql is the symlink, and mariadb the binary name. MariaDB 10.5.2부터는, mysql이 심볼릭 링크이고 mariadb가 바이너리의 이름입니다.

mysql 사용하기

The command to use mysql and the general syntax is: mysql과 일반 문법을 사용하기 위한 명령어는 다음과 같습니다<<toc>>

mysql 명령줄 클라이언트에 대하여

mysql (from MariaDB 10.4.6, also called mariadb) is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options. mysql (MariaDB 10.4.6부터는 mariadb라고도 불립니다.)은 간단한 SQL 셸입니다.(GNU readline 기능 포함) 대화형 및 비 대화형 사용을 지원합니다. 대화형으로 사용될 경우 쿼리 결과는 ASCII-테이블 형식으로 표시됩니다. 비 대화형으로 사용될 경우(예시 : 필터로 사용되는 경우) 쿼리 결과는 탭으로 구분된 형식으로 표시됩니다. 출력 형식은 명령어 옵션을 사용해 변경할 수 있습니다.

If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by returning the result set using the mysql_use_result() C API function in the client/server library rather than mysql_store_result(). 큰 결과집합을 위한 메모리가 충분하지 않은 문제가 발생하면, --quick 옵션을 사용하세요. 이 옵션은 mysql이 서버로부터 한번에 결과집합을 검색한 하여 표시하기도 전에 메모리에 버퍼링 하는 대신, 한번에 한 행씩 받아오도록 합니다. 이는 client/server 라이브러리의 C API 함수 중에서 mysql_store_result()함수 대신 mysql_use_result()함수를 사용하여 결과집합을 반환함으로서 이루어집니다.

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: mysql 사용은 매우 간단합니다. 명령 인터프리터 프롬프트에서 아래와 같이 호출하세요:

mysql db_name

또는 아래와 같이 호출하세요:

mysql --user=user_name --password=your_password db_name

Then type an SQL statement, end it with “;”, \g, or \G and press Enter. 그런 다음 SQL문을 작성하고, 끝에 ";"또는 \g 또는 \G를 붙인 후 엔터를 누르세요.

Typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits. Control-C를 입력하면 mysql이 현재의 구문을 강제 종료합니다. 이 작업을 수행 할 수 없거나 명령문이 종료되기 전에 Control-C를 다시 입력하면 mysql이 종료됩니다.

You can execute SQL statements in a script file (batch file) like this: 다음과 같이 스크립트 파일 (배치 파일)에서 SQL 문을 실행할 수 있습니다.

mysql db_name < script.sql > output.tab

From MariaDB 10.4.6, mariadb is available as a symlink to mysql. MariaDB 10.4.6부터, mariadb는 mysql의 심볼릭 링크입니다.

From MariaDB 10.5.2, mysql is the symlink, and mariadb the binary name. MariaDB 10.5.2부터는, mysql이 심볼릭 링크이고 mariadb가 바이너리의 이름입니다.

mysql 사용하기

The command to use mysql and the general syntax is: mysql과 일반 구문을 사용하기 위한 명령어는 다음과 같습니다:

mysql <options>

옵션

mysql supports the following options: mysql은 아래의 옵션들을 지원합니다.

Option Files

In addition to reading options from the command-line, mysql can also read options from option files. If an unknown option is provided to mysql in an option file, then it is ignored.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

In MariaDB 10.2 and later, mysql is linked with MariaDB Connector/C. However, MariaDB Connector/C does not yet handle the parsing of option files for this client. That is still performed by the server option file parsing code. See MDEV-19035 for more information.

Option Groups

mysql reads options from the following option groups from option files:

How to Specify Which Protocol to Use When Connecting to the mysqld Server

The following is true for all MySQL and MariaDB command line clients:

You can force which protocol to be used to connect to the mysqld server by giving the protocol option one of the following values: tcp, socket, pipe or memory.

If protocol is not specified, then the following happens:

Linux/Unix

• If hostname is not specified or hostname is localhost, then Unix sockets are used. Unused connection parameters (such as port) will be ignored.
• In other cases (hostname is given and it's not localhost) then a tcpip connection through the port option is used.

Note that localhost is a special value. Using 127.0.0.1 is not the same thing. The latter will connect to the mysqld server through tcpip.

Windows

• If shared-memory-base-name is specified and hostname is not specified or hostname is localhost, then the connection will happen through shared memory. Unused connection parameters (such as port) will be ignored.
• If shared-memory-base-name is not specified and hostname is not specified or hostname is localhost, then the connection will happen through windows named pipes.
• Named pipes will also be used if the libmysql / libmariadb client library detects that the client doesn't support tcpip.
• In other cases then a tcpip connection through the port option is used.

How to Test Which Protocol is Used

The status command shows you information about which protocol is used:

shell> mysql test

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.2-MariaDB-valgrind-max-debug Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> status;
--------------
mysql  Ver 15.1 Distrib 10.0.25-MariaDB, for Linux (x86_64) using readline 5.2

Connection id:          10
Current database:       test
Current user:           monty@localhost
...
Connection:             Localhost via UNIX socket
...
UNIX socket:            /tmp/mysql-dbug.sock

mysql Commands

There are also a number of commands that can be run inside the client. Note that all text commands must be first on line and end with ';'

The mysql_history File

On Unix, the mysql client writes a record of executed statements to a history file. By default, this file is named .mysql_history and is created in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable.

The .mysql_history file should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords.

If you do not want to maintain a history file, first remove .mysql_history if it exists, and then use either of the following techniques:

• Set the MYSQL_HISTFILE variable to /dev/null. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files.
• Create .mysql_history as a symbolic link to /dev/null:

shell> ln -s /dev/null $HOME/.mysql_history

You need do this only once.

prompt Command

The prompt command reconfigures the default prompt \N [\d]>. The string for defining the prompt can contain the following special sequences.

mysql Tips

This section describes some techniques that can help you use mysql more effectively.

Displaying Query Results Vertically

Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output:

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
    date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: [email protected]
  mail_to: "Thimble Smith" <[email protected]>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi.  I think this is a good idea.  Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my
Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
    file: inbox-jani-1
    hash: 190402944
1 row in set (0.09 sec)

Using the --safe-updates Option

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

When you use the --safe-updates option, mysql issues the following statement when it connects to the MariaDB server:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

The SET statement has the following effects:

• You are not allowed to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example:

UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;

• The server limits all largeSELECT results to 1,000 rows unless the statement includes a LIMIT clause.
• The server aborts multiple-table SELECT statements that probably need to examine more than 1,000,000 row combinations.

To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the --select_limit and --max_join_size options:

mysql --safe-updates --select_limit=500 --max_join_size=10000

Disabling mysql Auto-Reconnect

If the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it:

mysql> SET @a=1;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
Query OK, 1 row affected (1.30 sec)
mysql> SELECT * FROM t;
+------+
| a    |
+------+
| NULL |
+------+

The @a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have mysql terminate with an error if the connection has been lost, you can start the mysql client with the --skip-reconnect option.

관련 자료

• Troubleshooting Connection Issues
• Readline commands and configuration