Azure Database for MySQLのARMテンプレートからサーバパラメータと値を抽出

経緯

今VMで動いてるMySQLをAzure Database for MySQLへ移行できるか？という話が持ち上がったため、
ひとまずサポートされているサーバパラメータを一覧で出したかった。

やり方

1. Azure Database for MySQLリソースからARMテンプレートを取ってくる
   取得方法はAzureポータルでもAzure CLIでもお好きに

2. jq他で抽出

# parameter
jq '.resources[] | select (.type == "Microsoft.DBforMySQL/servers/configurations") | .name' <ARM Template File> | cut -d" " -f2 | cut -c3- | rev | cut -c5- | rev

# value
jq '.resources[] | select (.type == "Microsoft.DBforMySQL/servers/configurations") | .properties.value' <ARM Template File> | sed 's/"//g'

※本当はワンライナーでparameterと対応するvalueをCSV出力したかったけど、モチベが続かなかった笑

参考URL

https://docs.microsoft.com/ja-jp/azure/templates/microsoft.dbformysql/allversions
https://stedolan.github.io/jq/manual/
https://orebibou.com/2016/02/linuxunix%E3%81%A7%E6%96%87%E5%AD%97%E5%88%97%E3%81%8B%E3%82%89%E7%89%B9%E5%AE%9A%E9%83%A8%E5%88%86%E5%8F%B3%E3%81%8B%E3%82%89%E3%83%BB%E5%B7%A6%E3%81%8B%E3%82%89%E4%BD%95%E5%80%8B%E3%80%81%E3%80%87/

試してみる

環境

• コマンド実行環境

  • macOS High Sierra ver10.13.6
  • jq ver1.6(homebrewでインストール)

• Azure Database for MySQL

  • 西日本リージョン
  • MySQLバージョン5.7
  • 2019/06/25時点 注) サービスアップデートにより追加・変更される可能性あり

結果

結果を表示

ARMテンプレートのファイル名は「template.json」

# parameter
jq '.resources[] | select (.type == "Microsoft.DBforMySQL/servers/configurations") | .name' template.json | cut -d" " -f2 | cut -c3- | rev | cut -c5- | rev
audit_log_enabled
audit_log_events
audit_log_exclude_users
binlog_group_commit_sync_delay
binlog_group_commit_sync_no_delay_count
character_set_server
default_week_format
div_precision_increment
eq_range_index_dive_limit
event_scheduler
flush_time
ft_query_expansion_limit
group_concat_max_len
init_connect
innodb_adaptive_flushing
innodb_adaptive_flushing_lwm
innodb_adaptive_hash_index
innodb_adaptive_hash_index_parts
innodb_adaptive_max_sleep_delay
innodb_autoextend_increment
innodb_autoinc_lock_mode
innodb_buffer_pool_dump_pct
innodb_buffer_pool_size
innodb_change_buffer_max_size
innodb_change_buffering
innodb_cmp_per_index_enabled
innodb_commit_concurrency
innodb_compression_failure_threshold_pct
innodb_compression_level
innodb_compression_pad_pct_max
innodb_concurrency_tickets
innodb_deadlock_detect
innodb_default_row_format
innodb_file_format
innodb_fill_factor
innodb_ft_enable_stopword
innodb_ft_max_token_size
innodb_ft_min_token_size
innodb_ft_num_word_optimize
innodb_ft_result_cache_limit
innodb_ft_sort_pll_degree
innodb_io_capacity
innodb_io_capacity_max
innodb_large_prefix
innodb_lock_wait_timeout
innodb_log_compressed_pages
innodb_lru_scan_depth
innodb_max_dirty_pages_pct
innodb_max_dirty_pages_pct_lwm
innodb_max_purge_lag
innodb_max_purge_lag_delay
innodb_old_blocks_pct
innodb_old_blocks_time
innodb_optimize_fulltext_only
innodb_page_cleaners
innodb_purge_batch_size
innodb_purge_rseg_truncate_frequency
innodb_purge_threads
innodb_random_read_ahead
innodb_read_ahead_threshold
innodb_read_io_threads
innodb_rollback_segments
innodb_stats_auto_recalc
innodb_stats_include_delete_marked
innodb_stats_method
innodb_stats_on_metadata
innodb_stats_persistent
innodb_stats_persistent_sample_pages
innodb_stats_transient_sample_pages
innodb_sync_array_size
innodb_table_locks
innodb_thread_concurrency
innodb_thread_sleep_delay
innodb_write_io_threads
interactive_timeout
lock_wait_timeout
log_bin_trust_function_creators
log_queries_not_using_indexes
log_slow_admin_statements
log_slow_slave_statements
log_throttle_queries_not_using_indexes
long_query_time
lower_case_table_names
max_allowed_packet
max_connect_errors
max_connections
max_digest_length
max_error_count
max_execution_time
max_join_size
max_length_for_sort_data
max_points_in_geometry
max_prepared_stmt_count
max_seeks_for_key
max_sort_length
max_sp_recursion_depth
max_user_connections
max_write_lock_count
min_examined_row_limit
net_buffer_length
net_read_timeout
net_retry_count
net_write_timeout
optimizer_prune_level
optimizer_search_depth
parser_max_mem_size
performance_schema
preload_buffer_size
query_store_capture_interval
query_store_capture_mode
query_store_capture_utility_queries
query_store_retention_period_in_days
query_store_wait_sampling_capture_mode
query_store_wait_sampling_frequency
server_id
session_track_schema
session_track_state_change
show_compatibility_56
skip_show_database
slave_net_timeout
slow_query_log
sql_mode
stored_program_cache
table_open_cache
time_zone
transaction_alloc_block_size
transaction_prealloc_size
tx_isolation
updatable_views_with_limit
wait_timeout

# value
jq '.resources[] | select (.type == "Microsoft.DBforMySQL/servers/configurations") | .properties.value' template.json | sed 's/"//g'
OFF
CONNECTION
azure_superuser
1000
0
latin1
0
4
200
OFF
0
20
1024
null
ON
10
ON
8
150000
64
1
25
872415232
25
all
OFF
0
5
6
50
5000
ON
DYNAMIC
Barracuda
100
ON
84
3
2000
2000000000
2
200
2000
ON
50
ON
1024
75
0
0
0
37
1000
OFF
4
300
128
4
OFF
56
4
128
ON
OFF
nulls_equal
OFF
ON
20
8
1
ON
0
10000
4
28800
31536000
OFF
OFF
OFF
OFF
0
10
1
536870912
100
50
1024
64
0
18446744073709551615
1024
65536
16382
4294967295
1024
0
0
4294967295
0
16384
120
10
240
1
62
18446744073709551615
ON
32768
15
NONE
NO
7
NONE
30
1618785235
ON
OFF
OFF
OFF
60
OFF
null
256
2000
SYSTEM
8192
4096
REPEATABLE-READ
YES
120