【MySql】——MHA+GTID+failover+binlog-server+Atlas

2022-09-03 20:02:18

一、环境准备

1.mysql-db01

 #系统版本

 [root@mysql-db01 ~]# cat /etc/redhat-release

 CentOS release 6.7 (Final)

 #内核版本

 [root@mysql-db01 ~]# uname -r

 2.6.-.el6.x86_64

 #IP地址

 [root@mysql-db01 ~]# hostname -I

 10.0.0.51

2.mysql-db02

 #系统版本

 [root@mysql-db02 ~]# cat /etc/redhat-release

 CentOS release 6.7 (Final)

 #内核版本

 [root@mysql-db02 ~]# uname -r

 2.6.-.el6.x86_64

 #IP地址

 [root@mysql-db02 ~]# hostname -I

 10.0.0.52

3.mysql-db03

 #系统版本

 [root@mysql-db03 ~]# cat /etc/redhat-release

 CentOS release 6.7 (Final)

 #内核版本

 [root@mysql-db03 ~]# uname -r

 2.6.-.el6.x86_64

 #IP地址

 [root@mysql-db03 ~]# hostname -I

 10.0.0.53

二、安装mysql

1.安装包准备

 #创建安装包存放目录

 [root@mysql-db01 ~]# mkdir /home/oldboy/tools -p

 #进入目录

 [root@mysql-db01 ~]# cd /home/oldboy/tools/

 #上传mysql安装包（mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz）

 [root@mysql-db01 tools]# rz -be

2.安装

 #创建安装目录

 [root@mysql-db01 tools]# mkdir /application

 #解压mysql二进制包

 [root@mysql-db01 tools]# tar xf mysql-5.6.-linux-glibc2.-x86_64.tar.gz

 #移动安装包

 [root@mysql-db01 tools]# mv mysql-5.6.-linux-glibc2.-x86_64 /application/mysql-5.6.

 #做软链接

 [root@mysql-db01 tools]# ln -s /application/mysql-5.6./ /application/mysql

 #创建mysql用户

 [root@mysql-db01 tools]# useradd mysql -s /sbin/nologin -M

 #进入mysql初始化目录

 [root@mysql-db01 tools]# cd /application/mysql/scripts/

 #初始化mysql

 [root@mysql-db01 scripts]# ./mysql_install_db \

 --user=mysql \

 --datadir=/application/mysql/data/ \

 --basedir=/application/mysql/

 #注解

 --user：   指定mysql用户

 --datadir：指定mysql数据存放目录

 --basedir：指定mysql base目录

 #拷贝mysql配置文件

 [root@mysql-db01 ~]# \cp /application/mysql/support-files/my-default.cnf /etc/my.cnf

 #拷贝mysql启动脚本

 [root@mysql-db01 ~]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld

 #修改mysql默认安装目录（否则无法启动）

 [root@mysql-db01 ~]# sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld

 [root@mysql-db01 ~]# sed -i 's#/usr/local#/application#g' /application/mysql/bin/mysqld_safe

 #配置mysql环境变量

 [root@mysql-db01 ~]# echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh

 #刷新环境变量

 [root@mysql-db01 ~]# source /etc/profile

3.启动并添加开机自启

 #加入开机自启

 [root@mysql-db01 ~]# chkconfig mysqld on

 #启动mysql

 [root@mysql-db01 ~]# /etc/init.d/mysqld start

 Starting MySQL........... SUCCESS! #启动成功

4.设置root密码

 #配置mysql密码为oldboy123

 [root@mysql-db01 ~]# mysqladmin -uroot password oldboy123

三、主从复制

1.先决条件

主库和从库都要开启binlog （因为从库有可能被提升为主库，所以必须开启binlog）
主库和从库server-id不同
要有主从复制用户

2.主库操作

2.1修改配置文件

 #编辑mysql配置文件

 [root@mysql-db01 ~]# vim /etc/my.cnf

 #在mysqld标签下配置

 [mysqld]

 #主库server-id为1，从库必须和主库不一样

 server_id =

 #开启binlog日志

 log_bin=mysql-bin

2.2创建主从复制用户

 #登录数据库

 [root@mysql-db01 ~]# mysql -uroot -poldboy123

 #创建rep用户

 mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';

3.从库操作

3.1修改配置文件

 #修改mysql-db02配置文件

 [root@mysql-db02 ~]# vim /etc/my.cnf

 #在mysqld标签下配置

 [mysqld]

 #主库server-id为1，从库必须和主库不一样

 server_id =

 #开启binlog日志

 log_bin=mysql-bin

 #重启mysql

 [root@mysql-db02 ~]# /etc/init.d/mysqld restart

 #修改mysql-db03配置文件

 [root@mysql-db03 ~]# vim /etc/my.cnf

 #在mysqld标签下配置

 [mysqld]

 #主库server-id为1，从库必须和主库不一样

 server_id =

 #开启binlog日志

 log_bin=mysql-bin

 #重启mysql

 [root@mysql-db03 ~]# /etc/init.d/mysqld restart

注：在以往如果是基于binlog日志的主从复制，则必须要记住主库的master状态信息。

 mysql> show master status;

+------------------+----------+

| File | Position |

+------------------+----------+

| mysql-bin.000002 | 120 |

+------------------+----------+

4.开启GTID

 #没开启之前先看一下GTID的状态

 mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF   |
| gtid_executed            |       |
| gtid_mode                | OFF   |
| gtid_owned               |       |
| gtid_purged              |       |
+--------------------------+-------+

 #编辑mysql配置文件（主库从库都需要修改）

 [root@mysql-db01 ~]# vim /etc/my.cnf

 #在[mysqld]标签下添加

 [mysqld]

 gtid_mode=ON

 log_slave_updates

 enforce_gtid_consistency

 #重启数据库

 [root@mysql-db01 ~]# /etc/init.d/mysqld restart

 #检查GTID状态

 mysql> show global variables like '%gtid%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| enforce_gtid_consistency | ON | #执行GTID一致

| gtid_executed | |

| gtid_mode | ON | #开启GTID模块

| gtid_owned | |

| gtid_purged | |

+--------------------------+-------+

注：主库从库都需要开启GTID否则在做主从复制的时候就会报错

 [root@mysql-db02 ~]# mysql -uroot -poldboy123

 mysql> change master to

 -> master_host='10.0.0.51',

 -> master_user='rep',

 -> master_password='oldboy123',

 -> master_auto_position=;

 ERROR  (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION =  can only be executed when @@GLOBAL.GTID_MODE = ON.

5.配置主从复制

 #登录数据库

 [root@mysql-db02 ~]# mysql -uroot -poldboy123

 #配置复制主机信息

 mysql> change master to

 #主库IP

 -> master_host='10.0.0.51',

 #主库复制用户

 -> master_user='rep',

 #主库复制用户的密码

 -> master_password='oldboy123',

 #GTID位置点

 -> master_auto_position=;

 #开启slave

 mysql> start slave;

 #查看slave状态

 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51                                 #主库IP
                  Master_User: rep
                  Master_Port: 3306                                      #主库端口
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004                          #binlog名
          Read_Master_Log_Pos: 191
               Relay_Log_File: mysql-db02-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes                                       #IO线程状态
            Slave_SQL_Running: Yes                                       #SQL线程状态
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1       #GTID号
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1       #GTID号
                Auto_Position: 1

6.从库其它配置

 #登录从库

 [root@mysql-db02 ~]# mysql -uroot -poldboy123

 #禁用自动删除relay log 功能

 mysql> set global relay_log_purge = ;

 #设置只读

 mysql> set global read_only=;

 #编辑配置文件

 [root@mysql-db02 ~]# vim /etc/my.cnf

 #在mysqld标签下添加

 [mysqld]

 #禁用自动删除relay log 永久生效

 relay_log_purge =

四、部署MHA

1.环境准备（所有节点）

 #安装依赖包

 [root@mysql-db01 ~]# yum install perl-DBD-MySQL -y

 #进入安装包存放目录

 [root@mysql-db01 ~]# cd /home/oldboy/tools/

 #上传mha安装包

 [root@mysql-db01 tools]# rz -be

 mha4mysql-manager-0.56-.el6.noarch.rpm

 mha4mysql-manager-0.56.tar.gz

 mha4mysql-node-0.56-.el6.noarch.rpm

 mha4mysql-node-0.56.tar.gz

 #安装node包

 [root@mysql-db01 tools]# rpm -ivh mha4mysql-node-0.56-.el6.noarch.rpm

 #登录数据库

 [root@mysql-db01 tools]# mysql -uroot -poldboy123

 #添加mha管理账号

 mysql> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';

 #查看是否添加成功

 mysql> select user,host from mysql.user;

 #主库上创建，从库会自动复制（在从库上查看）

 mysql> select user,host from mysql.user;

2.命令软连接

 #如果不创建命令软连接，检测mha复制情况的时候会报错

 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

3.管理节点（mha-manager）

3.1在mysql-db03上安装管理节点

 #使用epel源

 [root@mysql-db03 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo

 #安装manager依赖包

 [root@mysql-db03 ~]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

 #安装manager包

 [root@mysql-db03 tools]# rpm -ivh mha4mysql-manager-0.56-.el6.noarch.rpm

 Preparing...              ########################################### [%]

 :mha4mysql-manager       ########################################### [%]

4.配置文件

 #创建配置文件目录

 [root@mysql-db03 ~]# mkdir -p /etc/mha

 #创建日志目录

 [root@mysql-db03 ~]# mkdir -p /var/log/mha/app1

 #编辑mha配置文件

 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf

 [server default]

 manager_log=/var/log/mha/app1/manager

 manager_workdir=/var/log/mha/app1

 master_binlog_dir=/application/mysql/data

 user=mha

 password=mha

 ping_interval=

 repl_password=oldboy123

 repl_user=rep

 ssh_user=root                               

 [server1]

 hostname=10.0.0.51

 port=                                  

 [server2]

 candidate_master=

 check_repl_delay=

 hostname=10.0.0.52

 port=

 [server3]

 hostname=10.0.0.53

 port=

【配置文件详解】

 [server default]

 #设置manager的工作目录

 manager_workdir=/var/log/masterha/app1

 #设置manager的日志

 manager_log=/var/log/masterha/app1/manager.log

 #设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录

 master_binlog_dir=/data/mysql

 #设置自动failover时候的切换脚本

 master_ip_failover_script= /usr/local/bin/master_ip_failover

 #设置手动切换时候的切换脚本

 master_ip_online_change_script= /usr/local/bin/master_ip_online_change

 #设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码

 password=

 #设置监控用户root

 user=root

 #设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover

 ping_interval=

 #设置远端mysql在发生切换时binlog的保存位置

 remote_workdir=/tmp

 #设置复制用户的密码

 repl_password=

 #设置复制环境中的复制用户名

 repl_user=rep

 #设置发生切换后发送的报警的脚本

 report_script=/usr/local/send_report

 #一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02

 secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=192.168.0.50 --master_port=

 #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）

 shutdown_script=""

 #设置ssh的登录用户名

 ssh_user=root 

 [server1]

 hostname=10.0.0.51

 port=

 [server2]

 hostname=10.0.0.52

 port=

 #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave

 candidate_master=

 #默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

 check_repl_delay=

5.ssh-key(所有节点)

 #创建秘钥对

 [root@mysql-db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null >&

 #发送公钥，包括自己

 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51

 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52

 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53

6.启动前测试

 #测试ssh

 [root@mysql-db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf

 #看到如下字样，则测试成功

 Tue Mar   ::  - [info] All SSH connection tests passed successfully.

 #测试复制

 [root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf

 #看到如下字样，则测试成功

 MySQL Replication Health is OK.

7.启动MHA

 #启动

 [root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log >& &

8.failover测试

 #登录数据库（db02）

 [root@mysql-db02 ~]# mysql -uroot -poldboy123

 #检查复制情况

 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 191
               Relay_Log_File: mysql-db02-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
                Auto_Position: 1

 #登录数据库（db03）

 [root@mysql-db03 ~]# mysql -uroot -poldboy123

 #检查复制情况

 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 191
               Relay_Log_File: mysql-db02-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1

 #停掉主库

 [root@mysql-db01 ~]# /etc/init.d/mysqld stop

 Shutting down MySQL..... SUCCESS!

 #登录数据库（db02）

 [root@mysql-db02 ~]# mysql -uroot -poldboy123

 #查看slave状态

 mysql> show slave status\G

 #db02的slave已经为空

 Empty set (0.00 sec)

 #登录数据库（db03）

 [root@mysql-db03 ~]# mysql -uroot -poldboy123

 #查看slave状态

 mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 191

Relay_Log_File: mysql-db03-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1

五、VIP漂移

1.vip漂移的两种方式

通过keepalived的方式，管理VIP的漂移
通过MHA自带脚本方式，管理VIP的漂移

2.MHA自带脚本方式

2.1修改配置文件

 #编辑配置文件

 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf

 #在[server default]标签下添加

 [server default]

 #使用MHA自带脚本

 master_ip_failover_script=/usr/local/bin/master_ip_failover

2.2编辑脚本

 #根据配置文件中脚本路径编辑

 [root@mysql-db03 ~]# vim /etc/mha/master_ip_failover

 #修改以下几行内容

 my $vip = '10.0.0.55/24';

 my $key = '';

 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";

 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

 #添加执行权限，否则mha无法启动

 [root@mysql-db03 ~]# chmod +x /etc/mha/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 = '';

my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";

my $ssh_stop_vip = "/sbin/ifconfig eth1:$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 = ;

        eval {

            print "Disabling the VIP on old master: $orig_master_host \n";

            &stop_vip();

            $exit_code = ;

        };

        if ($@) {

            warn "Got Error: $@\n";

            exit $exit_code;

        }

        exit $exit_code;

    }

    elsif ( $command eq "start" ) {

        my $exit_code = ;

        eval {

            print "Enabling the VIP - $vip on the new master - $new_master_host \n";

            &start_vip();

            $exit_code = ;

        };

        if ($@) {

            warn $@;

            exit $exit_code;

        }

        exit $exit_code;

    }

    elsif ( $command eq "status" ) {

        print "Checking the Status of the script.. OK \n";

        exit ;

    }

    else {

        &usage();

        exit ;

    }

}

sub start_vip() {

    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

}

sub stop_vip() {

     return   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";

}

2.3手动绑定VIP

 #绑定vip

 [root@mysql-db01 ~]# ifconfig eth0: 10.0.0.55/

 #查看vip

 [root@mysql-db01 ~]# ip a |grep eth0

 : eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu  qdisc pfifo_fast state UP qlen

     inet 10.0.0.51/ brd 10.0.0.255 scope global eth0

     inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0

2.4测试VIP漂移

 #登录db02

 [root@mysql-db02 ~]# mysql -uroot -poldboy123

 #查看slave信息

 mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 191

Relay_Log_File: mysql-db02-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 #停掉主库

 [root@mysql-db01 ~]# /etc/init.d/mysqld stop

 Shutting down MySQL..... SUCCESS!

 #在db03上查看从库slave信息

 mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 191

Relay_Log_File: mysql-db03-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 #在db01上查看vip信息

 [root@mysql-db01 ~]# ip a |grep eth0

 : eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu  qdisc pfifo_fast state UP qlen

 inet 10.0.0.51/ brd 10.0.0.255 scope global eth0

 #在db02上查看vip信息

 [root@mysql-db02 ~]# ip a |grep eth0

 : eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu  qdisc pfifo_fast state UP qlen

     inet 10.0.0.52/ brd 10.0.0.255 scope global eth0

     inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0

六、binlog-server

1.先决条件

mysql版本5.6以上，必须开启GTID
mha版本0.56以上

2.修改配置文件

 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf

 [binlog1]

 no_master=

 hostname=10.0.0.53

 master_binlog_dir=/data/mysql/binlog/

3.备份binlog

 #创建备份binlog目录

 [root@mysql-db03 ~]# mkdir -p /data/mysql/binlog/

 #进入该目录

 [root@mysql-db03 ~]# cd /data/mysql/binlog/

 #备份binlog

 [root@mysql-db03 binlog]# mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin. &

 #启动mha

 [root@mysql-db03 binlog]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log >& &

4.测试binlog-server

 #查看binlog目录中的binlog

 [root@mysql-db03 binlog]# ll

 total

 -rw-r--r--  root root  Mar   : mysql-bin.

 #登录主库

 [root@mysql-db01 ~]# mysql -uroot -poldboy123

 #刷新binlog

 mysql> flush logs;

 #再次查看binlog目录

 [root@mysql-db03 binlog]# ll

 total

 -rw-r--r--  root root  Mar   : mysql-bin.

 -rw-r--r--  root root  Mar   : mysql-bin.

七、mysql读写分离中间件Atlas

1.简介

　　Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。

2.主要功能

读写分离
从库负载均衡
IP过滤
自动分表
DBA可平滑上下线DB
自动摘除宕机的DB

3.Atlas相对于官方MySQL-Proxy的优势

将主流程中所有Lua代码用C重写，Lua仅用于管理接口
重写网络模型、线程模型
实现了真正意义上的连接池
优化了锁机制，性能提高数十倍

4.安装

大家有福了，安装Atlas真的是炒鸡简单，官方提供的Atlas有两种：

Atlas (普通) : Atlas-2.2.1.el6.x86_64.rpm

Atlas (分表) : Atlas-sharding_1.0.1-el6.x86_64.rpm

这里我们只需要下载普通的即可。

 #在主库安装，进入安装包目录

 [root@mysql-db01 ~]# cd /home/oldboy/tools/

 #下载Atlas

 [root@mysql-db01 tools]#

 wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm

 #安装

 [root@mysql-db01 tools]# rpm -ivh Atlas-2.2..el6.x86_64.rpm

 Preparing...               ########################################### [%]

   :Atlas                  ########################################### [%]

5.配置文件

 #进入Atlas工具目录

 [root@mysql-db01 ~]# cd /usr/local/mysql-proxy/bin/

 #生成密码

 [root@mysql-db01 bin]# ./encrypt oldboy123

 #修改Atlas配置文件

 [root@mysql-db01 ~]# vim /usr/local/mysql-proxy/conf/test.cnf

 #Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔

 proxy-backend-addresses = 10.0.0.51:

 #Atlas后端连接的MySQL从库的IP和端口

 proxy-read-only-backend-addresses = 10.0.0.52:,10.0.0.53:

 #用户名与其对应的加密过的MySQL密码

 pwds = root:1N/CNLSgqXuTZ6zxvGQr9A==

 #SQL日志的开关

 sql-log = ON

 #Atlas监听的工作接口IP和端口

 proxy-address = 0.0.0.0:

 #默认字符集，设置该项后客户端不再需要执行SET NAMES语句

 charset = utf8

6.启动

 [root@mysql-db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start

 OK: MySQL-Proxy of test is started

7.管理操作

 #用atlas管理用户登录

 [root@mysql-db01 ~]# mysql -uuser -ppwd -h127.0.0. -P2345

 #查看可用命令帮助

 mysql> select * from help;

 #查看后端代理的库

 mysql> SELECT * FROM backends;

　　+-------------+----------------+-------+------+

　　+-------------+----------------+-------+------+

　　| 1 | 10.0.0.51:3307 | up | rw |

　　| 2 | 10.0.0.53:3307 | up | ro |

　　| 3 | 10.0.0.52:3307 | up | ro |

　　+-------------+----------------+-------+------+

 #平滑摘除mysql

 mysql> REMOVE BACKEND ;

 Empty set (0.00 sec)

 #检查是否摘除成功

 mysql> SELECT * FROM backends;

　　+-------------+----------------+-------+------+

　　+-------------+----------------+-------+------+

　　| 1 | 10.0.0.51:3307 | up | rw |

　　| 2 | 10.0.0.52:3307 | up | ro |

　　+-------------+----------------+-------+------+

 #保存到配置文件中

 mysql> SAVE CONFIG;

 Empty set (0.06 sec)

【开源是一种精神，分享是一种美德】

— By GoodCook

— 笔者QQ：253097001

— 欢迎大家随时来交流

—原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。

码农公寓

一、环境准备

1.mysql-db01

2.mysql-db02

3.mysql-db03

二、安装mysql

1.安装包准备

2.安装

3.启动并添加开机自启

4.设置root密码

三、主从复制

1.先决条件

2.主库操作

2.1修改配置文件

2.2创建主从复制用户

3.从库操作

3.1修改配置文件

4.开启GTID

5.配置主从复制

6.从库其它配置

四、部署MHA

1.环境准备（所有节点）

2.命令软连接

3.管理节点（mha-manager）

3.1在mysql-db03上安装管理节点

4.配置文件

5.ssh-key(所有节点)

6.启动前测试

7.启动MHA

8.failover测试

五、VIP漂移

1.vip漂移的两种方式

2.MHA自带脚本方式

2.1修改配置文件

2.2编辑脚本

2.3手动绑定VIP

2.4测试VIP漂移

六、binlog-server

1.先决条件

2.修改配置文件

3.备份binlog

4.测试binlog-server

七、mysql读写分离中间件Atlas

1.简介

2.主要功能

3.Atlas相对于官方MySQL-Proxy的优势

4.安装

5.配置文件

6.启动

7.管理操作

相关文章