mysql高可用方案之MHA

標籤：mysql高可用方案之mha

環境規劃:

節點說明    主機名稱   IP地址

管理節點   tong3   192.168.1.249

主節點     tong2   192.168.1.248

主節點　   tong1   192.168.1.247

1.網路和主機名稱配置

設定每個主機的ＩＰ地址和/etc/hosts檔案互相解析

[[email protected] ~]# cat /etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 tong3
[[email protected] ~]# ping tong1 -c1         --網路必須ping通
PING tong1 (192.168.1.247) 56(84) bytes of data.
64 bytes from localhost (192.168.1.247): icmp_seq=1 ttl=64 time=0.021 ms

--- tong1 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.021/0.021/0.021/0.000 ms
[[email protected] ~]# ping tong2 -c1
PING tong2 (192.168.1.248) 56(84) bytes of data.
64 bytes from tong2 (192.168.1.248): icmp_seq=1 ttl=64 time=0.109 ms

--- tong2 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.109/0.109/0.109/0.000 ms
[[email protected] ~]# ping tong3 -c1
PING tong3 (192.168.1.249) 56(84) bytes of data.
64 bytes from tong3 (192.168.1.249): icmp_seq=1 ttl=64 time=0.124 ms

--- tong3 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.124/0.124/0.124/0.000 ms
[[email protected] ~]# 

2.安裝mha管理軟體mha manager

tong3管理節點:

[[email protected] ~]# yum install  perl-DBD-MySQL cpan　 --安裝perl工具

[[email protected] ~]# tar xvf mha4mysql-manager-0.53.tar.gz  -C /usr/local/

[[email protected] ~]# cd /usr/local/mha4mysql-manager-0.53/

[[email protected] mha4mysql-manager-0.53]# perl Makefile.PL 

[[email protected] mha4mysql-manager-0.53]# echo $?
0
[[email protected] mha4mysql-manager-0.53]# make && make install

[[email protected] mha4mysql-manager-0.53]# echo $?
0
[[email protected] mha4mysql-manager-0.53]#

3.在各資料節點安裝mha node

[[email protected] ~]# tar xvf mha4mysql-node-0.53.tar.gz
[[email protected] ~]# cd mha4mysql-node-0.53
[[email protected] mha4mysql-node-0.53]# yum install perl-DBD-mysql cpan -y

[[email protected] mha4mysql-node-0.53]# perl Makefile.PL
[[email protected] mha4mysql-node-0.53]# make && make install

[[email protected] mha4mysql-node-0.53]#  echo $?
0
[[email protected] mha4mysql-node-0.53]# 

4.各節點ssh互相信任

[[email protected] ~]# ssh-keygen  -t dsa

[[email protected] ~]# cd .ssh

[[email protected] .ssh]# cat id_dsa.pub  > authorized_keys

[[email protected] .ssh]# scp 192.168.1.247:/root/.ssh/id_dsa.pub 247

[[email protected] .ssh]# scp 192.168.1.248:/root/.ssh/id_dsa.pub 248

[[email protected] .ssh]# cat 248  247 >> authorized_keys
[[email protected] .ssh]# scp authorized_keys 192.168.1.248:/root/.ssh/
authorized_keys                                                                                                                                    100% 1800     1.8KB/s   00:00   
[[email protected] .ssh]# scp authorized_keys 192.168.1.247:/root/.ssh/
[email protected]‘s password:
authorized_keys                                                                                                                                    100% 1800     1.8KB/s   00:00    
[[email protected] .ssh]# ssh tong1 date
Tue Apr 28 12:57:02 CST 2015
[[email protected] .ssh]# ssh tong2 date
Tue Apr 28 12:59:57 CST 2015
[[email protected] .ssh]# ssh tong3 date
Tue Apr 28 12:57:25 CST 2015
[[email protected] .ssh]# 

5.在管理節點編輯設定檔

[[email protected] .ssh]# mkdir /etc/mysqlmha
[[email protected] .ssh]# cd /etc/mysqlmha/
[[email protected] mysqlmha]# cp -a /usr/local/mha4mysql-manager-0.53/samples/* .
[[email protected] mysqlmha]# vim conf/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
user=root1                --遠程登陸使用者
password=system
ssh_user=root
repl_user=repl_user       --複製使用者
repl_password=system!#%246
ping_interval=1       --心跳檢測

[server1]
hostname=192.168.1.249
master_binlog_dir="/usr/local/mysql-5.6.23/data/"
# candidate_master=1
no_master=1          --不能切換成主要資料庫

[server2]
hostname=192.168.1.248
master_binlog_dir="/usr/local/mysql-5.6.23/data/"     --二進位記錄檔存放
candidate_master=1     --可以切換成主要資料庫

[server4]
hostname=192.168.1.247
master_binlog_dir="/usr/local/mysql-5.6.23/data/"
candidate_master=1    -可以切換成主要資料庫

[[email protected] ~]# masterha_check_ssh --conf=/etc/mysqlmha/conf/app1.cnf
Tue Apr 28 15:39:21 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 28 15:39:21 2015 - [info] Reading application default configurations from /etc/mysqlmha/conf/app1.cnf..
Tue Apr 28 15:39:21 2015 - [info] Reading server configurations from /etc/mysqlmha/conf/app1.cnf..
Tue Apr 28 15:39:21 2015 - [info] Starting SSH connection tests..
Tue Apr 28 15:39:22 2015 - [debug]
Tue Apr 28 15:39:21 2015 - [debug]  Connecting via SSH from [email protected](192.168.1.249:22) to [email protected](192.168.1.248:22)..
Tue Apr 28 15:39:22 2015 - [debug]   ok.
Tue Apr 28 15:39:22 2015 - [debug]  Connecting via SSH from [email protected](192.168.1.249:22) to [email protected](192.168.1.247:22)..
Tue Apr 28 15:39:22 2015 - [debug]   ok.
Tue Apr 28 15:39:23 2015 - [debug]
Tue Apr 28 15:39:22 2015 - [debug]  Connecting via SSH from [email protected](192.168.1.248:22) to [email protected](192.168.1.249:22)..
Tue Apr 28 15:39:22 2015 - [debug]   ok.
Tue Apr 28 15:39:22 2015 - [debug]  Connecting via SSH from [email protected](192.168.1.248:22) to [email protected](192.168.1.247:22)..
Tue Apr 28 15:39:23 2015 - [debug]   ok.
Tue Apr 28 15:39:24 2015 - [debug]
Tue Apr 28 15:39:22 2015 - [debug]  Connecting via SSH from [email protected](192.168.1.247:22) to [email protected](192.168.1.249:22)..
Tue Apr 28 15:39:23 2015 - [debug]   ok.
Tue Apr 28 15:39:23 2015 - [debug]  Connecting via SSH from [email protected](192.168.1.247:22) to [email protected](192.168.1.248:22)..
Tue Apr 28 15:39:23 2015 - [debug]   ok.
Tue Apr 28 15:39:24 2015 - [info] All SSH connection tests passed successfully.
[[email protected] ~]#

6.修改資料庫設定檔和建立使用者

在所有節點建立相同的使用者:

mysql> grant all privileges on *.* to [email protected]‘192.168.1.%‘ identified by ‘system‘;

mysql> grant replication slave,replication client on *.* to [email protected]‘192.168.1.%‘ identified by ‘system!#%246‘;

tong1節點:

[[email protected] ~]# vim /etc/my.cnf

basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 20
socket = /tmp/mysql.sock

replicate-do-db=tong
replicate-ignore-db=mysql

log-bin=mysql-bin
log-bin-index=mysql-bin-index

auto_increment_offset=1
auto_increment_increment=2
relay_log_purge=0
read-only=1

[[email protected] ~]#

tong2節點:

[[email protected] ~]# vim /etc/my.cnf

basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 10
socket = /tmp/mysql.sock

replicate-do-db=tong
replicate-ignore-db=mysql

log-bin=mysql-bin
log-bin-index=mysql-bin-index

auto_increment_offset=2
auto_increment_increment=2

read-only=1
relay_log_purge=0

[[email protected] ~]#

tong3節點:

[[email protected] ~]# vim /etc/my.cnf

basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 30
socket = /tmp/mysql.sock

replicate-do-db=tong
replicate-ignore-db=mysql

[[email protected] ~]#

7.將tong1和tong2搭建為主主模式

tong1主機:

[[email protected] .ssh]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.6.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>  change master to master_host=‘192.168.1.248‘,master_port=3306,master_user=‘repl_user‘,master_password=‘system!#%246‘,master_log_file=‘mysql-bin.000010‘,master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.45 sec)

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

mysql>

tong1主機:

[[email protected] .ssh]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.6.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>  change master to master_host=‘192.168.1.247‘,master_port=3306,master_user=‘repl_user‘,master_password=‘system!#%246‘,master_log_file=‘mysql-bin.000010‘,master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.45 sec)

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

mysql>

8.檢查複製是否有錯

[[email protected] ~]# masterha_check_repl --conf=/etc/mysqlmha/conf/app1.cnf

Tue Apr 28 15:53:23 2015 - [info] Checking replication health on 192.168.1.249..
Tue Apr 28 15:53:23 2015 - [info]  ok.
Tue Apr 28 15:53:23 2015 - [info] Checking replication health on 192.168.1.248..
Tue Apr 28 15:53:23 2015 - [info]  ok.
Tue Apr 28 15:53:23 2015 - [warning] master_ip_failover_script is not defined.
Tue Apr 28 15:53:23 2015 - [warning] shutdown_script is not defined.
Tue Apr 28 15:53:23 2015 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

[[email protected] mysqlmha]# nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf  >> /tmp/mha_manager 2>&1 &

[[email protected] mysqlmha]# jobs
[1]+  Running                 nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &
[[email protected] mysqlmha]# masterha_check_status --conf=/etc/mysqlmha/conf/app1.cnf
app1 (pid:24330) is running(0:PING_OK), master:192.168.1.247　　--此時主節點在247伺服器上
[[email protected] mysqlmha]#

9.故障測試

tong1節點:

[[email protected] ~]# /etc/init.d/mysqld stop       --停掉tong1節點的資料庫
Shutting down MySQL............ SUCCESS!
[[email protected] ~]# /etc/init.d/mysqld start　　　--節點必須啟動才能做切換

Starting MySQL. SUCCESS!
[[email protected] ~]#

查看tong2主機的日誌狀態,將tong1節點必須要同步到tong2節點上才可以切換(change master to master_host=‘192.168.1.248,master_ ....................)

tong3節點:

[[email protected] mysqlmha]# rm -rf /var/log/masterha/app1/app1.failover.complete
[1]+  Done                    nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1          --必須刪除管理節點的監控檔案

[[email protected] mysqlmha]# nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf  >> /tmp/mha_manager 2>&1 &

[[email protected] ~]# masterha_check_status --conf=/etc/mysqlmha/conf/app1.cnf
app1 (pid:27870) is running(0:PING_OK), master:192.168.1.248
[[email protected] ~]#

本文出自 “一起走過的日子” 部落格，請務必保留此出處http://tongcheng.blog.51cto.com/6214144/1639693

mysql高可用方案之MHA