Posted at

MySQL Shellを入れてみた

More than 3 years have passed since last update.

MySQL Innovation Dayで聞いたMySQL Shellを入れてみました。


前提条件

CentOS Linux release 7.2.1511 (Core)

MySQL 5.7.12

インストール済みパッケージ一覧


mysql-community-client-5.7.12-1.el7.x86_64
mysql-community-common-5.7.12-1.el7.x86_64
mysql-community-devel-5.7.12-1.el7.x86_64
mysql-community-libs-5.7.12-1.el7.x86_64
mysql-community-libs-compat-5.7.12-1.el7.x86_64
mysql-community-server-5.7.12-1.el7.x86_64
mysql57-community-release-el7-7.noarch


mysqlxプラグインの有効化


# mysql -u root -p
mysql> install plugin mysqlx soname 'mysqlx.so';


ポート開け&再起動


# firewall-cmd --permanent --add-port=33060/tcp
# firewall-cmd --reload
# systemctl restart mysqld


起動確認


# lsof -i:33060

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 16894 mysql 22u IPv4 9234581 0t0 TCP *:33060 (LISTEN)

33060でLISTENしてくれました。

プラグインも確認


mysql> show plugins;

+----------------------------+----------+--------------------+-----------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-----------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | mysqlx.so | GPL |
+----------------------------+----------+--------------------+-----------+---------+



mysqlx がACTIVEになっています。


MySQL Shellのインストール

RPMで入れるために、まずはreleaseファイルを変更。


# yum remove mysql57-community-release
# rpm -ivh http://repo.mysql.com/yum/mysql-tools-preview/el/7/x86_64/mysql57-community-release-el7-8.noarch.rpm

以下のリポジトリ定義が追加されます。


/etc/yum.repos.d/mysql-community.repo

[mysql-tools-preview]

name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

インストールします。


# yum -y install mysql-shell --enablerepo=mysql-tools-preview
# rpm -ql mysql-shell

/usr/bin/mysqlsh
/usr/share/doc/mysql-shell-1.0.3
/usr/share/doc/mysql-shell-1.0.3/COPYING.txt
/usr/share/doc/mysql-shell-1.0.3/README
/usr/share/mysqlsh/modules/js/mysql.js
/usr/share/mysqlsh/modules/js/mysqlx.js


接続確認

※testデータベースは作成済み


# mysqlsh --sql --js --user=root test
mysql-js> db.createCollection('test_docstore')

mysql-js> db.collections
{
"test_docstore":
}

mysql-js> db.test_docstore.add ([ {'key1': 'val1'},{'key2': 'val2','key3': 'val3'}])
Query OK, 2 items affected (0.19 sec)

mysql-js> db.test_docstore.find()
[
{
"_id": "3632a54d4b17e611344700248c38799d",
"key2": "val2",
"key3": "val3"
},
{
"_id": "e230a54d4b17e611344700248c38799d",
"key1": "val1"
}
]
2 documents in set (0.00 sec)

"test_docstore"というコレクションを作成して適当なデータを入れました。

mysqlクライアントから接続してテーブルとして確認。


mysql> desc test.test_docstore;
+-------+-------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+------------------+
| doc | json | YES | | NULL | |
| _id | varchar(32) | NO | PRI | NULL | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

mysql> select * from test.test_docstore;
+-----------------------------------------------------------------------------+----------------------------------+
| doc | id |
+-----------------------------------------------------------------------------+----------------------------------+
| {"_id": "3632a54d4b17e611344700248c38799d", "key2": "val2", "key3": "val3"} | 3632a54d4b17e611344700248c38799d |
| {"id": "e230a54d4b17e611344700248c38799d", "key1": "val1"} | e230a54d4b17e611344700248c38799d |
+-----------------------------------------------------------------------------+----------------------------------+
2 rows in set (0.00 sec)

とりあえず動かすところまで。

面白そうなので、GAになるのが待ち遠しいです。