-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpercona-xtradb-cluster_install.txt
More file actions
188 lines (139 loc) · 4.74 KB
/
percona-xtradb-cluster_install.txt
File metadata and controls
188 lines (139 loc) · 4.74 KB
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
#三台机器：
192.168.142.186,192.168.142.187,192.168.142.188
#一 、安装软件
yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum -y install Percona-XtraDB-Cluster-57
#二、 修改数据库的初始密码，登录三台机器，执行下面脚本，修改数据库root密码
systemctl start mysql
#获取mysql自动生成的初始密码
INIT_PWD=`grep 'temporary password' /var/log/mysqld.log|awk -F 'root@localhost: ' '{print $2}'`
#新密码
NEW_PWD='rootPass'
#修改root密码
mysql -u root -p$INIT_PWD -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '$NEW_PWD';"
systemctl stop mysql
#三、分别修改三台机上的配置文件
#登录：192.168.142.186
#编辑：/etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.142.186,192.168.142.187,192.168.142.188
wsrep_node_name=pxc1
wsrep_node_address=192.168.142.186
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#登录：192.168.142.187
#编辑：/etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.142.186,192.168.142.187,192.168.142.188
wsrep_node_name=pxc2
wsrep_node_address=192.168.142.187
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#登录：192.168.142.188
#编辑：/etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.142.186,192.168.142.187,192.168.142.188
wsrep_node_name=pxc3
wsrep_node_address=192.168.142.188
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#四、启动bootstrap service。
#登录：192.168.142.186
#启动mysql
systemctl start [email protected]
#创建复制用户
mysql -u root -p$NEW_PWD -e "CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd'"
mysql -u root -p$NEW_PWD -e "GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'"
mysql -u root -p$NEW_PWD -e "FLUSH PRIVILEGES"
#五 启动其他两台mysql
systemctl start mysql
#MySQL 集群启动完成，应用可以连接任意节点访问数据库， 建议写入操作都使用同一个节点，性能更好。
#方案约束： 只能使用InnodbDB，所有表都必须有主键，LOCK TABLES and UNLOCK TABLES is not supported in multi-master setups Lock functions, such as GET_LOCK(), RELEASE_LOCK(), and so on
具体请参考：https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html
#配置percona-xtradb-cluster 的健康检查,haproxy 可以通过http访问检查各实例的状态
#给clustercheck 脚本授权访问数据库
mysql -u root -p$NEW_PWD -e "grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword\!'"
mysql -u root -p$NEW_PWD -e "flush privileges"
#启用/etc/xinetd.d/mysqlchk服务
yum install xinetd
systemctl start xinetd
#keepalived配置：
#安装killall 命令
yum -y install psmisc
global_defs {
router_id LVS_DEVEL
}
vrrp_script chk_haproxy {
script "killall -0 haproxy" # cheaper than pidof
interval 1 # check every 1 seconds
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.142.185/24
}
track_script {
chk_haproxy
}
}
#haproxy 配置
#修改 /etc/sysctl.conf 增加 net.ipv4.ip_nonlocal_bind=1 执行sysctl -p
maxconn 4096
chroot /usr/share/haproxy
user haproxy
group haproxy
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
frontend stats-front
bind 192.168.142.185:80
mode http
default_backend stats-back
frontend pxc-onenode-front
bind 192.168.142.185:4306
mode tcp
default_backend pxc-onenode-back
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:secret
backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server c1 192.168.142.186:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.142.187:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server c3 192.168.142.188:3306 check port 9200 inter 12000 rise 3 fall 3 backup