lvs+keepalived+mysql (完整版)
程序员文章站
2022-06-24 11:45:50
### MySQL主主复制+LVS+Keepalived实现MySQL高可用性架构图[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1pndFFSU-1594210072321)(assets/43136-20170124163407034-588511483.png)]1.环境192.168.183.100 VIP192.168.183.188 txc-lvs01（LVS01,keepalived）192.168.183.189 txc-lvs02（LVS...
### MySQL主主复制+LVS+Keepalived实现MySQL高可用性
架构图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1pndFFSU-1594210072321)(assets/43136-20170124163407034-588511483.png)]
1.环境
- 192.168.183.100 VIP
- 192.168.183.188 txc-lvs01（LVS01,keepalived）
- 192.168.183.189 txc-lvs02（LVS02,keepalived）
- 192.168.183.190 txc-mysql01（MySQL 主）
- 192.168.183.191 txc-mysql02（MySQL 主）
2.配置
主机名
[root@txc-lvs01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.183.188 txc-lvs01
192.168.183.189 txc-lvs02
192.168.183.190 txc-mysql01
192.168.183.191 txc-mysql02
[root@txc-lvs01 ~]# scp /etc/hosts 192.168.183.189:/etc
[root@txc-lvs01 ~]# scp /etc/hosts 192.168.183.190:/etc
[root@txc-lvs01 ~]# scp /etc/hosts 192.168.183.191:/etc
keepalived配置
# keepalived MASTER配置（txc-lvs01）
[root@txc-lvs01 ~]# yum -y install keepalived
[root@txc-lvs01 ~]# cd /etc/keepalived/
[root@txc-lvs01 keepalived]# mv keepalived.conf{,.bak}
[root@txc-lvs01 keepalived]# vim keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_1
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.183.100/24
}
}
virtual_server 192.168.183.100 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
nat_mask 255.255.255.0
# persistence_timeout 50
protocol TCP
real_server 192.168.183.190 3306 {
weight 1
TCP_CHECK {
connect_timeout 8
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 192.168.183.191 3306 {
weight 1
TCP_CHECK {
connect_timeout 8
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
[root@txc-lvs01 keepalived]# systemctl start keepalived.service
[root@txc-lvs01 keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:2f:a1:1e brd ff:ff:ff:ff:ff:ff
inet 192.168.183.188/24 brd 192.168.183.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.183.100/24 scope global secondary ens33
valid_lft forever preferred_lft forever
inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
# keepalived BACKUP配置（txc-lvs02）
[root@txc-lvs02 ~]# yum -y install keepalived
[root@txc-lvs02 ~]# cd /etc/keepalived/
[root@txc-lvs02 keepalived]# mv keepalived.conf{,.bak}
[root@txc-lvs02 keepalived]# vim keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_2
}
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.183.100/24
}
}
virtual_server 192.168.183.100 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
nat_mask 255.255.255.0
# persistence_timeout 50
protocol TCP
real_server 192.168.183.190 3306 {
weight 1
TCP_CHECK {
connect_timeout 8
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 192.168.183.191 3306 {
weight 1
TCP_CHECK {
connect_timeout 8
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
[root@txc-lvs02 keepalived]# systemctl start keepalived.service
[root@txc-lvs02 keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:eb:79:a1 brd ff:ff:ff:ff:ff:ff
inet 192.168.183.189/24 brd 192.168.183.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link noprefixroute
valid_lft forever preferred_lft forever
lvs配置
# txc-lvs01配置
[root@txc-lvs01 ~]# yum -y install ipvsadm
[root@txc-lvs01 ~]# ipvsadm -C
[root@txc-lvs01 ~]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 192.168.183.100:3306 rr
-> 192.168.183.190:3306 Route 1 0 0
-> 192.168.183.191:3306 Route 1 0 0
# txc-lvs02配置
[root@txc-lvs02 ~]# yum -y install ipvsadm
[root@txc-lvs02 ~]# ipvsadm -C
[root@txc-lvs02 ~]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
mysql配置
# 配置txc-mysql01作为txc-mysql02的master
# txc-mysql01配置
[root@txc-mysql01 ~]# rz
[root@txc-mysql01 ~]# ls
anaconda-ks.cfg mysql-community-libs-5.7.30-1.el7.x86_64.rpm
mysql-community-client-5.7.30-1.el7.x86_64.rpm mysql-community-server-5.7.30-1.el7.x86_64.rpm
mysql-community-common-5.7.30-1.el7.x86_64.rpm
[root@txc-mysql01 ~]# yum -y remove mariadb-libs.x86_64
[root@txc-mysql01 ~]# yum -y install mysql-community*
[root@txc-mysql01 ~]# systemctl enable mysqld --now
[root@txc-mysql01 ~]# grep password /var/log/mysqld.log
[root@txc-mysql01 ~]# mysqladmin -uroot -p'&o8wGopht1lr' password 'Qfedu.123com'
[root@txc-mysql01 ~]# cp /etc/my.cnf{,.bak}
[root@txc-mysql01 ~]# vim /etc/my.cnf
# 添加以下内容
# GTID
server_id=1
gtid_mode=on
enforce_gtid_consistency=on
# binlog
log_bin=mysql-bin
log-slave-updates=1
binlog_format=row
sync-master-info=1
sync_binlog=1
# relay log
skip_slave_start=1
[root@txc-mysql01 ~]# systemctl restart mysqld
[root@txc-mysql01 ~]# mysql -p'Qfedu.123com'
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
# 授权slave复制用户并刷新权限
mysql> grant replication slave,replication client on *.* to slave@'192.168.183.%' identified by 'Qfedu.123com';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 再次查看master状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 622
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
1 row in set (0.00 sec)
# txc-mysql02配置
[root@txc-mysql02 ~]# rz
[root@txc-mysql02 ~]# ls
anaconda-ks.cfg mysql-community-libs-5.7.30-1.el7.x86_64.rpm
mysql-community-client-5.7.30-1.el7.x86_64.rpm mysql-community-server-5.7.30-1.el7.x86_64.rpm
mysql-community-common-5.7.30-1.el7.x86_64.rpm
[root@txc-mysql02 ~]# yum -y remove mariadb-libs.x86_64
[root@txc-mysql02 ~]# yum -y install mysql-community*
[root@txc-mysql02 ~]# systemctl enable mysqld --now
[root@txc-mysql02 ~]# grep password /var/log/mysqld.log
[root@txc-mysql02 ~]# mysqladmin -uroot -p'!K/qqg,rf1!o' password 'Qfedu.123com'
[root@txc-mysql02 ~]# cp /etc/my.cnf{,.bak}
[root@txc-mysql02 ~]# vim /etc/my.cnf
# 添加以下内容
# GTID
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
# binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
# relay log
skip_slave_start = 1
read_only = on
[root@txc-mysql02 ~]# systemctl restart mysqld
# 登录mysql，配置主从复制
[root@txc-mysql02 ~]# mysql -p'Qfedu.123com'
mysql> change master to master_host='192.168.183.190',master_user='slave',master_password='Qfedu.123com',master_auto_position=1;
# 启动从服务，查看状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.183.190
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 622
Relay_Log_File: mysql2-relay-bin.000002
Relay_Log_Pos: 835
Relay_Master_Log_File: mysql-bin.000001
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: 622
Relay_Log_Space: 1043
Until_Condition: None
Until_Log_File:
Until_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: 916d153c-b104-11ea-b5a3-000c29dd7734
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
Executed_Gtid_Set: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
# 配置txc-mysql02作为txc-mysql01的master
[root@txc-mysql02 ~]# mysql -p'Qfedu.123com'
mysql> grant replication slave,replication client on *.* to slave@'192.168.183.%' identified by 'Qfedu.123com';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@txc-mysql01 ~]# mysql -p'Qfedu.123com'
mysql> change master to master_host='192.168.183.191',master_user='slave',master_password='Qfedu.123com',master_auto_position=1;
# 启动从服务，查看状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.183.191
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 622
Relay_Log_File: mysql1-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
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: 622
Relay_Log_Space: 622
Until_Condition: None
Until_Log_File:
Until_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: 2
Master_UUID: 905d0d99-b104-11ea-b5b0-000c2944ae12
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
Executed_Gtid_Set: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
3.验证
# 为两个数据库配置vip
[root@txc-mysql01 ~]# ifconfig ens33:0 192.168.183.100 netmask 255.255.255.255
[root@txc-mysql02 ~]# ifconfig ens33:0 192.168.183.100 netmask 255.255.255.255
# 使用vip在客户端上（不可以是keepalived的MASTER）远程登录数据库
[root@txc-lvs02 ~]# mysql -u 'slave' -p'Qfedu.123com' -h 192.168.183.100
# mysql负载均衡验证：停掉txc-mysql01的数据库，再次用vip远程登录
[root@txc-mysql01 ~]# systemctl stop mysqld.service
[root@txc-lvs02 ~]# mysql -u 'slave' -p'Qfedu.123com' -h 192.168.183.100
# lvs高可用验证：停掉txc-lvs01，vip自动飘到txc-lvs02上
[root@txc-lvs01 ~]# systemctl stop keepalived
[root@txc-lvs02 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:eb:79:a1 brd ff:ff:ff:ff:ff:ff
inet 192.168.183.189/24 brd 192.168.183.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.183.100/24 scope global secondary ens33
valid_lft forever preferred_lft forever
inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link noprefixroute
valid_lft forever preferred_lft forever
# 再次使用vip远程登录数据库
[root@txc-lvs01 ~]# mysql -uslave -p'Qfedu.123com' -h 192.168.183.100
inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link noprefixroute
valid_lft forever preferred_lft forever
再次使用vip远程登录数据库
[root@txc-lvs01 ~]# mysql -uslave -p’Qfedu.123com’ -h 192.168.183.100
本文地址：https://blog.csdn.net/qq_45897484/article/details/107214130
上一篇： 什么是客户流失以及如何防止？
下一篇： Android本地验证码的生成代码
推荐阅读
-
iOS开发之App主题切换解决方案完整版(Swift版)
-
2021年全国985和211学校名单一览表（最全完整版）
-
2021年国内大学排名100强名单-中国大学排名前100名（最新完整版）
-
2021年文科重点大学排名完整版-全国所有文科大学排名最新（权威榜单）
-
湖南民办大专学校最新完整版名单汇总-长沙有哪些大专学校比较容易进？
-
军工二级保密单位名单（院校）-国家二级保密大学名单最新完整版（含录取分数线）
-
2021软科中国高校实力排名-中国最好大学2021排名软科完整版
-
IntelliJ IDEA配置Tomcat（完整版图文教程）
-
java仿windows记事本功能完整版
-
2021年全国985和211学校名单一览表（最全完整版）