转载自https://blog.csdn.net/weixin_43800781/article/details/86690840,并修改了原文上面的一些细节错误,感谢原作者!

1\集群概述
需解决的关键点有：
MySQL主从同步及半同步复制
SSH免密登录
MHA集群的搭建
Mycat分片服务
HAProxy负载均衡
Keepalived的高可用

软件版本,除了mysql需要自己下载,其余yum源都有,其实Mysql用mariadb也可以
mysql-5.7.XX
java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
haproxy.x86_64

2\准备跳板机

[root@jumper ~]

# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.51 mysql51
192.168.1.52 mysql52
192.168.1.53 mysql53
192.168.1.54 mysql54
192.168.1.55 mysql55
192.168.1.56 mhamanager
192.168.1.61 mycat61
192.168.1.62 mycat62
192.168.1.63 mycat63
192.168.1.21 haproxy1
192.168.1.22 haproxy2

[root@jumper ~]

#

在跳板机上制作yum源,主要包括mysql社区版相关软件\MHA-node\MHA-master需要的perl依赖包,HAPROXY和KEEPALIVED本身centos源就有,其余软件随意

[root@jumper default]

# ll /var/ftp/default/
总用量 556860
-rw-r–r– 1 root root 113914 3月 26 15:40 mha4mysql-manager-0.56.tar.gz
-rw-r–r– 1 root root 36326 3月 26 15:40 mha4mysql-node-0.56-0.el6.noarch.rpm
-rw-r–r– 1 7155 31415 25037548 11月 30 2016 mysql-community-client-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 277812 11月 30 2016 mysql-community-common-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 3774276 11月 30 2016 mysql-community-devel-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 45474744 11月 30 2016 mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 23925668 11月 30 2016 mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 126047424 11月 30 2016 mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 2237116 11月 30 2016 mysql-community-libs-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 2112724 11月 30 2016 mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 54571580 11月 30 2016 mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 169496800 11月 30 2016 mysql-community-server-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 7155 31415 116682212 11月 30 2016 mysql-community-test-5.7.17-1.el7.x86_64.rpm
-rw-r–r– 1 root root 25508 3月 26 15:39 perl-Config-Tiny-2.14-7.el7.noarch.rpm
-rw-r–r– 1 root root 17524 3月 26 15:39 perl-Email-Date-Format-1.002-15.el7.noarch.rpm
-rw-r–r– 1 root root 84300 3月 26 15:39 perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
-rw-r–r– 1 root root 60212 3月 26 15:39 perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
-rw-r–r– 1 root root 29460 3月 26 15:39 perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
-rw-r–r– 1 root root 98316 3月 26 15:39 perl-MIME-Lite-3.030-1.el7.noarch.rpm
-rw-r–r– 1 root root 39184 3月 26 15:39 perl-MIME-Types-1.38-2.el7.noarch.rpm
-rw-r–r– 1 root root 29128 3月 26 15:39 perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

别忘了生成repo相关信息.

[root@jumper default]

# createrepo –update .

安装ansible,做好主机列表

[mysql]

mysql51
mysql52
mysql53
mysql54
mysql55

mysql51 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
mysql52 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
mysql53 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
mysql54 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
mysql55 ansible_ssh_user=”root” ansible_ssh_pass=”123456″

[mha]

mhamanager

mhamanager ansible_ssh_user=”root” ansible_ssh_pass=”123456″

[mycat]

mycat61
mycat62
mycat63

mycat61 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
mycat62 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
mycat63 ansible_ssh_user=”root” ansible_ssh_pass=”123456″

[haproxy]

haproxy1
haproxy2

haproxy1 ansible_ssh_user=”root” ansible_ssh_pass=”123456″
haproxy2 ansible_ssh_user=”root” ansible_ssh_pass=”123456″

[jmp]

jumper

jumper ansible_ssh_user=”root” ansible_ssh_pass=”123456″

然后在51-56做好免密,注意MHA需要所有主机相互之间能够免密,其他主机的免密不是必须的,随意你怎么操作

[root@jumper ~]

# ansible mha -m copy -a ‘src=/root/.ssh/id_rsa dest=/root/.ssh/’

[root@jumper ~]

# ansible mha -m shell -a ‘chmod 0400 /root/.ssh/id_rsa’ //私钥的权限必须为这个，拷贝过去的私钥一般为0644，不能用

3\部署MHA
虚拟机的话,磁盘性能较差,这一步可能要花费半个小时以上,如果是SSD要好得多,中途不能中断,否则重来

[root@jumper ~]

# ansible mysql –list
hosts (5):
mysql51
mysql52
mysql53
mysql54
mysql55

[root@jumper ~]

# ansible mysql -m shell -a ‘yum -y install mysql-community-*’

启动mysql,确认启动成功

[root@jumper ~]

# ansible mysql -m shell -a ‘systemctl start mysqld’
mysql55 | SUCCESS | rc=0 >>

mysql53 | SUCCESS | rc=0 >>

mysql51 | SUCCESS | rc=0 >>

mysql52 | SUCCESS | rc=0 >>

mysql54 | SUCCESS | rc=0 >>

[root@jumper ~]

# ansible mysql -m shell -a ‘ss -antup | grep 3306’
mysql54 | SUCCESS | rc=0 >>
tcp LISTEN 0 80 :::3306 :::* users:((“mysqld”,pid=13935,fd=18))

mysql52 | SUCCESS | rc=0 >>
tcp LISTEN 0 80 :::3306 :::* users:((“mysqld”,pid=14143,fd=20))

mysql51 | SUCCESS | rc=0 >>
tcp LISTEN 0 80 :::3306 :::* users:((“mysqld”,pid=14579,fd=35))

mysql53 | SUCCESS | rc=0 >>
tcp LISTEN 0 80 :::3306 :::* users:((“mysqld”,pid=13941,fd=20))

mysql55 | SUCCESS | rc=0 >>
tcp LISTEN 0 80 :::3306 :::* users:((“mysqld”,pid=14041,fd=18))

查看并记住临时密码

[root@jumper ~]

# ansible mysql -m shell -a ‘grep “temporary password” /var/log/mysqld.log’
mysql52 | SUCCESS | rc=0 >>
2019-03-26T07:24:47.975583Z 1 [Note] A temporary password is generated for root@localhost: UDf=dwfHO7YP

mysql53 | SUCCESS | rc=0 >>
2019-03-26T07:24:47.747827Z 1 [Note] A temporary password is generated for root@localhost: DtLaOMTE5H:9

mysql55 | SUCCESS | rc=0 >>
2019-03-26T07:24:48.382207Z 1 [Note] A temporary password is generated for root@localhost: OjBi.agtP9kU

mysql54 | SUCCESS | rc=0 >>
2019-03-26T07:24:48.547623Z 1 [Note] A temporary password is generated for root@localhost: b5zddVUhw>Hh

mysql51 | SUCCESS | rc=0 >>
2019-03-26T07:24:48.452091Z 1 [Note] A temporary password is generated for root@localhost: q%

登陆到mysql51上面的数据库,修改root的密码为 123456

[root@mysql51 ~]

# mysql -uroot -p
Enter password:
mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘123aaa_BBB’);
mysql> show global variables like “%password%”；
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=6;
mysql> alter user root@localhost identified by “123456”;
mysql> flush privileges;

回到跳板机,装下rsync,刚才忘了
ansible mysql -m shell -a ‘grep “yum install -y rsync’

再回到51数据库,把库文件同步到其他的数据库服务器

[root@mysql51 ~]

# for i in 192.168.1.{52..55} //同步的时候看一下其他主机下的/var/lib/mysql/auto.cnf 这个是放UUID的地方，一样需要改动或者删除

do
rsync -aSH –delete /var/lib/mysql/ root@$i:/var/lib/mysql/
done
注意,同步之后,所有mysql的UUID都是一样的,这样在启动mysql主从时会报错,需更改uuid或者直接删除auto.cnf.

回到跳板机,重启所有mysql数据库服务,并确认服务已启动,同时登陆验证一下

[root@jumper ~]

# ansible mysql -m shell -a ‘systemctl restart mysqld’

[root@jumper ~]

# ansible mysql -m shell -a ‘systemctl status mysqld’

[root@jumper ~]

# ansible mysql -m shell -a ‘ss -antup | grep 3306’

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.53
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]>

现在配置MHA集群,忘了的话,自己去查资料.

在所有节点安装mha4node,记得要先安装perl依赖

[root@jumper ~]

# ansible mha -m shell -a ‘yum -y install perl-‘

[root@jumper ~]

# ansible mha -m shell -a ‘yum -y install mha4’

到MHA56主节点,配置mha-manager

[root@jumper ~]

# scp mha4mysql-manager-0.56.tar.gz app1.cnf master_ip_failover [email protected]:/root

[root@mhamanager mha4mysql-manager-0.56]

# perl Makefile.PL

[root@mhamanager mha4mysql-manager-0.56]

# make

[root@mhamanager mha4mysql-manager-0.56]

# make install
…
[Core Features]

• DBI …loaded. (1.627)
• DBD::mysql …loaded. (4.023)
• Time::HiRes …loaded. (1.9725)
• Config::Tiny …loaded. (2.14)
• Log::Dispatch …loaded. (2.41)
• Parallel::ForkManager …loaded. (1.18)
• MHA::NodeConst …loaded. (0.56)
  *** Module::AutoInstall configuration finished.

#

到主数据库51上面,修改配置文件my.cnf,创建用户,并做相应授权

[root@mysql51 ~]

# vim /etc/my.cnf

[mysqld]

…
lower_case_table_names=1
validate_password_policy=0
validate_password_length=6
plugin-load=”rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
server_id=51
log_bin=master51
binlog_format=”mixed”
relay_log_purge=off
…

[root@mysql51 ~]

# systemctl restart mysqld

[root@mysql51 ~]

# mysql -uroot -p123456
mysql> show master status;
+—————–+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————–+———-+————–+——————+——————-+
| master51.000001 | 154 | | | |
+—————–+———-+————–+——————+——————-+
mysql> grant all on . to root@”%” identified by “123456”;
在51的主机上做root的授权，其他的会同步（如果不做，在验证数据节点的主从同步配置时会出错）
mysql> grant replication slave,replication client on . to repluser@”%” identified by “123456”;

配置备用主52和53,很简单,配置文件同步过去,改下server_id就行了

[root@mysql51 ~]

# rsync -aSH –delete /etc/my.cnf 192.168.1.52:/etc/ //同步到52和53上

[root@mysql51 ~]

# ssh 192.168.1.52

[root@mysql52 ~]

# vim /etc/my.cnf
…
server_id=52
log_bin=master52
…

[root@mysql52 ~]

# mysql -uroot -p123456
mysql> change master to
-> master_host=”192.168.1.51″,
-> master_user=”repluser”,
-> master_password=”123456″,
-> master_log_file=”master51.000001″,
-> master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
…
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select user,host from mysql.user;
+———–+———–+
| user | host |
+———–+———–+
| admin | % |
| repluser | % |
| root | % |
| mysql.sys | localhost |
| root | localhost |
+———–+———–+
5 rows in set (0.01 sec)

mysql>

配置从节点slave 54和55（配置一样，改动id就可以）,从库很简单,指下主就行了

[root@mysql54 ~]

# vim /etc/my.cnf

[mysqld]

server_id=54

[root@mysql54 ~]

# systemctl restart mysqld
mysql> change master to master_host=”192.168.1.51″,master_user=”repluser”,master_password=”123456″,master_log_file=”master51.000003″,master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
…
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…

配置mha56主节点

[root@jumper ~]

# ssh mhamanager

[root@mhamanager ~]

# cd mha4mysql-manager-0.56

[root@mhamanager mha4mysql-manager-0.56]

# cp bin/* /usr/local/bin/

[root@mhamanager mha4mysql-manager-0.56]

# mkdir /etc/mha_manager //创建工作目录

[root@mhamanager mha4mysql-manager-0.56]

# cp samples/conf/app1.cnf /etc/mha_manager/

[root@mhamanager ~]

# cat /etc/mha_manager/app1.cnf

[server default]

manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover

ssh_user=root
ssh_port=22
repl_user=repluser
repl_password=123456
user=root
password=123456

[server1]

hostname=192.168.1.51
port=3306

[server2]

hostname=192.168.1.52
port=3306
candidate_master=1

[server3]

hostname=192.168.1.53
port=3306
candidate_master=1

[server4]

hostname=192.168.1.54
no_master=1

[server5]

hostname=192.168.1.55
no_master=1

[root@mhamanager ~]

#

验证SSH

[root@mhamanager ~]

# masterha_check_ssh –conf=/etc/mha_manager/app1.cnf
Wed Mar 27 12:50:35 2019 – [info] All SSH connection tests passed successfully.

验证主从同步配置,注意要先把切换脚本注释,否则报错

[root@mhamanager ~]

# vim /etc/mha_manager/app1.cnf
…

master_ip_failover_script=/usr/local/bin/master_ip_failover //添加注释测试

…

[root@mhamanager bin]

# masterha_check_repl –conf=/etc/mha_manager/app1.cnf
Wed Mar 27 12:54:24 2019 – [info] Checking replication health on 192.168.1.52..
Wed Mar 27 12:54:24 2019 – [info] ok.
Wed Mar 27 12:54:24 2019 – [info] Checking replication health on 192.168.1.53..
Wed Mar 27 12:54:24 2019 – [info] ok.
Wed Mar 27 12:54:24 2019 – [info] Checking replication health on 192.168.1.54..
Wed Mar 27 12:54:24 2019 – [info] ok.
Wed Mar 27 12:54:24 2019 – [info] Checking replication health on 192.168.1.55..
Wed Mar 27 12:54:24 2019 – [info] ok.
Wed Mar 27 12:54:24 2019 – [info] Checking master_ip_failover_script status:
Wed Mar 27 12:54:24 2019 – [info] /usr/local/bin/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.1.51 –orig_master_ip=192.168.1.51 –orig_master_port=3306
Wed Mar 27 12:54:24 2019 – [info] OK.
Wed Mar 27 12:54:24 2019 – [warning] shutdown_script is not defined.
Wed Mar 27 12:54:24 2019 – [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

启动MHA集群

[root@mhamanager ~]

# masterha_manager –conf=/etc/mha_manager/app1.cnf –remove_dead_master_conf –ignore_last_failover
Wed Mar 27 12:53:54 2019 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 27 12:53:54 2019 – [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Wed Mar 27 12:53:54 2019 – [info] Reading server configuration from /etc/mha_manager/app1.cnf..

重新开个终端验证集群状态

[root@mhamanager ~]

# masterha_check_status –conf=/etc/mha_manager/app1.cnf
app1 (pid:19203) is running(0:PING_OK), master:192.168.1.51

现在,更改vip脚本,把vip添加到mhamanager上面去

[root@mhamanager bin]

# vim master_ip_failover
…
34
35 my $vip = ‘192.168.1.150/24’; # Virtual IP
36 my $key = “1”;
37 my $ssh_start_vip = “/sbin/ifconfig eth0:$key $vip”;
38 my $ssh_stop_vip = “/sbin/ifconfig eth0:$key down”;
39
…
65 &stop_vip(); //添加
…
95 FIXME_xxx_create_user( $new_master_handler->{dbh} ); //删除此行
…
99 FIXME_xxx; //删除行
99 &start_vip(); //添加
120 sub start_vip() {
121 ssh $ssh_user\@$new_master_host \" $ssh_start_vip \";
122 }
123 sub stop_vip() {
124 return 0 unless ($ssh_user);
125 ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \";
126 }
…

[root@mhamanager bin]

# vim /etc/mha_manager/app1.cnf

[server default]

…
master_ip_failover_script=/usr/local/bin/master_ip_failover //去掉注释，添加自动failover脚本

[root@mhamanager bin]

# chmod 755 /etc/mha_manager/app1.cnf

临时在51上面配置vip

[root@mysql51 ~]

# ifconfig eth0:1 192.168.1.150/24 //临时配置vip在51上

[root@mysql51 ~]

# ifconfig eth0:1
eth0:1: flags=4163 mtu 1500
inet 192.168.1.150 netmask 255.255.255.0 broadcast 192.168.1.255
ether 52:54:00:32:4d:4b txqueuelen 1000 (Ethernet)

再次启动mha集群

[root@mhamanager ~]

# masterha_manager –conf=/etc/mha_manager/app1.cnf –remove_dead_master_conf –ignore_last_failover
Wed Mar 27 12:53:54 2019 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 27 12:53:54 2019 – [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Wed Mar 27 12:53:54 2019 – [info] Reading server configuration from /etc/mha_manager/app1.cnf..

到此,MHA配置完成

4\配置mycat集群
配置这个集群的目的是实现读写分离
在主库51上创建一个用于查询的用户

mysql> grant select,insert on . to admin@”%” identified by “123456”;
 编辑ansible文件hosts

[root@jumper ~]

# ansible mycat –list
hosts (3):
mycat61
mycat62
mycat63
mycat服务器上安装 java-1.8.0-openjdk-devel

[root@jumper ~]

# ansible mycat -m shell -a ‘yum -y install java-1.8.0-openjdk-devel’

安装mycat

[root@jumper ~]

# for i in 192.168.1.{61..63}

do
scp Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz $i:/root
done

[root@jumper ~]

# ansible mycat -m shell -a ‘tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz’

[root@jumper ~]

# ansible mycat -m shell -a ‘mv mycat/ /usr/local/’

修改mycat61的配置文件server.xml

[root@mycat61 ~]

# cd /usr/local/mycat/

[root@mycat61 mycat]

# cd conf/

[root@mycat61 conf]

# vim server.xml

123456
TESTDB
…

123456
TESTDB
true
…

修改mycat61的配置文件schema.xml,同时别忘了去51添加对应的db1库,我懒得贴代码了

[root@mycat61 ~]

# cat /usr/local/mycat/conf/schema.xml

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.1.150:3306" user="root"
                               password="123456">
                    <!-- can have multi read hosts -->
                    <readHost host="hostS4" url="192.168.1.54:3306" user="root" password="123456" />
                    <readHost host="hostS5" url="192.168.1.55:3306" user="root" password="123456" />
            </writeHost>
    </dataHost>

[root@mycat61 ~]

#

启动mycat

[root@mycat61 conf]

# /usr/local/mycat/bin/mycat start
Starting Mycat-server…

[root@mycat61 conf]

# ss -pntul | grep 066
tcp LISTEN 0 100 :::8066 :::* users:((“java”,pid=2545,fd=78))
tcp LISTEN 0 100 :::9066 :::*

去jump测试一下mysql(肯定要先装个mysql的客户端啦,这个我也懒得贴了)
可以看到,多查几次,主机会变的,目的达到

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.61 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql54 |
+————+
1 row in set (0.00 sec)

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql54 |
+————+
1 row in set (0.01 sec)

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql54 |
+————+
1 row in set (0.01 sec)

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql55 |
+————+
1 row in set (0.00 sec)

MySQL [(none)]>

拷贝文件server.xml和schema.xml文件到另外两台mycat62和63上

[root@jumper ~]

# scp /root/.ssh/id_rsa 192.168.1.61:/root/.ssh/
id_rsa 100% 1706 4.5MB/s 00:00

[root@mycat61 conf]

# for i in 192.168.1.{62,63}; do scp schema.xml server.xml $i:/usr/local/mycat/conf/; done
schema.xml 100% 862 2.6MB/s 00:00
server.xml 100% 3720 12.7MB/s 00:00
schema.xml 100% 862 2.8MB/s 00:00
server.xml 100% 3720 11.2MB/s 00:00
启动两台mycat62和63

[root@ecs-jumper ~]

# ansible mycat -m shell -a ‘/usr/local/mycat/bin/mycat start’

[root@jumper ~]

# ansible mycat -m shell -a ‘ss -antup | grep 066’
mycat62 | SUCCESS | rc=0 >>
tcp LISTEN 0 100 :::8066 :::* users:((“java”,pid=11234,fd=78))
tcp LISTEN 0 100 :::9066 :::* users:((“java”,pid=11234,fd=74))

mycat63 | SUCCESS | rc=0 >>
tcp LISTEN 0 100 :::8066 :::* users:((“java”,pid=11226,fd=78))
tcp LISTEN 0 100 :::9066 :::* users:((“java”,pid=11226,fd=74))

mycat61 | SUCCESS | rc=0 >>
tcp LISTEN 0 100 :::8066 :::* users:((“java”,pid=10894,fd=76))
tcp LISTEN 0 100 :::9066 :::* users:((“java”,pid=10894,fd=72))

你要是闲着没事,可以验证下mycat62和mycat63,我就是这样

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.62 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql55 |
+————+
1 row in set (0.00 sec)

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql54 |
+————+
1 row in set (0.01 sec)

MySQL [(none)]>

#

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.63 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql55 |
+————+
1 row in set (0.00 sec)

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql55 |
+————+
1 row in set (0.00 sec)

MySQL [(none)]> select @@hostname;
+————+
| @@hostname |
+————+
| mysql54 |
+————+
1 row in set (0.00 sec)

MySQL [(none)]>

5\部署HAPROXY+KEEPALIVED
haproxy是为了实现负载均衡,keepalived是为了实现高可用.

安装haproxy和keepalived

[root@jumper ~]

# ansible haproxy –list
hosts (2):
haproxy1
haproxy2

[root@ecs-jumper ~]

# ansible haproxy -m shell -a ‘yum -y install haproxy keepalived’
修改haproxy配置文件（保留global和defaults 剩下的frontend backend 删除就可以 添加如下配置）

[root@haproxy1 ~]

# vim /etc/haproxy/haproxy.cfg
…
listen mycat_3306 *:3306
mode tcp # mysql 得使用 tcp 协议
option tcpka # 使用长连接
balance leastconn # 最小连接调度算法
server mycat61 192.168.1.61:8066 check inter 3000 rise 1 maxconn 1000 fall 3
server mycat62 192.168.1.62:8066 check inter 3000 rise 1 maxconn 1000 fall 3
server mycat63 192.168.1.63:8066 check inter 3000 rise 1 maxconn 1000 fall 3

[root@haproxy2 ~]

# vim /etc/haproxy/haproxy.cfg
…
listen mycat_3306 *:3306
mode tcp # mysql 得使用 tcp 协议
option tcpka # 使用长连接
balance leastconn # 最小连接调度算法
server mycat61 192.168.1.61:8066 check inter 3000 rise 1 maxconn 1000 fall 3
server mycat62 192.168.1.62:8066 check inter 3000 rise 1 maxconn 1000 fall 3
server mycat63 192.168.1.63:8066 check inter 3000 rise 1 maxconn 1000 fall 3

启动haproxy,并且看下服务起来没有

[root@ecs-jumper ~]

# ansible haproxy -m shell -a ‘systemctl start haproxy’

[root@jumper ~]

# ansible haproxy -m shell -a ‘systemctl status haproxy’
haproxy2 | SUCCESS | rc=0 >>
● haproxy.service – HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2019-03-27 09:01:35 CST; 4h 9min ago
Main PID: 10244 (haproxy-systemd)
CGroup: /system.slice/haproxy.service
├─10244 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
├─10247 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
└─10248 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds

haproxy1 | SUCCESS | rc=0 >>
● haproxy.service – HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2019-03-27 09:01:35 CST; 4h 9min ago
Main PID: 10228 (haproxy-systemd)
CGroup: /system.slice/haproxy.service
├─10228 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
├─10231 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
└─10232 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds

2台haproxy都测试一下,不要偷懒,就快成功了

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.21 -e ‘select @@hostname’
+————+
| @@hostname |
+————+
| mysql55 |
+————+

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.21 -e ‘select @@hostname’
+————+
| @@hostname |
+————+
| mysql54 |
+————+

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.22 -e ‘select @@hostname’
+————+
| @@hostname |
+————+
| mysql55 |
+————+

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.22 -e ‘select @@hostname’
+————+
| @@hostname |
+————+
| mysql54 |
+————+

修改keepalived配置文件,写法很多,网上自己搜,不要局限于案例

[root@jumper ~]

# ansible haproxy -m shell -a ‘cat /etc/keepalived/keepalived.conf’
haproxy1 | SUCCESS | rc=0 >>
! Configuration File for keepalived

global_defs {
router_id haproxy1
}
vrrp_strict chk_haproxy {
script “killall -0 haproxy” # cheaper than pidof
interval 2 # check every 2 seconds
}

vrrp_instance mycat1 {
state MASTER
interface eth0
virtual_router_id 51
priority 200
nopreempt
advert_int 2
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.111/24 brd 192.168.1.255 dev eth0 label eth0:1
}
track_script {
chk_haproxy weight=0 # +2 if process is present
}
}

haproxy2 | SUCCESS | rc=0 >>
global_defs {
router_id haproxy2
}
vrrp_strict chk_haproxy {
script “killall -0 haproxy”
interval 2
}

vrrp_instance mycat1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
! nopreempt
advert_int 2
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.111/24 brd 192.168.1.255 dev eth0 label eth0:1
}
track_script {
chk_haproxy weight=0
}
}

[root@jumper ~]

#

启动keepalived

[root@jumper ~]

# ansible haproxy -m shell -a ‘systemctl start keepalived’
haproxy1 | CHANGED | rc=0 >>

haproxy2 | CHANGED | rc=0 >>

验证VIP是否绑定成功

[root@jumper ~]

# ansible haproxy -m shell -a ‘ip addr’
haproxy2 | SUCCESS | rc=0 >>
1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1
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: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:7e:48:cb brd ff:ff:ff:ff:ff:ff
inet 192.168.1.22/24 brd 192.168.1.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fe7e:48cb/64 scope link
valid_lft forever preferred_lft forever

haproxy1 | SUCCESS | rc=0 >>
1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1
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: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:35:98:95 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.1.111/24 brd 192.168.1.255 scope global secondary eth0:1
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fe35:9895/64 scope link
valid_lft forever preferred_lft forever

故障切换自己去验证,懒得贴了

6\关键时刻到了,验证集群能否正常访问

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.111 -e ‘select @@hostname’
+————+
| @@hostname |
+————+
| mysql54 |
+————+

[root@jumper ~]

# mysql -uroot -p123456 -h192.168.1.111 -e ‘select @@hostname’
+————+
| @@hostname |
+————+
| mysql55 |
+————+

[root@jumper ~]

#

搞完收工,吃饭去了.