41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

MySQL ShellのダンプとMDSへのインポート

はじめに

今回はずっと触りたかったMySQL Shellのダンプをやりつつ、 最後は前回作ったMDS相手にインポートしてみようかと思います。

MDSにインポートするためにはociクライアントの設定や、オブジェクトストレージが必要なので、それらを途中で設定していきます。

準備

1. 手元のdbdeployerで作成したMySQL8.0.21の環境に、公式が出しているテスト用データベースたちを適当に入れていきます。

自分はgit cloneで取得しましたが、zip形式でもダウンロードは可能です。

$ git clone https://github.com/datacharmer/test_db.git

2. MySQL Shellももちろんインストールしておいてください

自分のところの環境はUbuntuとOCI側はCentOSですので、以下のとおりです。

Ubuntu

$ sudo dpkg -i /PATH/version-specific-package-name.deb -- 今時点だとmysql-apt-config_0.8.15-1_all.debが最新の様子
$ sudo apt-get update
$ sudo apt-get install mysql-shell

CentOS(前回やりましたが、一応転記)

$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$ sudo yum install mysql-shell

3. 1で取得したスキーマ達をローカルの8.0.21にインポート

$ cd test_db/
$ mysql -uroot -p -h127.0.0.1 -P8021 < employees.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < test_employees_md5.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < test_employees_sha.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < sakila/sakila-mv-schema.sql
$ mysql -uroot -p -h127.0.0.1 -P8021 < sakila/sakila-mv-data.sql
$ 
$ # 作成したスキーマを確認(testはdbdeployerで自動で作っている)
$ mysql -uroot -p -h127.0.0.1 -P8021

(root@127.0.0.1) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

MySQL ShellのdumpInstanceを使っていく

ダンプ取得で使えるものとしてdumpInstanceとdumpSchemasがあるけど、その差はスキーマ(データベース)単位でやるか否かだけの話。

dumpInstanceのほうが自分はよく使いそうだし、2つの差も引数としてスキーマ指定が絡むだけっぽいのでdumpInstanceを触っていく

dumpInstanceを試す

ダンプユーティリティのオプションについては公式ドキュメントにあるのでそれを参照するのだが、事例が少ない。

書き方で戸惑うかもしれないので試してみて実行できた書き方を以下並べてみる。

第1引数が出力先のディレクトリ名になります。(/tmp/dumpdirとか)

上書きしようとするとエラーになるので、

excludeSchemas

指定したスキーマをダンプから除外します。

例)testスキーマを除外

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir",{excludeSchemas:["test"]})
Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Preparing data dump for table `sakila`.`actor`
Writing DDL for schema `sakila`
Writing DDL for view `sakila`.`customer_list`
Writing DDL for view `sakila`.`actor_info`
Writing DDL for view `sakila`.`film_list`
Data dump for table `sakila`.`actor` will be chunked using column `actor_id`
Preparing data dump for table `sakila`.`address`
Data dump for table `sakila`.`address` will be chunked using column `address_id`
Preparing data dump for table `sakila`.`category`
Data dump for table `sakila`.`category` will be chunked using column `category_id`
Preparing data dump for table `sakila`.`city`
Data dump for table `sakila`.`city` will be chunked using column `city_id`
Preparing data dump for table `sakila`.`country`
Data dump for table `sakila`.`country` will be chunked using column `country_id`
Preparing data dump for table `sakila`.`customer`
Data dump for table `sakila`.`customer` will be chunked using column `customer_id`
Preparing data dump for table `sakila`.`film`
Data dump for table `sakila`.`film` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`film_actor`
Data dump for table `sakila`.`film_actor` will be chunked using column `actor_id`
Preparing data dump for table `sakila`.`film_category`
Data dump for table `sakila`.`film_category` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`film_text`
Writing DDL for view `sakila`.`nicer_but_slower_film_list`
Data dump for table `sakila`.`film_text` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`inventory`
Data dump for table `sakila`.`inventory` will be chunked using column `inventory_id`
Preparing data dump for table `sakila`.`language`
Writing DDL for view `sakila`.`sales_by_film_category`
Data dump for table `sakila`.`language` will be chunked using column `language_id`
Preparing data dump for table `sakila`.`payment`
Writing DDL for view `sakila`.`sales_by_store`
Writing DDL for view `sakila`.`staff_list`
Data dump for table `sakila`.`payment` will be chunked using column `payment_id`
Preparing data dump for table `sakila`.`rental`
Data dump for table `sakila`.`rental` will be chunked using column `rental_id`
Preparing data dump for table `sakila`.`staff`
Writing DDL for table `sakila`.`actor`
Data dump for table `sakila`.`staff` will be chunked using column `staff_id`
Preparing data dump for table `sakila`.`store`
Data dump for table `sakila`.`store` will be chunked using column `store_id`
Preparing data dump for table `employees`.`departments`
Data dump for table `employees`.`departments` will be chunked using column `dept_no`
Preparing data dump for table `employees`.`dept_emp`
Data dump for table `employees`.`dept_emp` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`dept_manager`
Writing DDL for table `sakila`.`address`
Data dump for table `employees`.`dept_manager` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`employees`
Writing DDL for table `sakila`.`category`
Data dump for table `employees`.`employees` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`salaries`
Data dump for table `employees`.`salaries` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`titles`
Data dump for table `employees`.`titles` will be chunked using column `emp_no`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `sakila`.`city`
Writing DDL for table `sakila`.`country`
Writing DDL for table `sakila`.`customer`
Writing DDL for table `sakila`.`film`
Writing DDL for table `sakila`.`film_actor`
Writing DDL for table `sakila`.`film_category`
Writing DDL for table `sakila`.`film_text`
Writing DDL for table `sakila`.`inventory`
Writing DDL for table `sakila`.`language`
Writing DDL for table `sakila`.`payment`
Writing DDL for table `sakila`.`rental`
Writing DDL for table `sakila`.`staff`
Writing DDL for table `sakila`.`store`
Writing DDL for schema `employees`
Writing DDL for view `employees`.`current_dept_emp`
Writing DDL for view `employees`.`dept_emp_latest_date`
Writing DDL for table `employees`.`departments`
Writing DDL for table `employees`.`dept_emp`
Writing DDL for table `employees`.`dept_manager`
Writing DDL for table `employees`.`employees`
Writing DDL for table `employees`.`salaries`
Writing DDL for table `employees`.`titles`
Data dump for table `sakila`.`actor` will be written to 1 file
Data dump for table `sakila`.`category` will be written to 1 file
Data dump for table `sakila`.`address` will be written to 1 file
Data dump for table `sakila`.`country` will be written to 1 file
Data dump for table `sakila`.`customer` will be written to 1 file
Data dump for table `sakila`.`city` will be written to 1 file
Data dump for table `sakila`.`film_actor` will be written to 1 file
Data dump for table `sakila`.`film` will be written to 1 file
Data dump for table `sakila`.`film_category` will be written to 1 file
Data dump for table `sakila`.`film_text` will be written to 1 file
Data dump for table `sakila`.`language` will be written to 1 file
Data dump for table `sakila`.`payment` will be written to 1 file
Data dump for table `sakila`.`inventory` will be written to 1 file
Data dump for table `sakila`.`rental` will be written to 1 file
Data dump for table `sakila`.`store` will be written to 1 file
Data dump for table `sakila`.`staff` will be written to 1 file
Data dump for table `employees`.`departments` will be written to 1 file
Data dump for table `employees`.`dept_manager` will be written to 1 file
Data dump for table `employees`.`dept_emp` will be written to 1 file
Data dump for table `employees`.`employees` will be written to 1 file
Data dump for table `employees`.`salaries` will be written to 1 file                                            
Data dump for table `employees`.`titles` will be written to 1 file                                               
1 thds dumping - 100% (3.97M rows / ~3.93M rows), 733.12K rows/s, 26.80 MB/s uncompressed, 7.02 MB/s compressed  
Duration: 00:00:05s                                                                                            
Schemas dumped: 2                                                                                              
Tables dumped: 22                                                                                              
Uncompressed data size: 144.53 MB                                                                              
Compressed data size: 37.75 MB                                                                                 
Compression ratio: 3.8                                                                                         
Rows written: 3966288                                                                                          
Bytes written: 37.75 MB                                                                                        
Average uncompressed throughput: 26.31 MB/s                                                                    
Average compressed throughput: 6.87 MB/s 

確かにtestスキーマに対してはバックアップを取得していないですね。

最初だけは出力結果を上記のように表示しましたが、長いんであとは省略。

基本コマンド・オプション実行例だけだしておきます。

excludeTables

任意のテーブルをダンプから除外します。

DDLだけとかデータだけとかそういうオプションはありません、とのこと。

ちなみにmysql.apply_status、 mysql.general_log、 mysql.schema、とmysql.slow_log tablesあたりのデータだけは勝手に除外されるようです。

例)test.test02テーブルを除外

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir2",{excludeTables:["test.test02"]})

ユーザ情報を除外

ユーザ関連の情報を除外します。これはcreate userやrole,grantとかそういったものを移行先に持っていかないようにするやつですね。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir3",{users:false})

メタ定義のみ

いわゆるmysqldumpでいう--no-dataですね。DDLだけ取得するやつです。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir4",{ddlOnly:true})

データオンリー

いわゆるmysqldumpでいう--no-create-infoですね。データだけ取得するやつです。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir5",{dataOnly:true})

ドライラン

要するに実際に作りはしないけど、やったらどうなるかな?ってやつ。

試しに既に作成済のダンプディレクトリに出力しようとした→ドライランなのにエラー(なかなか便利そう)

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir5",{excludeSchemas:["test"],dryRun:true})
Util.dumpInstance: Cannot proceed with the dump, '/tmp/dumpdir5' already exists at the target location /tmp/dumpdir5. (ArgumentError)

testスキーマ抜きでやってみる→実際は出力されない(OK)

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true})
/tmp$ ll 
合計 92
drwxrwxrwt 20 root    root    4096 10月  4 06:29 ./
drwxr-xr-x 26 root    root    4096  9月 23 09:59 ../
drwxr-x---  2 (user) (user) 4096 10月  4 06:21 dumpdir5/
srwxrwxrwx  1 (user) (user)    0 10月  4 06:11 mysql_sandbox8021.sock=
...

/tmp/dumpdir6がないですね。

mdsに持っていけるかチェック

エラーのオンパレードの模様…

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true, ocimds: true})
Checking for compatibility with MySQL Database Service 8.0.21
ERROR: User root@localhost is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
ERROR: User R_DO_IT_ALL@% is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
ERROR: User R_POWERFUL@% is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
NOTE: Database sakila had unsupported ENCRYPTION option commented out
ERROR: Function sakila.get_customer_balance - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Function sakila.inventory_held_by_customer - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Function sakila.inventory_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.film_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.film_not_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.rewards_report - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
NOTE: Database employees had unsupported ENCRYPTION option commented out
ERROR: Trigger sakila.customer_create_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.ins_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.upd_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.del_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.payment_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.rental_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.actor_info - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.customer_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.film_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.nicer_but_slower_film_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.sales_by_film_category - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.sales_by_store - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.staff_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View employees.current_dept_emp - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View employees.dept_emp_latest_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
Compatibility issues with MySQL Database Service 8.0.21 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL.
Util.dumpInstance: Compatibility issues were found (RuntimeError)

どうやら今のところMDSに持っていくためには変換が必要=compatibilityっていうオプションが必要な模様

compatibilityを使う

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true,ocimds:true,compatibility:["strip_definers", "strip_restricted_grants"]})
Checking for compatibility with MySQL Database Service 8.0.21
NOTE: User root@localhost had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: User R_DO_IT_ALL@% had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: User R_POWERFUL@% had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: Database sakila had unsupported ENCRYPTION option commented out
NOTE: Function sakila.get_customer_balance had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Function sakila.inventory_held_by_customer had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Function sakila.inventory_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.film_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.film_not_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.rewards_report had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Database employees had unsupported ENCRYPTION option commented out
NOTE: Trigger sakila.customer_create_date had definer clause removed
NOTE: Trigger sakila.ins_film had definer clause removed
NOTE: Trigger sakila.upd_film had definer clause removed
NOTE: Trigger sakila.del_film had definer clause removed
NOTE: Trigger sakila.payment_date had definer clause removed
NOTE: Trigger sakila.rental_date had definer clause removed
NOTE: View sakila.actor_info had definer clause removed
NOTE: View sakila.customer_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.film_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.nicer_but_slower_film_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.sales_by_film_category had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.sales_by_store had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.staff_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View employees.current_dept_emp had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View employees.dept_emp_latest_date had definer clause removed and SQL SECURITY characteristic set to INVOKER
Compatibility issues with MySQL Database Service 8.0.21 were found and repaired. Please review the changes made before loading them.
...

チェック作業が事前に入ってる。

パフォーマンスの調整したい人に

並列数2スレッド、ダンプ中のデータ読み取りスループットのスレッドあたりの1秒あたりの最大バイト数を10MBに抑えるような場合

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("/tmp/dumpdir7",threads:2,maxRate:"10M"})

他にも

まだオプションはあるんだけど、あんまり使わなそうなやつは今回省略w

知りたい方はドキュメントを熟読して僕に教えてください。

MDSに直接入れたい

ローカルにあるダンプ結果をOCIだとどうやって上げるんだろう?

というので、調べたところ、オブジェクトストレージというものがありました。(AWSで言うところのS3かな)

これ作って、そこにアップしたらええんやな?

いや、待てよ。直接MySQL Shellでそこにアップできない?

できるんです!くうぅ〜〜〜!!

準備作業

  1. オブジェクトストレージ(正確にはバケット)を作りましょう。(作り方はドキュメントをご参照ください)

  2. OCI クライアントをインストールしましょう。(これもインストールの仕方はドキュメントを参照してください)

とはいえ、OCI クライアントはちょっとハマったので簡単にメモを残しておきます

インストール

$ sudo bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" --accept-all-defaults

oci cliのconfig作成

$ oci setup config

こんな感じのやり取りがある

    This command provides a walkthrough of creating a valid CLI config file.

    The following links explain where to find the information required by this
    script:

    User API Signing Key, OCID and Tenancy OCID:

        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other

    Region:

        https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm

    General config documentation:

        https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm


Enter a location for your config [/home/(user)/.oci/config]: 
Config file: /home/(user)/.oci/config already exists. Do you want add a profile here? (If no, you will be prompted to overwrite the file) [Y/n]: n
File: /home/(user)/.oci/config already exists. Do you want to overwrite (Removes existing profiles!!!)? [y/N]: y
Enter a user OCID: ocid1.user.oc1..aaaaaaaa7oboyrvb4bqaeunr4jns7yetr5y5746noimtap2lxo5rtgq7phsq
Enter a tenancy OCID: ocid1.tenancy.oc1..aaaaaaaav7xgcb3nvajmwss3fleubvt4w3fysrtacd5icp6qopwaib3nmjwa
Enter a region (e.g. ap-chiyoda-1, ap-chuncheon-1, ap-hyderabad-1, ap-melbourne-1, ap-mumbai-1, ap-osaka-1, ap-seoul-1, ap-sydney-1, ap-tokyo-1, ca-montreal-1, ca-toronto-1, eu-amsterdam-1, eu-frankfurt-1, eu-zurich-1, me-jeddah-1, sa-saopaulo-1, uk-gov-cardiff-1, uk-gov-london-1, uk-london-1, us-ashburn-1, us-gov-ashburn-1, us-gov-chicago-1, us-gov-phoenix-1, us-langley-1, us-luke-1, us-phoenix-1, us-sanjose-1): ap-tokyo-1
Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: Y
Enter a directory for your keys to be created [/home/(user)/.oci]: 
Enter a name for your key [oci_api_key]: 
Public key written to: /home/(user)/.oci/oci_api_key_public.pem
Enter a passphrase for your private key (empty for no passphrase): 
Private key written to: /home/(user)/.oci/oci_api_key.pem
Fingerprint: 1e:2b:f5:83:93:93:9e:03:85:05:66:7f:b1:f8:ac:ca
Config written to /home/(user)/.oci/config


    If you haven't already uploaded your API Signing public key through the
    console, follow the instructions on the page linked below in the section
    'How to upload the public key':

        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2

公開鍵をアップロードする

このドキュメントを見ながらユーザー>公開キーの追加で公開鍵を登録します。

公開鍵はこんな感じで見れるかと。

cat /home/(user)/.oci/oci_api_key_public.pem

トークンの検証

oci iam region list

を実行して、以下のように出たらOK

{
  "data": [
    {
      "key": "AMS",
      "name": "eu-amsterdam-1"
    },
    {
      "key": "BOM",
      "name": "ap-mumbai-1"
    },
    {
      "key": "DXB",
      "name": "me-dubai-1"
    },
    ...
    {
      "key": "YYZ",
      "name": "ca-toronto-1"
    },
    {
      "key": "ZRH",
      "name": "eu-zurich-1"
    }
  ]
}

さっそくOCIへ吐き出してみる

osBucketNameとosNamespace

OCIのオブジェクトストレージのバケットに出力するにはこの2つのオプションが必要。

osBucketNameは文字通り、オブジェクトストレージのバケット名、osNamespaceでバケット名前空間を指定します。

 MySQL  127.0.0.1:8021 ssl  JS > util.dumpInstance("testbackup",{ocimds:true,compatibility:["strip_definers", "strip_restricted_grants"],"osBucketName": "bucket-hogehoge", "osNamespace": "xxxxxxx"})

osNamespaceは以下のコマンドで取得するか、オブジェクトストレージ>バケットの詳細>ネームスペースで確認するといいです。

oci os ns get

MDSにインポート

事前準備

OCIにインスタンスを一つ立てて、そっちにもOCI クライアントをインストールしましょう。

インポート

そしてMySQL Shellで以下のように実行すればインポートできます

 MySQL  xxxxxx:3306 ssl  JS >util.loadDump("testbackup", {osBucketName: "bucket-hogehoge", osNamespace: "xxxxxxx"})

並列度はインスタンスのCPUパワー次第で上げたら良いんじゃないかなと思いますが、僕はお試しなんでデフォルトのままでやりました。

インポートのほうのパラメータはあんまり調べませんでしたが、パフォーマンスの設定以外は基本エクスポートでやっちゃう人が多いんじゃないかな?と思って今回は手を抜きましたw

さいごに

今回はMySQL ShellとOCI両方ちょっとずつ学ぶことができました。

インポートは若干駆け足気味ですが、まあそんなに難しくないかと思って。

MySQL Shellのダンプが使えるのは8.0.21限定ですし、MDSもまだまだ検証段階って人も多いと思います。

そんな段階だからこそ、触っていざというときにどっちも使えるようになっておけば、きっと将来役に立つこと間違い無しです(?)

MySQL Database Serviceを触るまでのお話

はじめに

MDS(MySQL Database Service)が東京リージョンにも来ました!

(^O^) <祝!

というわけで、朝4時に起こされたこともあり、時間もできたんで久々にOCIを触ってみたんですが、作りたいものが簡単に作れて楽しかったです。

ただ、公式チュートリアルにMDS関連がまだ無いこと、MDS系だとlefredさんのブログとかOracleのセミナー資料しか手順でわかりやすい情報が無いのでまだまだ敷居が微妙に高い可能性もあり、初見の人がどうするの?っていうのをざっくり流れで書いてみました。

でも、細かいところは前述の公式のチュートや記事を参考にしてね。

やりたいこと

要するに、1台どっからでもインターネット上から繋げられるようなVM立てて、そのマシン経由でMySQLに繋げられるイメージ。

マイPC--->インターネット--->OCI領域[VM(Linux)--->MySQL]

作り方(超ざっくり)

1.アカウント作成

まずはなにはともあれアカウントを作る

通常、ここからOracle Cloud Freeでアカウントが作れる。

30日間有効な$300のクレジットがもらえて、MySQL DBシステムは有料なのでそこから支払われる。

と言っても、普段Auroraとか触ってる人からすると安すぎて笑うくらい。

多くの人は30日じゃ使い切れないんじゃないかな。

また、Oracle主催のセミナーとか参加すると更に+$200で合計$500のクレジットとかになることがある(それ用のURLを教えてもらえる)。

そっちからだと通常はクレカ登録があるのだが、無いので楽ちんでもある。

注意点

アカウント作成時の注意点は以下の2つ

  • クラウド・アカウント名を忘れない
  • ホーム・リージョンを日本東部(東京)にしよう

クラウド・アカウント名はログイン時に使う。

ログイン時に使うのはクラウド・アカウント名、メールアドレス、パスワードの3つ

ホーム・リージョンはせっかく日本でMDS使えるようになったー!ってなったのに、アメリカにするのもどうなん?ってことで。

まあ、アッシュバーンは空いてるなんて話もあるんで、それでも良いって人は止めませんがw

2.ログイン

チュートリアル読みながらログインしてみてください。

ちなみにクラウド・アカウント名はテナンシー名とも言われるので、どっかで出てきたら脳内変換してやってください。

クラウド・アカウントの領域を内部で区画わけするコンパートメントって概念もあるけど、個人で使う分には無視でいいかも。

整理整頓好きな人は使ってみるのもいいかも(自分は一回作って、ちょっと触って削除した)

3.VCNの作成

2同様チュートリアル読みながら作成してみてください。

チュートリアルにもありますが、VCNウィザード使ったほうが楽です。VCN名考えることくらいしかやることありませんw

パブリック・サブネットもプライベート・サブネットも作ってくれます。

プライベート・サブネットの更新でエラーが出ることがありますが、プロビジョニングの再試行で普通に作れます。

4.インスタンスの作成

チュートリアルを参考に作成してみてください。

ここに用意されているOSのイメージがデフォルトでは使え、OWindows Server以外はAlways Free対象です。

とりあえず、自分はCentOS7とOracle Linux7.8の2つを作ってみました。

シェイプ、ネットワークおよびストレージ・オプションの表示を開くと先程作ったVCNのパブリックサブネットからIPがデフォルトで割り振られているのがわかります。

接続については

  • SSHキー・ペアの生成
  • 公開キー・ファイルの選択
  • 公開キーの貼付け

がありますが、自分は公開キーを貼り付けてます。(どうせ使うPC決まってるし)

作成されたパブリックIPを使ってsshでつなぎます(以下イメージです)

接続例

$ ssh opc@xxx.xxx.xxx.xxx
The authenticity of host 'xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx)' can't be established.
ECDSA key fingerprint is SHA256:pKpvubZvr4MeMfHVHMzRJxMhRlIdpJqimTdiPD64qLk.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'xxx.xxx.xxx.xxx' (ECDSA) to the list of known hosts.
[opc@tkyk7704vm-c7 ~]$ 

5.MySQLクライアント等を入れとく

mysqlshやmysqlクライアントでDBにつなごうと思っているので、それらをインストールしておきます。

$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$ sudo yum install mysql-shell
$ sudo yum install mysql-community-client

6.プライベート・サブネットのイングレス・ルールにMySQLポート追加

パブリックIPがこれから作るMySQL DBシステムに接続できるよう、先にプライベート・サブネットのイングレス・ルールにMySQLポートの3306と33060を追加しておきます。

この作業はMySQL DBシステム作ったあとでも問題ありません。

ポート番号もMySQL DBシステムを別の番号で作るのであれば臨機応変に変えてください。

ネットワーキング>仮想クラウド・ネットワーク>作ったVCN>プライベート・サブネット>プライベート・サブネットのセキュリティ・リスト という感じでクリックしていくと、先程作ったVCNに紐づくプライベート・サブネットのイングレス・ルールの追加画面が表示されます。

ここでイングレス・ルールの追加ボタンを押して、パブリック・サブネットをソースCIDRに、宛先ポート範囲に3306,33060を入れて追加ボタンを押せばOKです。

f:id:next4us-ti:20200926173024p:plain

(ちなみにプライベート・サブネットの他のルールは消しておくのが吉です。無駄な穴は消しておきましょう)

山崎さんのスライドでもこの辺の話は記載されてますので参考にしてください。

7.MySQL DBシステムの作成

MySQL>DBシステムをクリックすると作成画面に入ります。

MySQL DBシステムの作成ボタンを押して作ります。

作り方や機能の詳細についてはここからダウンロードできる資料のほうが詳しいので僕は手抜きで説明しませんw

出来上がるまで結構かかります。10分〜15分くらいかかることも。コーヒーでも淹れてゆっくり飲みながら待つのが吉です。

8.DBへ接続

MySQLクライアントやMySQL Shellで接続してみてください。

mysql -u(DBユーザ名) -p -h(DBのエンドポイント名)

mysqlsh (DBユーザ名)@(DBのエンドポイント名)

ちゃんと入れてるのに MySQL Error 2003 (HY000): Can't connect to MySQL server が出るよーって人はイングレス・ルールを見直してみてください (入れるのはプライベート・サブネットのほうですよ!)

9.ローカルからDBを見てみたい?

そういう人はlefredさんのブログを読むと言いです。

SSHトンネルやWorkbenchでつなぐ方法があります。

(一つ前の記事にMySQL Router使うなんてなかなかトリッキーなのもありますが・・・)

コスト

一番小さいサイズとはいえ、数時間放置してて、おっといけね!と見てみたが、

f:id:next4us-ti:20200926193225p:plain

安ぅ〜〜〜!

コストの見積もり、ここでできるみたいだけど、一番小さいやつ、デフォルトだと4400円くらいなんで、$300とか、ましてや$500とか最初にも書いたけどやっぱり使い切れない気がする。

サイズ上げて、ディスクも増やしてガンガン遊んでみるかー!(家族が時間をクレナイケド・・・)

さいごに

1時間もあればこれらの作業ができます。

しかも一回VCNやインスタンスを設定してしまえば、更に時間は短縮されるので、今からVM環境作ったりするよりは圧倒的に早いです。

自分も今日作った環境でこれから色々と遊んでいこうと思います。

追記

日本語のチュートリアルが無いってだけで、ちゃんとドキュメントにはある。

docs.cloud.oracle.com

(これを朝見つけておきたかったなー)

シンキノウ×デ×シルキノウ (CREATE TABLE ... SELECT)

Myリノベ 8.0.21に参加

先日MySQL Release note でわいわい言う勉強会 8.0.21」に参加してまいりました。

今回も最新のMySQL8.0.21のリリースノートをみんなで見ながら、思いつくままに語る会で、その中で僕は相変わらずROM専ですが参加しておりました。

流れとしては坂井さんがオープニングトーク、次にまつさんがJSON_VALUE()のLTを語ってくださり、その他の機能についてはリリースノートを見ながら…という感じでした。

いずれyoutubeあたりに動画が上がると思いますので、全体が知りたい方はtwitterやslackでチェックしてるとよいかと。

8.0.21からはCREATE TABLE ... SELECT気軽に使えるぜ!

さて、その会の中でyoku0825さんがつぶやいたCREATE TABLE ... SELECTの以下の話、マイナーかもしれないけど結構驚きの変更だと思うんですよね。

On storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is now logged as one transaction in the binary log when row-based replication is in use.
Previously, it was logged as two transactions, one to create the table, and the other to insert data.
With this change, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication.
For more information, see Atomic Data Definition Statement Support.

見たことないですか?

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

GTIDを使う人は頭に叩き込まれてるはずなんです。

  • 行ベースで
  • GTID使ってる場合
  • CREATE TABLE ... SELECTはできないって

だって、ドキュメントにもあるんだから。

みんな5.6以降、GTIDを使う人はCREATE TABLE ... SELECT使わないぞ!と心に誓ってたはずなんです。

でも、

MySQL8.0.21からはフツーにレプリカに反映されるようになったんです。

試してみた

作業概要

環境構築

僕はdbdeployer使ってます。とてもかんたんに環境作れますよ。

dbdeployerの詳しい説明については適当にググってもらうとかすればいいんですが、ざっくり書くと

  1. MySQLのtarファイルゲットしてきて
  2. dbdeployerインストールして
  3. dbdeployer unpack mysql-xxxxxxxx-8.0.xx-linux-x86_64-minimal.tar.xz みたいな感じで解凍して
  4. 以下のようなコマンドでレプリケーション環境を作れます。
dbdeployer deploy replication 8.0.xx \
    --custom-role-name=R_POWERFUL \
    --custom-role-privileges='ALL PRIVILEGES' \
    --custom-role-target='*.*' \
    --custom-role-extra='WITH GRANT OPTION' \
    --default-role=R_POWERFUL \
    --bind-address=0.0.0.0 \
    --remote-access='%' \
    --gtid \
    --db-user=admin \
    --db-password=xxxxx

こんな感じで2つのソースレプリカ形式のDBのペアを用意しました。

$ dbdeployer sandboxes
 rsandbox_8_0_20          :   master-slave           8.0.20   [21021 31021 21022 31022 21023 31023 ]
 rsandbox_8_0_21          :   master-slave           8.0.21   [21122 31122 21123 31123 21124 31124 ]

検証開始

そして、2つのバージョンで比較するんですが、やることはかんたん。

  1. テーブル(t1)を作って
  2. テーブルに適当にデータ入れて
  3. 一応テーブルにデータ入ったこと確認して
  4. そのテーブルからデータコピーをCREATE TABLE ...SELECT構文でやって
  5. レプリカ側のほうにあるか確認

というだけの作業。

GTIDの確認

こんな感じでGTIDモードはいずれもONです

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 -e "show variables like 'gtid_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 -e "show variables like 'gtid_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+

MySQL8.0.20

ソース(マスター)側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, 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 '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> CREATE TABLE `t1` (
    ->    `id` int(11) NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.04 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
(root@127.0.0.1) [test]> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

ERROR 1786が出ますね。

当然ソース・レプリカいずれのインスタンスにもt2テーブルは作られていません。

これを8.0.21でもやります。

MySQL8.0.21

ソース(マスター)側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, 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 '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> CREATE TABLE `t1` (
    ->    `id` int(11) NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

エラーが出ない!

んじゃ、レプリカ側はどうなってるかな?

レプリカ(スレーブ)側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21123 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, 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 '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

できてるぅ!ヒャッホー!

binlogの比較

GITDがONだと比較できないので、OFFにしてmysqlbinlogで覗いてみる。

MySQL8.0.20

# at 9418
#200814 18:50:04 server id 100  end_log_pos 9543 CRC32 0xccceb3e3   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398604/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0'
)
/*!*/;
# at 9543
#200814 18:50:04 server id 100  end_log_pos 9622 CRC32 0xf3e5f383   Anonymous_GTID  last_committed=38   sequence_number=39  rbr_only=yes    original_committed_timestamp=1597398604099299   immediate_commit_timestamp=1597398604099299 transaction_length=283
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST)
# immediate_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST)
/*!80001 SET @@session.original_commit_timestamp=1597398604099299*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 9622
#200814 18:50:04 server id 100  end_log_pos 9697 CRC32 0x796b6528   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398604/*!*/;
BEGIN
/*!*/;
# at 9697
#200814 18:50:04 server id 100  end_log_pos 9745 CRC32 0x1fde1b11   Table_map: `test`.`t2` mapped to number 112
# at 9745
#200814 18:50:04 server id 100  end_log_pos 9795 CRC32 0xc4bf3987   Write_rows: table id 112 flags: STMT_END_F

BINLOG '
TF42XxNkAAAAMAAAABEmAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQARG94f
TF42Xx5kAAAAMgAAAEMmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAIc5v8Q=
'/*!*/;
# at 9795
#200814 18:50:04 server id 100  end_log_pos 9826 CRC32 0xbc8b4b0a   Xid = 71
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

MySQL8.0.21

# at 9496
#200814 18:47:28 server id 100  end_log_pos 9639 CRC32 0xe24b1dc8   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398448/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0'
) START TRANSACTION
/*!*/;
# at 9639
#200814 18:47:28 server id 100  end_log_pos 9687 CRC32 0x4972ee4a   Table_map: `test`.`t2` mapped to number 112
# at 9687
#200814 18:47:28 server id 100  end_log_pos 9737 CRC32 0x56fa2558   Write_rows: table id 112 flags: STMT_END_F

BINLOG '
sF02XxNkAAAAMAAAANclAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQBK7nJJ
sF02Xx5kAAAAMgAAAAkmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAFgl+lY=
'/*!*/;
# at 9737
#200814 18:47:28 server id 100  end_log_pos 9768 CRC32 0xa09930d6   Xid = 71
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

明らかに8.0.21のほうが短いね。

読み解いたわけじゃないけど、雰囲気は感じる。

所感

リリースノートではサラッと一行で書かれてるけど、結構な変更だと思いません?

GTIDが無効な場合でもクラッシュ時とかにレプリカにはテーブルはあるけどデータが無いとか無くなるわけだし、

GTIDが有効な場合でもエラーにならずにCREATE TABLE ... SELECTが使えるようになったわけです。

リリースノートをみんなで眺めることで、そういう知見が多くの人の目に止まったんです。

素敵な会ですね!

この話以外にも気になる機能について色々語られてました。

非公式の何かがちょろっと配布されたりして、意外と録画されていないところも楽しい勉強会w

みなさんも次回は参加してみては?

(今日はHunter×Hunter アニメ版のようなタイトルの付け方してみました)

クエリーキャッシュを有効にしたけりゃサーバー再起動

え?何当たり前のこと言ってるの?

そう思われた貴方、クエリキャッシュをちゃんと使うか使わないか最初に考えている偉い人ですね。

query_cache_typeをON(1)にするとクエリーキャッシュが有効になるんですが、これ、ドキュメントを読むと少なくともMySQL5.6以降は動的に変更(SET GLOBAL)できそうに見えます。

f:id:next4us-ti:20200719174422p:plain

5.5でもできそうに見える)

ところが、有効なのを無効にはできるんですが、無効な状態を有効にはできないんです。

試してみた

my.cnfでまず有効にした状態で起動してみます。

$ grep query_cache_type ./msb_5_7_30/my.sandbox.cnf
query_cache_type=1
$ ./msb_5_7_30/restart
stop /home/takiida/sandboxes/msb_5_7_30
. sandbox server started
$ mysql -uroot -pxxxxx -h127.0.0.1 -P5730
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2020, 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 '\h' for help. Type '\c' to clear the current input statement.

有効な状態から無効に変更することはSET GLOBAL句で可能です。

(root@127.0.0.1) [(none)]> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.01 sec)

(root@127.0.0.1) [(none)]> set global query_cache_type = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.01 sec)

(root@127.0.0.1) [(none)]> ^DBye

ところが

クエリキャッシュを無効にして再起動します。

$ grep query_cache_type ./msb_5_7_30/my.sandbox.cnf
query_cache_type=0
$ ./msb_5_7_30/restart
stop /home/takiida/sandboxes/msb_5_7_30
. sandbox server started
$ mysql -uroot -pxxxxx -h127.0.0.1 -P5730
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2020, 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 '\h' for help. Type '\c' to clear the current input statement.

無効な状態から有効にしようとすると怒られる!

(root@127.0.0.1) [(none)]> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> set global query_cache_type = 1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

query_cache_typeを1にするんならサーバー再起動しろや!と。

クエリキャッシュ、最初から有効か、無効の環境しか使ったこと無いから知らんかった。

ドキュメントが微妙ですね…

ドキュメント的に優しくないので直るといいなぁ、と思うのでちょっとプッシュしておこうかと思います。 (まあ、MySQL8.0で廃止されたクエリーキャッシュをあとから有効にしたいとかそんなにニーズが無いから放置されてたのかもしれませんが…)

MySQL5.xではデッドロックだけど8.0では死なないよ

はじめに

タイトル通り、MySQL5.x系だとデッドロックになるんだけど、 MySQL8.0だとロック機構が変わってデッドロックにならないよ という組み合わせのお話。

僕はこの話をどっかで見た記憶が無かった(忘れた?)ので 教えてもらったとき結構驚いたんだけど、GA(8.0.11)では 既にこれがあったんで、まあ古い話なんだと思うし、 何をイマサラなのかもしれないので、ご存じの方は笑ってやってください。

ちなみに、REPEATABLE READでのお話なので、 READ COMMITTEDの場合は5系でもデッドロックにはなりまへん。

検証

今回やるのはこういうこと

  1. 最初のトランザクション(以下Tx1)でトランザクション開始、共有(S)ロックで全レコードを参照。
  2. 別のトランザクション(以下Tx2)でトランザクション開始、INSERTによる排他(X)ロック
  3. Tx1でINSERTによる排他(X)ロック

結論を先にいうと、3による結果がMySQL5.xではデッドロック発生(Tx1のINSERT死亡)、 MySQL8.0ではすんなりTx1のINSERTが通る(Tx2は待ちのまま)。

以下は自分で試してみたい人・どういうふうになるのか知りたい人のため。

準備

MySQL5.7(今回は.29を使いました)とMySQL8.0.11で以下のようなテーブルを作成する。

データベース作成

とりあえずいつものtestスキーマ

(root@127.0.0.1) [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)

testにログイン

(root@127.0.0.1) [(none)]> use test;
Database changed

テーブル作成

とりあえずのtテーブル(とりあえずすぎるネーミング)

(root@127.0.0.1) [test]> CREATE TABLE `t` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)

1行インサート

(root@127.0.0.1) [test]> insert t select null;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [test]> select * from t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

ここまでで準備はOK

MySQL5.x系(デッドロックになるパターン)

Tx1で共有ロック

$ mysql -uroot -pxxxxxx -h127.0.0.1 -P5729 test
mysql: [Warning] Using a password on the command line interface can be insecure.
…
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> begin; select * from t where id >=1 lock in share mode;
Query OK, 0 rows affected (0.00 sec)

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Tx2で排他ロック

$ mysql -uroot -pxxxxxx -h127.0.0.1 -P5729 test
mysql: [Warning] Using a password on the command line interface can be insecure.
…
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> begin; insert into t select null;
Query OK, 0 rows affected (0.00 sec)

beginは通ったが、insertは待たされている。

Tx1でINSERTするとデッドロック発生

違和感はあるものの、MySQLのRRでのロックから考えると納得はする。(MySQLにマヒしてる?)

(root@127.0.0.1) [test]> insert into t select null;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

とはいえ、別トランザクションでロックかけられて、自分のトランザクションで知らずにデッドロックとかやはり悲しいものがある。

それがMySQL8.0では改善していた!

MySQL8.0系(デッドロックにならないパターン)

Tx1で共有ロック

$ mysql -uroot -pxxxxx -h127.0.0.1 -P8011 test
mysql: [Warning] Using a password on the command line interface can be insecure.
…
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> begin; select * from t where id >=1 lock in share mode;
Query OK, 0 rows affected (0.00 sec)

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Tx2で排他ロック

$ mysql -uroot -pxxxxx -h127.0.0.1 -P8011 test
mysql: [Warning] Using a password on the command line interface can be insecure.
…
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> begin; insert into t select null;
Query OK, 0 rows affected (0.00 sec)

beginは通ったが、insertは待たされている。 ここまで全く同じだが、次が違う

Tx1ではTx2の排他ロックを気にしない

(root@127.0.0.1) [test]> insert t select null;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [test]> select * from t;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

でも、ちゃんと「id=2」はTx2が抑えているのを考慮している!

Tx2は待ち疲れでタイムアウト

でも、Tx1がいつまでもコミットしてくれないと、Tx2はタイムアウトしちゃいますw

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MySQL8.0のロック、だいぶ賢い!

逆に変わりすぎてて、こことかに明記されててもおかしくないと思ったんだが…。

追記(変わった理由)

この記事書いたとき気づいてなかったんだけど、innodb_autoinc_lock_modeがデフォルト値1から2に変わったことが理由ですね。

パラメータの差異確認して、かつtom__boさんのWeeklyのコメントも見ておそらくこれやなと思ってたけど、検証するのサボってましたw

innodb_autoinc_lock_modeについては第49回のMySQL道普請を参考にすると良いです。

MySQL5.7の段階でbinlog-formatがROWに変わってるのでinnodb_autoinc_lock_modeのデフォルトも変わってても良かった気がする。

パーティションほとんどわからんので触ってみた

パーティショニングしたテーブルの確認方法

MySQL触ってて、実は仕事上では一度も触ったことがなかったパーティショニング。

(だって、色んな人が使えないって言ってたんだもん)

だけど、意外と世の中使ってる人は多いので今後のためにもパーティショニングしたテーブルの確認方法を色々見てみました。

まずはパーティションテーブル作る

ドキュメントからテーブルを拝借しました。

パーティションタイプはレンジ、リスト、ハッシュ、キーの4つがあるが、今回はハッシュにしました。 (特に意味はないです。適当に選んだだけです)

mysql [localhost:8020] {admin} (test) > CREATE TABLE ti (
    ->     id INT,
    ->     amount DECIMAL(7 , 2 ),
    ->     tr_date DATE
    -> )  ENGINE=INNODB PARTITION BY HASH (MONTH(tr_date)) PARTITIONS 6;
Query OK, 0 rows affected (0.09 sec)

適当に何件か入れてみる

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (1,
    -> 100,
    -> '2020-01-01');
Query OK, 1 row affected (0.02 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (1,
    -> 101,
    -> '2020-01-02');
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (2,
    -> 200,
    -> '2020-02-02');
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (2,
    -> 201,
    -> '2020-02-01');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (3,
    -> 301,
    -> '2020-03-01');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (4,
    -> 400,
    -> '2020-04-01');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (5,
    -> 500,
    -> '2020-05-01');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti`
    -> (`id`,
    -> `amount`,
    -> `tr_date`)
    -> VALUES
    -> (6,
    -> 600,
    -> '2020-06-01');
Query OK, 1 row affected (0.01 sec)

6件ほど入れてみました。

パーティションテーブルの確認

SHOW CREATE TABLE

SHOW CREATE TABLEではパーティション化されたテーブルの作成に使用されたパーティショニング句を表示します。

mysql [localhost:8020] {admin} (test) > show create table `test`.`ti`\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int DEFAULT NULL,
  `amount` decimal(7,2) DEFAULT NULL,
  `tr_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (month(`tr_date`))
PARTITIONS 6 */
1 row in set (0.00 sec)

SHOW TABLE STATUS

SHOW TABLE STATUSではテーブルがパーティション化されているかどうかを判別します。

mysql [localhost:8020] {admin} (test) > show table status like 'ti'\G
*************************** 1. row ***************************
           Name: ti
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 12288
    Data_length: 98304
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-06-19 21:31:01
    Update_time: 2020-06-19 21:34:08
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: partitioned
        Comment: 
1 row in set (0.01 sec)

Create_optionsがpartitionedとなっているので、パーティション化されているテーブルです、と。

まあ、ここではただそれだけなんですねw

information_schema.partitions

I_Sのpartitionsビューでも確認できます。 ここではパーティションごとに行が作られますので、先のshow create tableよりも詳細な情報が得られます。

mysql [localhost:8020] {admin} (test) > select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 21:31:01
                  UPDATE_TIME: 2020-06-19 21:34:08
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 21:31:01
                  UPDATE_TIME: 2020-06-19 21:32:58
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p2
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 21:31:01
                  UPDATE_TIME: 2020-06-19 21:33:21
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 4. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p3
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 21:31:01
                  UPDATE_TIME: 2020-06-19 21:33:31
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 5. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p4
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 5
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 21:31:01
                  UPDATE_TIME: 2020-06-19 21:33:45
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 6. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p5
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 6
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 21:31:01
                  UPDATE_TIME: 2020-06-19 21:33:55
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
6 rows in set (0.00 sec)

パーティションのタイプもわかりますし、最後にデータが更新された時間やどのパーティションに何件入ってるかなどもわかりますね。

EXPLAIN

EXPLAINで指定された SELECT によってどのパーティションが使用されているかを確認することができます。

mysql [localhost:8020] {admin} (test) > EXPLAIN select * from `test`.`ti`\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ti
   partitions: p0,p1,p2,p3,p4,p5
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.02 sec)

partitions列に作られたパーティションが表示されていますね。

mysql [localhost:8020] {admin} (test) > EXPLAIN select * from `test`.`ti` where tr_date  = '2020-01-01';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ti
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

WHERE句に条件を入れると、使用されたパーティションだけが表示されました。

ALTER TABLE ...COALESCE PARTITION

昔レンジパーティションの記事を読んだときにalter table drop partitionってあったな、と思ったらドキュメント

DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see later in this section).

(DROP PARTITIONはRANGE または LIST パーティションでのみ使え、HASH または KEY パーティションに使用することはできません。HASH または KEY パーティションの場合は代わりに COALESCE PARTITION を使用します。)

とあったのでALTER TABLE ...COALESCE PARTITIONでパーティションを減らしました。

ちなみにCOALESCE PARTITION で指定する数字は減らすパーティション数なのでご注意を。

試しにやってみます

mysql [localhost:8020] {admin} (test) > alter table ti drop partition p0;
ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

mysql [localhost:8020] {admin} (test) > ALTER TABLE ti COALESCE PARTITION 4;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

さて、確認してみましょう。

SHOW CREATE TABLE

mysql [localhost:8020] {admin} (test) > show create table `test`.`ti`\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int DEFAULT NULL,
  `amount` decimal(7,2) DEFAULT NULL,
  `tr_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (month(`tr_date`))
PARTITIONS 2 */
1 row in set (0.00 sec)

4つ減らしたので2になってます。

SHOW TABLE STATUS

まあ、これは変わりませんねw

mysql [localhost:8020] {admin} (test) > show table status like 'ti'\G
*************************** 1. row ***************************
           Name: ti
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 12288
    Data_length: 98304
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-06-19 22:27:58
    Update_time: 2020-06-19 21:34:08
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: partitioned
        Comment: 
1 row in set (0.00 sec)

information_schema.partitions

mysql [localhost:8020] {admin} (test) >  select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 22:27:58
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-19 22:27:58
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)

TABLE_ROWSが0になってますね・・・

ドキュメントには

The data contained in the last number partitions will be merged into the remaining partitions. In this case, partitions 4 and 5 will be merged into the first 4 partitions (the partitions numbered 0, 1, 2, and 3).

とあるんで、これらに入ると思ったんですがふっとんだんでしょうか?

mysql [localhost:8020] {admin} (test) > select * from ti;
+------+--------+------------+
| id   | amount | tr_date    |
+------+--------+------------+
|    6 | 600.00 | 2020-06-01 |
|    2 | 200.00 | 2020-02-02 |
|    2 | 201.00 | 2020-02-01 |
|    4 | 400.00 | 2020-04-01 |
|    1 | 100.00 | 2020-01-01 |
|    1 | 101.00 | 2020-01-02 |
|    3 | 301.00 | 2020-03-01 |
|    5 | 500.00 | 2020-05-01 |
+------+--------+------------+
8 rows in set (0.00 sec)

ありますねえ…。

この後、再起動したり、データ入れてみたりしましたが、今まで入ってた分はTABLE_ROWSに戻らず。

EXPLAIN

(root@localhost) [test]> EXPLAIN select * from `test`.`ti`\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ti
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.02 sec)

partitionsはp0,p1 だけになってますね。

こちらもrowsが1(試しに後から入れた1件)のみ…。

どうしても行数を復活させたい

というわけで試すことにしました。

ALTER TABLE ... REBUILD PARTITION;

ALTER TABLE ... REBUILD PARTITIONでパーティションを再構築してみます。

ドキュメントでは

Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.

パーティションに格納されているすべてのレコードを削除してからそれらを再度挿入することと同じ効果があります。これはデフラグに役立つことがあります。)

とあるので、再構築すれば読めるようになるのでは?

(root@localhost) [test]> alter table ti rebuild partition p0,p1;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
(root@localhost) [test]> select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-20 08:47:54
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-20 08:47:54
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.01 sec)

残念、再構築では入りませんでした。

ALTER TABLE ... OPTIMIZE PARTITION

次にパーティションの最適化をやってみます。

ドキュメントでは

ALTER TABLE ... OPTIMIZE PARTITION を使用して、未使用領域を解放したりパーティションデータファイルをデフラグしたりできます。 (パーティションから多数の行を削除した場合、または可変長行を持つ (つまり、VARCHAR、BLOB、または TEXT カラムを持つ) パーティション化されたテーブルに多くの変更を行なった場合は、 ALTER TABLE ... OPTIMIZE PARTITION を使用して、未使用領域を解放したりパーティションデータファイルをデフラグしたりできます。)

とあるので、少しイメージと違うような気持ちもしますが試してみましょう。

(root@localhost) [test]> ALTER TABLE ti OPTIMIZE PARTITION p0, p1;
+---------+----------+----------+---------------------------------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                                                    |
+---------+----------+----------+---------------------------------------------------------------------------------------------+
| test.ti | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| test.ti | optimize | status   | OK                                                                                          |
+---------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (0.08 sec)

1パーティションに対してはできないよ!という注意が見えますが、ちゃんと全パーティションを選択しているのでOKとなってます。

(root@localhost) [test]> select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 5
               AVG_ROW_LENGTH: 3276
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-20 08:48:16
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: ti
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: month(`tr_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 4
               AVG_ROW_LENGTH: 4096
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-06-20 08:48:16
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)

おお、TABLE_ROWSが見事に見えるようになりました。

正解はOPTIMIZE PARTITIONだったようです。

(root@localhost) [test]> EXPLAIN select * from `test`.`ti`\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ti
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAINでもrowsが 9に見えてます。

最後に

今日はパーティションと少し戯れてみました。

パーティションの数変更した後に確認しようとしたときに行数が狂っちゃう(0になっちゃう)のはイケてないと思うんだけど、直し方はありました。

(バグ登録されてるのかな?あとで見てみよう)

まだ触りたてなので、他の3つのパーティションタイプやそれぞれでのALTER TABLE ... ANALYZE PARTITION、CHECK PARTITION、REPAIR PARTITION、REMOVE PARTITIONINGあたりも触ってないのでそのへんも気が向いたら手を出していきます。

(でも、そもそも使うとしたらみなさんレンジやリストのタイプなのかしら?)

あ、あと、MySQL5.6ではEXPLAINではなくて、EXPLAIN PARTITIONSなのでお間違えなく

(root@127.0.0.1) [test]> EXPLAIN PARTITIONS select * from ti\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ti
   partitions: p0,p1,p2,p3,p4,p5
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: NULL
1 row in set (0.00 sec)

参考

dev.mysql.com

dev.mysql.com

dev.mysql.com

【ポエムというか愚痴】おれはソネットをやめるぞ!ジョジョーーーーーッ‼

全くMySQLに関係ない、でも、きっかけは家で仕事するのに不都合だったから起こった身の上の話

光を超越する!

オトコは悩んでいた。

家は東京のとある集合住宅、150以上の世帯が住むマンション。

その家の光回線が遅いのである。

回線速度を測ると、1Gbpsをうたっているはずの光回線がその100分の1も出ないのである。

ひどいときには1000分の1になるときもあった。

オトコの仕事はサポート業務。

お客さんがトラブルを解消したい場合や、知見を得たいというときにそれを補助する仕事だ。

トラブルの時はログファイル等を添付していることもある。

あるバージョンのアプリケーションで問題が発生しているといったときはそのバージョンのアプリケーションをダウンロードして検証しなければならない。

オトコはそれまで家で仕事をしたことが無かった。

仕事は会社でするもの。

家でするのはあくまで緊急時、特に深夜のみだった。

しかし、コロナウイルスが家で仕事を強要した。

もちろん、それはオトコだけでなく、オトコの妻も、マンションに住む全ての社会人がそうなった。

そして、子供たちも学校や幼稚園・保育園に行けず、家にいることになった。

彼らは何をしただろう。

恐らく、オトコの家と同じく子供はインターネット経由で動画を楽しみ、親はリモートワークでインターネットを使ったのだろう。

同じ時間帯にそれらがマンションの半分、いや3分の1の世帯で起こったとしたら…。

マンションは築40年を超える物件だ。

今流行りの1Gbpsの光回線を入れることはできないらしい。

光と言っても100Mbpsが最大の貧弱な回線だ。

そこに一斉にインターネットを使う状況ができれば、それはそうなるのは明らかである。

オトコは決断した。

光をやめて、無線にしよう!

携帯のほうが速度も出ている。

速度の出ない光を使い続けるくらいなら無線でその数倍~数十倍の速度でやったほうが仕事がはかどるハズだ!

プロバイダーはそのままに、光コラボからWiMAX

無駄にダラダラと文章を書いたけど、要するにコロナのせいでマンションの有線がクッソ遅すぎて仕事にならないから無線に切り替えようと考えたんです。

そしてその際、プロバイダーとして使ってたSo-netWiMAXの取り扱いがあったので、光回線を解約してWiMAXの契約をポチリました。

光回線は月末で廃線になる予定なので、それまでは使い、WiMAXとの並行運用で速度検証を行ったりしました。

WiMAXは3日後くらいに来たので、1週間くらい並行稼働してましたが、光が1Mbps~10Mbpsなのに対し、WiMAXが10Mbps~80Mbpsと約10倍の速度が出ていて、これなら自宅でも多少快適になるだろうと喜んでいました。

解約料が7500円取られるらしいけど、それくらいなら我慢しよう、そう思ってました。

ところが、

突然WiMAXがつながらなくなる

子供が動画を見ていたところ、急にインターネットが使えなくなりました。

時間は19時頃。

「何事だ?」

電源を入れなおしても、リセットしても通信ができません。

機器の故障か?と思いましたが、直接その機器にはアクセスできるようなのでそうではない模様。

「まさか!?」

So-netの契約を見に行きました。

退会処理が完了しました(5月末日付け退会)

いやいやいや、どういうこと???

確認を取ろうと思いましたが、コロナの状況下でプロバイダーのサポート業務も18時までとなっていました。

過去のメールを見ても解約になってるのは光回線のみ。WiMAXの文字はありません。

ただ、光回線のメールに不穏な文字が。

「退会した場合にもこのメールは送られます」

至急確認したいし、明日仕事どうすれば?

いや、日中ならまだしも、そっちの定時後にやられたら確認のしようが無いじゃないか…。

自分一人ならテザリングでなんとかしのげますが、嫁も家にいる日、時間帯があるので至急復旧が必要です。

会社には無理やり1日休みをもらい、朝から確認と、最悪別契約を取りに行く準備を前日に行いました。

翌朝9時からプロバイダーに確認

翌朝、朝9時にPCの前に座り、So-netのサポートに問い合わせました。

サポートの人と話すことで、以下のようなことが分かりました。

  • WiMAXの契約前に光回線を解約している。
  • 自分は光回線の「オプション」としてWiMAXを契約していた。(本人は単にマイページがあるので、「サービスを利用中の場合」のところから契約をした)
  • 光回線が解約された時、退会も同時に行われた。
  • WiMAXはオプション扱いなので同時に解約された。
  • コース変更というものがあったが、それに気づいてなかった
  • 退会されたのでWiMAXの復旧は不可(少なくとも1日以内では)。
  • WiMAXの解約料も当然発生。

光回線を主回線とし、WiMAXはオプションで従という関係なのは100歩譲ってわかるとしても、

主回線の解約後に従のオプション回線が契約できて、主が解約・退会になったから従も併せて解約…

って常識で考えたらおかしいって思わないかな?

せめてコース変更を自動で切り替えてくれればこんなこと起こらなかったのに。

まあ、自分もサポート業務やってるので、サポートの人に言ってもかわいそうなんで、もめずに解約料の話も許容して引き下がりましたよ。

(でも、納得は行ってないから書くけどね!)

1週間程度しか使ってないもの解約料と登録手数料に22000円、そして解約した光の7500円かかって、その他諸々で3万超えてます。

そして、急遽回線引かなければいけなくなったんで、お店が開いてて今日SIMもらえるってなると限られてて、結局WiMAXしか選択肢なかったし。

かつ、SIMだけっていうのが無くてルーターも買わなきゃで、また出費(約2万)…。

合計5万程度余計に払うことに。

復旧作業に4万強使いました。

それでもポジティブに考えてみる

無駄にお金をドブに捨ててるので、何を考えてみても焼け石に水なのですが、それでも無理やりひねり出すと、

  • 公式で契約したことで携帯代がこれから毎月300円安くなる(手続きもやってもらった)
  • ルーターをHOME用と持ち歩き用両方手に入れた
  • 明日は仕事ができる!(急遽休んでごめんなさい)
  • 体調悪かったので、時間も十分にあるので病院に行ける(これから行く)
  • So-netは契約のシステムがザルだったのを忘れてた自分が悪い(そういえばそうだった)
  • 子供が大きくなったら、このマンションは貸して、田舎に引っ越そうという気持ちが固まった
  • 全てコロナのせい

全てコロナが悪いんですよ!

早く正常な世の中に戻ってほしいですね。