Create Instance
Alauda Database Service for MySQL-PXC provides a set of functions to create and manage MySQL database instances in a Kubernetes cluster. Users can quickly deploy MySQL instances that meet their requirements by configuring resource specifications, parameter templates, account information, scheduling policies, and Pod toleration rules. Once the instance is created, some parameters can be adjusted dynamically; however, the scheduling configuration cannot be modified.
TOC
Prerequisites
Before creating a MySQL-PXC instance, ensure that you have reviewed the and confirmed that the current cluster supports that type of storage class and has sufficient available space. It is recommended to use TopoLVM storage class.
Procedure
-
Create Password
kubectl -n <namespace> create secret generic <name> \
--from-literal=clustercheck=<password> \
--from-literal=monitor=<password> \
--from-literal=operator=<password> \
--from-literal=proxyadmin=<password> \
--from-literal=root=<password> \
--from-literal=xtrabackup=<password>
INFO
<name> is the name of the password used to identify the instance and must be unique; it is recommended to use the same name as the instance. This is set in the spec.pxc.secretsName field of the instance.<namespace> is the namespace to which the instance belongs.<password> is the password; different passwords can be set for the multiple users mentioned above.
-
Create Instance
kubectl -n <namespace> apply -f - <<EOF
apiVersion: middleware.alauda.io/v1
kind: Mysql
metadata:
annotations:
middleware.upgrade.component.version: ""
middleware.upgrade.crVersion: ""
labels:
mysql/arch: pxc
name: <name>
namespace: <namespace>
spec:
params:
mysql:
client:
default_character_set: "utf8mb4"
mysql:
default_character_set: "utf8mb4"
prompt: mysql(\u@\h:\d) >
mysqld:
autocommit: "ON"
big_tables: "OFF"
binlog_cache_size: "32768"
binlog_checksum: "crc32"
binlog_format: "ROW"
binlog_row_image: "FULL"
binlog_rows_query_log_events: "1"
bulk_insert_buffer_size: "8388608"
character_set_server: "utf8mb4"
collation_server: "utf8mb4_general_ci"
connect_timeout: "28800"
default_authentication_plugin: "mysql_native_password"
default_password_lifetime: "0"
default_storage_engine: "INNODB"
default_time_zone: "+8:00"
default_tmp_storage_engine: "INNODB"
event_scheduler: "ON"
expire_logs_days: "2"
explicit_defaults_for_timestamp: "1"
general_log: "OFF"
init_connect: "SET NAMES utf8mb4"
innodb_adaptive_flushing: "ON"
innodb_adaptive_flushing_lwm: "10"
innodb_adaptive_hash_index: "OFF"
innodb_autoinc_lock_mode: "2"
innodb_buffer_pool_dump_at_shutdown: "1"
innodb_buffer_pool_dump_pct: "40"
innodb_buffer_pool_instances: "2"
innodb_buffer_pool_load_at_startup: "1"
innodb_buffer_pool_size: "1536M"
innodb_checksum_algorithm: "crc32"
innodb_checksums: "ON"
innodb_cmp_per_index_enabled: "OFF"
innodb_compression_level: "6"
innodb_deadlock_detect: "1"
innodb_default_row_format: "DYNAMIC"
innodb_doublewrite: "1"
innodb_fast_shutdown: "1"
innodb_file_format: "Barracuda"
innodb_file_format_check: "ON"
innodb_file_format_max: "Barracuda"
innodb_file_per_table: "ON"
innodb_flush_log_at_timeout: "1"
innodb_flush_log_at_trx_commit: "1"
innodb_flush_method: "O_DIRECT_NO_FSYNC"
innodb_flush_neighbors: "0"
innodb_flushing_avg_loops: "30"
innodb_force_recovery: "0"
innodb_log_file_size: "1G"
innodb_log_files_in_group: "2"
innodb_lru_scan_depth: "2048"
innodb_max_dirty_pages_pct: "90"
innodb_max_dirty_pages_pct_lwm: "10"
innodb_max_purge_lag: "0"
innodb_max_purge_lag_delay: "0"
innodb_max_undo_log_size: "2G"
innodb_numa_interleave: "1"
innodb_online_alter_log_max_size: "32M"
innodb_page_cleaners: "2"
innodb_page_size: "16384"
innodb_print_all_deadlocks: "1"
innodb_print_lock_wait_timeout_info: "ON"
innodb_purge_batch_size: "300"
innodb_purge_rseg_truncate_frequency: "128"
innodb_purge_threads: "2"
innodb_random_read_ahead: "OFF"
innodb_read_ahead_threshold: "56"
innodb_read_io_threads: "4"
innodb_rollback_on_timeout: "0"
innodb_rollback_segments: "128"
innodb_sort_buffer_size: "1M"
innodb_status_output: "0"
innodb_status_output_locks: "1"
innodb_strict_mode: "1"
innodb_support_xa: "1"
innodb_undo_log_truncate: "1"
innodb_undo_logs: "128"
innodb_use_native_aio: "ON"
innodb_write_io_threads: "8"
interactive_timeout: "28800"
internal_tmp_disk_storage_engine: "INNODB"
join_buffer_size: "262144"
lock_wait_timeout: "50"
log_bin: "bin"
log_error_verbosity: "3"
log_queries_not_using_indexes: "1"
log_slow_admin_statements: "1"
log_slow_slave_statements: "1"
log_throttle_queries_not_using_indexes: "10"
long_query_time: "20"
max_allowed_packet: "64M"
max_binlog_size: "1024M"
max_connect_errors: "10000"
max_connections: "300"
max_heap_table_size: "16M"
max_join_size: "18446744073709551615"
max_user_connections: "60"
min_examined_row_limit: "100"
open_files_limit: "65535"
pxc_strict_mode: "ENFORCING"
query_cache_type: "0"
read_buffer_size: "131072"
read_rnd_buffer_size: "262144"
show_compatibility_56: "OFF"
skip_external_locking: "1"
skip_name_resolve: "1"
skip_symbolic_links: "ON"
slow_query_log: "0"
sort_buffer_size: "262144"
sql_mode: "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sync_binlog: "1"
table_open_cache: "4096"
table_open_cache_instances: "2"
thread_handling: "pool-of-threads"
thread_pool_idle_timeout: "60"
thread_pool_max_threads: "1000"
thread_pool_oversubscribe: "8"
thread_pool_size: "4"
tmp_table_size: "16M"
transaction_isolation: "READ-COMMITTED"
wait_timeout: "28800"
wsrep_OSU_method: "TOI"
wsrep_RSU_commit_timeout: "5000"
wsrep_auto_increment_control: "ON"
wsrep_causal_reads: "OFF"
wsrep_certification_rules: "strict"
wsrep_certify_nonPK: "ON"
wsrep_convert_LOCK_to_trx: "OFF"
wsrep_debug: "OFF"
wsrep_desync: "OFF"
wsrep_dirty_reads: "OFF"
wsrep_drupal_282555_workaround: "OFF"
wsrep_forced_binlog_format: "ROW"
wsrep_load_data_splitting: "ON"
wsrep_log_conflicts: "OFF"
wsrep_max_ws_rows: "0"
wsrep_max_ws_size: "2147483647"
wsrep_on: "ON"
wsrep_preordered: "OFF"
wsrep_recover: "OFF"
wsrep_reject_queries: "NONE"
wsrep_replicate_myisam: "OFF"
wsrep_restart_slave: "OFF"
wsrep_retry_autocommit: "1"
wsrep_slave_FK_checks: "ON"
wsrep_slave_UK_checks: "OFF"
wsrep_slave_threads: "4"
wsrep_sync_wait: "0"
pxc:
proxysql:
resources:
limits:
cpu: 500m
memory: 512Mi
requests:
cpu: 500m
memory: 512Mi
size: 3
volumeSpec:
persistentVolumeClaim:
resources:
requests:
storage: <size>Gi
storageClassName: <storageClassName>
pxc:
resources:
limits:
cpu: "2"
memory: 4Gi
requests:
cpu: "2"
memory: 4Gi
size: 3
volumeSpec:
persistentVolumeClaim:
resources:
requests:
storage: <size>Gi
storageClassName: <storageClassName>
secretsName: <name>
runAsRoot: false
upgradeOption:
autoUpgrade: true
version: "5.7"
EOF
INFO
<name> is the instance name used to identify the instance and must be unique.<namespace> is the namespace to which the instance belongs.<spec.pxc.secretsName> is the name of the password created in step 1.<spec.params.mysql> is the MySQL parameter configuration, with options corresponding to MySQL 5.7.<spec.pxc.pxc.resources> is the resource configuration for the PXC Pod, corresponding to Pod resource configurations.<spec.pxc.pxc.volumeSpec.persistentVolumeClaim> is the data volume configuration for the PXC Pod.<spec.pxc.pxc.size> is the number of PXC Pods.<spec.pxc.proxysql.resources> is the resource configuration for the ProxySQL Pod, corresponding to Pod resource configurations.<spec.pxc.proxysql.volumeSpec.persistentVolumeClaim> is the data volume configuration for the ProxySQL Pod.<spec.pxc.proxysql.size> is the number of ProxySQL Pods.
-
Wait for the instance creation to complete
kubectl get mysql -n <namespace> <name> -w
Execute the command and wait for the STATE field in the result to change to ready.
- Access the Instance
After the instance is successfully created, you can access it and check the instance connection status.
