mysql 搭建mgr_mysql 8.0.16 单主 mgr搭建

if [ -f /etc/my.cnf ]; then

mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`.bakfi# node1cat >/data/mysql/mysql_3306/my_3306.cnf <

port= 3306socket= /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]

prompt="\u@\h \R:\m:\s [\d]>"no-auto-rehash

[mysqld]

user=mysql

port= 3306admin_address= 127.0.0.1basedir= /usr/local/mysql

datadir= /data/mysql/mysql_3306/data

socket= /data/mysql/mysql_3306/tmp/mysql_3306.sock

pid-file =mysql_3306.pid

character-set-server =utf8mb4

skip_name_resolve= 1#replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%# Two-Master configure

#server-1#auto-increment-offset = 1#auto-increment-increment = 2#server-2#auto-increment-offset = 2#auto-increment-increment = 2# semisyncreplication settings #

#plugin_dir= /usr/local/mysql/lib/mysql/plugin

#plugin_load= "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"plugin_dir= /usr/local/mysql/lib/plugin #官方版本的路径

plugin_load= "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#官方版本的路径

slave_parallel_workers= 4slave_parallel_type=LOGICAL_CLOCK

slave_preserve_commit_order= 1open_files_limit= 65535back_log= 1024max_connections= 1024max_connect_errors= 1000000table_open_cache= 1024table_definition_cache= 1024table_open_cache_instances= 64thread_stack=512K

external-locking =FALSE

max_allowed_packet=32M

sort_buffer_size=4M

join_buffer_size=4M

thread_cache_size= 1536interactive_timeout= 600wait_timeout= 600tmp_table_size=32M

max_heap_table_size=32M

slow_query_log= 1log_timestamps=SYSTEM

slow_query_log_file= /data/mysql/mysql_3306/logs/slow.log

log-error = /data/mysql/mysql_3306/logs/error.log

long_query_time= 0.1log_queries_not_using_indexes=1log_throttle_queries_not_using_indexes= 60min_examined_row_limit= 100log_slow_admin_statements= 1log_slow_slave_statements= 1server-id = 1423306log-bin = /data/mysql/mysql_3306/logs/mysql-bin

sync_binlog= 1binlog_cache_size=4M

max_binlog_cache_size=2G

max_binlog_size=1G

binlog_expire_logs_seconds=2592000master_info_repository=TABLE

relay_log_info_repository=TABLE

gtid_mode=on

enforce_gtid_consistency= 1binlog_checksum=NONE

log_slave_updates

slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'binlog_format=row

binlog_row_image=FULL

relay_log_recovery= 1relay-log-purge = 1key_buffer_size=32M

read_buffer_size=8M

read_rnd_buffer_size=4M

bulk_insert_buffer_size=64M

myisam_sort_buffer_size=128M

myisam_max_sort_file_size=10G

myisam_repair_threads= 1lock_wait_timeout= 3600explicit_defaults_for_timestamp= 1innodb_thread_concurrency= 0innodb_sync_spin_loops= 100innodb_spin_wait_delay= 30#transaction_isolation= REPEATABLE-READ

transaction_isolation= READ-COMMITTED

#innodb_additional_mem_pool_size=16M

innodb_buffer_pool_size=2867M

innodb_buffer_pool_instances= 4innodb_buffer_pool_load_at_startup= 1innodb_buffer_pool_dump_at_shutdown= 1innodb_data_file_path=ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit= 1innodb_log_buffer_size=32M

innodb_log_file_size=2G

innodb_log_files_in_group= 3innodb_max_undo_log_size=4G

innodb_undo_directory= /data/mysql/mysql_3306/undolog

# 根据您的服务器IOPS能力适当调整

# 一般配普通SSD盘的话，可以调整到10000 - 20000# 配置高端PCIe SSD卡的话，则可以调整的更高，比如50000 - 80000innodb_io_capacity= 4000innodb_io_capacity_max= 8000innodb_flush_sync= 0innodb_flush_neighbors= 0innodb_write_io_threads= 8innodb_read_io_threads= 8innodb_purge_threads= 4innodb_page_cleaners= 4innodb_open_files= 65535innodb_max_dirty_pages_pct= 50innodb_flush_method=O_DIRECT

innodb_lru_scan_depth= 4000innodb_checksum_algorithm=crc32

innodb_lock_wait_timeout= 10innodb_rollback_on_timeout= 1innodb_print_all_deadlocks= 1innodb_file_per_table= 1innodb_online_alter_log_max_size=4G

innodb_stats_on_metadata= 0# some varfor MySQL 8log_error_verbosity= 3innodb_print_ddl_logs= 1binlog_expire_logs_seconds= 2592000#innodb_dedicated_server= 0innodb_status_file= 1# 注意: 开启 innodb_status_output& innodb_status_output_locks 后, 可能会导致log-error文件增长较快

innodb_status_output= 0innodb_status_output_locks= 0#performance_schema

performance_schema= 1performance_schema_instrument= '%memory%=on'performance_schema_instrument= '%lock%=on'#innodb monitor

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"#MGR

#GR配置项 基中loose前缀表示若group Replication plugin未加载 mysql server仍明治维新启动

transaction_write_set_extraction=XXHASH64 #对每个事务获取write set，并且用XXHASH64算法获取hash值

loose-group_replication_group_name = "58f6e65e-9309-11e9-9d88-525400184a0a"#组名，此处可拿select uuid()生成

loose-group_replication_start_on_boot =off #在mysqld启动时不自动启动组复制

loose-group_replication_local_address = "192.168.142.142:33006"#本节点的IP地址和端口，注意该端口是组内成员之间通信的端口，而不是MySQL对外提供服务的端口

loose-group_replication_group_seeds = "192.168.142.142:33006,192.168.142.143:33006,192.168.142.145:33006"#种子节点的IP和端口号，新成员加入到集群的时候需要联系种子节点，启动集群的节点不使用该选项

loose-group_replication_bootstrap_group =off #关闭，如果打开会造成脑裂 #是否启动集群，注意，该选项任何时候只能用于一个节点，通常情况下启动集群的时候使用，启动之后需要关闭该选项

loose-group_replication_member_weight = 50#权重选择

[mysqldump]

quick

max_allowed_packet=32M

[mysqld_safe]

#malloc-lib=/usr/local/mysql/lib/jmalloc.sonice=-19open-files-limit=65535EOF