|
一、集群环境： 172.16.0.201 172.16.0.202 172.16.0.203 二、部署方式：  1、使用 mysql shell 创建 mgr 集群时，在添加节点时候报错：  2、配置文件如下： [client] port = 4306 socket = /data/database/db_mgr_test_4306_greatsql/tmp/db_mgr_test_4306.sock default_character_set = utf8mb4 [mysql] loose-skip-binary-as-hex prompt="(\D)[\u@mysql][\d]>" no-auto-rehash default_character_set = utf8mb4 [mysqld] user = mysql port = 4306 server_id = 2024306 report-host = 172.16.0.201 report-port = 4306 skip_name_resolve = ON skip_slave_start = ON default_authentication_plugin = mysql_native_password #authentication_policy = mysql_native_password # dir path basedir = /usr/local/mysql datadir = /data/database/db_mgr_test_4306_greatsql/data socket = /data/database/db_mgr_test_4306_greatsql/tmp/db_mgr_test_4306.sock pid-file = /data/database/db_mgr_test_4306_greatsql/tmp/db_mgr_test_4306.pid innodb_data_home_dir = /data/database/db_mgr_test_4306_greatsql/data innodb_log_group_home_dir = /data/database/db_mgr_test_4306_greatsql/data secure_file_priv = # sql mode sql_mode = NO_ENGINE_SUBSTITUTION # character set character_set_server = utf8mb4 character_set_client_handshake = 0 init_connect = 'set names utf8mb4' collation_server = utf8mb4_unicode_ci skip_name_resolve = 1 # read only （ 主库为0，从库为 1 ） read_only = 1 super_read_only = 1 #若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数 default_time_zone = "+8:00" #performance setttings max_allowed_packet = 64M lock_wait_timeout = 30 open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 99999999 table_open_cache = 512 table_definition_cache = 512 thread_stack = 512K sort_buffer_size = 4M join_buffer_size = 4M read_buffer_size = 8M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 64M thread_cache_size = 128 interactive_timeout = 500 wait_timeout = 500 tmp_table_size = 96M max_heap_table_size = 96M #log settings log_timestamps = SYSTEM log_error = /data/database/db_mgr_test_4306_greatsql/logs/db_mgr_test_4306.err log_error_verbosity = 3 slow_query_log = 1 log_slow_extra = 1 slow_query_log_file = /data/database/db_mgr_test_4306_greatsql/logs/db_mgr_test_4306.slow general_log_file = /data/database/db_mgr_test_4306_greatsql/logs/db_mgr_test_4306.log long_query_time = 1 log_queries_not_using_indexes = 0 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100000 log_slow_admin_statements = 1 #log_slow_replica_statements = 1 log_slow_slave_statements = 1 log_slow_verbosity = FULL # binlog & relaylog log_bin = /data/database/db_mgr_test_4306_greatsql/data/db_mgr_test_4306-bin binlog_format = ROW sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 1G max_binlog_size = 512M binlog_rows_query_log_events = 1 binlog_expire_logs_seconds = 604800 binlog_checksum = CRC32 relay_log = /data/database/db_mgr_test_4306_greatsql/data/db_mgr_test_4306-relay-bin relay_log_index = /data/database/db_mgr_test_4306_greatsql/data/db_mgr_test_4306-relay-bin.index # group commit # binlog_group_commit_sync_no_delay_count = # binlog_group_commit_sync_delay = # gtid info gtid_mode = ON enforce_gtid_consistency = ON #log_replica_updates = ON #myisam settings key_buffer_size = 32M myisam_sort_buffer_size = 64M # M-S replication settings: relay_log_recovery = 1 #master_info_repository=TABLE #relay_log_info_repository=TABLE # semi sync plugin_dir=/usr/local/mysql/lib/plugin plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #rpl_semi_sync_master_enabled=1 #rpl_semi_sync_slave_enabled=1 #rpl_semi_sync_master_wait_for_slave_count=1 #rpl_semi_sync_master_timeout=1000 #rpl_semi_sync_master_wait_point=AFTER_SYNC # parallel replication settings binlog_transaction_dependency_tracking = WRITESET slave_preserve_commit_order = 1 # replica_preserve_commit_order = 1 slave_checkpoint_period = 2 #replica_checkpoint_period = 2 ## 8.0.27 之前默认是 DATABASE，即 5.6 版本库的并行复制机制。8.0.27之后默认改成 LOGICAL_CLOCK。 slave_parallel_type = LOGICAL_CLOCK # replica_parallel_type = LOGICAL_CLOCK ## 并行复制线程数可以设置为逻辑CPU数量的2倍 slave_parallel_workers = 8 #replica_parallel_workers = 8 #mgr settings loose-plugin_load_add = 'mysql_clone.so' loose-plugin_load_add = 'group_replication.so' # MGR 集群的 UUID ，多实例场景下该 ID 必须唯一。（每个节点 uuid 前几个字符固定，后几个字符自定义，方便阅读） #loose-group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1' # 新加入节点，clone全量数据时，选择的 donor 节点，尽量使用非主节点作为 donor 节点 # clone_valid_donor_list = "172.16.0.201:43061" # MGR本地节点IP  ORT，请自行替换：loose-group_replication_local_address = "172.16.0.201:43061" # MGR集群所有节点IP ORT，请自行替换：loose-group_replication_group_seeds = 172.16.0.201:43061,172.16.0.202:43061,172.16.0.203:43061 # 数据库启动时是否需要自动拉起 MGR： loose-group_replication_start_on_boot = ON # 数据库启动只有是否开启 MGR 引导节点： loose-group_replication_bootstrap_group = OFF # MGR 节点出现异常时改成 read_only 模式： loose-group_replication_exit_state_action = READ_ONLY # 禁用流控制模式： loose-group_replication_flow_control_mode = "DISABLED" # 单主模式： loose-group_replication_single_primary_mode = ON # 在AFTER模式下，当发生个别节点异常时，只要多数派达成一致即可，不会导致整个MGR都被hang住。 loose-group_replication_majority_after_mode = ON # MGR 通信的最大消息大小。大于此大小的消息将自动拆分为片段，分别发送并由收件人重新组装： loose-group_replication_communication_max_message_size = 10M # MGR 接收最大的事务大小： loose-group_replication_transaction_size_limit = 20MB # 设置网络分区的超时时间，如果设置 0 将会一直等待，其上事务会被阻塞。（强烈要求设置为 0 避免分区后 2 个集群数据不一致） loose-group_replication_unreachable_majority_timeout = 0 # 将可疑（suspicious）节点踢出集群的等待时长，最大支持3600秒（1个小时）的等待时间。 # 如果网络环境一般，可疑适当调大 30-60，但不要太大，AFTER 模式下，该值调整的特别大，容易造成 OOM。 loose-group_replication_member_expel_timeout = 5 # 自动尝试连入集群的次数，每次尝试间隔 5 分钟（min）： loose-group_replication_autorejoin_tries = 3 # GreatSQL 版本开启/关闭冲裁节点功能， # 注意在有仲裁节点的情况下，将单主切换成多主模式时，需要把投票节点先关闭再机型切换，否则可能会导致切换失败，并且仲裁节点报错退出MGR。 loose-group_replication_arbitrator = 0 # 可以对每个节点设置地理标签，主要用于解决多机房数据同步的问题。 # 作用：在同城多机房部署方案中，同一个机房的节点可以设置相同的数值，另一个机房里的节点设置另一个不同的数值， # 作用：这样在事务提交时会要求每组 group_replication_zone_id 中至少有个节点确认事务，然后才能继续处理下一个事务。这就可以确保每个机房的某个节点里，总有最新的事务。 #group_replication_zone_id = [0~8] # GreatSQL 版本 启用/关闭 快速单主模式： loose-group_replication_single_primary_fast_mode = 1 # GreatSQL 版本 MGR 集群选主，优先判断各节点事务应用状态，自动选择拥有最新事务的节点作为新的主节点。 loose-group_replication_primary_election_mode = GTID_FIRST # GreatSQL 版本 记录那些因为网络延迟较大导致的 MGR 性能瓶颈（单位：毫秒） loose-group_replication_request_time_threshold = 20000 # GreatSQL 版本启用InnoDB并行查询优化功能 loose-force_parallel_execute = OFF #设置每个SQL语句的并行查询最大并发度 loose-parallel_default_dop = 8 #设置系统中总的并行查询线程数，可以和最大逻辑CPU数量一样 loose-parallel_max_threads = 8 #并行执行时leader线程和worker线程使用的总内存大小上限，可以设置物理内存的5-10%左右 loose-parallel_memory_limit = 512M #innodb settings innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 8 innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 512M innodb_log_files_in_group = 4 innodb_doublewrite_files = 2 innodb_max_undo_log_size = 4G # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 200 innodb_io_capacity_max = 800 innodb_open_files = 65534 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 1024 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_online_alter_log_max_size = 4G innodb_print_ddl_logs = 1 innodb_status_file = 1 innodb_status_output = 0 innodb_status_output_locks = 1 innodb_sort_buffer_size = 64M #innodb monitor settings innodb_monitor_enable = "module_innodb" innodb_monitor_enable = "module_server" innodb_monitor_enable = "module_dml" innodb_monitor_enable = "module_ddl" innodb_monitor_enable = "module_trx" innodb_monitor_enable = "module_os" innodb_monitor_enable = "module_purge" innodb_monitor_enable = "module_log" innodb_monitor_enable = "module_lock" innodb_monitor_enable = "module_buffer" innodb_monitor_enable = "module_index" innodb_monitor_enable = "module_ibuf_system" innodb_monitor_enable = "module_buffer_page" innodb_monitor_enable = "module_adaptive_hash" #pfs settings performance_schema = 1 #performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' |
|
|
dongye
2023-10-4 11:55:43
| |
|
|
dongye
2023-10-4 12:02:44
| |
|
|
yejr
2023-10-6 07:37:21
| |
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
