复制代码
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
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443mysql双主部署+keepalived 安装mysql（此处使用二进制，还可以用 rpm包安装 或 mysql编译安装） https://blog.csdn.net/u012562411/article/details/85220783 0.卸载原来版本mysql或mariadb rpm -e mariadb-libs --nodeps 1.进入/usr/local，下载tar包 wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 2.解压并重命名 tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mv /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql 3.在/usr/local/mysql新建data目录 mkdir data 4.新建用户组 groupadd mysql 5.为用户组添加新用户 useradd mysql -g mysql 6.将mysql文件夹下所有文件/文件夹的所有者及所属组改为mysql chown -R mysql.mysql /usr/local/mysql 7.配置 /usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data 8.继续安装 /usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize 9.编辑/etc/my.cnf文件，将一下代码添加到[mysqld]下。 vi /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 #取消密码验证 skip-grant-tables 10.将mysql加入服务，并设置开机自启 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql chkconfig mysql on 11.开启服务 service mysql start 12.登录mysql（由于/etc/my.cnf中设置了取消密码验证，所以此处密码任意，或者直接回车） /usr/local/mysql/bin/mysql -u root -p 13.使用mysql数据库 >>use mysql; 14.修改密码 >>update user set authentication_string=password('Cxyjy!Z1zqsc') where user='root'; >>flush privileges; >>exit; 15.将/etc/my.cnf中的skip-grant-tables删除或注释掉 vim /etc/my.cnf 16.登录再次设置密码（可以和刚刚的相同） /usr/local/mysql/bin/mysql -u root -p >>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; >>exit; 17.设置允许远程连接，设置了这条，就可以在自己的window电脑上连接并操作数据库了 /usr/local/mysql/bin/mysql -u root -p >>use mysql; >>update user set host='%' where user = 'root'; >>flush privileges; >>eixt; 18.开放端口 firewall-cmd --zone=public --add-port=3600/tcp --permanent firewall-cmd --reload 双主配置 https://blog.csdn.net/yuefei169/article/details/81297668 https://www.cnblogs.com/kevingrace/p/6710136.html 1.主机添加从机账户 -- masterA配置masterB信息 grant replication slave on *.* to 'replicate'@'172.23.7.117' identified by '123456'; flush privileges; -- masterB配置masterA信息 grant replication slave on *.* to 'replicate'@'172.23.7.116' identified by '123456'; flush privileges; 2.修改配置文件 -- masterA [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 # 主从配置 server-id=1 log-bin=mysql-bin # 跳过复制错误 slave-skip-errors=all # 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 log-slave-updates=true # 自增 auto-increment-increment=1 auto-increment-offset=2 # 忽略的数据库 binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=information_schema -- masterB [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 # 主从配置 server-id=2 log-bin=mysql-bin # 跳过复制错误 slave-skip-errors=all # 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 log-slave-updates=true # 自增 auto-increment-increment=2 auto-increment-offset=2 # 忽略的数据库 binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=information_schema 3.查看主从状态 -- masterA > show master status; -- masterB > show master status; -- 查询结果 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 4.配置同步信息 -- masterA配置masterB信息 > stop slave; > change master to master_host='172.23.7.117',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=150; > start slave; > show slave statusG; 显示有如下状态则正常： Slave_IO_Running: Yes Slave_SQL_Running: Yes -- masterB配置masterA信息 > stop slave; > change master to master_host='172.23.7.116',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=437; > start slave; > show slave statusG; 显示有如下状态则正常： Slave_IO_Running: Yes Slave_SQL_Running: Yes 配置Mysql+Keepalived https://www.cnblogs.com/kevingrace/p/6710136.html https://www.cnblogs.com/Steward-Xu/p/7275273.html 1.安装keepalived并将其配置成系统服务。master1和master2两台机器上同样进行如下操作 [root@master1 ~]# yum install -y openssl-devel [root@master1 ~]# yum install gcc [root@master1 ~]# cd /usr/local/src/ [root@master1 src]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz [root@master1 src]# tar -zvxf keepalived-1.3.5.tar.gz [root@master1 src]# cd keepalived-1.3.5 [root@master1 keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived [root@master1 keepalived-1.3.5]# make && make install [root@master1 keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/ [root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ [root@master1 keepalived-1.3.5]# mkdir /etc/keepalived/ [root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ [root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ [root@master1 keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local 2-1.master1机器上的keepalived.conf配置。（下面配置中没有使用lvs的负载均衡功能，所以不需要配置虚拟服务器virtual server） [root@master1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak [root@master1 ~]# vim /etc/keepalived/keepalived.conf ＃清空默认内容，直接采用下面配置： global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from ops@wangshibo.cn smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MASTER-HA } vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等 script "/opt/chk_mysql.sh" #这里通过脚本监测 interval 2 #脚本执行间隔，每2s检测一次 weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5 fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间） rise 1 #检测1次成功就算成功。但不修改优先级 } vrrp_instance VI_1 { state BACKUP interface ens33 #指定虚拟ip的网卡接口 ip addr查询 virtual_router_id 51 #路由器标识，MASTER和BACKUP必须是一致的 priority 101 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 advert_int 1 nopreempt #非抢占模式 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.23.7.115 } track_script { chk_mysql_port } } 2-2.编写切换脚本。KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管 [root@master1 ~]# vim /opt/chk_mysql.sh #!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) if [ "${counter}" -eq 0 ]; then /etc/init.d/keepalived stop fi [root@master1 ~]# chmod 755 /opt/chk_mysql.sh 2-3.启动keepalived服务 [root@master1 ~]# vi /lib/systemd/system/keepalived.service PIDFile=/var/run/keepalived.pid [root@master1 ~]# systemctl daemon-reload [root@master1 ~]# /etc/init.d/keepalived start 正在启动 keepalived： [确定] 3-1. master1机器上的keepalived.conf配置。（下面配置中没有使用lvs的负载均衡功能，所以不需要配置虚拟服务器virtual server） [root@master1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak [root@master1 ~]# vim /etc/keepalived/keepalived.conf ＃清空默认内容，直接采用下面配置： global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from ops@wangshibo.cn smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MASTER-HA } vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等 script "/opt/chk_mysql.sh" #这里通过脚本监测 interval 2 #脚本执行间隔，每2s检测一次 weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5 fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间） rise 1 #检测1次成功就算成功。但不修改优先级 } vrrp_instance VI_1 { state BACKUP interface ens33 #指定虚拟ip的网卡接口 ip addr查询 virtual_router_id 51 #路由器标识，MASTER和BACKUP必须是一致的 priority 99 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.23.7.115 } track_script { chk_mysql_port } } 3-2.编写切换脚本。KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管 [root@master1 ~]# vim /opt/chk_mysql.sh #!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) if [ "${counter}" -eq 0 ]; then /etc/init.d/keepalived stop fi [root@master1 ~]# chmod 755 /opt/chk_mysql.sh 3-3.启动keepalived服务 [root@master1 ~]# vi /lib/systemd/system/keepalived.service PIDFile=/var/run/keepalived.pid [root@master1 ~]# systemctl daemon-reload [root@master1 ~]# /etc/init.d/keepalived start 正在启动 keepalived： [确定] 4.检测keepalive是否起作用 mysql双主部署-配置文件 执行sql -- 117 show master status; stop slave; change master to master_host='172.23.7.116',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=851; start slave; show slave statusG; -- 116 show master status; stop slave; change master to master_host='172.23.7.117',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=851; start slave; show slave statusG; 配置文件 [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 # 主从配置 server-id=1 log-bin=mysql-bin # 跳过复制错误 slave-skip-errors=all # 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 log-slave-updates=true # 自增 auto-increment-increment=1 auto-increment-offset=2 # 忽略的数据库 binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=information_schema [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 # 主从配置 server-id = 2 log-bin = mysql-bin # 跳过复制错误 slave-skip-errors = all # 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 log-slave-updates = true # 自增 auto-increment-increment = 2 auto-increment-offset = 2 # 忽略的数据库 binlog-ignore-db = mysql binlog-ignore-db = performance_schema binlog-ignore-db = information_schema keepalive配置文件 global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from ops@wangshibo.cn smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MASTER-HA } vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等 script "/opt/chk_mysql.sh" #这里通过脚本监测 interval 2 #脚本执行间隔，每2s检测一次 weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5 fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间） rise 1 #检测1次成功就算成功。但不修改优先级 } vrrp_instance VI_1 { state BACKUP nopreempt interface ens32 #指定虚拟ip的网卡接口 ip addr查询 virtual_router_id 151 #路由器标识，MASTER和BACKUP必须是一致的 priority 90 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 advert_int 2 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 10.9.13.61 } track_script { chk_mysql_port } } keepalive检测mysql脚本 #!/bin/bash MYSQL=/usr/local/mysql/bin/mysql MYSQL_HOST=10.9.13.62 MYSQL_USER=root MYSQL_PASSWORD=1qaz@WSX3edc CHECK_TIME=3 #mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0 MYSQL_OK=1 function check_mysql_helth (){ $MYSQL -h $MYSQL_HOST -P33060 -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1 if [ $? = 0 ] ;then MYSQL_OK=1 else MYSQL_OK=0 fi return $MYSQL_OK } while [ $CHECK_TIME -ne 0 ] do let "CHECK_TIME -= 1" check_mysql_helth if [ $MYSQL_OK = 1 ] ; then CHECK_TIME=0 exit 0 fi if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ] then pkill keepalived exit 1 fi done l
最后
以上就是勤劳裙子最近收集整理的关于mysql集群双活+高可用的全部内容，更多相关mysql集群双活+高可用内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复