mysql 8.0.18 mgr 搭建及其切换功能

一、系统安装包

?

1	yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz

二、关闭防火墙和selinux

?

1 2 3 4 5 6

sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config setenforce 0   /etc/init.d/iptables stop   echo "/etc/init.d/iptables stop" >>/etc/rc. local

三、修改系统限制参数

?

1 2 3 4 5 6 7 8 9

cat >> /etc/security/limits.conf << EOF # ###custom # * soft nofile 20480 * hard nofile 65535 * soft nproc 20480 * hard nproc 65535 EOF

四、配置每台hosts主机解析

?

1 2 3 4 5 6 7

cat >> /etc/hosts << "EOF"   10.10.146.28 bj-db-m1 10.10.1.139 bj-db-m2 10.10.173.84 bj-db-m3   EOF

五、修改内核参数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

cat >>/etc/sysctl.conf << "EOF" vm.swappiness=0 #增加tcp支持的队列数 net.ipv4.tcp_max_syn_backlog = 65535 #减少断开连接时 ，资源回收 net.ipv4.tcp_max_tw_buckets = 8000 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 10 #改变本地的端口范围 net.ipv4.ip_local_port_range = 1024 65535 #允许更多的连接进入队列 net.ipv4.tcp_max_syn_backlog = 4096 #对于只在本地使用的数据库服务器 net.ipv4.tcp_fin_timeout = 30 #端口监听队列 net.core.somaxconn=65535 #接受数据的速率 net.core.netdev_max_backlog=65535 net.core.wmem_default=87380 net.core.wmem_max=16777216 net.core.rmem_default=87380 net.core.rmem_max=16777216 EOF   sysctl -p

六、下载安装包

?

1 2 3 4 5 6 7 8 9 10 11 12 13

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz # 解压安装包 tar -xJf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz # 进入目录，做软连接，方便以后升级 cd /usr/ local / ln -s /opt/mysql-8.0.18-linux-glibc2.12-x86_64 mysql # 创建用户 groupadd mysql useradd -g mysql mysql -d /home/mysql -s /sbin/nologin   # 创建相应的目录   mkdir -p /data/mysql/mysql_3306/{logs,tmp,undolog}

七、创建my.cnf配置文件

7-1、第一台配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200

# 第一台 if [ -f /etc/my.cnf ]; then   mv /etc/my.cnf /etc/my.cnf.` date +%Y%m%d%H%m`.bak fi # node1 cat >/data/mysql/mysql_3306/my_3306.cnf << "EOF" [client] port = 3306 socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock   [mysql] prompt= "\u@\h \R:\m:\s [\d]> " no -auto-rehash   [mysqld] user = mysql port = 3306 admin_address = 127.0.0.1 basedir = /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     # semi sync replication 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 = 4 slave_parallel_type = LOGICAL_CLOCK slave_preserve_commit_order = 1   open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_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.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 server-id = 1423306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 binlog_checksum=NONE log_slave_updates slave- rows -search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image= FULL   relay_log_recovery = 1 relay-log-purge = 1 key_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 = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_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 = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog   # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0   # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0   innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0   #performance_schema performance_schema = 1 performance_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 = "10.10.146.28:33006" #本节点的IP地址和端口，注意该端口是组内成员之间通信的端口，而不是MySQL对外提供服务的端口 loose-group_replication_group_seeds = "10.10.146.28:33006,10.10.1.139:33006,10.10.173.84: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.so nice=-19 open -files-limit=65535   EOF

 

7-2、第二台配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200

# 第二台 if [ -f /etc/my.cnf ]; then   mv /etc/my.cnf /etc/my.cnf.` date +%Y%m%d%H%m`.bak fi # node1 cat >/data/mysql/mysql_3306/my_3306.cnf << "EOF" [client] port = 3306 socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock   [mysql] prompt= "\u@\h \R:\m:\s [\d]> " no -auto-rehash   [mysqld] user = mysql port = 3306 admin_address = 127.0.0.1 basedir = /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     # semi sync replication 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 = 4 slave_parallel_type = LOGICAL_CLOCK slave_preserve_commit_order = 1   open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_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.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 server-id = 1433306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 binlog_checksum=NONE log_slave_updates slave- rows -search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image= FULL   relay_log_recovery = 1 relay-log-purge = 1 key_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 = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_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 = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog   # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0   # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0   innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0   #performance_schema performance_schema = 1 performance_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 = "10.10.1.139:33006" #本节点的IP地址和端口，注意该端口是组内成员之间通信的端口，而不是MySQL对外提供服务的端口 loose-group_replication_group_seeds = "10.10.146.28:33006,10.10.1.139:33006,10.10.173.84: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.so nice=-19 open -files-limit=65535   EOF

7-3、第三台配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200

# 第三台 if [ -f /etc/my.cnf ]; then   mv /etc/my.cnf /etc/my.cnf.` date +%Y%m%d%H%m`.bak fi # node1 cat >/data/mysql/mysql_3306/my_3306.cnf << "EOF" [client] port = 3306 socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock   [mysql] prompt= "\u@\h \R:\m:\s [\d]> " no -auto-rehash   [mysqld] user = mysql port = 3306 admin_address = 127.0.0.1 basedir = /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     # semi sync replication 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 = 4 slave_parallel_type = LOGICAL_CLOCK slave_preserve_commit_order = 1   open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_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.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 server-id = 1443306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 binlog_checksum=NONE log_slave_updates slave- rows -search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image= FULL   relay_log_recovery = 1 relay-log-purge = 1 key_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 = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_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 = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog   # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0   # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0   innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0   #performance_schema performance_schema = 1 performance_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 = "10.10.173.84:33006" #本节点的IP地址和端口，注意该端口是组内成员之间通信的端口，而不是MySQL对外提供服务的端口 loose-group_replication_group_seeds = "10.10.146.28:33006,10.10.1.139:33006,10.10.173.84: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.so nice=-19 open -files-limit=65535   EOF

八、修改权限、初始化并启动

?

1 2 3 4 5 6 7 8 9 10

chown -R mysql.mysql /data/mysql/mysql_3306 chown -R mysql.mysql /usr/ local /mysql/   #初始化 # /usr/ local /mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize-insecure # 官方推荐使用 --initialize，会在错误日志中生成难以输入的临时密码，我这里使用的免密码的方式。 /usr/ local /mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure --user=mysql &   #启动数据库 /usr/ local /mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &

九、查看日志

?

1 2	#9、查看日志 # tail -f /data/mysql/mysql_3306/logs/error.log

十、初次登陆

?

1 2	#10、初次登陆 /usr/ local /mysql/bin/mysql -S /data/mysql/mysql_3306/tmp/mysql_3306.sock

十一、修改密码

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

# 修改密码方法   set sql_log_bin = 0;   ALTER USER 'root' @ 'localhost' IDENTIFIED WITH mysql_native_password BY 'GJjumB6g4FcwdF3R6AZc' PASSWORD EXPIRE NEVER ; create user 'root' @ '127.0.0.1' identified WITH mysql_native_password by 'GJjumB6g4FcwdF3R6AZc' PASSWORD EXPIRE NEVER ; grant all privileges on *.* to 'root' @ '127.0.0.1' with grant option ;   create user 'admin_m' @ '127.0.0.1' identified WITH mysql_native_password by 'fcfmTbRw1tz2x5L5GvjJ' PASSWORD EXPIRE NEVER ; grant all privileges on *.* to 'admin_m' @ '127.0.0.1' with grant option ;   create user 'admin_m' @ '%' identified WITH mysql_native_password by 'fcfmTbRw1tz2x5L5GvjJ' PASSWORD EXPIRE NEVER ; grant all privileges on *.* to 'admin_m' @ '%' with grant option ;   create user 'test_w' @ '%' identified with mysql_native_password by 'EeCrfUDO6wRzn72BBQ52' PASSWORD EXPIRE NEVER ; grant insert , delete , update , select on db144.* to 'test_w' @ '%' ;   create user 'test_r' @ '%' identified with mysql_native_password by 'EeCrfUDO6wRzn72BBQ52' PASSWORD EXPIRE NEVER ; grant insert , delete , update , select on db144.* to 'test_r' @ '%' ;   create user 'repl' @ '%' IDENTIFIED with mysql_native_password by 'replpfhOTnWffQdQL3F3' ; GRANT REPLICATION SLAVE ON *.* TO 'repl' @ '%' ; set sql_log_bin = 1;

十二、快捷方式设置

快捷方式

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

ln -s /usr/ local /mysql/lib/libmysqlclient.so /usr/lib/ ln -s /usr/ local /mysql/lib/libmysqlclient.so.21 /usr/lib/libmysqlclient.so.21 ln -s /usr/ local /mysql/lib/libmysqlclient.so /usr/lib64/ ln -s /usr/ local /mysql/lib/libmysqlclient.so.21 /usr/lib64/libmysqlclient.so.21     ln -s /data/mysql/mysql_3306/tmp/mysql_3306.sock /tmp/mysql.sock ln -s /usr/ local /mysql/bin/* /usr/bin/   cat >>~/.bashrc << "EOF" ##########     alias mysql.3306.start= "/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &" alias mysql.3306.stop= "/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'GJjumB6g4FcwdF3R6AZc' shutdown &" alias mysql.3306.login= "/usr/local/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p'GJjumB6g4FcwdF3R6AZc'"     ########## EOF     source /root/.bash_profile   cat >>/etc/ld.so.conf << "EOF" /usr/ local /mysql/lib EOF   ldconfig mysql.3306.login

十三、MGR配置

13-1、第一台配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

# MGR 第一台配置： # 第一步：创建用于复制的用户   set sql_log_bin=0; create user 'repuser' @ '%' identified by 'JhXpMK44ju8Vp5bxvO2N' ;   grant replication slave,replication client on *.* to 'repuser' @ '%' ;   create user 'repuser' @ '127.0.0.1' identified by 'JhXpMK44ju8Vp5bxvO2N' ;   grant replication slave,replication client on *.* to 'repuser' @ '127.0.0.1' ;   create user 'repuser' @ 'localhost' identified by 'JhXpMK44ju8Vp5bxvO2N' ;   grant replication slave,replication client on *.* to 'repuser' @ 'localhost' ;   set sql_log_bin=1;   # 第二步：配置复制所使用的用户   change master to master_user= 'repuser' ,master_password= 'JhXpMK44ju8Vp5bxvO2N' for channel 'group_replication_recovery' ;   # 第三步：安装mysql group replication这个插件     # 备注：如果在my.cnf里写写入 plugin_load= "group_replication=group_replication.so" 这步就可以不用操作   install plugin group_replication soname 'group_replication.so' ;   # 通过show plugins;查看是否安装成功 show plugins;   # 第四步：建个群(官方点的说法就是初始化一个复制组   set global group_replication_bootstrap_group= on ; start group_replication; set global group_replication_bootstrap_group= off ;   select * from performance_schema.replication_group_members;

 

13-2、第二台、第三台配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

########################################################################## #MGR 配置其他从节点 #在所有从主机上的mysql中执行 # 第一步：创建用于复制的用户   set sql_log_bin=0; create user 'repuser' @ '%' identified by 'JhXpMK44ju8Vp5bxvO2N' ;   grant replication slave,replication client on *.* to 'repuser' @ '%' ;   create user 'repuser' @ '127.0.0.1' identified by 'JhXpMK44ju8Vp5bxvO2N' ;   grant replication slave,replication client on *.* to 'repuser' @ '127.0.0.1' ;   create user 'repuser' @ 'localhost' identified by 'JhXpMK44ju8Vp5bxvO2N' ;   grant replication slave,replication client on *.* to 'repuser' @ 'localhost' ;   set sql_log_bin=1;   # 第二步：配置复制所使用的用户   change master to master_user= 'repuser' ,master_password= 'JhXpMK44ju8Vp5bxvO2N' for channel 'group_replication_recovery' ;   # 第三步：安装mysql group replication这个插件     # 备注：如果在my.cnf里写写入 plugin_load= "group_replication=group_replication.so" 这步就可以不用操作   install plugin group_replication soname 'group_replication.so' ;   # 通过show plugins;查看是否安装成功 show plugins;   # 第四步：加入前面创建好的复制组   start group_replication; select * from performance_schema.replication_group_members; #########################################################################################   # 检查状态 mysql> select * from performance_schema.replication_group_members; + ---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME  | MEMBER_ID    | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | + ---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d955da6d-0048-11ea-b7b4-525400f4342d | bj-db-m1 | 3306 | ONLINE | PRIMARY | 8.0.18  | | group_replication_applier | e050c34f-0048-11ea-917d-52540021fab9 | bj-db-m3 | 3306 | ONLINE | SECONDARY | 8.0.18  | | group_replication_applier | e6c56347-0048-11ea-9e8b-5254007c241f | bj-db-m2 | 3306 | ONLINE | SECONDARY | 8.0.18  | + ---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)

十四、单主切换到多主

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

###################################################################### # 单主切换到多主 # MGR切换模式需要重新启动组复制，因此需要在所有节点上先关闭组复制， #设置 group_replication_single_primary_mode= OFF 等参数，再启动组复制。 1) 停止组复制(在所有MGR节点上执行)： stop group_replication; set global group_replication_single_primary_mode= OFF ; set global group_replication_enforce_update_everywhere_checks= ON ;   2) 随便选择某个MGR节点执行 (比如这里选择在MGR-node1节点): set global group_replication_recovery_get_public_key=1; SET GLOBAL group_replication_bootstrap_group= ON ; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group= OFF ;   3) 然后在其他的MGR节点执行 (这里指MGR-node2和MGR-node3节点上执行): set global group_replication_recovery_get_public_key=1; START GROUP_REPLICATION;   4) 查看MGR组信息 (在任意一个MGR节点上都可以查看) SELECT * FROM performance_schema.replication_group_members;     # 可以看到所有MGR节点状态都是online，角色都是 PRIMARY ，MGR多主模式搭建成功。

##########################################################################

十五、多主切换回单主

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

########################################################################## # 多主切回单主模式 1) 停止组复制(在所有MGR节点上执行)： stop group_replication; set global group_replication_enforce_update_everywhere_checks= OFF ; set global group_replication_single_primary_mode= ON ;   2) 选择一个节点作为主节点, 在主节点上执行 (这里选择MGR-node1节点作为主节点) SET GLOBAL group_replication_bootstrap_group= ON ; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group= OFF ;   3) 在其他剩余的节点, 也就是从库节点上执行 (这里从库节点指的就是MGR-node2和MGR-node3): START GROUP_REPLICATION;   4) 查看MGR组信息 (在任意一个MGR节点上都可以查看) SELECT * FROM performance_schema.replication_group_members; ##########################################################################

十六、故障注意事项

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

# 故障注意点： # 单主模式，恢复MGR-node1节点, 恢复后, 需要手动激活下该节点的组复制功能 # 如果节点发生故障, 在恢复后需要重新加入到MGR集群里, 正确的做法是: STOP GROUP_REPLICATION; START GROUP_REPLICATION;   # 如果某个节点挂了, 则其他的节点继续进行同步. # 当故障节点恢复后, 只需要手动激活下该节点的组复制功能( "START GROUP_REPLICATION;" ), # 即可正常加入到MGR组复制集群内并自动同步其他节点数据.     # 如果是i/o复制出现异常 # 确定数据无误后 # 查找主库的gtid情况 mysql> show global variables like '%gtid%' ; + ----------------------------------------------+-------------------------------------------------------+ | Variable_name    | Value       | + ----------------------------------------------+-------------------------------------------------------+ | binlog_gtid_simple_recovery   | ON       | | enforce_gtid_consistency   | ON       | | group_replication_gtid_assignment_block_size | 1000000      | | gtid_executed    | 58f6e65e-9309-11e9-9d88-525400184a0a:1-946050:1000003 | | gtid_executed_compression_period  | 1000       | | gtid_mode     | ON       | | gtid_owned     |       | | gtid_purged     |       | | session_track_gtids    | OFF       | + ----------------------------------------------+-------------------------------------------------------+ rows in set (0.00 sec)   # 在有故障的从库中操作 stop GROUP_REPLICATION; reset master; set global gtid_purged= '58f6e65e-9309-11e9-9d88-525400184a0a:1-946055:1000003' ; START GROUP_REPLICATION;   # 添加白名单网段 stop group_replication; set global group_replication_ip_whitelist= "127.0.0.1/32,172.16.60.0/24,172.16.50.0/24,172.16.51.0/24" ; start group_replication; show variables like "group_replication_ip_whitelist" ;   # 一定要注意: 配置白名单前面一定要先关闭 Group Replication, 及先要执行 "stop group_replication;"

总结

以上所述是小编给大家介绍的mysql 8.0.18 mgr 搭建及其切换功能，希望对大家有所帮助！

原文链接：https://www.cnblogs.com/bjx2020/p/11810747.html