本文主要是介绍MySQL双主搭建+keepalived高可用的实现，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,...
一、测试环境准备
| 节点1 | 节点2 | |
|---|---|---|
| IP地址 | 192.168.101.77 | 192.168.101.79 |
| mysql版本 | 8.0.32 | 8.0.32 |
二、主从搭建
1.创建复制用户
节点1执行：
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123123'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; mysql> FLUSH PRIVILEGES; mysql> show master status;
2.创建复制关系
节点2执行
mysql> change master to master_host='192.168.101.77', MASTER_PORT=3306, master_user='repl', master_password='123123' , MASTER_AUTO_POSITION=1;
3.开启复制，确认复制是否成功
开启复制
mysql> start slave;
查看复制状态（下面是正常状态）
root@localhost:(none)>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.101.77
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 197
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 413
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 197
Relay_Log_Space: 2098
Until_Condition: None
Until_Log_File:
Untjsil_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f6ca767b-2144-11ee-80e4-080027a4fdbc
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:4-8
Executed_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
可在节点1插入数据，然后在节点2查询数据是否同步，确定同步是否正常。4.同步排错
报错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.
解决方法：
报错2：
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''
解决方法：
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 @@GLGZyIUHLOBAL.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.创建复制用户
mysql> CREATE USER 'repl1'@'%' IDENTIFIED BY '123123'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'%'; mysql> FLUSH PRIVILEGES; mysql> show master status;
2.创建复制关系
mysql> change master to master_host='192.168.101.79', MASTER_PORT=3306, master_user='repl', master_password='123123' , MASTER_AUTO_POSITION=1;
3.开启复制，确认复制是否成功
mysql> start slave; androidmysql>show slave status \G;
4.双主测试
节点1插入数据，节点2查询
节点2插入数据，节点1查询
四、keepalived配置
1.keepalived安装、启停操作
yum -y install keepalived systemctl status keepalived systemctl start keepalived systemctl stop keepalived
2.keepalived配置文件
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
router_id MYCAT_HA # 定义名称
}
vrrp_script chk_mycat_alive {
script "/etc/keepalived/mysql_check.sh" # 返回状态码为0表示正常,检测脚本为true;返回状态码非0表示异常,检测脚本为false
interval 2 # 检测脚本执行的间隔，单位是秒
weight 20
}
vrrp_instance VI_1 {
state MASTER #主节点为MASTER，备节点为BACKUP
interface enp0s3 #根据实际网卡名配置
virtual_router_id 88
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass qwert
}
track_script {
chk_mycat_alive # 调用检测脚本
}
virtual_ipaddress {
192.168.101.199/24 # 定义虚拟ip(VIP)
}
}
3.keepalived配置文件
服务状态检查脚本
cat /etc/keepalived/mysql_check.sh
#!/bin/bash
count=`ps -C mysqld --no-heading|wc -l`
time=$(date "+%Y-%m-%d %H:%M:%S")
if [ $count = 0 ]; then
echo "$time : count=$count, mysql is not running..." >> /var/log/keepalived_check.log
exit 1 # 返回1说明脚本非正常执行,mysql不在运行中
else
echo "$time : count=$count, mysql is running..." >> /var/log/keepalived_check.log
exit 0 # 返回0说明脚本正常执行,mysql正在运行中
fi
4.keepalived配置成功
配置完成后可在其中一节点上看到虚拟地址，同网段也可以ping通虚拟地址，同时也可以通过虚拟地址连接上数据库。
五、集群状态测试
两节点物理地址均可连接数据库，也可通过虚拟地址连接数据库，下面我们测试一下关闭一台服务器（虚拟地址所服务器）测试是否还是可通过虚拟地址连接数据库。
节点2关机后虚拟地址跑到节点1上去了，测试用虚拟地址连接数据库依然可以连接
重新启动节点2后，启动数据库即可拉起双主集群
到此这篇关于MySQL双主搭建+keepalived高可用的实现的文章就介绍到这了,更多相关MySQL双主搭建+keepalived高可用内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！
这篇关于MySQL双主搭建+keepalived高可用的实现的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
