概要
Windows10に開発・検証用にMySQL Community Server 5.7.19をインストールし、初歩的な設定を行うまでの作業メモです。
インストーラー版は使わずZIP Archive版で手動インストールします。
環境
- Windows 10 Professional
- MySQL Community Server 5.7.19 (zip archive)
参考
- [MySQL 5.7 Reference Manual / 2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive] (https://dev.mysql.com/doc/refman/5.7/en/windows-install-archive.html)
- [MySQL 5.7 Reference Manual / 5.1.5 Server System Variables] (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html)
MySQL Community Server インストール
アーカイブファイルのダウンロード
ダウンロードページよりアーカイブファイルをダウンロードします。
今回ダウンロードしたアーカイブファイルはmysql-5.7.19-winx64.zipです。
アーカイブファイルの展開
アーカイブファイルを適当な場所へ展開します。今回はD:\dev\mysql-5.7.19-winx64にしました。
次に下記ディレクトリを作成します。
D:\dev\mysql-5.7.19-winx64\data
D:\dev\mysql-5.7.19-winx64\logs
D:\dev\mysql-5.7.19-winx64\tmp
my.iniの準備
my-default.iniがアーカイブに同梱されていなかったので、5.6.xで使用していたiniファイルを参考に作成しました。（5.7.18よりmy-default.iniはパッケージに含まれなくなったようです）
D:\dev\mysql-5.7.19-winx64\my.ini
今回使用したmy.iniファイルは下記の通りです。デフォルト値を使う設定はコメントアウトしています。
[mysqld]
basedir = D:\\dev\\mysql-5.7.19-winx64
datadir = D:\\dev\\mysql-5.7.19-winx64\\data
tmpdir = D:\\dev\\mysql-5.7.19-winx64\\tmp
#plugin_dir = D:\\dev\\mysql-5.7.19-winx64\\lib\\plugin
#port = 3306
#server_id = 0
#connect_timeout = 10
max_connections = 30
#max_allowed_packet = 4194304
#table_open_cache = 2000
#table_definition_cache = -1
#key_buffer_size = 8388608
#default_storage_engine = innodb
#default_tmp_storage_engine = innodb
#default-time-zone = Asia/Tokyo
#default_password_lifetime = 0
#sql_mode = ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
#tx_isolation = REPEATABLE-READ
#autocommit = 1
#secure_file_priv = NULL
#lock_wait_timeout = 31536000
explicit_defaults_for_timestamp = 1
#lc_time_names = en_US
## innodb
#innodb_buffer_pool_size = 134217728
#innodb_file_per_table = 1
innodb_status_output = 1
innodb_status_output_locks = 1
#lower_case_table_names = 0
## log_bin
#log_bin = mysql-bin
## character-set
character_set_server = utf8
collation_server = utf8_general_ci
## logging
#log_output = FILE
general_log = 1
general_log_file = D:\\dev\\mysql-5.7.19-winx64\\logs\\general_query_all.log
#log_error_verbosity = 3
log_error = D:\\dev\\mysql-5.7.19-winx64\\logs\\mysqld_error.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_syslog = 0
log_timestamps = SYSTEM
long_query_time = 3
slow_query_log = 1
slow_query_log_file = D:\\dev\\mysql-5.7.19-winx64\\logs\\slow_query.log
[mysql]
default-character-set = utf8
show-warnings
prompt = "\u@\h [\d] > "
データディレクトリの初期化
initialize
D:\dev\mysql-5.7.19-winx64> bin\mysqld --defaults-file=D:\dev\mysql-5.7.19-winx64\my.ini --initialize
mysql serverの起動と接続
起動
startup
D:\dev\mysql-5.7.19-winx64> bin\mysqld --defaults-file=D:\dev\mysql-5.7.19-winx64\my.ini --console
rootユーザーでログイン
初回ログイン時のパスワードは一時パスワードを使用します。一時パスワードはerrorログファイルに出力されていました。
login
D:\dev\mysql-5.7.19-winx64> bin\mysql -u root -p
Enter password: "temporary password"
一時パスワードでログインして、パスワードを更新します。
> alter user 'root'@'localhost' identified by 'new_password';
停止
shutdown
D:\dev\mysql-5.7.19-winx64> bin\mysqladmin -u root -p shutdown
インストール後の環境確認、設定
mysqldのオプションの確認と設定値の出力
(mysqlが起動している状態で実行、結果が長いのでtextファイルに出力しています。)
option_dump
D:\dev\mysql-5.7.19-winx64> bin\mysqld.exe --defaults-file=D:\dev\mysql-5.7.19-winx64\my.ini --help --verbose > option_dump.txt
version
D:\dev\mysql-5.7.19-winx64> bin\mysqladmin.exe -u root -p version
Enter password: ********
bin\mysqladmin.exe Ver 8.42 Distrib 5.7.19, for Win64 on x86_64
Copyright (c) 2000, 2017, 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.
Server version 5.7.19-log
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 1 min 10 sec
Threads: 1 Questions: 3 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.042
mysqlshow
D:\dev\mysql-5.7.19-winx64> bin\mysqlshow -u root -p
Enter password: ********
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
不要アカウントの削除
今回のバージョンでは特に不要なアカウントは作られていなかったのでそのままにしています。
root@localhost [(none)] > SELECT User, Host FROM mysql.user;
+---------------+-----------+
| User | Host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql.sys
予約済みアカウント
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html
sys schema objects now have a DEFINER of 'mysql.sys'@'localhost'. (Previously, the DEFINER was 'root'@'localhost'.) Use of the reserved mysql.sys account avoids problems that occur if a DBA renames or removes the root account.
mysql.session
予約済みアカウント
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html
During data directory initialization or upgrade, MySQL now creates a 'mysql.session'@'localhost' reserved account. This account is used internally by plugins to access the server. It is locked so that it cannot be used for client connections. (Bug #25642343)
補足
JSON型
MySQL 5.7.8からJSON型をサポートしています。
CREATE TABLE IF NOT EXISTS json_test (
id INT NOT NULL AUTO_INCREMENT,
contents JSON NOT NULL,
create_at DATETIME NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
)
DEFAULT CHARSET = UTF8MB4;
なお、json型にはindexが貼れません。
> CREATE INDEX idx1_json_test ON json_test (contents);
ERROR 3152 (42000): JSON column 'contents' cannot be used in key specification.
代替策として、Generated Columnsを使うというものがあります。
- [Indexing JSON documents via Virtual Columns] (http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/)
テストデータ
insert into json_test (contents) values('{"color": "red", "status": "done"}');
insert into json_test (contents) values('{"color": "green", "shape": "rectangle"}');
insert into json_test (contents) values('{"color": "blue", "shape": "triangle", "status": "not started"}');
insert into json_test (contents) values('{"color": "red", "status": "in progress"}');
insert into json_test (contents) values('{"status": "waiting"}');
insert into json_test (contents) values('{"shape": "pentagon", "status": "waiting"}');
insert into json_test (contents) values('{"color": "green", "shape": "rectangle", "status": "not started"}');
insert into json_test (contents) values('{"color": "red", "shape": "hexagon", "status": "waiting"}');
insert into json_test (contents) values('{"color": "blue"}');
select * from json_test order by id;
+----+-------------------------------------------------------------------+---------------------+
| id | contents | create_at |
+----+-------------------------------------------------------------------+---------------------+
| 19 | {"color": "red", "status": "done"} | 2017-09-14 00:56:17 |
| 20 | {"color": "green", "shape": "rectangle"} | 2017-09-14 00:56:17 |
| 21 | {"color": "blue", "shape": "triangle", "status": "not started"} | 2017-09-14 00:56:17 |
| 22 | {"color": "red", "status": "in progress"} | 2017-09-14 00:56:17 |
| 23 | {"status": "waiting"} | 2017-09-14 00:56:17 |
| 24 | {"shape": "pentagon", "status": "waiting"} | 2017-09-14 00:56:17 |
| 25 | {"color": "green", "shape": "rectangle", "status": "not started"} | 2017-09-14 00:56:17 |
| 26 | {"color": "red", "shape": "hexagon", "status": "waiting"} | 2017-09-14 00:56:17 |
| 27 | {"color": "blue"} | 2017-09-14 00:56:17 |
+----+-------------------------------------------------------------------+---------------------+
9 rows in set (0.00 sec)
検索
select id, contents->"$.color"
from json_test
order by id;
+----+---------------------+
| id | contents->"$.color" |
+----+---------------------+
| 19 | "red" |
| 20 | "green" |
| 21 | "blue" |
| 22 | "red" |
| 23 | NULL |
| 24 | NULL |
| 25 | "green" |
| 26 | "red" |
| 27 | "blue" |
+----+---------------------+
9 rows in set (0.00 sec)
または、JSON_EXTRACTを使用する
select id, JSON_EXTRACT(contents, '$.color')
from json_test
order by id;
+----+-----------------------------------+
| id | JSON_EXTRACT(contents, '$.color') |
+----+-----------------------------------+
| 19 | "red" |
| 20 | "green" |
| 21 | "blue" |
| 22 | "red" |
| 23 | NULL |
| 24 | NULL |
| 25 | "green" |
| 26 | "red" |
| 27 | "blue" |
+----+-----------------------------------+
9 rows in set (0.00 sec)
複数指定することもできる
select id, JSON_EXTRACT(contents, '$.color', '$.shape')
from json_test
order by id;
+----+----------------------------------------------+
| id | JSON_EXTRACT(contents, '$.color', '$.shape') |
+----+----------------------------------------------+
| 19 | ["red"] |
| 20 | ["green", "rectangle"] |
| 21 | ["blue", "triangle"] |
| 22 | ["red"] |
| 23 | NULL |
| 24 | ["pentagon"] |
| 25 | ["green", "rectangle"] |
| 26 | ["red", "hexagon"] |
| 27 | ["blue"] |
+----+----------------------------------------------+
9 rows in set (0.00 sec)
検索条件の指定
select id, contents->"$.color"
from json_test
where contents->"$.color" IS NOT NULL
order by id;
+----+---------------------+
| id | contents->"$.color" |
+----+---------------------+
| 19 | "red" |
| 20 | "green" |
| 21 | "blue" |
| 22 | "red" |
| 25 | "green" |
| 26 | "red" |
| 27 | "blue" |
+----+---------------------+
7 rows in set (0.00 sec)
または
select id, contents->"$.color"
from json_test
where JSON_EXTRACT(contents, '$.color') IS NOT NULL
order by id;
更新
JSON_SETを使用する
select contents from json_test where id = 27;
+-------------------+
| contents |
+-------------------+
| {"color": "blue"} |
+-------------------+
1 row in set (0.00 sec)
update json_test
set contents = JSON_SET(contents, '$.shape', 'triangle')
where id = 27;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
select contents from json_test where id = 27;
+----------------------------------------+
| contents |
+----------------------------------------+
| {"color": "blue", "shape": "triangle"} |
+----------------------------------------+
1 row in set (0.00 sec)
削除
JSON_REMOVEを使用する
update json_test
set contents = JSON_REMOVE(contents, '$.shape')
where id = 27;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
select contents from json_test where id = 27;
+-------------------+
| contents |
+-------------------+
| {"color": "blue"} |
+-------------------+
1 row in set (0.00 sec)
開発用のDB、アカウントの作成
データベース
CREATE DATABASE IF NOT EXISTS sample_db
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
;
アカウント
CREATE USER IF NOT EXISTS 'test_user'@'localhost'
IDENTIFIED BY 'test_user'
PASSWORD EXPIRE NEVER
;
権限
GRANT ALL ON sample_db.* TO 'test_user'@'localhost';
テーブル
CREATE TABLE IF NOT EXISTS memo (
id BIGINT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
done BOOLEAN DEFAULT FALSE NOT NULL,
updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (id)
) CHARACTER SET = utf8mb4, COLLATE utf8mb4_general_ci;