Oracle MySQL Cloud ServiceでMySQLインスタンスのPoint In Time Recoveryを実行
MySQLCS (MySQL Cloud Service)
ダッシュボードから時間指定でのリストアを実行した時に、バックグラウンドで動いているプロセスの確認になります。

STEP1) 既存データをフルバックアップ
STEP2) 差分データのINSERT
STEP3) 差分バックアップ
STEP4) データの削除
STEP5) STEP3の直前まで時間指定でリストア
STEP6) データの確認

■ FULL BACKUP実行時のデータ

mysql> show tables;
+--------------+
| Tables_in_SC |
+--------------+
| T_POT_TEST   |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T_POT_TEST;
+----+-----------+---------+
| id | body      | type    |
+----+-----------+---------+
|  1 | 123456789 | INTEGER |
|  2 | "abcde"   | STRING  |
+----+-----------+---------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:20:56 |
+---------------------+
1 row in set (0.00 sec)

■ FULLバックアップ

mysql> select * from backup_history order by backup_id desc limit 1\G
*************************** 1. row ***************************
                backup_id: 14855264590951390
                tool_name: /u01/bin/meb/bin/mysqlbackup --user=oracle --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --backup-image=backufull.mbi --compress backup-to-image 
               start_time: 2017-01-27 14:14:19
                 end_time: 2017-01-27 14:14:28
               binlog_pos: 154
              binlog_file: opc-mysql01-mysql-1.000001
        compression_level: 1
                  engines: CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA
    innodb_data_file_path: ibdata1:12M:autoextend
       innodb_file_format: Barracuda
                start_lsn: 6623068672
                  end_lsn: 6623068712
     incremental_base_lsn: 0
              backup_type: FULL
            backup_format: IMAGE
           mysql_data_dir: /u01/data/mysql/
     innodb_data_home_dir: 
innodb_log_group_home_dir: /u01/translog/inno-bin-logs
innodb_log_files_in_group: 6
     innodb_log_file_size: 1073741824
       backup_destination: /u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d
                lock_time: 0.809
               exit_state: SUCCESS
               last_error: NO_ERROR
          last_error_code: 0
1 row in set (0.01 sec)

■ FULL BACKUP後にデータをINSERT

mysql> insert into T_POT_TEST(body) values('"PITR TEST"');
Query OK, 1 row affected (0.11 sec)

mysql> select * from T_POT_TEST;
+----+-------------+---------+
| id | body        | type    |
+----+-------------+---------+
|  1 | 123456789   | INTEGER |
|  2 | "abcde"     | STRING  |
|  3 | "PITR TEST" | STRING  |
+----+-------------+---------+
3 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:21:46 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

■ ここで差分を取得してみる。

mysql> select * from backup_history order by backup_id desc limit 1\G
*************************** 1. row ***************************
                backup_id: 14855271179669251
                tool_name: /u01/bin/meb/bin/mysqlbackup --user=oracle --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledIncremental/63412628-3fcd-4b60-9e9d-ef1bacc15032 --backup-image=backup_incremental.mbi --incremental_base=dir:/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --incremental backup-to-image 
               start_time: 2017-01-27 14:25:17
                 end_time: 2017-01-27 14:25:21
               binlog_pos: 19807766
              binlog_file: opc-mysql01-mysql-1.000002
        compression_level: 0
                  engines: CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA
    innodb_data_file_path: ibdata1:12M:autoextend
       innodb_file_format: Barracuda
                start_lsn: 6623068713
                  end_lsn: 6633728074
     incremental_base_lsn: 0
              backup_type: INCREMENTAL
            backup_format: IMAGE
           mysql_data_dir: /u01/data/mysql/
     innodb_data_home_dir: 
innodb_log_group_home_dir: /u01/translog/inno-bin-logs
innodb_log_files_in_group: 6
     innodb_log_file_size: 1073741824
       backup_destination: /u01/backup/MySQLCS/OPC-MYSQL01/scheduledIncremental/63412628-3fcd-4b60-9e9d-ef1bacc15032
                lock_time: 0.684
               exit_state: SUCCESS
               last_error: NO_ERROR
          last_error_code: 0
1 row in set (0.00 sec)

mysql> 

■テーブルからデータを削除します。

mysql> truncate table T_POT_TEST;select * from T_POT_TEST;select now();
Query OK, 0 rows affected (0.02 sec)

Empty set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:28:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

■ 目標リストア時間を指定してリストア

2017-01-27 14:21:45

※こちらの画像はサンプルですので、時間は上記の時間を指定しました。

■ダッシュボードから時間指定でリストア開始
STEP1) フルリストアが実行されている


[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   14423 14348 20 14:31 ?        00:00:05 /u01/bin/meb/bin/mysqlbackup --user=oracle --cloud-service=openstack --cloud-user-id=Storage-xxxxxxx:xxxxx.xxxxx@oracle.com --cloud-password=xxxxxxxxxx --cloud-tempauth-url=https://xxxxxxx.storage.oraclecloud.com --cloud-container=mysqlstorage --cloud-object=MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d/backup_full.mbi --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --cloud-ca-info=/etc/ssl/certs/ca-bundle.crt --datadir=/u01/data/mysql-14348 --innodb_log_group_home_dir=/u01/translog/inno-bin-logs-14348 --backup-image=- --uncompress --force copy-back-and-apply-log
oracle   14459 10312  0 14:32 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

STEP2) 差分リストアが実行されている

[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   14509     1  0 14:33 ?        00:00:00 /bin/sh ./bin/mysqld_safe --skip-networking
oracle   15593 14509 25 14:33 ?        00:00:06 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --skip-networking --log-error=/u01/data/mysql/opc-mysql01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/opc-mysql01-mysql-1.pid
oracle   15721 14348  0 14:33 ?        00:00:00 /bin/sh -c /u01/bin/mysql/bin/mysqlbinlog --skip-gtids --start-position=154 --stop-datetime="2017-01-27 14:21:45"  /u01/backup/temp/datadir/opc-mysql01-mysql-1.000001 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000002|/u01/bin/mysql/bin/mysql -uoracle 
oracle   15722 15721  1 14:33 ?        00:00:00 /u01/bin/mysql/bin/mysqlbinlog --skip-gtids --start-position=154 --stop-datetime=2017-01-27 14:21:45 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000001 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000002
oracle   15723 15721  3 14:33 ?        00:00:00 /u01/bin/mysql/bin/mysql -uoracle
oracle   15729 10312  0 14:34 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

STEP3) MySQLの起動

[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   15811     1  1 14:35 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   16875 15811 28 14:35 ?        00:00:00 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/opc-mysql01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/opc-mysql01-mysql-1.pid
oracle   16974 10312  0 14:35 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

※ リストアが問題無く終わると自動的にFULLバックアップの取得が開始されます。

■リストア後のデータ確認


mysql> select * from T_POT_TEST;select now();
+----+-------------+---------+
| id | body        | type    |
+----+-------------+---------+
|  1 | 123456789   | INTEGER |
|  2 | "abcde"     | STRING  |
|  3 | "PITR TEST" | STRING  |
+----+-------------+---------+
3 rows in set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:36:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> 


MySQL Cluster7.5でNDBINFOに新たに便利なテーブルが追加されているので、此方にリストアップしておきました。詳細に関しては、以下のマニュアルを参照下さい。

MySQL Cluster7.5 pdfマニュアル
20.1.4 What is New in MySQL NDB Cluster 7.5

あと、オンラインでデータノードを追加したあとに、テーブルのデータを再分布する場合のコマンドがALTER TABLE ~ ONLINEではなくなりました。
MySQLと同じALTER TABLE ~ ALGORITHM = INPLACE ~に変わりました。

ALTER TABLE Changes:
NDB Cluster formerly supported an alternative syntax for online ALTER TABLE.
This is no longer supported in NDB Cluster 7.5, which makes exclusive use of
ALGORITHM = DEFAULT|COPY|INPLACE for table DDL, as in the standard MySQL Server.

New Tables in NDBINFO
メタデータの確認や、MySQL Cluster内部で何処でLOCKが発生しているか確認し易くなってます。
便利です。


/***** provides per-thread CPU statistics gathered each second, for each thread running in the NDB kernel. ******/

mysql> select * from cpustat;
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
| node_id | thr_no | OS_user | OS_system | OS_idle | thread_exec | thread_sleeping | thread_spinning | thread_send | thread_buffer_full | elapsed_time |
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
|       1 |      0 |       0 |         0 |      99 |           1 |              99 |               0 |           0 |                  0 |      1022319 |
|       1 |      1 |       0 |         1 |      99 |           0 |             100 |               0 |           0 |                  0 |      1043157 |
|       1 |      2 |       1 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1044002 |
|       1 |      3 |       6 |         1 |      93 |           2 |              98 |               0 |           0 |                  0 |      1004561 |
|       2 |      0 |       0 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1021821 |
|       2 |      1 |       0 |         0 |     100 |           0 |             100 |               0 |           0 |                  0 |      1041181 |
|       2 |      2 |       0 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1000446 |
|       2 |      3 |       4 |         4 |      92 |           2 |              98 |               0 |           0 |                  0 |      1006593 |
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
8 rows in set (0.01 sec)

/***** provides raw, per-thread CPU data obtained each second for each thread running in the NDB kernel.  ******/

mysql> select * from cpustat_1sec limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |         4249 |           6543 |      1008090 |      6723 |    1012159 |         0 |         0 |                0 |      1018882 |
|       1 |      0 |         3042 |           7882 |      1012472 |      6636 |    1016760 |         0 |         0 |                0 |      1023396 |
|       1 |      0 |         3779 |           6373 |      1003011 |      6882 |    1006281 |         0 |         0 |                0 |      1013163 |
|       1 |      0 |         2011 |           6151 |      1030521 |      5951 |    1032732 |         0 |         0 |                0 |      1038683 |
|       1 |      0 |         4056 |           5567 |      1039330 |      6329 |    1042624 |         0 |         0 |                0 |      1048953 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

/***** provides raw, per-thread CPU data obtained each 50 milliseconds for each thread running in the NDB kernel ******/

mysql> select * from cpustat_50ms limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |          138 |            180 |        56563 |       244 |      56637 |         0 |         0 |                0 |        56881 |
|       1 |      0 |          411 |            537 |        58241 |       626 |      58563 |         0 |         0 |                0 |        59189 |
|       1 |      0 |          124 |            630 |        52635 |       471 |      52918 |         0 |         0 |                0 |        53389 |
|       1 |      0 |            0 |            943 |        49634 |       398 |      50179 |         0 |         0 |                0 |        50577 |
|       1 |      0 |            0 |            377 |        52557 |       299 |      52635 |         0 |         0 |                0 |        52934 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

mysql>

/***** provides raw, per-thread CPU data obtained each 20 seconds, for each thread running in the NDB kernel. ******/

mysql> mysql> select * from cpustat_20sec limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |        85502 |         105281 |     19833351 |    131831 |   19892303 |         0 |         0 |                0 |     20024134 |
|       1 |      0 |        85028 |          98842 |     19820167 |    121907 |   19882130 |         0 |         0 |                0 |     20004037 |
|       1 |      0 |        77030 |         105719 |     19842259 |    121151 |   19903857 |         0 |         0 |                0 |     20025008 |
|       1 |      0 |        74431 |         101572 |     19827545 |    122715 |   19880833 |         0 |         0 |                0 |     20003548 |
|       1 |      0 |        85342 |         101835 |     19813255 |    127802 |   19872630 |         0 |         0 |                0 |     20000432 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides information about threads running in the NDB kernel. ******/
 

mysql> select * from threads;
+---------+--------+-------------+------------------------------------------------------------------+
| node_id | thr_no | thread_name | thread_description                                               |
+---------+--------+-------------+------------------------------------------------------------------+
|       1 |      0 | main        | main thread, schema and distribution handling                    |
|       1 |      1 | rep         | rep thread, asynch replication and proxy block handling          |
|       1 |      2 | ldm         | ldm thread, handling a set of data partitions                    |
|       1 |      3 | recv        | receive thread, performing receieve and polling for new receives |
|       2 |      0 | main        | main thread, schema and distribution handling                    |
|       2 |      1 | rep         | rep thread, asynch replication and proxy block handling          |
|       2 |      2 | ldm         | ldm thread, handling a set of data partitions                    |
|       2 |      3 | recv        | receive thread, performing receieve and polling for new receives |
+---------+--------+-------------+------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> 


/***** provides information about current lock requests holding and waiting for locks on NDB tables in an NDB Cluster , and is intended as a companion table to cluster_operations. ******/

mysql> desc cluster_locks;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| node_id         | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance  | int(10) unsigned    | YES  |     | NULL    |       |
| tableid         | int(10) unsigned    | YES  |     | NULL    |       |
| fragmentid      | int(10) unsigned    | YES  |     | NULL    |       |
| rowid           | bigint(20) unsigned | YES  |     | NULL    |       |
| transid         | bigint(22) unsigned | YES  |     | NULL    |       |
| mode            | varchar(1)          | NO   |     |         |       |
| state           | varchar(1)          | NO   |     |         |       |
| detail          | varchar(1)          | NO   |     |         |       |
| op              | varchar(9)          | NO   |     |         |       |
| duration_millis | int(10) unsigned    | YES  |     | NULL    |       |
| lock_num        | int(10) unsigned    | YES  |     | NULL    |       |
| waiting_for     | bigint(10) unsigned | YES  |     | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

mysql> 



/***** provides information about counts of lock claim requests, and the outcomes of these requests on a per-fragment basis, serving as a companion table to operations_per_fragment and memory_per_fragment.  ******/

mysql> select * from locks_per_fragment limit 5;
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| fq_name                      | parent_fq_name         | type              | table_id | node_id | block_instance | fragment_num | ex_req | ex_imm_ok | ex_wait_ok | ex_wait_fail | sh_req | sh_imm_ok | sh_wait_ok | sh_wait_fail | wait_ok_millis | wait_fail_millis |
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       1 |              1 |            0 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       1 |              1 |            1 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       2 |              1 |            0 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       2 |              1 |            1 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sys/def/24/idx_memo01$unique | TEST_DB/def/T_TABLE01  | Unique hash index |       79 |       1 |              1 |            0 |     10 |        10 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
5 rows in set (0.03 sec)

mysql> 



mysql> select * from locks_per_fragment where fq_name like '%sbtest1';
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| fq_name              | parent_fq_name | type       | table_id | node_id | block_instance | fragment_num | ex_req | ex_imm_ok | ex_wait_ok | ex_wait_fail | sh_req | sh_imm_ok | sh_wait_ok | sh_wait_fail | wait_ok_millis | wait_fail_millis |
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| sysbench/def/sbtest1 | NULL           | User table |       90 |       1 |              1 |            0 |   5080 |      5080 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       1 |              1 |            1 |   4920 |      4920 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       2 |              1 |            0 |   5080 |      5080 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       2 |              1 |            1 |   4920 |      4920 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
4 rows in set (0.03 sec)

mysql> 


/***** similar in structure to the cluster_locks table, and provides a subset of the information found in the latter table, but which is specific to the SQL node ******/

mysql> desc server_locks;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| mysql_connection_id | bigint(21) unsigned | NO   |     | 0       |       |
| node_id             | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance      | int(10) unsigned    | YES  |     | NULL    |       |
| tableid             | int(10) unsigned    | YES  |     | NULL    |       |
| fragmentid          | int(10) unsigned    | YES  |     | NULL    |       |
| rowid               | bigint(20) unsigned | YES  |     | NULL    |       |
| transid             | bigint(22) unsigned | YES  |     | NULL    |       |
| mode                | varchar(1)          | NO   |     |         |       |
| state               | varchar(1)          | NO   |     |         |       |
| detail              | varchar(1)          | NO   |     |         |       |
| op                  | varchar(9)          | NO   |     |         |       |
| duration_millis     | int(10) unsigned    | YES  |     | NULL    |       |
| lock_num            | int(10) unsigned    | YES  |     | NULL    |       |
| waiting_for         | bigint(10) unsigned | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> 

/***** provides the names and types of database objects in NDB, as well as information about parent obejcts where applicable ******/

mysql> select * from dict_obj_info limit 5;
+------+------+----------+-------+-----------------+---------------+----------------------------+
| type | id   | version  | state | parent_obj_type | parent_obj_id | fq_name                    |
+------+------+----------+-------+-----------------+---------------+----------------------------+
|    6 |   89 |        1 |     4 |               2 |            88 | sys/def/88/PRIMARY         |
|   18 |   44 |        1 |     4 |               6 |            98 | NDB$INDEX_98_CUSTOM        |
|    2 |   31 | 16777218 |     4 |               0 |             0 | TEST_DB_InnoDB/def/Country |
|    6 |   59 |        1 |     4 |               2 |            33 | sys/def/33/PRIMARY         |
|   18 |    2 |        1 |     4 |               6 |            37 | NDB$INDEX_37_CUSTOM        |
+------+------+----------+-------+-----------------+---------------+----------------------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides NDB table distribution status information  ******/

mysql> select * from table_distribution_status limit 5;
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
| node_id | table_id | tab_copy_status | tab_update_status | tab_lcp_status | tab_status | tab_storage | tab_partitions | tab_fragments | current_scan_count | scan_count_wait | is_reorg_ongoing |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
|       1 |        2 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |        3 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       11 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       12 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       13 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides information about the distribution of NDB table fragments   ******/

mysql> select * from table_fragments limit 5;
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
| node_id | table_id | partition_id | fragment_id | partition_order | log_part_id | no_of_replicas | current_primary | preferred_primary | current_first_backup | current_second_backup | current_third_backup | num_alive_replicas | num_dead_replicas | num_lcp_replicas |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
|       1 |        2 |   4294967295 |           0 |               0 |           0 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        2 |   4294967295 |           1 |               0 |           0 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           0 |               0 |           1 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           1 |               0 |           1 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |       11 |   4294967295 |           0 |               0 |           3 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

/***** provides information about logging, checkpointing, storage, and other options in force for each NDB table    ******/

mysql> select * from table_info where table_id = 31;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       31 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
1 row in set (0.01 sec)

mysql> 


mysql> select * from table_info where table_id = 90;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       90 |            1 |                1 |                     1 |           1 |                0 | MEMORY       |          1 | SPECIFIC          |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
1 row in set (0.01 sec)

mysql> 

/***** provides information about fragment replicas   ******/

mysql> select * from table_replicas limit 5;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |        2 |           0 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           0 |           2 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        3 |           0 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
5 rows in set (0.00 sec)

mysql> 


mysql> select * from table_replicas where table_id = 90;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |       90 |           0 |       36225 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           0 |       36225 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           1 |       36225 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           1 |       36225 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
4 rows in set (0.01 sec)

mysql> 

config_paramsとconfig_valuesをJOINした結果

パラメータ確認し易くて良い


mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.02 sec)

cluster_locksでロックを確認した場合


mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              1 |     107 |          1 |     0 | 13569072998383655 | X    | W     |        | READ |            2593 |        0 |           1 |
|       2 |              1 |     107 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        1 |        NULL |
|       2 |              1 |     113 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        3 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
3 rows in set (0.04 sec)

MySQL7.5のDefault設定値
何も設定しなければ、こちらの値が適用されます。

+------------------------------------------+----------+------------+----------+----------------+----------+
| Name                                     | Type     | Default    | Minimum  | Maximum        | Required |
+------------------------------------------+----------+------------+----------+----------------+----------+
| MaxNoOfSubscriptions                     | unsigned | 0          | 0        | 4294967039     | N        |
| MaxNoOfSubscribers                       | unsigned | 0          | 0        | 4294967039     | N        |
| MaxNoOfConcurrentSubOperations           | unsigned | 256        | 0        | 4294967039     | N        |
| TcpBind_INADDR_ANY                       | bool     | 0          |          |                | N        |
| HostName                                 | string   | localhost  |          |                | N        |
| NodeId                                   | unsigned |            | 1        | 48             | Y        |
| ServerPort                               | unsigned |            | 1        | 65535          | N        |
| NoOfReplicas                             | unsigned | 2          | 1        | 4              | N        |
| MaxNoOfAttributes                        | unsigned | 1000       | 32       | 4294967039     | N        |
| MaxNoOfTables                            | unsigned | 128        | 8        | 20320          | N        |
| MaxNoOfOrderedIndexes                    | unsigned | 128        | 0        | 4294967039     | N        |
| MaxNoOfUniqueHashIndexes                 | unsigned | 64         | 0        | 4294967039     | N        |
| MaxNoOfConcurrentIndexOperations         | unsigned | 8192       | 0        | 4294967039     | N        |
| MaxNoOfTriggers                          | unsigned | 768        | 0        | 4294967039     | N        |
| MaxNoOfFiredTriggers                     | unsigned | 4000       | 0        | 4294967039     | N        |
| MaxNoOfSavedMessages                     | unsigned | 25         | 0        | 4294967039     | N        |
| LockExecuteThreadToCPU                   | bitmask  |            |          |                | N        |
| LockMaintThreadsToCPU                    | unsigned |            | 0        | 65535          | N        |
| RealtimeScheduler                        | bool     | 0          |          |                | N        |
| LockPagesInMainMemory                    | unsigned | 0          | 0        | 2              | N        |
| TimeBetweenWatchDogCheck                 | unsigned | 6000       | 70       | 4294967039     | N        |
| SchedulerExecutionTimer                  | unsigned | 50         | 0        | 11000          | N        |
| MaxSendDelay                             | unsigned | 0          | 0        | 11000          | N        |
| SchedulerSpinTimer                       | unsigned | 0          | 0        | 500            | N        |
| SchedulerResponsiveness                  | unsigned | 5          | 0        | 10             | N        |
| __sched_scan_priority                    | unsigned | 6          | 1        | 6              | N        |
| TimeBetweenWatchDogCheckInitial          | unsigned | 6000       | 70       | 4294967039     | N        |
| StopOnError                              | bool     | 1          |          |                | N        |
| MaxNoOfConcurrentOperations              | unsigned | 32768      | 32       | 4294967039     | N        |
| MaxDMLOperationsPerTransaction           | unsigned | 4294967295 | 32       | 4294967295     | N        |
| MaxNoOfLocalOperations                   | unsigned |            | 32       | 4294967039     | N        |
| MaxNoOfLocalScans                        | unsigned |            | 32       | 4294967039     | N        |
| BatchSizePerLocalScan                    | unsigned | 256        | 1        | 992            | N        |
| MaxNoOfConcurrentTransactions            | unsigned | 4096       | 32       | 4294967039     | N        |
| MaxNoOfConcurrentScans                   | unsigned | 256        | 2        | 500            | N        |
| TransactionBufferMemory                  | unsigned | 1048576    | 1024     | 4294967039     | N        |
| IndexMemory                              | unsigned | 18874368   | 1048576  | 1099511627776  | N        |
| DataMemory                               | unsigned | 83886080   | 1048576  | 1099511627776  | N        |
| UndoIndexBuffer                          | unsigned | 2097152    | 1048576  | 4294967039     | N        |
| UndoDataBuffer                           | unsigned | 16777216   | 1048576  | 4294967039     | N        |
| RedoBuffer                               | unsigned | 33554432   | 1048576  | 4294967039     | N        |
| LongMessageBuffer                        | unsigned | 67108864   | 524288   | 4294967039     | N        |
| DiskPageBufferMemory                     | unsigned | 67108864   | 4194304  | 1099511627776  | N        |
| SharedGlobalMemory                       | unsigned | 134217728  | 0        | 70368744177664 | N        |
| StartPartialTimeout                      | unsigned | 30000      | 0        | 4294967039     | N        |
| StartPartitionedTimeout                  | unsigned | 60000      | 0        | 4294967039     | N        |
| StartFailureTimeout                      | unsigned | 0          | 0        | 4294967039     | N        |
| StartNoNodegroupTimeout                  | unsigned | 15000      | 0        | 4294967039     | N        |
| HeartbeatIntervalDbDb                    | unsigned | 5000       | 10       | 4294967039     | N        |
| ConnectCheckIntervalDelay                | unsigned | 0          | 0        | 4294967039     | N        |
| HeartbeatIntervalDbApi                   | unsigned | 1500       | 100      | 4294967039     | N        |
| TimeBetweenLocalCheckpoints              | unsigned | 20         | 0        | 31             | N        |
| TimeBetweenGlobalCheckpoints             | unsigned | 2000       | 20       | 32000          | N        |
| TimeBetweenGlobalCheckpointsTimeout      | unsigned | 120000     | 10       | 4294967039     | N        |
| TimeBetweenEpochs                        | unsigned | 100        | 0        | 32000          | N        |
| TimeBetweenEpochsTimeout                 | unsigned | 0          | 0        | 256000         | N        |
| MaxBufferedEpochs                        | unsigned | 100        | 1        | 100000         | N        |
| MaxBufferedEpochBytes                    | unsigned | 26214400   | 26214400 | 4294967039     | N        |
| NoOfFragmentLogParts                     | unsigned | 4          | 4        | 32             | N        |
| NoOfFragmentLogFiles                     | unsigned | 16         | 3        | 4294967039     | N        |
| FragmentLogFileSize                      | unsigned | 16777216   | 4194304  | 1073741824     | N        |
| InitFragmentLogFiles                     | string   | sparse     |          |                | N        |
| DiskIOThreadPool                         | unsigned | 2          | 0        | 4294967039     | N        |
| MaxNoOfOpenFiles                         | unsigned |            | 20       | 4294967039     | N        |
| InitialNoOfOpenFiles                     | unsigned | 27         | 20       | 4294967039     | N        |
| TimeBetweenInactiveTransactionAbortCheck | unsigned | 1000       | 1000     | 4294967039     | N        |
| TransactionInactiveTimeout               | unsigned | 4294967039 | 0        | 4294967039     | N        |
| TransactionDeadlockDetectionTimeout      | unsigned | 1200       | 50       | 4294967039     | N        |
| Diskless                                 | bool     | 0          |          |                | N        |
| ArbitrationTimeout                       | unsigned | 7500       | 10       | 4294967039     | N        |
| Arbitration                              | enum     | Default    |          |                | N        |
| DataDir                                  | string   | .          |          |                | N        |
| FileSystemPath                           | string   |            |          |                | N        |
| LogLevelStartup                          | unsigned | 1          | 0        | 15             | N        |
| LogLevelShutdown                         | unsigned | 0          | 0        | 15             | N        |
| LogLevelStatistic                        | unsigned | 0          | 0        | 15             | N        |
| LogLevelCheckpoint                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelNodeRestart                      | unsigned | 0          | 0        | 15             | N        |
| LogLevelConnection                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelCongestion                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelError                            | unsigned | 0          | 0        | 15             | N        |
| LogLevelInfo                             | unsigned | 0          | 0        | 15             | N        |
| BackupDataDir                            | string   |            |          |                | N        |
| DiskSyncSize                             | unsigned | 4194304    | 32768    | 4294967039     | N        |
| MinDiskWriteSpeed                        | unsigned | 10485760   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeed                        | unsigned | 20971520   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeedOtherNodeRestart        | unsigned | 52428800   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeedOwnRestart              | unsigned | 209715200  | 1048576  | 1099511627776  | N        |
| BackupDiskWriteSpeedPct                  | unsigned | 50         | 0        | 90             | N        |
| BackupDataBufferSize                     | unsigned | 16777216   | 524288   | 4294967039     | N        |
| BackupLogBufferSize                      | unsigned | 16777216   | 2097152  | 4294967039     | N        |
| BackupWriteSize                          | unsigned | 262144     | 32768    | 4294967039     | N        |
| BackupMaxWriteSize                       | unsigned | 1048576    | 262144   | 4294967039     | N        |
| StringMemory                             | unsigned | 25         | 0        | 4294967039     | N        |
| MaxAllocate                              | unsigned | 33554432   | 1048576  | 1073741824     | N        |
| MemReportFrequency                       | unsigned | 0          | 0        | 4294967039     | N        |
| BackupReportFrequency                    | unsigned | 0          | 0        | 4294967039     | N        |
| StartupStatusReportFrequency             | unsigned | 0          | 0        | 4294967039     | N        |
| ODirect                                  | bool     | 0          |          |                | N        |
| CompressedBackup                         | bool     | 0          |          |                | N        |
| CompressedLCP                            | bool     | 0          |          |                | N        |
| ExtraSendBufferMemory                    | unsigned | 0          | 0        | 34359738368    | N        |
| TotalSendBufferMemory                    | unsigned | 0          | 262144   | 4294967039     | N        |
| Nodegroup                                | unsigned |            | 0        | 65536          | N        |
| MaxNoOfExecutionThreads                  | unsigned | 0          | 2        | 72             | N        |
| __ndbmt_lqh_workers                      | unsigned |            | 1        | 4              | N        |
| __ndbmt_lqh_threads                      | unsigned |            | 1        | 4              | N        |
| __ndbmt_classic                          | bool     |            |          |                | N        |
| ThreadConfig                             | string   |            |          |                | N        |
| FileSystemPathDD                         | string   |            |          |                | N        |
| FileSystemPathDataFiles                  | string   |            |          |                | N        |
| FileSystemPathUndoFiles                  | string   |            |          |                | N        |
| InitialLogfileGroup                      | string   |            |          |                | N        |
| InitialTablespace                        | string   |            |          |                | N        |
| MaxLCPStartDelay                         | unsigned | 0          | 0        | 600            | N        |
| BuildIndexThreads                        | unsigned | 0          | 0        | 128            | N        |
| HeartbeatOrder                           | unsigned | 0          | 0        | 65535          | N        |
| DictTrace                                | unsigned |            | 0        | 100            | N        |
| MaxStartFailRetries                      | unsigned | 3          | 0        | 4294967039     | N        |
| StartFailRetryDelay                      | unsigned | 0          | 0        | 4294967039     | N        |
| EventLogBufferSize                       | unsigned | 8192       | 0        | 65536          | N        |
| Numa                                     | unsigned | 1          | 0        | 1              | N        |
| RedoOverCommitLimit                      | unsigned | 20         | 0        | 4294967039     | N        |
| RedoOverCommitCounter                    | unsigned | 3          | 0        | 4294967039     | N        |
| LateAlloc                                | unsigned | 1          | 0        | 1              | N        |
| MaxParallelCopyInstances                 | unsigned | 0          | 0        | 64             | N        |
| TwoPassInitialNodeRestartCopy            | bool     | 0          |          |                | N        |
| MaxParallelScansPerFragment              | unsigned | 256        | 1        | 4294967039     | N        |
| IndexStatAutoCreate                      | unsigned | 0          | 0        | 1              | N        |
| IndexStatAutoUpdate                      | unsigned | 0          | 0        | 1              | N        |
| IndexStatSaveSize                        | unsigned | 32768      | 0        | 4294967039     | N        |
| IndexStatSaveScale                       | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatTriggerPct                      | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatTriggerScale                    | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatUpdateDelay                     | unsigned | 60         | 0        | 4294967039     | N        |
| CrashOnCorruptedTuple                    | bool     | 1          |          |                | N        |
| MinFreePct                               | unsigned | 5          | 0        | 100            | N        |
| DefaultHashmapSize                       | unsigned | 0          | 0        | 3840           | N        |
| LcpScanProgressTimeout                   | unsigned | 60         | 0        | 4294967039     | N        |
| __at_restart_skip_indexes                | bool     | 0          |          |                | N        |
| __at_restart_skip_fks                    | bool     | 0          |          |                | N        |
| __debug_mixology_level                   | unsigned | 0          | 0        | 4294967039     | N        |
| RestartSubscriberConnectTimeout          | unsigned | 120000     | 0        | 4294967039     | N        |
| DiskPageBufferEntries                    | unsigned | 10         | 1        | 4294967295     | N        |
+------------------------------------------+----------+------------+----------+----------------+----------+

MySQL5.7で実装されたJSONデータ型とJSON関数のレビューになります。
ご利用になる場合は、参考にして頂ければと思います。

ネイティブJSONデータ型 (バイナリ形式)
Insert時のJSON構文バリデーション機能
組み込みJSON関数 (保存、検索、更新、操作)
ドキュメントにインデックス設定し高速アクセス
SQLとの統合を容易にする、新しいインライン構文
utf8mb4の文字セットとutf8mb4_binの照合 「🐬」
サイズはmax_allowed_packetの値で制限 (Default:4MB)

MySQL5.7からは、リレーショナル、スキーマレスを同じ技術スタックで利用可能になっています。

13.16.1 JSON Function Reference
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

参考: Modifying JSON Values in MySQL 5.7
https://planet.mysql.com/entry/?id=5994648

JSONで表現する全てのデータ型をサポート

数値, 文字列, bool(true,false)
オブジェクト {“キー”: “値”}, 配列 [123456, “String”, …]
null
日付(date), 時刻, 日付(datetime), タイムスタンプ, その他


[CONFIRM]> show create table T_JSON_SUPPORT\G
*************************** 1. row ***************************
       Table: T_JSON_SUPPORT
Create Table: CREATE TABLE `T_JSON_SUPPORT` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`body` json DEFAULT NULL,
`type` varchar(20) GENERATED ALWAYS AS (json_type(`body`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4


[CONFIRM]> select * from T_JSON_SUPPORT;
+----+---------------------------------------------+----------+
| id | body                                        | type     |
+----+---------------------------------------------+----------+
|  1 | 123456789                                   | INTEGER  |
|  2 | NULL                                        | NULL     |
|  3 | true                                        | BOOLEAN  |
|  4 | "abcde"                                     | STRING   |
|  5 | {"id": 5, "name": "オブジェクト"}            | OBJECT   |
|  6 | [-122.42200352825247, 37.80848009696725, 0] | ARRAY    |
|  7 | "2016-02-29"                                | DATE     |
|  8 | "2016-02-29 00:00:00.000000"                | DATETIME |
+----+---------------------------------------------+----------+

データ型と照合順
4byteなので絵文字を格納可能、またutf8mb4_binなので大文字と小文字を区別します。


 [NEW57]> SET @j = JSON_OBJECT('key', 'value');
1 row in set (0.00 sec)

 [NEW57]> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+
1 row in set (0.00 sec)

 [NEW57]> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+
1 row in set (0.00 sec)

JSONドキュメントと生成列を利用して列を作成し、対象列にINDEXを付けて高速な検索を行う事が可能です
生成列はファンクションインデックスとして利用可能

こちらは、生成列を利用したテーブルの作成例になります。
string1とstring2にデータをINSERTする事で、string1_w_string、string2_w_string、compareのデータは自動生成されます。


[CONFIRM]> show create table T_Character_COLLATE_utf8mb4_bin\G
*************************** 1. row ***************************
       Table: T_Character_COLLATE_utf8mb4_bin
Create Table: CREATE TABLE `T_Character_COLLATE_utf8mb4_bin` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string2` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string1_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
  `string2_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
  `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','a');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('あ','ぁ');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','A');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> select * from T_Character_COLLATE_utf8mb4_bin;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 000041           | 000061           | 0       |
|   2 | あ      | ぁ      | 003042           | 003041           | 0       |
|   3 | A       | A       | 000041           | 000041           | 1       |
+-----+---------+---------+------------------+------------------+---------+

JSONデータと生成列を利用すると、特定のJSON識別子からデータを抜き出して列を作成し、その列にインデックスを付ける事が可能
例えば、JSONデータをINSERTしてJSONドキュメントにUSER_IDやPRODUCT_IDがあれば、その項目だけを抜き出して列にしてINDEXを付与。
検索する場合は、それらの値を利用して検索するとJSONドキュメントも高速検索が可能になります。


[NEW57]> CREATE TABLE `T_JSON` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,‘$.properties.STREET’)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


[NEW57]> alter table features add index idx_feature_type(`feature_type`);
Query OK, 0 rows affected (6.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

[NEW57]> explain select distinct(feature_type) from features;
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys    | key              | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | features | NULL       | index | idx_feature_type | idx_feature_type | 123     | NULL | 199013 |   100.00 | Using index |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+

【JSON関数の使い方】

(1)JSONドキュメントの情報取得


[NEW57]> SELECT JSON_VALID(body) from T_JSON_DOC where id = 5;
+------------------+
| JSON_VALID(body) |
+------------------+
|                1 |
+------------------+

[NEW57]> SELECT JSON_TYPE(body) from T_JSON_DOC where id = 5;
+-----------------+
| JSON_TYPE(body) |
+-----------------+
| OBJECT          |
+-----------------+

[NEW57]> SELECT JSON_KEYS(body) from T_JSON_DOC where id = 5;
+---------------------------------------+
| JSON_KEYS(body)                       |
+---------------------------------------+
| ["id", "name", "price", "Conditions"] |
+---------------------------------------+


[NEW57]> SELECT JSON_SEARCH(feature,'one','MARKET') AS extract_path FROM features WHERE id = 121254;
+-----------------------+
| extract_path          |
+-----------------------+
| "$.properties.STREET" |
+-----------------------+

(2)通常のリレーショナルテーブルから,JSONドキュメントを作成する
Object{}とArray[]関数を利用


/*** リレーショナルテーブル ***/
[NEW57]> SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+

/*** リレーショナルテーブルからJSONデータを作成 ({})***/
[NEW57]> SELECT JSON_OBJECT('CITY',NAME,'Country',CountryCode) from world.City where CountryCode ='JPN' limit 1;
+------------------------------------------------+
| JSON_OBJECT('CITY',NAME,'Country',CountryCode) |
+------------------------------------------------+
| {"CITY": "Tokyo", "Country": "JPN"}            |
+------------------------------------------------+

/*** VIEWにしておくと呼び出しがより楽になります ***/
[NEW57]> CREATE VIEW v_City_json AS
    -> SELECT JSON_OBJECT('ID', ID, 'name', Name, 'CountryCode', CountryCode, 'District', District,'Population',Population) as doc FROM City where CountryCode = 'JPN';
Query OK, 0 rows affected (0.01 sec)

[NEW57]> select * from v_City_json limit 1\G
*************************** 1. row ***************************
doc: {"ID": 1532, "name": "Tokyo", "District": "Tokyo-to", "Population": 7980230, "CountryCode": "JPN"}
1 row in set (0.01 sec)


/*** リレーショナルテーブル ***/
[NEW57]>SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+


/*** リレーショナルテーブルからJSONデータを作成 ([])***/
[NEW57]> SELECT JSON_ARRAY(NAME,CountryCode)  from world.City where CountryCode ='JPN' limit 1;
+------------------------------+
| JSON_ARRAY(NAME,CountryCode) |
+------------------------------+
| ["Tokyo", "JPN"]             |
+------------------------------+


/*** リレーショナルテーブル***/
[NEW57]> select * from T_JSON_PLACE;
+----+-------------------------------------+----------+-----------+
| id | place                               | latitude | longitude |
+----+-------------------------------------+----------+-----------+
|  1 | 東京都港区北青山2-5-8                | 35.67125 | 139.71864 |
|  2 | 大阪府大阪市北区堂2-4-27            | 34.69584 | 135.49291 |
+----+-------------------------------------+----------+-----------+


/*** リレーショナルテーブルからJSONデータを作成 ({},[])***/
[NEW57]> select JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude)) from T_JSON_PLACE;
+---------------------------------------------------------------------------------------------------+
| JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude))                      |
+---------------------------------------------------------------------------------------------------+
| {"ID": 1, "住所": "東京都港区北青山2-5-8", "緯度経度": [35.67125, 139.71864]}                     |
| {"ID": 2, "住所": "大阪府大阪市北区堂2-4-27", "緯度経度": [34.69584, 135.49291]}                 |
+---------------------------------------------------------------------------------------------------+

メモ: ST_AsGeoJSONやST_GeomFromGeoJSON等のSpatial GeoJSON Functionsで空間を表すJSONも利用する事が可能

JSONドキュメントの更新処理


/**** JSON_REMOVE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+---------------------------------------------------------------------------------------------------+------------+
| id | body                                                                                              | price_only |
+----+---------------------------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}             |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}                 |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 17131, "Conditions": ["NEW", 2015]}                          |   17131.00 |
|  4 | {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}                     |  500000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |   25000.00 |
+----+---------------------------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REMOVE(body,"$.Quantity") from T_JSON_DOC where id = 5;
+-----------------------------------------------------------------------------+
| JSON_REMOVE(body,"$.Quantity")                                              |
+-----------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REMOVE(body,"$.Quantity") where id = 5;



/**** JSON_SET (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_SET(body,'$.name',"オートバイ", '$.price',500000) from T_JSON_DOC where id = 4;
+------------------------------------------------------------------------------------+
| JSON_SET(body,'$.name',"オートバイ", '$.price',500000)                             |
+------------------------------------------------------------------------------------+
| {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}      |
+------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_SET(body,'$.name',"オートバイ", '$.price',500000) where id = 4;


/**** JSON_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_INSERT(body,'$.Quantity','[1,10]') from NEW57.T_JSON_DOC where id = 5;
+---------------------------------------------------------------------------------------------------+
| JSON_INSERT(body,'$.Quantity','[1,10]')                                                           |
+---------------------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |
+---------------------------------------------------------------------------------------------------+

[NEW57]> update NEW57.T_JSON_DOC set body = JSON_INSERT(body,'$.Quantity','[1,10]') where id = 5;


/**** JSON_REPLACE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) from T_JSON_DOC where id = 3;
+-----------------------------------------------------------------------------+
| JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000)))                 |
+-----------------------------------------------------------------------------+
| {"id": 3, "name": "冷蔵庫", "price": 18359, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) where id = 3;


/**** JSON_ARRAY_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]>SELECT JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') from T_JSON_DOC where id = 5;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(body,'$.Conditions[2]','January')                                    |
+----------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015, "January"]}    |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') where id = 5;



/**** JSON_ARRAY_APPEND (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_ARRAY_APPEND(body,'$.Conditions','故障') from NEW57.T_JSON_DOC where id = 2;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_APPEND(body,'$.Conditions','故障')                                          |
+----------------------------------------------------------------------------------------+
| {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}      |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_APPEND(body,'$.Conditions','故障') where id = 2;


/**** JSON_MERGE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_MERGE(body,'{"Conditions":"Excellent"}') from T_JSON_DOC where id = 1;
+------------------------------------------------------------------------------------------+
| JSON_MERGE(body,'{"Conditions":"Excellent"}')                                            |
+------------------------------------------------------------------------------------------+
| {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}    |
+------------------------------------------------------------------------------------------+


[NEW57]> update T_JSON_DOC set body = JSON_MERGE(body,'{"Conditions":"Excellent"}') where id = 1;

補足情報
json_extractと->は同等

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.type'))) VIRTUAL

メモ:
-> /* JSON_EXTRACT() */
->> /* JSON_UNQUOTE(JSON_EXTRACT()) */

必要に応じてjson_unquoteでQuoteを外して列を作成

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL

[NEW57]> select feature_type,feature_street from T_JSON limit 10,5;
+--------------+----------------+
| feature_type | feature_street |
+--------------+----------------+
| Feature      | "JEFFERSON"    |
| Feature      | "TAYLOR"       |
| Feature      | "BEACH"        |
| Feature      | "BEACH"        |
| Feature      | "JEFFERSON"    |
+--------------+----------------+

生成列のVirtualとStoredの違いはありますが、Virtualであれば作成時も参照、更新もオンライン処理が可能。
但し、参照時にCPUを利用するのでStoredを選択するかは状況により使い分け。
Storedは実データを含む為、高速だが追加でディスク容量が必用。また、作成時は参照のみが可能になります。

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") STORED;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

ALTER TABLE T_ONLINE ADD feature_type varchar(30) AS (feature->"$.type") STORED;
Query OK, 206560 rows affected (6.17 sec)
Records: 206560  Duplicates: 0  Warnings: 0