MySQLのMecabで2020年7月以降の高速バス関連でつぶやかれているワードをピックアップしてみた

含まれている単語からノイズを除外して10000回以上つぶやかれている単語をピックアップして、その中から、再開という単語を選択して確認してみた。

NO.1ツイート


*************************** 1. row ***************************
    text: RT @kamoseaOfficial: 〈高速バスセットプラン販売再開のお知らせ〉本日より東京・千葉・君津から発着の3路線の便利な高速バスセットプランを販売再開しました。お近くのターミナルから、新幹線や飛行機からもぐっと身近に!https://t.co/0GltjR…
count(*): 71

確認方法

1) 単語を抽出
2) 選択した単語からTweetを抽出


[ec2-user@ ~]$ mysql -u root -p -e "select rank() OVER(ORDER BY count(*) desc) ranking,WORD,count(*) from FTS_WORD_TABLE where length(WORD) > 4 group by WORD having count(*) >= 10000 order by ranking";
Enter password: 
+---------+-----------+----------+
| ranking | WORD      | count(*) |
+---------+-----------+----------+
|       1 | バス      |   125194 |
|       2 | 高速      |    99663 |
|       3 | から      |    39348 |
|       4 | https     |    38086 |
|       5 | ます      |    27354 |
|       6 | ない      |    21659 |
|       7 | です      |    21414 |
|       8 | 運行      |    19084 |
|       9 | 東京      |    14653 |
|      10 | する      |    14406 |
|      11 | コロナ    |    13539 |
|      12 | まし      |    11307 |
|      13 | 再開      |    11170 |
|      14 | いる      |    10449 |
+---------+-----------+----------+
[ec2-user@ ~]$ 



[ec2-user@ ~]$ mysql -u root -p -e "select replace(replace(replace(left(text,50),'\)r\n',''),'\r',''),'\n','') as text,count(*) from FTS_Tweets WHERE MATCH (text) AGAINST ('+再開' IN BOOLEAN MODE) and tweet_time >= '2020-07-01 00:00:00' group by text order by count(*) desc limit 50";
Enter password: 
+------------------------------------------------------------------------------------------------------------------------------+----------+
| text                                                                                                                         | count(*) |
+------------------------------------------------------------------------------------------------------------------------------+----------+
| RT @kamoseaOfficial: 〈高速バスセットプラン販売再開のお知らせ〉本日より東京                                                  |       71 |
| RT @mainichiphoto: 西日本鉄道は新型コロナウイルスの影響で4月から運休していた福岡                                             |       41 |
| RT @mainichi: 西日本鉄道は福岡・北九州―東京間の夜行高速バス「はかた号」に新型車両2台                                        |       38 |
| RT @nkkyushuokinawa: 博多―東京を北九州経由で結ぶ #西鉄 の高速夜行バス「 #                                                   |       36 |
| RT @kyoto_dramakan: 新型コロナウイルス感染症の影響で運休していた、京阪バスのみつ                                             |       31 |
| RT @db_serviceinfo: 【都市間バス運休便の一部運行再開について】新型コロナウイル                                               |       29 |
| RT @OITABUS10: 新型コロナウイルスの影響で運休や減便をしておりました高速・特急バスです                                        |       23 |
| RT @chunichi_tokai: 遠州鉄道は十五日、高速バス「イーライナー」の渋谷・新宿線の運                                             |       23 |
| RT @FHFamily2019Rg: 〔緊急〕#中国バス夜行高速乗合バス【広島・福山⇔東京(東京                                                  |       16 |
| RT @CHUOBUS_JP: (7月10日 ~当面の間) 都市間高速バス運休便の一部運行再開につい                                             |       16 |
| RT @r_tsubame: 九州南部豪雨における人吉営業所管内の路線バス及び高速バスひとよし号の運                                        |       15 |
| RT @nishinippon_dsg: 大分道通行止め解除で福岡―大分路線は順次運行再開 西鉄グル                                              |       15 |
| RT @takehara_city: 【高速バスかぐや姫号の運行状況】(7月7日6時20分)山陽自                                                   |       12 |
| RT @FHFamily2019Rg: 〔緊急〕#中国バス高速乗合バス【広島⇔福岡】線『広福ライナ                                                |       11 |
| RT @hankyubus_info: 【高速バス】新型コロナウイルスの影響に伴う運行状況(一部路線                                             |       11 |
| RT @hankyubus_info: 【高速バス】新型コロナウイルスの影響に伴う運行状況(一部路線                                             |       10 |
| RT @tottori_bousai: 7月7日(火)高速バス運行再開情報日本交通株式会社より入                                                   |        9 |
| RT @Centrairairport: 【新型コロナウイルス関連情報】セントレア発着の交通機関に                                                |        9 |
| RT @nnrhbus_info: 福岡・福岡空港~阿蘇線「ASOエクスプレス」が2020年7月18                                                     |        9 |
| RT @sanyo_bus3715: 四国高速バスさまがおっしゃる通り、バスは動いてる時が一番幸せで                                            |        9 |
| RT @sanyo_bus3715: 【お知らせ】昼間高速バス「神戸~徳島線」の運行再開について新                                              |        8 |
| RT @shinkeisei_info: 運休中の高速バス新松戸駅・松戸駅~羽田空港線の運行を7/1                                                 |        8 |
| RT @Ryo_R158: 濃飛バス高速・特急…東海北陸道が復旧していることから、名古屋線・大阪                                           |        8 |
| RT @iyotetsu2017: いよ子です。今月から順次、いよてつ高速バスは、全路線の運行を再                                             |        8 |
| RT @utynews: 約3か月ぶり 竜王・甲府‐羽田空港 高速バス再開 https://t.co                                                   |        8 |
| RT @FHFamily2019Rg: 夜行高速乗合バス【広島・福山⇔東京(東京ドーム・大崎)】線『                                                 |        7 |
| RT @hankyubus_info: 【高速バス】新型コロナウイルスの影響に伴う運行状況(一部路線                                             |        7 |
| RT @761morning: 7/8 #トポモ #DailyScrapbook📒西鉄高速バス                                                                       |        7 |
| RT @iyotetsu2017: いよ子です。今月から順次、いよてつ高速バスは、全路線の運行を再                                             |        7 |
| RT @miraikun610: 京阪バス、高速バスを7月17日運行再開。枚方、亀岡、有馬温泉、米子                                             |        7 |
| RT @KintetsuBus: 【高速バス運行再開情報】近鉄バスでは、高速バスの運行を順次再開し                                            |        6 |
| RT @mainichi_houdou: 西日本鉄道は1日、新型コロナウイルスの影響で4月から運休し                                                |        6 |
| RT @fminyu: 福島交通「高速バス」一部再開へ 競馬場・福島-仙台など4路線#新型コロナウ                                        |        6 |
| RT @travelwatch_jp: 京阪バス、高速バスを7月17日運行再開。枚方、亀岡、有馬温泉                                                |        6 |
| RT @yokotanaka1972: 高山線の高山~下呂の長期運休は、ニュース画像を素人目に見て、                                             |        6 |
| RT @kotsu_TR: 【交通新聞電子版 トピックスニュース】7/15西鉄 福岡~東京の夜行高                                              |        6 |
| RT @FHFamily2019Rg: 〔続報〕夜行高速乗合バス【広島⇔東京】線『ニューブリーズ』運                                             |        6 |
| RT @iwakunibus: 7/1 11時配信岩国~広島高速バスは7/18(土)より、下記の便                                                     |        5 |
| RT @fukutetsubus: 【高速バス運行情報】新型コロナウイルス感染症の影響により運休中                                             |        5 |
| RT @iyotetsu2017: いよ子です。今月から順次、いよてつ高速バスは、全路線の運行を再                                             |        5 |
| RT @sanyo_bus3715: 四国高速バスさまがおっしゃる通り、バスは動いてる時が一番幸せで                                            |        5 |
| RT @entetsubambi: 【長島温泉リゾートライナー申込受付開始!!!】遠鉄高速バスe                                                |        5 |
| RT @utynews: きょうもスゴろくのニュースをご覧ください。昨夜のVF甲府の振り返り、コロナ                                      |        5 |
| RT @matudoramen: 運休されていた松戸ディズニー間の高速バス7月16日から順次再                                                   |        4 |
| RT @FHFamily2019Rg: 〔緊急〕#防長交通夜行高速乗合バス【萩・山口湯田・徳山・岩                                                   |        4 |
| RT @KitaibarakiCity: 高速バス「いわき・北茨城⇔東京」線の一部ダイヤの運航再開に                                              |        4 |
| RT @becchy9547: 【富山地鉄HP】高速バス「金沢・富山=山形・仙台線」7/17(金)金                                               |        4 |
| RT @nnrhbus_unkou: (7/10 16:28 現在)<運行再開>●高                                                                     |        4 |
| RT @nnrhbus_unkou: (7/10 18:16 現在)<運行再開>●高                                                                     |        4 |
| RT @iyotetsu2017: いよ子です。伊予鉄バスの高速バスは、順次全路線の運行を再開いた                                             |        4 |
+------------------------------------------------------------------------------------------------------------------------------+----------+
[ec2-user@~]$ 



MySQLやPostgreSQLに特定のデータベースなどを移行する時に、予めユーザー情報を別途取得して流し込んでおく必要があります。
そのな時に、サクッと情報を取得する方法を改めてシンプルにメモしておきます。

MySQLにおけるユーザー一覧と権限の確認

    存在するユーザーは,mysql.userテーブルを参照すれば確認可能

root@localhost [mysql]> select user,host,Super_priv,account_locked from user;
+------------------+----------------+------------+----------------+
| user             | host           | Super_priv | account_locked |
+------------------+----------------+------------+----------------+
| app_user         | %              | N          | N              |
| ssl_user         | 127.0.0.1      | N          | N              |
| mysql.infoschema | localhost      | N          | Y              |
| mysql.session    | localhost      | Y          | Y              |
| mysql.sys        | localhost      | N          | Y              |
| root             | localhost      | Y          | N              |
+------------------+----------------+------------+----------------+
7 rows in set (0.01 sec)

特定のユーザーだけを移行したい場合は、SHOW GRANTSで特定ユーザー情報のみに設定された権限を確認して、

    アカウントを作成後に移行先のデータベースにて権限を付与してあげればOK。

root@localhost [mysql]> show grants for root@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.00 sec)

root@localhost [mysql]> show grants for app_user@'%'\G
*************************** 1. row ***************************
Grants for app_user@%: GRANT USAGE ON *.* TO `app_user`@`%`
*************************** 2. row ***************************
Grants for app_user@%: GRANT ALL PRIVILEGES ON `APP_DB`.* TO `app_user`@`%`
2 rows in set (0.00 sec)

root@localhost [mysql]> 

WORKBENCHで勿論、確認する事が可能です。

PostgreSQLにおけるユーザー一覧と権限の確認

    特定データベース移行前にユーザー(ROLE)がいないとエラーになるので、予めROLE情報をダンプして流し込んでおいてあげてください。

postgres=# \du
                                                          ロール一覧
     ロール名     |                                   属性                                   |          所属グループ          
------------------+--------------------------------------------------------------------------+--------------------------------
 admin            | スーパユーザ                                                             | {pg_monitor,pg_signal_backend}
 application_role | スーパユーザ                                                             | {}
 postgres         | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
 replication_user | レプリケーション可                                                       | {}
 user_a           | スーパユーザ                                                             | {}
 user_b           | スーパユーザ                                                             | {}
 weather_app      | DB作成可                                                                 | {}

postgres=# select rolname, rolsuper, rolcanlogin from pg_roles;
          rolname          | rolsuper | rolcanlogin 
---------------------------+----------+-------------
 pg_monitor                | f        | f
 pg_read_all_settings      | f        | f
 pg_read_all_stats         | f        | f
 pg_stat_scan_tables       | f        | f
 pg_read_server_files      | f        | f
 pg_write_server_files     | f        | f
 pg_execute_server_program | f        | f
 pg_signal_backend         | f        | f
 admin                     | t        | t
 application_role          | t        | t
 postgres                  | t        | t
 replication_user          | f        | t
 user_a                    | t        | t
 user_b                    | t        | t
 weather_app               | f        | t
(15 行)

postgres-# \z
                                アクセス権限
 スキーマ |       名前       |    型    | アクセス権限 | 列の権限 | ポリシー 
----------+------------------+----------+--------------+----------+----------
 public   | pgbench_accounts | テーブル |              |          | 
 public   | pgbench_branches | テーブル |              |          | 
 public   | pgbench_history  | テーブル |              |          | 
 public   | pgbench_tellers  | テーブル |              |          | 
(4 行)

    pg_dumpallでアカウント情報をダウンプする事が可能です。

-bash-4.2$ pg_dumpall -U postgres  --roles-only > dump_role_only.sql
-bash-4.2$ cat dump_role_only.sql 
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE admin;
ALTER ROLE admin WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5d9b39f44ce7405804d605804d6';
CREATE ROLE application_role;
ALTER ROLE application_role WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5d9b39f44ce7405804d605804d6';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md5d9b39f44ce7405804d605804d6';

    pg_adminでも付与さけた権限や権限設定が可能です

MySQLとPostgreSQLにおける基本的なユーザーアカウント管理について


RailsのGem seed-fuを利用して都道府県の初期データを作成する

都道府県番号(厚生労働省)

#### Prefecture Tableの準備

[admin@postgresql weather]$ bundle exec rails g model prefecture
      invoke  active_record
      create    db/migrate/20200612225551_create_prefectures.rb
      create    app/models/prefecture.rb
      invoke    test_unit
      create      test/models/prefecture_test.rb
      create      test/fixtures/prefectures.yml
[admin@postgresql weather]$ 

[admin@postgresql weather]$ vim db/migrate/20200612225551_create_prefectures.rb
[admin@postgresql weather]$ cat db/migrate/20200612225551_create_prefectures.rb
class CreatePrefectures < ActiveRecord::Migration[6.0]
  def change
    create_table :prefectures do |t|
      t.string :name, null: false,  comment: "都道府県"
      t.timestamps
    end
  end
end
[admin@postgresql weather]$ 


[admin@postgresql weather]$ bundle exec rails db:migrate
== 20200612225551 CreatePrefectures: migrating ================================
-- create_table(:prefectures)
   -> 0.1382s
== 20200612225551 CreatePrefectures: migrated (0.1387s) =======================


#### Prefecture Tableの確認


weather_development-# \d prefectures;
                                           テーブル"public.prefectures"
     列     |               型               | 照合順序 | Null 値を許容 |               デフォルト                
------------+--------------------------------+----------+---------------+-----------------------------------------
 id         | bigint                         |          | not null      | nextval('prefectures_id_seq'::regclass)
 name       | character varying              |          | not null      | 
 created_at | timestamp(6) without time zone |          | not null      | 
 updated_at | timestamp(6) without time zone |          | not null      | 
インデックス:
    "prefectures_pkey" PRIMARY KEY, btree (id)
参照元:
    TABLE "weather_forecasts" CONSTRAINT "fk_rails_514be57da6" FOREIGN KEY (prefecture_id) REFERENCES prefectures(id)


#### Gem(seed-fu)のインストールとデータの準備

[admin@postgresql weather]$ tail Gemfile
# https://github.com/mbleigh/seed-fu
  gem 'seed-fu'

[admin@postgresql weather]$ bundle install
Fetching seed-fu 2.3.9
Installing seed-fu 2.3.9
<SNIP>

[admin@postgresql weather]$ cat db/fixtures/prefecture.rb 
Prefecture.seed(:id,
  { :id => 1, :name => "北海道" },
  { :id => 2, :name => "青森県" },
  { :id => 3, :name => "岩手県" },
  { :id => 4, :name => "宮城県" },
  { :id => 5, :name => "秋田県" },
  { :id => 6, :name => "山形県" },
  { :id => 7, :name => "福島県" },
  { :id => 8, :name => "茨城県" },
  { :id => 9, :name => "栃木県" },
  { :id => 10, :name => "群馬県" },
  { :id => 11, :name => "埼玉県" },
  { :id => 12, :name => "千葉県" },
  { :id => 13, :name => "東京都" },
  { :id => 14, :name => "神奈川県" },
  { :id => 15, :name => "新潟県" },
  { :id => 16, :name => "富山県" },
  { :id => 17, :name => "石川県" },
  { :id => 18, :name => "福井県" },
  { :id => 19, :name => "山梨県" },
  { :id => 20, :name => "長野県" },
  { :id => 21, :name => "岐阜県" },
  { :id => 22, :name => "静岡県" },
  { :id => 23, :name => "愛知県" },
  { :id => 24, :name => "三重県" },
  { :id => 25, :name => "滋賀県" },
  { :id => 26, :name => "京都府" },
  { :id => 27, :name => "大阪府" },
  { :id => 28, :name => "兵庫県" },
  { :id => 29, :name => "奈良県" },
  { :id => 30, :name => "和歌山県" },
  { :id => 31, :name => "鳥取県" },
  { :id => 32, :name => "島根県" },
  { :id => 33, :name => "岡山県" },
  { :id => 34, :name => "広島県" },
  { :id => 35, :name => "山口県" },
  { :id => 36, :name => "徳島県" },
  { :id => 37, :name => "香川県" },
  { :id => 38, :name => "愛媛県" },
  { :id => 39, :name => "高知県" },
  { :id => 40, :name => "福岡県" },
  { :id => 41, :name => "佐賀県" },
  { :id => 42, :name => "長崎県" },
  { :id => 43, :name => "熊本県" },
  { :id => 44, :name => "大分県" },
  { :id => 45, :name => "宮崎県" },
  { :id => 46, :name => "鹿児島県" },
  { :id => 47, :name => "沖縄県" }
)
[admin@postgresql weather]$ 

#### Gem(seed-fu)にてデータの投入


[admin@postgresql weather]$ bundle exec rails db:seed_fu

== Seed from /home/admin/app/ruby/weather/db/fixtures/prefecture.rb
 - Prefecture {:id=>1, :name=>"北海道"}
 - Prefecture {:id=>2, :name=>"青森県"}
 - Prefecture {:id=>3, :name=>"岩手県"}
 - Prefecture {:id=>4, :name=>"宮城県"}
 - Prefecture {:id=>5, :name=>"秋田県"}
 - Prefecture {:id=>6, :name=>"山形県"}
 - Prefecture {:id=>7, :name=>"福島県"}
 - Prefecture {:id=>8, :name=>"茨城県"}
 - Prefecture {:id=>9, :name=>"栃木県"}
 - Prefecture {:id=>10, :name=>"群馬県"}
 - Prefecture {:id=>11, :name=>"埼玉県"}
 - Prefecture {:id=>12, :name=>"千葉県"}
 - Prefecture {:id=>13, :name=>"東京都"}
 - Prefecture {:id=>14, :name=>"神奈川県"}
 - Prefecture {:id=>15, :name=>"新潟県"}
 - Prefecture {:id=>16, :name=>"富山県"}
 - Prefecture {:id=>17, :name=>"石川県"}
 - Prefecture {:id=>18, :name=>"福井県"}
 - Prefecture {:id=>19, :name=>"山梨県"}
 - Prefecture {:id=>20, :name=>"長野県"}
 - Prefecture {:id=>21, :name=>"岐阜県"}
 - Prefecture {:id=>22, :name=>"静岡県"}
 - Prefecture {:id=>23, :name=>"愛知県"}
 - Prefecture {:id=>24, :name=>"三重県"}
 - Prefecture {:id=>25, :name=>"滋賀県"}
 - Prefecture {:id=>26, :name=>"京都府"}
 - Prefecture {:id=>27, :name=>"大阪府"}
 - Prefecture {:id=>28, :name=>"兵庫県"}
 - Prefecture {:id=>29, :name=>"奈良県"}
 - Prefecture {:id=>30, :name=>"和歌山県"}
 - Prefecture {:id=>31, :name=>"鳥取県"}
 - Prefecture {:id=>32, :name=>"島根県"}
 - Prefecture {:id=>33, :name=>"岡山県"}
 - Prefecture {:id=>34, :name=>"広島県"}
 - Prefecture {:id=>35, :name=>"山口県"}
 - Prefecture {:id=>36, :name=>"徳島県"}
 - Prefecture {:id=>37, :name=>"香川県"}
 - Prefecture {:id=>38, :name=>"愛媛県"}
 - Prefecture {:id=>39, :name=>"高知県"}
 - Prefecture {:id=>40, :name=>"福岡県"}
 - Prefecture {:id=>41, :name=>"佐賀県"}
 - Prefecture {:id=>42, :name=>"長崎県"}
 - Prefecture {:id=>43, :name=>"熊本県"}
 - Prefecture {:id=>44, :name=>"大分県"}
 - Prefecture {:id=>45, :name=>"宮崎県"}
 - Prefecture {:id=>46, :name=>"鹿児島県"}
 - Prefecture {:id=>47, :name=>"沖縄県"}
[admin@postgresql weather]$ 

#### データの確認
都道府県データが正常に作成されている事を確認しました。

weather_development=# select * from prefectures;
 id |   name   |         created_at         |         updated_at         
----+----------+----------------------------+----------------------------
  1 | 北海道   | 2020-06-12 23:54:33.283651 | 2020-06-12 23:54:33.283651
  2 | 青森県   | 2020-06-12 23:54:33.294884 | 2020-06-12 23:54:33.294884
  3 | 岩手県   | 2020-06-12 23:54:33.301876 | 2020-06-12 23:54:33.301876
  4 | 宮城県   | 2020-06-12 23:54:33.309521 | 2020-06-12 23:54:33.309521
  5 | 秋田県   | 2020-06-12 23:54:33.315667 | 2020-06-12 23:54:33.315667
  6 | 山形県   | 2020-06-12 23:54:33.322739 | 2020-06-12 23:54:33.322739
  7 | 福島県   | 2020-06-12 23:54:33.330228 | 2020-06-12 23:54:33.330228
  8 | 茨城県   | 2020-06-12 23:54:33.352692 | 2020-06-12 23:54:33.352692
  9 | 栃木県   | 2020-06-12 23:54:33.361259 | 2020-06-12 23:54:33.361259
 10 | 群馬県   | 2020-06-12 23:54:33.368557 | 2020-06-12 23:54:33.368557
 11 | 埼玉県   | 2020-06-12 23:54:33.377314 | 2020-06-12 23:54:33.377314
 12 | 千葉県   | 2020-06-12 23:54:33.385298 | 2020-06-12 23:54:33.385298
 13 | 東京都   | 2020-06-12 23:54:33.394371 | 2020-06-12 23:54:33.394371
 14 | 神奈川県 | 2020-06-12 23:54:33.40101  | 2020-06-12 23:54:33.40101
 15 | 新潟県   | 2020-06-12 23:54:33.409158 | 2020-06-12 23:54:33.409158
 16 | 富山県   | 2020-06-12 23:54:33.416567 | 2020-06-12 23:54:33.416567
 17 | 石川県   | 2020-06-12 23:54:33.423705 | 2020-06-12 23:54:33.423705
 18 | 福井県   | 2020-06-12 23:54:33.434071 | 2020-06-12 23:54:33.434071
 19 | 山梨県   | 2020-06-12 23:54:33.464188 | 2020-06-12 23:54:33.464188
 20 | 長野県   | 2020-06-12 23:54:33.473083 | 2020-06-12 23:54:33.473083
 21 | 岐阜県   | 2020-06-12 23:54:33.483633 | 2020-06-12 23:54:33.483633
 22 | 静岡県   | 2020-06-12 23:54:33.4898   | 2020-06-12 23:54:33.4898
 23 | 愛知県   | 2020-06-12 23:54:33.507801 | 2020-06-12 23:54:33.507801
 24 | 三重県   | 2020-06-12 23:54:33.516001 | 2020-06-12 23:54:33.516001
 25 | 滋賀県   | 2020-06-12 23:54:33.523702 | 2020-06-12 23:54:33.523702
 26 | 京都府   | 2020-06-12 23:54:33.533514 | 2020-06-12 23:54:33.533514
 27 | 大阪府   | 2020-06-12 23:54:33.541808 | 2020-06-12 23:54:33.541808
 28 | 兵庫県   | 2020-06-12 23:54:33.550734 | 2020-06-12 23:54:33.550734
 29 | 奈良県   | 2020-06-12 23:54:33.557281 | 2020-06-12 23:54:33.557281
 30 | 和歌山県 | 2020-06-12 23:54:33.566571 | 2020-06-12 23:54:33.566571
 31 | 鳥取県   | 2020-06-12 23:54:33.573718 | 2020-06-12 23:54:33.573718
 32 | 島根県   | 2020-06-12 23:54:33.582212 | 2020-06-12 23:54:33.582212
 33 | 岡山県   | 2020-06-12 23:54:33.589471 | 2020-06-12 23:54:33.589471
 34 | 広島県   | 2020-06-12 23:54:33.598941 | 2020-06-12 23:54:33.598941
 35 | 山口県   | 2020-06-12 23:54:33.605716 | 2020-06-12 23:54:33.605716
 36 | 徳島県   | 2020-06-12 23:54:33.614624 | 2020-06-12 23:54:33.614624
 37 | 香川県   | 2020-06-12 23:54:33.621642 | 2020-06-12 23:54:33.621642
 38 | 愛媛県   | 2020-06-12 23:54:33.628824 | 2020-06-12 23:54:33.628824
 39 | 高知県   | 2020-06-12 23:54:33.637536 | 2020-06-12 23:54:33.637536
 40 | 福岡県   | 2020-06-12 23:54:33.644695 | 2020-06-12 23:54:33.644695
 41 | 佐賀県   | 2020-06-12 23:54:33.653393 | 2020-06-12 23:54:33.653393
 42 | 長崎県   | 2020-06-12 23:54:33.659903 | 2020-06-12 23:54:33.659903
 43 | 熊本県   | 2020-06-12 23:54:33.66802  | 2020-06-12 23:54:33.66802
 44 | 大分県   | 2020-06-12 23:54:33.675145 | 2020-06-12 23:54:33.675145
 45 | 宮崎県   | 2020-06-12 23:54:33.683799 | 2020-06-12 23:54:33.683799
 46 | 鹿児島県 | 2020-06-12 23:54:33.700625 | 2020-06-12 23:54:33.700625
 47 | 沖縄県   | 2020-06-12 23:54:33.708507 | 2020-06-12 23:54:33.708507
(47 行)

weather_development=# 


WSL1からWSL2への更新

これまで20年弱、Windows上でLinuxシステムを利用する為に色々な方法で対応してきたけど、WSL2のリリースでひと段落ついた印象です。
Virtual Boxを利用したりしてデモしたり、検証したりする事も多かったですが、Windowsの環境の変化によってインストールに手間がかかるケースも多々ありました。
WSLであればWindows Nativeだし、互換性等は気にする事が減るのは有難い。WLS2はHyper-Vなどが利用出来ないWindows Home Editionでも動くのも尚良し。
更に、WSL1とWSL2共に共存出来るので使い分け可能だし、WSL2ではWSL1では利用出来なかったソフトも利用可能。Windows 3.11の頃からWindowsを利用している自分としては、
これからもWindows一択で問題は無さそう。

Windowsバージョン

Windows 10 用 Windows Subsystem for Linux のインストール ガイド
https://docs.microsoft.com/ja-jp/windows/wsl/install-win10

仮想マシン プラットフォーム のオプション コンポーネントを有効にする


PS C:\WINDOWS\system32> dism.exe /online /enable-feature /featurename:VirtualMachinePlatform /all /norestart

展開イメージのサービスと管理ツール
バージョン: 10.0.19041.1

イメージのバージョン: 10.0.19041.264

機能を有効にしています
[==========================100.0%==========================]
操作は正常に完了しました。
PS C:\WINDOWS\system32>

WSL 2 Linux カーネルの更新
https://docs.microsoft.com/ja-jp/windows/wsl/wsl2-kernel

WSL 2 を既定のバージョンとして設定する

PS C:\WINDOWS\system32> wsl --set-default-version 2
WSL 2 との主な違いについては、https://aka.ms/wsl2 を参照してください
PS C:\WINDOWS\system32>

ディストリビューションのバージョンを WSL 1 または WSL 2 に設定

PS C:\WINDOWS\system32> wsl --list --verbose
  NAME            STATE           VERSION
* Ubuntu-18.04    Stopped         1
PS C:\WINDOWS\system32> wsl --set-version Ubuntu-18.04 2
変換中です。この処理には数分かかることがあります...
WSL 2 との主な違いについては、https://aka.ms/wsl2 を参照してください
変換が完了しました。
PS C:\WINDOWS\system32> wsl --list --verbose
  NAME            STATE           VERSION
* Ubuntu-18.04    Stopped         2
PS C:\WINDOWS\system32>

Comparing WSL 2 and WSL 1
https://docs.microsoft.com/en-us/windows/wsl/compare-versions

備考:慣れたコンソールを利用する為にSSHアクセスの設定

root@DESKTOP-9DIOKCA:~# ssh-keygen -A
ssh-keygen: generating new host keys: RSA DSA ECDSA ED25519
root@DESKTOP-9DIOKCA:~# service ssh restart
 * Restarting OpenBSD Secure Shell server sshd                                                                                                                                              [ OK ]
root@DESKTOP-9DIOKCA:~#

root@DESKTOP-9DIOKCA:~# systemctl enable ssh
Synchronizing state of ssh.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable ssh
root@DESKTOP-9DIOKCA:~#


MySQLとPostgreSQLにおけるログの確認方法

TPCCなどをダウンロードして参照のみ、更新と参照、参照と更新の比率変更等して様々な負荷をかけてベンチマークする方が良いと思うが、カジュアルにベンチマークしたいケースもあると思います。毎回、同じ方法でベンチマークする事で、ある程度サーバーの構成変更時のパフォーマンスの変化を把握できるのでMySQLやPostgreSQLにバンドルされているベンチマークツールも便利なベンチマークオプションとして使える。取得したログを簡単に確認したり、実際に運用中のシステムのログも定期的に確認出来れば尚便利。そんなニーズに対して、カジュアルにメモしておきました。

PostgreSQL


postgres@ubuntu:~$ pgbench -i scaffold
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.28 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

postgres@ubuntu:~$ psql scaffold -c "\d"
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)

postgres@ubuntu:~$

postgres@ubuntu:~$ pgbench -c 30 -t 1000 scaffold
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 30000/30000
latency average = 41.333 ms
tps = 725.808779 (including connections establishing)
tps = 726.545616 (excluding connections establishing)
postgres@ubuntu:~$

root@ubuntu:/var/log/postgresql# pgbadger postgresql-10-main.log
[========================>] Parsed 96663 bytes of 96663 (100.00%), queries: 0, events: 301
LOG: Ok, generating html report...
root@ubuntu:/var/log/postgresql# ls -l
total 1392
-rw-r--r-- 1 root root 1324353 May 24 10:34 out.html
-rw-r----- 1 postgres adm 96663 May 24 10:23 postgresql-10-main.log

pgbadger
*メモ:それぞれのパッケージは、aptもしくはyumでインストールしています。

pgbadgerのアウトプット
pgbadger

MySQL

負荷をかけてログを記録


[root@GA02 admin]# /usr/local/mysql/bin/mysqlslap --no-defaults --create-schema=SQLSLAP --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=InnoDB --number-int-cols=3 --number-char-cols=5 --concurrency=30 --auto-generate-sql-write-number=1000 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -p
Enter password:
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 19.724 seconds
Minimum number of seconds to run all queries: 19.724 seconds
Maximum number of seconds to run all queries: 19.724 seconds
Number of clients running queries: 30
Average number of queries per client: 1000

[root@GA02 admin]#

mysqldumpslow (MySQL Default)でログ解析


[root@GA02 data]# mysqldumpslow -s  c /usr/local/mysql/data/mysql-slow.log 

Reading mysql slow query log from /usr/local/mysql/data/mysql-slow.log
Count: 17349  Time=0.03s (463s)  Lock=0.00s (5s)  Rows=0.0 (0), root[root]@localhost
  INSERT INTO t1 VALUES (NULL,N,N,N,'S','S','S','S','S')

Count: 13375  Time=0.00s (2s)  Lock=0.00s (1s)  Rows=1.0 (13375), root[root]@localhost
  SELECT intcol1,intcol2,intcol3,charcol1,charcol2,charcol3,charcol4,charcol5 FROM t1 WHERE id =  'S'

Count: 31  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (30), root[root]@localhost
  #

Count: 31  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

Percona Tool Kitでログ解析


[root@GA02 admin]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
--2020-05-24 13:51:20--  https://repo.percona.com/yum/percona-release-latest.noarch.rpm
repo.percona.com (repo.percona.com) をDNSに問いあわせています... 167.71.118.3, 157.245.119.64, 167.99.233.229
repo.percona.com (repo.percona.com)|167.71.118.3|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 17684 (17K) [application/x-redhat-package-manager]
`percona-release-latest.noarch.rpm' に保存中

100%[==============================================================================================================================================================================================>] 17,684      --.-K/s 時間 0s      

2020-05-24 13:51:21 (51.5 GB/s) - `percona-release-latest.noarch.rpm' へ保存完了 [17684/17684]

[root@GA02 admin]# yum install percona-release-latest.noarch.rpm 
読み込んだプラグイン:fastestmirror, priorities
percona-release-latest.noarch.rpm を調べています: percona-release-1.0-18.noarch
次のリポジトリーへの更新として percona-release-latest.noarch.rpm を設定します: percona-release-0.1-4.noarch
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:0.1-4 を 更新
---> パッケージ percona-release.noarch 0:1.0-18 を アップデート
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================================================================================================
 Package                                                  アーキテクチャー                                バージョン                                      リポジトリー                                                             容量
========================================================================================================================================================================================================================================
更新します:
 percona-release                                          noarch                                          1.0-18                                          /percona-release-latest.noarch                                           22 k

トランザクションの要約
========================================================================================================================================================================================================================================
更新  1 パッケージ

合計容量: 22 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  更新します              : percona-release-1.0-18.noarch                                                                                                                                                                           1/2 
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
  percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html
  整理中                  : percona-release-0.1-4.noarch                                                                                                                                                                            2/2 
  検証中                  : percona-release-1.0-18.noarch                                                                                                                                                                           1/2 
  検証中                  : percona-release-0.1-4.noarch                                                                                                                                                                            2/2 
更新:
  percona-release.noarch 0:1.0-18                                                                                                                                                                                                       
完了しました!
[root@GA02 admin]# 

[root@GA02 admin]# yum install percona-toolkit
読み込んだプラグイン:fastestmirror, priorities
percona-release-noarch                                                                                                                                                                                           | 2.9 kB  00:00:00     
percona-release-x86_64                                                                                                                                                                                           | 2.9 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirrors.cat.net
 * epel: ftp.jaist.ac.jp
 * extras: mirrors.cat.net
 * updates: mirrors.cat.net
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-toolkit.x86_64 0:3.2.0-1.el7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================================================================================================
 Package                                                  アーキテクチャー                                バージョン                                              リポジトリー                                                     容量
========================================================================================================================================================================================================================================
インストール中:
 percona-toolkit                                          x86_64                                          3.2.0-1.el7                                             percona-release-x86_64                                           12 M

トランザクションの要約
========================================================================================================================================================================================================================================
インストール  1 パッケージ

総ダウンロード容量: 12 M
インストール容量: 12 M
Is this ok [y/d/N]: y
Downloading packages:
警告: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/percona-toolkit-3.2.0-1.el7.x86_64.rpm: ヘッダー V4 RSA/SHA256 Signature、鍵 ID 8507efa5: NOKEY========================================-] 184 kB/s |  12 MB  00:00:00 ETA 
percona-toolkit-3.2.0-1.el7.x86_64.rpm の公開鍵がインストールされていません
percona-toolkit-3.2.0-1.el7.x86_64.rpm                                                                                                                                                                           |  12 MB  00:00:50     
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
 Userid     : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
 Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
 Package    : percona-release-1.0-18.noarch (@/percona-release-latest.noarch)
 From       : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか? [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-toolkit-3.2.0-1.el7.x86_64                                                                                                                                                                      1/1 
  検証中                  : percona-toolkit-3.2.0-1.el7.x86_64                                                                                                                                                                      1/1 
インストール:
  percona-toolkit.x86_64 0:3.2.0-1.el7                                                                                                                                                                                                  
完了しました!
[root@GA02 admin]# 

pt-query-digestのアウトプット


[root@GA02 data]# pt-query-digest /usr/local/mysql/data/mysql-slow.log --no-version-check

# 20.9s user time, 50ms system time, 31.58M rss, 232.37M vsz
# Current date: Sun May 24 14:19:42 2020
# Hostname: GA02
# Files: /usr/local/mysql/data/mysql-slow.log
# Overall: 30.76k total, 9 unique, 242.22 QPS, 3.73x concurrency _________
# Time range: 2020-05-24T05:07:09 to 2020-05-24T05:09:16
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           474s     1us   221ms    15ms    34ms    15ms    20ms
# Lock time             7s       0   179ms   230us   332us     4ms   103us
# Rows sent         14.04k       0     999    0.47    0.99    5.52       0
# Rows examine      14.04k       0     999    0.47    0.99    5.52       0
# Query size        12.96M      17     707  441.72  685.39  291.70  685.39

# Profile
# Rank Query ID                        Response time  Calls R/Call V/M   I
# ==== =============================== ============== ===== ====== ===== =
#    1 0x76F7DD6D2A72EB7BCAEC22E79B... 468.9853 99.0% 17349 0.0270  0.00 INSERT t?
# MISC 0xMISC                            4.5256  1.0% 13413 0.0003   0.0 <8 ITEMS>

# Query 1: 667.27 QPS, 18.04x concurrency, ID 0x76F7DD6D2A72EB7BCAEC22E79B0EFD3B at byte 988485
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-05-24T05:08:50 to 2020-05-24T05:09:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         56   17349
# Exec time     99    469s     1ms   221ms    27ms    38ms    11ms    26ms
# Lock time     75      5s       0   179ms   309us   384us     5ms   108us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    90  11.67M     704     707  705.36  685.39       0  685.39
# String:
# Databases    SQLSLAP
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `SQLSLAP` LIKE 't1'\G
#    SHOW CREATE TABLE `SQLSLAP`.`t1`\G
INSERT INTO t1 VALUES (NULL,633998215,1598162325,1839677468,'l4dgQpQ2FFmHegccMlq0FmNIrmjthL1IPWzrCHuRYgk2KDWaqKb57qNyczrAKlSl7JcJcMe8uN26SYhItGF0yXybWhdqQxRQHvltIGuCfwJzTs9M0OEyv4PHl274sQ','W9M7ugNokudvtyn2CnJQmEMBO76N3yqZzCZtkwaSrBGc93XDjqfLwtOK0WkwKaLJeu5MrfiaiyAjuZwdNRyKKMYClG85iMHKGMaztHPR7SA11yWPPKddj9pKQqH8CQ','LKgvKZ4d0C52570cOHw1uXAkjSQ1TeJqy0lAPqoQ2lKzsLR8WG9PnC1NYRG1xbrvbdy1ReJLNfC8sgGFwIgbMhyu0EwxinsHGiAbKLoct1klhs0d2Y4O53J5972run','13L3eiPRAISuv1eoWhKaxQTGNfFIMA5F5QJj0k2A2WwxNRlt8yS5GpEfSTxjXuycD9CDtEGnd4d0XEufCEdS3j0O4p7ckx4x7G2sNAg0FH0mXmIc0nwwWoKaeJ2E87','3FFx0e8YWNpN1C9JgA6T6SHI3vdx1lw4tC2liAH5PySQ2dejfcCd5Lv0HOxA2uFnyHYYJhdcf52ZbYGE9KkEifErw31qx8dnH1KSax4DvzmWXvcY7KmhssOowIWlQz')\G
[root@GA02 data]# 

MySQLのエラーログ含めてGUIで確認するのであればこんなツールもあります。
Percona Monitoring and Management
MySQL Enterprise Monitor


MySQLのMECABによる最近つぶやかれている単語の解析

MySQLのMecabプラグインを利用して集計したデータを解析する過程で、INNODB_FT_INDEX_TABLEにて含まれている単語を確認し集計しようとしたが、
サーバーのスペックが低く集計中にハングしてしまったので、メモリーのINNODB_FT_INDEX_TABLEの内容をInnoDBテーブルに落として集計してみた。
もし、同様に形態素解析で解析されたワードを集計したいけれども、メモリー量が不足していて単語を集計出来ない場合の回避策として。参考までに。


root@localhost [(none)]> desc INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| WORD         | varchar(337)        | NO   |     |         |       |
| FIRST_DOC_ID | bigint(21) unsigned | NO   |     |         |       |
| LAST_DOC_ID  | bigint(21) unsigned | NO   |     |         |       |
| DOC_COUNT    | bigint(21) unsigned | NO   |     |         |       |
| DOC_ID       | bigint(21) unsigned | NO   |     |         |       |
| POSITION     | bigint(21) unsigned | NO   |     |         |       |
+--------------+---------------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

root@localhost [confirm]> show create table INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE\G
*************************** 1. row ***************************
       Table: INNODB_FT_INDEX_TABLE
Create Table: CREATE TEMPORARY TABLE `INNODB_FT_INDEX_TABLE` (
  `WORD` varchar(337) NOT NULL DEFAULT '',
  `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
  `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
  `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0',
  `DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
  `POSITION` bigint unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

メモリーのINNODB_FT_INDEX_TABLEからInnoDBのテーブルにデータをコピーする


root@localhost [(confirm)]> SET GLOBAL innodb_ft_aux_table='APP/Tweets';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(confirm)]> SET GLOBAL innodb_optimize_fulltext_only=ON;optimize table Tweets;
Query OK, 0 rows affected (0.00 sec)

root@localhost [confirm]> CREATE TEMPORARY TABLE `FTS_INDEX_TABLE` (
    ->   `WORD` varchar(337) NOT NULL DEFAULT '',
    ->   `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `POSITION` bigint unsigned NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)


root@localhost [confirm]> insert into FTS_INDEX_TABLE select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
Query OK, 11642045 rows affected (12 min 51.51 sec)
Records: 11642045  Duplicates: 0  Warnings: 0


オーバーヘッド
MEMORYからデータのコピー中はサーバースペックも低いのでメモリー+SWAPが最大限に利用されている。
時間も上記のように12分もかかってしまいました。

メモ:再集計する時に、TRUNCATEするのでTEMPORARYのままでもいいけど、念のためInnoDB間のみでデータコピー速度を確認してみた。
こちらは、1分半程度で終わっています。


root@localhost [confirm]> CREATE TABLE `FTS_WORD_TABLE` (
    ->   `WORD` varchar(337) NOT NULL DEFAULT '',
    ->   `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `POSITION` bigint unsigned NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [confirm]> insert into FTS_WORD_TABLE select * from FTS_INDEX_TABLE;
Query OK, 11642045 rows affected (1 min 29.70 sec)
Records: 11642045  Duplicates: 0  Warnings: 0

TWEETワードランキング
以下、直近のTweet集計結果です。コロナの影響もあり、殆どがコロナ関連の単語が上位にきている事が分かります。


root@localhost [confirm]> select rank() OVER(ORDER BY count(*) desc) ranking,WORD,count(*) 
    -> from FTS_WORD_TABLE where length(WORD) > 4 group by WORD having count(*) >= 10000 order by ranking;
+---------+-----------------+----------+
| ranking | WORD            | count(*) |
+---------+-----------------+----------+
|       1 | ウイルス        |   282891 |
|       2 | コロナ          |   232527 |
|       3 | 新型            |   173684 |
|       4 | 感染            |   157965 |
|       5 | https           |   137958 |
|       6 | ます            |   101664 |
|       7 | ない            |    97355 |
|       8 | から            |    81599 |
|       9 | する            |    70057 |
|      10 | こと            |    66332 |
|      11 | まし            |    65643 |
|      12 | いる            |    64802 |
|      13 | です            |    62606 |
|      14 | 拡大            |    43248 |
|      15 | バス            |    40119 |
|      16 | 日本            |    31022 |
|      17 | ある            |    29622 |
|      18 | 対策            |    29344 |
|      19 | ため            |    28480 |
|      20 | この            |    25914 |
|      21 | 中国            |    24729 |
|      22 | てる            |    24267 |
|      23 | など            |    23112 |
|      24 | まで            |    22573 |
|      25 | という          |    22429 |
|      26 | よう            |    22123 |
|      27 | 検査            |    21856 |
|      28 | なっ            |    20436 |
|      29 | 医療            |    20188 |
|      30 | 東京            |    19908 |
|      31 | これ            |    18426 |
|      32 | 影響            |    18350 |
|      33 | 確認            |    18141 |
|      34 | ので            |    17266 |
|      35 | あり            |    17175 |
|      36 | さん            |    16897 |
|      37 | について        |    16705 |
|      38 | ませ            |    16701 |
|      39 | たら            |    16575 |
|      40 | マスク          |    16337 |
|      41 | なり            |    16270 |
|      42 | 緊急            |    16173 |
|      43 | より            |    15770 |
|      44 | なく            |    15662 |
|      45 | 防止            |    15512 |
|      46 | 受け            |    15471 |
|      47 | 世界            |    15462 |
|      48 | って            |    15407 |
|      49 | けど            |    15108 |
|      50 | 発表            |    14998 |
|      51 | として          |    14969 |
|      52 | 政府            |    14914 |
|      53 | れる            |    14863 |
|      54 | yahoonewstopics |    14258 |
|      55 | なる            |    14207 |
|      56 | 自粛            |    12808 |
|      57 | その            |    12730 |
|      58 | 情報            |    12621 |
|      59 | 事態            |    12430 |
|      60 | だけ            |    12010 |
|      61 | 武漢            |    11872 |
|      62 | お知らせ        |    11751 |
|      63 | そう            |    11453 |
|      64 | できる          |    11284 |
|      65 | による          |    11218 |
|      66 | でき            |    11120 |
|      67 | 開催            |    11085 |
|      68 | たい            |    10899 |
|      69 | 宣言            |    10855 |
|      70 | でも            |    10801 |
|      71 | ニュース        |    10720 |
|      72 | 状況            |    10663 |
|      73 | 中止            |    10641 |
|      74 | 新た            |    10626 |
|      75 | おり            |    10507 |
|      76 | 患者            |    10391 |
|      77 | だっ            |    10352 |
|      78 | なら            |    10304 |
|      79 | 時間            |    10096 |
|      80 | 対応            |    10093 |
|      81 | 病院            |    10092 |
+---------+-----------------+----------+
81 rows in set (26.52 sec)

root@localhost [confirm]> 

参考: Windows関数 on MySQL


エンジニア以外のメンバーが気軽にデータベースを参照出来るツールは無いかと見てみたら、metabaseは使い勝手が良さそうだなと感じました。

1) アカウントを複数作成出来る
2) グループに分けられる
3) クエリーを使うこともGUIで参照する事も可能
4) Slackやメールとの連携が可能
5) OpenSourceである事。(翻訳に協力しようと考えてます)
6) 管理工数、セットアップが容易
7) ビジネス側のメンバーが容易にグラフで状況を理解する事が可能
8) 非エンジニアでも、SQLを少し学べばカスタマイズ出来る事(日付範囲等)

インストール (CentOS7を利用しています)
Javaのバージョンが要件を満たしていれば、metabase.jarをダウンロードしきて実行するだけ。


[root@GA01 admin]# java -version
openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)
[root@GA01 admin]# 


[root@GA01 metabase]# wget http://downloads.metabase.com/v0.30.4/metabase.jar
--2018-10-27 21:13:55--  http://downloads.metabase.com/v0.30.4/metabase.jar
downloads.metabase.com (downloads.metabase.com) をDNSに問いあわせています... 52.216.100.114
downloads.metabase.com (downloads.metabase.com)|52.216.100.114|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 117673285 (112M) [application/java-archive]
`metabase.jar' に保存中

[root@GA01 metabase]# java -jar /usr/local/metabase/metabase.jar 
10-27 21:25:49 INFO util.encryption :: 保存された資格情報は、このMetabase Instanceでは無効化されています 🔓 
For more information, see https://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-your-database-connection-details-at-rest
10-27 21:26:44 INFO metabase.core :: MetabseをSTANDALONEモードで起動中
10-27 21:26:44 INFO metabase.core :: configで埋め込みJetty Webserverを起動中: 
 {:port 3000}

10-27 21:26:45 INFO metabase.core :: Metabseバージョンv0.30.4 (8bf182f release-0.30.4)を起動中...
10-27 21:26:45 INFO metabase.core :: システムタイムゾーンは"Asia/Tokyo"です
WARNING: any? already refers to: #'clojure.core/any? in namespace: monger.collection, being replaced by: #'monger.collection/any?
10-27 21:27:05 INFO metabase.core :: Metabse DBを移動設定中。これには時間がかかることがあります。
10-27 21:27:05 INFO metabase.db :: h2データベース接続を確認中...
10-27 21:27:06 INFO metabase.db :: データベース接続を確認... ✅

インストールが終わったら以下のURLにアクセスして初期設定
Default Portは3000ですが、初期設定後に設定画面からURL含めて変更する事が可能です。


http://localhost:3000/setup/

レポーティングとクエリーコンソール

ユーザー管理
アカウントやグループ毎に関連するデータベース接続があっても良いかもしれません。(監査等)
共通アプリケーションアカウントでも問題ありません。
機密情報や重要な情報にアクセス出来ないようにデータベースアカウントの権限を絞ると良いかと思います。

その他、Slack等との連携

なかなか便利なツールです。非エンジニア以外の方にはテンプレートを作成してあげたり、読み込み専用DBにアクセスを分けてあげると安心です。
Let’s enjoy metabase.


DB Tech Showcase 2018 Tokyo

DB Tech Showcase 2018 TokyoにてInnoDB Clusterを用いた、MySQLの高可用性構成への取り組みに関して紹介させて頂きました。プロダクションのデータベースなので、これまでの機能紹介やシンプルなデモとは異なり、様々な確認や調整が必要で、DBのアップグレード以上に時間を割いている感じです。移行後は、シンプルにマスターを切り替える事で、MySQL Routerが自動的に接続を切り替えてくれるので非常に楽になります。
また、作業工数も大幅に削減出来るので、会社にとってもダウンタイムによる機会損失削減といったメリットだけでなく、エンジニアの工数削減によりサービス開発に専念が出来るようになり非常に良いソリューションだと考えています。

これからもMySQLだけでは無く、様々な高可用性構成をコストと運用の観点から考察して、順次導入していきたいと思っています。
システム移行と同時に様々な、改善も並行して出来るので1年を目途に最適化が出来ると良いかなと思ってます。

“MySQL5.6から5.7へ、そして更なるサービスの可用性を目指して ~急成長する靴の通販 LOCONDO.jp を支えるサービス安定化への取り組み”

補足:
資料には明記してませんが、シングルプライマリーモードで構成しているので、グループレプリケーション構成直後に以下の値を全て1に変更しています。


group_replication_auto_increment_increment 
auto_increment_increment
auto_increment_offset

Group Replicationのマスター、スレーブ間のレプリケーションラグの確認方法

select @last_exec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( @@global.GTID_EXECUTED,':',-1),':',1),'-',-1) last_executed;select @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( Received_transaction_set,':',-1),':',1),'-',-1) last_received FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';select (@last_rec - @last_exec) as real_lag;

補足:RECOVERY中は値が正確に取得出来ない様子
https://bugs.mysql.com/bug.php?id=92219


MySQL用のコネクター、Connector/Jの動作を改めて確認してみました。
検証した内容としては、jdbc:mysql、jdbc:mysql:replication、jdbc:mysql+ReplicationDriver, jdbc:mysql:loadbalanceの接続方法による挙動の違い。

検証環境:
Connector/J: mysql-connector-java-community-5.1.36-bin.jar
MySQL環境:  mysql 5.7.21 でグループレプリケーション(シングルマスターモード)


-bash-4.2$ ./gr_mysql_status.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 1d69db5d-a273-11e8-b673-080027d65c57 | replications | 3310 | ONLINE |
| group_replication_applier | 271d12be-a273-11e8-bc0a-080027d65c57 | replications | 3320 | ONLINE |
| group_replication_applier | 2e395847-a273-11e8-866f-080027d65c57 | replications | 3330 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
-bash-4.2$

オフィシャルマニュアルには以下のように書いてあるけど、十分に検証しないといけないので少し落ち着いたタイミングにならないとアップグレードは少々躊躇する。
変更点を一つ一つ確認していくのは難しいので、にバージョン毎の差を明確に、分かりやすく書いてあるマニュアルがあると有難い。
現状でも、5.1.17~5.1.47までリリースがある。

MySQL Connector / J 8.0は、MySQL Server 8.0,5.7,5.6、および5.5での使用を強く推奨します。 MySQL Connector / J 8.0にアップグレードしてください。

Connector / J 5.1は、JDBC 3.0,4.0,4.1、および4.2仕様に準拠したタイプ4の純粋なJava JDBCドライバーです。 5.5,5.6、および5.7を含むMySQLのすべての機能との互換性を提供します。 Connector / J 5.1は、Driver Managerによる自動登録、標準化された妥当性チェック、分類されたSQLExceptions、大規模な更新回数のサポート、java.timeパッケージからのローカルおよびオフセットの日時のサポートのサポート、 JDBC-4.x XML処理、接続ごとのクライアント情報のサポート、NCHAR、NVARCHARおよびNCLOBデータ型のサポートします。

Connector / J 8.0は、Java 8プラットフォーム用のタイプ4の純粋なJava JDBC 4.2ドライバーです。 これは、MySQL 5.5,5.6,5.7,8.0のすべての機能との互換性を提供します。

Connector / Jのバージョンの概要

*いくつかの暗号スイートを使用する場合、Connector / J 5.1がMySQL 5.6,5.7、および8.0にSSL / TLSで接続するためには、JRE 1.8.xが必要です。
*Connector / Jの現在の推奨バージョンは5.1です。 このガイドでは、以前のバージョンのConnector / Jについても説明します。具体的な注釈は、設定が特定のバージョンに適用される場合に指定します。

Connector / Jに必要なJavaバージョンの要約

Pattern(1): Standard Connector/J (jdbc:mysql)

8.1 Configuring Server Failover


[root@GA01 java]# cat conn_j_standard.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_standard{
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";
conn = DriverManager.getConnection("jdbc:mysql://" + strSQLNode1 + "," +strSQLNode2 +',' + strSQLNode3 + "/" +
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&autoReconnect=true" +
"&autoReconnectForPools=true"+
"&failOverReadOnly=true"+
"&roundRobinLoadBalance=true"+
"&connectTimeout=30000"+
"&socketTimeout=30000"+
"&maxReconnects=1"+
"&initialTimeout=1"+
"&loadBalanceBlacklistTimeout=3600000"+
"&loadBalancePingTimeout=100"
);
//conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

コンパイルして実行してみます。 基本的には、常時左から順に利用可能なサーバーにアクセスして処理します。 但し、アクセスしているサーバーに障害が発生した場合は、例2のようにフェールオーバーする事が可能です。


[root@GA01 java]# ls -l conn_j_standard.*
-rw-r--r--. 1 root root 2275 8月 18 08:26 conn_j_standard.class
-rw-r--r--. 1 root root 2443 8月 18 08:26 conn_j_standard.java
[root@GA01 java]#

[root@GA01 java]# java testmysql
0 replications 3310
1 replications 3310
2 replications 3310
3 replications 3310
4 replications 3310
5 replications 3310
6 replications 3310
7 replications 3310
8 replications 3310
9 replications 3310
10 replications 3310

例2)10回目の接続時に最初にアクセスしたサーバーをダウンさせたらフェールオーバーします。一番、左側に記載したサーバーが利用可能になると、一番左側のサーバーにアクセスします。(オプション設定)

[root@GA01 java]# java testmysql
0 replications 3310
1 replications 3310
2 replications 3310
3 replications 3310
4 replications 3310
5 replications 3310
6 replications 3310
7 replications 3310
8 replications 3310
9 replications 3310
10 replications 3320
11 replications 3320
12 replications 3320
13 replications 3320
14 replications 3320
15 replications 3320

Pattern(2): Connector/J Replication接続 (jdbc:mysql:replication)

8.3 Configuring Master/Slave Replication with Connector/J


[root@GA01 java]# cat conn_j_replication.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_replication {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";

conn = DriverManager.getConnection("jdbc:mysql:replication://" + strSQLNode1 + "," +strSQLNode2 + "," + strSQLNode3 + "/"+
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&autoReconnect=true" +
"&autoReconnectForPools=true"+
"&failOverReadOnly=true"+
"&roundRobinLoadBalance=true"+
"&initialTimeout=1"
);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

コンパイルして実行してみます。基本的に, 以下の設定でマスターへの接続かスレーブへの接続か判断して処理します。

conn.setReadOnly(false) = マスター接続(特に記載なければマスター接続)
conn.setReadOnly(true)  = スレーブ


[root@GA01 java]# javac conn_j_replication.java
[root@GA01 java]# java conn_j_replication
0 replications 3320
1 replications 3330
2 replications 3330
3 replications 3330
4 replications 3330
5 replications 3330
6 replications 3320
7 replications 3330
8 replications 3330
9 replications 3320
10 replications 3320
11 replications 3320
12 replications 3320
13 replications 3320
14 replications 3320
15 replications 3330
[root@GA01 java]#

Pattern(3): Connector/J Replication接続② (jdbc:mysql + 明示的にレプリケーションドライバーを指定)
マニュアルには具体的な内容は発見出来ていませんが以下のようにもコメントされています。現状では上記のようにjdbc:mysql:Replicationでコーディングする方が主流なのかと。

Deprecate宣言

com.mysql.jdbc.[NonRegistering]Driver now understands URLs of the format jdbc:mysql:replication:// and jdbc:mysql:loadbalance:// which will create a ReplicationConnection (exactly like when using [NonRegistering]ReplicationDriver) and an experimental load-balanced connection designed for use with SQL nodes in a MySQL Cluster/NDB environment, respectively.

In an effort to simplify things, we’re working on deprecating multiple drivers, and instead specifying different core behavior based upon JDBC URL prefixes, so watch for [NonRegistering]ReplicationDriver to eventually disappear, to be replaced with com.mysql.jdbc[NonRegistering]Driver with the new URL prefix.

https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-0-6.html

Remove宣言

4.3.1.6 Other Changes
Here are other changes with Connector/J 8.0:

Removed ReplicationDriver. Instead of using a separate driver, you can now obtain a connection for a replication setup just by using the jdbc:mysql:replication:// scheme.

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html

If you have a write transaction, or if you have a read that is time-sensitive (remember, replication in MySQL is asynchronous), set the connection to be not read-only, by calling Connection.setReadOnly(false) and the driver will ensure that further calls are sent to the master MySQL server. The driver takes care of propagating the current state of autocommit, isolation level, and catalog between all of the connections that it uses to accomplish this load balancing functionality.

To enable this functionality, use the com.mysql.jdbc.ReplicationDriver class when configuring your application server’s connection pool or when creating an instance of a JDBC driver for your standalone application. Because it accepts the same URL format as the standard MySQL JDBC driver, ReplicationDriver does not currently work with java.sql.DriverManager-based connection creation unless it is the only MySQL JDBC driver registered with the DriverManager .

書き込みトランザクションがある場合や、読み込み時間が重要な場合(MySQLのレプリケーションが非同期であることを覚えておいてください)、Connection.setReadOnly(false)を呼び出して接続を読み取り専用に設定すると、ドライバは それ以降のコールがマスターMySQLサーバーに送信されるようにしてください。 ドライバは、このロードバランシング機能を達成するために使用するすべての接続間で、自動コミット、分離レベル、およびカタログの現在の状態を伝達します。

この機能を有効にするには、アプリケーション・サーバーの接続プールを構成するとき、またはスタンドアロン・アプリケーション用のJDBCドライバのインスタンスを作成するときにcom.mysql.jdbc.ReplicationDriverクラスを使用します。 標準のMySQL JDBCドライバと同じURLフォーマットを受け入れるため、DriverManagerに登録されている唯一のMySQL JDBCドライバでない限り、ReplicationDriverは現在java.sql.DriverManagerベースの接続作成では動作しません。


[root@GA01 java]# cat conn_j_standard2.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_standard2 {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
ReplicationDriver driver = new ReplicationDriver();

try {
for(int i=0; i < 100; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

Properties props = new Properties();
props.put("autoReconnect", "true");
props.put("roundRobinLoadBalance","true");
props.put("failOverReadOnly","true");
props.put("user","router_user");
props.put("password","password");

Connection conn =
driver.connect(
"jdbc:mysql://192.168.56.111:3310,192.168.56.111:3320,192.168.56.111:3330/demodb",props);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

jdbc:mysql:Replicationと同じく、conn.setReadOnly(false)はマスターにconn.setReadOnly(true)はスレーブに参照しに行きます。
上記の例だとスレーブ間をラウンドロビンします。

[root@GA01 java]javac conn_j_standard2.java
[root@GA01 java]# java conn_j_standard2
0 replications 3320
1 replications 3330
2 replications 3330
3 replications 3330
4 replications 3330
5 replications 3330
6 replications 3330
7 replications 3320
8 replications 3330
9 replications 3320
10 replications 3320
11 replications 3320
12 replications 3330
13 replications 3320
14 replications 3320
15 replications 3330

Pattern(4): Connector/J Load Balance処理用の接続 (jdbc:mysql:loadbalance)

8.2 Configuring Load Balancing with Connector/J


[root@GA01 java]# cat conn_j_loadbalance.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_loadbalance {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";
conn = DriverManager.getConnection("jdbc:mysql:loadbalance://" + strSQLNode1 + "," + strSQLNode2 + "," + strSQLNode3 + "/"+
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&loadBalanceConnectionGroup=first" +
"&loadBalanceEnableJMX=true"
);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}

[root@GA01 java]#

以下のように、全てのサーバーにロードバランスされるのでマルチマスター環境で利用可能です。MySQL NDB Cluster, MySQL InnoDB Cluster (Multi Master設定)の構成で便利です。
但し、InnoDB Clusterの場合は制限事項があるので、バッチ処理等と同じデータベースと利用する場合は十分に検証が必要。


[root@GA01 java]# javac conn_j_loadbalance.java
[root@GA01 java]# ls -l conn_j_loadbalance.*
-rw-r--r--. 1 root root 1992 8月 18 09:03 conn_j_loadbalance.class
-rw-r--r--. 1 root root 1890 8月 18 09:04 conn_j_loadbalance.java
[root@GA01 java]#

[root@GA01 java]# java conn_j_loadbalance
0 replications 3330
1 replications 3330
2 replications 3320
3 replications 3310
4 replications 3310
5 replications 3330
6 replications 3310
7 replications 3330
8 replications 3330
9 replications 3330
10 replications 3320
11 replications 3320
12 replications 3330
13 replications 3330
14 replications 3320
15 replications 3310

参考:
MySQL Connector/J 5.1 Developer Guide
Chapter 10 Using Connector/J with Tomcat
Chapter 12 Using Connector/J with Spring


MySQL5.7からMySQL8.0へのアップグレード対応時のメモ

他のインスタンスも既に、アップグレードしましたが、UTF8MB4以外の文字コードを利用していたり、パーティションエンジンを利用していたので、
少々手間取ってしまいました。こちらの、アップグレード例はもともと、MySQL5.7で初期インストールして利用していたデータベースでUTF8MB4を利用して、
パーティションもInnoDB Native Partitionを利用していたので直ぐにアップグレードする事が出来ました。ただし、いくつか設定を変更しました。

アップグレード手順
1: mysqlsh (Ver 8.0.11) にてアップグレード事前チェックを行う
2: MySQL8.0のバリナリーダウンロード (Generic Tarを利用しました)
3: 既存のサービスを停止して、シンボリックリンクの張り直し
4: 必要なファイルやフォルダーをコピー(直ぐに、ロールバック出来るようにオリジナルフォルダーは残しています)
5: my.cnfに事前に必要な設定を入れて、MySQL8.0に無いパラメータを削除しておく
6: MySQLサービスを開始して、mysql_upgradeを実行
7: 必要に応じて、SchemaやTableのCOLLATEを変更
8: アプリケーションの接続やStrictモードを必要に応じて変更
9: 動作確認して終了

STEP1: mysqlshによるアップグレード対象インスタンスの互換性の確認
色々なアドバイスが出てくるので、必要に応じて適宜対応してください。(例:文字コード変換、パーティションストレージエンジンをInnoDBに変換等)


[root@AP01 bin]# ./mysqlsh root:password@localhost:3306 -e "util.checkForServerUpgrade();"
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.21-log - MySQL Community Server (GPL)

1) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

2) Usage of utf8mb3 charset
  No issues found

3) Usage of use ZEROFILL/display length type attributes
  Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0

  APP.T_Laravel.age - int(4)

4) Issues reported by 'check table x for upgrade' command
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Usage of old temporal type
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Usage of removed functions
  No issues found

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@AP01 bin]# 

■ 8.0のバイナリーをダウンロードして展開

[root@AP01 local]# tar zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz 
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisampack
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_config_editor
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_secure_installation
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_ssl_rsa_setup
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_tzinfo_to_sql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_upgrade
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqladmin
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlbinlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlcheck
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqldump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlimport
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mys

■既存のサービスを停止して、シンボリックリンクの張り直し

[root@AP01 local]# ls -l 
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  2月 19 17:04 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS! 
[root@AP01 local]# rm mysql
rm: シンボリックリンク `mysql' を削除しますか? y
[root@AP01 local]# ln -s mysql-8.0.11-linux-glibc2.12-x86_64 mysql
[root@AP01 local]# ls -l
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  4月 23 14:05 mysql -> mysql-8.0.11-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# 

■必要なファイルやフォルダーをコピー

[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# ls -l
合計 44
-rw-r--r--.  1  7161 31415 17987 12月 28 12:46 COPYING
-rw-r--r--.  1  7161 31415  2478 12月 28 12:46 README
drwxr-xr-x.  2 root  root   4096  2月 19 17:03 bin
drwxr-x---.  6 mysql mysql  4096  4月 23 14:06 data
drwxr-xr-x.  2 root  root     52  2月 19 17:03 docs
drwxr-xr-x.  3 root  root   4096  2月 19 17:03 include
drwxr-xr-x.  5 root  root   4096  2月 19 17:03 lib
drwxr-xr-x.  2 mysql mysql    60  2月 19 17:14 logs
drwxr-xr-x.  4 root  root     28  2月 19 17:03 man
drwxr-x---.  2 mysql mysql     6  2月 19 17:05 mysql-files
drwxr-xr-x. 28 root  root   4096  2月 19 17:03 share
drwxr-xr-x.  2 root  root     86  2月 19 17:03 support-files
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp data /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp mysql-files /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# 

■ my.cnfに以下の設定を入れておく。

default_authentication_plugin=mysql_native_password

また、mysql8.0に無いパラメータは、削除しておかないとエラーになるので削除しておきましょう。
例)2018-04-23T05:39:57.859413Z 0 [ERROR] [MY-011071] [Server] unknown variable ‘query_cache_type=0’

パラメータのチェックに関しては、こちらのページが便利です。
https://tmtm.github.io/mysql-params/?vers=5.7.22,8.0.11&diff=true

■ MySQLの起動とUpgrade


[root@AP01 data]# /etc/init.d/mysql.server start
Starting MySQL... SUCCESS! 
[root@AP01 data]#

[root@AP01 mysql]# mysql_upgrade -u root -p
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
APP.T_ChangeLog                                    OK
APP.T_Laravel                                      OK
APP.T_business                                     OK
APP.T_databases                                    OK
APP.T_others                                       OK
APP.T_scripts                                      OK
APP.cars                                           OK
APP.migrations                                     OK
APP.password_resets                                OK
APP.users                                          OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@AP01 mysql]# 

■ ファイルが自動的にアップグレードされ、メタデータ関連ファイルが無くなっている事が確認できる。

[root@AP01 mysql]# ls -l
合計 10732
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 columns_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 columns_priv.MYI
-rw-r-----. 1 mysql mysql    7763  4月 23 14:36 columns_priv_83.sdi
-rw-r-----. 1 mysql mysql    1464  2月 19 17:28 db.MYD
-rw-r-----. 1 mysql mysql    5120  2月 19 17:28 db.MYI
-rw-r-----. 1 mysql mysql   19285  4月 23 14:36 db_84.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 engine_cost.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 func.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:27 func.MYI
-rw-r-----. 1 mysql mysql    4819  4月 23 14:36 func_87.sdi
-rw-r-----. 1 mysql mysql      35  4月 23 14:31 general_log.CSM
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 general_log.CSV
-rw-r-----. 1 mysql mysql    5520  4月 23 14:36 general_log_88.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 gtid_executed.ibd
-rw-r-----. 1 mysql mysql  131072  4月 23 14:36 help_category.ibd
-rw-r-----. 1 mysql mysql  262144  4月 23 14:36 help_keyword.ibd
-rw-r-----. 1 mysql mysql  163840  4月 23 14:36 help_relation.ibd
-rw-r-----. 1 mysql mysql 8388608  4月 23 14:36 help_topic.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:05 ndb_binlog_index.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:05 ndb_binlog_index.MYI
-rw-r-----. 1 mysql mysql   10729  4月 23 14:36 ndb_binlog_index_96.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 plugin.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 procs_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 procs_priv.MYI
-rw-r-----. 1 mysql mysql    8960  4月 23 14:36 procs_priv_98.sdi
-rw-r-----. 1 mysql mysql     837  2月 19 17:05 proxies_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:05 proxies_priv.MYI
-rw-r-----. 1 mysql mysql    7813  4月 23 14:36 proxies_priv_99.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 server_cost.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 servers.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_master_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_relay_log_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_worker_info.ibd
-rw-r-----. 1 mysql mysql      35  4月 23 14:32 slow_log.CSM
-rw-r-----. 1 mysql mysql  104677  4月 23 14:31 slow_log.CSV
-rw-r-----. 1 mysql mysql   11741  4月 23 14:36 slow_log_105.sdi
-rw-r-----. 1 mysql mysql    1894  2月 19 17:27 tables_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:50 tables_priv.MYI
-rw-r-----. 1 mysql mysql    9379  4月 23 14:36 tables_priv_106.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_leap_second.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_name.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition_type.ibd
-rw-r-----. 1 mysql mysql     504  2月 19 17:27 user.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:50 user.MYI
-rw-r-----. 1 mysql mysql   36754  4月 23 14:36 user_112.sdi
[root@AP01 mysql]# 


[root@AP01 mysql]# ls -l
合計 444
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 general_log.CSM
-rw-r-----. 1 mysql mysql      0  4月 23 14:43 general_log.CSV
-rw-r-----. 1 mysql mysql   5520  4月 23 14:43 general_log_365.sdi
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 slow_log.CSM
-rw-r-----. 1 mysql mysql 109876  4月 23 14:43 slow_log.CSV
-rw-r-----. 1 mysql mysql  11741  4月 23 14:43 slow_log_367.sdi
[root@AP01 mysql]# 

必要に応じて、既存データベースやテーブルのCOLLATION(照合順序)を変更しておく

root@localhost [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */
1 row in set (0.00 sec)

root@localhost [APP]> alter schema APP DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.08 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
1 row in set (0.00 sec)

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `comment` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost [APP]> alter table `T_ChangeLog` convert to character set utf8mb4;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `comment` varchar(2048) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root@localhost [APP]> 

■アプリケーションの接続や必要に応じてStrictモードを変更
メモ: COMPOSERで最新版のフレームワークにアップグレード後にTRUEに戻しました。
(本番環境では、意図しないエラーが発生することもあるので注意してTRUE,FALSEの設定を行ってください。)

            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_0900_ai_ci',
            'prefix' => '',
            /* 'strict' => true, */
            'strict' => false,

NO_AUTO_CREATE_USER等の、SQLモードが削除されているのでフレームワークによっては、StrictモードがTrueのままだと、
アプリケーションがエラーになる場合があります。

参照:アカウント管理に関連する次の機能は削除されました。
GRANTを使用してユーザーを作成する代わりに、CREATE USERを使用するようになっています。 “NO_AUTO_CREATE_USER” SQLモードはGRANT文にとって重要ではない為、削除されました。
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

その他、参考:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html