|
本帖最后由 fuhao009 于 2023-9-13 12:39 编辑 # cat /etc/my.cnf [client] port = 3306 socket = /mjxt/mysql/mysql.sock [mysql] prompt = "\u@mysqldb \R:\m:\s [\d]> " no_auto_rehash loose-skip-binary-as-hex [mysqld] user = mysql port = 3306 #主从复制或MGR集群中，server_id记得要不同 #另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同 #server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以 server_id = 3306 basedir = /mjxt/GreatSQL datadir = /mjxt/mysql socket = /mjxt/mysql/mysql.sock pid_file = mysqldb.pid character_set_server = UTF8MB4 skip_name_resolve = 1 #若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数 default_time_zone = "+8:00" #启用admin_port，连接数爆满等紧急情况下给管理员留个后门 admin_address = '127.0.0.1' admin_port = 33062 #performance setttings lock_wait_timeout = 3600 open_files_limit = 65535 back_log = 1024 max_connections = 100 max_connect_errors = 1000000 table_open_cache = 200 table_definition_cache = 200 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 = 150 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M #log settings log_timestamps = SYSTEM log_error = /mjxt/mysql/error.log log_error_verbosity = 3 slow_query_log = 1 log_slow_extra = 1 slow_query_log_file = /mjxt/mysql/slow.log long_query_time = 0.1 log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_bin = /mjxt/mysql/mybinlog binlog_format = ROW sync_binlog = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G binlog_rows_query_log_events = 1 binlog_expire_logs_seconds = 604800 #MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行 binlog_checksum = CRC32 gtid_mode = ON enforce_gtid_consistency = TRUE #myisam settings key_buffer_size = 32M myisam_sort_buffer_size = 128M #replication settings relay_log_recovery = 1 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 64 #可以设置为逻辑CPU数量的2倍 binlog_transaction_dependency_tracking = WRITESET slave_preserve_commit_order = 1 slave_checkpoint_period = 2 replication_optimize_for_static_plugin_config = ON replication_sender_observe_commit_only = ON #mgr settings loose-plugin_load_add = 'mysql_clone.so' loose-plugin_load_add = 'group_replication.so' loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1" #MGR本地节点IP  ORT，请自行替换loose-group_replication_local_address = "10.33.0.18:33061" #MGR集群所有节点IP ORT，请自行替换loose-group_replication_group_seeds = "10.33.0.17:33061,10.33.0.18:33061,10.33.0.19:33061" loose-group_replication_start_on_boot = OFF loose-group_replication_bootstrap_group = OFF loose-group_replication_exit_state_action = READ_ONLY loose-group_replication_flow_control_mode = "DISABLED" loose-group_replication_single_primary_mode = ON loose-group_replication_communication_max_message_size = 10M loose-group_replication_unreachable_majority_timeout = 30 loose-group_replication_member_expel_timeout = 5 loose-group_replication_autorejoin_tries = 288 #innodb settings transaction_isolation = REPEATABLE-READ innodb_buffer_pool_size = 5120M innodb_buffer_pool_instances = 4 innodb_data_file_path = ibdata1:12M:autoextend innodb_flush_log_at_trx_commit = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能 innodb_log_buffer_size = 32M innodb_log_file_size = 1G #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小 innodb_log_files_in_group = 3 loose-innodb_redo_log_capacity = 3G innodb_max_undo_log_size = 4G # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_open_files = 65535 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 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 & innodb_status_output_locks 后, 可能会导致log_error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 1 innodb_sort_buffer_size = 67108864 innodb_adaptive_hash_index = OFF #提高索引统计信息精确度 innodb_stats_persistent_sample_pages = 500 innodb_adaptive_hash_index = 0 loose-sql_generate_invisible_primary_key = ON #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' loose-performance_schema_instrument = '%lock%=on' [mysqldump] quick # 报错信息 2023-09-13T11:15:31.747181+08:00 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory. 2023-09-13T11:15:31.749256+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-09-13T11:15:31.749891+08:00 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3306 2023-09-13T11:15:31.749975+08:00 0 [Note] [MY-010253] [Server] IPv6 is available. 2023-09-13T11:15:31.749991+08:00 0 [Note] [MY-010264] [Server] - '::' resolves to '::'; 2023-09-13T11:15:31.750006+08:00 0 [Note] [MY-010251] [Server] Server socket created on IP: '::'. 2023-09-13T11:15:31.754604+08:00 0 [ERROR] [MY-010147] [Server] Too many arguments (first extra is '&'). 2023-09-13T11:15:31.754634+08:00 0 [Note] [MY-010148] [Server] Use --verbose --help to get a list of available options! 2023-09-13T11:15:31.754712+08:00 0 [ERROR] [MY-010119] [Server] Aborting 2023-09-13T11:15:31.756093+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize |
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
