前回Windowsだったので、今回はUbuntuで引き続き初心者向けにライトな感じで導入部分を書いてみました。
1. MySQL APT Repositoryの取得
ここからダウンロード(インストール済の人はスキップ)
2020/1/25現在の最新版はmysql-apt-config_0.8.14-1_all.deb
2. インストール＆アップデート
$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb $ sudo apt-get update
実行例
$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb 以前に未選択のパッケージ mysql-apt-config を選択しています。 (データベースを読み込んでいます ... 現在 200765 個のファイルとディレクトリがインストールされています。) mysql-apt-config_0.8.14-1_all.deb を展開する準備をしています ... mysql-apt-config (0.8.14-1) を展開しています... mysql-apt-config (0.8.14-1) を設定しています ... Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config) OK $ sudo apt-get update 取得:1 http://repo.mysql.com/apt/ubuntu bionic InRelease [19.4 kB] ヒット:2 http://archive.ubuntulinux.jp/ubuntu bionic InRelease ヒット:3 http://jp.archive.ubuntu.com/ubuntu bionic InRelease ヒット:4 http://archive.ubuntulinux.jp/ubuntu-ja-non-free bionic InRelease 取得:5 https://download.docker.com/linux/ubuntu bionic InRelease [64.4 kB] 取得:6 http://jp.archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB] 取得:7 http://jp.archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB] 無視:8 http://dl.google.com/linux/chrome/deb stable InRelease ヒット:9 http://packages.microsoft.com/repos/vscode stable InRelease ヒット:11 http://dl.google.com/linux/chrome/deb stable Release 取得:12 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB] ヒット:10 https://packages.cloud.google.com/apt kubernetes-xenial InRelease ヒット:13 https://packagecloud.io/slacktechnologies/slack/debian jessie InRelease ヒット:14 http://apt.postgresql.org/pub/repos/apt bionic-pgdg InRelease 取得:16 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 Sources [928 B] 取得:17 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config i386 Packages [566 B] 取得:18 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config amd64 Packages [566 B] 取得:19 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages [5,667 B] 取得:20 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 i386 Packages [5,657 B] 取得:21 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 Packages [4,294 B] 取得:22 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools i386 Packages [3,694 B] 357 kB を 6秒 で取得しました (61.4 kB/s) パッケージリストを読み込んでいます... 完了
3. MySQL Shellのインストール
$ sudo apt-get install mysql-shell
実行例
$ sudo apt-get install mysql-shell パッケージリストを読み込んでいます... 完了 依存関係ツリーを作成しています 状態情報を読み取っています... 完了 以下のパッケージが自動でインストールされましたが、もう必要とされていません: libllvm8 postgresql-client-11 python3-flask-htmlmin python3-htmlmin これを削除するには 'sudo apt autoremove' を利用してください。 以下のパッケージが新たにインストールされます: mysql-shell アップグレード: 0 個、新規インストール: 1 個、削除: 0 個、保留: 14 個。 13.0 MB のアーカイブを取得する必要があります。 この操作後に追加で 72.3 MB のディスク容量が消費されます。 取得:1 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 mysql-shell amd64 8.0.19-1ubuntu18.04 [13.0 MB] 13.0 MB を 2秒 で取得しました (7,678 kB/s) 以前に未選択のパッケージ mysql-shell:amd64 を選択しています。 (データベースを読み込んでいます ... 現在 200770 個のファイルとディレクトリがインストールされています。) .../mysql-shell_8.0.19-1ubuntu18.04_amd64.deb を展開する準備をしています ... mysql-shell:amd64 (8.0.19-1ubuntu18.04) を展開しています... mysql-shell:amd64 (8.0.19-1ubuntu18.04) を設定しています ...
4. 確認
mysqlshのHELP（オプション確認）
$ mysqlsh MySQL Shell 8.0.19 Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.
MySQL JS > \help
The Shell Help is organized in categories and topics. To get help for a
specific category or topic use: \? <pattern>
The <pattern> argument should be the name of a category or a topic.
The pattern is a filter to identify topics for which help is required, it can
use the following wildcards:
- ? matches any single character.
- * matches any character sequence.
The following are the main help categories:
- AdminAPI The AdminAPI is an API that enables configuring and managing
InnoDB clusters and replicasets, among other things.
- Shell Commands Provides details about the available built-in shell commands.
- ShellAPI Contains information about the shell and util global objects
as well as the mysql module that enables executing SQL on
MySQL Servers.
- SQL Syntax Entry point to retrieve syntax help on SQL statements.
- X DevAPI Details the mysqlx module as well as the capabilities of the
X DevAPI which enable working with MySQL as a Document Store
The available topics include:
- The dba global object and the classes available at the AdminAPI.
- The mysqlx module and the classes available at the X DevAPI.
- The mysql module and the global objects and classes available at the
ShellAPI.
- The functions and properties of the classes exposed by the APIs.
- The available shell commands.
- Any word that is part of an SQL statement.
- Command Line - invoking built-in shell functions without entering interactive
mode.
SHELL COMMANDS
The shell commands allow executing specific operations including updating the
shell configuration.
The following shell commands are available:
- \ Start multi-line input when in SQL mode.
- \connect (\c) Connects the shell to a MySQL server and assigns the
global session.
- \edit (\e) Launch a system editor to edit a command to be executed.
- \exit Exits the MySQL Shell, same as \quit.
- \help (\?,\h) Prints help information about a specific topic.
- \history View and edit command line history.
- \js Switches to JavaScript processing mode.
- \nopager Disables the current pager.
- \nowarnings (\w) Don't show warnings after every statement.
- \option Allows working with the available shell options.
- \pager (\P) Sets the current pager.
- \py Switches to Python processing mode.
- \quit (\q) Exits the MySQL Shell.
- \reconnect Reconnects the global session.
- \rehash Refresh the autocompletion cache.
- \show Executes the given report with provided options and
arguments.
- \source (\.) Loads and executes a script from a file.
- \sql Executes SQL statement or switches to SQL processing
mode when no statement is given.
- \status (\s) Print information about the current global session.
- \system (\!) Execute a system shell command.
- \use (\u) Sets the active schema.
- \warnings (\W) Show warnings after every statement.
- \watch Executes the given report with provided options and
arguments in a loop.
GLOBAL OBJECTS
The following modules and objects are ready for use when the shell starts:
- dba Used for InnoDB cluster administration.
- mysql Support for connecting to MySQL servers using the classic MySQL
protocol.
- mysqlx Used to work with X Protocol sessions using the MySQL X DevAPI.
- os Gives access to functions which allow to interact with the operating
system.
- shell Gives access to general purpose functions and properties.
- sys Gives access to system specific parameters.
- util Global object that groups miscellaneous tools like upgrade checker
and JSON import.
For additional information on these global objects use: <object>.help()
EXAMPLES
\? AdminAPI
Displays information about the AdminAPI.
\? \connect
Displays usage details for the \connect command.
\? checkInstanceConfiguration
Displays usage details for the dba.checkInstanceConfiguration function.
\? sql syntax
Displays the main SQL help categories.
\?または\helpを最初に書いて、その後オプションを書く。
MySQL SQL > \? \connect
NAME
\connect - Connects the shell to a MySQL server and assigns the global
session.
SYNTAX
\connect [<TYPE>] <URI>
\c [<TYPE>] <URI>
DESCRIPTION
TYPE is an optional parameter to specify the session type. Accepts the
following values:
- --mc, --mysql: create a classic MySQL protocol session (default port
3306)
- --mx, --mysqlx: create an X protocol session (default port 33060)
If TYPE is omitted, automatic protocol detection is done, unless the
protocol is given in the URI.
URI format is: [user[:password]@]hostname[:port]
EXAMPLE
\connect --mx root@localhost
Creates a global session using the X protocol to the indicated URI.
DB接続
mysqlのクライアントとしてDBに接続するときはこんな感じで書くということらしい。
MySQL SQL > \connect --mc user:password@127.0.0.1:3306
or
MySQL SQL > \connect --mysql user:password@127.0.0.1:3306
実行例
MySQL SQL > \connect --mysql root:xxxxxx@127.0.0.1:3306 Creating a Classic session to 'root@127.0.0.1:3306' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 4410 Server version: 8.0.19 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL 127.0.0.1:3306 ssl SQL >
スキーマ変更は\use <schema>
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \use performance_schema Default schema set to `performance_schema`. Fetching table and column names from `performance_schema` for auto-completion... Press ^C to stop. MySQL 127.0.0.1:3306 ssl performance_schema SQL >
セッションの再接続
セッション切れたっぽいぞ、と思ったら\reconnect
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \reconnect Attempting to reconnect to 'mysql://root@127.0.0.1:3306/performance_schema'.. The global session was successfully reconnected.
接続先DBサーバの状態確認
繋いでいるDBサーバの状態を見るのは\status
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \status MySQL Shell version 8.0.19 Session type: Classic Connection Id: 4430 Current schema: performance_schema Current user: root@172.xxx.xxx.1 SSL: Cipher in use: ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2 Using delimiter: ; Server version: 8.0.19 MySQL Community Server - GPL Protocol version: classic 10 Client library: 8.0.19 Connection: 127.0.0.1 via TCP/IP TCP port: 3306 Server characterset: utf8mb4 Schema characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 Compression: Disabled Uptime: 23 hours 35 min 19.0000 sec Threads: 6 Questions: 391129 Slow queries: 0 Opens: 542 Flush tables: 3 Open tables: 462 Queries per second avg: 4.605
別のMySQLサーバ（5.7）の環境に繋いで\status実行してみる
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \connect --mc root:xxxxx@127.0.0.1:3307 Creating a Classic session to 'root@127.0.0.1:3307' Fetching schema names for autocompletion... Press ^C to stop. Closing old connection... Your MySQL connection id is 4440 Server version: 5.7.26 MySQL Community Server (GPL) No default schema selected; type \use <schema> to set one. MySQL 127.0.0.1:3307 ssl SQL > \use information_schema Default schema set to `information_schema`. Fetching table and column names from `information_schema` for auto-completion... Press ^C to stop. MySQL 127.0.0.1:3307 ssl information_schema SQL > \status MySQL Shell version 8.0.19 Session type: Classic Connection Id: 4440 Current schema: information_schema Current user: root@172.xxx.xxx.1 SSL: Cipher in use: DHE-RSA-AES256-SHA TLSv1.1 Using delimiter: ; Server version: 5.7.26 MySQL Community Server (GPL) Protocol version: classic 10 Client library: 8.0.19 Connection: 127.0.0.1 via TCP/IP TCP port: 3307 Server characterset: latin1 Schema characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 Compression: Disabled Uptime: 23 hours 36 min 19.0000 sec Threads: 5 Questions: 388499 Slow queries: 0 Opens: 204626 Flush tables: 1 Open tables: 2000 Queries per second avg: 4.571
コマンド履歴の確認
今のセッションのコマンド履歴を見たいときは\history
MySQL SQL > \history
1 \history
2 \status
但し、その番号のコマンドが実行できるわけではなさそう
MySQL SQL > \? \history
NAME
\history - View and edit command line history.
SYNTAX
\history [options].
DESCRIPTION
The operation done by this command depends on the given options. Valid
options are:
- del range Deletes entry/entries from history.
- clear Clear history.
- save Save history to file.
If no options are given the command will display the history entries.
Range in the delete operation can be given in one of the following forms:
- num single number identifying entry to delete.
- num-num numbers specifying lower and upper bounds of the range.
- num- range from num till the end of history.
- -num last num entries.
NOTE: The history.autoSave shell option must be set to true to
automatically save the contents of the command history when MySQL Shell
exits.
EXAMPLES
\history
Displays the entire history.
\history del 123
Deletes entry number 123 from the history.
\history del 10-20
Deletes range of entries from number 10 to 20 from the history.
\history del 10-
Deletes entries from number 10 and ahead from the history.
\history del -5
Deletes last 5 entries from the history.
ページャーの設定と解除
先頭１０行で区切る
\pager more -10
うう、改行されちゃったものも一行とみなすのね・・・。
MySQL 127.0.0.1:3306 ssl information_schema SQL > select * from TABLES;
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+---------
--------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+---------------------------------------+------------------------------
--------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA
_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT
|
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+---------
--------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+---------------------------------------+------------------------------
--------------------+
| def | mysql | innodb_table_stats | BASE TABLE | InnoDB | 10 | Dynamic | 67 | 244 | 16384 |
--More--
ページャーの解除は\nopager
\nopager
抜けるときは\qか\quitか\exitで
MySQL 127.0.0.1:3306 ssl information_schema SQL > \q Bye! $
最後に
\js や\pyでJavaScriptやPythonに切り替えられますし、Replica SetやMySQL InnoDB Clusterを使うならそっちなので、今度はそっちの話を書こうかと思います。
