一、实验材料
1 外部环境
- amd64主机
- Windows x64
- Oracle VM VirtualBox
2 实验环境 (文章最后有下载链接)
- VirtualBox的4个虚拟机debian x64
- mysql-proxy (Atlas)
- mha
二、实验步骤
1 确保hosts和hostname配置正确
1.1 每台主机都相同(主机:debtest1, debtest2, debtest3, debtest4)
1
2
3
4
5
6
7
8
9
10
11
|
root@debtest1:~/.ssh# cat /etc/hosts
127.0.0.1 localhost
192.168.56.13 debtest1.sam.test debtest1
192.168.56.14 debtest2.sam.test debtest2
192.168.56.15 debtest3.sam.test debtest3
192.168.56.16 debtest4.sam.test debtest4
# The following lines are desirable for IPv6 capable hosts
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
|
1.2 按照实际情况设定主机名,重启
1
2
3
|
root@debtest1:~/.ssh# cat /etc/hostname
debtest1
|
2 使用ssh-keygen工具生成统一公私钥对,并同步到所有机器。测试公私钥验证访问
2.1 使用ssh-keygen工具生成统一公私钥对(主机:debtest1)
1
2
3
4
5
|
ssh-keygen -t rsa
cd ~/.ssh
cat id_rsa.pub authorized_keys
|
2.2 同步到所有机器
1
2
3
4
5
|
scp ./* root@debtest2:/root/.ssh/
scp ./* root@debtest3:/root/.ssh/
scp ./* root@debtest4:/root/.ssh/
|
2.3 每台主机上测试公私钥验证访问(必须要手动访问一次,在相应主机上产生无密码ssh访问记录)
1
2
3
4
5
6
7
|
ssh debtest1
ssh debtest2
ssh debtest3
ssh debtest4
|
3.规划节点用户和ip配置
3.1 规划
192.168.56.13 debtest1 --> mha manager node & mha node
192.168.56.14 debtest2 --> mysql1(master) & mha node & mysql-proxy(Atlas)
192.168.56.15 debtest3 --> mysql2(slave) & mha node
192.168.56.16 debtest4 --> mysql3(slave) & mha node
192.168.56.19 vip
3.2 定好虚拟ip后别忘了在当前的主库上添加虚拟ip 每次重启都需要操作
ip addr add 192.168.56.19/24 dev eth0
# 删除虚拟ip的命令 ip addr del 192.168.56.19/24 dev eth0
4 数据库主从关系配置
4.1 配置my.cnf
找到[mysqld]节点,添加或修改成以下内容。
server-id=1 #服务器ID
log-bin=mysql-bin
binlog-do-db=test #这里设置需要在主服务器记录日志的数据库,只有在这里设置了的数据库才能被复制到从服务器
binlog-ignore-db=mysql #这里设置在主服务器上不记度日志的数据库
expire_logs_days=10
4.2 进入mysql客户端,执行以下命令 每次重启都需要操作
主数据库(主机:debtest2)
1
2
3
|
RESET MASTER;
STOP SLAVE;
RESET MASTER;
|
从数据库(主机:debtest3, debtest4)
1
2
3
4
5
6
7
8
9
10
11
12
|
RESET MASTER;
STOP SLAVE;
change master to
master_host='192.168.56.14',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos=107;
START SLAVE;
SHOW SLAVE STATUS;
|
5 安装mha
5.1 在全部节点上安装mha node包和其依赖包(主机:debtest1, debtest2, debtest3, debtest4)
1
2
3
|
apt-get install libdbd-mysql-perl
dpkg -i mha4mysql-node_0.53_all.deb
|
5.2 仅需要在mha manager节点上安装mha manager包及其依赖包(主机:debtest1)
1
2
3
4
5
6
7
8
9
|
apt-get install libdbd-mysql-perl
apt-get install libconfig-tiny-perl
apt-get install liblog-dispatch-perl
apt-get install libparallel-forkmanager-perl
dpkg -i mha4mysql-manager_0.53_all.deb
|
6 建立配置文件目录,编辑mha必要的三个文件,一个配置文件,2个虚拟ip管理脚本(主机:debtest1)
master_ip_online_change 和 master_ip_failover 一模一样
mha_manager.cnf
7 测试和启动mha(主机:debtest1)
7.1 可以尝试验证一下配置是否成功
1
2
3
|
masterha_check_ssh --conf=/root/mha_base/mha_manager.cnf
masterha_check_repl --conf=/root/mha_base/mha_manager.cnf
|
7.2 在manager节点启动mha服务,然后观察日志,并尝试关闭当前主库,注意观察日志,主要看失效发现,日志检测,ip漂移和角色切换过程 每次重启都需要操作
1
2
|
nohup /usr/bin/masterha_manager --conf=/root/mha_base/mha_manager.cnf --ignore_last_failover < /dev/null > /root/mha_base/manager.log
2>&1 &
|
8 mysql-proxy(Atlas)
8.1 修改Atlas的配置文件test.cnf
# 配置主数据库
proxy-backend-addresses = 192.168.56.19:3306
# 配置从数据库
proxy-read-only-backend-addresses = 192.168.56.14:3306, 192.168.56.15:3306, 192.168.56.16:3306
# 配置Atlas访问各个数据库使用的用户名和密码
pwds = mha:O2jBXONX098=
# Atlas工作端口
proxy-address = 0.0.0.0:4040
# Atlas管理端口
admin-address = 0.0.0.0:4041
8.2 启动Atlas 每次重启都需要操作
1
|
./mysql-proxyd test start
|
8.3 进入Atlas工作界面,通过Atlas来执行sql语句,自动分离读写操作
1
|
mysql -u192.168.56.14 -P4040 -umha -hmha
|
三、实验辅助手段及遇到的坑
1 破解debian root密码
http://jingyan.baidu.com/article/fec7a1e5f0ea281190b4e7bb.html
2 克隆虚拟机
目的:利用debtest3克隆一个debtest4,增加实验需要的节点
3 设置虚拟机为静态IP
目的:确保每次重启虚拟机,IP地址都是固定的,便于以后的实验。
4 Virtual Box网卡配置
宿主机里配置Virtual Box虚拟网卡的ip,网段和虚拟机的静态ip保持一致:192.168.56.1
- 效果:
- 宿主机可以ping通虚拟机;
- 虚拟机可以ping通宿主机;
- 虚拟机内部可以互相ping通;
- 虚拟机可以上互联网
5 停止master上mysql没有自动转移 ,manager.log 出现错误
1
2
|
[error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln178] Got ERROR: Use of uninitialized value $msg in scalar chomp at
/usr/local/share/perl5/MHA/ManagerConst.pm line 90.
|
这是一个bug,解决方法:
在文件 /usr/share/perl5/vendor_perl/MHA/ManagerConst.pm
第90行(chomp $msg)前加入一行:
$msg = "" unless($msg);
四、资源下载及附件
说明:
内含所有虚拟机,所有配置都已经执行过,debtest1中/root/tmp/目录下有Atlas和mha的安装包
(1) 所有主机的root密码
123
(2) 所有数据库的root密码
123
(3) 所有数据库的其他账户
mha mha
repl repl
2 mha_manager.cnf
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
|
[server default]
manager_workdir=/root/mha_base
manager_log=/root/mha_base/manager.log
remote_workdir=/root/mha_base
ssh_user=root
ssh_port=22
user=mha
password=mha
repl_user=repl
repl_password=repl
multi_tier_slave=1
ping_interval=1
ping_type=CONNECT
master_ip_failover_script=/root/mha_base/master_ip_failover
master_ip_online_change_script=/root/mha_base/master_ip_online_change
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.56.13 -s 192.168.56.15 -s 192.168.56.16 --user=root --port=22 --master_host=debtest2 --master_ip=192.168.56.14 --master_port=3306
[server1]
candidate_master=0
ignore_fail=1
check_repl_delay = 1
hostname=debtest2
ip=192.168.56.14
port=3306
ssh_port=22
master_binlog_dir=/var/log/mysql/
[server2]
candidate_master=0
ignore_fail=1
check_repl_delay = 1
hostname=debtest3
ip=192.168.56.15
port=3306
ssh_port=22
master_binlog_dir=/var/log/mysql/
[server3]
candidate_master=0
ignore_fail=1
check_repl_delay = 1
hostname=debtest4
ip=192.168.56.16
port=3306
ssh_port=22
master_binlog_dir=/var/log/mysql/
|
3 master_ip_online_change 和 master_ip_failover 两个文件一模一样
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
#!/usr/bin/env perl
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port
);
my $vip = '192.168.56.19/24'; #virtual ip
my $ssh_start_vip = "ip addr add $vip dev eth0";
my $ssh_stop_vip = "ip addr del $vip dev eth0";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
# updating global catalog, etc
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on old master: $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
# If you want to continue failover, exit 10.
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
# Enable the VIP on the new_master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# Disable the VIP on the old_master
sub stop_vip() {
my $ssh_user = "root";
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
|