一、系统安装包
|
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/configsetenforce 0/etc/init.d/iptables stopecho "/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 65535EOF |
四、配置每台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 = 8000net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.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=65535net.core.wmem_default=87380net.core.wmem_max=16777216net.core.rmem_default=87380net.core.rmem_max=16777216EOFsysctl -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 mysqluseradd -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`.bakfi # node1cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"[client]port = 3306socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock[mysql]prompt="\u@\h \R:\m:\s [\d]> "no-auto-rehash[mysqld]user = mysqlport = 3306admin_address = 127.0.0.1basedir = /usr/local/mysqldatadir = /data/mysql/mysql_3306/datasocket = /data/mysql/mysql_3306/tmp/mysql_3306.sockpid-file = mysql_3306.pidcharacter-set-server = utf8mb4skip_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 = 4slave_parallel_type = LOGICAL_CLOCKslave_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 = 512Kexternal-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 1536interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mmax_heap_table_size = 32Mslow_query_log = 1log_timestamps = SYSTEMslow_query_log_file = /data/mysql/mysql_3306/logs/slow.loglog-error = /data/mysql/mysql_3306/logs/error.loglong_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-binsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gbinlog_expire_logs_seconds=2592000 master_info_repository = TABLErelay_log_info_repository = TABLEgtid_mode = onenforce_gtid_consistency = 1binlog_checksum=NONElog_slave_updatesslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'binlog_format = rowbinlog_row_image=FULLrelay_log_recovery = 1relay-log-purge = 1key_buffer_size = 32Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1lock_wait_timeout = 3600explicit_defaults_for_timestamp = 1innodb_thread_concurrency = 0innodb_sync_spin_loops = 100innodb_spin_wait_delay = 30#transaction_isolation = REPEATABLE-READtransaction_isolation = READ-COMMITTED#innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 2867Minnodb_buffer_pool_instances = 4innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1G:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 3innodb_max_undo_log_size = 4Ginnodb_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_DIRECTinnodb_lru_scan_depth = 4000innodb_checksum_algorithm = crc32innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_online_alter_log_max_size = 4Ginnodb_stats_on_metadata = 0# some var for 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_schemaperformance_schema = 1performance_schema_instrument = '%memory%=on'performance_schema_instrument = '%lock%=on'#innodb monitorinnodb_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]quickmax_allowed_packet = 32M[mysqld_safe]#malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19open-files-limit=65535EOF |
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`.bakfi # node1cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"[client]port = 3306socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock[mysql]prompt="\u@\h \R:\m:\s [\d]> "no-auto-rehash[mysqld]user = mysqlport = 3306admin_address = 127.0.0.1basedir = /usr/local/mysqldatadir = /data/mysql/mysql_3306/datasocket = /data/mysql/mysql_3306/tmp/mysql_3306.sockpid-file = mysql_3306.pidcharacter-set-server = utf8mb4skip_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 = 4slave_parallel_type = LOGICAL_CLOCKslave_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 = 512Kexternal-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 1536interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mmax_heap_table_size = 32Mslow_query_log = 1log_timestamps = SYSTEMslow_query_log_file = /data/mysql/mysql_3306/logs/slow.loglog-error = /data/mysql/mysql_3306/logs/error.loglong_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 = 1433306log-bin = /data/mysql/mysql_3306/logs/mysql-binsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gbinlog_expire_logs_seconds=2592000 master_info_repository = TABLErelay_log_info_repository = TABLEgtid_mode = onenforce_gtid_consistency = 1binlog_checksum=NONElog_slave_updatesslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'binlog_format = rowbinlog_row_image=FULLrelay_log_recovery = 1relay-log-purge = 1key_buffer_size = 32Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1lock_wait_timeout = 3600explicit_defaults_for_timestamp = 1innodb_thread_concurrency = 0innodb_sync_spin_loops = 100innodb_spin_wait_delay = 30#transaction_isolation = REPEATABLE-READtransaction_isolation = READ-COMMITTED#innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 2867Minnodb_buffer_pool_instances = 4innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1G:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 3innodb_max_undo_log_size = 4Ginnodb_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_DIRECTinnodb_lru_scan_depth = 4000innodb_checksum_algorithm = crc32innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_online_alter_log_max_size = 4Ginnodb_stats_on_metadata = 0# some var for 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_schemaperformance_schema = 1performance_schema_instrument = '%memory%=on'performance_schema_instrument = '%lock%=on'#innodb monitorinnodb_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]quickmax_allowed_packet = 32M[mysqld_safe]#malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19open-files-limit=65535EOF |
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`.bakfi # node1cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"[client]port = 3306socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock[mysql]prompt="\u@\h \R:\m:\s [\d]> "no-auto-rehash[mysqld]user = mysqlport = 3306admin_address = 127.0.0.1basedir = /usr/local/mysqldatadir = /data/mysql/mysql_3306/datasocket = /data/mysql/mysql_3306/tmp/mysql_3306.sockpid-file = mysql_3306.pidcharacter-set-server = utf8mb4skip_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 = 4slave_parallel_type = LOGICAL_CLOCKslave_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 = 512Kexternal-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 1536interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mmax_heap_table_size = 32Mslow_query_log = 1log_timestamps = SYSTEMslow_query_log_file = /data/mysql/mysql_3306/logs/slow.loglog-error = /data/mysql/mysql_3306/logs/error.loglong_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 = 1443306log-bin = /data/mysql/mysql_3306/logs/mysql-binsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gbinlog_expire_logs_seconds=2592000 master_info_repository = TABLErelay_log_info_repository = TABLEgtid_mode = onenforce_gtid_consistency = 1binlog_checksum=NONElog_slave_updatesslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'binlog_format = rowbinlog_row_image=FULLrelay_log_recovery = 1relay-log-purge = 1key_buffer_size = 32Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1lock_wait_timeout = 3600explicit_defaults_for_timestamp = 1innodb_thread_concurrency = 0innodb_sync_spin_loops = 100innodb_spin_wait_delay = 30#transaction_isolation = REPEATABLE-READtransaction_isolation = READ-COMMITTED#innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 2867Minnodb_buffer_pool_instances = 4innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1G:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 3innodb_max_undo_log_size = 4Ginnodb_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_DIRECTinnodb_lru_scan_depth = 4000innodb_checksum_algorithm = crc32innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_online_alter_log_max_size = 4Ginnodb_stats_on_metadata = 0# some var for 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_schemaperformance_schema = 1performance_schema_instrument = '%memory%=on'performance_schema_instrument = '%lock%=on'#innodb monitorinnodb_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]quickmax_allowed_packet = 32M[mysqld_safe]#malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19open-files-limit=65535EOF |
八、修改权限、初始化并启动
|
1
2
3
4
5
6
7
8
9
10
|
chown -R mysql.mysql /data/mysql/mysql_3306chown -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.21ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib64/ln -s /usr/local/mysql/lib/libmysqlclient.so.21 /usr/lib64/libmysqlclient.so.21ln -s /data/mysql/mysql_3306/tmp/mysql_3306.sock /tmp/mysql.sockln -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'"##########EOFsource /root/.bash_profilecat >>/etc/ld.so.conf <<"EOF"/usr/local/mysql/libEOFldconfig 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
