Alauda Database Service for MySQL-PXC

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

CLI
Web Console
  1. 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.
  1. 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.
  1. 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.

  1. Access the Instance

After the instance is successfully created, you can access it and check the instance connection status.

ON THIS PAGE