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

时间：2021-07-23 22:37:43

一、环境准备

1.mysql-db01

1 #系统版本
2 [root@mysql-db01 ~]# cat /etc/redhat-release 
3 CentOS release 6.7 (Final)
4 #内核版本
5 [root@mysql-db01 ~]# uname -r
6 2.6.32-573.el6.x86_64
7 #IP地址
8 [root@mysql-db01 ~]# hostname -I
9 10.0.0.51

2.mysql-db02

1 #系统版本
2 [root@mysql-db02 ~]# cat /etc/redhat-release
3 CentOS release 6.7 (Final)
4 #内核版本
5 [root@mysql-db02 ~]# uname -r
6 2.6.32-573.el6.x86_64
7 #IP地址
8 [root@mysql-db02 ~]# hostname -I
9 10.0.0.52

3.mysql-db03

1 #系统版本
2 [root@mysql-db03 ~]# cat /etc/redhat-release 
3 CentOS release 6.7 (Final)
4 #内核版本
5 [root@mysql-db03 ~]# uname -r
6 2.6.32-573.el6.x86_64
7 #IP地址
8 [root@mysql-db03 ~]# hostname -I
9 10.0.0.53

二、安装mysql

1.安装包准备

1 #创建安装包存放目录
2 [root@mysql-db01 ~]# mkdir /home/oldboy/tools -p
3 #进入目录
4 [root@mysql-db01 ~]# cd /home/oldboy/tools/
5 #上传mysql安装包（mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz）
6 [root@mysql-db01 tools]# rz -be

2.安装

 1 #创建安装目录
 2 [root@mysql-db01 tools]# mkdir /application
 3 #解压mysql二进制包
 4 [root@mysql-db01 tools]# tar xf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
 5 #移动安装包
 6 [root@mysql-db01 tools]# mv mysql-5.6.16-linux-glibc2.5-x86_64 /application/mysql-5.6.16
 7 #做软链接
 8 [root@mysql-db01 tools]# ln -s /application/mysql-5.6.16/ /application/mysql
 9 #创建mysql用户
10 [root@mysql-db01 tools]# useradd mysql -s /sbin/nologin -M
11 #进入mysql初始化目录
12 [root@mysql-db01 tools]# cd /application/mysql/scripts/
13 #初始化mysql
14 [root@mysql-db01 scripts]# ./mysql_install_db \
15 --user=mysql \
16 --datadir=/application/mysql/data/ \
17 --basedir=/application/mysql/
18 #注解
19 --user：   指定mysql用户
20 --datadir：指定mysql数据存放目录
21 --basedir：指定mysql base目录
22 #拷贝mysql配置文件
23 [root@mysql-db01 ~]# \cp /application/mysql/support-files/my-default.cnf /etc/my.cnf
24 #拷贝mysql启动脚本
25 [root@mysql-db01 ~]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
26 #修改mysql默认安装目录（否则无法启动）
27 [root@mysql-db01 ~]# sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld
28 [root@mysql-db01 ~]# sed -i 's#/usr/local#/application#g' /application/mysql/bin/mysqld_safe
29 #配置mysql环境变量
30 [root@mysql-db01 ~]# echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
31 #刷新环境变量
32 [root@mysql-db01 ~]# source /etc/profile

3.启动并添加开机自启

1 #加入开机自启
2 [root@mysql-db01 ~]# chkconfig mysqld on
3 #启动mysql
4 [root@mysql-db01 ~]# /etc/init.d/mysqld start
5 Starting MySQL........... SUCCESS! #启动成功

4.设置root密码

1 #配置mysql密码为oldboy123
2 [root@mysql-db01 ~]# mysqladmin -uroot password oldboy123

三、主从复制

1.先决条件

  • 主库和从库都要开启binlog （因为从库有可能被提升为主库，所以必须开启binlog）

  • 主库和从库server-id不同

  • 要有主从复制用户

2.主库操作

2.1修改配置文件

1 #编辑mysql配置文件
2 [root@mysql-db01 ~]# vim /etc/my.cnf
3 #在mysqld标签下配置
4 [mysqld]
5 #主库server-id为1，从库必须和主库不一样
6 server_id =1
7 #开启binlog日志
8 log_bin=mysql-bin

2.2创建主从复制用户

1 #登录数据库
2 [root@mysql-db01 ~]# mysql -uroot -poldboy123
3 #创建rep用户
4 mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';

3.从库操作

3.1修改配置文件

 1 #修改mysql-db02配置文件
 2 [root@mysql-db02 ~]# vim /etc/my.cnf
 3 #在mysqld标签下配置
 4 [mysqld]
 5 #主库server-id为1，从库必须和主库不一样
 6 server_id =5
 7 #开启binlog日志
 8 log_bin=mysql-bin
 9 #重启mysql
10 [root@mysql-db02 ~]# /etc/init.d/mysqld restart
11 
12 #修改mysql-db03配置文件
13 [root@mysql-db03 ~]# vim /etc/my.cnf
14 #在mysqld标签下配置
15 [mysqld]
16 #主库server-id为1，从库必须和主库不一样
17 server_id =10
18 #开启binlog日志
19 log_bin=mysql-bin
20 #重启mysql
21 [root@mysql-db03 ~]# /etc/init.d/mysqld restart

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

 

1 mysql> show master status;

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

| File             | Position |

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

| mysql-bin.000002 |      120 |

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

 

 

 

4.开启GTID

 1 #没开启之前先看一下GTID的状态
 2 mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF   |
| gtid_executed            |       |
| gtid_mode                | OFF   |
| gtid_owned               |       |
| gtid_purged              |       |
+--------------------------+-------+
 3 #编辑mysql配置文件（主库从库都需要修改）
 4 [root@mysql-db01 ~]# vim /etc/my.cnf
 5 #在[mysqld]标签下添加
 6 [mysqld]
 7 gtid_mode=ON
 8 log_slave_updates
 9 enforce_gtid_consistency
10 #重启数据库
11 [root@mysql-db01 ~]# /etc/init.d/mysqld restart
12 #检查GTID状态
13 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否则在做主从复制的时候就会报错

1 [root@mysql-db02 ~]# mysql -uroot -poldboy123
2 mysql> change master to
3 -> master_host='10.0.0.51',
4 -> master_user='rep',
5 -> master_password='oldboy123',
6 -> master_auto_position=1;
7 ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.

 

5.配置主从复制

 1 #登录数据库
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #配置复制主机信息
 4 mysql> change master to
 5 #主库IP
 6 -> master_host='10.0.0.51',
 7 #主库复制用户
 8 -> master_user='rep',
 9 #主库复制用户的密码
10 -> master_password='oldboy123',
11 #GTID位置点
12 -> master_auto_position=1;
13 #开启slave
14 mysql> start slave;
15 #查看slave状态
16 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.从库其它配置

 1 #登录从库
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #禁用自动删除relay log 功能
 4 mysql> set global relay_log_purge = 0;
 5 #设置只读
 6 mysql> set global read_only=1;
 7 #编辑配置文件
 8 [root@mysql-db02 ~]# vim /etc/my.cnf
 9 #在mysqld标签下添加
10 [mysqld]
11 #禁用自动删除relay log 永久生效
12 relay_log_purge = 0

四、部署MHA

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

 1 #安装依赖包
 2 [root@mysql-db01 ~]# yum install perl-DBD-MySQL -y
 3 #进入安装包存放目录
 4 [root@mysql-db01 ~]# cd /home/oldboy/tools/
 5 #上传mha安装包
 6 [root@mysql-db01 tools]# rz -be
 7 mha4mysql-manager-0.56-0.el6.noarch.rpm
 8 mha4mysql-manager-0.56.tar.gz
 9 mha4mysql-node-0.56-0.el6.noarch.rpm
10 mha4mysql-node-0.56.tar.gz
11 #安装node包
12 [root@mysql-db01 tools]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
13 #登录数据库
14 [root@mysql-db01 tools]# mysql -uroot -poldboy123
15 #添加mha管理账号
16 mysql> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';
17 #查看是否添加成功
18 mysql> select user,host from mysql.user;
19 #主库上创建，从库会自动复制（在从库上查看）
20 mysql> select user,host from mysql.user;

2.命令软连接

1 #如果不创建命令软连接，检测mha复制情况的时候会报错
2 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
3 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

3.管理节点（mha-manager）

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

1 #使用epel源
2 [root@mysql-db03 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
3 #安装manager依赖包
4 [root@mysql-db03 ~]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
5 #安装manager包
6 [root@mysql-db03 tools]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
7 Preparing...              ########################################### [100%]
8 1:mha4mysql-manager       ########################################### [100%]

4.配置文件

 1 #创建配置文件目录
 2 [root@mysql-db03 ~]# mkdir -p /etc/mha
 3 #创建日志目录
 4 [root@mysql-db03 ~]# mkdir -p /var/log/mha/app1
 5 #编辑mha配置文件
 6 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf
 7 [server default]
 8 manager_log=/var/log/mha/app1/manager        
 9 manager_workdir=/var/log/mha/app1            
10 master_binlog_dir=/application/mysql/data        
11 user=mha                                   
12 password=mha                               
13 ping_interval=2
14 repl_password=oldboy123
15 repl_user=rep
16 ssh_user=root                               
17 
18 [server1]                                   
19 hostname=10.0.0.51
20 port=3306                                  
21 
22 [server2]
23 candidate_master=1                         
24 check_repl_delay=0                          
25 hostname=10.0.0.52
26 port=3306
27 
28 [server3]
29 hostname=10.0.0.53
30 port=3306

【配置文件详解】

 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=192.168.0.50 --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

5.ssh-key(所有节点)

1 #创建秘钥对
2 [root@mysql-db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
3 #发送公钥，包括自己
4 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
5 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
6 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53

6.启动前测试

1 #测试ssh
2 [root@mysql-db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
3 #看到如下字样，则测试成功
4 Tue Mar  7 01:03:33 2017 - [info] All SSH connection tests passed successfully.
5 #测试复制
6 [root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
7 #看到如下字样，则测试成功
8 MySQL Replication Health is OK.

7.启动MHA

1 #启动
2 [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 2>&1 &

8.failover测试

 1 #登录数据库（db02）
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #检查复制情况
 4 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
 5 #登录数据库（db03）
 6 [root@mysql-db03 ~]# mysql -uroot -poldboy123
 7 #检查复制情况
 8 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
 9 #停掉主库
10 [root@mysql-db01 ~]# /etc/init.d/mysqld stop
11 Shutting down MySQL..... SUCCESS!
12 #登录数据库（db02）
13 [root@mysql-db02 ~]# mysql -uroot -poldboy123
14 #查看slave状态
15 mysql> show slave status\G
16 #db02的slave已经为空
17 Empty set (0.00 sec)
18 #登录数据库（db03）
19 [root@mysql-db03 ~]# mysql -uroot -poldboy123
20 #查看slave状态
21 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修改配置文件

1 #编辑配置文件
2 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf
3 #在[server default]标签下添加
4 [server default]
5 #使用MHA自带脚本
6 master_ip_failover_script=/usr/local/bin/master_ip_failover

2.2编辑脚本

1 #根据配置文件中脚本路径编辑
2 [root@mysql-db03 ~]# vim /etc/mha/master_ip_failover
3 #修改以下几行内容
4 my $vip = '10.0.0.55/24';
5 my $key = '0';
6 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
7 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; 
8 #添加执行权限，否则mha无法启动
9 [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 = '1';
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 = 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";
}

2.3手动绑定VIP

1 #绑定vip
2 [root@mysql-db01 ~]# ifconfig eth0:0 10.0.0.55/24
3 #查看vip
4 [root@mysql-db01 ~]# ip a |grep eth0
5 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
6     inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
7     inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0

2.4测试VIP漂移

 1 #登录db02
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #查看slave信息
 4 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

 5 #停掉主库
 6 [root@mysql-db01 ~]# /etc/init.d/mysqld stop
 7 Shutting down MySQL..... SUCCESS!
 8 #在db03上查看从库slave信息
 9 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

10 #在db01上查看vip信息
11 [root@mysql-db01 ~]# ip a |grep eth0
12 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
13 inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
14 #在db02上查看vip信息
15 [root@mysql-db02 ~]# ip a |grep eth0
16 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
17     inet 10.0.0.52/24 brd 10.0.0.255 scope global eth0
18     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.修改配置文件

1 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf
2 [binlog1]
3 no_master=1
4 hostname=10.0.0.53
5 master_binlog_dir=/data/mysql/binlog/

3.备份binlog

1 #创建备份binlog目录
2 [root@mysql-db03 ~]# mkdir -p /data/mysql/binlog/
3 #进入该目录
4 [root@mysql-db03 ~]# cd /data/mysql/binlog/
5 #备份binlog
6 [root@mysql-db03 binlog]# mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &
7 #启动mha
8 [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 2>&1 &

4.测试binlog-server

 1 #查看binlog目录中的binlog
 2 [root@mysql-db03 binlog]# ll
 3 total 44
 4 -rw-r--r-- 1 root root 285 Mar  8 03:11 mysql-bin.000001
 5 #登录主库
 6 [root@mysql-db01 ~]# mysql -uroot -poldboy123
 7 #刷新binlog
 8 mysql> flush logs;
 9 #再次查看binlog目录
10 [root@mysql-db03 binlog]# ll
11 total 48
12 -rw-r--r-- 1 root root 285 Mar  8 03:11 mysql-bin.000001
13 -rw-r--r-- 1 root root 143 Mar  8 04:00 mysql-bin.000002

七、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

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

1 #在主库安装，进入安装包目录
2 [root@mysql-db01 ~]# cd /home/oldboy/tools/
3 #下载Atlas
4 [root@mysql-db01 tools]# 
5 wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
6 #安装
7 [root@mysql-db01 tools]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 
8 Preparing...               ########################################### [100%]
9   1:Atlas                  ########################################### [100%]

5.配置文件

 1 #进入Atlas工具目录
 2 [root@mysql-db01 ~]# cd /usr/local/mysql-proxy/bin/
 3 #生成密码
 4 [root@mysql-db01 bin]# ./encrypt oldboy123
 5 #修改Atlas配置文件
 6 [root@mysql-db01 ~]# vim /usr/local/mysql-proxy/conf/test.cnf
 7 #Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
 8 proxy-backend-addresses = 10.0.0.51:3306
 9 #Atlas后端连接的MySQL从库的IP和端口
10 proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306
11 #用户名与其对应的加密过的MySQL密码
12 pwds = root:1N/CNLSgqXuTZ6zxvGQr9A==
13 #SQL日志的开关
14 sql-log = ON
15 #Atlas监听的工作接口IP和端口
16 proxy-address = 0.0.0.0:3307
17 #默认字符集，设置该项后客户端不再需要执行SET NAMES语句
18 charset = utf8

6.启动

1 [root@mysql-db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
2 OK: MySQL-Proxy of test is started

7.管理操作

 1 #用atlas管理用户登录
 2 [root@mysql-db01 ~]# mysql -uuser -ppwd -h127.0.0.1 -P2345
 3 #查看可用命令帮助
 4 mysql> select * from help;
 5 #查看后端代理的库
 6 mysql> SELECT * FROM backends;

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

　　| backend_ndx | address        | state | type |

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

　　|           1 | 10.0.0.51:3307 | up    | rw   |

　　|           2 | 10.0.0.53:3307 | up    | ro   |

　　|           3 | 10.0.0.52:3307 | up    | ro   |

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

 7 #平滑摘除mysql
 8 mysql> REMOVE BACKEND 2;
 9 Empty set (0.00 sec)
10 #检查是否摘除成功
11 mysql> SELECT * FROM backends;

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

　　| backend_ndx | address        | state | type |

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

　　|           1 | 10.0.0.51:3307 | up    | rw   |

　　|           2 | 10.0.0.52:3307 | up    | ro   |

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

12 #保存到配置文件中
13 mysql> SAVE CONFIG;
14 Empty set (0.06 sec)

 

 

 

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

  — By GoodCook

  — 笔者QQ：253097001

  — 欢迎大家随时来交流

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

标签：

相关文章