metabase迁移到k8s
(1)部署一个mysql,给metabase用
1.configmap,用来配置my.cnf
#config-map.yaml apiVersion: v1 kind: ConfigMap metadata: name: mysql-config namespace: ops labels: app: mysql data: ## default_auth is the name of config. This will be referred from volume mount definition mysql.cnf: | [mysql] prompt=\\u@\\h:\\d\\r:\\m:\\s> auto-rehash [mysqld] ####: for global skip_ssl max_prepared_stmt_count =1048576 # 16382 open_files_limit =65536 # 65536 skip_name_resolve =1 # 0 super_read_only =OFF # OFF sql_require_primary_key =ON # OFF cte_max_recursion_depth =1000 # 1000 log_timestamps =system # UTC # lower_case_table_names =1 # 0 auto_increment_increment =1 # 1 auto_increment_offset =1 # 1 lock_wait_timeout =31536000 # ! for metadata lock wait event_scheduler =1 # ON auto_generate_certs =ON # ON big_tables =OFF # OFF ! for TempTable storage engine join_buffer_size =256k # 0.25M activate_all_roles_on_login =ON # OFF end_markers_in_json =OFF # OFF max_connections =512 # 151 autocommit =ON # ON sort_buffer_size =262144 # 262144(256k) ! fix too many Sort_merge_passes per second ####: for table cache table_open_cache =4000 # 2000 table_definition_cache =2000 # 1400 table_open_cache_instances =32 # 16 ####: for net max_allowed_packet =64M # 64M bind_address =* # * connect_timeout =10 # 10 interactive_timeout =28800 # 28800 net_read_timeout =30 # 30 net_retry_count =10 # 10 net_write_timeout =60 # 60 net_buffer_length =32k # 16384(16k) ####: for logs log_output =FILE # FILE ## -- general log general_log =OFF # OFF general_log_file =general.log # $(hotname).log log_error =err.log # stderr log_error_verbosity =3 log_statements_unsafe_for_binlog =ON # ! for error 1592 ## -- slow log long_query_time =2.0 # 10.000000 log_queries_not_using_indexes =OFF # OFF log_slow_admin_statements =OFF # OFF log_slow_slave_statements =OFF # OFF slow_query_log =ON # OFF slow_query_log_file =slow.log # slow.log ####: for binlog log_bin =mysql-bin binlog_checksum =none # CRC32 log_bin_trust_function_creators =ON # OFF binlog_direct_non_transactional_updates =OFF # OFF binlog_expire_logs_seconds =604800 # 2592000(30days) | 604800(7days) binlog_error_action =ABORT_SERVER # ABORT_SERVER | IGNORE_ERROR binlog_format =ROW # ROW | STATEMENT | MIXED max_binlog_stmt_cache_size =1G # 18446744073709547520 max_binlog_cache_size =1G # 18446744073709547520(1G) max_binlog_size =1G binlog_order_commits =ON # ON binlog_row_image =FULL # FULL | MINIMAL | NOBLOB binlog_row_metadata =MINIMAL # MINIMAL | FULL binlog_rows_query_log_events =ON # OFF sync_binlog =1 # 1 | 0 | N binlog_stmt_cache_size =32k # 32768(32k) log_slave_updates =ON # ON binlog_group_commit_sync_delay =4000 # 0 binlog_group_commit_sync_no_delay_count =10 # 0 binlog_cache_size =96k # 32768(32k) binlog_transaction_dependency_history_size =25000 # 25000 binlog_transaction_dependency_tracking =WRITESET # COMMIT_ORDER | WRITESET | WRITESET_SESSION ####: for replication master_info_repository =table # TABLE | FILE sync_master_info =10000 # 10000 # relay_log_info_repository =table # file skip_slave_start =0 # 0 relay_log_info_repository =table # file slave_parallel_type =logical_clock # database | LOGICAL_CLOCK slave_parallel_workers =4 # 0 slave_max_allowed_packet =1073741824 # 1073741824 sync_relay_log =10000 # 10000 sync_relay_log_info =10000 # 10000 ####: for gtid binlog_gtid_simple_recovery =ON # ON enforce_gtid_consistency =ON # ON gtid_executed_compression_period =1000 # 1000 gtid_mode =ON # OFF relay_log_recovery =ON ####: for storage engine default_storage_engine =innodb # InnoDB default_tmp_storage_engine =innodb # InnoDB internal_tmp_mem_storage_engine =memory # TempTable ####: for innodb ## disk I/O and file space management innodb_data_home_dir =./ # ./ transaction_isolation =READ-COMMITTED innodb_page_size =16k # 16384(16k) innodb_default_row_format =dynamic # dynamic | compact | redundant innodb_log_group_home_dir =./ # ./ innodb_log_files_in_group =4 # 2 innodb_log_file_size =128M # 50331648(48M) innodb_log_buffer_size =256M # 16777216(16M) innodb_redo_log_encrypt =OFF # OFF innodb_online_alter_log_max_size =128M # 134217728(128M) innodb_undo_directory =./ # ./ innodb_undo_log_encrypt =OFF # OFF innodb_undo_log_truncate =ON # ON innodb_max_undo_log_size =1G # 1073741824(1G) innodb_rollback_on_timeout =OFF # OFF innodb_rollback_segments =128 # 128 [1~128] innodb_log_checksums =ON # ON innodb_checksum_algorithm =crc32 # crc32 innodb_log_compressed_pages =ON # ON innodb_doublewrite =ON # ON ! do not disable it please. innodb_commit_concurrency =0 # 0 ## configuring innodb readonly innodb_read_only =OFF # OFF ## configuring innodb dedicated server innodb_dedicated_server =OFF # OFF ! related to mysql auto config please donot chanage ## configuring innodb buffer pool size and instances innodb_buffer_pool_chunk_size =128M # 134217728(128M) innodb_buffer_pool_size =2G # 134217728(128M) innodb_buffer_pool_instances =2 # 1 ## making the buffer pool scan resistant innodb_old_blocks_pct =37 # 37 innodb_old_blocks_time =1000 # 1000 ## configuring innodb buffer pool prefetching(read ahead) innodb_random_read_ahead =off # OFF innodb_read_ahead_threshold =56 # 56 ## configuring innodb buffer pool flushing innodb_max_dirty_pages_pct_lwm =20 # 10 innodb_max_dirty_pages_pct =90 # 90 ## fine-tuning innodb buffer pool flushing innodb_flush_neighbors =0 # off | on (off for ssd ,on for hdd) innodb_lru_scan_depth =1024 # 1024 ## tuning for sharp checkpoint innodb_adaptive_flushing =ON # ON innodb_adaptive_flushing_lwm =10 # 10 innodb_flushing_avg_loops =30 # 30(a heih value means adaptive flushing is slow) ## saving and restoring the buffer pool state innodb_buffer_pool_dump_pct =50 # 50 innodb_buffer_pool_dump_at_shutdown =ON # ON innodb_buffer_pool_load_at_startup =ON # ON innodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_pool innodb_stats_persistent =ON # ON innodb_stats_on_metadata =ON # OFF innodb_stats_method =nulls_equal # nulls_equal innodb_stats_auto_recalc =ON # ON innodb_stats_include_delete_marked =ON # ON innodb_stats_persistent_sample_pages=20 # 20 innodb_stats_transient_sample_pages =8 # 8 innodb_status_output =OFF # OFF innodb_status_output_locks =OFF # OFF innodb_buffer_pool_dump_now =OFF # OFF innodb_buffer_pool_load_abort =OFF # OFF innodb_buffer_pool_load_now =OFF # OFF ## configuring thread concurrency for innodb innodb_thread_concurrency =0 # 0 innodb_concurrency_tickets =5000 # 5000 innodb_thread_sleep_delay =15000 # 4000 ( 4ms) innodb_adaptive_max_sleep_delay =150000 # 150000 (15/100 s) innodb_read_io_threads =4 # 4 innodb_write_io_threads =4 # 4 ## using asynchronous i/o on linux innodb_use_native_aio =ON # ON ## configuring the innodb master thread i/o rate innodb_flush_sync =OFF # ON # disable innodb_flush_sync. innodb_io_capacity =4000 # 200 innodb_io_capacity_max =20000 # 2000 ## configuring spin lock polling innodb_spin_wait_delay =6 # 6 ## configuring innoDB purge scheduling innodb_purge_threads =4 # 4 innodb_purge_batch_size =300 # 300(300 undo log page) innodb_purge_rseg_truncate_frequency=128 # 128 ## Lock & Wait innodb_deadlock_detect =ON # ON innodb_autoinc_lock_mode =2 # 0 | 1 | 2 innodb_print_all_deadlocks =ON # OFF innodb_lock_wait_timeout =50 # 50 innodb_table_locks =ON # ON innodb_sync_array_size =1 # 1 innodb_sync_spin_loops =30 # 30 ## innodb others innodb_print_ddl_logs =OFF # OFF innodb_replication_delay =0 # 0 innodb_cmp_per_index_enabled =OFF # ! do not enable it please. innodb_disable_sort_file_cache =OFF # OFF innodb_numa_interleave =OFF # OFF innodb_strict_mode =ON # ON innodb_sort_buffer_size =1M # 1M(global and only for full-text search) innodb_fast_shutdown =1 # 0 | 1 | 2 innodb_force_load_corrupted =OFF # OFF innodb_force_recovery =0 # 0 | 1 | 2 | 3 | 4 | 5 | 6 innodb_temp_tablespaces_dir =./#innodb_temp/ # ./#innodb_temp/ innodb_tmpdir =./ # ! the sort file temp dir of alter table opration innodb_temp_data_file_path =ibtmp1:64M:autoextend # ibtmp1:12M:autoextend ! stores rollback segments for changes made to user-created temporary tables. innodb_page_cleaners =4 # 1 ## adaptive hash index innodb_adaptive_hash_index =ON # ON innodb_adaptive_hash_index_parts =8 # 8 ## -- Flush & Io innodb_flush_log_at_timeout =1 # 1 innodb_flush_log_at_trx_commit =1 # 1 | 0 | 2 innodb_flush_method =O_DIRECT # fsync | o_direct innodb_fsync_threshold =0 # 0 ~ 2**64-1 innodb_change_buffer_max_size =25 # 25 innodb_change_buffering =all # all | none | inserts | deletes | changes | purges ####: others div_precision_increment =4 # 4 eq_range_index_dive_limit =200 # 200 explicit_defaults_for_timestamp =ON # ON group_concat_max_len =1024 # 1024 flush =OFF # OFF flush_time =0 # 0 automatic_sp_privileges =ON # ON innodb_fill_factor =90 # 100 innodb_file_per_table =ON # ON innodb_autoextend_increment =64 # 64 innodb_open_files =64000 # 4000 ####: for authentication default_authentication_plugin =mysql_native_password ####: for character character_set_server =utf8mb4 # utf8mb4 collation_server =utf8mb4_general_ci # utf8mb4_0900_ai_ci ####: for optimizer optimizer_prune_level =1 optimizer_search_depth =62 optimizer_switch =index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on optimizer_trace =enabled=off,one_line=off optimizer_trace_features =greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on optimizer_trace_limit =1 optimizer_trace_max_mem_size =1048576 optimizer_trace_offset =-1 #### for performance_schema performance_schema =on # on performance_schema_consumer_global_instrumentation =on # on performance_schema_consumer_thread_instrumentation =on # on performance_schema_consumer_events_stages_current =on # off performance_schema_consumer_events_stages_history =on # off performance_schema_consumer_events_stages_history_long =off # off performance_schema_consumer_statements_digest =on # on performance_schema_consumer_events_statements_current =on # on performance_schema_consumer_events_statements_history =on # on performance_schema_consumer_events_statements_history_long =off # off performance_schema_consumer_events_waits_current =on # off performance_schema_consumer_events_waits_history =on # off performance_schema_consumer_events_waits_history_long =off # off performance-schema-instrument ='memory/%=COUNTED'
2.创建services
apiVersion: v1
kind: Service
metadata:
name: mysql-metabase
labels:
app: mysql
spec:
ports:
- name: mysql
port: 3306
clusterIP: None
selector:
app: mysql
3.创建statefulset
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
namespace: ops
spec:
selector:
matchLabels:
app: mysql
replicas: 1
serviceName: mysql-metabase
template:
metadata:
labels:
app: mysql
spec:
terminationGracePeriodSeconds: 10
containers:
- name: mysql-ops
image: mysql:8.0
ports:
- containerPort: 3306
name: mysql-tcp
protocol: TCP
env:
- name: MYSQL_ROOT_PASSWORD
value: "xxxxxxx"
- name: MYSQL_DATABASE
value: "ops"
- name: MYSQL_USER
value: "devops"
- name: MYSQL_PASSWORD
value: "xxxxxxxx"
volumeMounts:
- name: sql-data
mountPath: /var/lib/mysql
- name: mysql-cfg
mountPath: /etc/mysql/conf.d/mysql.cnf
subPath: mysql.cnf
volumes:
- name: mysql-cfg
configMap:
name: mysql-config
securityContext:
runAsUser: 1000
runAsGroup: 3000
fsGroup: 2000
runAsNonRoot: true
volumeClaimTemplates:
- metadata:
name: sql-data
namespace: ops
spec:
storageClassName: balanced-regionalpd
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 20Gi
这里有点需要注意，就是存放mysql数据的磁盘，我用的gcp都k8s,之前驱动csi插件都是安装好了的,所以直接用，不同云的k8s所载的驱动用法可能不太一样，具体看看文档就行了，所以我这里直接引用了，有的需要先安装csi驱动
比如我这个用的是firestore,共享盘的一种，然后挂载到k8s，创建pv,pvc直接用。
（2）安装metabase:
由于是helm安装的，也就没啥可说的，只需要修改values.yaml就行了：
root@dba-ops:~/charts/stable/metabase# cat values.yaml
image:
repository: metabase/metabase
tag: v0.42.3
pullPolicy: IfNotPresent
# 修改数据库配置
database:
type: mysql
host: mysql-metabase
port: 3306
dbname: metabase
username: root
password: "xxxxxxx"
# 修改时区
timeZone: Asia/Shanghai
service:
name: metabase-bi
type: ClusterIP
externalPort: 80
internalPort: 3000
ingress:
enabled: false
hosts:
path: /
listen:
host: "0.0.0.0"
port: 3000
ssl:
enabled: false
password:
complexity: normal
length: 10
# 设置资源限制
resources:
limits:
cpu: 1000m
memory: 4096Mi
requests:
cpu: 100m
memory: 256Mi
session:
maxSessionAge: 3600
sessionCookies: 0
livenessProbe:
initialDelaySeconds: 120
timeoutSeconds: 30
failureThreshold: 6
readinessProbe:
initialDelaySeconds: 30
timeoutSeconds: 3
periodSeconds: 5
正常情况还需要设置ingress转发，但是我不想每个服务都建一个ingress服务，就在现有的ingress里面做了转发，配置没写在这里。
业余经济爱好者
