配置MySQL高可用集群MHA

配置MySQL高可用集群+++++++++++++++++++
主機角色 ：
客戶端 client50
數據庫服務器 mysql51 到 mysql55
管理主機 mgm56
VIP地址 192.168.4.100
拓撲結構：

  client50 

     | 
  mysql51主  
     |  

| | | | |
mysql52 mysql53 mysql54 mysql55 mgm56
從 從 從 從 管理集群
備用主 備用主
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
配置MySQL主從同步結構：一主多從
配置主庫51
配置備用從庫52
配置備用從庫53
配置純從庫54
配置純從庫55

51-55都要進行
安裝mysql【yum -y install mysql-community-*】
mysql-5.7.17.tartar -xf
mysql-community-client-5.7.17-1.el7.x86_64.rpm
mysql-community-common-5.7.17-1.el7.x86_64.rpm
mysql-community-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-test-5.7.17-1.el7.x86_64.rpm
在所有服務器51-56上安裝mha_node軟件[mha4mysql-node-0.56-0.el6.noarch.rpm]
]#yum -y install perl-DBD-mysql
]#rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
一、配置所有數據庫主機之間可以互相以ssh密鑰對方式認證登陸[免密碼]

二、配置manager56主機 無密碼ssh登錄 所有數據庫主機【免密碼】

三、配置主從同步,要求如下：
51 主庫 開半同步復制
52 從庫（備用主庫） 開半同步復制
53 從庫（備用主庫） 開半同步復制
54 從庫 不做備用主庫所以不用開半同步復制
55 從庫 不做備用主庫所以不用開半同步復制
配置半同步復制模式

查看當前的數據庫服務是否支持動態加載模塊
mysql>show variables like 'have_dynamic_loading';

+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+

使用命令安裝模塊
mysql>
mysql> INSTALL PLUGIN rpl_semi_sync_master
-> SONAME 'semisync_master.so';

mysql> INSTALL PLUGIN rpl_semi_sync_slave
-> SONAME 'semisync_slave.so';

查看模塊狀態
mysql>
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
啟用半同步復制
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

查看是否啟用成功
mysql> show variables like 'rpl_semisync%_enabled';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
把配置寫進配置文件，使其永久生效

3.1、master51配置：
vim /etc/my.cnf
[mysqld]
#開啟插件加載半同步復制
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

relay_log_purge=off; //不允許自動刪除中繼日志文件【mysql> set global relay_log_purge=off;】

server_id=51
log-bin=master51
binlog-format="mixed"
:wq
#systemctl restart mysqld
#mysql -uroot -p123abcxyz
mysql> grant replication slave on . to repluser@"%" identified by "123abcxyz";
mysql> show master status;
注意：備用52-53上也要有repluser用戶
3.2、備用master52的配置
vim /etc/my.cnf
[mysqld]
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

relay_log_purge=off; //不允許自動刪除中繼日志文件【mysql> set global relay_log_purge=off;】

server_id=52
log-bin=master52
binlog-format="mixed"
:wq
#systemctl restart mysqld
]# ls /var/lib/mysql/master52.*
/var/lib/mysql/master52.000001 /var/lib/mysql/master52.index

#mysql -uroot -p123abcxyz
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123abcxyz",
-> master_log_file="master51.000001",
-> master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.3、備用master53的配置
]# vim /etc/my.cnf
[mysqld]
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

relay_log_purge=off; //不允許自動刪除中繼日志文件【mysql> set global relay_log_purge=off;】

server_id=53
log-bin=master53
binlog-format="mixed"
:wq

]# systemctl restart mysqld
]# ls /var/lib/mysql/master53.*

]# mysql -uroot -p123abcxyz
mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123abcxyz",master_log_file="master51.000001",master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.4、配置從服務器54

]# vim /etc/my.cnf
[mysqld]
server_id=54
:wq

]# systemctl restart mysqld
]# mysql -uroot -p123abcxyz
mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123abcxyz",master_log_file="master51.000001",master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.5、配置從服務器55
]# vim /etc/my.cnf
[mysqld]
server_id=55
:wq
]# systemctl restart mysqld

]# mysql -uroot -p123abcxyz
mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123abcxyz",master_log_file="master51.000001",master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[配置51-55主從同步完成]
阿保存在所有數據庫服務器51-55上授權監控用戶
mysql>
grant all on . to root@'%' identified by '123abcxyz';
++++++++++++++++++++++++++++++++++
準備集群環境：
1 在所有主機上安裝共享目錄里的perl軟件包 （51-56）
公共配置：在所有主機上安裝軟件軟件包
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm

]# yum -y install perl-.rpm
2 在所有服務器51-56上安裝mha_node軟件包
]#cd mha-soft-student/
]#yum -y install perl-DBD-mysql
]#rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
4 在管理主機56上安裝mha_manager軟件包
]# yum -y install perl-ExtUtils- perl-CPAN-*

]# tar -zxf mha4mysql-manager-0.56.tar.gz

]# cd mha4mysql-manager-0.56

]# perl Makefile.pl //[需要的依賴包可通過yum list | grep -i 關鍵字 獲取yum -y install perl-ExtUtils- perl-CPAN-]

]# make

]# make install

配置MHA:
在管理主機56上創建管理命令
]# mkdir /root/bin
]#cd mha4mysql-manager-0.56/bin
]#cp * /root/bin/
創建主配置 并編輯
]# mkdir /etc/mha
]#cp mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha/
]#vim /etc/mha/app1.cnf
[server default] #服務默認配置
manager_workdir=/etc/mha #工作目錄
manager_log=/etc/mha/manager.log #日志文件

master_ip_failover_script=/etc/mha/master_ip_failover #故障切換腳本

#ssh服務用戶名及端口
ssh_user=root
ssh_port=22

#主從復制時，從庫連接主庫的用戶名及密碼
repl_user=repluser
repl_password=123abcxyz

#監控數據庫時，連接服務器的用戶及密碼
user=root
password=123abcxyz

#指定數據庫服務器ip及角色
[server1]
hostname=192.168.4.51
candidate_master=1
port=3306
[server2]
hostname=192.168.4.52
candidate_master=1
port=3306
[server3]
hostname=192.168.4.53
candidate_master=1
port=3306
[server4]
hostname=192.168.4.54
no_master=1
port=3306
[server5]
hostname=192.168.4.55
no_master=1
port=3306
:wq

創建故障切換腳本并編輯[正常在mha4mysql-manager-0.56目錄下]
]# cd mha-soft-student
]# cp master_ip_failover /etc/mha/
]# chmod +x /etc/mha/master_ip_failover

]# vim /etc/mha/master_ip_failover
35 my $vip = '192.168.4.100/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";
:wq

把腳本中指定的ip地址手動部署在當前主庫51 上。
mysql51]# ifconfig eth0:1 192.168.4.100/24
mysql51]# ifconfig eth0:1

啟動服務
1 檢查ssh連接
mgm56]# masterha_check_ssh --conf=/etc/mha/app1.cnf

2檢查主從同步配置
mgm56]# masterha_check_repl --conf=/etc/mha/app1.cnf
3 啟動管理服務
mgm56]#
masterha_manager --conf=/etc/mha/app1.cnf \
--remove_dead_master_conf --ignore_last_failover
4查看狀態
]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:9541) is running(0:PING_OK), master:192.168.4.51

測試配置
在客戶端50主機上連接vip 192.168.4.100 訪問數據庫服務
1主庫做用戶授權
mysql51>create database db3;create table db3.a(id int);
mysql51>grant select,insert on db3. to test@"%" identified
by "123abcxyz";
2客戶端使用授權用戶連接
client50]# mysql -h292.168.4.100 -utest -p123abcxyz db3
mysql>
mysql> insert into db3.a values(100);
Query OK, 1 row affected (0.07 sec)
mysql> select from db3.a;

測試高可用配置
把主庫51的數據庫服務停止
客戶端仍然可以連接VIP地址192.168.4.100 訪問數據庫服務
mysql51]# systemctl stop mysqld

client50]# ping -c 2 192.168.4.100

client50]# mysql -h292.168.4.100 -uplj -p123abcxyz db3
mysql> select @@hostname;
mysql> select * from db3.a;
mysql> insert into db3.a values(101);

在/etc/mha/app1.cnf文件沒有了主庫51的配置
數據庫53 - 55 把 主庫地址指向 新選舉出的主庫ip地址

把壞掉的數據庫服務器51 再添加到集群里。
MySQL51]# systemctl start mysqld
MySQL51]# mysql -uroot -p123abcxyz

mysql> change master to master_host="192.168.4.52",
-> master_user="repluser",
-> master_password="123abcxyz",
-> master_log_file="master52.000002", 當前主庫日志
-> master_log_pos=1237; 對應的pos點
mysql> start slave;
mysql> show slave status\G; IO 和SQL 進程都是Yes 主機ip 52

mgm56]# vim /etc/mha/app1.cnf
[server1]
candidate_master=1
hostname=192.168.4.51
:wq

mgm56]#
masterha_manager --conf=/etc/mha/app1.cnf \
--remove_dead_master_conf --ignore_last_failover

查看狀態
mgm56]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:9541) is running(0:PING_OK), master:192.168.4.52
把主庫52的數據庫服務停止后，客戶端仍然可以連接
vip地址192.168.4.100 訪問MySQL數據庫服務.
在/etc/mha/app1.cnf文件沒有了主庫52的配置
數據庫53 - 55 把 主庫地址指向 新選舉出的主庫ip地址[日志最新的51/53]