MySQL 5.6 Developer試験公式「試験内容チェックリスト」

https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com

MySQL 5.6 リファレンスマニュアル

memcached API

memcached API

dev.mysql.com

【補】環境構築

こんな感じに環境を用意する
5.6だとすぐ使えなそうだったので5.7にしちゃった

docker-compose.yml

version: "3"
services:
  mysql5.7:
    build:
      context: ./image/5.7
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
    tty: true

Dockerfile

FROM mysql:5.7

ADD ./1_setup_memcacmed.sh  /docker-entrypoint-initdb.d

1_setup_memcached.sh

#!/bin/sh

echo 'setup'
mysql -u root -proot -vvv < /usr/share/mysql/innodb_memcached_config.sql
echo 'setup done'
echo 'install'
mysql -u root -proot -vvv <<EOF
INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
SELECT 'OK' FROM DUAL;
EOF
echo 'install done'

起動ログ

...
mysql5.7_1  | 2020-06-06T00:09:22.282706Z 0 [Note] mysqld: ready for connections.
mysql5.7_1  | Version: '5.7.27'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server (GPL)
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
mysql5.7_1  | 
mysql5.7_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1_setup_memcacmed.sh
mysql5.7_1  | setup
mysql5.7_1  | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql5.7_1  | --------------
mysql5.7_1  | create database innodb_memcache
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE  TABLE IF NOT EXISTS `cache_policies` (
mysql5.7_1  |   `policy_name` VARCHAR(40) PRIMARY KEY,
mysql5.7_1  |   `get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1  |    NOT NULL ,
mysql5.7_1  |   `set_policy` ENUM('innodb_only', 'cache_only','caching','disabled')
mysql5.7_1  |    NOT NULL ,
mysql5.7_1  |   `delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1  |    NOT NULL,
mysql5.7_1  |   `flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1  |    NOT NULL
mysql5.7_1  | ) ENGINE = innodb
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE  TABLE IF NOT EXISTS `containers` (
mysql5.7_1  |   `name` varchar(50) not null primary key,
mysql5.7_1  |   `db_schema` VARCHAR(250) NOT NULL,
mysql5.7_1  |   `db_table` VARCHAR(250) NOT NULL,
mysql5.7_1  |   `key_columns` VARCHAR(250) NOT NULL,
mysql5.7_1  |   `value_columns` VARCHAR(250),
mysql5.7_1  |   `flags` VARCHAR(250) NOT NULL DEFAULT "0",
mysql5.7_1  |   `cas_column` VARCHAR(250),
mysql5.7_1  |   `expire_time_column` VARCHAR(250),
mysql5.7_1  |   `unique_idx_name_on_key` VARCHAR(250) NOT NULL
mysql5.7_1  | ) ENGINE = InnoDB
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.02 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE  TABLE IF NOT EXISTS `config_options` (
mysql5.7_1  |   `name` varchar(50) not null primary key,
mysql5.7_1  |   `value` varchar(50)) ENGINE = InnoDB
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO containers VALUES ("aaa", "test", "demo_test",
mysql5.7_1  |                  "c1", "c2",  "c3", "c4", "c5", "PRIMARY")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
mysql5.7_1  |                 "innodb_only", "innodb_only", "innodb_only")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO config_options VALUES("separator", "|")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO config_options VALUES("table_map_delimiter", ".")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE DATABASE IF NOT EXISTS test
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE TABLE demo_test (c1 VARCHAR(32),
mysql5.7_1  |           c2 VARCHAR(1024),
mysql5.7_1  |           c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
mysql5.7_1  | ENGINE = INNODB
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0)
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | Bye
mysql5.7_1  | setup done
mysql5.7_1  | install
mysql5.7_1  | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql5.7_1  | --------------
mysql5.7_1  | INSTALL PLUGIN daemon_memcached soname "libmemcached.so"
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | InnoDB MEMCACHED: Memcached uses atomic increment 
mysql5.7_1  | Query OK, 0 rows affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | SELECT 'OK' FROM DUAL
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | +----+
mysql5.7_1  | | OK |
mysql5.7_1  | +----+
mysql5.7_1  | | OK |
mysql5.7_1  | +----+
mysql5.7_1  | 1 row in set (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | Bye
mysql5.7_1  | install done
mysql5.7_1  | 
...

やっていること
- innodb_memcacheデータベースのセットアップ
  - memcached APIに必要なコンフィグテーブルのセットアップ
    - cache_policies
    - containers
    - config_options
- テストデータベースtestのセットアップ
コンフィグテーブルとデモテーブルの確認

mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.00 sec)


mysql> SELECT * FROM test.demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)

innodb_memcache.containersには、どのスキーマのどのテーブルをどのように解釈するかの情報が格納される
test.demo_testの定義と照らす

mysql> DESC test.demo_test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c1    | varchar(32)         | NO   | PRI | NULL    |       |
| c2    | varchar(1024)       | YES  |     | NULL    |       |
| c3    | int(11)             | YES  |     | NULL    |       |
| c4    | bigint(20) unsigned | YES  |     | NULL    |       |
| c5    | int(11)             | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5

PK, VARCHARなキーが必要
3つの管理カラムが必要
- フラグ
- 連番
- expiration timestamp

動作確認

telnetでササッと
- mysqlコンテナにはtelnetが入っていないので適宜aptで入れる

root@db45fbb2747e:/# telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

AAキーによるデータ取得

get AA
VALUE AA 8 12
HELLO, HELLO
END

BBキーによるデータ挿入

set BB 10 0 16
GOODBYE, GOODBYE
STORED

SQLで読み取ってみる

mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------+
| c1 | c2               | c3   | c4   | c5   |
+----+------------------+------+------+------+
| AA | HELLO, HELLO     |    8 |    0 |    0 |
| BB | GOODBYE, GOODBYE |   10 |    1 |    0 |
+----+------------------+------+------+------+
2 rows in set (0.00 sec)

memcached APIのsetの意味

set BB 10 0 16
GOODBYE, GOODBYE

BBキーのレコードの値にGOODBYE, GOODBYEをセット
10: 操作のフラグ
- memcachedには無視される
0: expiration time (TTL)
16: ブロックバイト長
expiration timeのテスト

set CC 10 10 15
EXPIRATION DEMO
STORED

10秒すると読めなくなる

get CC
VALUE CC 10 15
EXPIRATION DEMO
END

get CC
VALUE CC 10 15
EXPIRATION DEMO
END

get CC
END

SQLで読むと

mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------------+
| c1 | c2               | c3   | c4   | c5         |
+----+------------------+------+------+------------+
| AA | HELLO, HELLO     |    8 |    0 |          0 |
| BB | GOODBYE, GOODBYE |   10 |    1 |          0 |
| CC | EXPIRATION DEMO  |   10 |    2 | 1591403745 |
+----+------------------+------+------+------------+
3 rows in set (0.00 sec)

c4: 連番
- setするたびに増える
c5: EXPIREする時刻のUNIXタイムスタンプ

【補】memcached APIのトランザクション分離レベル

READ UNCOMMITTED.
- Dirty Readがおきるので注意する

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.demo_test VALUES('DD', 'INSERTED VIA SQL', 12, 8, 0);
Query OK, 1 row affected (0.00 sec)

commitしない状態でmemcached APIからgetで読める

get DD
VALUE DD 12 16
INSERTED VIA SQL
END

rollbackするともちろん読めなくなる

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

get DD
END

勉強日記

チラ裏

MySQL 5.6 Developer試験対策 9 MySQL アプリケーションの開発 -- memcached API

memcached API

【補】環境構築

動作確認

【補】memcached APIのトランザクション分離レベル