mysql 8.0.18 mgr 搭建及其切换功能

这篇文章主要介绍了mysql 8.0.18 mysql搭建及其切换功能,本文通过实例代码给大家讲解的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下

一、系统安装包

?

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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/13568/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-08-20 1:56:49
下一篇 2022-08-20 1:57:00

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!