目 录CONTENT

文章目录

搭建MHA高可用

简中仙
2020-10-23 / 0 评论 / 0 点赞 / 52 阅读 / 0 字 / 正在检测是否收录...
温馨提示:
本文最后更新于2024-03-06,若内容或图片失效,请留言反馈。 本文如有错误或者侵权的地方,欢迎您批评指正!

一、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)
0

评论区