MHA高可用架构与Atlas读写分离

 1 [root@db02 ~]# cat /etc/my.cnf
 2 [mysqld]
 3 basedir=/application/mysql
 4 datadir=/application/mysql/data
 5 socket=/tmp/mysql.sock
 6 log-error=/var/log/mysql.log
 7 log-bin=/data/mysql/mysql-bin
 8 binlog_format=row
 9 secure-file-priv=/tmp 
10 server-id=52
11 skip-name-resolve
12 gtid-mode=on
13 enforce-gtid-consistency=true
14 log-slave-updates=1
15 relay_log_purge = 0 
16 [mysql]
17 socket=/tmp/mysql.sock

 1 [root@db03 ~]# cat /etc/my.cnf
 2 [mysqld]
 3 basedir=/application/mysql
 4 datadir=/application/mysql/data
 5 socket=/tmp/mysql.sock
 6 log-error=/var/log/mysql.log
 7 log-bin=/data/mysql/mysql-bin
 8 binlog_format=row
 9 secure-file-priv=/tmp 
10 server-id=53
11 skip-name-resolve
12 gtid-mode=on
13 enforce-gtid-consistency=true
14 log-slave-updates=1
15 relay_log_purge = 0 
16 skip-name-resolve
17 [mysql]
18 socket=/tmp/mysql.sock

 1 [server default]
 2 #设置manager的工作目录
 3 manager_workdir=/var/log/masterha/app1
 4 #设置manager的日志
 5 manager_log=/var/log/masterha/app1/manager.log 
 6 #设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
 7 master_binlog_dir=/data/mysql
 8 #设置自动failover时候的切换脚本
 9 master_ip_failover_script= /usr/local/bin/master_ip_failover
10 #设置手动切换时候的切换脚本
11 master_ip_online_change_script= /usr/local/bin/master_ip_online_change
12 #设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
13 password=123456
14 #设置监控用户root
15 user=root
16 #设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover
17 ping_interval=1
18 #设置远端mysql在发生切换时binlog的保存位置
19 remote_workdir=/tmp
20 #设置复制用户的密码
21 repl_password=123456
22 #设置复制环境中的复制用户名 
23 repl_user=rep
24 #设置发生切换后发送的报警的脚本
25 report_script=/usr/local/send_report
26 #一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02
27 secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=10.0.0.51 --master_port=3306
28 #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）
29 shutdown_script=""
30 #设置ssh的登录用户名
31 ssh_user=root 
32 
33 [server1]
34 hostname=10.0.0.51
35 port=3306
36 
37 [server2]
38 hostname=10.0.0.52
39 port=3306
40 #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
41 candidate_master=1
42 #默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master
43 check_repl_delay=0

[root@db03 ~]# cat /usr/local/bin/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 = '10.0.0.55/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$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" ) {

        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" ) {

        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";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    `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";
}

 1 mysql> show slave status\G
 2 *************************** 1. row ***************************
 3                Slave_IO_State: Waiting for master to send event
 4                   Master_Host: 10.0.0.51
 5                   Master_User: repl
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000007
 9           Read_Master_Log_Pos: 191
10                Relay_Log_File: db02-relay-bin.000002
11                 Relay_Log_Pos: 361
12         Relay_Master_Log_File: mysql-bin.000007
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 191
25               Relay_Log_Space: 564
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 51
43                   Master_UUID: c8fcd56e-eb79-11e7-97b0-000c296c7a11
44              Master_Info_File: /application/mysql-5.6.36/data/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 
55             Executed_Gtid_Set: c8fcd56e-eb79-11e7-97b0-000c296c7a11:1-3
56                 Auto_Position: 1
57 1 row in set (0.00 sec)

 1 mysql>  show slave status\G
 2 *************************** 1. row ***************************
 3                Slave_IO_State: Waiting for master to send event
 4                   Master_Host: 10.0.0.52
 5                   Master_User: repl
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000009
 9           Read_Master_Log_Pos: 191
10                Relay_Log_File: db03-relay-bin.000002
11                 Relay_Log_Pos: 361
12         Relay_Master_Log_File: mysql-bin.000009
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 191
25               Relay_Log_Space: 564
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 52
43                   Master_UUID: c8fa1d13-eb79-11e7-97b0-000c29d60ab3
44              Master_Info_File: /application/mysql-5.6.36/data/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 
55             Executed_Gtid_Set: c8fcd56e-eb79-11e7-97b0-000c296c7a11:1-3
56                 Auto_Position: 1
57 1 row in set (0.00 sec)

 1 [mysql-proxy]
 2 #(必备，默认值即可)管理接口的用户名
 3 admin-username = user
 4 #(必备，默认值即可)管理接口的密码
 5 admin-password = pwd
 6 #(必备，根据实际情况配置)主库的IP和端口
 7 proxy-backend-addresses = 192.168.0.12:3306
 8 #(非必备，根据实际情况配置)从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔。如果想让主库也能分担读请求的话，只需要将主库信息加入到下面的配置项中
 9 proxy-read-only-backend-addresses = 192.168.0.13:3306,192.168.0.14:3306
10 #(必备，根据实际情况配置)用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，用户名与密码之间用冒号分隔。主从数据库上需要先创建该用户并设置密码（用户名和密码在主从数据库上要一致）。比如用户名为myuser，密码为mypwd，执行./encrypt mypwd结果为HJBoxfRsjeI=。如果有多个用户用逗号分隔即可。则设置如下行所示：
11 pwds = myuser: HJBoxfRsjeI=,myuser2:HJBoxfRsjeI=
12 #(必备，默认值即可)Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true
13 daemon = true
14 #(必备，默认值即可)设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true
15 keepalive = true
16 #(必备，根据实际情况配置)工作线程数，推荐设置成系统的CPU核数
17 # 对性能和正常运行起到重要作用
18 event-threads = 4
19 #(必备，默认值即可)日志级别，分为message、warning、critical、error、debug五个级别
20 log-level = message
21 #(必备，默认值即可)日志存放的路径
22 log-path = /usr/local/mysql-proxy/log
23 #(必备，根据实际情况配置)SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，该模式下日志刷新是基于缓冲区的，当日志填满缓冲区后，才将日志信息刷到磁盘。REALTIME用于调试，代表记录SQL日志且实时写入磁盘，默认为OFF
24 sql-log = OFF
25 #(可选项，可不设置）慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
26 sql-log-slow = 10
27 #(可选项，可不设置）关闭不活跃的客户端连接设置。当设置了该参数时，Atlas会主动关闭经过'wait-timeout'时间后一直未活跃的连接。单位：秒
28 wait-timeout = 10
29 #(必备，默认值即可)Atlas监听的工作接口IP和端口；代表客户端应该使用1234这个端口连接Atlas来发送SQL请求。
30 proxy-address = 0.0.0.0:1234
31 #(必备，默认值即可)Atlas监听的管理接口IP和端口 ；代表DBA应该使用2345这个端口连接Atlas来执行运维管理操作。
32 admin-address = 0.0.0.0:2345
33 #(可选项，可不设置)分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项，子表需要事先建好，子表名称为表名_数字，数字范围为[0,子表数-1]，如本例里，子表名称为mt_0、mt_1、mt_2
34 tables = person.mt.id.3
35 #(可选项，可不设置)默认字符集，若不设置该项，则默认字符集为latin1
36 charset = utf8
37 #(可选项，可不设置)允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
38 client-ips = 127.0.0.1, 192.168.1
39 #(可选项，极少需要)Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
40 lvs-ips = 192.168.1.1

 1 [root@db03 ~]# cat /etc/init.d/atlasd 
 2 #!/bin/sh
 3 #
 4 # atlas:    Atlas Daemon
 5 #
 6 # chkconfig:    - 90 25
 7 # description:  Atlas Daemon
 8 # user:clsn
 9 # Blog: http://blog.nmtui.com
10 # Source function library.
11 
12 Demo=test
13 
14 start()
15 {
16         echo -n $"Starting atlas: "
17         /usr/local/mysql-proxy/bin/mysql-proxyd $Demo start
18 }
19 stop()
20 {
21         echo -n $"Shutting down atlas: "
22         /usr/local/mysql-proxy/bin/mysql-proxyd $Demo stop
23 }
24 status()
25 {
26         echo  $"Atlas status: "
27         /usr/local/mysql-proxy/bin/mysql-proxyd $Demo status
28 }
29 restart()
30 {
31         echo $"Atlas Restart Info: "
32         /usr/local/mysql-proxy/bin/mysql-proxyd $Demo restart
33 }
34 
35 
36 ATLAS="/usr/local/mysql-proxy/bin/mysql-proxyd"
37 [ -f $ATLAS ] || exit 1
38 # See how we were called.
39 case "$1" in
40         start)
41                 start
42                 ;;
43         stop)
44                 stop
45                 ;;
46         restart)
47                 restart
48                 ;;
49         status)
50                 status
51                 ;;
52         *)
53                 echo $"Usage: $0 {start|stop|restart|status}"
54                 exit 1
55 esac
56 exit 0