一、MHA架构
一般来说最少需要MySQL一主二从,管理节点可以放在单独的服务器也可以放在一台从库服务器上。
建议使用MySQL5.7以上的较新版本,CentOS7或Debian9以上版本。
需要注意的是MHA版本之间有差异,mha4mysql-node-0.56-0.el6.noarch.rpm需要跟MySQL5.6配合使用。mha4mysql-node-0.58-0.el7.noarch.rpm跟MySQL5.7以上版本配合使用。
manager端 | node端 |
---|---|
mysql-03(从) | mysql-01(主),mysql-02(从),mysql-03(从) |
二、搭建MHA
1、全局设置(三台都需要)
1、创建软连接
# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
2、开启无密码传送
# ssh-keygen -t rsa
# ssh-copy-id 192.168.1.10:
# ssh-copy-id 192.168.1.20:
# ssh-copy-id 192.168.1.30:
3、安装软件包
# yum -y install perl-DBD-MySQL
# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 192.168.1.20:
# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 192.168.1.30:
# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
2、在主库中创建用户
# mysql
db01 [(none)]>grant all privileges on *.* to mha@'192.168.1.%' identified by 'mha';
3、管理节点安装Manager软件
# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
# yum repolist
# yum makecache fast
# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
4、准备配置文件
# mkdir -p /etc/mha
# mkdir -p /var/log/mha/app1
# cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
#二进制日志目录
master_binlog_dir=/data/binlog
#mha用户信息
user=mha
password=mha
#探测心跳的间隔时间(秒),默认探测3次
ping_interval=2
#主从的用户信息
repl_user=repl
repl_password=123
ssh_user=root
[server1]
hostname=192.168.1.10
port=3306
[server2]
hostname=192.168.1.20
port=3306
[server3]
hostname=192.168.1.30
port=3306
EOF
5、状态检查并开启MHA
# masterha_check_ssh --conf=/etc/mha/app1.cnf
Mon Oct 19 21:19:07 2020 - [debug] ok.
Mon Oct 19 21:19:07 2020 - [info] All SSH connection tests passed successfully.
# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:69402) is running(0:PING_OK), master:192.168.1.10
参数 | 说明 |
---|---|
--conf | 指定配置文件 |
--remove_dead_master_conf | 当主库宕机时自动将故障节点从配置文件中去除 |
--ignore_last_failover | 忽略最后一次的切换 |
三、应用透明
1、准备所需脚本
# vim master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
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.1.100/24'; # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
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 {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
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;
};
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 the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`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";
}
# cp master_ip_failover /usr/local/bin/master_ip_failover
# cd /usr/local/bin/
# vim master_ip_failover
my $vip = '192.168.1.100/24';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
# chmod +x /usr/local/bin/master_ip_failover
# yum -y install dos2unix
# dos2unix /usr/local/bin/master_ip_failover
2、更改配置文件
# vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
master_ip_failover_script=/usr/local/bin/master_ip_failover
[server1]
hostname=192.168.1.10
port=3306
[server2]
hostname=192.168.1.20
port=3306
[server3]
hostname=192.168.1.30
port=3306
3、主库手工添加vip
# ifconfig ens33:1 192.168.1.100/24
4、重新启动MHA
# masterha_stop --conf=/etc/mha/app1.cnf
Stopped app1 successfully.
[1]+ 退出 1 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1
# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
[1] 17601
# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:17601) is running(0:PING_OK), master:192.168.1.10
四、数据补偿
1、准备备份数据库配置文件
# vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
master_ip_failover_script=/usr/local/bin/master_ip_failover
[server1]
hostname=192.168.1.10
port=3306
[server2]
hostname=192.168.1.20
port=3306
[server3]
hostname=192.168.1.30
port=3306
[binlog1]
no_master=1
hostname=192.168.1.40
master_binlog_dir=/data/mysql/binlog
2、创建目录
# mkdir -p /data/mysql/binlog
# chown -R mysql.mysql /data/*
3、拉取主库binlog日志
# cd /data/mysql/binlog
# mysqlbinlog -R --host=192.168.1.10 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
[2] 35693
# ls
mysql-bin.000001 mysql-bin.000002
# ll
总用量 8
-rw-r----- 1 root root 177 10月 23 18:51 mysql-bin.000001
-rw-r----- 1 root root 736 10月 23 18:51 mysql-bin.000002
拉取日志的起点,需要按照目前主库正在使用的binlog为起点
mysql01 [(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 736 | | | 72f32e7d-150b-11eb-afeb-000c2979853f:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
五、MHA故障处理
1、启动故障节点
# systemctl restart mysqld
2、恢复主从
1、查看当前主库信息
# grep "CHANGE MASTER TO" /var/log/mha/app1/manager
Thu Jul 18 18:31:54 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.20', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
2、故障节点加入主库
mysql01 [(none)]>CHANGE MASTER TO
MASTER_HOST='192.168.1.20',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='repl',
MASTER_PASSWORD='123';
db01 [(none)]>start slave;
3、MHA管理节点恢复配置文件
# vim /etc/mha/app1.cnf
[server1]
hostname=192.168.1.10
port=3306
[server2]
hostname=192.168.1.20
port=3306
[server3]
hostname=192.168.1.30
port=3306
# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
4、重新启动MHA并恢复数据补偿
# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:16543) is running(0:PING_OK), master:192.168.1.20
# cd /data/mysql/binlog
# rm -rf /data/mysql/binlog/*
# cd /data/mysql/binlog
# mysqlbinlog -R --host=192.168.1.20 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
六、MHA配合Atlas实现读写分离
1、安装配置
# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
# yum -y install Atlas-2.2.1.el6.x86_64.rpm
# cd /usr/local/mysql-proxy/conf
# mv test.cnf test.cnf.bak
# vim test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 192.168.1.100:3306
proxy-read-only-backend-addresses = 192.168.1.20:3306,192.168.1.30:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8
# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
# ps -ef |grep proxy
root 42818 1 0 22:44 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 42819 42818 0 22:44 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 42832 16804 0 22:44 pts/0 00:00:00 grep --color=auto proxy
# mysql -umha -pmha -h 192.168.1.30 -P 33060
mysql03 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 52 |
+-------------+
1 row in set (0.03 sec)
mysql03 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 53 |
+-------------+
1 row in set (0.01 sec)
mysql03 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 51 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
2、添加生产用户
1、查看主库
# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:35587) is running(0:PING_OK), master:192.168.1.10
2、主库添加生产用户
# mysql
mysql01 [(none)]>grant select ,update,insert on *.* to zhangsan@'192.168.1.%' identified by '123456';
3、Atlas添加用户
# /usr/local/mysql-proxy/bin/encrypt 123456 # 加密密码
/iZxz+0GRoA=
# vim /usr/local/mysql-proxy/conf/test.cnf
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,zhangsan:/iZxz+0GRoA=
# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started
# mysql -uzhangsan -p123456 -h 192.168.1.30 -P 33060
mysql03 [(none)]>
3、 Atlas基本管理
1、连接管理接口
# mysql -uuser -ppwd -h127.0.0.1 -P2345
mysql03 [(none)]>select * from help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
2、常用管理命令
1、查看节点
mysql03 [(none)]>SELECT * FROM backends;
+-------------+--------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+--------------------+-------+------+
| 1 | 192.168.1.100:3306 | up | rw |
| 2 | 192.168.1.20:3306 | up | ro |
| 3 | 192.168.1.30:3306 | up | ro |
+-------------+--------------------+-------+------+
2、下线节点
mysql03 [(none)]>set offline 2;
+-------------+-------------------+---------+------+
| backend_ndx | address | state | type |
+-------------+-------------------+---------+------+
| 2 | 192.168.1.20:3306 | offline | ro |
+-------------+-------------------+---------+------+
3、上线节点
mysql03 [(none)]>set online 2;
+-------------+-------------------+---------+------+
| backend_ndx | address | state | type |
+-------------+-------------------+---------+------+
| 2 | 192.168.1.20:3306 | unknown | ro |
+-------------+-------------------+---------+------+
4、删除节点
mysql03 [(none)]>REMOVE BACKEND 3;
5、添加节点(从)
mysql03 [(none)]>ADD SLAVE 192.168.1.30:3306;
6、添加生产用户
mysql03 [(none)]>ADD PWD lisi:123456;
Empty set (0.00 sec)
mysql03 [(none)]>select * from pwds;
+----------+--------------+
| username | password |
+----------+--------------+
| repl | 3yb5jEku5h4= |
| mha | O2jBXONX098= |
| zhangsan | /iZxz+0GRoA= |
| lisi | /iZxz+0GRoA= |
+----------+--------------+
4 rows in set (0.00 sec)
7、保存修改
mysql03 [(none)]>save config;
Empty set (0.00 sec)
评论区