keepalived+HAProxy+MySQL双主实验

keepalived+HAProxy+MySQL双主实验

• 环境准备

node1(HAProxy1):192.168.184.10
node2(HAProxy2):192.168.184.20
node3(MySQL1):192.168.184.30
node4(MySQL2):192.168.184.40
虚拟IP vip：192.168.184.100
1
2
3
4
5

• MySQL部署

在node3执行以下脚本：
#!/bin/bash
systemctl stop firewalld
setenforce 0
yum install mariadb-server -y
sed -i '/^\[mysqld\]$/a\binlog-ignore = information_schema' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\binlog-ignore = mysql' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\skip-name-resolve' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto-increment-increment = 1' /etc/my.cnf.d/server.cnf # 注意node4节点上必须不同
sed -i '/^\[mysqld\]$/a\log-bin = mysql-bin' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto_increment_offset = 1' /etc/my.cnf.d/server.cnf # 注意node4节点上必须不同
sed -i '/^\[mysqld\]$/a\server-id = 1' /etc/my.cnf.d/server.cnf # 注意node4节点上必须不同
systemctl restart mariadb
mysql -uroot -e "grant replication slave on *.* to repuser@'192.168.184.30' identified by '000000';"
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在node4执行以下脚本：
#!/bin/bash
systemctl stop firewalld
setenforce 0
yum install mariadb-server -y
sed -i '/^\[mysqld\]$/a\binlog-ignore = information_schema' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\binlog-ignore = mysql' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\skip-name-resolve' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto-increment-increment = 2' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\log-bin = mysql-bin' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto_increment_offset = 2' /etc/my.cnf.d/server.cnf 
sed -i '/^\[mysqld\]$/a\server-id = 2' /etc/my.cnf.d/server.cnf 
systemctl restart mariadb
mysql -uroot -e "grant replication slave on *.* to repuser@'192.168.184.30' identified by '000000';"
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询node3节点master状态：
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
|       File       | Position | Binlog_Do_DB |      Binlog_Ignore_DB    |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000001 |   401    |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1
2
3
4
5
6
7

查询node4节点master状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
|       File       | Position | Binlog_Do_DB |     Binlog_Ignore_DB     |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000001 |    245   |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1
2
3
4
5
6
7

在node3节点执行连接命令：
MariaDB [(none)]> change master to master_host="192.168.184.40",master_port=3306,master_user="repuser",master_password="000000",master_log_file="mysql-bin.000001",master_log_pos=245;
MariaDB [mysql]> start slave;
1
2
3

在node4节点执行连接命令：
MariaDB [(none)]> change master to master_host="192.168.184.30",master_port=3306,master_user="repuser",master_password="000000",master_log_file="mysql-bin.000001",master_log_pos=401;
MariaDB [mysql]> start slave;
1
2
3

查看从节点状态： show slave status \G; 观察IO和SQL线程是否为YES
MariaDB [(none)]> show slave status \G;
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
1
2
3
4

测试：
1.在node3上创建db1数据库，在node4上查看是否有db1
2.在node4上创建db2数据库，在node3上查看是否有db2
3.最终要实现node3和node4上保持数据同步
1
2
3
4

• HAProxy部署

在node1和node2上执行以下脚本：
#!/bin/bash
yum install haproxy ‐y
mv /etc/haproxy/haproxy.cfg{,.bak}
cat > /etc/haproxy/haproxy.cfg << EOF
global
	log 127.0.0.1 local2
	chroot /var/lib/haproxy
	pidfile /var/run/haproxy.pid
	maxconn 4000
	user haproxy
	group haproxy
	daemon
	stats socket /var/lib/haproxy/stats
listen mysql_proxy
	bind 0.0.0.0:3306
	mode tcp
	balance source
	server mysqldb1 192.168.184.30:3306 weight 1 check
	server mysqldb2 192.168.184.40:3306 weight 2 check
listen stats
	mode http
	bind 0.0.0.0:8080
	stats enable
	stats uri /dbs
	stats realm haproxy\ statistics
	stats auth admin:admin
EOF
systemctl start haproxy
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

• keepalived部署

node1上执行以下脚本：
#!/bin/bash
yum install keepalived ‐y
mv /etc/keepalived/keepalived.conf{,.bak}
cat > /etc/keepalived/keepalived.conf << EOF
! Configuration File for keepalived
global_defs {
	router_id node1
}
vrrp_script chk_http_port {
	script "/usr/local/src/check_proxy_pid.sh"
	interval 1
	weight ‐2
}
vrrp_instance VI_1 {
	state MASTER
	interface ens33
	virtual_router_id 10
	priority 100
	advert_int 1
	authentication {
	auth_type PASS
	auth_pass 1111
	}
	track_script {
		chk_http_port
	}
	virtual_ipaddress {
		192.168.184.100
	}
}
EOF
systemctl start keepalived
node2上执行以下脚本：
#!/bin/bash
yum install keepalived ‐y
mv /etc/keepalived/keepalived.conf{,.bak}
cat > /etc/keepalived/keepalived.conf << EOF
! Configuration File for keepalived
global_defs {
	router_id node2
}
vrrp_instance VI_1 {
	state MASTER
	interface ens33
	virtual_router_id 10
	priority 99
	advert_int 1
	authentication {
		auth_type PASS
		auth_pass 1111
	}
	virtual_ipaddress {
		192.168.10.100
	}
}
EOF
systemctl start keepalived
[root@node1 src]# cat check_proxy_pid.sh
#!/bin/bash
A=`ps -C haproxy --no-header | wc -l`
if [ $A -eq 0 ];then
exit 1
else
exit 0
fi
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