MySQL双主搭建+keepalived高可用的实现！
MySQL双主搭建+keepalived高可用的实现！
作者：zongzizz
本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。
一、测试环境准备
| 节点1 | 节点2 | |
|---|---|---|
| IP地址 | 192.168.101.77 | 192.168.101.79 |
| MySQL版本 | 8.0.32 | 8.0.32 |
二、主从搭建
1.创建复制用户
节点1执行：
|
1
2
3
4
|
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123123';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';mysql> FLUSH PRIVILEGES;mysql> show master status; |
2.创建复制关系
节点2执行
|
1
2
3
4
5
6
7
|
mysql> change master tomaster_host='192.168.101.77', MASTER_PORT=3306, master_user='repl', master_password='123123' , MASTER_AUTO_POSITION=1; |
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
|
开启复制mysql> start slave;查看复制状态（下面是正常状态）root@localhost:(none)>show slave status \G*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.101.77Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 197Relay_Log_File: localhost-relay-bin.000004Relay_Log_Pos: 413Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 197Relay_Log_Space: 2098Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: f6ca767b-2144-11ee-80e4-080027a4fdbcMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:4-8Executed_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:1-8Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.00 sec)可在节点1插入数据，然后在节点2查询数据是否同步，确定同步是否正常。 |
4.同步排错
报错1：
|
1
|
Last_IO_Error: error connecting to master 'repl@192.168.101.77:3306' - retry-time: 60 retries: 21 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. |
解决方法：
|
1
|
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123123'; |
报错2：
|
1
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '75433121-6a95-11ef-8b71-080027123ed0:1-3', and the missing transactions are 'f6ca767b-2144-11ee-80e4-080027a4fdbc:1-3'' |
解决方法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
root@localhost:(none)>stop slave;Query OK, 0 rows affected, 1 warning (0.11 sec)root@localhost:(none)>reset master;Query OK, 0 rows affected (0.10 sec)root@localhost:(none)>reset slave;Query OK, 0 rows affected, 1 warning (0.20 sec)root@localhost:(none)>set @@GLOBAL.GTID_PURGED='f6ca767b-2144-11ee-80e4-080027a4fdbc:1-3';Query OK, 0 rows affected (0.06 sec)root@localhost:(none)>start slave;Query OK, 0 rows affected, 1 warning (0.10 sec) |
三、双主配置
双主配置也就是将上面的主从操作反向操作一遍。
1.创建复制用户
|
1
2
3
4
|
mysql> CREATE USER 'repl1'@'%' IDENTIFIED BY '123123';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'%';mysql> FLUSH PRIVILEGES;mysql> show master status; |
2.创建复制关系
|
1
2
3
4
5
6
7
|
mysql>change master tomaster_host='192.168.101.79',MASTER_PORT=3306,master_user='repl',master_password='123123' ,MASTER_AUTO_POSITION=1; |
3.开启复制，确认复制是否成功
|
1
2
|
mysql> start slave;mysql>show slave status \G; |
4.双主测试
节点1插入数据，节点2查询
节点2插入数据，节点1查询
四、keepalived配置
1.keepalived安装、启停操作
|
1
2
3
4
|
yum -y install keepalivedsystemctl status keepalivedsystemctl start keepalivedsystemctl stop keepalived |
2.keepalived配置文件
|
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
|
vi /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locrouter_id MYCAT_HA # 定义名称}vrrp_script chk_mycat_alive {script "/etc/keepalived/mysql_check.sh" # 返回状态码为0表示正常,检测脚本为true;返回状态码非0表示异常,检测脚本为falseinterval 2 # 检测脚本执行的间隔，单位是秒weight 20}vrrp_instance VI_1 {state MASTER #主节点为MASTER，备节点为BACKUPinterface enp0s3 #根据实际网卡名配置virtual_router_id 88priority 100advert_int 1authentication {auth_type PASSauth_pass qwert}track_script {chk_mycat_alive # 调用检测脚本}virtual_ipaddress {192.168.101.199/24 # 定义虚拟ip(VIP)}} |
3.keepalived配置文件
服务状态检查脚本
|
1
2
3
4
5
6
7
8
9
10
11
|
cat /etc/keepalived/mysql_check.sh#!/bin/bashcount=`ps -C mysqld --no-heading|wc -l`time=$(date "+%Y-%m-%d %H:%M:%S")if [ $count = 0 ]; thenecho "$time : count=$count, mysql is not running..." >> /var/log/keepalived_check.logexit 1 # 返回1说明脚本非正常执行,mysql不在运行中elseecho "$time : count=$count, mysql is running..." >> /var/log/keepalived_check.logexit 0 # 返回0说明脚本正常执行,mysql正在运行中fi |
4.keepalived配置成功
配置完成后可在其中一节点上看到虚拟地址，同网段也可以ping通虚拟地址，同时也可以通过虚拟地址连接上数据库。
五、集群状态测试
两节点物理地址均可连接数据库，也可通过虚拟地址连接数据库，下面我们测试一下关闭一台服务器（虚拟地址所服务器）测试是否还是可通过虚拟地址连接数据库。
节点2关机后虚拟地址跑到节点1上去了，测试用虚拟地址连接数据库依然可以连接
重新启动节点2后，启动数据库即可拉起双主集群。
到此这篇关于MySQL双主搭建+keepalived高可用的实现的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
你将获得：
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需129元，即可下载本站文章涉及的文件和软件。